git.rot13.org Git - SQL2XLS/blob - sql2xls.cgi

   1 #!/usr/bin/perl -T
   2 use warnings;
   3 use strict;
   4
   5 =head1 NAME
   6
   7 sql2xls.pl - convert sql queries on file system to Excel file
   8
   9 =head1 USAGE
  10
  11 Each file in current directory which ends in C<*.sql> will
  12 be converted to Excel sheet. If you want to have specific order, you can
  13 prefix filenames with numbers which will be striped when creating sheet
  14 names.
  15
  16 Comments in sql files (lines beginning with C<-->) will be placed
  17 in first line in bold.
  18
  19 To specify database on which SQL query is executed
  20 C<\c database> syntax is supported.
  21
  22 You can also run script from command line, and it will produce
  23 C<sql_reports.xls> file.
  24
  25 If run within directory, it will use files in it to produce file.
  26
  27 When called as CGI, directory name can be appended to name of script
  28 to produce report for any sub-directory within directory where
  29 C<sql2xls.cgi> is installed.
  30
  31 =head1 INSTALLATION
  32
  33 Only required file is this script C<< sql2xls.cgi >>
  34
  35 If your server is configured to execute C<.cgi> files, you can
  36 drop this script anywhere, but you can also add something like
  37
  38    ScriptAlias /xls-reports /srv/SQL2XLS/sql2xls.cgi
  39
  40 in Apache's virtual host configuration to get nice URLs
  41
  42 To configure default database, user, password and other settings create
  43 C<config.pl> file in same directory in which C<sql2xls.cgi> is with something
  44 like this:
  45
  46   $dsn      = 'DBI:mysql:dbname=';
  47   $database = 'database';
  48   $user     = 'user';
  49   $passwd   = 'password';
  50   $path     = 'sql_reports.xls';
  51
  52   $db_encoding     = 'utf-8';
  53   $xls_date_format = 'dd.mm.yyyy';
  54
  55   $debug = 1;
  56
  57 =head1 SECURITY
  58
  59 There is none. Use apache auth modules if you need it.
  60
  61 =head1 AUTHOR
  62
  63 Dobrica Pavlinusic, dpavlin@rot13.org, L<http://svn.rot13.org/index.cgi/SQL2XLS/>
  64
  65 =cut
  66
  67 use Spreadsheet::WriteExcel;
  68 use DBI;
  69 use CGI::Carp qw(fatalsToBrowser);
  70 use Encode qw/decode/;
  71 use Data::Dump qw/dump/;
  72
  73 our $dsn      = 'DBI:Pg:dbname=';
  74 our $database = 'template1';
  75 our $user     = 'dpavlin';
  76 our $passwd   = '';
  77 our $path     = 'sql_reports.xls';
  78
  79 our $db_encoding     = 'iso-8859-2';
  80 our $xls_date_format = 'dd.mm.yyyy';
  81
  82 our $debug = 1;
  83
  84 my $sql_dir = $ENV{SCRIPT_FILENAME} || '.';
  85 $sql_dir =~ s,/[^/]+$,,;
  86
  87 sub require_config {
  88         my $config_path = $1 if "$sql_dir/config.pl" =~ m/^(.+)$/; # untaint
  89         warn "# using $config_path\n";
  90         require $config_path if -e $config_path;
  91 }
  92
  93 require_config;
  94
  95 my $reports_path = $ENV{PATH_INFO} || '';
  96 $reports_path =~ s/\.\.//g; # some protection against path exploits
  97 $reports_path ||= shift @ARGV; # for CLI invocation
  98 $sql_dir .= "/$reports_path" if -e "$sql_dir/$reports_path";
  99
 100 require_config;
 101
 102 warn "SQL queries from $sql_dir\n";
 103
 104 opendir(DIR, $sql_dir) || die "can't opendir $sql_dir: $!";
 105 my @sql_files = sort grep { /\.sql$/i && -f "$sql_dir/$_" } readdir(DIR);
 106 closedir DIR;
 107
 108 my $workbook;
 109 if ($ENV{GATEWAY_INTERFACE} && $ENV{GATEWAY_INTERFACE} =~ m/CGI/i) {
 110         # use as cgi script
 111         print "Content-type: application/vnd.ms-excel\n\n";
 112         $workbook = Spreadsheet::WriteExcel->new("-");
 113 } else {
 114         # Create a new Excel workbook
 115         $workbook = Spreadsheet::WriteExcel->new( $path );
 116         warn "Creating XLS file $path\n";
 117 }
 118
 119 my $date_format = $workbook->add_format(num_format => $xls_date_format);
 120
 121 our $dbh;
 122 sub use_database {
 123         $dbh->disconnect if $dbh;
 124         my $database = shift || return;
 125         print STDERR "## connect to $database\n" if $debug;
 126         $dbh = DBI->connect($dsn . $database,$user,$passwd, { RaiseError => 1, AutoCommit => 0 }) || die $DBI::errstr;
 127         if ( $db_encoding ) {
 128                 if ( $dsn =~ m{Pg} ) {
 129                         $dbh->do( qq{ set client_encoding = '$db_encoding'; } );
 130                 } elsif ( $dsn =~ m{mysql} ) {
 131                         $dbh->do( qq{ set names '$db_encoding'; } );
 132                 } else {
 133                         warn "Don't know how to set encoding to $db_encoding for $dsn";
 134                 }
 135         }
 136 }
 137
 138 use_database( $database );
 139
 140 sub _c {
 141         return shift unless $db_encoding;
 142         return decode( $db_encoding, shift );
 143 }
 144
 145 foreach my $sql_file (@sql_files) {
 146
 147         my $sheet_name = $sql_file;
 148         $sheet_name =~ s/\d+[_-]//;
 149         $sheet_name =~ s/_/ /g;
 150         $sheet_name =~ s/\.sql//;
 151
 152         # Add a worksheet
 153         warn "# clipping sheet name '$sheet_name' to 31 char limit\n" if length $sheet_name > 31;
 154         my $worksheet = $workbook->addworksheet( substr($sheet_name,0,31) );
 155
 156         print STDERR "working on $sql_file\n" if ($debug);
 157
 158         open(SQL,"$sql_dir/$sql_file") || die "can't open sql file '$sql_dir/$sql_file': $!";
 159         my $comment = '';
 160         my $full_sql = "";
 161         while(<SQL>) {
 162                 chomp;
 163                 if (/^\\c\s+(\S+)/) {
 164                         use_database( $1 );
 165                 } elsif (/^--(.+)/) {
 166                         $comment.=$1;
 167                 } else {
 168                         $full_sql.= ' ' . $_;
 169                 }
 170         }
 171         close(SQL);
 172
 173         $full_sql =~ s/\s\s+/ /gs;
 174         $full_sql .= ';' unless $full_sql =~ m/;\s*/s;
 175
 176         print STDERR "sql: $full_sql\ncomment: $comment\n" if ($debug);
 177
 178         my $row = 0;
 179
 180         if ($comment) {
 181
 182                 #  Add and define a format
 183                 my $fmt_comment = $workbook->addformat();    # Add a format
 184                 $fmt_comment->set_bold();
 185
 186                 $comment =~ s/^\s+//;
 187                 $comment =~ s/\s+$//;
 188
 189                 $worksheet->write($row, 0, _c($comment), $fmt_comment);
 190                 $row+=2;
 191         }
 192
 193         my $fmt_header = $workbook->addformat();    # Add a format
 194         $fmt_header->set_italic();
 195
 196         foreach my $sql ( split(/;/, $full_sql ) ) {
 197
 198                 warn "SQL: $sql\n";
 199
 200                 my $sth = $dbh->prepare($sql);
 201                 $sth->execute();
 202
 203                 next unless $sth->{NAME} && $sth->rows > 0; # $sth->rows alone doesn't work for insert into with MySQL
 204
 205                 my @types = eval {
 206                         map { $dbh->type_info($_) ? $dbh->type_info($_)->{TYPE_NAME} : '?' } @{ $sth->{TYPE} };
 207                 };
 208
 209                 for(my $col=0; $col<=$#{ $sth->{NAME} }; $col++) {
 210                         $worksheet->write($row, $col, ${ $sth->{NAME} }[$col], $fmt_header);
 211                 }
 212                 $row++;
 213
 214                 while (my @row = $sth->fetchrow_array() ) {
 215                         for(my $col=0; $col<=$#row; $col++) {
 216                                 my $data = $row[$col];
 217                                 next unless defined $data;
 218                                 if ( $types[$col] && $types[$col] =~ m/^date/i ) {
 219                                         $data .= 'T' if $data =~ m/^\d\d\d\d-\d\d-\d\d$/;
 220                                         $data =~ s/^(\d\d\d\d-\d\d-\d\d)\s(\d\d:\d\d:\d\d)$/$1T$2/;
 221                                         warn "## by type datetime $data\n" if $debug;
 222                                         $worksheet->write_date_time( $row, $col, $data, $date_format );
 223                                 } elsif ( $data =~ s/^(\d\d\d\d-\d\d-\d\d)[\sT](\d\d:\d\d:\d\d)$/$1T$2/ ) {
 224                                         warn "## heuristic date time: $1T$2\n" if $debug;
 225                                         $worksheet->write_date_time( $row, $col, "$1T$2", $date_format );
 226                                 } else {
 227                                         $data = "'" . $data if $data =~ m/^=/; # escape data which looks like formula
 228                                         $worksheet->write($row, $col, _c( $data ) );
 229                                 }
 230                         }
 231                         $row++;
 232                 }
 233
 234                 $row++; # separete queries by one row
 235         }
 236 }
 237
 238 $dbh->disconnect;
 239
 240 1;
 241
 242 __END__
 243