/[SQL2XLS]/sql2xls.cgi
This is repository of my old source code which isn't updated any more. Go to git.rot13.org for current projects!
ViewVC logotype

Diff of /sql2xls.cgi

Parent Directory Parent Directory | Revision Log Revision Log | View Patch Patch

revision 3 by dpavlin, Mon Mar 24 16:35:29 2008 UTC revision 23 by dpavlin, Thu Nov 6 10:49:02 2008 UTC
# Line 1  Line 1 
1  #!/usr/bin/perl -w  #!/usr/bin/perl -T
2  #  use warnings;
 # convert sql queries on file system to excel file (one query per  
 # sheet)  
 #  
 # Dobrica Pavlinsic <dpavlin@rot13.org>, 2002-04-10  
   
3  use strict;  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;  use Spreadsheet::WriteExcel;
68  use DBI;  use DBI;
69  use CGI::Carp qw(fatalsToBrowser);  use CGI::Carp qw(fatalsToBrowser);
 use CGI qw(path_translated);  
70  use Encode qw/decode/;  use Encode qw/decode/;
71  use Data::Dump qw/dump/;  use Data::Dump qw/dump/;
72    
73  # Connect to DB  our $dsn      = 'DBI:Pg:dbname=';
74  my $connect = "DBI:Pg:dbname=new";  our $database = 'template1';
75  my $user = "web";  our $user     = 'dpavlin';
76  my $passwd = "";  our $passwd   = '';
77    our $path     = 'sql_reports.xls';
78    
79  my $db_encoding = 'iso-8859-2';  our $db_encoding     = 'iso-8859-2';
80  my $xls_date_format = 'dd.mm.yyyy';  our $xls_date_format = 'dd.mm.yyyy';
81    
82  my $debug = 1;  our $debug = 1;
83    
84  my $sql_dir = path_translated || '.';  my $sql_dir = $ENV{SCRIPT_FILENAME} || '.';
85  $sql_dir =~ s,/[^/]+$,,;  $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 "# reading SQL queries from $sql_dir\n" if $debug;
103    
104  opendir(DIR, $sql_dir) || die "can't opendir $sql_dir: $!";  opendir(DIR, $sql_dir) || die "can't opendir $sql_dir: $!";
105  my @sql_files = sort grep { /\.sql$/i && -f "$sql_dir/$_" } readdir(DIR);  my @sql_files = sort grep { /\.sql$/i && -f "$sql_dir/$_" } readdir(DIR);
106  closedir DIR;  closedir DIR;
107    
108  my $workbook;  my $workbook;
109  if ($0 =~ m/\.cgi$/i) {  if ($ENV{GATEWAY_INTERFACE} && $ENV{GATEWAY_INTERFACE} =~ m/CGI/i) {
110          # use as cgi script          # use as cgi script
111          print "Content-type: application/vnd.ms-excel\n\n";          print "Content-type: application/vnd.ms-excel\n\n";
112          $workbook = Spreadsheet::WriteExcel->new("-");          $workbook = Spreadsheet::WriteExcel->new("-");
113  } else {  } else {
114          # Create a new Excel workbook          # Create a new Excel workbook
115          $workbook = Spreadsheet::WriteExcel->new("sql_result.xls");          $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);  my $date_format = $workbook->add_format(num_format => $xls_date_format);
120    
121  my $dbh = DBI->connect($connect,$user,$passwd) || die $DBI::errstr;  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 {  sub _c {
141            return shift unless $db_encoding;
142          return decode( $db_encoding, shift );          return decode( $db_encoding, shift );
143  }  }
144    
145  foreach my $sql_file (@sql_files) {  foreach my $sql_file (@sql_files) {
146    
147          my $sheet_name = $sql_file;          my $sheet_name = $sql_file;
148          $sheet_name =~ s/\d+_//;          $sheet_name =~ s/\d+[_-]//;
149          $sheet_name =~ s/_/ /g;          $sheet_name =~ s/_/ /g;
150          $sheet_name =~ s/\.sql//;          $sheet_name =~ s/\.sql//;
151    
152          # Add a worksheet          # Add a worksheet
153          my $worksheet = $workbook->addworksheet($sheet_name);          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);          print STDERR "working on $sql_file\n" if ($debug);
157    
158          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': $!";
159          my $comment;          my $comment = '';
160          my $sql = "";          my $full_sql = "";
161          while(<SQL>) {          while(<SQL>) {
162                  chomp;                  chomp;
163                  if (/^--(.+)/) {                  if (/^\\c\s+(\S+)/) {
164                            use_database( $1 );
165                    } elsif (/^--(.+)/) {
166                          $comment.=$1;                          $comment.=$1;
167                  } else {                  } else {
168                          $sql.=$_;                          $full_sql.= ' ' . $_;
169                  }                  }
170          }          }
171          close(SQL);          close(SQL);
172    
173          print STDERR "sql: $sql\ncomment: $comment\n" if ($debug);          $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;          my $row = 0;
179    
# Line 83  foreach my $sql_file (@sql_files) { Line 183  foreach my $sql_file (@sql_files) {
183                  my $fmt_comment = $workbook->addformat();    # Add a format                  my $fmt_comment = $workbook->addformat();    # Add a format
184                  $fmt_comment->set_bold();                  $fmt_comment->set_bold();
185    
186                    $comment =~ s/^\s+//;
187                    $comment =~ s/\s+$//;
188    
189                  $worksheet->write($row, 0, _c($comment), $fmt_comment);                  $worksheet->write($row, 0, _c($comment), $fmt_comment);
190                  $row+=2;                  $row+=2;
191          }          }
192    
         my $sth = $dbh->prepare($sql) || die $dbh->errstr();  
         $sth->execute() || die $sth->errstr();  
   
