simile/sql/sql.pl

#!/usr/bin/perl

use warnings;
use strict;

# sql.pl - covert SQL files to json
#
# 10/25/08 00:06:01 CEST Dobrica Pavlinusic <dpavlin@rot13.org>

use DBI;
use JSON;

my $dsn = 'DBI:Pg:dbname=new';

my $comment;
my $sql;

while(<>) {
        if (/^\\c\s*(.+)/ ) {
                my $database = $1;
                $dsn =~ s/=.*$/=$database/;
        } elsif ( /^--(.+)/ ) {
                $comment .= "$1\n";
        } else {
                $sql .= $_;
        }
}

warn "# dsn = $dsn";
my $dbh = DBI->connect( $dsn, '', '', { RaiseError => 1 } ) || die $DBI::errstr;
$dbh->do( qq{ set client_encoding='utf-8' } );

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

my $items;

while ( my $row = $sth->fetchrow_hashref ) {
        push @$items, $row;
}

print to_json( { items => $items } );
1	dpavlin	33	#!/usr/bin/perl
2
3			use warnings;
4			use strict;
5
6			# sql.pl - covert SQL files to json
7			#
8			# 10/25/08 00:06:01 CEST Dobrica Pavlinusic <dpavlin@rot13.org>
9
10			use DBI;
11			use JSON;
12
13			my $dsn = 'DBI:Pg:dbname=new';
14
15			my $comment;
16			my $sql;
17
18			while(<>) {
19			if (/^\\c\s*(.+)/ ) {
20			my $database = $1;
21			$dsn =~ s/=.*$/=$database/;
22			} elsif ( /^--(.+)/ ) {
23			$comment .= "$1\n";
24			} else {
25			$sql .= $_;
26			}
27			}
28
29			warn "# dsn = $dsn";
30			my $dbh = DBI->connect( $dsn, '', '', { RaiseError => 1 } ) \|\| die $DBI::errstr;
31			$dbh->do( qq{ set client_encoding='utf-8' } );
32
33			warn "# sql $sql";
34			my $sth = $dbh->prepare( $sql );
35			$sth->execute();
36
37			my $items;
38
39			while ( my $row = $sth->fetchrow_hashref ) {
40			push @$items, $row;
41			}
42
43			print to_json( { items => $items } );