#!/usr/bin/perl

# original from http://www.sitening.com/meta-slonik

# adaptations by andrew dunstan:
# . process all schemas except information_schema and pg_*
# . identify and use candidate keys, by looking for unique constraints and then
#   checking that all the cols have not null set.

use DBI;

use strict;


# We assume that the following environment variables are set:
# CLUSTER
# DBNAME1
# DBNAME2
# HOST1
# HOST2
# SLONY_USER

my( $database ) = @ARGV;
if( !$database ) {
	print STDERR "Database required.\n";
	exit( 1 );
}

my $dbh = DBI->connect( "dbi:Pg:dbname=$database" );
if( !$dbh ) {
	print STDERR "$DBI::errstr\n";
	exit( 1 );
}

print <<'EOF';
slonik <<_EOF_
cluster name = $CLUSTER;
node 1 admin conninfo = 'dbname=$DBNAME1 host=$HOST1 user=$SLONY_USER';
node 2 admin conninfo = 'dbname=$DBNAME2 host=$HOST2 user=$SLONY_USER';
init cluster ( id = 1 );
create set ( id = 1, origin = 1, comment = 'set 1 for $CLUSTER' );

EOF

my $sequence_sql = <<EOF;
SELECT c.relname as "Name", n.nspname as "NameSpace"
FROM pg_catalog.pg_class c
     LEFT JOIN pg_catalog.pg_user u ON u.usesysid = c.relowner
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind = 'S'
      AND n.nspname <> 'information_schema' AND n.nspname !~ '^pg_' 
EOF

my $sequence_sth = $dbh->prepare( $sequence_sql );
if( $dbh->err ) {
	print STDERR "@{ [ $dbh->errstr ] }\n";
	print STDERR "Aborting.\n";
	exit( 1 );
}
$sequence_sth->execute;
if( $sequence_sth->err ) {
	print STDERR "@{ [ $sequence_sth->errstr ] }\n";
	print STDERR "Aborting.\n";
	exit( 1 );
}

print "# Sequences\n";
my $id = 1;
while( my ($sequence, $nspname) = $sequence_sth->fetchrow_array ) {
	print "set add sequence ( set id = 1, origin = 1, id = $id, fully qualified name = '$nspname.$sequence' );\n";
	$id++;
}
$sequence_sth->finish;

print "\n";
print "# Constrained Tables\n";
my $constrained_table_sql = <<EOF;
SELECT c.relname, n.nspname
FROM pg_catalog.pg_class c
     LEFT JOIN pg_catalog.pg_user u ON u.usesysid = c.relowner
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind = 'r'
      AND n.nspname <> 'information_schema' AND n.nspname !~ '^pg_' 
      AND EXISTS (
            SELECT 1
            FROM pg_catalog.pg_class c2, pg_catalog.pg_index i
            WHERE c.oid = i.indrelid
            AND i.indexrelid = c2.oid
            AND i.indisprimary = 't'
      )
EOF

my $constrained_table_sth = $dbh->prepare( $constrained_table_sql );
if( $dbh->err ) {
	print STDERR "@{ [ $dbh->errstr ] }\n";
	print STDERR "Aborting.\n";
	exit( 1 );
}
$constrained_table_sth->execute;
if( $constrained_table_sth->err ) {
	print STDERR "@{ [ $constrained_table_sth->errstr ] }\n";
	print STDERR "Aborting.\n";
	exit( 1 );
}

while( my ($constrained_table, $constrained_namespace) = $constrained_table_sth->fetchrow_array ) {
	print "set add table ( set id = 1, origin = 1, id = $id, fully qualified name = '$constrained_namespace.$constrained_table' );\n";
	$id++;
}
$constrained_table_sth->finish;

print "\n";

print "# Keyed Tables\n";

my $keyed_table_sql = <<EOF;
SELECT c.oid, c.relname, n.nspname, t.conname, t.conkey 
FROM pg_catalog.pg_class c
	 JOIN pg_catalog.pg_constraint t ON (t.conrelid = c.oid)
     LEFT JOIN pg_catalog.pg_user u ON u.usesysid = c.relowner
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind = 'r'
	  AND t.contype = 'u'
      AND n.nspname <> 'information_schema' AND n.nspname !~ '^pg_' 
      AND NOT EXISTS (
            SELECT 1
            FROM pg_catalog.pg_class c2, pg_catalog.pg_index i
            WHERE c.oid = i.indrelid
            AND i.indexrelid = c2.oid
            AND i.indisprimary = 't'
      )
