/[SQL2RSS]/sql2rss.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

Annotation of /sql2rss.cgi

Parent Directory Parent Directory | Revision Log Revision Log


Revision 1 - (hide annotations)
Thu Sep 17 19:44:49 2009 UTC (14 years, 6 months ago) by dpavlin
File size: 4538 byte(s)
first version of SQL to RSS converted modeled after SQL2XML

1 dpavlin 1 #!/usr/bin/perl -T
2     use warnings;
3     use strict;
4    
5     =head1 NAME
6    
7     sql2rss.cgi - convert sql queries on file system to RSS feed
8    
9     =head1 USAGE
10    
11     Each file in current directory which ends in C<*.sql> will
12     be converted rss item. 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     =head1 INSTALLATION
23    
24     If your server is configured to execute C<.cgi> files, you can
25     drop this script anywhere, but you can also add something like
26    
27     ScriptAlias /xls-reports /srv/SQL2XLS/sql2xls.cgi
28    
29     in Apache's virtual host configuration to get nice URLs
30    
31     To configure default database, user, password and other settings create
32     C<config.pl> file in same directory in which C<sql2xls.cgi> is with something
33     like this:
34    
35     $dsn = 'DBI:mysql:dbname=';
36     $database = 'database';
37     $user = 'user';
38     $passwd = 'password';
39    
40     $db_encoding = 'utf-8';
41    
42     $debug = 1;
43    
44     =head1 SECURITY
45    
46     There is none. Use apache auth modules if you need it.
47    
48     =head1 AUTHOR
49    
50     Dobrica Pavlinusic, dpavlin@rot13.org, L<http://svn.rot13.org/index.cgi/SQL2RSS/>
51    
52     =cut
53    
54     use XML::FeedPP;
55     use DBI;
56     use CGI::Carp qw(fatalsToBrowser);
57     use Data::Dump qw/dump/;
58    
59     warn dump( \%ENV );
60    
61     our $dsn = 'DBI:Pg:dbname=';
62     our $database = 'template1';
63     our $user = 'dpavlin';
64     our $passwd = '';
65    
66     our $db_encoding;
67    
68     our $debug = 1;
69    
70    
71     my $sql_dir = $ENV{SCRIPT_FILENAME} || '.';
72     $sql_dir =~ s,/[^/]+$,,;
73    
74     my $reports_path = $ENV{PATH_INFO} || '';
75     $reports_path =~ s/\.\.//g; # some protection against path exploits
76     $reports_path ||= shift @ARGV; # for CLI invocation
77    
78    
79     sub require_config {
80     my $config_path = $1 if "$sql_dir/config.pl" =~ m/^(.+)$/; # untaint
81     warn "# using $config_path\n";
82     require $config_path if -e $config_path;
83     }
84    
85     require_config;
86    
87    
88     my @sql_files;
89    
90     if ( -d "$sql_dir/$reports_path" ) {
91     $sql_dir .= '/' . $reports_path;
92     warn "SQL queries from $sql_dir\n";
93     opendir(DIR, $sql_dir) || die "can't opendir $sql_dir: $!";
94     @sql_files = sort grep { /\.sql$/i && -f "$sql_dir/$_" } readdir(DIR);
95     closedir DIR;
96     } elsif ( -f "$sql_dir/$reports_path" ) {
97     @sql_files = ( $reports_path );
98     }
99    
100     my $url = "http://$ENV{HTTP_HOST}/$ENV{REQUEST_URI}";
101     print qq|Content-type: application/rss+xml\n\n|;
102    
103     my $feed = XML::FeedPP::RSS->new();
104     $feed->title( $reports_path );
105     $feed->link( $url );
106     #$feed->pubDate( "Thu, 23 Feb 2006 14:43:43 +0900" );
107    
108     our $dbh;
109     sub use_database {
110     $dbh->disconnect if $dbh;
111     my $database = shift || return;
112     print STDERR "## connect to $database\n" if $debug;
113     $dbh = DBI->connect($dsn . $database,$user,$passwd, { RaiseError => 1, AutoCommit => 0 }) || die $DBI::errstr;
114     if ( 0 && $db_encoding ) { # FIXME
115     if ( $dsn =~ m{Pg} ) {
116     $dbh->do( qq{ set client_encoding = '$db_encoding'; } );
117     } elsif ( $dsn =~ m{mysql} ) {
118     $dbh->do( qq{ set names '$db_encoding'; } );
119     } else {
120     warn "Don't know how to set encoding to $db_encoding for $dsn";
121     }
122     }
123     }
124    
125     use_database( $database );
126    
127     sub table_row {
128     my $type = shift @_;
129     my $html = join('', map { qq|<$type>$_</$type>| } @_ );
130     return qq|<tr>$html</tr>|;
131     }
132    
133     foreach my $sql_file (@sql_files) {
134    
135     print STDERR "working on $sql_file\n" if ($debug);
136    
137     open(SQL,"$sql_dir/$sql_file") || die "can't open sql file '$sql_dir/$sql_file': $!";
138     my $comment = '';
139     my $full_sql = "";
140     while(<SQL>) {
141     chomp;
142     if (/^\\c\s+(\S+)/) {
143     use_database( $1 );
144     } elsif (/^--(.+)/) {
145     $comment.=$1;
146     } else {
147     $full_sql.= ' ' . $_;
148     }
149     }
150     close(SQL);
151    
152     $full_sql =~ s/\s\s+/ /gs;
153     $full_sql .= ';' unless $full_sql =~ m/;\s*/s;
154    
155     print STDERR "sql: $full_sql\ncomment: $comment\n" if ($debug);
156    
157     foreach my $sql ( split(/;/, $full_sql ) ) {
158    
159     warn "SQL: $sql\n";
160    
161     my $sth = $dbh->prepare($sql);
162     $sth->execute();
163    
164     next unless $sth->{NAME} && $sth->rows > 0; # $sth->rows alone doesn't work for insert into with MySQL
165    
166     my @types = eval {
167     map { $dbh->type_info($_) ? $dbh->type_info($_)->{TYPE_NAME} : '?' } @{ $sth->{TYPE} };
168     };
169    
170     my $table = table_row( 'th' => @{ $sth->{NAME} } );
171    
172     while (my @row = $sth->fetchrow_array() ) {
173     $table .= table_row( 'td' => @row );
174     }
175    
176     my $item = $feed->add_item( "$url/$sql_file" );
177     $item->title( $comment );
178     # $item->pubDate( "2006-02-23T14:43:43+09:00" );
179     $item->description( qq|
180     <pre style="font-size: 70%">$sql</pre>
181     <table border=1>$table</table>
182     | );
183    
184     }
185     }
186    
187     print $feed->to_string;
188    
189     1;

Properties

Name Value
svn:executable *

  ViewVC Help
Powered by ViewVC 1.1.26