Repository List / SQL2RSS / sql2rss.cgi @ HEAD

#!/usr/bin/perl -T
use warnings;
use strict;

=head1 NAME

sql2rss.cgi - convert sql queries on file system to RSS feed

=head1 USAGE

Each file in current directory which ends in C<*.sql> will
be converted rss item. If you want to have specific order, you can
prefix filenames with numbers which will be striped when creating sheet
names.

Comments in sql files (lines beginning with C<-->) will be placed
in first line in bold.

To specify database on which SQL query is executed
C<\c database> syntax is supported.

=head1 INSTALLATION

If your server is configured to execute C<.cgi> files, you can
drop this script anywhere, but you can also add something like

   ScriptAlias /xls-reports /srv/SQL2XLS/sql2xls.cgi

in Apache's virtual host configuration to get nice URLs

To configure default database, user, password and other settings create
C<config.pl> file in same directory in which C<sql2xls.cgi> is with something
like this:

  $dsn      = 'DBI:mysql:dbname=';
  $database = 'database';
  $user     = 'user';
  $passwd   = 'password';

  $db_encoding     = 'utf-8';

  $debug = 1;

=head1 SECURITY

There is none. Use apache auth modules if you need it.

=head1 AUTHOR

Dobrica Pavlinusic, dpavlin@rot13.org, L<http://svn.rot13.org/index.cgi/SQL2RSS/>

=cut

use XML::FeedPP;
use DBI;
use CGI::Carp qw(fatalsToBrowser);
use Data::Dump qw/dump/;

warn dump( \%ENV );

our $dsn      = 'DBI:Pg:dbname=';
our $database = 'template1';
our $user     = 'dpavlin';
our $passwd   = '';

our $db_encoding;

our $debug = 1;


my $sql_dir = $ENV{SCRIPT_FILENAME} || '.';
$sql_dir =~ s,/[^/]+$,,;

my $reports_path = $ENV{PATH_INFO} || '';
$reports_path =~ s/\.\.//g; # some protection against path exploits
$reports_path ||= shift @ARGV; # for CLI invocation


sub require_config {
	my $config_path = $1 if "$sql_dir/config.pl" =~ m/^(.+)$/; # untaint
	warn "# using $config_path\n";
	require $config_path if -e $config_path;
}

require_config;


my @sql_files;

if ( -d "$sql_dir/$reports_path" ) {
	$sql_dir .= '/' . $reports_path;
	warn "SQL queries from $sql_dir\n";
	opendir(DIR, $sql_dir) || die "can't opendir $sql_dir: $!";
	@sql_files = sort grep { /\.sql$/i && -f "$sql_dir/$_" } readdir(DIR);
	closedir DIR;
} elsif ( -f "$sql_dir/$reports_path" ) {
	@sql_files = ( $reports_path );
}

my $url = "http://$ENV{HTTP_HOST}/$ENV{REQUEST_URI}";
print qq|Content-type: application/rss+xml\n\n|;

my $feed = XML::FeedPP::RSS->new();
$feed->title( $reports_path );
$feed->link( $url );
#$feed->pubDate( "Thu, 23 Feb 2006 14:43:43 +0900" );

our $dbh;
sub use_database {
	$dbh->disconnect if $dbh;
	my $database = shift || return;
	print STDERR "## connect to $database\n" if $debug;
	$dbh = DBI->connect($dsn . $database,$user,$passwd, { RaiseError => 1, AutoCommit => 0 }) || die $DBI::errstr;
	if ( 0 && $db_encoding ) { # FIXME
		if ( $dsn =~ m{Pg} ) {
			$dbh->do( qq{ set client_encoding = '$db_encoding'; } );
		} elsif ( $dsn =~ m{mysql} ) {
			$dbh->do( qq{ set names '$db_encoding'; } );
		} else {
			warn "Don't know how to set encoding to $db_encoding for $dsn";
		}
	}
}

use_database( $database );

sub table_row {
	my $type = shift @_;
	my $html = join('', map { qq|<$type>$_</$type>| } @_ );
	return qq|<tr>$html</tr>|;
}

foreach my $sql_file (@sql_files) {

	print STDERR "working on $sql_file\n" if ($debug);

	open(SQL,"$sql_dir/$sql_file") || die "can't open sql file '$sql_dir/$sql_file': $!";
	my $comment = '';
	my $full_sql = "";
	while(<SQL>) {
		chomp;
		if (/^\\c\s+(\S+)/) {
			use_database( $1 );
		} elsif (/^--(.+)/) {
			$comment.=$1;
		} else {
			$full_sql.= ' ' . $_;
		}
	}
	close(SQL);

	$full_sql =~ s/\s\s+/ /gs;
	$full_sql .= ';' unless $full_sql =~ m/;\s*/s;

	print STDERR "sql: $full_sql\ncomment: $comment\n" if ($debug);

	foreach my $sql ( split(/;/, $full_sql ) ) {

		warn "SQL: $sql\n";

		my $sth = $dbh->prepare($sql);
		$sth->execute();

		next unless $sth->{NAME} && $sth->rows > 0; # $sth->rows alone doesn't work for insert into with MySQL

		my @types = eval {
			map { $dbh->type_info($_) ? $dbh->type_info($_)->{TYPE_NAME} : '?' } @{ $sth->{TYPE} };
		};

		my $table = table_row( 'th' => @{ $sth->{NAME} } );

		while (my @row = $sth->fetchrow_array() ) {
			$table .= table_row( 'td' => @row );
		}

		my $item = $feed->add_item( "$url/$sql_file" );
		$item->title( $comment );
#		$item->pubDate( "2006-02-23T14:43:43+09:00" );
		$item->description( qq|
			<pre style="font-size: 70%">$sql</pre>
			<table border=1>$table</table>
		| );

	}
}

print $feed->to_string;

1;