#! /bin/sh
# This is a shell archive.  Remove anything before this line, then unpack
# it by saving it into a file and typing "sh file".  To overwrite existing
# files, type "sh file -c".  You can also feed this as standard input via
# unshar, or by typing "sh <file", e.g..  If this archive is complete, you
# will see the following message at the end:
#		"End of shell archive."
# Contents:  README.dbalter dbalter
# Wrapped by chrisb@diamond on Wed Sep 14 15:52:54 1994
PATH=/bin:/usr/bin:/usr/ucb ; export PATH
if test -f 'README.dbalter' -a "${1}" != "-c" ; then 
  echo shar: Will not clobber existing file \"'README.dbalter'\"
else
echo shar: Extracting \"'README.dbalter'\" \(2603 characters\)
sed "s/^X//" >'README.dbalter' <<'END_OF_FILE'
Xdbalter - Update Informix database table structure
X
XINTRODUCTION
X
Xdbalter(1L) is an isqlperl script which, given an Informix database
Xand a file of SQL statements to create a database, will update the
Xdatabase to to same structure as the script, retaining the data.
X
XPREREQUISITES
X
XThis needs isqlperl v>=1.2, Perl with Informix SQL extensions and
Xof course a properly installed Informix SQL.
X
XLIMITATIONS
X
XThis version of dbalter directly executes *all* commands in the
XSQL-script except CREATE TABLE which is processed specially.  It
Xit does not validate the commands.
X
Xdbalter has only been tested with "INFORMIX-SQL Version 2.10.03F",
Xbut should work with any version.
X
XThe "algorithm" is pretty simple minded ... but works for me.  I
Xhope it is obvious from the code.
X
XI have tried to make the the program failsafe -- It's my (customers)
Xdata I'm playing with -- but if it goes wrong :-( my comiserations.
X
XTO DO
X
XAdd any improvements suggested by you, dear reader :-)
X
XAdd changes for Informix v5 if^H^Hwhen I get time to convert to
Xit.
X
XBACKGROUND
X
X[reminise mode: on] Back in the dawn of time, in about 1983 I
Xstarted using Informix v3.10? (the pre-SQL Informix) because it
Xwas really neat and it could access the data files of our COBOL
Xapplication.
X
XTo create a table you could do:         dbbuild schema-file
X
Xwhere schema-file was a description of the table structure.
X
XTo change the structure of a table you could do:
X
X		dbbuild -r schema-file
X
Xdbbuild would list the differences and ask for confirmation, then
Xconvert the file to the new format.
X
X	... time passes ...
X
XNow I'm managing another programmer, the technicians, the
Xsupport/training staff and occasionally programming in Uniface with
XI-SQL.  I'm writing, installing, training and supporting an
Xall-singing, all-dancing, front-office application for lawyers.
XThe system is continuously changing: new customers get the latest
X"snapshot" of the system.
X
XThis is OK until I need to upgrade the customer.  I've tried poring
Xover lists of the table structures, it's difficult(impossible) to
Xdo right and *s*l*o*w*. I've tried keeping the SQL &/or programs
Xused to alter the database structure, but when I get to the customer
Xsite, there is always some missing.
X
XUniface produces SQL to create the database, but Informix-SQL
Xdoesn't have "dbbuild -r" to update the structure for me
X... so ... dbalter.
X[reminise mode: off]
X
XCOPYRIGHT
X
XCopyright (C) 1994 Chris Benson, <chrisb@jesmond.demon.co.uk>
X
XUse it how you want except (a) retain my copyright msg, (b) don't
Xblame me if it fails.
X
XPlease send me your suggestions/comments/ideas.
END_OF_FILE
if test 2603 -ne `wc -c <'README.dbalter'`; then
    echo shar: \"'README.dbalter'\" unpacked with wrong size!
fi
# end of 'README.dbalter'
fi
if test -f 'dbalter' -a "${1}" != "-c" ; then 
  echo shar: Will not clobber existing file \"'dbalter'\"
