#! /bin/sh

# This is a shell archive.  Type 'sh <file>' to unpack.

echo x - README.1st
cat >README.1st <<'MKSHAR_EOF'
 Utility: mkcnt.awk
 File(s): mkcnt.awk

Description:
	Process dbschema or myschema output and produce an SQL script to count 
	and report the number of rows in each table.

-----------------------------------------------------------------------------

 Utility: mkdbcopy.awk
 File(s): mkdbcopy.awk

Description:
	Process dbschema or myschema output and produce a shell script to run a
	sequence of dbcopy tasks to copy the tables to another server.  Source
	and target can be specified on the commandline of the resulting script
	and the name of the schema file is the default database name which can
	also be overridden.

-----------------------------------------------------------------------------

 Utility: mkdrop.awk
 File(s): mkdrop.awk

Description:
        Process dbschema or myschema output and produce an SQL script to drop
	the each table.

-----------------------------------------------------------------------------

 Utility: mkload.awk
 File(s): mkload.awk

Description:
        Process dbschema or myschema output and produce an SQL script to load 
	each table from a file named: <table>.unl

-----------------------------------------------------------------------------

 Utility: mknew.awk
 File(s): mknew.awk

Description:
        Process dbschema or myschema output and produce an SQL script to drop, 
	recreate, and reload a table from another server.

-----------------------------------------------------------------------------

 Utility: mkreload.awk
 File(s): mkreload.awk

Description:
        Process dbschema or myschema output and produce an SQL script to delete 
	all rows from each table and reload from a file named: <table>.unl

-----------------------------------------------------------------------------

 Utility: mkrename.awk
 File(s): mkrename.awk

Description:
        Process dbschema or myschema output and produce an SQL script to rename 	each table to <table>_o.

-----------------------------------------------------------------------------

 Utility: mkul_l.awk
 File(s): mkul_l.awk

Description:
        Process dbschema or myschema output and produce a shell script to 
	unload each table using my ul.ec binary load/unload utility.

-----------------------------------------------------------------------------

 Utility: mkul_u.awk
 File(s): mkul_u.awk

Description:
        Process dbschema or myschema output and produce a shell script to 
	load each table from a binary unload file using my ul.ec binary 
	load/unload utility.

-----------------------------------------------------------------------------

 Utility: mkunl.awk
 File(s): mkunl.awk

Description:
        Process dbschema or myschema output and produce an SQL script to 
	unload each table to a file named: <table>.unl

-----------------------------------------------------------------------------

MKSHAR_EOF


echo x - mkcnt.awk
cat >mkcnt.awk <<'MKSHAR_EOF'
/CREATE TABLE/{
	printf "select \"%s\", count(*) from %s;\n", $3, $3; 
}
/create table/{
        split($3, a, ".");
	printf "select \"%s\", count(*) from %s;\n", a[2], a[2]; 
}
MKSHAR_EOF


echo x - mkdbcopy.awk
cat >mkdbcopy.awk <<'MKSHAR_EOF'
# Assumes that input is a file named <mydatabase>.sql and uses the filename
# as the database name in the dbcopy commands that it generates.
#
# Also remember that one of the connections that dbcopy uses MUST be a 
# non-shared memory connection.  Our convention is that every server has 
# a TCP/IP alias that is the same as it's DBSERVERNAME but with an 's' for 
# sockets appended.  Therefore at execution time the script that this generates 
# will append an 's' to the source hostname.  If your conventions for hostnames 
# differs then modify this awk script to generate different dbcopy command 
# lines (usually just remove or change the 's' following ${SOURCE}.

BEGIN{
	blocking=10000
	delim=";"
	print "#!/usr/bin/ksh";
	print "if [[ $# -eq 0 && -z $REMOTE ]]; then echo \"$0  TARGET <SOURCE> <DATABASE>\"; exit 22; fi";
	print "if [[ $# -ge 1 ]]; then ";
	print "   export TARGET=$1";
	print "else";
	print "   export TARGET=$REMOTE";
	print "fi";
	print "if [[ $# -ge 2 ]]; then ";
	print "   export SOURCE=$2";
	print "else";
	print "   export SOURCE=$INFORMIXSERVER";
	print "fi";
	print "if [[ $# -ge 3 ]]; then";
	print "   export DBASE=$3";
	print "else";
	print "   export DBASE=${DBASE}";
	print "   if [[ -z $DBASE ]]; then";
	pos=index(FILENAME, "." );
	printf "        export DBASE=%s\n", substr( FILENAME, 1, (pos - 1) );
	print "   fi";
	print "fi";
}
# FNR==1{
	# pos=index(FILENAME, "." );
	# printf "export DBASE=%s\n", substr( FILENAME, 1, (pos - 1) );
# }
/create table/{ 
	if (split($3, a, ".") == 2)
	    printf  " dbcopy -f 1000 -F -w 10 -p 2 -h ${SOURCE}s -H $TARGET -t %s -f %d -l %s.unl -d $DBASE %s\n", a[2], blocking, a[2], delim; 
	else
	    printf  " dbcopy -f 1000 -F -w 10 -p 2 -h ${SOURCE}s -H $TARGET -t %s -f %d -l %s.unl -d $DBASE %s\n", a[1], blocking, a[1], delim; 
}
/CREATE TABLE/{ 
	printf  " dbcopy -f 1000 -F -w 10 -p 2 -h ${SOURCE}s -H $TARGET -t %s -f %d -l %s.unl -d $DBASE %s\n", $3, blocking, $3, delim; 
}
MKSHAR_EOF