EOF

my $keyed_table_sth = $dbh->prepare( $keyed_table_sql );
if( $dbh->err ) {
	print STDERR "@{ [ $dbh->errstr ] }\n";
	print STDERR "Aborting.\n";
	exit( 1 );
}
$keyed_table_sth->execute;
if( $constrained_table_sth->err ) {
	print STDERR "@{ [ $constrained_table_sth->errstr ] }\n";
	print STDERR "Aborting.\n";
	exit( 1 );
}

my %have_candidate;
my %badkeys;

my $check_candidate_sql = q[

    SELECT bool_and(attnotnull)
    FROM pg_catalog.pg_attribute
    WHERE attrelid = ?
        AND attnum = ANY (?)
							];
my $check_candidate_sth = $dbh->prepare ($check_candidate_sql);

if( $dbh->err ) {
	print STDERR "@{ [ $dbh->errstr ] }\n";
	print STDERR "Aborting.\n";
	exit( 1 );
}

while (my ($k_oid,$k_rel,$k_nsp,$k_con,$k_cols) = $keyed_table_sth->fetchrow_array)
{
	my $keyok = $dbh->selectrow_array($check_candidate_sth,{},$k_oid, $k_cols);
	my $fqn = "$k_nsp.$k_rel";
	if ($keyok)
	{
		if ($have_candidate{$fqn})
		{
			print " # rejecting candidate $k_con for $fqn - previously selected $have_candidate{$fqn}\n";
		}
		else
		{
			$have_candidate{$fqn} = $k_con;
			print "set add table ( set id = 1, origin = 1, id = $id, fully qualified name = '$fqn', key = '$k_con' );\n";
			$id++;
		}
	}
	else
	{
		$badkeys{$fqn} ||= [];
		push(@{$badkeys{$fqn}},$k_con);
	}
}

print "\n";

print "# tables with unique constraints but no acceptable candidate key:\n";
foreach my $cand (keys %badkeys)
{
	next if $have_candidate{$cand};
	print "# table: $cand, keys: ",join(",",@{$badkeys{$cand}}),"\n";
}


print "\n";

print "# Unconstrained Tables\n";
my $unconstrained_table_sql = <<EOF;
SELECT c.relname, n.nspname
FROM pg_catalog.pg_class c
     LEFT JOIN pg_catalog.pg_user u ON u.usesysid = c.relowner
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind = 'r'
      AND n.nspname <> 'information_schema' AND n.nspname !~ '^pg_' 
      AND NOT EXISTS (
            SELECT 1
            FROM pg_catalog.pg_class c2, pg_catalog.pg_index i
            WHERE c.oid = i.indrelid
            AND i.indexrelid = c2.oid
            AND i.indisprimary = 't'
      )
EOF

my $unconstrained_table_sth = $dbh->prepare( $unconstrained_table_sql );
if( $dbh->err ) {
	print STDERR "@{ [ $dbh->errstr ] }\n";
	print STDERR "Aborting.\n";
	exit( 1 );
}
$unconstrained_table_sth->execute;
if( $unconstrained_table_sth->err ) {
	print STDERR "@{ [ $unconstrained_table_sth->errstr ] }\n";
	print STDERR "Aborting.\n";
	exit( 1 );
}

while( my ($unconstrained_table, $unconstrained_namespace) = $unconstrained_table_sth->fetchrow_array ) {
	next if $have_candidate{"$unconstrained_namespace.$unconstrained_table"};
	print "table add key ( node id = 1, fully qualified name = '$unconstrained_namespace.$unconstrained_table' );\n";
	print "set add table ( set id = 1, origin = 1, id = $id, fully qualified name = '$unconstrained_namespace.$unconstrained_table', key = serial );\n";
	$id++;
}
$unconstrained_table_sth->finish;

$dbh->disconnect;

print <<'EOF';

store node ( id = 2, comment = 'Node 2' );
store path ( server = 1, client = 2, conninfo = 'dbname=$DBNAME1 host=$HOST1 user=$SLONY_USER');
store path ( server = 2, client = 1, conninfo = 'dbname=$DBNAME2 host=$HOST2 user=$SLONY_USER');
store listen ( origin = 1, provider = 1, receiver = 2 );
store listen ( origin = 2, provider = 2, receiver = 1 );
_EOF_
EOF