
From roberts@panix.com Sat Oct 10 20:39:17 1998
Date: 11 Sep 1998 00:46:51 -0400
From: Roland Roberts <roberts@panix.com>
Reply-To: keystone-users@homeport.org
To: keystone-users@homeport.org
Subject: [keystone-users] Yet some more PostgreSQL patches

-----BEGIN PGP SIGNED MESSAGE-----

I discovered, to my dismay, that some of the screens don't work
because PostgreSQL and MySQL handle char() columns differently.  FWIW,
if anyone out there decided to put together an Oracle port, you'll
have the same issue.  MySQL char() columns should be PostgreSQL
varchar() columns and Oracle varchar2() columns.  This patch fixes the
columns definitions.

The only columns I did _not_ change from char() to varchar() are those
which 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 painful
task of modifying people and contacts, at least, before things will
really work right.  Unfortunately, PostgreSQL doesn't let you modify a
column's attributes on the fly.  You will have to drop the table and
recreate 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 of
this 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 have
any 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 full
dump including the schema, so you can restore completely by doing a
destroydb/createdb.


- --- ../keystone-0.30.03/struct/contacts.pgsql   Thu Aug  6 23:29:12 1998
+++ struct/contacts.pgsql       Thu Sep 10 22:50:44 1998
@@ -9,12 +9,12 @@
 -- Table structure for table 'contacts'
 --
 CREATE TABLE contacts (
- -  tag char(10) NOT NULL PRIMARY KEY,
- -  name char(40),
- -  company char(50),
- -  phone char(20),
- -  email char(30),
- -  passwd char(15)
+  tag varchar(10) NOT NULL PRIMARY KEY,
+  name varchar(40),
+  company varchar(50),
+  phone varchar(20),
+  email varchar(30),
+  passwd varchar(15)
 );
 
 
- --- ../keystone-0.30.03/struct/groups.pgsql     Thu Aug  6 23:29:16 1998
+++ struct/groups.pgsql Thu Sep 10 23:07:09 1998
@@ -9,7 +9,7 @@
 -- Table structure for table 'groups'
 --
 CREATE TABLE groups (
- -       g_id char(10) NOT NULL PRIMARY KEY ,
+       g_id varchar(10) NOT NULL PRIMARY KEY ,
        g_assign char(1) ,
        g_people varchar(50)
 );
- --- ../keystone-0.30.03/struct/master.pgsql     Thu Aug  6 23:29:17 1998
+++ struct/master.pgsql Thu Sep 10 23:08:27 1998
@@ -9,8 +9,8 @@
 -- Table structure for table 'master'
 --
 CREATE TABLE master (
- -  flag char(10) NOT NULL PRIMARY KEY,
- -  cvalue char(40),
+  flag varchar(10) NOT NULL PRIMARY KEY,
+  cvalue varchar(40),
   ivalue int
 );
 
- --- ../keystone-0.30.03/struct/people.pgsql     Wed Sep  9 22:25:04 1998
+++ struct/people.pgsql Thu Sep 10 22:47:23 1998
@@ -9,13 +9,13 @@
 -- Table structure for table 'people'
 --
 CREATE TABLE people (
- -  login char(10) NOT NULL PRIMARY KEY,
- -  fullname char(40),
+  login varchar(10) NOT NULL PRIMARY KEY,
+  fullname varchar(40),
   passwd varchar(50),
- -  phone char(20),
- -  email char(30),
- -  grp char(10),
- -  company char(20)
+  phone varchar(20),
+  email varchar(30),
+  grp varchar(10),
+  company varchar(40)
 );
 
 
- --- ../keystone-0.30.03/struct/status.pgsql     Thu Aug 27 10:34:15 1998
+++ struct/status.pgsql Thu Sep 10 23:09:40 1998
@@ -10,7 +10,7 @@
 --
 CREATE TABLE status (
   flag char(1) NOT NULL,
- -  label char(15) NOT NULL PRIMARY KEY
+  label varchar(15) NOT NULL PRIMARY KEY
 );
 
 CREATE INDEX status_flag_idx ON status(flag);



- ---- pg_strip.pl ----
#! /usr/bin/perl

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";

    # Find the primary key for this table
    $q = " select a.attname, t.typname "
	."  from pg_class c, pg_class c2, pg_index i, pg_attribute a, pg_type t "
	." where c.relname='$t' "
	."   and c.oid=i.indrelid "
	."   and i.indexrelid = c2.oid "
	."   and c2.relname ~ '_pkey\$' "
	."   and a.attrelid = c.oid "
	."   and a.attnum = i.indkey[0] "
	."   and t.oid = a.atttypid ";
    $c2	= $dbh->exec($q);
    if ($c2->ntuples == 0) {
	$pkey = "_rowid";
	$ptyp = "int4";
    } else {
	$pkey = $c2->getvalue(0,0);
	$ptyp = $c2->getvalue(0,1);
    }
    die "Pg::exec($q)" unless $c2->resultStatus == PGRES_TUPLES_OK;
    print "$t pkey is $pkey\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 $pkey, 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' ";
	    if ($ptyp eq "int4") {
		$q .= " where $pkey = $v1";
	    } else {
		$q .= " where $pkey = '$v1'";
	    }
	    $c3 = $dbh->exec ($q);
	    if ($c3->resultStatus == PGRES_COMMAND_OK) {
		$numRows++;
	    }
	}
	print "updated $numRows rows $t.$nam\n";
    }
}


roland

-----BEGIN PGP SIGNATURE-----
Version: 2.6.2
Comment: Processed by Mailcrypt 3.4, an Emacs/PGP interface

iQCVAwUBNfirNeoW38lmvDvNAQFc6wP+Mo02/b4ev5buSmLKNd/Ldj8QcMghxIqy
28DkqdUw4dxmXOS0SRBt2bmoavlmxKpsODQHzSr3WqjbxBOj4NVpddA00AXzNbIn
aAW49axhNovImWzVlIbrj49/5eNTAmQHTqKKDugsx5Tcedl0j4ck1gP5Q0hDPLPV
wJv74g7/1Q0=
=/JJ5
-----END PGP SIGNATURE-----
-- 
Roland B. Roberts, PhD                  Custom Software Solutions
roberts@panix.com                           101 West 15th St #4NN
                                               New York, NY 10011
