Author: spadkins
Date: Thu Apr 15 13:27:56 2010
New Revision: 13906
Modified:
p5ee/trunk/App-Repository/bin/dbget
Log:
add csv option
Modified: p5ee/trunk/App-Repository/bin/dbget
==============================================================================
--- p5ee/trunk/App-Repository/bin/dbget (original)
+++ p5ee/trunk/App-Repository/bin/dbget Thu Apr 15 13:27:56 2010
@@ -4,7 +4,7 @@
use App::Options (
options => [ qw(dbhost dbname dbuser dbpass repository table params
columns headings order_by compact decimals subtotal_columns totals
- distinct startrow endrow cache_skip cache_refresh verbose)
],
+ distinct startrow endrow csv delimiter quoted cache_skip
cache_refresh verbose) ],
option => {
repository => {
default => "default",
@@ -57,6 +57,16 @@
hashkey => {
description => "hash key to get params from the cache",
},
+ csv => {
+ description => "Create output as a CSV (or with any other
delimiter specified by --delimiter)",
+ },
+ delimiter => {
+ description => "The delimiter to use when producing CSV output
files",
+ default => ",",
+ },
+ quoted => {
+ description => "quote each value if it contains the delimiter",
+ },
log_cache => {
description => "Log cache activity",
},
@@ -80,12 +90,13 @@
{
my $context = App->context();
- my $db = $context->repository($App::options{repository});
- my $table = $App::options{table};
- my $verbose = $App::options{verbose};
+ my $options = $context->{options};
+ my $db = $context->repository($options->{repository});
+ my $table = $options->{table};
+ my $verbose = $options->{verbose};
my ($columns, $params, $headings, $get_options, $cache_rows);
- if ($table && $App::options{hashkey}) {
- my $hashkey = $App::options{hashkey};
+ if ($table && $options->{hashkey}) {
+ my $hashkey = $options->{hashkey};
my $table_def = $db->get_table_def($table);
my $cache_name = $table_def->{cache_name};
if ($cache_name) {
@@ -105,30 +116,30 @@
}
}
else {
- if ($App::options{columns}) {
- $columns = [ split(/,/, $App::options{columns}) ];
+ if ($options->{columns}) {
+ $columns = [ split(/,/, $options->{columns}) ];
}
else {
$columns = $db->_get_default_columns($table);
}
- die "Must supply the --params option\n" if (! defined
$App::options{params});
- $params = { split(/[=>\|]+/, $App::options{params}) };
- $headings = $App::options{headings} ? [ split(/,/,
$App::options{headings}) ] : [];
+ die "Must supply the --params option\n" if (! defined
$options->{params});
+ $params = { split(/[=>\|]+/, $options->{params}) };
+ $headings = $options->{headings} ? [ split(/,/, $options->{headings})
] : [];
$get_options = { extend_columns => 1 };
}
- $get_options->{distinct} = 1 if ($App::options{distinct});
- $get_options->{order_by} = [ split(/,/, $App::options{order_by}) ] if
($App::options{order_by});
- $get_options->{startrow} = $App::options{startrow} if
($App::options{startrow});
- $get_options->{endrow} = $App::options{endrow} if
($App::options{endrow});
- $get_options->{cache_skip} = 1 if ($App::options{cache_skip});
- $get_options->{cache_refresh} = 1 if ($App::options{cache_refresh});
+ $get_options->{distinct} = 1 if ($options->{distinct});
+ $get_options->{order_by} = [ split(/,/, $options->{order_by}) ] if
($options->{order_by});
+ $get_options->{startrow} = $options->{startrow} if
($options->{startrow});
+ $get_options->{endrow} = $options->{endrow} if
($options->{endrow});
+ $get_options->{cache_skip} = 1 if ($options->{cache_skip});
+ $get_options->{cache_refresh} = 1 if ($options->{cache_refresh});
my $rows = $db->get_rows($table, $params, $columns, $get_options);
my ($subtotal_rows, $total_rows);
- if ($App::options{subtotal_columns}) {
- my $subtotal_columns = [ split(/,/, $App::options{subtotal_columns}) ];
+ if ($options->{subtotal_columns}) {
+ my $subtotal_columns = [ split(/,/, $options->{subtotal_columns}) ];
$subtotal_rows = $db->summarize_rows($table, $rows, $columns,
$subtotal_columns);
}
- if ($App::options{totals}) {
+ if ($options->{totals}) {
$total_rows = $db->summarize_rows($table, $rows, $columns);
}
if ($subtotal_rows) {
@@ -138,7 +149,12 @@
push(@$rows, @$total_rows);
}
my $formats = [];
- &print_table($rows, $columns, $formats, { compact =>
$App::options{compact}, headings => $headings });
+ if ($options->{csv}) {
+ &print_csv($rows, $columns, $formats, { headings => $headings,
delimiter => $options->{delimiter}, quoted => $options->{quoted} });
+ }
+ else {
+ &print_table($rows, $columns, $formats, { compact =>
$options->{compact}, headings => $headings });
+ }
}
sub print_table {
@@ -235,6 +251,60 @@
&App::sub_exit() if ($App::trace);
}
+sub print_csv {
+ &App::sub_entry if ($App::trace);
+ my ($rows, $columns, $formats, $options) = @_;
+ my ($row, $r, $c, $elem, $format, $len, $f, $heading);
+ my (@autoformat);
+ my $headings = $options->{headings};
+ my $delimiter = $options->{delimiter};
+ my $quoted = $options->{quoted};
+
+ my ($delimiter_regexp);
+ if ($delimiter eq "|") {
+ $delimiter_regexp = "\\$delimiter";
+ }
+ else {
+ $delimiter_regexp = $delimiter;
+ }
+
+ # compute the number of columns as the max columns of any row
+ my $max_columns = 0;
+ for ($r = 0; $r <= $#$rows; $r++) {
+ $row = $rows->[$r];
+ if ($max_columns < $#$row + 1) {
+ $max_columns = $#$row + 1;
+ }
+ }
+
+ # compute automatic sprintf formats
+ for ($c = 0; $c <= $#$columns; $c++) {
+ print $delimiter if ($c > 0);
+ $heading = ($headings && $headings->[$c]) ? $headings->[$c] :
$columns->[$c];
+ print $heading;
+ }
+ print "\n";
+ for ($r = 0; $r <= $#$rows; $r++) {
+ $row = $rows->[$r];
+ for ($c = 0; $c <= $#$row; $c++) {
+ print $delimiter if ($c > 0);
+ if ($quoted) {
+ if ($row->[$c] =~ /$delimiter_regexp/) {
+ print '"', $row->[$c], '"';
+ }
+ else {
+ print $row->[$c];
+ }
+ }
+ else {
+ print $row->[$c];
+ }
+ }
+ print "\n";
+ }
+ &App::sub_exit() if ($App::trace);
+}
+
sub determine_sprintf_fmt {
&App::sub_entry if ($App::trace);
my ($f) = @_;
