use Encode qw/decode/;
use Data::Dump qw/dump/;
-# edit following to set defaults
our $dsn = 'DBI:Pg:dbname=';
our $database = 'template1';
our $user = 'dpavlin';
require_config;
-my $reports_path = $ENV{PATH_INFO};
+my $reports_path = $ENV{PATH_INFO} || '';
$reports_path =~ s/\.\.//g; # some protection against path exploits
$reports_path ||= shift @ARGV; # for CLI invocation
$sql_dir .= "/$reports_path" if -e "$sql_dir/$reports_path";
open(SQL,"$sql_dir/$sql_file") || die "can't open sql file '$sql_dir/$sql_file': $!";
my $comment = '';
- my $sql = "";
+ my $full_sql = "";
while(<SQL>) {
chomp;
if (/^\\c\s+(\S+)/) {
} elsif (/^--(.+)/) {
$comment.=$1;
} else {
- $sql.= ' ' . $_;
+ $full_sql.= ' ' . $_;
}
}
close(SQL);
- $sql =~ s/\s\s+/ /gs;
+ $full_sql =~ s/\s\s+/ /gs;
+ $full_sql .= ';' unless $full_sql =~ m/;\s*/s;
- print STDERR "sql: $sql\ncomment: $comment\n" if ($debug);
+ print STDERR "sql: $full_sql\ncomment: $comment\n" if ($debug);
my $row = 0;
$row+=2;
}
- my $sth = $dbh->prepare($sql);
- $sth->execute();
-
my $fmt_header = $workbook->addformat(); # Add a format
$fmt_header->set_italic();
- for(my $col=0; $col<=$#{ $sth->{NAME} }; $col++) {
- $worksheet->write($row, $col, ${ $sth->{NAME} }[$col], $fmt_header);
- }
- $row++;
-
- my @types = map { $dbh->type_info($_) ? $dbh->type_info($_)->{TYPE_NAME} : '?' } @{ $sth->{TYPE} };
-
- while (my @row = $sth->fetchrow_array() ) {
- for(my $col=0; $col<=$#row; $col++) {
- my $data = $row[$col];
- if ( $types[$col] =~ m/^date/i ) {
- $data .= 'T' if $data =~ m/^\d\d\d\d-\d\d-\d\d$/;
- $data =~ s/^(\d\d\d\d-\d\d-\d\d)\s(\d\d:\d\d:\d\d)$/$1T$2/;
- warn "## by type datetime $data\n";
- $worksheet->write_date_time( $row, $col, $data, $date_format );
- } elsif ( $data =~ s/^(\d\d\d\d-\d\d-\d\d)[\sT](\d\d:\d\d:\d\d)$/$1T$2/ ) {
- warn "## heuristic date time: $1T$2\n";
- $worksheet->write_date_time( $row, $col, "$1T$2", $date_format );
- } else {
- $worksheet->write($row, $col, _c( $data ) );
- }
+ foreach my $sql ( split(/;/, $full_sql ) ) {
+
+ warn "SQL: $sql\n" if $debug;
+
+ my $sth = $dbh->prepare($sql);
+ $sth->execute();
+
+ next unless $sth->{NAME}; # $sth->rows doesn't work for insert into with MySQL
+
+ my @types = eval {
+ map { $dbh->type_info($_) ? $dbh->type_info($_)->{TYPE_NAME} : '?' } @{ $sth->{TYPE} };
+ };
+
+ for(my $col=0; $col<=$#{ $sth->{NAME} }; $col++) {
+ $worksheet->write($row, $col, ${ $sth->{NAME} }[$col], $fmt_header);
}
$row++;
- }
+ while (my @row = $sth->fetchrow_array() ) {
+ for(my $col=0; $col<=$#row; $col++) {
+ my $data = $row[$col];
+ next unless defined $data;
+ if ( $types[$col] && $types[$col] =~ m/^date/i ) {
+ $data .= 'T' if $data =~ m/^\d\d\d\d-\d\d-\d\d$/;
+ $data =~ s/^(\d\d\d\d-\d\d-\d\d)\s(\d\d:\d\d:\d\d)$/$1T$2/;
+ warn "## by type datetime $data\n" if $debug;
+ $worksheet->write_date_time( $row, $col, $data, $date_format );
+ } elsif ( $data =~ s/^(\d\d\d\d-\d\d-\d\d)[\sT](\d\d:\d\d:\d\d)$/$1T$2/ ) {
+ warn "## heuristic date time: $1T$2\n" if $debug;
+ $worksheet->write_date_time( $row, $col, "$1T$2", $date_format );
+ } else {
+ $worksheet->write($row, $col, _c( $data ) );
+ }
+ }
+ $row++;
+ }
+
+ $row++; # separete queries by one row
+ }
}
$dbh->disconnect;