/[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

Contents of /sql2rss.cgi

Parent Directory Parent Directory | Revision Log Revision Log


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

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