echo x - mkdrop.awk
cat >mkdrop.awk <<'MKSHAR_EOF'
/create table/{ 
	if ( $3 == "*.*" ) {
		split($3, a, ".");
		printf "drop table %s;\n", a[2]; 
	} else	{
		printf "drop table %s;\n", $3; 
	}
}
/CREATE TABLE/{ 
	if ( $3 == "*.*" ) {
		split($3, a, ".");
		printf "drop table %s;\n", a[2]; 
	} else	{
		printf "drop table %s;\n", $3; 
	}
}
MKSHAR_EOF


echo x - mkload.awk
cat >mkload.awk <<'MKSHAR_EOF'
/create table/{ 
	split($3, a, ".");
	printf "load from %s.unl insert into %s;\n", a[2], a[2]; 
}
/CREATE TABLE/{ 
	printf "load from %s.unl insert into %s;\n", $3, $3; 
}
MKSHAR_EOF


echo x - mknew.awk
cat >mknew.awk <<'MKSHAR_EOF'
BEGIN{
	state=0;
	RemoteServer="remote";
}
FNR==1{
        pos=index(FILENAME, "." );
        database = substr( FILENAME, 1, (pos - 1) );
}
$1=="create" && $2=="table" {
        tablename = $3
	printf "drop table %s;\n", tablename; 
	state = 1;
}
$1=="CREATE" && $2=="TABLE" {
        tablename = $3
	printf "drop table %s;\n", tablename; 
	state = 1;
}
state==1 && $1==")" {
	state=2;
	print $0; 
	next;
}
state==2 {
	printf "insert into %s select * from %s@%s:%s;\n", 
		tablename, database, RemoteServer, tablename; 
	state=0; 
}
{
	print $0;
}
MKSHAR_EOF


echo x - mkreload.awk
cat >mkreload.awk <<'MKSHAR_EOF'
/create table/{ 
	split($3, a, ".");
	printf "delete from %s;\n", a[2]; 
	printf "load from %s.unl insert into %s;\n", a[2], a[2]; 
}
/CREATE TABLE/{ 
	printf "delete from %s;\n", $3; 
	printf "load from %s.unl insert into %s;\n", $3, $3; 
}
MKSHAR_EOF


echo x - mkrename.awk
cat >mkrename.awk <<'MKSHAR_EOF'
BEGIN {
	Extend="_o";
}
/create table/{
        split($3, a, ".");
	printf "rename table %s to %s%s;\n", a[2], a[2], Extend; 
}
/CREATE TABLE/{
	printf "rename table %s to %s%s;\n", $3, $3, Extend; 
}
MKSHAR_EOF


echo x - mkul_l.awk
cat >mkul_l.awk <<'MKSHAR_EOF'
# Assumes that the input is a file named <databasename>.sql and uses the base 
# filename as the database name in the commands it generates.
#
# Also since there is a delivered utility named 'ul' on many UNIX systems if 
# you have that filter you should probably name ul.ec and the executable 
# something else and change the sprintf below.
#

BEGIN{
	pos=index(FILENAME, "." );
	db=substr( FILENAME, 1, (pos - 1) );
	if (length(db) == 0) db = "DatabaseName";
	intable=0;
	vallist="";
}
/create table/{
        split($3, a, ".");
	tabname = a[2];
	intable = 1;
	next;
}
/CREATE TABLE/{
	tabname = $3;
	intable = 1;
	next;
}
intable == 0 {
	next;
}
$1 ~ /\(/ {
	next;
}
$1 ~ /\)/ {
	cmd = sprintf( "ul -l -c 1000 -d %s -q 'INSERT INTO %-18.18s VALUES (%s)' -f %s.ul \n", db, tabname, vallist, tabname ); 
	print cmd;
	intable = 0;
	vallist = "";
	next;
}
intable == 1 {
	if (length(vallist) == 0) {
		vallist = "?";
	} else {
		vallist = vallist ", ?";
	}
	next;
}
MKSHAR_EOF


echo x - mkul_u.awk
cat >mkul_u.awk <<'MKSHAR_EOF'
# Assumes that the input is a file named <databasename>.sql and uses the base 
# filename as the database name in the commands it generates.
#
# Also since there is a delivered utility named 'ul' on many UNIX systems if 
# you have that filter you should probably name ul.ec and the executable 
# something else and change the sprintf below.
#

BEGIN{
	pos=index(FILENAME, "." );
	db=substr( FILENAME, 1, (pos - 1) );
	if (length(db) == 0) db="DatabaseName";
}
/create table/{
        split($3, a, ".");
	printf "ul -u -c 1000 -d %s -q 'SELECT * FROM %-18.18s' -f %s.ul \n", db, a[2], a[2]; 
}
/CREATE TABLE/{
	printf "ul -u -c 1000 -d %s -q 'SELECT * FROM %-18.18s' -f %s.ul \n", db, $3, $3; 
}
MKSHAR_EOF


echo x - mkunl.awk
cat >mkunl.awk <<'MKSHAR_EOF'
/create table/{
        split($3, a, ".");
	printf "unload to %s.unl select * from %s;\n", a[2], a[2]; 
}
/CREATE TABLE/{
	printf "unload to %s.unl select * from %s;\n", $3, $3; 
}
MKSHAR_EOF