#! /usr/bin/perl
#
# I discovered, to my dismay, that some of the screens don't
# workbecause PostgreSQL and MySQL handle char() columns differently.
# FWIW,if anyone out there decided to put together an Oracle port,
# you'llhave the same issue.  MySQL char() columns should be
# PostgreSQLvarchar() columns and Oracle varchar2() columns.  This
# patch fixes thecolumns definitions.
# 
# The only columns I did _not_ change from char() to varchar() are
# thosewhich are one or two characters, e.g.,
# 
#     dictionary.dtype    char(1)
#     eschema.etype       char(1)
#     groups.g_assign     char(1)
#     resources.type      char(2)
#     slips.status        char(1)
#     slips.is_public     char(1)
#     status.flag         char(1)
# 
# After applying this, you will have to go through and do the
# painfultask of modifying people and contacts, at least, before
# things willreally work right.  Unfortunately, PostgreSQL doesn't let
# you modify acolumn's attributes on the fly.  You will have to drop
# the table andrecreate it.  What I did was...
# 
#     pg_dump -a -d -D -t people -f people.dump keystone
#     *** Edit people.dump, remove trailing whitespace from fields ***
#     psql keystone
#     keystone=> drop table people;
#     keystone=> \i people.pgsql
#     keystone=> \i people.dump
# 
# However, that was painful.  I've added a Perl script at the end
# ofthis message that will allow you to change this to:
# 
#     pg_dump -d -D -f keystone.dump keystone
#     pg_dump -a -d -D -t people -f people.dump keystone
#     pg_dump -a -d -D -t groups -f groups.dump keystone
#     [...master, contacts, status...]
#     psql keystone
#     keystone=> drop table people;
#     keystone=> \i people.pgsql
#     keystone=> \i people.dump
#     keystone=> drop table groups;
#     keystone=> \i groups.pgsql
#     keystone=> \i groups.dump
#     [...master, contacts, status...]
#     keystone=> \q
#     perl pg_strip.pl
#     perl pg_grantall.pl
# 
# This will trim trailing blanks from ALL varchar columns.  If you
# haveany trailing blanks in columns that are significant, YOU WILL
# LOSE THEM.
# 
# You've been warned.
# 
# Worst case, if it trashes your database, the first dump was a
# fulldump including the schema, so you can restore completely by
# doing a destroydb/createdb.

use Pg;

if (defined $ARGV[0]) {
    $dbname = $ARGV[0];
} else {
    $dbname = "keystone";
}
$dbh = Pg::connectdb("dbname = $dbname");

die "Pg::connectdb failed, $!" unless ($dbh->status == PGRES_CONNECTION_OK);

$c1 = $dbh->exec (" select relname "
		  ."  from pg_class "
		  ." where relname !~ '^pg_' "
		  ."   and relkind = 'r'");
die "Pg::exec $!" unless $c1->resultStatus == PGRES_TUPLES_OK;
for ($i = 0; $i < $c1->ntuples; $i++) {
    push @table, $c1->getvalue($i,0);
}

foreach $t (@table) {
    print "Starting table $t\n";

    $c1 = $dbh->exec (" select a.attnum, a.attname "
		      ."  from pg_class c, pg_attribute a, pg_type t "
		      ." where c.relname = '$t' "
		      ."   and a.attnum > 0 "
		      ."   and a.attrelid = c.oid "
		      ."   and a.atttypid = t.oid "
		      ."   and t.typname = 'varchar' "
		      ." order by a.attnum asc ");
    die "Pg::exec $!" unless $c1->resultStatus == PGRES_TUPLES_OK;

    # Find the column types for this table
    for ($i = 0; $i < $c1->ntuples; $i++) {
	$nam = $c1->getvalue($i,1);
	# Trim whitespace off the right-hand side
	$c2 = $dbh->exec ("select oid, rtrim($nam) from $t where $nam ~ ' \$'");
	die "Pg::exec $!" unless $c2->resultStatus == PGRES_TUPLES_OK;
	$numRows = 0;
	for ($j = 0; $j < $c2->ntuples; $j++) {
	    $v1 = $c2->getvalue($j,0);
	    $v2 = $c2->getvalue($j,1);
	    $q = " update $t set $nam = '$v2' where oid = $v1";
	    $c3 = $dbh->exec ($q);
	    if ($c3->resultStatus == PGRES_COMMAND_OK) {
		$numRows++;
	    }
	}
	print "updated $numRows rows $t.$nam\n";
    }
}
