Author: spadkins
Date: Mon Sep 21 12:15:52 2009
New Revision: 13369
Added:
p5ee/trunk/App-Repository/bin/dbsql (contents, props changed)
Log:
new
Added: p5ee/trunk/App-Repository/bin/dbsql
==============================================================================
--- (empty file)
+++ p5ee/trunk/App-Repository/bin/dbsql Mon Sep 21 12:15:52 2009
@@ -0,0 +1,173 @@
+#!/usr/local/bin/perl -w
+
+use Date::Format;
+
+use App::Options (
+ options => [ qw(dbhost dbname dbuser dbpass repository table compact
decimals verbose) ],
+ option => {
+ repository => {
+ default => "default",
+ description => "Name of the repository to get the rows from",
+ },
+ table => {
+ description => "Specify the table for formatting purposes (if not
discernible from the query)",
+ },
+ compact => {
+ description => "Trim titles to make compact table",
+ },
+ decimals => {
+ description => "Number of decimal places to print on floats",
+ default => "2",
+ },
+ verbose => {
+ default => 1,
+ description => "Verbose level",
+ },
+ },
+);
+
+use App;
+use strict;
+
+$| = 1; # autoflush stdout
+
+{
+ my $context = App->context();
+ my $db = $context->repository($App::options{repository});
+ my $verbose = $App::options{verbose};
+ my $columns = [];
+ my $get_options = { columns => $columns };
+
+ local($/) = undef;
+ my $sql = <>;
+
+ my $rows = $db->_do($sql, $get_options);
+
+ my ($table);
+ if ($sql =~ /\bfrom\s+(\w+)/is) {
+ $table = $1;
+ }
+ else {
+ $table = $App::options{table};
+ }
+ die "Could not determine what table this query is for and it wasn't given
on the command line\n" if (!$table);
+
+ my $formats = [];
+ &print_table($rows, $columns, $formats, { compact =>
$App::options{compact}, });
+}
+
+sub print_table {
+ &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};
+
+ # 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 < $max_columns; $c++) {
+
+ if (! defined $autoformat[$c]) {
+ $autoformat[$c] = {
+ max_length => 0,
+ type => 2, # 0=string, 1=float, 2=integer
+ min => undef,
+ max => undef,
+ };
+ }
+ $f = $autoformat[$c];
+
+ # set the length of the column by the length of its heading
+ $heading = ($headings && $headings->[$c]) ? $headings->[$c] : "";
+ if ($heading) {
+ $len = length($heading);
+ if ($len > $f->{max_length}) {
+ $f->{max_length} = $len;
+ }
+ }
+ elsif (! $options->{compact}) {
+ $len = length($columns->[$c]);
+ if ($len > $f->{max_length}) {
+ $f->{max_length} = $len;
+ }
+ }
+
+ for ($r = 0; $r <= $#$rows; $r++) {
+ $row = $rows->[$r];
+ if ($c <= $#$row && defined $row->[$c]) {
+ $elem = $row->[$c];
+ $len = length($elem);
+ if ($elem =~ /^-?[0-9]*\.[0-9]+$/) { # float
+ $len = length(sprintf("%.$App::options{decimals}f",$elem));
+ $f->{type} = 1 if ($f->{type} > 1);
+ if (!defined $f->{min} || $elem < $f->{min}) {
+ $f->{min} = $elem;
+ }
+ if (!defined $f->{max} || $elem < $f->{max}) {
+ $f->{max} = $elem;
+ }
+ }
+ elsif ($elem =~ /^-?[0-9]+$/) { # integer
+ if (!defined $f->{min} || $elem < $f->{min}) {
+ $f->{min} = $elem;
+ }
+ if (!defined $f->{max} || $elem < $f->{max}) {
+ $f->{max} = $elem;
+ }
+ }
+ else {
+ $f->{type} = 0;
+ }
+ $f->{max_length} = $len if ($len > $f->{max_length});
+ }
+ }
+ &determine_sprintf_fmt($f);
+ }
+ for ($c = 0; $c <= $#$columns; $c++) {
+ $format = $autoformat[$c]->{title_fmt} || "%s";
+ print " " if ($c > 0);
+ $heading = ($headings && $headings->[$c]) ? $headings->[$c] :
$columns->[$c];
+ printf($format, $heading);
+ }
+ print "\n";
+ for ($r = 0; $r <= $#$rows; $r++) {
+ $row = $rows->[$r];
+ for ($c = 0; $c <= $#$row; $c++) {
+ $format = $autoformat[$c]->{fmt} || "%s";
+ print " " if ($c > 0);
+ printf($format, $row->[$c]);
+ }
+ print "\n";
+ }
+ &App::sub_exit() if ($App::trace);
+}
+
+sub determine_sprintf_fmt {
+ &App::sub_entry if ($App::trace);
+ my ($f) = @_;
+ my ($width, $int_len, $fract_len);
+ if ($f->{type} == 1) { # float
+ $f->{title_fmt} = "%$f->{max_length}.$f->{max_length}s";
+ $f->{fmt} = "%$f->{max_length}.$App::options{decimals}f";
+ }
+ elsif ($f->{type} == 2) { # integer
+ $f->{title_fmt} = "%$f->{max_length}.$f->{max_length}s";
+ $f->{fmt} = "%$f->{max_length}d";
+ }
+ else { # string
+ $f->{title_fmt} = "%-$f->{max_length}.$f->{max_length}s";
+ $f->{fmt} = "%-$f->{max_length}s";
+ }
+ &App::sub_exit($f->{fmt}) if ($App::trace);
+}
+
+exit (0);
+
