-#!/usr/bin/perl
+#!/usr/bin/perl -T
use warnings;
use strict;
=head1 USAGE
-Each file in current directory which ends in C<< *.sql >> will
+Each file in current directory which ends in C<*.sql> will
be converted to Excel sheet. If you want to have specific order, you can
prefix filenames with numbers which will be striped when creating sheet
names.
-Comments in sql files (lines beginning with --) will be placed
+Comments in sql files (lines beginning with C<-->) will be placed
in first line in bold.
To specify database on which SQL query is executed
-C<< \c database >> syntax is supported.
+C<\c database> syntax is supported.
You can also run script from command line, and it will produce
-C<< sql_reports.xls >> file.
+C<sql_reports.xls> file.
+
+If run within directory, it will use files in it to produce file.
+
+When called as CGI, directory name can be appended to name of script
+to produce report for any sub-directory within directory where
+C<sql2xls.cgi> is installed.
=head1 INSTALLATION
in Apache's virtual host configuration to get nice URLs
+To configure default database, user, password and other settings create
+C<config.pl> file in same directory in which C<sql2xls.cgi> is with something
+like this:
+
+ $dsn = 'DBI:mysql:dbname=';
+ $database = 'database';
+ $user = 'user';
+ $passwd = 'password';
+ $path = 'sql_reports.xls';
+
+ $db_encoding = 'utf-8';
+ $xls_date_format = 'dd.mm.yyyy';
+
+ $debug = 1;
+
+=head1 SECURITY
+
+There is none. Use apache auth modules if you need it.
+
=head1 AUTHOR
-Dobrica Pavlinusic, dpavlin@rot13.org
+Dobrica Pavlinusic, dpavlin@rot13.org, L<http://svn.rot13.org/index.cgi/SQL2XLS/>
=cut
use Spreadsheet::WriteExcel;
use DBI;
use CGI::Carp qw(fatalsToBrowser);
-use CGI qw(path_translated);
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';
our $debug = 1;
-my $sql_dir = path_translated || '.';
+my $sql_dir = $ENV{SCRIPT_FILENAME} || '.';
$sql_dir =~ s,/[^/]+$,,;
+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} || '';
+$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";
+
+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);
closedir DIR;
-my $config_path = "$sql_dir/config.pl";
-warn "# using $config_path\n";
-require $config_path if -e $config_path;
-
my $workbook;
if ($ENV{GATEWAY_INTERFACE} && $ENV{GATEWAY_INTERFACE} =~ m/CGI/i) {
# use as cgi script
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//;
print STDERR "working on $sql_file\n" if ($debug);
- open(SQL,$sql_file) || die "can't open sql file '$sql_file': $!";
+ 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;