else
echo shar: Extracting \"'dbalter'\" \(5647 characters\)
sed "s/^X//" >'dbalter' <<'END_OF_FILE'
X#!/usr/local/bin/isqlperl -w
X# vi:set ts=4 ai sm:
X# TODO:
X#	disable interrupts?
X#	changes for v5?
X#	???
X
X$RCSid = '$Id: dbalter,v 1.6 1994/09/14 14:52:04 chrisb Exp $';
X$0 =~ s#^.*/##;
X$title = <<EOS;
X$0: Update Informix database table structures
XCopyright (C) 1994 Chris Benson <chrisb@jesmond.demon.co.uk>
X$RCSid
Xwritten in Perl w/Informix extensions (isqlperl)
X**NO WARRANTY** BACKUP THE DATABASE FIRST! **NO WARRANTY** 
X-------------------------------------------------------------------------------
XEOS
X
X$verbose = 1;
X$usage = "usage: $0 database sql-file\n";
X($version) = split(' ', $isql_attrib);
Xdie "sorry, must be isqlperl v1.2 or greater\n" unless $version >= 1.2;
X
Xprint $title;
X
X$#ARGV == 1	|| die $usage, "$0: not enough arguments\n";
X
X$database = $ARGV[0];
X
X# process the sql file
Xopen(SQL, $ARGV[1])
X	|| die $usage, "$0: can't open sql-file '$ARGV[1]': $!\n";
X
X&isql_execute("database $database")
X	|| die $usage, 
X		"$0: can't connect to database '$database': $isql_code\n";
X
X$/ = ';';	# read whole SQL statements
X
Xwhile ( <SQL> ) {
X	# tidy command
X	s/\n/ /g;	s/^\s*//;	s/\s*;\s*$//;	s/\s+/ /g;
X	next	if (/^$/);
X
X	if ( /create\s+table\s+([a-z0-9_]+)/i ) {
X		&convert_table($1, $_);
X	} else {
X		&my_execute($_)	|| &my_warn('command failed');
X	}
X}
Xclose(SQL);
Xexit;
X
X#########################
X# execute or print with a newline
Xsub my_execute {
X	local($cmd) = @_;
X	# print '+ ', (length($cmd) > 77 ? substr($cmd, 0, 74)."..." : $cmd), "\n"
X	print $cmd, "\n"
X		if $verbose;
X	return &isql_execute($cmd);
X}
X
X#########################
X# create the new table
X# if the table exists, insert from old, 
X# rename old, rename new, if OK, delete old
Xsub convert_table {
X	local($table, $cmd)	= @_;
X	local($tabid) = &get_tabid($table);
X	local($x);
X
X	print <<EOS;
X$0: processing $table 
X-------------------------------------------------------------------------------
XEOS
X	if (&get_tabid('mytable1')) {
X		&isql_execute('drop table mytable1')	|| do {
X			&my_warn("can't drop old temp table 'mytable1'");
X			return;
X		}
X	}
X	$cmd =~ s/$table/mytable1/;
X
X	# create the table
X	&my_execute($cmd)	|| do {
X		&my_warn("can't create new table 'mytable1'");
X		return;
X	};
X
X	# re-load the table?
X	if ( $tabid ) {
X		local(@common_fields, @old_fields, @new_fields, @added_fields);
X		local(%mark, %old_types, %new_types);
X
X		@new_fields = &get_fields('mytable1');
X		@old_fields = &get_fields($table);
X		%new_types = &get_types('mytable1');
X		%old_types = &get_types($table);
X
X		%mark = ();
X		grep( $mark{$_}++, @old_fields );
X		@common_fields = grep( $mark{$_}, @new_fields );
X		if ($#common_fields >= 0) {
X			# print "common:\t", join(',', @common_fields), "\n";
X		} else {
X			print "$0: no fields in common!\n";
X		}
X
X		%mark = ();
X		grep( $mark{$_}++, @old_fields );
X		@added_fields = grep(!$mark{$_}, @new_fields );
X		print "add:\t", join(', ', @added_fields ), "\n";
X
X		%mark = ();
X		grep( $mark{$_}++, @new_fields );
X		print "drop:\t", join(', ', grep(!$mark{$_}, @old_fields )), "\n";
X
X		print "change:";
X		foreach $x (@common_fields) {
X			if ( $old_types{$x} ne $new_types{$x} ) {
X				print "	$x	$old_types{$x}	-->	$new_types{$x} \n";
X			}
X		}
X
X		print "\r$0: update table $table (y/n/q)?: ";
X		{
X			local($/) = "\n";
X			$x = <STDIN>;
X		}
X		($x =~ /^y/i)	|| do {		# not Yes
X			&my_execute("drop table mytable1");
X			$x =~ /^q/i ? exit : return;
X		};
X
X		if ( $#common_fields >= 0 ) {
X			local($all_fields, $all_values);
X			local(@added_values);		# to init new columns
X
X			print "copying data ....\n";
X
X			# prepare default values for new fields
X			@added_values = @added_fields;
X			foreach (@added_values) {
X				if ($new_types{$_} =~ /CHAR/i) {
X					$_ = "' '";
X				} elsif ($new_types{$_} =~ /DATE/i) {
X					$_ = "'1/1/1'";
X				} else {
X					$_ = "0";
X				}
X			}
X			$all_fields = join(',', @common_fields, @added_fields);
X			$all_values = join(',', @common_fields, @added_values);
X
X			&my_execute( "insert into mytable1 ($all_fields) " . 
X					"select $all_values from $table" )	|| do {
X				&my_warn("insert into mytable1 failed");
X				&my_execute("drop table mytable1");
X				return;
X			};
X		}
X
X		# rename the original table
X		&my_execute('drop table mytable2')	if &get_tabid('mytable2');
X		&my_execute("rename table $table to mytable2")	|| do {
X			&my_warn("can't rename original table, stopping");
X			exit 1;
X		};
X	}
X
X	# install the new file
X	&my_execute("rename table mytable1 to $table")	|| do {
X		&my_warn("can't rename new table, stopping");
X		exit 1;
X	};
X
X	# and tidy up
X	&my_execute('drop table mytable2')	if &get_tabid('mytable2');
X
X	print <<EOS;
X-------------------------------------------------------------------------------
XEOS
X}
X
X#########################
X# return the tabid
Xsub get_tabid {
X	local($table) = @_;
X	local($id, $tabid);
X
X	$id = &isql_open("select tabid from systables where tabname = '$table'");
X	($tabid) = &isql_fetch($id);
X	&isql_close($id);
X	$tabid;
X}
X
X#########################
X# return an array of field names
Xsub get_fields {
X	local($table) = @_;
X	local($id, @titles);
X
X	$id = &isql_open("select * from $table");
X	@titles = &isql_titles($id);
X	&isql_close($id);
X	return (wantarray ? @titles : $#titles);
X}
X
X#########################
X# return an array of field types
Xsub get_types {
X	local($table) = @_;
X	local($id, $i, @titles, @types, %types);
X
X	$id = &isql_open("select * from $table");
X	@titles = &isql_titles($id);
X	@types = &isql_columns($id);
X	&isql_close($id);
X	for ( $i = 0 ; $i <= $#titles ; $i++ ) {
X		$types{ $titles[$i] } = $types[$i];
X		# print $titles[$i], " ", $types[$i], "\t";
X	}
X	return ( %types );
X}
X
X#########################
Xsub my_warn {
X	warn "$0: ", @_, ": $isql_code\n";
X}
X#########################
END_OF_FILE
if test 5647 -ne `wc -c <'dbalter'`; then
    echo shar: \"'dbalter'\" unpacked with wrong size!
fi
chmod +x 'dbalter'
# end of 'dbalter'
fi
echo shar: End of shell archive.
exit 0