#!/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;
use Data::Dump qw/dump/;

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

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 $json;
# I would love to use $sth->{TYPE} to get types from database, but it's just
# not verbose enough to be useful for us, so we are using _n suffix

my $rename_cols;

foreach my $col ( @{ $sth->{NAME} } ) {
	warn "## check type of $col" if $debug;
	my $old_name = $col;
	if ( $col =~ s/_valueType_(\w+)//i ) {
		$json->{properties}->{$col} = { valueType => $1 };
		$rename_cols->{ $old_name } = $col;
	}
}

warn "## rename_cols ", dump( $rename_cols );

while ( my $row = $sth->fetchrow_hashref ) {
	$row->{ $rename_cols->{ $_ } } = delete $row->{ $_ } foreach keys %$rename_cols;
	push @{ $json->{items} }, $row;
}

print to_json( $json );
# we have to call dump *AFTER* to_json because it screws types
warn "# json = ",dump( $json ) if $debug;
