/[SQL2XLS]/sql2xlsx.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 /sql2xlsx.cgi

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

revision 13 by dpavlin, Mon Nov 3 20:32:44 2008 UTC revision 22 by dpavlin, Thu Nov 6 10:37:17 2008 UTC
# Line 8  sql2xls.pl - convert sql queries on file Line 8  sql2xls.pl - convert sql queries on file
8    
9  =head1 USAGE  =head1 USAGE
10    
11  Each file in current directory which ends in C<< *.sql >> will  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  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  prefix filenames with numbers which will be striped when creating sheet
14  names.  names.
15    
16  Comments in sql files (lines beginning with --) will be placed  Comments in sql files (lines beginning with C<-->) will be placed
17  in first line in bold.  in first line in bold.
18    
19  To specify database on which SQL query is executed  To specify database on which SQL query is executed
20  C<< \c database >> syntax is supported.  C<\c database> syntax is supported.
21    
22  You can also run script from command line, and it will produce  You can also run script from command line, and it will produce
23  C<< sql_reports.xls >> file.  C<sql_reports.xls> file.
24    
25  If run within directory, it will use files in it to produce file.  If run within directory, it will use files in it to produce file.
26    
# Line 39  drop this script anywhere, but you can a Line 39  drop this script anywhere, but you can a
39    
40  in Apache's virtual host configuration to get nice URLs  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  =head1 AUTHOR
62    
63  Dobrica Pavlinusic, dpavlin@rot13.org  Dobrica Pavlinusic, dpavlin@rot13.org, L<http://svn.rot13.org/index.cgi/SQL2XLS/>
64    
65  =cut  =cut
66    
# Line 51  use CGI::Carp qw(fatalsToBrowser); Line 70  use CGI::Carp qw(fatalsToBrowser);
70  use Encode qw/decode/;  use Encode qw/decode/;
71  use Data::Dump qw/dump/;  use Data::Dump qw/dump/;
72    
 # edit following to set defaults  
73  our $dsn      = 'DBI:Pg:dbname=';  our $dsn      = 'DBI:Pg:dbname=';
74  our $database = 'template1';  our $database = 'template1';
75  our $user     = 'dpavlin';  our $user     = 'dpavlin';
# Line 66  our $debug = 1; Line 84  our $debug = 1;
84  my $sql_dir = $ENV{SCRIPT_FILENAME} || '.';  my $sql_dir = $ENV{SCRIPT_FILENAME} || '.';
85  $sql_dir =~ s,/[^/]+$,,;  $sql_dir =~ s,/[^/]+$,,;
86    
87  my $config_path = "$sql_dir/config.pl";  sub require_config {
88  warn "# using $config_path\n";          my $config_path = $1 if "$sql_dir/config.pl" =~ m/^(.+)$/; # untaint
89  require $config_path if -e $config_path;          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};  my $reports_path = $ENV{PATH_INFO} || '';
96  $reports_path =~ s/\.\.//g; # some protection against path exploits  $reports_path =~ s/\.\.//g; # some protection against path exploits
97  $reports_path ||= shift @ARGV; # for CLI invocation  $reports_path ||= shift @ARGV; # for CLI invocation
98  $sql_dir .= "/$reports_path" if -e "$sql_dir/$reports_path";  $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;  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: $!";
# Line 94  if ($ENV{GATEWAY_INTERFACE} && $ENV{GATE Line 118  if ($ENV{GATEWAY_INTERFACE} && $ENV{GATE
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($dsn . $database,$user,$passwd, { RaiseError => 1, AutoCommit => 0 }) || 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            $dbh->do( qq{ set names '$db_encoding'; } ) if $db_encoding && $dsn =~ m{mysql};
128    }
129    
130    use_database( $database );
131    
132  sub _c {  sub _c {
133            return shift unless $db_encoding;
134          return decode( $db_encoding, shift );          return decode( $db_encoding, shift );
135  }  }
136    
137  foreach my $sql_file (@sql_files) {  foreach my $sql_file (@sql_files) {
138    
139          my $sheet_name = $sql_file;          my $sheet_name = $sql_file;
140          $sheet_name =~ s/\d+_//;          $sheet_name =~ s/\d+[_-]//;
141          $sheet_name =~ s/_/ /g;          $sheet_name =~ s/_/ /g;
142          $sheet_name =~ s/\.sql//;          $sheet_name =~ s/\.sql//;
143    
# Line 115  foreach my $sql_file (@sql_files) { Line 149  foreach my $sql_file (@sql_files) {
149    
150          open(SQL,"$sql_dir/$sql_file") || die "can't open sql file '$sql_dir/$sql_file': $!";          open(SQL,"$sql_dir/$sql_file") || die "can't open sql file '$sql_dir/$sql_file': $!";
151          my $comment = '';          my $comment = '';
152          my $sql = "";          my $full_sql = "";
153          while(<SQL>) {          while(<SQL>) {
154                  chomp;                  chomp;
155                  if (/^\\c\s+(\S+)/) {                  if (/^\\c\s+(\S+)/) {
156                          $dbh->disconnect if $dbh;                          use_database( $1 );
                         print STDERR "## connect to $1\n" if $debug;  
                         $dbh = DBI->connect($dsn . $1,$user,$passwd, { RaiseError => 1, AutoCommit => 0 }) || die $DBI::errstr;  
157                  } elsif (/^--(.+)/) {                  } elsif (/^--(.+)/) {
158                          $comment.=$1;                          $comment.=$1;
159                  } else {                  } else {
160                          $sql.= ' ' . $_;                          $full_sql.= ' ' . $_;
161                  }                  }
162          }          }
163          close(SQL);          close(SQL);
164    
165          $sql =~ s/\s\s+/ /gs;          $full_sql =~ s/\s\s+/ /gs;
166            $full_sql .= ';' unless $full_sql =~ m/;\s*/s;
167    
168          print STDERR "sql: $sql\ncomment: $comment\n" if ($debug);          print STDERR "sql: $full_sql\ncomment: $comment\n" if ($debug);
169    
170          my $row = 0;          my $row = 0;
171    
# Line 149  foreach my $sql_file (@sql_files) { Line 182  foreach my $sql_file (@sql_files) {
182                  $row+=2;                  $row+=2;
183          }          }
184    
         my $sth = $dbh->prepare($sql);  
         $sth->execute();  
   
