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';
my $sql_dir = $ENV{SCRIPT_FILENAME} || '.';
$sql_dir =~ s,/[^/]+$,,;
-my $config_path = "$sql_dir/config.pl";
-warn "# using $config_path\n";
-require $config_path if -e $config_path;
+sub require_config {
+ my $config_path = $1 if "$sql_dir/config.pl" =~ m/^(.+)$/; # untaint
+ warn "# using $config_path\n";
+ require $config_path if -e $config_path;
+}
+
+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";
-warn "# reading SQL queries from $sql_dir\n" if $debug;
+require_config;
+
+warn "SQL queries from $sql_dir\n";
opendir(DIR, $sql_dir) || die "can't opendir $sql_dir: $!";
my @sql_files = sort grep { /\.sql$/i && -f "$sql_dir/$_" } readdir(DIR);
my $date_format = $workbook->add_format(num_format => $xls_date_format);
-my $dbh = DBI->connect($dsn . $database,$user,$passwd, { RaiseError => 1, AutoCommit => 0 }) || die $DBI::errstr;
+our $dbh;
+sub use_database {
+ $dbh->disconnect if $dbh;
+ my $database = shift || return;
+ print STDERR "## connect to $database\n" if $debug;
+ $dbh = DBI->connect($dsn . $database,$user,$passwd, { RaiseError => 1, AutoCommit => 0 }) || die $DBI::errstr;
+ if ( $db_encoding ) {
+ if ( $dsn =~ m{Pg} ) {
+ $dbh->do( qq{ set client_encoding = '$db_encoding'; } );
+ } elsif ( $dsn =~ m{mysql} ) {
+ $dbh->do( qq{ set names '$db_encoding'; } );
+ } else {
+ warn "Don't know how to set encoding to $db_encoding for $dsn";
+ }
+ }
+}
+
+use_database( $database );
sub _c {
+ return shift unless $db_encoding;
return decode( $db_encoding, shift );
}
foreach my $sql_file (@sql_files) {
my $sheet_name = $sql_file;
- $sheet_name =~ s/\d+_//;
+ $sheet_name =~ s/\d+[_-]//;
$sheet_name =~ s/_/ /g;
$sheet_name =~ s/\.sql//;
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+)/) {
- $dbh->disconnect if $dbh;
- print STDERR "## connect to $1\n" if $debug;
- $dbh = DBI->connect($dsn . $1,$user,$passwd, { RaiseError => 1, AutoCommit => 0 }) || die $DBI::errstr;
+ use_database( $1 );
} 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:\S+)$/$1T$2/;
- warn "## $data\n";
- $worksheet->write_date_time( $row, $col, $data, $date_format );
- } else {
- $worksheet->write($row, $col, _c( $data ) );
- }
+ foreach my $sql ( split(/;/, $full_sql ) ) {
+
+ warn "SQL: $sql\n";
+
+ my $sth = $dbh->prepare($sql);
+ $sth->execute();
+
+ next unless $sth->{NAME} && $sth->rows > 0; # $sth->rows alone 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 {
+ $data = "'" . $data if $data =~ m/^=/; # escape data which looks like formula
+ $worksheet->write($row, $col, _c( $data ) );
+ }
+ }
+ $row++;
+ }
+
+ $row++; # separete queries by one row
+ }
}
$dbh->disconnect;
projects / SQL2XLS / blobdiff