193          my $fmt_header = $workbook->addformat();    # Add a format          my $fmt_header = $workbook->addformat();    # Add a format
194          $fmt_header->set_italic();          $fmt_header->set_italic();
195    
196          for(my $col=0; $col<=$#{ $sth->{NAME} }; $col++) {          foreach my $sql ( split(/;/, $full_sql ) ) {
                 $worksheet->write($row, $col, ${ $sth->{NAME} }[$col], $fmt_header);  
         }  
         $row++;  
197    
198          my @types = map { scalar $dbh->type_info($_)->{TYPE_NAME} } @{ $sth->{TYPE} };                  warn "SQL: $sql\n" if $debug;
199    
200          while (my @row = $sth->fetchrow_array() ) {                  my $sth = $dbh->prepare($sql);
201                  for(my $col=0; $col<=$#row; $col++) {                  $sth->execute();
202                          my $data = $row[$col];  
203                          if ( $types[$col] =~ m/^date/i ) {                  next unless $sth->{NAME}; # $sth->rows doesn't work for insert into with MySQL
204                                  $data .= 'T' if $data =~ m/^\d\d\d\d-\d\d-\d\d$/;  
205                                  $worksheet->write_date_time( $row, $col, $data, $date_format );                  my @types = eval {
206                          } else {                          map { $dbh->type_info($_) ? $dbh->type_info($_)->{TYPE_NAME} : '?' } @{ $sth->{TYPE} };
207                                  $worksheet->write($row, $col, _c( $data ) );                  };
208                          }  
209                    for(my $col=0; $col<=$#{ $sth->{NAME} }; $col++) {
210                            $worksheet->write($row, $col, ${ $sth->{NAME} }[$col], $fmt_header);
211                  }                  }
212                  $row++;                  $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                                            $worksheet->write($row, $col, _c( $data ) );
228                                    }
229                            }
230                            $row++;
231                    }
232    
233                    $row++; # separete queries by one row
234            }
235  }  }
236    
237  $dbh->disconnect;  $dbh->disconnect;
# Line 121  $dbh->disconnect; Line 240  $dbh->disconnect;
240    
241  __END__  __END__
242    
 =head1 NAME  
   
 sql2xls.pl - convert sql queries on file system to excel file  
   
 =head1 USAGE  
   
 Edit top of script and edit @sql_files array which describes  
 files which are going to be loaded and executed producing  
 Excel sheets (one sheet per file)  
   
 Comments in sql files (lines beginning with --) will be placed  
 in outout sheet on top in bold.  
   
 Sheet will have name same as sql file.  
   
 Run script and examine b<sql_result.xls> file.  
   
 =head1 AUTHOR  
   
 Dobrica Pavlinusic, dpavlin@rot13.org  
   
Legend:
Removed from v.3  
changed lines
  Added in v.23
  ViewVC Help
Powered by ViewVC 1.1.26