185          my $fmt_header = $workbook->addformat();    # Add a format          my $fmt_header = $workbook->addformat();    # Add a format
186          $fmt_header->set_italic();          $fmt_header->set_italic();
187    
188          for(my $col=0; $col<=$#{ $sth->{NAME} }; $col++) {          foreach my $sql ( split(/;/, $full_sql ) ) {
                 $worksheet->write($row, $col, ${ $sth->{NAME} }[$col], $fmt_header);  
         }  
         $row++;  
189    
190          my @types = map { $dbh->type_info($_) ? $dbh->type_info($_)->{TYPE_NAME} : '?' } @{ $sth->{TYPE} };                  warn "SQL: $sql\n" if $debug;
191    
192          while (my @row = $sth->fetchrow_array() ) {                  my $sth = $dbh->prepare($sql);
193                  for(my $col=0; $col<=$#row; $col++) {                  $sth->execute();
194                          my $data = $row[$col];  
195                          if ( $types[$col] =~ m/^date/i ) {                  next unless $sth->{NAME}; # $sth->rows doesn't work for insert into with MySQL
196                                  $data .= 'T' if $data =~ m/^\d\d\d\d-\d\d-\d\d$/;  
197                                  $data =~ s/^(\d\d\d\d-\d\d-\d\d)\s(\d\d:\S+)$/$1T$2/;                  my @types = eval {
198                                  warn "## $data\n";                          map { $dbh->type_info($_) ? $dbh->type_info($_)->{TYPE_NAME} : '?' } @{ $sth->{TYPE} };
199                                  $worksheet->write_date_time( $row, $col, $data, $date_format );                  };
200                          } else {  
201                                  $worksheet->write($row, $col, _c( $data ) );                  for(my $col=0; $col<=$#{ $sth->{NAME} }; $col++) {
202                          }                          $worksheet->write($row, $col, ${ $sth->{NAME} }[$col], $fmt_header);
203                  }                  }
204                  $row++;                  $row++;
         }  
205    
206                    while (my @row = $sth->fetchrow_array() ) {
207                            for(my $col=0; $col<=$#row; $col++) {
208                                    my $data = $row[$col];
209                                    next unless defined $data;
210                                    if ( $types[$col] && $types[$col] =~ m/^date/i ) {
211                                            $data .= 'T' if $data =~ m/^\d\d\d\d-\d\d-\d\d$/;
212                                            $data =~ s/^(\d\d\d\d-\d\d-\d\d)\s(\d\d:\d\d:\d\d)$/$1T$2/;
213                                            warn "## by type datetime $data\n" if $debug;
214                                            $worksheet->write_date_time( $row, $col, $data, $date_format );
215                                    } elsif ( $data =~ s/^(\d\d\d\d-\d\d-\d\d)[\sT](\d\d:\d\d:\d\d)$/$1T$2/ ) {
216                                            warn "## heuristic date time: $1T$2\n" if $debug;
217                                            $worksheet->write_date_time( $row, $col, "$1T$2", $date_format );
218                                    } else {
219                                            $worksheet->write($row, $col, _c( $data ) );
220                                    }
221                            }
222                            $row++;
223                    }
224    
225                    $row++; # separete queries by one row
226            }
227  }  }
228    
229  $dbh->disconnect;  $dbh->disconnect;
Legend:
Removed from v.13  
changed lines
  Added in v.22
  ViewVC Help
Powered by ViewVC 1.1.26