# This is a shell archive.  Remove anything before this line,
# then unpack it by saving it in a file and typing "sh file".
#
# Wrapped by Carlton Doe <hcd@cronus> on Fri Oct  8 14:32:35 1999
#
# This archive contains:
#	Makefile	READ.ME		dbdiff2_new.4gl	dbdiff_old.4gl	
#	dialg_lib.4gl	history		lib_c.c		rds_lib.4gl	
#	server.per	w_log.per	
#

LANG=""; export LANG
PATH=/bin:/usr/bin:/usr/sbin:/usr/ccs/bin:$PATH; export PATH

echo x - Makefile
sed 's/^@//' >Makefile <<'@EOF'
#######################################################################
# Make file for dbdiff2  
#######################################################################

INCLUDE_DIR = -I$(INFORMIXDIR)/incl -I$(<D) 
CFLAGS      = -c $(INCLUDE_DIR)

@.SUFFIXES : .4gl .o .per .frm  .4gl .4go

@.4gl.o :; c4gl $(CFLAGS) -o $@ $<        		    ;\
	rm -f  $(*F).c $(*F).ec 

# implied anyway
#.c.o :; cc $(CFLAGS) $< 

@.per.frm :; - form4gl $< 

@.4gl.4go :; $(INFORMIXDIR)/bin/fglpc $<

all		:	dbdiff2 server.frm w_log.frm

rds_all		:	dbdiff2.4gi server.frm w_log.frm

dbdiff2		:	dbdiff2.o dialg_lib.o lib_c.o
			c4gl -o $@ dbdiff2.o dialg_lib.o lib_c.o

dbdiff2.4gi	:	dbdiff2.4go dialg_lib.4go rds_lib.4go
			cat dbdiff2.4go dialg_lib.4go rds_lib.4go > $@

sharfile	:	READ.ME dbdiff2.4gl
			shar READ.ME history Makefile dialg_lib.4gl \
			dbdiff2.4gl server.per w_log.per lib_c.c rds_lib.4gl \
			> dbdiff2.sh

dbdiff2.o	:	dbdiff2.4gl

dialg_lib.o	:	dialg_lib.4gl

dialg_lib.4go	:	dialg_lib.4gl

rds_lib.4go	:	rds_lib.4gl
@EOF

chmod 664 Makefile

echo x - READ.ME
cat >READ.ME <<'@EOF'

################################################################
			NEW !!!
Added by Carlton Doe  10/8/1999

This archive contains two versions of the dbdiff2 program source code.
   dbdiff2_old.4gl is the latest version that Jack Parker, the original author
        created. This readme file was created as part of that version. 
   dbdiff2_new.4gl is an updated version created by David Williams.  David
        did not provide any additional documentation to this version so I do
        not know what has been specifically added or deleted.

To compile and use this program, change the name of one of the 4GL files to
dbdiff2.4gl then run make.  
################################################################

Enclosed is dbdiff2.4gl and a Makefile to compile it.

dbdiff2 generates SQL to bring one version (-nd) of a database in line with
another (-od).  Please be careful with the SQL generated.  This program has
been tested - but not exhaustively.  Don't run the SQL blind - look at it and
check it first.  This pgm is placed in the public domain without any
warranty whatsoever.  If you use it and it trashes your database - then you
should be more careful - but it is not my problem.

The purpose in offering this code is to allow y'all to find the bugs for me.
So be sure to let me know what goes wrong eh?

By now you have unpacked the file (or you can read the directions about
20 lines up on how to do that).  To compile dbdiff2 type 'make all'.  This
should compile dbdiff2 as well as two forms w_log.frm and server.frm.

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

Syntax is listed at the start of the source and explained at the end of this 
file - or can be obtained by using a 'dbdiff2 -?'.

In case you don't notice - it outputs to /tmp/mod_db.sql.  This
can be changed with a -o switch.

If EITHER of the engines you are working with are SE then use the "-db SE"
switch.

dbdiff2 does not currently resolve columns that are out of order.  The only 
way I can think of doing that without losing data is to unload the table, 
drop, recreate it properly and reload.  I am working on a rebuild option to 
generate SQL to do that.

In the index portion there is no gaurantee in what order I'll get the indices.
I can't think of a way to sort to resolve this... If I need to CLUSTER an
index, and a different index is CLUSTERED on the target engine then there
is no gaurantee that the code will NOT CLUSTER the old index before
CLUSTERing the new index. This would result in a runtime error for the SQL
code.  Accordingly I generate a warning - a comment in the SQL code 
identifying both indices and a message on the tube.

There may be some things that SE doesn't support ('WITH NO LOG'?),
please let me know.

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

Future (prioritized):

Permissions (authority tables)	( in process - haven't done columns yet )
Rebuild table option when column order is hosed.
Full Support for ANSI mode
Form driven option

------------------------------------
Syntax:

dbdiff2 [-db SE|OL]  engine type - SE or Online (Online=default)

		If either of the two engines is an SE engine then use -db SE 
		since it will not support the online specific code.

        -od  old database name

		This is the database you are using as the model.

        -nd  new database name

		This is the database you are generating code to alter.

        [-o]   output file name (default = /tmp/mod_db.sql)

		Self explanatory.

        [-a]   Authority tables (permissions)

		Will generate code to bring table permissions in line 
		with the '-od' database.  (In the future this switch will
		also correct column level permissions as applicable).

        [-u]   Users

		Will generate code to bring user permissions in line 
		with the '-od' database.

        [-s1]  Only do segment 1

		Only unload the files from -od.  This option is meant for those
		who cannot connect to two databases within the code.

        [-s2]  Only do segment 2

		Proceed with generation of code.  This presumes that dbdiff2
		has already been run with the -s1 switch.

        [-dbg] debug mode on

		Currently dbdiff2 is quiet unless it runs into a synonym
		which points to another server - in which case it will
		ask you if you wish the new database to point to a different
		server.  -dbg displays status messages as the program rolls
		along.

        [-t]   table name specification

		Like it sounds.  -t table will generate code for ONE TABLE
		ONLY - where applicable.  It may also be used as a wildcard,
		e.g.: 
			-t 'prefix*' 
		or 
			-t '*suffix'

	[-c]   process constraints. (Default is no)

		will generate code to support constraints

	[-trg] process triggers. (Default is no)

		will generate code to support triggers

	[-spl] process stored procedures. (Default is no)

		will generate code to support stored procedures

	[-q]   Quiet mode - no messages

	[-svr filename] identifies a file of servers.  This is in case your
	       first database uses a different set of servers for synonyms
	       than the second database (our case).  Format of the file is:

		server1|server2

		and means change all references to server1 to server2.  Up
		to 20 different servers are supported, each on their own line.
		Feel free to change the code to handle more (max_servers and
		DEFINE servers ARRAY [20]).  The default file name is 
		"dbd.servers".  Upon completion of a the program any new 
		server changes required are written out to this file.  So
		running the program once will log all server swaps you 
		normally do and then use those forever after.

	[-lg]	Turn on logging (default is dbdiff2.log).  Status messages 
		are written to this logfile.

	[-lgf filename] identifies a file to use instead of dbdiff2.log.
		Also turns on logging.
		
	[-all]  the same as -c -dbg -a -u -spl -trg.

        [?]    usage

		Generate a brief explanatory message.

Final:

	Only two switches are required -od and -nd.  Although the following
	are legal:

		dbdiff2 -od database -s1

	and

		dbdiff2 -nd database -s2

@EOF

chmod 664 READ.ME

echo x - dbdiff2_new.4gl
cat >dbdiff2_new.4gl <<'@EOF'
##############################################################################
#
# dbdiff2.4gl:  A shortened version of dbutil - a utility to generate SQL
#              to make one version of a database match another.
#
# syntax:     dbdiff2 [-db SE|OL] engine type - SE or Online (Online=default)
#                    -od dbname old database name 
#                    -nd dbname new database name
#                    [-o]       output file name (default = /tmp/mod_db.sql)
#                    [-a]       do Authority tables (permissions)
#                    [-c]       DON'T do Constraints 
#                    [-u]       DON'T do Users 
#                    [-s1]      Only do segment 1
#                    [-s2]      Only do segment 2
#                    [-dbg]     debug mode on
#                    [-t spec ]       table name specification
#                    [-trg]     triggers
#                    [-spl]     DON'T do Stored Procedures
#                    [-q]       quiet
#                    [-svr filename]     server file name
#                    [-lg]      turn on logging
#                    [-lgf filename]     log file name
#                    [?]        usage 
#
#       This generates SQL code to either change the schema 
#
#       Placed in the public domain - with no gaurantees whatsoever that it
#       will work.  If it breaks something of yours then you should be more
#       careful but it's NMFP.
#
#       This program was written in stolen hours by me and then added
#       to by the denizens of comp.databases.informix.  It has been tested
#       and generally accepted as a great utility by that newsgroup, but
#       all of them religiously check the SQL it generates before running it.
#
#       I highly recommend that you perform these same checks.
#
#       Inspired by Dave Snyder.
#
#       Jack Parker March, 1994   Kerry Sainsbury November, 1994
#                                       (procedures, triggers)
#
##############################################################################
GLOBALS

   DEFINE  ONLINE_SW,                   # what type of engines
           MODE_ANSI,                   # whether this is an ANSI database 
           SEG1, SEG2,                  # only do part1 or part2.
           DEBUG,                       # debug mode
           AUTH_SW, USER_SW,            # Authority tables, user_sw too
           CONST_SW,                    # Constraints
           TRIG_SW, SPL_SW,             # Triggers and Stored Procedures
           QUIET_SW,                    # no interaction w/ user.
           WRITE_SRV,                   # Server data needs writing switch
           LOG_SW                       # turn on logging (of the program)
           SMALLINT

   DEFINE  db_new, db_old  CHAR(64)     # new and old database names

   DEFINE  ofile_name      CHAR(64)     # output file name

   DEFINE  msg             CHAR(80)     # communicate w/ user

   DEFINE  datatype ARRAY[40] OF CHAR(20), # for coltype conversions
           datetype ARRAY[16] OF CHAR(11), 
           intvray  ARRAY[16] OF RECORD
              start_point SMALLINT,
              end_point SMALLINT
           END RECORD

   DEFINE  sql_strgs ARRAY[50] OF CHAR(80), # temp space for formatting SQL
           sql_idx         SMALLINT     # index to same

   DEFINE  max_parts       SMALLINT     # how many columns to an index

   DEFINE  second_round    SMALLINT     # switch to fix logic prob in psh_strg()

   DEFINE  oplog ARRAY[500] OF CHAR(80), # operations log and index
           op_idx          SMALLINT
      
   DEFINE  tb_name          CHAR(28)     # Table name pattern

   DEFINE  servers  ARRAY [20] OF RECORD        # alternate servers
              old_server    CHAR(20),
              new_server    CHAR(20)
           END RECORD,
           max_servers    SMALLINT

   DEFINE  logfile CHAR(80)             # name of logfile
   DEFINE  serverfle CHAR(80)           # name of flat file w/ server names
                                        # format = "server1|server2"
DEFINE  atcol   SMALLINT,       { column position of left edge of box }
                ident           char(80),
        atrow   SMALLINT,       { row position of top edge of box }
        ncols   SMALLINT,       { computed number of columns in box }
        nrows   SMALLINT,       { computed number of rows in box }
        nlines  SMALLINT,       { number of lines of msg_text }
        textline ARRAY[10] OF CHAR(74)  { separated lines of msg_text }
END GLOBALS


MAIN

   DEFINE i SMALLINT

   CALL hskpng()                # program init

   CALL load_schema()           # get the old schema

   IF SEG2 THEN                 
      CALL chg_tabs()           # table changes

      CALL chg_idx()            # index changes

      IF AUTH_SW THEN
                # have to do columns first, cause there is no way to
                # revoke column level privileges, so I'm going to have
                # to wipe the entire table first and then re-grant.  would
                # be a pain to do tables and then wipe them from colauth()
         CALL chg_auth()        # permissions
      END IF

      IF USER_SW THEN           # user privs.
         CALL chg_user()
      END IF
      
      IF CONST_SW THEN          # constraints
         CALL chg_constr()
      END IF

      IF TRIG_SW THEN           # triggers
         CALL chg_trigs()
      END IF

      IF SPL_SW THEN            # stored procedures
         CALL chg_spl()
      END IF

   END IF

   FINISH REPORT dump_SQL

                        # new server data collected, save it
   IF WRITE_SRV THEN
      START REPORT save_server TO serverfle
      FOR i = 1 TO max_servers
         IF LENGTH(servers[i].old_server) > 0 THEN
            OUTPUT TO REPORT save_server(servers[i].*)
         END IF
      END FOR
      FINISH REPORT save_server
   END IF

                        # close logging file (opened in hskpng())
   IF LOG_SW THEN
      FINISH REPORT logg
   END IF

END MAIN


#######################################################################
# Get the old catalogues
#######################################################################
FUNCTION load_schema()

   DEFINE os_cmd CHAR(220),
          unl_stmt CHAR(500)

   IF SEG1 THEN
      LET msg= "Dumping old schema..."
      CALL op_log(msg)
      DATABASE db_old

                                # systables
      LET unl_stmt = 
           "SELECT tabname, tabid, tabtype ",
           "FROM ""informix"".systables WHERE tabid > 99 ",
           "AND tabname matches '", tb_name clipped, "'"

      UNLOAD TO "systable.unl" unl_stmt

                                # syssyntable (synonyms)
      LET unl_stmt = 
           "SELECT a.tabid, a.servername, a.dbname, a.owner,  ",
           "a.tabname, a.btabid FROM ""informix"".syssyntable a, ",
           """informix"".systables WHERE a.tabid > 99 ",
           "and systables.tabid=a.tabid ",
           "AND systables.tabname matches '", tb_name clipped, "'"

      UNLOAD TO "syssyntab.unl" unl_stmt

                                # syscolumns
      LET unl_stmt = 
           "SELECT a.colname, a.tabid, a.colno, a.coltype, a.collength ",
           "FROM ""informix"".syscolumns a, ",
           """informix"".systables WHERE a.tabid > 99 ",
           "and systables.tabid=a.tabid ",
           "AND tabname matches '", tb_name clipped, "'"

      UNLOAD TO "syscol.unl" unl_stmt

                                # sysviews
      LET unl_stmt = 
           "SELECT a.tabid, a.seqno, a.viewtext ",
           "FROM ""informix"".sysviews a, ""informix"".systables ",
           "WHERE a.tabid > 99 and systables.tabid=a.tabid ",
           "AND tabname matches '", tb_name clipped, "'"

      UNLOAD TO "sysview.unl" unl_stmt

      IF ONLINE_SW THEN

                                # sysindexes
# Note took out the NOT MATCHES ' *' - we need constraints now.
         LET unl_stmt = 
           "SELECT a.idxname, a.tabid, a.idxtype, a.clustered, a.part1, ",
           "a.part2, a.part3, a.part4, a.part5, a.part6, a.part7, a.part8, ",
           "a.part9, a.part10, a.part11, a.part12, a.part13, a.part14, ",
           "a.part15, a.part16 FROM ""informix"".sysindexes a, ",
           """informix"".systables WHERE a.tabid > 99 ",
           "and systables.tabid=a.tabid ",
           "AND tabname matches '", tb_name clipped, "'"

         UNLOAD TO "sysind.unl" unl_stmt
      ELSE

# Note took out the NOT MATCHES ' *' - we need constraints now.
         LET unl_stmt = 
           "SELECT a.idxname, a.tabid, a.idxtype, a.clustered, a.part1, ",
           "a.part2, a.part3, a.part4, a.part5, a.part6, a.part7, a.part8 ",
           "FROM ""informix"".sysindexes a, ",
           """informix"".systables WHERE a.tabid > 99 ",
           "and systables.tabid=a.tabid ",
           "AND tabname matches '", tb_name clipped, "'"

         UNLOAD TO "sysind.unl" unl_stmt

      END IF

      IF AUTH_SW THEN

                                # systabauth and syscolauth
         LET unl_stmt = 
           "SELECT a.grantor, a.grantee, a.tabid, a.tabauth ",
           "FROM ""informix"".systabauth a, ",
           """informix"".systables WHERE a.tabid > 99 ",
           "and systables.tabid=a.tabid ",
           "AND tabname matches '", tb_name clipped, "'"

         UNLOAD TO "systabauth.unl" unl_stmt

         LET unl_stmt = 
           "SELECT a.grantor, a.grantee, a.tabid, a.colno, a.colauth ",
           "FROM ""informix"".syscolauth a, ",
           """informix"".systables WHERE a.tabid > 99 ",
           "and systables.tabid=a.tabid ",
           "AND tabname matches '", tb_name clipped, "'"

         UNLOAD TO "syscolauth.unl" unl_stmt

      END IF

      IF USER_SW THEN

                                # sysusers
         UNLOAD TO "sysusers.unl" 
           SELECT username, usertype FROM "informix".sysusers 

      END IF

                                # constraints
                                # sysconstraints, syscoldepend, syschecks
      IF CONST_SW THEN
         UNLOAD TO "sysconstr.unl" 
           SELECT constrid, constrname, owner, tabid, constrtype, idxname 
             FROM "informix".sysconstraints

         UNLOAD TO "syscoldep.unl" 
           SELECT constrid, tabid, colno
             FROM "informix".syscoldepend

         UNLOAD TO "syschecks.unl" 
           SELECT constrid, type, seqno, checktext
             FROM "informix".syschecks

# Note: updrule, delrule, matchtype, and pendant are reserved for future use
         UNLOAD TO "sysrefs.unl" 
           SELECT constrid, primary, ptabid, updrule, delrule, matchtype, 
                  pendant
             FROM "informix".sysreferences
      END IF

                                # systriggers, systrigbody
      IF TRIG_SW THEN
         LET unl_stmt =
           'SELECT a.trigid, a.trigname, a.owner, a.tabid, a.event, a.old, a.new',
            ' FROM "informix".systriggers a, "informix".systables  ',
            'WHERE systables.tabid > 99 and systables.tabid=a.tabid ',
              'AND tabname matches "', tb_name clipped, '"'

         UNLOAD TO "systrigg.unl" unl_stmt

         LET unl_stmt =
           'SELECT a.trigid, a.datakey, a.seqno, a.data ',
             'FROM "informix".systrigbody a, "informix".systriggers, ',
                  '"informix".systables  ',
            'WHERE systables.tabid > 99 and systables.tabid=systriggers.tabid ',
              'AND systriggers.trigid = a.trigid ',
              'AND tabname matches "', tb_name clipped, '" ',
              'AND datakey IN ("D", "A")'

         UNLOAD TO "systrigb.unl" unl_stmt
      END IF

                                # sysprocedures, sysprocbody
      IF SPL_SW THEN
         LET unl_stmt =
           'SELECT b.procid, b.seqno, b.data ',
             'FROM "informix".sysprocbody b ',
            'WHERE datakey = "T"'

         UNLOAD TO "sysprocb.unl" unl_stmt

         LET unl_stmt =
           'SELECT a.procname, a.owner, a.procid ',
             'FROM "informix".sysprocedures a '

         UNLOAD TO "sysproc.unl" unl_stmt
      END IF

      CLOSE DATABASE    # Think we have all we need from there.
   END IF

                                # Load the old schema into the new database
   IF SEG2 THEN
      LET msg = "Loading old schema..."
      CALL op_log(msg)
      DATABASE db_new

                                # systables
      CREATE TEMP TABLE o_systables (tabname char(18), tabid integer,
                                   tabtype CHAR(1)) WITH NO LOG
      LOAD FROM "systable.unl" INSERT INTO o_systables

                                # syssyntable
      CREATE TEMP TABLE o_syssyntab (tabid integer, servername char(18), 
           dbname char(18), owner char(8), ntabname char(18), btabid integer) 
           WITH NO LOG
      LOAD FROM "syssyntab.unl" INSERT INTO o_syssyntab
   
                                # syscolumns
      CREATE TEMP TABLE o_syscols ( colname char(18), tabid integer, 
         colno smallint, coltype smallint, collength smallint) WITH NO LOG
      LOAD FROM "syscol.unl" INSERT INTO o_syscols
      CREATE UNIQUE INDEX cl1 ON o_syscols(tabid, colname)
   
                                # sysviews
      CREATE TEMP TABLE o_sysviews (tabid integer, seqno smallint, 
         viewtext char(64)) WITH NO LOG
      LOAD FROM "sysview.unl" INSERT INTO o_sysviews
   
                                # sysindexes
      IF ONLINE_SW THEN
         CREATE TEMP TABLE o_sysindexes (idxname char(18), 
            tabid integer, idxtype char(1), clustered char(1), part1 smallint,
            part2 smallint, part3 smallint, part4 smallint, part5 smallint,
            part6 smallint, part7 smallint, part8 smallint, part9 smallint,
            part10 smallint, part11 smallint, part12 smallint,
            part13 smallint, part14 smallint, part15 smallint, part16 smallint)
            WITH NO LOG
      ELSE
         CREATE TEMP TABLE o_sysindexes (idxname char(18), 
            tabid integer, idxtype char(1), clustered char(1), part1 smallint,
            part2 smallint, part3 smallint, part4 smallint, part5 smallint,
            part6 smallint, part7 smallint, part8 smallint) WITH NO LOG
      END IF
      LOAD FROM "sysind.unl" INSERT INTO o_sysindexes

                                # systabauth, syscolauth
      IF AUTH_SW THEN
         CREATE TEMP TABLE o_systabauth (grantor CHAR(8), grantee CHAR(8), 
                           tabid INTEGER, tabauth CHAR(8)) WITH NO LOG
         LOAD FROM "systabauth.unl" INSERT INTO o_systabauth

         CREATE TEMP TABLE o_syscolauth (grantor CHAR(8), grantee CHAR(8), 
               tabid INTEGER, colno SMALLINT, colauth CHAR(3)) WITH NO LOG
         LOAD FROM "syscolauth.unl" INSERT INTO o_syscolauth
      END IF

      IF USER_SW THEN           # user privs.
         CREATE TEMP TABLE o_sysusers (username CHAR(8), usertype CHAR(1)) 
               WITH NO LOG
         LOAD FROM "sysusers.unl" INSERT INTO o_sysusers
      END IF
   
                                # sysconstraints, syscoldepend, syschecks
      IF CONST_SW THEN
         CREATE TEMP TABLE o_sysconstraints (constrid integer, 
                constrname CHAR(18), owner CHAR(8), tabid INTEGER, 
                constrtype CHAR(1), idxname CHAR(18)) WITH NO LOG
         LOAD FROM "sysconstr.unl" INSERT INTO o_sysconstraints

         CREATE TEMP TABLE o_syscoldepend (constrid integer, tabid integer,
                        colno smallint) WITH NO LOG
         LOAD FROM "syscoldep.unl" INSERT INTO o_syscoldepend

         CREATE TEMP TABLE o_syschecks (constrid integer, type char(1), seqno 
                SMALLINT, checktext CHAR(32)) WITH NO LOG
         LOAD FROM "syschecks.unl" INSERT INTO o_syschecks

# Note: updrule, delrule, matchtype, and pendant are reserved for future use
         CREATE TEMP TABLE o_sysreferences (constrid integer, prim integer,
                ptabid INTEGER, updrule CHAR(1), delrule CHAR(1), matchtype
                CHAR(1), pendant CHAR(1)) WITH NO LOG
         LOAD FROM "sysrefs.unl" INSERT INTO o_sysreferences
      END IF

                                # systriggers, systrigbody
      IF TRIG_SW THEN
         CREATE TEMP TABLE o_systriggers (trigid integer, trigname char(18),
            owner char(18), tabid int, event char(1), old char(18), 
            new char(18)) WITH NO LOG
         LOAD FROM "systrigg.unl" INSERT INTO o_systriggers

         CREATE TEMP TABLE o_systrigbody (trigid integer, datakey char(1),
            seqno int, data char(256)) WITH NO LOG
         LOAD FROM "systrigb.unl" INSERT INTO o_systrigbody
      END IF

                                # sysprocgers, sysprocbody
      IF SPL_SW THEN
         CREATE TEMP TABLE o_sysprocbody (procid integer, seqno int, 
            data char(256)) WITH NO LOG
         LOAD FROM "sysprocb.unl" INSERT INTO o_sysprocbody

         CREATE TEMP TABLE o_sysprocedures (procname char(18), owner char(8),
            procid int) WITH NO LOG
         LOAD FROM "sysproc.unl" INSERT INTO o_sysprocedures
      END IF
                
                                # clean up
         LET os_cmd = 
          "rm -f systable.unl syssyntab.unl syscol.unl sysind.unl sysview.unl ",
                "systabauth.unl syscolauth.unl sysusers.unl, sysconstr.unl ",
                "syscoldep.unl syschecks.unl sysrefs.unl systrigg.unl ",
                "systrigb.unl sysprocb.unl sysproc.unl"
         RUN os_cmd
   
      END IF    # SEG2

END FUNCTION

FUNCTION chg_tabs()

   DEFINE tabrec RECORD                 # tables
             tabname      CHAR(18),
             tabid        INTEGER,
             tabtype      CHAR(1)
          END RECORD,
          synrec RECORD                 # synonyms
             servername CHAR(18), 
             dbname     CHAR(18), 
             ntabname   CHAR(18), 
             btabid     INTEGER
          END RECORD,
          colrec RECORD                 # columns
             colname      CHAR(18),
             collength    SMALLINT, 
             coltype      SMALLINT, 
             colno        SMALLINT
          END RECORD,                   # comparison array
          chk_cols ARRAY[500] OF RECORD
             o_colname   CHAR(18),
             o_coltype   SMALLINT,
             o_collength SMALLINT,
             o_colno     SMALLINT,
             n_colname   CHAR(18),
             n_coltype   SMALLINT,
             n_collength SMALLINT,
             n_colno     SMALLINT
          END RECORD,
          exp_col SMALLINT,
          tmp_strg CHAR(80),
          strg CHAR(80),
          i, j, k          SMALLINT     # junk variables


   LET sql_idx = 1
##################################
   LET msg =  "Now producing DROPs..."
   CALL op_log(msg)
                                                # Drop tables
                                                # pretty basic
   DECLARE drop_curs CURSOR FOR
   SELECT tabname, tabtype
     FROM "informix".systables
    WHERE tabid > 99 AND tabname MATCHES tb_name
      AND NOT EXISTS
         (SELECT tabname FROM o_systables
           WHERE "informix".systables.tabname = o_systables.tabname)

   FOREACH drop_curs INTO tabrec.tabname, tabrec.tabtype
      CASE tabrec.tabtype

      WHEN 'T'                                  # Table
         LET strg = "DROP TABLE ", 
                    tabrec.tabname clipped, 
                    ";"
         OUTPUT TO REPORT dump_sql(strg)
         OUTPUT TO REPORT dump_sql("")          # blank line

      WHEN 'S'                                  # Synonym
         LET strg = "DROP SYNONYM ", 
                    tabrec.tabname clipped, 
                    ";"
         OUTPUT TO REPORT dump_sql(strg)
         OUTPUT TO REPORT dump_sql("")          # blank line

      WHEN 'V'                                  # View
         LET strg = "DROP VIEW ", 
                    tabrec.tabname clipped, 
                    ";"
         OUTPUT TO REPORT dump_sql(strg)
         OUTPUT TO REPORT dump_sql("")          # blank line

      #WHEN 'L'                                 # SE Log

      #WHEN 'P'                                 # Private synonym 
                                                # or synonym in ANSI
      END CASE
   END FOREACH
   FREE drop_curs

##################################
   LET msg =  "Now producing CREATEs..."
   CALL op_log(msg)
                                                # Add tables
                                                # format create statements
   DECLARE add_curs CURSOR FOR
   SELECT tabname, tabid, tabtype
     FROM o_systables
    WHERE tabname MATCHES tb_name AND NOT EXISTS
         (SELECT tabname FROM "informix".systables
           WHERE "informix".systables.tabname = o_systables.tabname)

   PREPARE c_prp FROM
   "SELECT colname, collength, coltype, colno FROM o_syscols WHERE tabid = ? ORDER BY colno"
   DECLARE get_col CURSOR FOR c_prp

   PREPARE vc FROM
   "SELECT viewtext, seqno FROM o_sysviews WHERE tabid = ? ORDER BY seqno"

   DECLARE view_curs CURSOR FOR vc

   FOREACH add_curs INTO tabrec.tabname, tabrec.tabid, tabrec.tabtype

      CASE tabrec.tabtype
         WHEN 'T'                       # table

            LET tmp_strg = "CREATE TABLE ", 
                           tabrec.tabname clipped, 
                           " ("

            OPEN get_col USING tabrec.tabid     # all columns for this table

            FOREACH get_col INTO colrec.colname, 
                                 colrec.collength, 
                                 colrec.coltype,
                                 colrec.colno

                                      # format the string for this column
               LET strg = colrec.colname clipped, 
                          column 27, 
                          col_cnvrt(colrec.coltype,colrec.collength)
               CALL psh_strg(strg,tmp_strg,",")
      
            END FOREACH

               CALL psh_strg("END","",");")

         WHEN 'S'                       # synonym

            SELECT servername, dbname, ntabname, btabid
              INTO synrec.servername, synrec.dbname, synrec.ntabname, 
                   synrec.btabid
              FROM o_syssyntab
             WHERE tabid = tabrec.tabid

            IF LENGTH(synrec.servername) > 0 THEN

               LET strg = "CREATE SYNONYM ", 
                           tabrec.tabname clipped, " FOR ", 
                           synrec.dbname clipped, "@",
                           get_server(synrec.servername, 
                                      tabrec.tabname) clipped, ":",
                           synrec.ntabname clipped, ";"

            ELSE IF LENGTH(synrec.dbname) > 0 THEN
   
                  LET strg = "CREATE SYNONYM ", 
                             tabrec.tabname clipped, " FOR ", 
                             synrec.dbname clipped, ":",
                             synrec.ntabname clipped, ";"

               ELSE     # in local database
   
                  SELECT tabname
                    INTO synrec.ntabname
                    FROM o_systables
                   WHERE tabid = synrec.btabid

                  LET strg = "CREATE SYNONYM ", 
                             tabrec.tabname clipped, " FOR ", 
                             synrec.ntabname clipped, ";"
               END IF
            END IF

            OUTPUT TO REPORT dump_sql(strg)
            OUTPUT TO REPORT dump_sql("")               # blank line

         WHEN 'V'                       # View

            LET strg = ""
            OPEN view_curs USING tabrec.tabid
            FOREACH view_curs INTO tmp_strg, i
               LET strg=strg clipped, tmp_strg
               CALL clip_strg(strg) RETURNING tmp_strg, strg
               CALL psh_strg(tmp_strg,"","")
            END FOREACH
            CALL psh_strg("END","",strg)  # need to reset the counter
         
         #WHEN 'P'                      # private synonym or ANSI syonym

         #WHEN 'L'                      # SE log

      END CASE
   END FOREACH
   FREE add_curs

##################################
# I am not very happy with this section and would appreciate alternate ideas.
##################################
   LET msg =  "Now producing ALTERs..."
   CALL op_log(msg)
                                        # Alter tables
                                        # twisted - have to compare
                                        # how tables look.

                                        # foreach table in both databases
                                        # dont need sort
   DECLARE table_list CURSOR FOR
   SELECT o_systables.tabid, 
          o_systables.tabname
     FROM o_systables, "informix".systables
    WHERE "informix".systables.tabname = o_systables.tabname
      AND "informix".systables.tabtype = 'T'
      AND o_systables.tabtype = 'T'
      AND o_systables.tabname MATCHES tb_name

   PREPARE n_c FROM
   'SELECT colname, colno, coltype, collength FROM "informix".syscolumns, "informix".systables WHERE tabname = ? AND "informix".syscolumns.tabid = "informix".systables.tabid ORDER BY colno'

   DECLARE n_cols CURSOR FOR n_c

   FOREACH table_list INTO tabrec.tabid, tabrec.tabname

   LET tmp_strg = "ALTER TABLE ", tabrec.tabname clipped

   FOR i = 1 TO 500
      INITIALIZE chk_cols[i].* TO NULL
   END FOR

# Note: j is not a junk variable in this loop.  j points to last valid
# column info found.  i is junk

                                        # load old columns into array
      LET j = 1
      OPEN get_col USING tabrec.tabid
      FOREACH get_col INTO chk_cols[j].o_colname, chk_cols[j].o_collength, 
                          chk_cols[j].o_coltype, chk_cols[j].o_colno
         LET j = j + 1
      END FOREACH                       # j points 1 past

                                        # load new columns - find match in array
      OPEN n_cols USING tabrec.tabname
      FOREACH n_cols INTO chk_cols[j].n_colname, chk_cols[j].n_colno, 
                          chk_cols[j].n_coltype, chk_cols[j].n_collength
         FOR i = 1 TO j
            IF chk_cols[i].o_colname = chk_cols[j].n_colname THEN
               LET chk_cols[i].n_colname   = chk_cols[j].n_colname
               LET chk_cols[i].n_colno     = chk_cols[j].n_colno
               LET chk_cols[i].n_coltype   = chk_cols[j].n_coltype
               LET chk_cols[i].n_collength = chk_cols[j].n_collength
               INITIALIZE chk_cols[j].* TO NULL   # clear it
               EXIT FOR
            END IF
         END FOR
         IF i >= j THEN                 # didn't find a match, j --> valid row
            LET j = j + 1               # j --> NULL row
            IF j > 500 THEN 
              LET msg="Over 500 column differences for this table, bailing out"
              CALL op_log(msg)
              LET msg="Table: ", tabrec.tabname clipped
              CALL disp_err()
              EXIT FOREACH
            END IF
         END IF
      END FOREACH

  LET j=j-1                             # j now --> last valid row

###################################################################
# We now have a loaded array of matching columns for this table.  Loop
# through this array and check to make sure all columns  are the same
# AND IN THE SAME ORDER.  If not - then we need to fix it.
###################################################################

   LET exp_col = 1                      # expected column number
                                        # loop through array:
   FOR i = 1 TO j
                                        # if colname null in old then 
                                        # drop column   

      IF chk_cols[i].o_colname IS NULL THEN

         LET strg = "DROP (", chk_cols[i].n_colname clipped, ")"
         CALL psh_strg(strg, tmp_strg, ",")

         LET exp_col = exp_col + 1      # keep track of expected colno

                                        # if colname null in new then add column
      ELSE IF chk_cols[i].n_colname IS NULL THEN

 # Have a good look for a column to ADD BEFORE...               #KJS

             FOR k = i TO j                                     #KJS
                IF chk_cols[k].n_colname IS NOT NULL THEN       #KJS
                   EXIT FOR                                     #KJS
                END IF                                          #KJS
             END FOR                                            #KJS
 # Was not possible to ADD a new column BEFORE the first column with the #KJS
 # "IF i > 1" condition included      -- KJS                    #KJS
 #           IF i > 1 AND LENGTH(chk_cols[k].n_colname) != 0 THEN  #KJS
             IF LENGTH(chk_cols[k].n_colname) != 0 THEN         #KJS
                 LET strg =
                   col_cnvrt(chk_cols[i].o_coltype, chk_cols[i].o_collength)
                 LET strg = "ADD (",
                     chk_cols[i].o_colname clipped,
                    column 27, strg clipped,
                    " BEFORE ", chk_cols[k].n_colname CLIPPED, ")"  #KJS

#            LET k = i + 1
#            IF i > 1 AND LENGTH(chk_cols[k].n_colname) != 0 THEN
#               LET strg = 
#                  col_cnvrt(chk_cols[i].o_coltype, chk_cols[i].o_collength)
#               LET strg = "ADD (", 
#                   chk_cols[i].o_colname clipped, 
#                   column 27, strg clipped, 
#                   " BEFORE ", chk_cols[k].n_colname, ")"
            ELSE
               LET strg = "ADD (", 
                   chk_cols[i].o_colname clipped, 
                   column 27, 
                   col_cnvrt(chk_cols[i].o_coltype,chk_cols[i].o_collength)
                        clipped, ")"
            END IF

            CALL psh_strg(strg, tmp_strg, ",")

         ELSE                           # o_colname = n_colname - 
                                        # check type/length/colno

#
# FIX THIS.  In this case what we should do is generate code to unload the
#            table, drop it, recreate it, reload it.  Problem is that we
#            don't know how far into creating the table we are.  psh_strg
#            may have already flushed the first 50+ columns.
#
            IF chk_cols[i].n_colno != exp_col THEN
                                        # wrong order !
               LET msg = "Warning:", tabrec.tabname clipped, 
                        " - Column in the wrong order - I'm confused."
               CALL op_log(msg)
               LET msg = "Column :", chk_cols[i].o_colname clipped
               CALL op_log(msg)

            END IF

            IF chk_cols[i].o_coltype   != chk_cols[i].n_coltype
            OR chk_cols[i].o_collength != chk_cols[i].n_collength THEN
               LET strg = "MODIFY (", 
                          chk_cols[i].n_colname clipped, 
                          column 27,
                          col_cnvrt(chk_cols[i].o_coltype, 
                                    chk_cols[i].o_collength) clipped, ")"
               CALL psh_strg(strg, tmp_strg, ",")
            END IF

            LET exp_col = exp_col + 1   # expected column number

         END IF
      END IF
   END FOR                              # columns
   IF sql_idx > 1 THEN
      CALL psh_strg("END", "", ";")
   END IF

   END FOREACH                          # table_list

   FREE get_col
END FUNCTION


###########################################################################
# Change indices
# note this routine pays no attention to the index name, but compares the
# indices based on the columns, and the order they are in.  So if indices
# have different names - it doesn't care.
###########################################################################
FUNCTION chg_idx()

   DEFINE sel_stmt CHAR(450),
          i  SMALLINT,
          idxrec RECORD 
             tabname    CHAR(18),
             idxname   CHAR(18), 
             tabid     INTEGER, 
             idxtype   CHAR(1), 
             clustered CHAR(1)
         END RECORD,
         p_colname CHAR(18),
         strg CHAR(306),                # 16*18 = 288 + 18*whitespace = 306
         last_idx, curr_idx RECORD
            tabname    CHAR(18),
            ver        SMALLINT, 
            idxname    CHAR(18), 
            descr      CHAR(306)
         END RECORD

                                        # get old indices
                                        # WHERE TABLES ALREADY EXIST
                                        # new tables will be handled shortly
                                        # dropped tables wont show in this list
LET msg =  "Getting old indices..."
CALL op_log(msg)

   IF ONLINE_SW THEN
      LET max_parts = 16
   ELSE
      LET max_parts = 8
   END IF

   LET sel_stmt = 
      'SELECT o_systables.tabname, idxname, o_sysindexes.tabid, idxtype, ',
              'clustered ',
        'FROM o_sysindexes, o_systables, "informix".systables ',
       'WHERE "informix".systables.tabname = o_systables.tabname ',
         'AND o_systables.tabid = o_sysindexes.tabid ',
         'AND o_systables.tabname MATCHES "', tb_name CLIPPED, '" ',
         'AND idxname NOT MATCHES " *"'

   PREPARE i_c FROM sel_stmt
   DECLARE o_idx_curs CURSOR FOR i_c

                                        # resolve indices into names
                                        # and load into temp table

   CREATE TEMP TABLE cmp_idx(tabname char(18), ver smallint, idxname char(18), 
                             descr char(306)) WITH NO LOG

   FOREACH o_idx_curs INTO idxrec.*
      LET msg = "idx_parts 1 - ",idxrec.idxname
      CALL op_log(msg)
      CALL idx_parts(idxrec.idxname, 0) RETURNING strg
      INSERT INTO cmp_idx (tabname, ver, idxname, descr) 
                   VALUES (idxrec.tabname, 1, idxrec.idxname, strg)
   END FOREACH
      
   FREE o_idx_curs

                                        # get new indices

   LET msg =  "Getting new indices..."
   CALL op_log(msg)

   IF ONLINE_SW THEN                    # current length of sel_stmt = 424 
      LET sel_stmt = 
      'SELECT "informix".systables.tabname, idxname, ',
             '"informix".sysindexes.tabid, idxtype, clustered ',
        'FROM "informix".sysindexes, o_systables, "informix".systables ',
       'WHERE "informix".systables.tabname = o_systables.tabname ',
         'AND "informix".systables.tabid = "informix".sysindexes.tabid ',
         'AND idxname NOT MATCHES " *"'
   ELSE
      LET sel_stmt = 
      'SELECT "informix".systables.tabname, idxname, ',
             '"informix".sysindexes.tabid, idxtype, clustered ',
        'FROM "informix".sysindexes, o_systables, "informix".systables ',
       'WHERE "informix".systables.tabname = o_systables.tabname ',
         'AND "informix".systables.tabid = "informix".sysindexes.tabid ',
         'AND idxname NOT MATCHES " *"'
   END IF

   PREPARE i_c2 FROM sel_stmt
   DECLARE n_idx_curs CURSOR FOR i_c2

                                        # resolve indices into names
                                        # and load into temp table

   FOREACH n_idx_curs INTO idxrec.*
      LET msg = "idx_parts 2 - ",idxrec.idxname
      CALL op_log(msg)
      CALL idx_parts(idxrec.idxname,1) RETURNING strg
      INSERT INTO cmp_idx (tabname, ver, idxname, descr) 
                   VALUES (idxrec.tabname, 2, idxrec.idxname, strg)
   END FOREACH

   FREE n_idx_curs

###########################################################################
# Now we've built a table of indices,  common indices should have identical
# descr strings.  Those that don't need to get fixed.  Just a touch of brute
# force...  Pity we can't use a GROUP BY, but we need the idxname still.
###########################################################################

# read each index, put the ver=1 ones into last_idx (they will be first)
# put the others into curr_idx.
# whenever curr_idx.ver=2 then we should have both loaded.  If not then
# last_idx.* is null - index doesn't exist on old
# curr_idx.* is null - we've just read a ver=1 and last_idx is NOT NULL

LET msg =  "Comparing indices..."
CALL op_log(msg)
   DECLARE idx_curs CURSOR FOR
      SELECT tabname, ver, idxname, descr
        FROM cmp_idx
       ORDER BY tabname, descr, ver

   INITIALIZE last_idx.* TO NULL
   FOREACH idx_curs INTO curr_idx.*

      CASE curr_idx.ver
         WHEN 1
            IF LENGTH(last_idx.idxname) > 0 THEN
               CALL create_idx(last_idx.idxname)
               LET last_idx.* = curr_idx.*
            END IF
            LET last_idx.* = curr_idx.*
         WHEN 2
            IF LENGTH(last_idx.descr) = 0 THEN
               LET msg = "DROP INDEX ", curr_idx.idxname clipped, ";"
               OUTPUT TO REPORT dump_sql(msg)
               OUTPUT TO REPORT dump_sql("")
               CALL op_log(msg)
            ELSE IF last_idx.descr != curr_idx.descr THEN
                  LET msg = "DROP INDEX ", curr_idx.idxname clipped, ";"
                  OUTPUT TO REPORT dump_sql(msg)
                  OUTPUT TO REPORT dump_sql("")
                  CALL op_log(msg)
                  CALL create_idx(last_idx.idxname)
                  INITIALIZE last_idx.* TO NULL
               ELSE             # same index
                  CALL check_idx(last_idx.idxname, curr_idx.idxname)
                  INITIALIZE last_idx.* TO NULL
               END IF
            END IF
      END CASE

   END FOREACH

   FREE idx_curs
      
                                        # new indices
   DECLARE new_idx CURSOR FOR
   SELECT idxname
     FROM o_systables, o_sysindexes
    WHERE NOT EXISTS
         (SELECT tabname FROM "informix".systables
           WHERE "informix".systables.tabname = o_systables.tabname)
      AND tabtype = 'T'
      AND o_systables.tabid = o_sysindexes.tabid
      AND idxname NOT MATCHES ' *'

   FOREACH new_idx INTO last_idx.idxname
      CALL create_idx(last_idx.idxname)
   END FOREACH
   FREE new_idx
      
END FUNCTION


########################################################################
# create indices
########################################################################
FUNCTION create_idx(p_idxname)
# bummer, don't want to use that descr line since it's too long

   DEFINE p_idxname char(18),
          idxrec RECORD 
             tabid     INTEGER, 
             tabname   CHAR(18),
             idxtype   CHAR(1), 
             clustered CHAR(1)
          END RECORD,
          parts ARRAY [16] OF SMALLINT,
          i            SMALLINT,
          p_colname    CHAR(24),
          desc_sw      SMALLINT,
          strg, tmp_strg CHAR(500),
          idx_strg     CHAR(500)

   LET msg = "idx_parts 3 - ",p_idxname
   CALL op_log(msg)
   CALL idx_parts(p_idxname,0) RETURNING idx_strg
   IF ONLINE_SW THEN
      SELECT o_systables.tabid, o_systables.tabname, idxtype, clustered
      INTO idxrec.* 
      FROM o_sysindexes, o_systables
     WHERE idxname = p_idxname
       AND o_sysindexes.tabid = o_systables.tabid
   ELSE
      SELECT o_systables.tabid, o_systables.tabname, idxtype, clustered
      INTO idxrec.*
      FROM o_sysindexes, o_systables
     WHERE idxname = p_idxname
       AND o_sysindexes.tabid = o_systables.tabid
   END IF

                                        # unique?
   IF idxrec.idxtype = 'U' THEN 
      LET tmp_strg = "CREATE UNIQUE"
   ELSE
      LET tmp_strg = "CREATE"
   END IF

                                        # clustered?
   IF idxrec.clustered = 'C' THEN
      SELECT idxname
        INTO p_colname                  # re-using other var, ignore name
        FROM "informix".sysindexes
       WHERE tabid = idxrec.tabid
         AND clustered = 'C'

# FIX THIS - EXCLUDE CURRENT INDEX?  CAN'T BECAUSE OF NAME?
# Commented out until a better solution is found.  There is no garauntee
# that the index in question is not a) the same one b) still existant.
#
#      IF status != NOTFOUND THEN       # uh-oh, already a clustered index
#         LET strg = "ALTER INDEX ", p_colname clipped, " TO NOT CLUSTER;"
#         OUTPUT TO REPORT dump_sql(strg)
#      END IF

      LET tmp_strg = tmp_strg clipped, " CLUSTER"
   END IF

                                        # tack on index name
   LET tmp_strg = tmp_strg clipped, " INDEX ", p_idxname clipped,
                  " ON ", idxrec.tabname clipped, " (", idx_strg clipped, ");"

                                        # add columns
   CALL fold_and_push(tmp_strg, 1)

END FUNCTION


###########################################################################
# Same index fields - are they the same type of index?  If not fix.
###########################################################################
FUNCTION check_idx(o_idx, n_idx)

   DEFINE o_idx, n_idx CHAR(18),
          o_clust, o_type, n_clust, n_type CHAR(1)

   SELECT clustered, idxtype
     INTO o_clust, o_type
     FROM o_sysindexes
    WHERE idxname = o_idx

   SELECT clustered, idxtype
     INTO n_clust, n_type
     FROM "informix".sysindexes
    WHERE idxname = n_idx

# cluster - there had better not be another.
IF o_clust != n_clust AND n_clust = 'C' THEN            # bummer
                                                        # WHAT index jack?
                                                        # find it!
   LET msg="{Warning - this command must be run before any other CLUSTER on ",
           "this table}"
   OUTPUT TO REPORT dump_sql(msg)
   CALL op_log(msg)
   LET msg = "ALTER INDEX ", n_idx clipped, " TO NOT CLUSTER;"
   OUTPUT TO REPORT dump_sql(msg)
   CALL op_log(msg)
   OUTPUT TO REPORT dump_sql("")
END IF

# unique/duplicate - they better be the same.
IF o_type != n_type THEN
   LET msg = "DROP INDEX ", n_idx clipped, ";"
   CALL op_log(msg)
   OUTPUT TO REPORT dump_sql(msg)
   OUTPUT TO REPORT dump_sql("")
   CALL create_idx(o_idx)
END IF

END FUNCTION


###########################################################################
# Change users
# Compare contents of sysusers and o_sysusers. 
#    drop old or different.  Add new or different
###########################################################################
FUNCTION chg_user()

DEFINE
   usr_rec RECORD
      username CHAR(8),
      usertype CHAR(1)
   END RECORD,
   sql_strg CHAR(80)

   DECLARE drop_user CURSOR FOR 
   SELECT username, usertype 
     FROM sysusers
    WHERE NOT EXISTS (SELECT username FROM o_sysusers
                      WHERE o_sysusers.username = sysusers.username)
    UNION
   SELECT username, usertype 
     FROM sysusers
    WHERE EXISTS (SELECT username FROM o_sysusers
                      WHERE o_sysusers.username = sysusers.username
                        AND o_sysusers.usertype != sysusers.usertype)

   DECLARE add_user CURSOR FOR 
   SELECT username, usertype 
     FROM o_sysusers
    WHERE NOT EXISTS (SELECT username FROM sysusers
                      WHERE o_sysusers.username = sysusers.username)
    UNION
   SELECT username, usertype 
     FROM o_sysusers
    WHERE EXISTS (SELECT username FROM sysusers
                      WHERE o_sysusers.username = sysusers.username
                        AND o_sysusers.usertype != sysusers.usertype)

   INITIALIZE usr_rec.* TO NULL

   CALL op_log("Generating REVOKE DATABASE PRIVILEGES")
   FOREACH drop_user INTO usr_rec.username, usr_rec.usertype
      CASE usr_rec.usertype
         WHEN "D"
            LET sql_strg= "REVOKE DBA FROM ", usr_rec.username CLIPPED, ";"
         WHEN "C"
            LET sql_strg= "REVOKE CONNECT FROM ", usr_rec.username CLIPPED, ";"
         WHEN "R"
            LET sql_strg= "REVOKE RESOURCE FROM ", usr_rec.username CLIPPED, ";"
      END CASE
      CALL psh_strg("END",sql_strg,";")
   END FOREACH

   INITIALIZE usr_rec.* TO NULL

   CALL op_log("Generating GRANT DATABASE PRIVILEGES")
   FOREACH add_user INTO usr_rec.username, usr_rec.usertype
      CASE usr_rec.usertype
         WHEN "D"
            LET sql_strg = "GRANT DBA TO ", usr_rec.username CLIPPED, ";"
         WHEN "C"
            LET sql_strg = "GRANT CONNECT TO ", usr_rec.username CLIPPED, ";"
         WHEN "R"
            LET sql_strg = "GRANT RESOURCE TO ", usr_rec.username CLIPPED, ";"
      END CASE
      CALL psh_strg("END",sql_strg,"")
   END FOREACH

   FREE drop_user
   FREE add_user

END FUNCTION


###########################################################################
# Change table authority/permissions
# compare tables and drop old or different.  Add new or different
###########################################################################

FUNCTION chg_auth()

DEFINE fn_rec RECORD
          grantor CHAR(8), 
          grantee CHAR(8), 
          tabauth CHAR(8), 
          tabname CHAR(18), 
          ntabauth CHAR(8)
       END RECORD,
   sql_strg CHAR(80)

# Problem, tabid is not guaranteed to be the same between databases (and is
# really not important in that context) - we need tabname

   DECLARE drop_tab CURSOR FOR
   SELECT grantee, tabname
     FROM systabauth, systables
    WHERE systabauth.tabid = systables.tabid
      AND USER = grantor                # can't revoke those you didn't give
      AND NOT EXISTS (SELECT grantor, grantee, tabname
                        FROM o_systabauth, o_systables
                       WHERE o_systabauth.tabid = o_systables.tabid
                         AND o_systabauth.grantor = systabauth.grantor 
                         AND o_systabauth.grantee = systabauth.grantee 
                         AND o_systables.tabname = systables.tabname)
    UNION
   SELECT grantee, tabname
     FROM systabauth, systables
    WHERE systabauth.tabid = systables.tabid
      AND USER = grantor                # can't revoke those you didn't give
      AND EXISTS (SELECT grantor, grantee, tabname
                    FROM o_systabauth, o_systables
                   WHERE o_systabauth.tabid = o_systables.tabid
                     AND o_systabauth.grantor = systabauth.grantor 
                     AND o_systabauth.grantee = systabauth.grantee 
                     AND o_systables.tabname = systables.tabname
                     AND o_systabauth.tabauth != systabauth.tabauth)

   DECLARE add_tab CURSOR FOR
   SELECT grantor, grantee, tabauth, tabname
     FROM o_systabauth, o_systables
    WHERE o_systabauth.tabid = o_systables.tabid
      AND NOT EXISTS (SELECT grantor, grantee, tabname
                        FROM systabauth, systables
                       WHERE systabauth.tabid = systables.tabid
                         AND o_systabauth.grantor = systabauth.grantor 
                         AND o_systabauth.grantee = systabauth.grantee 
                         AND o_systables.tabname = systables.tabname)
   UNION
   SELECT grantor, grantee, tabauth, tabname
     FROM o_systabauth, o_systables
    WHERE o_systabauth.tabid = o_systables.tabid
      AND EXISTS (SELECT grantor, grantee, tabname
                    FROM systabauth, systables
                   WHERE systabauth.tabid = systables.tabid
                     AND o_systabauth.grantor = systabauth.grantor 
                     AND o_systabauth.grantee = systabauth.grantee 
                     AND o_systables.tabname = systables.tabname
                     AND o_systabauth.tabauth != systabauth.tabauth)

   INITIALIZE fn_rec.* TO NULL

   CALL op_log("Generating REVOKE TABLE PRIVILEGES")
   FOREACH drop_tab INTO fn_rec.grantee, fn_rec.tabname
      LET sql_strg = "REVOKE ALL ON ", fn_rec.tabname clipped, " FROM ",
                      fn_rec.grantee clipped
      CALL psh_strg("END",sql_strg,";")
   END FOREACH

   INITIALIZE fn_rec.* TO NULL

   CALL op_log("Generating ADD TABLE PRIVILEGES")
   FOREACH add_tab INTO fn_rec.grantor, fn_rec.grantee, fn_rec.tabauth, 
                        fn_rec.tabname

# oh boy this is going to be fun! 8*2-1 values to check here.

   IF fn_rec.tabauth[1,1] MATCHES "[Ss]" THEN
      CALL priv_grant("SELECT", fn_rec.tabname, fn_rec.grantee, 
                      fn_rec.grantor, fn_rec.tabauth[1,1])
   END IF

   IF fn_rec.tabauth[2,2] MATCHES "[Uu]" THEN
      CALL priv_grant("UPDATE", fn_rec.tabname, fn_rec.grantee, 
                      fn_rec.grantor, fn_rec.tabauth[2,2])
   END IF

   IF fn_rec.tabauth[4,4] MATCHES "[Ii]" THEN
      CALL priv_grant("INSERT", fn_rec.tabname, fn_rec.grantee, 
                      fn_rec.grantor, fn_rec.tabauth[4,4])
   END IF

   IF fn_rec.tabauth[5,5] MATCHES "[Dd]" THEN
      CALL priv_grant("DELETE", fn_rec.tabname, fn_rec.grantee, 
                      fn_rec.grantor, fn_rec.tabauth[5,5])
   END IF

   IF fn_rec.tabauth[6,6] MATCHES "[Xx]" THEN
      CALL priv_grant("INDEX", fn_rec.tabname, fn_rec.grantee, 
                      fn_rec.grantor, fn_rec.tabauth[6,6])
   END IF

   IF fn_rec.tabauth[7,7] MATCHES "[Aa]" THEN
      CALL priv_grant("ALTER", fn_rec.tabname, fn_rec.grantee, 
                      fn_rec.grantor, fn_rec.tabauth[7,7])
   END IF

   IF fn_rec.tabauth[8,8] MATCHES "[Rr]" THEN
      CALL priv_grant("REFERENCES", fn_rec.tabname, fn_rec.grantee, 
                      fn_rec.grantor, fn_rec.tabauth[8,8])
   END IF

   END FOREACH

   FREE drop_tab
   FREE add_tab

END FUNCTION


###########################################################################
# Generate grant statement
###########################################################################
FUNCTION priv_grant(priv, tabname, grantee, grantor, g_opt)

DEFINE
        priv CHAR(10),
        tabname CHAR(18),
        grantee, grantor CHAR(8),
        g_opt CHAR(1),
        sql_strg CHAR(80)

      LET sql_strg = "GRANT ", priv clipped, " ON ", tabname clipped, 
                     " TO ", grantee clipped

                        # if it is upper case (ASCII 65-90) then they
                        # have grant option.

      IF g_opt < ASCII(91) THEN 
         LET sql_strg=sql_strg clipped, " WITH GRANT OPTION"
      END IF

                        # creator is a grantee, but grantor is blank!
      IF LENGTH(grantor) > 0 THEN
         LET sql_strg=sql_strg clipped, " AS ", grantor clipped, ";"
      ELSE
         LET sql_strg=sql_strg clipped, ";"
      END IF

      CALL psh_strg("END",sql_strg,"")

END FUNCTION


#########################################################################
#
# This is not going to be pretty.  A constraint is defined across multiple
# tables identified by multiple keys which are integral to the current
# database and do not necessarily match the other database (constrid, tabid,
# idxname, colno) the only way to ensure that we get the right ones and
# only the right ones is to work out the full constraint definition and
# then compare it to the other databases definitions - sort of like the
# way indexes were done.  This time though, I'm going to express it in 
# sql so that I don't need to make a second pass to generate it.
# jp 10/30/94
#
#########################################################################
FUNCTION chg_constr()

DEFINE constr_rec RECORD
        constr_id   INTEGER,
        constr_name CHAR(18),
        owner       CHAR(8),
        tabid       INTEGER,
        constrtype  CHAR(1),
        idxname     CHAR(18),
        tabname     CHAR(18),
        primary     INTEGER
       END RECORD,
        sel_stmt    CHAR(2000),
        stmt1, sql_stmt    CHAR(500),
        s1          CHAR(100),
        i, oldnew SMALLINT

# get some work space.
   CREATE TEMP TABLE tmp_constr (constrname CHAR(18), tabname CHAR(18),
                    old_new SMALLINT, constr_def CHAR(500)) WITH NO LOG

# 1 - identify each old constraint.
# handle checks seperately

   IF ONLINE_SW THEN
      LET sel_stmt = "SELECT o_sysconstraints.constrid, constrname, ",
           "o_sysconstraints.owner, o_sysconstraints.tabid, constrtype, ",
           "o_sysconstraints.idxname, tabname, prim ",
      "FROM o_sysconstraints, o_systables, OUTER o_sysreferences ",
     "WHERE o_sysconstraints.tabid = o_systables.tabid ",
       "AND o_sysconstraints.constrid = o_sysreferences.constrid ",
       "AND constrtype != 'C' AND constrtype != 'N'"
   ELSE      
      LET sel_stmt = "SELECT o_sysconstraints.constrid, constrname, ",
           "o_sysconstraints.owner, o_sysconstraints.tabid, constrtype, ",
           "o_sysconstraints.idxname, tabname, prim ",
      "FROM o_sysconstraints, o_systables, o_sysindexes,OUTER o_sysreferences ",
     "WHERE o_sysconstraints.tabid = o_systables.tabid ",
       "AND o_sysconstraints.constrid = o_sysreferences.constrid ",
       "AND constrtype != 'C' AND constrtype != 'N'"
   END IF

   PREPARE s1 FROM sel_stmt
   DECLARE r_cons_o CURSOR FOR s1

   # While we're at it, lets make the cursor for the new ones too.

   CALL translate("o_","",sel_stmt) RETURNING sel_stmt
   CALL translate("prim","primary",sel_stmt) RETURNING sel_stmt

   PREPARE s2 FROM sel_stmt
   DECLARE r_cons_n CURSOR FOR s2

   CALL op_log("Parsing old constraints")
   FOREACH r_cons_o INTO constr_rec.*
      CALL pars_cons(constr_rec.*, 0)
   END FOREACH

# Do it again for the new ones
   CALL op_log("Parsing new constraints")
   FOREACH r_cons_n INTO constr_rec.*
      CALL pars_cons(constr_rec.*, 1)
   END FOREACH

# select unique ones (NOT a SELECT UNIQUE situation mind you)
   CALL op_log("Comparing constraints")
   DECLARE cm_curs CURSOR FOR
   SELECT constr_def 
     FROM tmp_constr
    GROUP BY constr_def
   HAVING COUNT(*) = 1

# we couldn't grab everything and group by all of them because the old_new
# switch is going to be different

   FOREACH cm_curs INTO sql_stmt 

      SELECT constrname, tabname, old_new 
        INTO constr_rec.constr_name, constr_rec.tabname, oldnew
        FROM tmp_constr
       WHERE constr_def = sql_stmt

# drop or add according to oldnew switch
      IF oldnew = 0 THEN
         CALL fold_and_push(sql_stmt, 1)
      ELSE
         LET msg = "ALTER TABLE ", constr_rec.tabname clipped, 
                   " DROP CONSTRAINT ", constr_rec.constr_name clipped, ";"
         OUTPUT TO REPORT dump_sql(msg)
         OUTPUT TO REPORT dump_sql("")
      END IF
   END FOREACH

# Handle syschecks, since they may be VERY long they won't work with the 
# comparison style we just used, match them up against one another into a 
# temp table, things not matched into the table need fixing.

                                # find the id of all macthing ones
   CALL op_log("Parsing check constraints")
 SELECT o_syschecks.constrid old_id, syschecks.constrid new_id
   FROM o_syschecks, syschecks 
  WHERE o_syschecks.type = 'B'
    AND syschecks.type = 'B'
    AND o_syschecks.seqno=syschecks.seqno
    AND o_syschecks.checktext=syschecks.checktext
   INTO TEMP duppedchecks WITH NO LOG;

                                # these ones aren't there
DECLARE new_checks CURSOR FOR 
 SELECT unique constrid 
   FROM syschecks
  WHERE NOT EXISTS (SELECT new_id FROM duppedchecks)

                                # nor are these ones
DECLARE old_checks CURSOR FOR 
 SELECT unique constrid 
   FROM o_syschecks
  WHERE NOT EXISTS (SELECT old_id FROM duppedchecks)

                                # and we'll need the text for the old ones.
LET s1 = "SELECT checktext, seqno FROM o_syschecks WHERE constrid = ? AND ",
         "type = 'T' ORDER BY seqno"

PREPARE ock FROM s1
DECLARE oldchecks CURSOR FOR ock
  
   CALL op_log("Generating drops for old check constraints")
                        # Lose the bad ones
   FOREACH new_checks INTO constr_rec.constr_id

      SELECT constrname, tabname
        INTO constr_rec.constr_name, constr_rec.tabname
        FROM sysconstraints, systables
       WHERE sysconstraints.tabid = systables.tabid
         AND constrid = constr_rec.constr_id

      LET sql_stmt = 'ALTER TABLE ', constr_rec.tabname clipped, 
                     ' DROP CONSTRAINT ', constr_rec.constr_name clipped, ';'

      OUTPUT TO REPORT dump_sql(sql_stmt)
   END FOREACH

                        # add the new ones
   CALL op_log("Generating adds for new check constraints")
   FOREACH old_checks INTO constr_rec.constr_id
      SELECT tabname
        INTO constr_rec.tabname
        FROM o_sysconstraints, o_systables
       WHERE o_sysconstraints.tabid = o_systables.tabid
         AND constrid = constr_rec.constr_id

      LET sql_stmt = 'ALTER TABLE ', constr_rec.tabname clipped, 
                     ' ADD CONSTRAINT CHECK'

      OPEN oldchecks USING constr_rec.constr_id
                        # probably not broken on space boundaries....

# FIX THIS.  This duplicates the functionaility of fold_and_push

      FOREACH oldchecks INTO stmt1
         LET sql_stmt = sql_stmt clipped, stmt1
         IF LENGTH(sql_stmt) > 450 THEN
            FOR i = LENGTH(sql_stmt) TO 1 STEP -1
               IF sql_stmt[i,i] = ' ' THEN      # break it here
                  LET stmt1 = sql_stmt[i,500]   # put trailer into stmt1
                  LET sql_stmt = sql_stmt[1,i]  # get first part
                  CALL fold_and_push(sql_stmt, 1)       # fold and push
                  LET sql_stmt = stmt1          # reset 
                  EXIT FOR                      # outta here
               END IF
            END FOR
         END IF
      END FOREACH

      LET sql_stmt=sql_stmt clipped, ";"
      CALL fold_and_push(sql_stmt, 1)

   END FOREACH

END FUNCTION

###########################################################################
# A common routine to generate the text of a constraint (except for checks)
###########################################################################
FUNCTION pars_cons(constr_rec, o_n)

DEFINE constr_rec RECORD
        constr_id   INTEGER,
        constr_name CHAR(18),
        owner       CHAR(8),
        tabid       INTEGER,
        constrtype  CHAR(1),
        idxname     CHAR(18),
        tabname     CHAR(18),
        primary     INTEGER
       END RECORD,
        o_n SMALLINT,
        sql_stmt    CHAR(500),
        i, j        SMALLINT,
        p_colname   CHAR(18),
        p_tabname   CHAR(18),
        col_strng   CHAR(330)   #       16*20+10_just_in_case

   LET msg = "pars_cons - ",
             constr_rec.tabname CLIPPED,"/",
             constr_rec.constr_id,"/",
             constr_rec.constr_name CLIPPED,"/",
             constr_rec.constrtype
   CALL op_log(msg)
                                # base - all have this
   LET sql_stmt = 'ALTER TABLE ', constr_rec.tabname clipped,
                     ' ADD CONSTRAINT'
      
                                # constraint type
   CASE constr_rec.constrtype
      WHEN 'P' LET sql_stmt = sql_stmt clipped, ' PRIMARY KEY'
      WHEN 'U' LET sql_stmt = sql_stmt clipped, ' UNIQUE'
      WHEN 'R' LET sql_stmt = sql_stmt clipped, ' FOREIGN KEY'
   END CASE

                                # constraint columns
   LET msg= "idx_parts 4 - ",constr_rec.idxname
   CALL op_log(msg)
   CALL idx_parts(constr_rec.idxname,o_n) RETURNING col_strng

                                # add parens
   IF i > 2 THEN
      LET col_strng = "(", col_strng clipped, ")"
   END IF

                                # add the string to the SQL stmt
   LET sql_stmt = sql_stmt clipped, col_strng clipped
      
                                # if an 'R' then add on 'REFERENCES' clause
   IF constr_rec.constrtype = 'R' THEN

      LET sql_stmt = sql_stmt clipped, ' REFERENCES'

      IF o_n = 0 THEN                   # OLD
         SELECT idxname
           INTO p_colname
          FROM o_sysconstraints, o_sysreferences
         WHERE o_sysconstraints.constrid = prim
           AND o_sysreferences.constrid = constr_rec.constr_id

         SELECT tabname
           INTO p_tabname
           FROM o_sysreferences, o_systables
          WHERE o_sysreferences.ptabid = o_systables.tabid
            AND o_sysreferences.constrid = constr_rec.constr_id
      ELSE                              # NEW
         SELECT idxname
           INTO p_colname
          FROM sysconstraints, sysreferences
         WHERE sysconstraints.constrid = primary 
           AND sysreferences.constrid = constr_rec.constr_id

         SELECT tabname
           INTO p_tabname
           FROM sysreferences, systables
          WHERE sysreferences.ptabid = systables.tabid
            AND sysreferences.constrid = constr_rec.constr_id
      END IF

                                        # get column names
      LET msg = "idx_parts 5 - ",p_colname
      CALL op_log(msg)
      CALL idx_parts(p_colname,o_n) RETURNING col_strng
      LET sql_stmt = sql_stmt clipped, " ", p_tabname clipped, " (", 
                                       col_strng clipped, ")"
   END IF

                                        # add the ";"
   LET sql_stmt = sql_stmt CLIPPED, ";"

                                        # drop into table
   INSERT INTO tmp_constr VALUES(constr_rec.constr_name, constr_rec.tabname,
                                  o_n, sql_stmt)

END FUNCTION



###########################################################################
# Change Triggers       (Kerry Sainsbury)
# Compare contents of systriggers and o_systriggers,
#     and contents of systrigbody and o_systrigbody
#    drop old or different.  Add new or different
###########################################################################
FUNCTION chg_trigs()
DEFINE   l_trigname    CHAR(18),
         l_owner       CHAR(8),
         sql_strg      CHAR(80),
         l_o    RECORD
            trigid    INT,
            trigname  CHAR(18),
            owner     CHAR(8),
            tabid     INT,
            event     CHAR,
            old       CHAR(18),
            new       CHAR(18),
            tabname   CHAR(18)
         END RECORD,
         l_a    RECORD
            trigid    INT,
            trigname  CHAR(18),
            owner     CHAR(8),
            tabid     INT,
            event     CHAR,
            old       CHAR(18),
            new       CHAR(18),
            tabname   CHAR(18)
         END RECORD,
         l_os   RECORD
            datakey   CHAR,
            seqno     INT,
            data      CHAR(256)
         END RECORD,
         l_as   RECORD
            datakey   CHAR,
            seqno     INT,
            data      CHAR(256)
         END RECORD,
         l_acnt       INTEGER,
         l_ocnt       INTEGER

-- Drop all triggers that are not in o_systriggers...

   DECLARE drop_trigs CURSOR FOR
    SELECT systriggers.trigname, systriggers.owner, systables.tabname
      FROM systriggers, systables
     WHERE systriggers.tabid = systables.tabid
       AND systriggers.tabid > 99 
       AND tabname MATCHES tb_name
       AND NOT EXISTS (SELECT trigname FROM o_systriggers
                        WHERE o_systriggers.trigname = systriggers.trigname
                          AND o_systriggers.owner = systriggers.owner)

   CALL op_log("Generating DROP TRIGGERs")
   FOREACH drop_trigs INTO l_trigname, l_owner
      LET sql_strg= "drop trigger '", l_owner CLIPPED, "'.", 
                                      l_trigname CLIPPED,";"
      CALL psh_strg("END",sql_strg,";")
   END FOREACH
   FREE drop_trigs

-- Now build a list of all triggers that're in both databases...
-- with the same trigger name and owner

   DECLARE more_drop_trigs CURSOR FOR
    SELECT o.trigid, o.trigname, o.owner, o.tabid,
           o.event, o.old, o.new, p.tabname,
           a.trigid, a.trigname, a.owner, a.tabid,
           a.event, a.old, a.new, b.tabname
      FROM o_systriggers o, systriggers a, o_systables p, systables b
     WHERE o.trigname = a.trigname
       AND o.owner = a.owner
       AND o.tabid = p.tabid
       AND a.tabid = b.tabid

   FOREACH more_drop_trigs INTO l_o.*, l_a.*

-- Do a cheap n nasty check to see if triggers are different, based on the
-- number of lines in each...

      LET l_acnt = 0
      LET l_ocnt = 0
      SELECT COUNT(*) INTO l_acnt FROM systrigbody
       WHERE trigid = l_a.trigid
         AND datakey IN ("D", "A")

      SELECT COUNT(*) INTO l_ocnt FROM o_systrigbody
       WHERE trigid = l_o.trigid

      IF l_acnt != l_ocnt THEN    # Different number of lines in trigger
         LET sql_strg= "drop trigger '", l_a.owner CLIPPED, "'.", 
                                         l_a.trigname CLIPPED,";"
         CALL psh_strg("END",sql_strg,";")
      ELSE

-- Check for a subtle change (line count same, but content different)

         DECLARE subtle_curs CURSOR FOR
          SELECT o.datakey, o.seqno, o.data,
                 a.datakey, a.seqno, a.data
            FROM o_systrigbody o, systrigbody a
           WHERE a.trigid = l_a.trigid
             AND o.trigid = l_o.trigid
             AND o.seqno = a.seqno
             AND o.datakey = a.datakey
           ORDER BY o.datakey, o.seqno

          FOREACH subtle_curs INTO l_os.*, l_as.*

-- If text of trigger differs...

             IF l_os.data    != l_as.data THEN

-- then throw the old trigger away...

                LET sql_strg= "drop trigger '", l_a.owner CLIPPED, "'.", 
                                                l_a.trigname CLIPPED,";"
                CALL psh_strg("END",sql_strg,";")

-- ... and recreate it in o_'s image...
                CALL create_trigger(l_o.trigid, l_o.trigname)
                EXIT FOREACH
             END IF
          END FOREACH
          FREE subtle_curs
      END IF
   END FOREACH
   FREE more_drop_trigs

-- Finally: Any triggers in o_systriggers that aren't in systriggers
--          need to be created...

   CALL op_log("Creating missing TRIGGERs")

   DECLARE fin_curs CURSOR FOR
    SELECT trigid, trigname
      FROM o_systriggers
     WHERE NOT EXISTS (SELECT trigname, owner FROM systriggers
                        WHERE systriggers.trigname = o_systriggers.trigname
                          AND systriggers.owner = o_systriggers.owner)
   FOREACH fin_curs INTO l_o.trigid, l_o.trigname
      CALL create_trigger(l_o.trigid, l_o.trigname)
   END FOREACH
   FREE fin_curs

END FUNCTION


FUNCTION create_trigger(l_trigid, l_trigname)
DEFINE l_trigid     INT,
       l_trigname   CHAR(18)

DEFINE sql_strg     CHAR(1024),
       l_data       CHAR(4096),
       l_indata     CHAR(256),
       i, j         SMALLINT,
       l_lth        SMALLINT,
       l_ascii10    CHAR(1)

   LET l_ascii10 = ASCII(10)  # Informix Bug Alert!!

   DECLARE build_trig_curs CURSOR FOR
    SELECT data, datakey, seqno FROM o_systrigbody
     WHERE trigid = l_trigid
     ORDER BY datakey DESC, seqno

   OUTPUT TO REPORT dump_SQL("")
   LET sql_strg = ""
   LET l_lth = 1
   LET l_data = " "
   FOREACH build_trig_curs INTO l_indata
      LET l_data[l_lth+1,4096]=l_indata
      LET l_lth = LENGTH(l_data)
      WHILE l_lth > 0
         FOR i = 1 TO l_lth
            IF l_data[i] = l_ascii10 THEN   #<<<----- What about MSDOS?
               IF i > 1 THEN
                  LET sql_strg = l_data[1,i-1]
                  CALL fold_and_push(sql_strg,0)
               END IF
               IF i != l_lth THEN
                  LET l_data = l_data[i+1, l_lth]
               END IF
               LET l_lth = l_lth - i
               EXIT FOR
            END IF
         END FOR
         IF i > l_lth THEN  # Can't find an end of line
            EXIT WHILE      # so get another chunk of trigger definition
         END IF
      END WHILE
   END FOREACH
   LET sql_strg = l_data
   CALL fold_and_push(sql_strg,1)

   FREE build_trig_curs
END FUNCTION 


###########################################################################
# Change Stored Procedures      (Kerry Sainsbury)
# Compare contents of sysprocbody and o_sysprocbody
#    drop old or different.  Add new or different
###########################################################################
FUNCTION chg_spl()
DEFINE   sql_strg      CHAR(80),
         l_ocnt        INTEGER,
         l_odata       CHAR(256),
         l_oprocname   CHAR(18),
         l_oprocid     INTEGER,

         l_acnt        INTEGER,
         l_adata       CHAR(256),
         l_aprocname   CHAR(18),
         l_aprocid     INTEGER,
         l_aowner      CHAR(8)

-- Drop all spls that are not in o_systriggers...

   DECLARE drop_spls CURSOR FOR
    SELECT procname, owner
      FROM sysprocedures
     WHERE NOT EXISTS (SELECT procname FROM o_sysprocedures
                        WHERE o_sysprocedures.procname=sysprocedures.procname
                          AND o_sysprocedures.owner = sysprocedures.owner)

   CALL op_log("Generating DROP PROCEDUREs")
   FOREACH drop_spls INTO l_aprocname, l_aowner
      LET sql_strg= "DROP PROCEDURE '", l_aowner CLIPPED, "'.", 
                                        l_aprocname CLIPPED,";"
      CALL psh_strg("END",sql_strg,";")
   END FOREACH
   FREE drop_spls

-- Now build a list of all procedures that're in both databases...
-- with the same procedure name and owner

   DECLARE more_drop_spls CURSOR FOR
    SELECT o.procid, o.procname,
           a.procid, a.procname, a.owner
      FROM o_sysprocedures o, sysprocedures a
     WHERE o.procname = a.procname
       AND o.owner = a.owner

   FOREACH more_drop_spls INTO l_oprocid, l_oprocname,
                               l_aprocid, l_aprocname, l_aowner

-- Do a crass check to see if the stored procedures are different
-- (based on the number of lines in each procedure)...

      LET l_ocnt = 0
      LET l_acnt = 0
      SELECT COUNT(*) 
        INTO l_ocnt
        FROM o_sysprocbody
       WHERE procid = l_oprocid

      SELECT COUNT(*) 
        INTO l_acnt
        FROM sysprocbody
       WHERE procid = l_aprocid
         AND datakey = "T"

      IF l_ocnt != l_acnt THEN
         LET sql_strg= "DROP PROCEDURE '", l_aowner CLIPPED, "'.", 
                                           l_aprocname CLIPPED,";"
         CALL psh_strg("END",sql_strg,";")
         CALL create_procedure(l_oprocid, l_oprocname)
      ELSE

-- Now check for a subtle change (line count stays the same, but content
-- differs)

         DECLARE subtle_splcurs CURSOR FOR
          SELECT o.data,
                 a.data, o.seqno
            FROM o_sysprocbody o, sysprocbody a
           WHERE a.procid = l_aprocid
             AND o.procid = l_oprocid
             AND a.seqno = o.seqno
             AND a.datakey = "T"
           ORDER BY o.seqno

          FOREACH subtle_splcurs INTO l_odata, l_adata
             IF l_odata != l_adata THEN
                LET sql_strg= "DROP PROCEDURE '", l_aowner CLIPPED, "'.", 
                                                  l_aprocname CLIPPED,";"
                CALL psh_strg("END",sql_strg,";")
                CALL create_procedure(l_oprocid, l_oprocname)
                EXIT FOREACH
             END IF
          END FOREACH
          FREE subtle_splcurs
      END IF
   END FOREACH
   FREE more_drop_spls

-- Finally: Any procedures in o_sysprocedures that aren't in sysprocedures
--          need to be created...

   CALL op_log("Creating missing PROCEDUREs")

   DECLARE sfin_curs CURSOR FOR
    SELECT procid, procname
      FROM o_sysprocedures
     WHERE NOT EXISTS (SELECT procname, owner FROM sysprocedures
                        WHERE sysprocedures.procname = o_sysprocedures.procname
                          AND sysprocedures.owner = o_sysprocedures.owner)
   FOREACH sfin_curs INTO l_oprocid, l_oprocname
      CALL create_procedure(l_oprocid, l_oprocname)
   END FOREACH
   FREE sfin_curs

END FUNCTION


FUNCTION create_procedure(l_procid, l_procname)
DEFINE l_procid     INT,
       l_procname   CHAR(18)

DEFINE sql_strg     CHAR(1024),
       l_data       CHAR(4096),
       l_indata     CHAR(256),
       i, j         INTEGER,
       l_lth        INTEGER,
       l_ascii10    CHAR(1),
       junk         SMALLINT

   LET l_ascii10 = ASCII(10)  # Informix Bug Alert!!

   DECLARE build_spl_curs CURSOR FOR
    SELECT data, seqno FROM o_sysprocbody
     WHERE procid = l_procid
     ORDER BY seqno

   OUTPUT TO REPORT dump_SQL("")
   LET sql_strg = ""

# stick line together and flush to report all in one fell swoop
# If we try to pass a CR to fold_and_push we get a dropped core.

   FOREACH build_spl_curs INTO l_indata, junk
      LET l_data[l_lth+1,4096]=l_indata
      LET l_lth = LENGTH(l_data)
      FOR i = 1 TO l_lth
         IF l_data[i,i] = l_ascii10 THEN        #<<<----- What about MSDOS?
            IF i > 1 THEN                       # has data
               LET sql_strg = l_data[1,i-1]     # = line - CR
               CALL fold_and_push(sql_strg,0)   # flush the line
            ELSE                                # skip over SINGLE CR
               LET l_data = l_data[i+1,l_lth]   # lose the CR
               LET i = 0                        # start FOR over
               LET l_lth = LENGTH(l_data)       # reset length

# All I want for christmas is to duplicate that CR we dropped.
# But every time I try I drop core.
#               OUTPUT TO REPORT dump_SQL("")

               CONTINUE FOR
            END IF                              # flushing the line
            IF i != l_lth THEN
               LET l_data = l_data[i+1,l_lth]
               LET i = 0                        # start FOR over
               LET l_lth = LENGTH(l_data)       # reset length
            END IF                              # resetting after CR
         END IF                                 # found a CR
      END FOR                                   # scanning the string


# Kerry code. drops core on my box.
#      LET l_data[l_lth+1,4096]=l_indata
#      LET l_lth = LENGTH(l_data)
#      WHILE l_lth > 0
#         FOR i = 1 TO l_lth
#            IF l_data[i] = l_ascii10 THEN   #<<<----- What about MSDOS?
#               IF i > 1 THEN
#                  LET sql_strg = l_data[1,i-1]
#                  CALL fold_and_push(sql_strg,0)
#               END IF
#               IF i != l_lth THEN
#                  LET l_data = l_data[i+1, l_lth]
#               END IF
#               LET l_lth = l_lth - i
#               EXIT FOR
#            END IF
#         END FOR
#         IF i > l_lth THEN  # Can't find an end of line
#            EXIT WHILE      # so get another chunk of procedure definition
#         END IF
#      END WHILE

   END FOREACH
   LET sql_strg = l_data
   CALL fold_and_push(sql_strg,1)

   FREE build_spl_curs
END FUNCTION 
         
######################### Generic functions ##############################

###########################################################################
# Convert coltype/length into an SQL descriptor string
###########################################################################
FUNCTION col_cnvrt(coltype, collength)

   DEFINE coltype, collength, NONULL SMALLINT,
          SQL_strg CHAR(40),
          tmp_strg CHAR(4)

   LET coltype = coltype + 1            # datatype[] is offset by one
   LET NONULL  = coltype/256            # if > 256 then is NO NULLS
   LET coltype = coltype MOD 256        # lose the NO NULLS determinator

   LET SQL_strg = datatype[coltype] 

   CASE coltype

      WHEN 1                            # char
         LET tmp_strg = collength using "<<<<"
         LET SQL_strg = SQL_strg clipped, " (", tmp_strg clipped, ")"

# SQL syntax supports float(n) - Informix ignores this
#      WHEN 4                           # float
#         LET SQL_strg = SQL_strg clipped, " (", ")"

      WHEN 6                            # decimal
         LET SQL_strg = SQL_strg clipped, " (", fix_nm(collength,0) clipped, ")"

# Syntax supports serial(starting_no) - starting_no is unavaliable
#      WHEN 7                           # serial
#         LET SQL_strg = SQL_strg clipped, " (", ")"

      WHEN 9                            # money
         LET SQL_strg = SQL_strg clipped, " (", fix_nm(collength,0) clipped, ")"

      WHEN 11                           # datetime
         LET SQL_strg = SQL_strg clipped, " ", fix_dt(collength) clipped 

      WHEN 14                           # varchar
         LET SQL_strg = SQL_strg clipped, " (", fix_nm(collength,1) clipped, ")"

      WHEN 15                           # interval
         LET SQL_strg = SQL_strg clipped, " ", fix_dt(collength) clipped

   END CASE

   IF NONULL THEN
      LET SQL_strg = SQL_strg clipped, " NOT NULL"
   END IF
      
   RETURN SQL_strg

END FUNCTION

###########################################################################
# Turn collength into two numbers - return as string
###########################################################################
FUNCTION fix_nm(num,tp)

DEFINE num integer,
       tp smallint,
       strg CHAR(8),
       i, j   SMALLINT,
       strg1, strg2 char(3)

   LET i = num / 256
   LET j = num MOD 256
   LET strg1 = i using "<<&"
   LET strg2 = j using "<<&"
   IF tp = 0 THEN
      IF j > i THEN
         LET strg = strg1 clipped
      ELSE
         LET strg = strg1 clipped, ", ", strg2 clipped
      END IF
   ELSE         # varchar is just the opposite
      IF i = 0 THEN
         LET strg = strg2 clipped
      ELSE
         LET strg = strg2 clipped, ", ", strg1 clipped
      END IF
   END IF

   RETURN strg

END FUNCTION

###########################################################################
# Turn collength into meaningful date info - return as string
###########################################################################
FUNCTION fix_dt(num)
DEFINE num integer,
       i, j, k, len   SMALLINT,
       strg CHAR(30)

   LET i   = (num mod 16) + 1           # offset again
   LET j   = ((num mod 256) / 16) + 1   # offset again
   LET k   = num / 256                  # length of value

# If this is an interval then life gets interesting, 'k' is the length of 
# the entire string.  So a YEAR TO DAY is YYYYMMDD or 8.  A DAY(3) TO 
# MINUTE is DDDHHMM or 7.  We don't know how long the first one is, but 
# we can work it out by computing the 'should be length' of the string
# and then adding/subtracting the result from the 'should be length' of 
# the major element.
#
# Keep in mind --->    YYYYMMDDHHMMSSFFFFF
#     vs.         j =    1  2 3 4 5 678901
#
# I was just working an algorithm to do this, 4 notepads, 90 minutes, and 50
# lines into it I realized that I was creating something impossible to test
# or maintain.  Therefore I am opting for something a lot simpler.
#
# In the globals I have created an ARRAY of RECORD with start and end points
# for the major and minor pieces.  By subtracting the START point of the 
# major element from the END point of the minor element I get the 'should be
# length'
#

   LET len = intvray[i].end_point - intvray[j].start_point

# len should match k.   e.g.:
#                               DAY(5) TO MINUTE  ==> k = 9, len = 6
#                               YEAR(6) TO HOUR   ==> k = 12, len = 14

   LET len = k - len                    # add len to the major

   IF len = 0 OR j > 11 THEN            # is the default
                                        # 12 on have the precision alrdy coded

      LET strg = datetype[j] clipped, " TO ", datetype[i] clipped

   ELSE                         # isn't the default

                                # uh-oh, how long IS the default major?
      LET k = intvray[j].end_point - intvray[j].start_point 

                                # add in the extra
      LET k = k + len

      LET strg = datetype[j] clipped, "(", k using "<<", ")", " TO ", 
                 datetype[i] clipped
   
   END IF

   RETURN strg

END FUNCTION

###########################################################################
# process user args
###########################################################################
FUNCTION proc_arg()

DEFINE  errusage SMALLINT,
        opt_ind   SMALLINT,
        curr_opt  CHAR(20)

   LET errusage     = 0
   LET ONLINE_SW    = 1
   LET db_old       = ""
   LET db_new       = ""
   LET SEG1         = 1
   LET SEG2         = 1
   LET ofile_name   = "/tmp/mod_db.sql"
   LET DEBUG        = 0
   LET AUTH_SW      = 0                 # check table permissions
   LET USER_SW      = 1                 # Check users
   LET tb_name      = "*"
   LET CONST_SW     = 1                 # check constraints
   LET SPL_SW       = 1                 # Check SPLs
   LET TRIG_SW      = 0
   LET QUIET_SW     = 0
   LET serverfle    = "dbd.servers"
   LET logfile      = "dbdiff2.log"
   LET LOG_SW       = 0

  LET opt_ind = 0
  WHILE (opt_ind <= num_args())

     LET opt_ind = opt_ind + 1
     LET curr_opt = UPSHIFT(arg_val(opt_ind))

     CASE curr_opt

        WHEN "-DB"
           LET opt_ind = opt_ind + 1
           LET curr_opt = UPSHIFT(arg_val(opt_ind))
           IF curr_opt = "SE" THEN
              LET ONLINE_SW = 0
              LET msg = "SE mode selected"
           ELSE
              LET msg = "Online mode selected"
           END IF
           CALL op_log(msg)

        WHEN "-OD"
           LET opt_ind = opt_ind + 1
           LET db_old = arg_val(opt_ind)
           LET msg = "Old database: ", db_old
           CALL op_log(msg)

        WHEN "-ND"
           LET opt_ind = opt_ind + 1
           LET db_new = arg_val(opt_ind)
           LET msg = "New database: ", db_new
           CALL op_log(msg)

        WHEN "-C"
           LET CONST_SW = 0 
           LET msg = "Constraints off. "
           CALL op_log(msg)

        WHEN "-O"
           LET opt_ind = opt_ind + 1
           LET ofile_name = arg_val(opt_ind)
           LET msg = "Output file: ", ofile_name clipped
           CALL op_log(msg)

        WHEN "-S1" 
           LET SEG2 = 0
           LET msg = "Segment two turned off"
           CALL op_log(msg)

        WHEN "-S2" 
           LET SEG1 = 0
           LET msg = "Segment one turned off"
           CALL op_log(msg)

        WHEN "-DBG" 
           LET DEBUG = 1
           LET msg = "Debug mode turned on"
           CALL op_log(msg)

        WHEN "-A" 
           LET AUTH_SW = 1
           LET msg = "Authority mode turned on"
           CALL op_log(msg)

        WHEN "-U" 
           LET USER_SW = 0
           LET msg = "User mode turned off"
           CALL op_log(msg)

        WHEN "-SPL" 
           LET SPL_SW = 0
           LET msg = "Stored Procedures mode turned off"
           CALL op_log(msg)

        WHEN "-TRG" 
           LET TRIG_SW = 1
           LET msg = "Triggers mode turned on"
           CALL op_log(msg)

        WHEN "-Q" 
           LET QUIET_SW = 1
           LET msg = "Quiet mode turned on"
           CALL op_log(msg)

        WHEN "-SVR" 
           LET opt_ind = opt_ind + 1
           LET serverfle = arg_val(opt_ind)
           LET msg = "Server file name : ", serverfle clipped
           CALL op_log(msg)

        WHEN "-LG"
           LET LOG_SW = 1
           LET msg = "Logging turned on : "
           CALL op_log(msg)

        WHEN "-LGF" 
           LET opt_ind = opt_ind + 1
           LET logfile = arg_val(opt_ind)
           LET LOG_SW = 1
           START REPORT logg TO logfile
           LET msg = "Log file name : ", logfile clipped
           CALL op_log(msg)

        WHEN "-T" 
           LET opt_ind = opt_ind + 1
           LET tb_name = arg_val(opt_ind)
           LET msg = "Table name : ", tb_name clipped
           CALL op_log(msg)

        WHEN "-ALL" 
           LET CONST_SW = 1 
           LET DEBUG = 1
           LET AUTH_SW = 1
           LET USER_SW = 1
           LET SPL_SW = 1
           LET TRIG_SW = 1

        OTHERWISE
           IF LENGTH(curr_opt) > 0 THEN
              LET errusage=1
              EXIT WHILE
           END IF

     END CASE

  END WHILE


                                        # check for probs
   IF SEG1 THEN
      CALL db_check(db_old) RETURNING errusage
   END IF

   IF SEG2 THEN
      CALL db_check(db_new) RETURNING errusage
   END IF

   IF LOG_SW = 1 THEN
      START REPORT logg TO logfile
   END IF

  IF NOT errusage THEN
     RETURN
  END IF

   DISPLAY 'usage:'
   DISPLAY 'dbutil [-db SE|OL]   engine type - SE or Online (def: OL)'
   DISPLAY '       [-od]         old database name '
   DISPLAY '       [-nd]         new database name '
   DISPLAY '       [-s1]         Only do segment 1'
   DISPLAY '       [-s2]         Only do segment 2'
   DISPLAY '       [-dbg]        debug mode'
   DISPLAY '       [-o]          output file name  (def: /tmp/mod_db.sql)'
   DISPLAY '       [-a]          Authority tables (permissions)'
   DISPLAY '       [-c]          Constraints'
   DISPLAY '       [-u]          Users'
   DISPLAY '       [-spl]        Stored Procedures'
   DISPLAY '       [-trg]        Triggers'
   DISPLAY '       [-t]          table name specification '
   DISPLAY '       [-q]          quiet mode - no interaction '
   DISPLAY '       [-svr]        server file name (dbd.servers) '
   DISPLAY '       [-lg]         turn on logging '
   DISPLAY '       [-lgf]        Log file name (dbdiff2.log) '
   EXIT PROGRAM 0

END FUNCTION


#######################################################################
# ensure we can open database
#######################################################################
FUNCTION db_check(dbname)

DEFINE dbname CHAR(64),
       err  SMALLINT

     LET err = 0

     IF LENGTH(dbname) = 0 THEN
        LET msg = "Must specify database"
        CALL op_log(msg)
        LET err = 1
     END IF

     WHENEVER ERROR CONTINUE

     DATABASE dbname
     IF status != 0 THEN
        LET msg = "Can't open:", dbname clipped
        CALL op_log(msg)
        LET msg = "Status: ", status
        CALL op_log(msg)
        LET err = 1
     ELSE
        IF ONLINE_SW THEN
           SET LOCK MODE TO WAIT 30

           IF STATUS THEN
              LET ONLINE_SW = 0
              LET msg = "RESETTING TO SE !!!!! - ", dbname clipped
              CALL op_log(msg)
           END IF

        END IF
     END IF

     CLOSE DATABASE

     RETURN err

END FUNCTION



###########################################################################
# misc housekeeping - init stuff.
###########################################################################
FUNCTION hskpng()

DEFINE i SMALLINT, lne CHAR(129), retcode INTEGER

   LET datatype[1]  = "CHAR"
   LET datatype[2]  = "SMALLINT"
   LET datatype[3]  = "INTEGER"
   LET datatype[4]  = "FLOAT"
   LET datatype[5]  = "SMALLFLOAT"
   LET datatype[6]  = "DECIMAL"
   LET datatype[7]  = "SERIAL"
   LET datatype[8]  = "DATE"
   LET datatype[9]  = "MONEY"
   LET datatype[10] = "UNKNOWN"
   LET datatype[11] = "DATETIME"
   LET datatype[12] = "BYTE"
   LET datatype[13] = "TEXT"
   LET datatype[14] = "VARCHAR"
   LET datatype[15] = "INTERVAL"
   LET datatype[16] = "UNKNOWN"         # little room for growth
   LET datatype[17] = "UNKNOWN"
   LET datatype[18] = "UNKNOWN"
   LET datatype[19] = "UNKNOWN"
   LET datatype[20] = "UNKNOWN"

   LET datetype[1] = "YEAR"
   LET intvray[1].start_point = 1
   LET intvray[1].end_point = 5         # offset by one for easy math

   LET datetype[3] = "MONTH"
   LET intvray[3].start_point = 5
   LET intvray[3].end_point = 7         

   LET datetype[5] = "DAY"
   LET intvray[5].start_point = 7
   LET intvray[5].end_point = 9         

   LET datetype[7] = "HOUR"
   LET intvray[7].start_point = 9
   LET intvray[7].end_point = 11                

   LET datetype[9] = "MINUTE"
   LET intvray[9].start_point = 11
   LET intvray[9].end_point = 13                

   LET datetype[11] = "SECOND"
   LET intvray[11].start_point = 13
   LET intvray[11].end_point = 15               

   LET datetype[12] = "FRACTION(1)"
   LET intvray[12].start_point = 15
   LET intvray[12].end_point = 16

   LET datetype[13] = "FRACTION(2)"
   LET intvray[13].start_point = 16
   LET intvray[13].end_point = 17               

   LET datetype[14] = "FRACTION(3)"
   LET intvray[14].start_point = 17
   LET intvray[14].end_point = 18               

   LET datetype[15] = "FRACTION(4)"
   LET intvray[15].start_point = 18
   LET intvray[15].end_point = 19               

   LET datetype[16] = "FRACTION(5)"
   LET intvray[16].start_point = 19
   LET intvray[16].end_point = 20               


   CALL proc_arg()

   START report dump_SQL TO ofile_name

   LET second_round=0                   # switch for psh_strg()

                                        # load file of server swaps
   FOR max_servers = 1 TO 20
      INITIALIZE servers[max_servers].* TO NULL
   END FOR

   LET max_servers=20                   # number of array elements

   CALL fle_acc(serverfle,LENGTH(serverfle) + 1) RETURNING retcode
   IF retcode = 0 THEN
      LET i = 1
      LET retcode = 0
      WHILE retcode = 0
         CALL read_line(serverfle,LENGTH(serverfle) + 1) RETURNING lne, retcode
         IF retcode = 0 THEN
            LET servers[i].old_server = ex_field(lne,1)
            LET servers[i].new_server = ex_field(lne,2)
         END IF
         LET i = i + 1
      END WHILE
   ELSE
      LET msg = "Cannot open server file - ", retcode, " ", serverfle clipped
      CALL op_log(msg)
   END IF

                                        # switch to indicate new servers added
   LET WRITE_SRV = 0

END FUNCTION


############################################################################
# Easy way to dump to file
############################################################################

REPORT dump_SQL(SQL_line)

   DEFINE SQL_line CHAR(80)

   OUTPUT
     LEFT MARGIN 0
     RIGHT MARGIN 80
     TOP MARGIN 0
     BOTTOM MARGIN 0
     PAGE LENGTH 1                      # no blank lines please

   FORMAT

      ON EVERY ROW
     print SQL_line clipped

END REPORT


############################################################################
# A function to handle commas and start/end pieces
# strg = string to put into statement
# strt = starting syntax e.g. "ALTER TABLE "
# end  = ending stuff to tack on to each line e.g. ","
# if strg = "END" then this is the last time we'll be here.
############################################################################
FUNCTION psh_strg(strg, strt, end_)

   DEFINE strg CHAR(80),
          strt, end_ CHAR(80),
          i, j, k   SMALLINT

   IF strg = "END" THEN
      IF sql_idx > 1 THEN
         LET k = sql_idx-1
         LET sql_strgs[k] = sql_strgs[k] clipped, end_ clipped
         FOR i = 1 TO sql_idx - 1
            OUTPUT TO REPORT dump_SQL(sql_strgs[i])
            INITIALIZE sql_strgs[i] TO NULL
         END FOR
         LET sql_idx=1
         OUTPUT TO REPORT dump_SQL("")  # blank line
      ELSE
         LET strt = strt, end_ clipped 
         OUTPUT TO REPORT dump_SQL(strt)
         OUTPUT TO REPORT dump_SQL("")  # blank line
      END IF

   ELSE

      IF sql_idx > 2 OR second_round THEN
         LET k = sql_idx-1
         LET sql_strgs[k] = sql_strgs[k] clipped, end_
         LET second_round=0
      END IF

      IF sql_idx = 1 THEN               # this is the first time
         LET sql_strgs[1] = strt
         LET sql_strgs[2] = "   ", strg # indent
         LET sql_idx = 2
      ELSE
         LET sql_strgs[sql_idx] = "   ", strg   
      END IF

      LET sql_idx = sql_idx + 1

      IF sql_idx > 50 THEN              # array overflow imminent!
         FOR i = 1 TO sql_idx - 2       # save last line - move to [1]
            OUTPUT TO REPORT dump_SQL(sql_strgs[i])
            INITIALIZE sql_strgs[i] TO NULL
         END FOR
         LET sql_strgs[1]=sql_strgs[50] 
        #######################################################################
        # NUTS!! we specifically don't add $end to [1].  At the same time there
        # is no gaurantee that we don't have exactly 49 fields - in which case
        # this could REALLY be the last line and we don't want to stick $end 
        # on it we won't know till the next time thru.  Need a switch - how 
        # I hate fixing logic with switches.....
        #######################################################################
         LET second_round=1
         LET sql_idx = 2
      END IF
   END IF

END FUNCTION


#############################################################################
# the last word in string may be truncated - so cut off everything up to
# that last word and return it, also return the remainder.
#############################################################################
FUNCTION clip_strg(strg)

DEFINE  strg, rmdr CHAR(80),
        i,j,k SMALLINT

   LET j = LENGTH(strg)

   IF j > 60 THEN
      FOR i = j TO 1 STEP -1
                                # need to look for space OR comma,
         IF strg[i,i] = " " OR strg[i,i] = "," THEN 
            EXIT FOR
         END IF
      END FOR

       IF i = j THEN
          LET rmdr = ""
       ELSE
         LET k    = i+1
         LET rmdr = strg[k,j]
         LET strg = strg[1,i]
       END IF
   ELSE
      LET rmdr = ""
   END IF

   RETURN strg, rmdr

END FUNCTION

###########################################################################
# operations logs - store
###########################################################################
FUNCTION op_log(txt)

DEFINE txt CHAR(80),
        i,j SMALLINT

   IF LOG_SW THEN
      OUTPUT TO REPORT logg(txt)
   END IF

   LET op_idx = op_idx + 1

   IF op_idx > 500 THEN

      FOR i = 401 to 500
         LET j=i-400
         LET oplog[j]=oplog[i]
      END FOR

      LET op_idx = 102
      LET oplog[101]="Log overflow - dropping first 400 msgs"

   END IF

   LET oplog[op_idx]=txt

   IF DEBUG THEN
      DISPLAY txt
   END IF

END FUNCTION

###########################################################################
# operations logs - view
###########################################################################

FUNCTION view_log()

   OPTIONS NEXT KEY CONTROL-N,
           PREVIOUS KEY CONTROL-P,
           ACCEPT KEY CONTROL-M

   OPEN WINDOW w_log AT 10,2 WITH FORM "w_log" ATTRIBUTE(BORDER)
   CALL set_count(op_idx)
   DISPLAY ARRAY oplog TO s_log.*

END FUNCTION


###########################################################################
# display errors - option to view log
###########################################################################
FUNCTION disp_err()

   DEFINE op CHAR(1)

   CALL op_log(msg)

   IF NOT QUIET_SW THEN

      WHILE TRUE
      LET op=button_at(5, 5, "title",msg, "View [L]og, [E]xit, [C]ontinue", "[LEC]")

         CASE op
            WHEN "L"
               CALL view_log()
            WHEN "E"
               EXIT PROGRAM
            WHEN "C"
               EXIT WHILE
         END CASE
      END WHILE

   END IF

END FUNCTION


###########################################################################
# Try to resolve old synonym server vs desired server.
###########################################################################
FUNCTION get_server(old_server, tabname)

DEFINE old_server, tabname CHAR(20),
       i          SMALLINT

FOR i = 1 TO max_servers

                                        # already matched?
   IF old_server = servers[i].old_server THEN
      EXIT FOR
   END IF

                                        # new match
   IF  LENGTH(servers[i].old_server) = 0 THEN
      LET servers[i].old_server = old_server
      CALL accpt_server(servers[i].old_server, tabname) 
                  RETURNING servers[i].new_server
      EXIT FOR
   END IF

   IF i = 20 THEN
      CALL op_log("Server array overflow")
      RETURN old_server
   END IF

END FOR

RETURN servers[i].new_server

END FUNCTION


###########################################################################
# can't resolve it, ask.
###########################################################################
FUNCTION accpt_server(serv_name, tabname)

DEFINE serv_name, tabname CHAR(20)

   IF NOT QUIET_SW THEN

      OPEN WINDOW w_serv AT 7,9 WITH FORM "server" ATTRIBUTE(BORDER)
      DISPLAY tabname TO formonly.tabname
      INPUT serv_name WITHOUT DEFAULTS FROM formonly.serv_name
      CLOSE WINDOW w_serv
      LET WRITE_SRV = 1
      RETURN serv_name

   END IF

END FUNCTION

###########################################################################
# Reformat an extralong sql statement and push it out to the report.
# Note - this does not handle a string with no spaces.  To do that add a
# test for a "," in the IF statement.
# FIX THIS.
# This routine also can break a quoted string which is undesirable at
# times.
###########################################################################

FUNCTION fold_and_push(stmt,brk)

DEFINE stmt, stmt2 CHAR(1024),
        brk, i, j SMALLINT

# brk is a switch to indicate give or not give a blank line after end.

   WHILE LENGTH(stmt) > 80         # while still over 80
      FOR i = 80 TO 1 STEP -1      # search backwards for a break
         IF stmt[i,i] = " " THEN   # found - cut it
            LET stmt2 = stmt[1,i]  # grab first part...
            OUTPUT TO REPORT dump_sql(stmt2)   # and write it
            LET j=i+1              # start of next part
            LET i = LENGTH(stmt)   # end of whole string
            LET stmt2 = stmt[j,i]  # grab remaining string
            LET stmt = stmt2       # put it back into starting string
            EXIT FOR               # start search again
         END IF
      END FOR
   END WHILE

   OUTPUT TO REPORT dump_sql(stmt)         # don't forget last part

   IF brk THEN
      OUTPUT TO REPORT dump_sql("")        # add a blank line
   END IF

END FUNCTION



############################################################################
# idx_parts(idxname, old_new)
# I grow weary of the same code in multiple locations.  This routine reads
# the parts[] structure from a sysindexes table and builds a column list
# thence.  It is called for indices and constraints.  Since constraints don't
# use the 'DESC' verb the parts structure should never have a negative value
# so don't worry about it.
############################################################################

FUNCTION idx_parts(p_idxname, old_new)

DEFINE  p_idxname       CHAR(18),
        p_tabname       CHAR(18),
        idxrec RECORD 
           tabid     INTEGER, 
           tabname   CHAR(18),
           idxtype   CHAR(1), 
           clustered CHAR(1)
        END RECORD,
        parts ARRAY [16] OF SMALLINT,
        i            SMALLINT,
        p_colname    CHAR(24),
        desc_sw      SMALLINT,
        strg, tmp_strg CHAR(80),
        idx_strng    CHAR(5000),
        old_new SMALLINT

   IF old_new = 0 THEN          # old
      IF ONLINE_SW THEN
         SELECT o_systables.tabid, o_systables.tabname, idxtype, clustered,  
             part1, part2, part3, part4, part5, part6, part7, part8, 
            part9, part10, part11, part12, part13, part14, part15, part16
         INTO idxrec.*, parts[1], parts[2], parts[3], parts[4],
                       parts[5], parts[6], parts[7], parts[8], 
                       parts[9], parts[10], parts[11], parts[12],
                       parts[13], parts[14], parts[15], parts[16] 
         FROM o_sysindexes, o_systables
         WHERE idxname = p_idxname
         AND o_sysindexes.tabid = o_systables.tabid 
      ELSE
         SELECT o_systables.tabid, o_systables.tabname, idxtype, clustered, 
            part1, part2, part3, part4, part5, part6, part7, part8 
         INTO idxrec.*, parts[1], parts[2], parts[3], parts[4], 
                       parts[5], parts[6], parts[7], parts[8] 
         FROM o_sysindexes, o_systables
         WHERE idxname = p_idxname
         AND o_sysindexes.tabid = o_systables.tabid 
      END IF
   ELSE                         # new
      IF ONLINE_SW THEN
         SELECT systables.tabid, systables.tabname, idxtype, clustered,  
             part1, part2, part3, part4, part5, part6, part7, part8, 
            part9, part10, part11, part12, part13, part14, part15, part16
         INTO idxrec.*, parts[1], parts[2], parts[3], parts[4],
                       parts[5], parts[6], parts[7], parts[8], 
                       parts[9], parts[10], parts[11], parts[12],
                       parts[13], parts[14], parts[15], parts[16] 
         FROM sysindexes, systables
         WHERE idxname = p_idxname
         AND sysindexes.tabid = systables.tabid 
      ELSE
         SELECT systables.tabid, systables.tabname, idxtype, clustered, 
            part1, part2, part3, part4, part5, part6, part7, part8 
         INTO idxrec.*, parts[1], parts[2], parts[3], parts[4], 
                       parts[5], parts[6], parts[7], parts[8] 
         FROM sysindexes, systables
         WHERE idxname = p_idxname
         AND sysindexes.tabid = systables.tabid 
      END IF
   END IF

   LET idx_strng = ""
                                        # add columns
   FOR i = 1 TO max_parts

      LET desc_sw = 0                   # switch for descending sort

      IF parts[i] = 0 THEN
         EXIT FOR
      ELSE IF parts[i] < 0 THEN         # negative indicates a DESC
            LET desc_sw =1 
            LET parts[i]=parts[i] * (-1)        # reset to get col
         END IF
      END IF
      
      IF old_new = 0 THEN               # old
         SELECT colname                 # get column name
           INTO p_colname
           FROM o_syscols
          WHERE tabid = idxrec.tabid
            AND colno = parts[i]
      ELSE
         SELECT colname                 # get column name
           INTO p_colname
           FROM syscolumns
          WHERE tabid = idxrec.tabid
            AND colno = parts[i]
      END IF
   
      IF desc_sw THEN                   # check for descending and fix
         LET p_colname = p_colname CLIPPED, " DESC"
      END IF

      LET idx_strng = idx_strng CLIPPED, " ", p_colname CLIPPED, ","

   END FOR

   LET i=LENGTH(idx_strng CLIPPED) - 1

   LET idx_strng = idx_strng[1,i]
   RETURN idx_strng

END FUNCTION

#############################################################################
# extract a "|" delimited field from a string
#############################################################################
FUNCTION ex_field(strng, fld)

DEFINE strng CHAR(130), fld, i, j, beg_pos, end_pos SMALLINT

   LET beg_pos = 1
   LET end_pos = 0

   LET i = 1            # index to string
   LET j = 0            # number of flds found so far

   WHILE j != fld       # while we haven't found the fld

      WHILE strng[i,i] != "|" AND i <= LENGTH(strng)
         LET i = i + 1
      END WHILE

      LET j = j + 1     # found one.

      IF j = (fld - 1) THEN   # the right one too
                              # note, will skip first time, which is ok
                              # since we initialized beg_pos to 1
         LET beg_pos = i + 1  # start point
      END IF

      IF j = fld THEN         # end point
         LET end_pos = i - 1
      END IF

      LET i = i + 1
   END WHILE            # j <= fld

   IF end_pos = 0 THEN 
      LET end_pos = LENGTH(strng)
   END IF

   RETURN strng[beg_pos, end_pos]

END FUNCTION

###########################################################################
# write out the server names so we have them the next time
###########################################################################

REPORT save_server(servers)

DEFINE  servers  RECORD # alternate servers
              old_server    CHAR(20),
              new_server    CHAR(20)
           END RECORD

OUTPUT
     LEFT MARGIN 0
     RIGHT MARGIN 80
     TOP MARGIN 0
     BOTTOM MARGIN 0
     PAGE LENGTH 1                      # no blank lines please


FORMAT
ON EVERY ROW
   PRINT servers.old_server clipped, "|", servers.new_server

END REPORT

############################################################################
# Easy way to dump to file
############################################################################

REPORT logg(logmsg)

   DEFINE logmsg CHAR(80)

   OUTPUT
     LEFT MARGIN 0
     RIGHT MARGIN 80
     TOP MARGIN 0
     BOTTOM MARGIN 0
     PAGE LENGTH 1                      # no blank lines please

   FORMAT

      ON EVERY ROW
     print logmsg clipped

END REPORT

{/* $Log:       dbdiff2.4gl,v $
Revision 1.4  95/06/14  12:49:50  12:49:50  jparker (Jack Parker)
s1/s2 switches made to work again (Cathy Kipp)
Typo in incorporating triggers/procedures o_systabs should be o_systables
Triggers and Procedures changed to use fold_and_push()
column can now be added BEFORE the first existing column  (Kerry)
Typo line 1033 (John Fowler)
log() changed to logg() (J Fowler)
Intervals with a precision (e.g. DAY(3) TO DAY) are now handled (jp)
Defaulted Constraints, User permissions and SPLs to ON.
Bug fix - when doing USER and not AUTH would crash - corrected.
SPL function no longer drops core.
General - added support for SPL, triggers and constraints.  Some bug fixes.

Revision 1.3  94/09/01  13:39:25  13:39:25  jparker (Jack Parker)
Corrected DATETIME/INTERVAL to print proper end points
Corrected DATETIME/INTERVAL to not include parens
Now handles DECIMAL(n) and DECIMAL(n,0)
Now handles DESCending indices.
Corrected syntax for BEFORE in the ALTER TABLE clause

Revision 1.2  94/05/30  16:05:31  16:05:31  jparker (Jack Parker)
now supports systabauth (-a) and sysusers (-u)
now supports individual or groups of tables (-t table_spec)
now supports on the fly changing of server names
Parens added to ALTER statement to support other versions of ISQL
Added status to db_check routine

 */}

###############################################################################
#
# Dialogue box library courtesy of Alan Popiel - Denver Co.
#
###############################################################################





{ module util_box.4gl - Dialog box utility functions
  author:       R. Alan Popiel,  President, Popiel Computing
  version:      2.10
  date:         04 Sep 1992


  NOTE:         This software is hereby placed in the public domain.  Popiel 
                Computing retains no rights or responsibility to this software.

                *** USE OF THIS SOFTWARE IS ENTIRELY AT YOUR OWN RISK. ***

                While Popiel Computing has made reasonable efforts to ensure 
                that these functions operate correctly, we make no claims as 
                their merchantability or fitness for any particular purpose.


  purpose:      This module contains utility functions for displaying dialog
                boxes, etc., on the screen.  All functions in this module 
                display a dialog box similar to this on the computer screen:

    +--------------------------+    upper left corner at 10,nn* or 'rw','cl'
    |     Centered 'title'     |    title and blank line omitted, if title = ""
    |                          |
    | 'msg_text', line 1       |   *nn will be computed to approximately
    | 'msg_text', line 2, etc. |    center the box horizontally in 80 cols.
    |                          |
    | 'ask_for' prompt string  |    'alert' does not use 'ask_for'
    +--------------------------+

  functions included:

    FUNCTION alert         - no 'ask_for' or value, 3 second delay, auto close
    FUNCTION alert_at      - same as above, with positioning

    FUNCTION button        - generalized button box handler
    FUNCTION button_at     - same as above, with positioning

    FUNCTION dialog        - generalized dialog box handler, no validation on
                                return value
    FUNCTION dialog_at     - same as above, with positioning

    FUNCTION notify        - 'ask_for' = "Press any key.", no return value
    FUNCTION notify_at     - same as above, with positioning

    FUNCTION accept_cancel - 'ask_for' = accept/cancel buttons, value in [AC]
    FUNCTION accept_cancel_at - same as above, with positioning

    FUNCTION screen_print  - 'ask_for' = screen/print/exit buttons, value in [SPX]
    FUNCTION screen_print_at - same as above, with positioning

    FUNCTION yes_no        - 'ask_for' = yes/no buttons, value in [YN]
    FUNCTION yes_no_at     - same as above, with positioning
}


###############################################################################

FUNCTION alert ( title, msg_text )

{ purpose:      Display an alert box with message, then close it.
  inputs:       1. title:    (optional) text of title for alert box.
                2. msg_text: text of message, with | between lines.
  output:       displayed alert box.
}
DEFINE  title   CHAR(64),
        msg_text CHAR(512)

CALL alert_at ( 10, -1, title, msg_text )

END FUNCTION { alert }


FUNCTION alert_at ( rw, cl, title, msg_text )

{ purpose:      Display an alert box with message, then close it.
  inputs:       1. rw:       row location of top edge of dialog box. 
                2. cl:       column location of left edge of dialog box
                3. title:    (optional) text of title for dialog box.
                4. msg_text: text of message, with | between lines.
  output:       displayed dialog box.
}
DEFINE  rw      SMALLINT,
        cl      SMALLINT,
        title   CHAR(64),
        msg_text CHAR(512)

let ident = "@(#)$Header: dialg_lib.4gl,v 1.2 94/01/26 16:06:25 hmgr Exp $"
LET atrow = rw
LET atcol = cl
LET ncols = LENGTH ( title ) + 2

IF ncols = 2 THEN               { Special case for alerts without titles: }
    LET msg_text = "|", msg_text CLIPPED, "||"
END IF                          { Prevent one-line box which fouls up menus. }

CALL parse_text ( msg_text )
                                { Compute box dimensions. }
LET ncols = ncols  + 2
LET nrows = nlines
IF LENGTH ( title ) > 0 THEN
    LET nrows = nrows + 2
END IF

CALL open_dialog_box ( title )

SLEEP 3

CALL close_dialog_box ()

END FUNCTION { alert_at }

###############################################################################

FUNCTION button ( title, msg_text, ask_for, okay ) { RETURNING CHAR(1) }

{ purpose:      Display dialog box with buttons.
  inputs:       1. title:    (optional) text of title for dialog box.
                2. msg_text: text of message, with | between lines.
                3. ask_for:  text of prompt message.
                4. okay:     list of acceptable input chars.  format: "[ABC]".
  outputs:      1. displayed dialog box.
                2. function value: character entered by user, validated against
                   "okay" characters.
}
DEFINE  title   CHAR(64),
        msg_text CHAR(512),
        ask_for CHAR(64),
        okay    CHAR(8)

RETURN button_at ( 10, -1, title, msg_text, ask_for, okay )

END FUNCTION { button }


FUNCTION button_at ( rw, cl, title, msg_text, ask_for, okay )
                { RETURNING CHAR(1) }

{ purpose:      Display dialog box with buttons.
  inputs:       1. rw:       row location of top edge of dialog box. 
                2. cl:       column location of left edge of dialog box
                3. title:    (optional) text of title for dialog box.
                4. msg_text: text of message, with | between lines.
                5. ask_for:  text of prompt message.
                6. okay:     list of acceptable input chars.  format: "[ABC]".
  outputs:      1. displayed dialog box.
                2. function value: character entered by user, validated against
                   "okay" characters.
}
DEFINE  rw      SMALLINT,
        cl      SMALLINT,
        title   CHAR(64),
        msg_text CHAR(512),
        ask_for CHAR(64),
        okay    CHAR(8),

        answer  CHAR(1),        { single character, input by user }
        last_ok SMALLINT        { position of last okay character }

LET atrow = rw
LET atcol = cl
LET ncols = LENGTH ( title ) + 2

CALL parse_text ( msg_text )
LET ask_for = buttons ( ask_for )

                                { Compute box dimensions. }
LET ncols = ncols  + 2
LET nrows = nlines + 2
IF LENGTH ( title ) > 0 THEN
    LET nrows = nrows + 2
END IF
 
CALL open_dialog_box ( title )
                                { Prompt for and get user response. }
LET INT_FLAG = FALSE
LET answer   = " "
LET okay     = UPSHIFT ( okay )
LET last_ok  = LENGTH  ( okay ) - 1
WHILE TRUE
    PROMPT ask_for CLIPPED, " " FOR CHAR answer

    CASE
    WHEN INT_FLAG
        LET INT_FLAG = FALSE
        LET answer   = okay[last_ok]
    WHEN LENGTH ( answer ) < 1 
        LET answer   = okay[2]
    OTHERWISE
        LET answer   = UPSHIFT ( answer )
    END CASE

    IF answer MATCHES okay THEN
        EXIT while
    ELSE
        ERROR ""
    END IF
END WHILE

CALL close_dialog_box ()

RETURN answer

END FUNCTION { button_at }

###############################################################################

FUNCTION dialog ( title, msg_text, ask_for ) { RETURNING CHAR(1) }

{ purpose:      Display generalized dialog box.
  inputs:       1. title:    (optional) text of title for dialog box
                2. msg_text: text of message, with | between lines
                3. ask_for:  (optional) text of prompt message
  outputs:      1. displayed dialog box
                2. function value: character entered by user, unvalidated
}
DEFINE  title    CHAR(64),
        msg_text CHAR(512),
        ask_for  CHAR(64)

RETURN dialog_at ( 10, -1, title, msg_text, ask_for )

END FUNCTION { dialog }


FUNCTION dialog_at ( rw, cl, title, msg_text, ask_for ) { RETURNING CHAR(1) }

{ purpose:      Display generalized dialog box.
  inputs:       1. rw:       row location of top edge of dialog box. 
                2. cl:       column location of left edge of dialog box
                3. title:    (optional) text of title for dialog box
                4. msg_text: text of message, with | between lines
                5. ask_for:  (optional) text of prompt message
  outputs:      1. displayed dialog box
                2. function value: character entered by user, unvalidated
}
DEFINE  rw      SMALLINT,
        cl      SMALLINT,
        title   CHAR(64),
        msg_text CHAR(512),
        ask_for CHAR(64),

        answer  CHAR(1)         { single character, input by user }

LET atrow = rw
LET atcol = cl
LET ncols = LENGTH ( title ) + 2

CALL parse_text ( msg_text )
                                { Set default prompt string, if necessary. }
IF LENGTH ( ask_for ) = 0 THEN
    LET ask_for = " Press any key to continue."
ELSE
    IF ask_for[1] != " " THEN
        LET ask_for = " ", ask_for CLIPPED
    END IF
END IF
                                { Compute box dimensions. }
IF LENGTH ( ask_for ) > ncols THEN
    LET ncols = LENGTH ( ask_for )
END IF
LET ncols = ncols  + 2
LET nrows = nlines + 2
IF LENGTH ( title ) > 0 THEN
    LET nrows = nrows + 2
END IF

CALL open_dialog_box ( title )
                                { Prompt for and get user response. }
PROMPT ask_for CLIPPED, " " FOR CHAR answer

CALL close_dialog_box ()

RETURN answer

END FUNCTION { dialog_at }

###############################################################################

FUNCTION notify ( title, msg_text )

{ purpose:      Display generalized dialog box.
  inputs:       1. title:    (optional) text of title for dialog box.
                2. msg_text: text of message, with | between lines.
  output:       displayed dialog box.
}
DEFINE  title   CHAR(64),
        msg_text CHAR(512)

CALL notify_at ( 10, -1, title, msg_text )

END FUNCTION { notify }
 

FUNCTION notify_at ( rw, cl, title, msg_text )

{ purpose:      Display generalized dialog box.
  inputs:       1. title:    (optional) text of title for dialog box.
                2. msg_text: text of message, with | between lines.
  output:       displayed dialog box.
}
DEFINE  rw      SMALLINT,
        cl      SMALLINT,
        title   CHAR(64),
        msg_text CHAR(512),

        answer  CHAR(1)         { single character, input by user }

LET answer = dialog_at ( rw,cl, title, msg_text, " Press space bar to continue." )

END FUNCTION { notify }
 
###############################################################################

{ Generally useful button box routines. }
 
###############################################################################
 
FUNCTION accept_cancel ( title, msg_text ) { RETURNING CHAR(1) }

{ purpose:      Display dialog box with accept/cancel buttons.
  inputs:       1. title:    (optional) text of title for dialog box
                2. msg_text: text of message, with | between lines
  outputs:      1. displayed dialog box
                2. function value: character entered by user, either "A" or "C"
}
DEFINE  title   CHAR(64),
        msg_text CHAR(512)

RETURN button_at ( 10, -1, title, msg_text, " ((Accept))  (Cancel)", "[AC]" )

END FUNCTION { accept_cancel }


FUNCTION accept_cancel_at ( rw, cl, title, msg_text ) { RETURNING CHAR(1) }

{ purpose:      Display dialog box with accept/cancel buttons.
  inputs:       1. rw:       row location of top edge of dialog box. 
                2. cl:       column location of left edge of dialog box
                3. title:    (optional) text of title for dialog box
                4. msg_text: text of message, with | between lines
  outputs:      1. displayed dialog box
                2. function value: character entered by user, either "A" or "C"
}
DEFINE  rw      SMALLINT,
        cl      SMALLINT,
        title   CHAR(64),
        msg_text CHAR(512)

RETURN button_at ( rw, cl, title, msg_text, " ((Accept))  (Cancel)", "[AC]" )

END FUNCTION { accept_cancel_at }

###############################################################################
 
FUNCTION screen_print ( title, msg_text ) { RETURNING CHAR(1) }

{ purpose:      Display dialog box with screen/print/exit buttons.
  inputs:       1. title:    (optional) text of title for dialog box
                2. msg_text: text of message, with | between lines
  outputs:      1. displayed dialog box
                2. function value: character entered by user, one of "S","P","X"
}
DEFINE  title   CHAR(64),
        msg_text CHAR(512)

RETURN button_at ( 10, -1, title, msg_text, 
                 " ((Screen))  (Print)  (eXit)", "[SPX]" )

END FUNCTION { screen_print_at }


FUNCTION screen_print_at ( rw, cl, title, msg_text ) { RETURNING CHAR(1) }

{ purpose:      Display dialog box with screen/print/exit buttons.
  inputs:       1. rw:       row location of top edge of dialog box. 
                2. cl:       column location of left edge of dialog box
                3. title:    (optional) text of title for dialog box
                4. msg_text: text of message, with | between lines
  outputs:      1. displayed dialog box
                2. function value: character entered by user, one of "S","P","X"
}
DEFINE  rw      SMALLINT,
        cl      SMALLINT,
        title   CHAR(64),
        msg_text CHAR(512)

RETURN button_at ( rw, cl, title, msg_text,
                 " ((Screen))  (Print)  (eXit)", "[SPX]" )

END FUNCTION { screen_print_at }

###############################################################################
 
FUNCTION yes_no ( title, msg_text ) { RETURNING CHAR(1) }

{ purpose:      Display dialog box with yes/no buttons.
  inputs:       1. title:    (optional) text of title for dialog box
                2. msg_text: text of message, with | between lines
  outputs:      1. displayed dialog box
                2. function value: character entered by user, either "Y" or "N"
}
DEFINE  title    CHAR(64),
        msg_text CHAR(512)

RETURN button_at ( 10, -1, title, msg_text, "  ((Yes))  (No)", "[YN]" )
 
END FUNCTION { yes_no }

 
FUNCTION yes_no_at ( rw, cl, title, msg_text ) { RETURNING CHAR(1) }

{ purpose:      Display dialog box with yes/no buttons.
  inputs:       1. rw:       row location of top edge of dialog box. 
                2. cl:       column location of left edge of dialog box
                3. title:    (optional) text of title for dialog box
                4. msg_text: text of message, with | between lines
  outputs:      1. displayed dialog box
                2. function value: character entered by user, either "Y" or "N"
}
DEFINE  rw      SMALLINT,
        cl      SMALLINT,
        title   CHAR(64),
        msg_text CHAR(512)

RETURN button_at ( rw, cl, title, msg_text, "  ((Yes))  (No)", "[YN]" )
 
END FUNCTION { yes_no_at }

###############################################################################

{ Internal routines.  Not intended for external use. }

###############################################################################

FUNCTION buttons ( ask_for ) { RETURNING CHAR(64) }

{ purpose:      Construct a prompt string containing centered "buttons".
  inputs:       1. ask_for: simulated buttons, as prompt string.
                2. ncols: number of columns in box.
  outputs:      1. functions value: centered prompt string.
                2. ncols: possibly incremented number of columns in box.
  NOTE: This function is intended for use ONLY by routines in this module. 
}
DEFINE  ask_for CHAR(64),
        blanks  CHAR(64),
        n       SMALLINT

IF LENGTH ( ask_for ) > ncols THEN
    LET ncols = LENGTH ( ask_for )
END IF
LET blanks = "                                                                "
LET n = ( ncols - LENGTH ( ask_for ) ) / 2
IF n > 0 THEN
    LET ask_for = blanks[1,n], ask_for CLIPPED
END IF
RETURN ask_for

END FUNCTION { buttons }

###############################################################################

FUNCTION close_dialog_box ()
 
{ purpose:      Close window and reset position of PROMPT LINE.
  input:        none.
  outputs:      1. closed dialog box.
                2. OPTIONS reset.
  NOTE: This function is intended for use ONLY by routines in this module. 
}
CLOSE WINDOW dialog_box
OPTIONS PROMPT LINE FIRST

END FUNCTION { close_dialog_box }

###############################################################################

FUNCTION open_dialog_box ( title )

{ purpose:      Open window and display title, if any, and message text.
  input:        title:    (optional) text of title for dialog box
  outputs:      1. displayed dialog box.
                2. function value: 0 or 2, used as affset for positioning the
                   rest of the line in the dialog box.
  NOTE: This function is intended for use ONLY by routines in this module. 
}
DEFINE  title   CHAR(64),
 
        j, k, n SMALLINT,       { scratch indices used for positioning, etc. }
        topline CHAR(64)        { variable used to construct title line }

IF atrow <= 0 OR atrow >= 50 THEN { Test for uninitialized row. }
    LET atrow = 10
END IF
IF atcol <= 0 OR atcol >= 70 THEN { Test for "center box" flag: atcol <= 0 }
    LET atcol = (80 - ncols) / 2 + 1
END IF
OPEN WINDOW dialog_box AT atrow,atcol WITH nrows ROWS, ncols COLUMNS
    ATTRIBUTE (BORDER, PROMPT LINE LAST)
IF LENGTH ( title ) > 0 THEN
    LET topline =
        "                                                                "
    LET n = ( ncols - LENGTH ( title ) ) / 2
    LET topline = topline[1,n], title CLIPPED
    DISPLAY topline AT 1,1
    LET n = 2
ELSE
    LET n = 0
END IF
                                { Display message text. }
FOR j = 1 TO nlines
    LET k = j + n
    DISPLAY textline[j] AT k,2
END FOR

END FUNCTION { open_dialog_box }

###############################################################################

FUNCTION parse_text ( msg_text )

{ purpose:      Parse msg_text into lines.
  inputs:       1. msg_text: text of message, with | between lines
                2. ncols: (minimum) number of columns in box
  outputs:      1. ncols: (possibly) updated number of columns in box, based
                          on widest line in textline array
                2. nlines: number of lines in textline array 
                3. textline: array containing separated lines of msg_text
  NOTE: This function is intended for use ONLY by routines in this module. 
}
DEFINE  msg_text CHAR(512),     { text of message, with | between lines }

        j, k, n SMALLINT,       { indexes used to parse msg_text into line }
        len     SMALLINT        { length of msg_text string }

LET len    = LENGTH ( msg_text )
LET n      = 0
LET nlines = 0

WHILE n < len AND nlines < 10
                                { Search for line separator. }
    LET j = n + 1
    LET k = j
    WHILE msg_text[k] != "|" AND k < len
        LET k = k + 1
    END WHILE
    IF k - j > 72 THEN
        LET k = j + 72
    END IF
                                { Separator found.  Check for special cases. }
    LET n = k
    IF msg_text[k] = "|" THEN
        LET k = k - 1
    END IF
                                { Move line into array of lines. }
    LET nlines = nlines + 1
    IF k <= j THEN
        LET textline[nlines] = ""
    ELSE
        LET textline[nlines] = msg_text[j,k]
    END IF
    IF LENGTH ( textline[nlines] ) > ncols THEN
        LET ncols = LENGTH ( textline[nlines] )
    END IF
END WHILE       

END FUNCTION { parse_text }

###############################################################################

# lib_4gl.4gl
# 4GL-RDS routines which emulate the lib_c.c routines.
# Kerry Sainsbury 1994.

FUNCTION translate(l_old, l_new, l_in)
DEFINE   l_old            CHAR(256),
         l_new            CHAR(256),
         l_in             CHAR(512),
         l_cnt            SMALLINT,
         l_oldlth         SMALLINT,
         l_difflth        SMALLINT,
         l_for            SMALLINT,
         i                SMALLINT

   LET l_cnt = LENGTH(l_in)
   LET l_oldlth = LENGTH(l_old)
   LET l_difflth = l_old - LENGTH(l_new)
   LET l_for = l_cnt - l_oldlth
   FOR i = 1 TO l_for
      IF l_in[i, i+l_oldlth-1]=l_old CLIPPED THEN
         IF i > 1 THEN
            LET l_in = l_in[1,i-1],l_new CLIPPED,
                l_in[i+l_oldlth,l_cnt] CLIPPED
         ELSE
            LET l_in = l_new CLIPPED,
                l_in[i+l_oldlth,l_cnt] CLIPPED
         END IF
         LET l_for = l_for - l_difflth
         LET l_cnt = l_cnt - l_difflth
         LET i = i - l_difflth
      END IF
   END FOR
   RETURN l_in
END FUNCTION


FUNCTION fle_acc(serverfle, dummy) 
DEFINE   serverfle        CHAR(80),     # dbdiff2 compatability parameters
         dummy            INTEGER

DEFINE   l_unix_cmd       CHAR(300)

DEFINE   l_text           CHAR(300),
         l_tempfile       CHAR(100),
         l_delimiters     CHAR(6),
         l_delim          CHAR(1),
         i                INTEGER

   LET l_unix_cmd = "cat ", serverfle CLIPPED," 2>&1 > /dev/null | grep shutup"
   RUN l_unix_cmd RETURNING i
   IF i !=0 THEN
      RETURN 1
   END IF

   LET l_unix_cmd = "cat ", serverfle CLIPPED

   WHENEVER ANY ERROR CONTINUE  # It's a bit ugly I know..
   DROP TABLE picklist
   CREATE TEMP TABLE picklist
         (ptext   char(300),
          pline   serial) WITH NO LOG
   WHENEVER ANY ERROR STOP

   LET l_tempfile = "/tmp/",fgl_getenv("LOGNAME") CLIPPED, TIME,".tmp"

   LET l_delimiters = ASCII(94),ASCII(92),ASCII(96),ASCII(124),ASCII(126),
                      ASCII(95)
   FOR i = 1 TO 6
      LET l_delim = l_delimiters[i]
      LET l_text = l_unix_cmd CLIPPED,
                 " | sed -e 's/$/",l_delim,"0",l_delim,"/' > ", l_tempfile
      RUN l_text
      WHENEVER ANY ERROR CONTINUE
      LOAD FROM l_tempfile DELIMITER l_delim
      INSERT INTO picklist 
      WHENEVER ANY ERROR STOP
      IF status =0 THEN
         LET i = 999
         EXIT FOR
      END IF
   END FOR

   IF i != 999 THEN
      ERROR "ERROR: open_flat() is unable to process ASCII file ", l_tempfile CLIPPED
      SLEEP 5
#     LET l_unix_cmd = "ERROR: open_flat() is unable to process an ASCII file ",
#                      "which contains all of the following characters: ",
#                       l_delimiters CLIPPED,". Please advise your support ",
#                       "company of this error and suggest they look in the ",
#                       "file ",l_tempfile CLIPPED,"."
#     CALL message_prompt(l_unix_cmd, "")
      RETURN 1
   END IF

   DECLARE flat_curs CURSOR FOR
    SELECT ptext, pline FROM picklist
     ORDER BY pline
   OPEN flat_curs
   IF status !=0 THEN
      RETURN 1
   END IF

   LET l_text = "rm -f ",l_tempfile
   RUN l_text WITHOUT WAITING
   RETURN 1

END FUNCTION


FUNCTION read_line(dummy1, dummy2)
DEFINE   dummy1           CHAR(80),
         dummy2           INTEGER
DEFINE   l_text           CHAR(300),
         l_retcode        SMALLINT

   FETCH flat_curs INTO l_text
   IF status !=0 THEN
      LET l_text = ""
      LET l_retcode = 0
   ELSE
      LET l_retcode = 1
   END IF
   IF l_text IS NULL THEN LET l_text = " " END IF

   RETURN l_text, l_retcode
END FUNCTION

@EOF

chmod 644 dbdiff2_new.4gl

echo x - dbdiff_old.4gl
cat >dbdiff_old.4gl <<'@EOF'
##############################################################################
#
# dbdiff2.4gl:  A shortened version of dbutil - a utility to generate SQL
#	       to make one version of a database match another.
#
# syntax:     dbdiff2 [-db SE|OL] engine type - SE or Online (Online=default)
#		     -od dbname	old database name 
#		     -nd dbname	new database name
#		     [-o]	output file name (default = /tmp/mod_db.sql)
#		     [-a]	do Authority tables (permissions)
#		     [-c]	DON'T do Constraints 
#		     [-u]	DON'T do Users 
#                    [-s1]      Only do segment 1
#                    [-s2]      Only do segment 2
#                    [-dbg]     debug mode on
#                    [-t spec ]       table name specification
#		     [-trg]     triggers
#		     [-spl]     DON'T do Stored Procedures
#		     [-q]       quiet
#		     [-svr filename]     server file name
#		     [-lg]      turn on logging
#		     [-lgf filename]     log file name
#		     [?]	usage 
#
#	This generates SQL code to either change the schema 
#
#	Placed in the public domain - with no gaurantees whatsoever that it
#	will work.  If it breaks something of yours then you should be more
#	careful but it's NMFP.
#
#       This program was written in stolen hours by me and then added
#       to by the denizens of comp.databases.informix.  It has been tested
#	and generally accepted as a great utility by that newsgroup, but
#       all of them religiously check the SQL it generates before running it.
#
#	I highly recommend that you perform these same checks.
#
#       Inspired by Dave Snyder.
#
#       Jack Parker March, 1994   Kerry Sainsbury November, 1994
#					(procedures, triggers)
#
##############################################################################
GLOBALS

   DEFINE  ONLINE_SW,              	# what type of engines
	   MODE_ANSI,                   # whether this is an ANSI database 
           SEG1, SEG2,			# only do part1 or part2.
	   DEBUG,			# debug mode
	   AUTH_SW, USER_SW,        	# Authority tables, user_sw too
           CONST_SW,         		# Constraints
	   TRIG_SW, SPL_SW,		# Triggers and Stored Procedures
	   QUIET_SW,			# no interaction w/ user.
	   WRITE_SRV,			# Server data needs writing switch
	   LOG_SW			# turn on logging (of the program)
           SMALLINT

   DEFINE  db_new, db_old  CHAR(64)	# new and old database names

   DEFINE  ofile_name      CHAR(64)	# output file name

   DEFINE  msg	           CHAR(80)	# communicate w/ user

   DEFINE  datatype ARRAY[40] OF CHAR(20), # for coltype conversions
           datetype ARRAY[16] OF CHAR(11), 
           intvray  ARRAY[16] OF RECORD
              start_point SMALLINT,
              end_point SMALLINT
           END RECORD

   DEFINE  sql_strgs ARRAY[50] OF CHAR(80), # temp space for formatting SQL
           sql_idx         SMALLINT     # index to same

   DEFINE  max_parts       SMALLINT	# how many columns to an index

   DEFINE  second_round    SMALLINT     # switch to fix logic prob in psh_strg()

   DEFINE  oplog ARRAY[500] OF CHAR(80), # operations log and index
           op_idx          SMALLINT
      
   DEFINE  tb_name          CHAR(28)     # Table name pattern

   DEFINE  servers  ARRAY [20] OF RECORD	# alternate servers
              old_server    CHAR(20),
              new_server    CHAR(20)
           END RECORD,
           max_servers    SMALLINT

   DEFINE  logfile CHAR(80)		# name of logfile
   DEFINE  serverfle CHAR(80)		# name of flat file w/ server names
					# format = "server1|server2"
END GLOBALS


MAIN

   DEFINE i SMALLINT

   CALL hskpng()		# program init

   CALL load_schema()		# get the old schema

   IF SEG2 THEN			
      CALL chg_tabs()		# table changes

      CALL chg_idx()		# index changes

      IF AUTH_SW THEN
		# have to do columns first, cause there is no way to
		# revoke column level privileges, so I'm going to have
		# to wipe the entire table first and then re-grant.  would
		# be a pain to do tables and then wipe them from colauth()
         CALL chg_auth()	# permissions
      END IF

      IF USER_SW THEN		# user privs.
         CALL chg_user()
      END IF
      
      IF CONST_SW THEN		# constraints
         CALL chg_constr()
      END IF

      IF TRIG_SW THEN		# triggers
         CALL chg_trigs()
      END IF

      IF SPL_SW THEN		# stored procedures
         CALL chg_spl()
      END IF

   END IF

   FINISH REPORT dump_SQL

			# new server data collected, save it
   IF WRITE_SRV THEN
      START REPORT save_server TO serverfle
      FOR i = 1 TO max_servers
         IF LENGTH(servers[i].old_server) > 0 THEN
            OUTPUT TO REPORT save_server(servers[i].*)
         END IF
      END FOR
      FINISH REPORT save_server
   END IF

			# close logging file (opened in hskpng())
   IF LOG_SW THEN
      FINISH REPORT logg
   END IF

END MAIN


#######################################################################
# Get the old catalogues
#######################################################################
FUNCTION load_schema()

   DEFINE os_cmd CHAR(220),
	  unl_stmt CHAR(500)

   IF SEG1 THEN
      LET msg= "Dumping old schema..."
      CALL op_log(msg)
      DATABASE db_old

				# systables
      LET unl_stmt = 
           "SELECT tabname, tabid, tabtype ",
           "FROM ""informix"".systables WHERE tabid > 99 ",
           "AND tabname matches '", tb_name clipped, "'"

      UNLOAD TO "systable.unl" unl_stmt

				# syssyntable (synonyms)
      LET unl_stmt = 
	   "SELECT a.tabid, a.servername, a.dbname, a.owner,  ",
           "a.tabname, a.btabid FROM ""informix"".syssyntable a, ",
           """informix"".systables WHERE a.tabid > 99 ",
           "and systables.tabid=a.tabid ",
           "AND systables.tabname matches '", tb_name clipped, "'"

      UNLOAD TO "syssyntab.unl" unl_stmt

				# syscolumns
      LET unl_stmt = 
	   "SELECT a.colname, a.tabid, a.colno, a.coltype, a.collength ",
           "FROM ""informix"".syscolumns a, ",
           """informix"".systables WHERE a.tabid > 99 ",
           "and systables.tabid=a.tabid ",
           "AND tabname matches '", tb_name clipped, "'"

      UNLOAD TO "syscol.unl" unl_stmt

				# sysviews
      LET unl_stmt = 
	   "SELECT a.tabid, a.seqno, a.viewtext ",
           "FROM ""informix"".sysviews a, ""informix"".systables ",
           "WHERE a.tabid > 99 and systables.tabid=a.tabid ",
           "AND tabname matches '", tb_name clipped, "'"

      UNLOAD TO "sysview.unl" unl_stmt

      IF ONLINE_SW THEN

				# sysindexes
# Note took out the NOT MATCHES ' *' - we need constraints now.
         LET unl_stmt = 
	   "SELECT a.idxname, a.tabid, a.idxtype, a.clustered, a.part1, ",
	   "a.part2, a.part3, a.part4, a.part5, a.part6, a.part7, a.part8, ",
           "a.part9, a.part10, a.part11, a.part12, a.part13, a.part14, ",
           "a.part15, a.part16 FROM ""informix"".sysindexes a, ",
           """informix"".systables WHERE a.tabid > 99 ",
           "and systables.tabid=a.tabid ",
           "AND tabname matches '", tb_name clipped, "'"

         UNLOAD TO "sysind.unl" unl_stmt
      ELSE

# Note took out the NOT MATCHES ' *' - we need constraints now.
         LET unl_stmt = 
	   "SELECT a.idxname, a.tabid, a.idxtype, a.clustered, a.part1, ",
	   "a.part2, a.part3, a.part4, a.part5, a.part6, a.part7, a.part8 ",
           "FROM ""informix"".sysindexes a, ",
           """informix"".systables WHERE a.tabid > 99 ",
           "and systables.tabid=a.tabid ",
           "AND tabname matches '", tb_name clipped, "'"

         UNLOAD TO "sysind.unl" unl_stmt

      END IF

      IF AUTH_SW THEN

				# systabauth and syscolauth
         LET unl_stmt = 
	   "SELECT a.grantor, a.grantee, a.tabid, a.tabauth ",
           "FROM ""informix"".systabauth a, ",
           """informix"".systables WHERE a.tabid > 99 ",
           "and systables.tabid=a.tabid ",
           "AND tabname matches '", tb_name clipped, "'"

         UNLOAD TO "systabauth.unl" unl_stmt

         LET unl_stmt = 
	   "SELECT a.grantor, a.grantee, a.tabid, a.colno, a.colauth ",
           "FROM ""informix"".syscolauth a, ",
           """informix"".systables WHERE a.tabid > 99 ",
           "and systables.tabid=a.tabid ",
           "AND tabname matches '", tb_name clipped, "'"

         UNLOAD TO "syscolauth.unl" unl_stmt

      END IF

      IF USER_SW THEN

				# sysusers
         UNLOAD TO "sysusers.unl" 
	   SELECT username, usertype FROM "informix".sysusers 

      END IF

				# constraints
				# sysconstraints, syscoldepend, syschecks
      IF CONST_SW THEN
         UNLOAD TO "sysconstr.unl" 
	   SELECT constrid, constrname, owner, tabid, constrtype, idxname 
	     FROM "informix".sysconstraints

         UNLOAD TO "syscoldep.unl" 
	   SELECT constrid, tabid, colno
	     FROM "informix".syscoldepend

         UNLOAD TO "syschecks.unl" 
	   SELECT constrid, type, seqno, checktext
	     FROM "informix".syschecks

# Note: updrule, delrule, matchtype, and pendant are reserved for future use
         UNLOAD TO "sysrefs.unl" 
	   SELECT constrid, primary, ptabid, updrule, delrule, matchtype, 
		  pendant
	     FROM "informix".sysreferences
      END IF

				# systriggers, systrigbody
      IF TRIG_SW THEN
         LET unl_stmt =
           'SELECT a.trigid, a.trigname, a.owner, a.tabid, a.event, a.old, a.new',
            ' FROM "informix".systriggers a, "informix".systables  ',
            'WHERE systables.tabid > 99 and systables.tabid=a.tabid ',
              'AND tabname matches "', tb_name clipped, '"'

         UNLOAD TO "systrigg.unl" unl_stmt

         LET unl_stmt =
           'SELECT a.trigid, a.datakey, a.seqno, a.data ',
             'FROM "informix".systrigbody a, "informix".systriggers, ',
                  '"informix".systables  ',
            'WHERE systables.tabid > 99 and systables.tabid=systriggers.tabid ',
              'AND systriggers.trigid = a.trigid ',
              'AND tabname matches "', tb_name clipped, '" ',
              'AND datakey IN ("D", "A")'

         UNLOAD TO "systrigb.unl" unl_stmt
      END IF

				# sysprocedures, sysprocbody
      IF SPL_SW THEN
         LET unl_stmt =
           'SELECT b.procid, b.seqno, b.data ',
             'FROM "informix".sysprocbody b ',
            'WHERE datakey = "T"'

         UNLOAD TO "sysprocb.unl" unl_stmt

         LET unl_stmt =
           'SELECT a.procname, a.owner, a.procid ',
             'FROM "informix".sysprocedures a '

         UNLOAD TO "sysproc.unl" unl_stmt
      END IF

      CLOSE DATABASE	# Think we have all we need from there.
   END IF

				# Load the old schema into the new database
   IF SEG2 THEN
      LET msg = "Loading old schema..."
      CALL op_log(msg)
      DATABASE db_new

				# systables
      CREATE TEMP TABLE o_systables (tabname char(18), tabid integer,
                                   tabtype CHAR(1)) WITH NO LOG
      LOAD FROM "systable.unl" INSERT INTO o_systables

				# syssyntable
      CREATE TEMP TABLE o_syssyntab (tabid integer, servername char(18), 
           dbname char(18), owner char(8), ntabname char(18), btabid integer) 
           WITH NO LOG
      LOAD FROM "syssyntab.unl" INSERT INTO o_syssyntab
   
				# syscolumns
      CREATE TEMP TABLE o_syscols ( colname char(18), tabid integer, 
         colno smallint, coltype smallint, collength smallint) WITH NO LOG
      LOAD FROM "syscol.unl" INSERT INTO o_syscols
      CREATE UNIQUE INDEX cl1 ON o_syscols(tabid, colname)
   
				# sysviews
      CREATE TEMP TABLE o_sysviews (tabid integer, seqno smallint, 
         viewtext char(64)) WITH NO LOG
      LOAD FROM "sysview.unl" INSERT INTO o_sysviews
   
				# sysindexes
      IF ONLINE_SW THEN
         CREATE TEMP TABLE o_sysindexes (idxname char(18), 
            tabid integer, idxtype char(1), clustered char(1), part1 smallint,
            part2 smallint, part3 smallint, part4 smallint, part5 smallint,
            part6 smallint, part7 smallint, part8 smallint, part9 smallint,
            part10 smallint, part11 smallint, part12 smallint,
            part13 smallint, part14 smallint, part15 smallint, part16 smallint)
            WITH NO LOG
      ELSE
         CREATE TEMP TABLE o_sysindexes (idxname char(18), 
            tabid integer, idxtype char(1), clustered char(1), part1 smallint,
            part2 smallint, part3 smallint, part4 smallint, part5 smallint,
            part6 smallint, part7 smallint, part8 smallint) WITH NO LOG
      END IF
      LOAD FROM "sysind.unl" INSERT INTO o_sysindexes

				# systabauth, syscolauth
      IF AUTH_SW THEN
         CREATE TEMP TABLE o_systabauth (grantor CHAR(8), grantee CHAR(8), 
                           tabid INTEGER, tabauth CHAR(8)) WITH NO LOG
         LOAD FROM "systabauth.unl" INSERT INTO o_systabauth

         CREATE TEMP TABLE o_syscolauth (grantor CHAR(8), grantee CHAR(8), 
               tabid INTEGER, colno SMALLINT, colauth CHAR(3)) WITH NO LOG
         LOAD FROM "syscolauth.unl" INSERT INTO o_syscolauth
      END IF

      IF USER_SW THEN		# user privs.
         CREATE TEMP TABLE o_sysusers (username CHAR(8), usertype CHAR(1)) 
               WITH NO LOG
         LOAD FROM "sysusers.unl" INSERT INTO o_sysusers
      END IF
   
				# sysconstraints, syscoldepend, syschecks
      IF CONST_SW THEN
         CREATE TEMP TABLE o_sysconstraints (constrid integer, 
		constrname CHAR(18), owner CHAR(8), tabid INTEGER, 
		constrtype CHAR(1), idxname CHAR(18)) WITH NO LOG
         LOAD FROM "sysconstr.unl" INSERT INTO o_sysconstraints

         CREATE TEMP TABLE o_syscoldepend (constrid integer, tabid integer,
			colno smallint) WITH NO LOG
         LOAD FROM "syscoldep.unl" INSERT INTO o_syscoldepend

         CREATE TEMP TABLE o_syschecks (constrid integer, type char(1), seqno 
		SMALLINT, checktext CHAR(32)) WITH NO LOG
         LOAD FROM "syschecks.unl" INSERT INTO o_syschecks

# Note: updrule, delrule, matchtype, and pendant are reserved for future use
         CREATE TEMP TABLE o_sysreferences (constrid integer, prim integer,
		ptabid INTEGER, updrule CHAR(1), delrule CHAR(1), matchtype
                CHAR(1), pendant CHAR(1)) WITH NO LOG
         LOAD FROM "sysrefs.unl" INSERT INTO o_sysreferences
      END IF

				# systriggers, systrigbody
      IF TRIG_SW THEN
         CREATE TEMP TABLE o_systriggers (trigid integer, trigname char(18),
            owner char(18), tabid int, event char(1), old char(18), 
            new char(18)) WITH NO LOG
         LOAD FROM "systrigg.unl" INSERT INTO o_systriggers

         CREATE TEMP TABLE o_systrigbody (trigid integer, datakey char(1),
            seqno int, data char(256)) WITH NO LOG
         LOAD FROM "systrigb.unl" INSERT INTO o_systrigbody
      END IF

				# sysprocgers, sysprocbody
      IF SPL_SW THEN
         CREATE TEMP TABLE o_sysprocbody (procid integer, seqno int, 
            data char(256)) WITH NO LOG
         LOAD FROM "sysprocb.unl" INSERT INTO o_sysprocbody

         CREATE TEMP TABLE o_sysprocedures (procname char(18), owner char(8),
            procid int) WITH NO LOG
         LOAD FROM "sysproc.unl" INSERT INTO o_sysprocedures
      END IF
		
				# clean up
         LET os_cmd = 
          "rm -f systable.unl syssyntab.unl syscol.unl sysind.unl sysview.unl ",
	        "systabauth.unl syscolauth.unl sysusers.unl, sysconstr.unl ",
		"syscoldep.unl syschecks.unl sysrefs.unl systrigg.unl ",
                "systrigb.unl sysprocb.unl sysproc.unl"
         RUN os_cmd
   
      END IF	# SEG2

END FUNCTION

FUNCTION chg_tabs()

   DEFINE tabrec RECORD			# tables
	     tabname      CHAR(18),
             tabid        INTEGER,
	     tabtype      CHAR(1)
          END RECORD,
	  synrec RECORD			# synonyms
             servername CHAR(18), 
	     dbname     CHAR(18), 
	     ntabname   CHAR(18), 
	     btabid     INTEGER
	  END RECORD,
	  colrec RECORD			# columns
	     colname      CHAR(18),
	     collength    SMALLINT, 
	     coltype      SMALLINT, 
	     colno        SMALLINT
          END RECORD,			# comparison array
	  chk_cols ARRAY[500] OF RECORD
             o_colname   CHAR(18),
             o_coltype   SMALLINT,
             o_collength SMALLINT,
             o_colno     SMALLINT,
             n_colname   CHAR(18),
             n_coltype   SMALLINT,
             n_collength SMALLINT,
             n_colno     SMALLINT
          END RECORD,
	  exp_col SMALLINT,
	  tmp_strg CHAR(80),
	  strg CHAR(80),
   	  i, j, k	   SMALLINT     # junk variables


   LET sql_idx = 1
##################################
   LET msg =  "Now producing DROPs..."
   CALL op_log(msg)
   						# Drop tables
						# pretty basic
   DECLARE drop_curs CURSOR FOR
   SELECT tabname, tabtype
     FROM "informix".systables
    WHERE tabid > 99 AND tabname MATCHES tb_name
      AND NOT EXISTS
         (SELECT tabname FROM o_systables
           WHERE "informix".systables.tabname = o_systables.tabname)

   FOREACH drop_curs INTO tabrec.tabname, tabrec.tabtype
      CASE tabrec.tabtype

      WHEN 'T' 					# Table
         LET strg = "DROP TABLE ", 
                    tabrec.tabname clipped, 
                    ";"
         OUTPUT TO REPORT dump_sql(strg)
         OUTPUT TO REPORT dump_sql("")		# blank line

      WHEN 'S'					# Synonym
         LET strg = "DROP SYNONYM ", 
                    tabrec.tabname clipped, 
                    ";"
         OUTPUT TO REPORT dump_sql(strg)
         OUTPUT TO REPORT dump_sql("")		# blank line

      WHEN 'V'					# View
         LET strg = "DROP VIEW ", 
                    tabrec.tabname clipped, 
                    ";"
         OUTPUT TO REPORT dump_sql(strg)
         OUTPUT TO REPORT dump_sql("")		# blank line

      #WHEN 'L'					# SE Log

      #WHEN 'P'					# Private synonym 
                                                # or synonym in ANSI
      END CASE
   END FOREACH
   FREE drop_curs

##################################
   LET msg =  "Now producing CREATEs..."
   CALL op_log(msg)
   						# Add tables
						# format create statements
   DECLARE add_curs CURSOR FOR
   SELECT tabname, tabid, tabtype
     FROM o_systables
    WHERE tabname MATCHES tb_name AND NOT EXISTS
         (SELECT tabname FROM "informix".systables
           WHERE "informix".systables.tabname = o_systables.tabname)

   PREPARE c_prp FROM
   "SELECT colname, collength, coltype, colno FROM o_syscols WHERE tabid = ? ORDER BY colno"
   DECLARE get_col CURSOR FOR c_prp

   PREPARE vc FROM
   "SELECT viewtext, seqno FROM o_sysviews WHERE tabid = ? ORDER BY seqno"

   DECLARE view_curs CURSOR FOR vc

   FOREACH add_curs INTO tabrec.tabname, tabrec.tabid, tabrec.tabtype

      CASE tabrec.tabtype
         WHEN 'T'			# table

            LET tmp_strg = "CREATE TABLE ", 
                           tabrec.tabname clipped, 
                           " ("

            OPEN get_col USING tabrec.tabid	# all columns for this table

            FOREACH get_col INTO colrec.colname, 
                                 colrec.collength, 
                                 colrec.coltype,
	                         colrec.colno

				      # format the string for this column
               LET strg = colrec.colname clipped, 
                          column 27, 
                          col_cnvrt(colrec.coltype,colrec.collength)
               CALL psh_strg(strg,tmp_strg,",")
      
            END FOREACH

               CALL psh_strg("END","",");")

         WHEN 'S'			# synonym

            SELECT servername, dbname, ntabname, btabid
              INTO synrec.servername, synrec.dbname, synrec.ntabname, 
                   synrec.btabid
              FROM o_syssyntab
             WHERE tabid = tabrec.tabid

            IF LENGTH(synrec.servername) > 0 THEN

               LET strg = "CREATE SYNONYM ", 
                           tabrec.tabname clipped, " FOR ", 
                           synrec.dbname clipped, "@",
                   	   get_server(synrec.servername, 
                                      tabrec.tabname) clipped, ":",
                           synrec.ntabname clipped, ";"

            ELSE IF LENGTH(synrec.dbname) > 0 THEN
   
                  LET strg = "CREATE SYNONYM ", 
                             tabrec.tabname clipped, " FOR ", 
                             synrec.dbname clipped, ":",
                             synrec.ntabname clipped, ";"

               ELSE     # in local database
   
                  SELECT tabname
                    INTO synrec.ntabname
                    FROM o_systables
                   WHERE tabid = synrec.btabid

                  LET strg = "CREATE SYNONYM ", 
                             tabrec.tabname clipped, " FOR ", 
                             synrec.ntabname clipped, ";"
               END IF
            END IF

            OUTPUT TO REPORT dump_sql(strg)
            OUTPUT TO REPORT dump_sql("")		# blank line

         WHEN 'V'			# View

            LET strg = ""
            OPEN view_curs USING tabrec.tabid
            FOREACH view_curs INTO tmp_strg, i
               LET strg=strg clipped, tmp_strg
               CALL clip_strg(strg) RETURNING tmp_strg, strg
               CALL psh_strg(tmp_strg,"","")
            END FOREACH
            CALL psh_strg("END","",strg)  # need to reset the counter
         
         #WHEN 'P'			# private synonym or ANSI syonym

         #WHEN 'L'			# SE log

      END CASE
   END FOREACH
   FREE add_curs

##################################
# I am not very happy with this section and would appreciate alternate ideas.
##################################
   LET msg =  "Now producing ALTERs..."
   CALL op_log(msg)
  					# Alter tables
  					# twisted - have to compare
					# how tables look.

					# foreach table in both databases
					# dont need sort
   DECLARE table_list CURSOR FOR
   SELECT o_systables.tabid, 
          o_systables.tabname
     FROM o_systables, "informix".systables
    WHERE "informix".systables.tabname = o_systables.tabname
      AND "informix".systables.tabtype = 'T'
      AND o_systables.tabtype = 'T'
      AND o_systables.tabname MATCHES tb_name

   PREPARE n_c FROM
   'SELECT colname, colno, coltype, collength FROM "informix".syscolumns, "informix".systables WHERE tabname = ? AND "informix".syscolumns.tabid = "informix".systables.tabid ORDER BY colno'

   DECLARE n_cols CURSOR FOR n_c

   FOREACH table_list INTO tabrec.tabid, tabrec.tabname

   LET tmp_strg = "ALTER TABLE ", tabrec.tabname clipped

   FOR i = 1 TO 500
      INITIALIZE chk_cols[i].* TO NULL
   END FOR

# Note: j is not a junk variable in this loop.  j points to last valid
# column info found.  i is junk

					# load old columns into array
      LET j = 1
      OPEN get_col USING tabrec.tabid
      FOREACH get_col INTO chk_cols[j].o_colname, chk_cols[j].o_collength, 
                          chk_cols[j].o_coltype, chk_cols[j].o_colno
         LET j = j + 1
      END FOREACH			# j points 1 past

					# load new columns - find match in array
      OPEN n_cols USING tabrec.tabname
      FOREACH n_cols INTO chk_cols[j].n_colname, chk_cols[j].n_colno, 
                          chk_cols[j].n_coltype, chk_cols[j].n_collength
         FOR i = 1 TO j
            IF chk_cols[i].o_colname = chk_cols[j].n_colname THEN
               LET chk_cols[i].n_colname   = chk_cols[j].n_colname
               LET chk_cols[i].n_colno     = chk_cols[j].n_colno
               LET chk_cols[i].n_coltype   = chk_cols[j].n_coltype
               LET chk_cols[i].n_collength = chk_cols[j].n_collength
               INITIALIZE chk_cols[j].* TO NULL   # clear it
               EXIT FOR
            END IF
         END FOR
         IF i >= j THEN			# didn't find a match, j --> valid row
            LET j = j + 1		# j --> NULL row
            IF j > 500 THEN 
              LET msg="Over 500 column differences for this table, bailing out"
              CALL op_log(msg)
              LET msg="Table: ", tabrec.tabname clipped
              CALL disp_err()
              EXIT FOREACH
	    END IF
         END IF
      END FOREACH

  LET j=j-1				# j now --> last valid row

###################################################################
# We now have a loaded array of matching columns for this table.  Loop
# through this array and check to make sure all columns  are the same
# AND IN THE SAME ORDER.  If not - then we need to fix it.
###################################################################

   LET exp_col = 1			# expected column number
					# loop through array:
   FOR i = 1 TO j
					# if colname null in old then 
					# drop column	

      IF chk_cols[i].o_colname IS NULL THEN

         LET strg = "DROP (", chk_cols[i].n_colname clipped, ")"
         CALL psh_strg(strg, tmp_strg, ",")

         LET exp_col = exp_col + 1	# keep track of expected colno

					# if colname null in new then add column
      ELSE IF chk_cols[i].n_colname IS NULL THEN

 # Have a good look for a column to ADD BEFORE... 		#KJS

             FOR k = i TO j					#KJS
                IF chk_cols[k].n_colname IS NOT NULL THEN	#KJS
                   EXIT FOR					#KJS
                END IF						#KJS
             END FOR						#KJS
 # Was not possible to ADD a new column BEFORE the first column with the #KJS
 # "IF i > 1" condition included      -- KJS			#KJS
 #           IF i > 1 AND LENGTH(chk_cols[k].n_colname) != 0 THEN	 #KJS
             IF LENGTH(chk_cols[k].n_colname) != 0 THEN		#KJS
                 LET strg =
                   col_cnvrt(chk_cols[i].o_coltype, chk_cols[i].o_collength)
                 LET strg = "ADD (",
                     chk_cols[i].o_colname clipped,
                    column 27, strg clipped,
                    " BEFORE ", chk_cols[k].n_colname CLIPPED, ")"	 #KJS

#            LET k = i + 1
#            IF i > 1 AND LENGTH(chk_cols[k].n_colname) != 0 THEN
#               LET strg = 
#	           col_cnvrt(chk_cols[i].o_coltype, chk_cols[i].o_collength)
#               LET strg = "ADD (", 
#                   chk_cols[i].o_colname clipped, 
#                   column 27, strg clipped, 
#                   " BEFORE ", chk_cols[k].n_colname, ")"
            ELSE
               LET strg = "ADD (", 
		   chk_cols[i].o_colname clipped, 
		   column 27, 
	   	   col_cnvrt(chk_cols[i].o_coltype,chk_cols[i].o_collength)
			clipped, ")"
            END IF

            CALL psh_strg(strg, tmp_strg, ",")

         ELSE				# o_colname = n_colname - 
					# check type/length/colno

#
# FIX THIS.  In this case what we should do is generate code to unload the
#            table, drop it, recreate it, reload it.  Problem is that we
#            don't know how far into creating the table we are.  psh_strg
#            may have already flushed the first 50+ columns.
#
            IF chk_cols[i].n_colno != exp_col THEN
					# wrong order !
               LET msg = "Warning:", tabrec.tabname clipped, 
			" - Column in the wrong order - I'm confused."
               CALL op_log(msg)
               LET msg = "Column :", chk_cols[i].o_colname clipped
               CALL op_log(msg)

            END IF

            IF chk_cols[i].o_coltype   != chk_cols[i].n_coltype
            OR chk_cols[i].o_collength != chk_cols[i].n_collength THEN
               LET strg = "MODIFY (", 
                          chk_cols[i].n_colname clipped, 
                          column 27,
                          col_cnvrt(chk_cols[i].o_coltype, 
                                    chk_cols[i].o_collength) clipped, ")"
               CALL psh_strg(strg, tmp_strg, ",")
            END IF

            LET exp_col = exp_col + 1	# expected column number

         END IF
      END IF
   END FOR	    			# columns
   IF sql_idx > 1 THEN
      CALL psh_strg("END", "", ";")
   END IF

   END FOREACH      			# table_list

   FREE get_col
END FUNCTION


###########################################################################
# Change indices
# note this routine pays no attention to the index name, but compares the
# indices based on the columns, and the order they are in.  So if indices
# have different names - it doesn't care.
###########################################################################
FUNCTION chg_idx()

   DEFINE sel_stmt CHAR(450),
	  i  SMALLINT,
	  idxrec RECORD 
	     tabname    CHAR(18),
             idxname   CHAR(18), 
	     tabid     INTEGER, 
	     idxtype   CHAR(1), 
	     clustered CHAR(1)
         END RECORD,
	 p_colname CHAR(18),
	 strg CHAR(306),		# 16*18 = 288 + 18*whitespace = 306
         last_idx, curr_idx RECORD
	    tabname    CHAR(18),
            ver        SMALLINT, 
            idxname    CHAR(18), 
            descr      CHAR(306)
         END RECORD

					# get old indices
					# WHERE TABLES ALREADY EXIST
					# new tables will be handled shortly
					# dropped tables wont show in this list
LET msg =  "Getting old indices..."
CALL op_log(msg)

   IF ONLINE_SW THEN
      LET max_parts = 16
   ELSE
      LET max_parts = 8
   END IF

   LET sel_stmt = 
      'SELECT o_systables.tabname, idxname, o_sysindexes.tabid, idxtype, ',
	      'clustered ',
        'FROM o_sysindexes, o_systables, "informix".systables ',
       'WHERE "informix".systables.tabname = o_systables.tabname ',
         'AND o_systables.tabid = o_sysindexes.tabid ',
         'AND o_systables.tabname MATCHES "', tb_name CLIPPED, '" ',
         'AND idxname NOT MATCHES " *"'

   PREPARE i_c FROM sel_stmt
   DECLARE o_idx_curs CURSOR FOR i_c

					# resolve indices into names
					# and load into temp table

   CREATE TEMP TABLE cmp_idx(tabname char(18), ver smallint, idxname char(18), 
                             descr char(306)) WITH NO LOG

   FOREACH o_idx_curs INTO idxrec.*
      CALL idx_parts(idxrec.idxname, 0) RETURNING strg
      INSERT INTO cmp_idx (tabname, ver, idxname, descr) 
                   VALUES (idxrec.tabname, 1, idxrec.idxname, strg)
   END FOREACH
      
   FREE o_idx_curs

					# get new indices

LET msg =  "Getting new indices..."
CALL op_log(msg)
   IF ONLINE_SW THEN			# current length of sel_stmt = 424 
      LET sel_stmt = 
      'SELECT "informix".systables.tabname, idxname, ',
             '"informix".sysindexes.tabid, idxtype, clustered ',
        'FROM "informix".sysindexes, o_systables, "informix".systables ',
       'WHERE "informix".systables.tabname = o_systables.tabname ',
         'AND "informix".systables.tabid = "informix".sysindexes.tabid ',
         'AND idxname NOT MATCHES " *"'
   ELSE
      LET sel_stmt = 
      'SELECT "informix".systables.tabname, idxname, ',
             '"informix".sysindexes.tabid, idxtype, clustered ',
        'FROM "informix".sysindexes, o_systables, "informix".systables ',
       'WHERE "informix".systables.tabname = o_systables.tabname ',
         'AND "informix".systables.tabid = "informix".sysindexes.tabid ',
         'AND idxname NOT MATCHES " *"'
   END IF

   PREPARE i_c2 FROM sel_stmt
   DECLARE n_idx_curs CURSOR FOR i_c2

					# resolve indices into names
					# and load into temp table

   FOREACH n_idx_curs INTO idxrec.*
      CALL idx_parts(idxrec.idxname,1) RETURNING strg
      INSERT INTO cmp_idx (tabname, ver, idxname, descr) 
                   VALUES (idxrec.tabname, 2, idxrec.idxname, strg)
   END FOREACH

   FREE n_idx_curs

###########################################################################
# Now we've built a table of indices,  common indices should have identical
# descr strings.  Those that don't need to get fixed.  Just a touch of brute
# force...  Pity we can't use a GROUP BY, but we need the idxname still.
###########################################################################

# read each index, put the ver=1 ones into last_idx (they will be first)
# put the others into curr_idx.
# whenever curr_idx.ver=2 then we should have both loaded.  If not then
# last_idx.* is null - index doesn't exist on old
# curr_idx.* is null - we've just read a ver=1 and last_idx is NOT NULL

LET msg =  "Comparing indices..."
CALL op_log(msg)
   DECLARE idx_curs CURSOR FOR
      SELECT tabname, ver, idxname, descr
        FROM cmp_idx
       ORDER BY tabname, descr, ver

   INITIALIZE last_idx.* TO NULL
   FOREACH idx_curs INTO curr_idx.*

      CASE curr_idx.ver
         WHEN 1
            IF LENGTH(last_idx.idxname) > 0 THEN
               CALL create_idx(last_idx.idxname)
               LET last_idx.* = curr_idx.*
            END IF
            LET last_idx.* = curr_idx.*
         WHEN 2
            IF LENGTH(last_idx.descr) = 0 THEN
               LET msg = "DROP INDEX ", curr_idx.idxname clipped, ";"
               OUTPUT TO REPORT dump_sql(msg)
               OUTPUT TO REPORT dump_sql("")
               CALL op_log(msg)
            ELSE IF last_idx.descr != curr_idx.descr THEN
                  LET msg = "DROP INDEX ", curr_idx.idxname clipped, ";"
                  OUTPUT TO REPORT dump_sql(msg)
                  OUTPUT TO REPORT dump_sql("")
                  CALL op_log(msg)
                  CALL create_idx(last_idx.idxname)
                  INITIALIZE last_idx.* TO NULL
               ELSE 		# same index
                  CALL check_idx(last_idx.idxname, curr_idx.idxname)
                  INITIALIZE last_idx.* TO NULL
               END IF
            END IF
      END CASE

   END FOREACH

   FREE idx_curs
      
					# new indices
   DECLARE new_idx CURSOR FOR
   SELECT idxname
     FROM o_systables, o_sysindexes
    WHERE NOT EXISTS
         (SELECT tabname FROM "informix".systables
           WHERE "informix".systables.tabname = o_systables.tabname)
      AND tabtype = 'T'
      AND o_systables.tabid = o_sysindexes.tabid
      AND idxname NOT MATCHES ' *'

   FOREACH new_idx INTO last_idx.idxname
      CALL create_idx(last_idx.idxname)
   END FOREACH
   FREE new_idx
      
END FUNCTION


########################################################################
# create indices
########################################################################
FUNCTION create_idx(p_idxname)
# bummer, don't want to use that descr line since it's too long

   DEFINE p_idxname char(18),
	  idxrec RECORD 
	     tabid     INTEGER, 
	     tabname   CHAR(18),
	     idxtype   CHAR(1), 
	     clustered CHAR(1)
          END RECORD,
	  parts ARRAY [16] OF SMALLINT,
	  i            SMALLINT,
	  p_colname    CHAR(24),
	  desc_sw      SMALLINT,
	  strg, tmp_strg CHAR(500),
	  idx_strg     CHAR(500)

   CALL idx_parts(p_idxname,0) RETURNING idx_strg
   IF ONLINE_SW THEN
      SELECT o_systables.tabid, o_systables.tabname, idxtype, clustered
      INTO idxrec.* 
      FROM o_sysindexes, o_systables
     WHERE idxname = p_idxname
       AND o_sysindexes.tabid = o_systables.tabid
   ELSE
      SELECT o_systables.tabid, o_systables.tabname, idxtype, clustered
      INTO idxrec.*
      FROM o_sysindexes, o_systables
     WHERE idxname = p_idxname
       AND o_sysindexes.tabid = o_systables.tabid
   END IF

					# unique?
   IF idxrec.idxtype = 'U' THEN 
      LET tmp_strg = "CREATE UNIQUE"
   ELSE
      LET tmp_strg = "CREATE"
   END IF

					# clustered?
   IF idxrec.clustered = 'C' THEN
      SELECT idxname
        INTO p_colname			# re-using other var, ignore name
        FROM "informix".sysindexes
       WHERE tabid = idxrec.tabid
         AND clustered = 'C'

# FIX THIS - EXCLUDE CURRENT INDEX?  CAN'T BECAUSE OF NAME?
# Commented out until a better solution is found.  There is no garauntee
# that the index in question is not a) the same one b) still existant.
#
#      IF status != NOTFOUND THEN	# uh-oh, already a clustered index
#         LET strg = "ALTER INDEX ", p_colname clipped, " TO NOT CLUSTER;"
#         OUTPUT TO REPORT dump_sql(strg)
#      END IF

      LET tmp_strg = tmp_strg clipped, " CLUSTER"
   END IF

					# tack on index name
   LET tmp_strg = tmp_strg clipped, " INDEX ", p_idxname clipped,
                  " ON ", idxrec.tabname clipped, " (", idx_strg clipped, ");"

					# add columns
   CALL fold_and_push(tmp_strg, 1)

END FUNCTION


###########################################################################
# Same index fields - are they the same type of index?  If not fix.
###########################################################################
FUNCTION check_idx(o_idx, n_idx)

   DEFINE o_idx, n_idx CHAR(18),
	  o_clust, o_type, n_clust, n_type CHAR(1)

   SELECT clustered, idxtype
     INTO o_clust, o_type
     FROM o_sysindexes
    WHERE idxname = o_idx

   SELECT clustered, idxtype
     INTO n_clust, n_type
     FROM "informix".sysindexes
    WHERE idxname = n_idx

# cluster - there had better not be another.
IF o_clust != n_clust AND n_clust = 'C' THEN 		# bummer
							# WHAT index jack?
							# find it!
   LET msg="{Warning - this command must be run before any other CLUSTER on ",
           "this table}"
   OUTPUT TO REPORT dump_sql(msg)
   CALL op_log(msg)
   LET msg = "ALTER INDEX ", n_idx clipped, " TO NOT CLUSTER;"
   OUTPUT TO REPORT dump_sql(msg)
   CALL op_log(msg)
   OUTPUT TO REPORT dump_sql("")
END IF

# unique/duplicate - they better be the same.
IF o_type != n_type THEN
   LET msg = "DROP INDEX ", n_idx clipped, ";"
   CALL op_log(msg)
   OUTPUT TO REPORT dump_sql(msg)
   OUTPUT TO REPORT dump_sql("")
   CALL create_idx(o_idx)
END IF

END FUNCTION


###########################################################################
# Change users
# Compare contents of sysusers and o_sysusers. 
#    drop old or different.  Add new or different
###########################################################################
FUNCTION chg_user()

DEFINE
   usr_rec RECORD
      username CHAR(8),
      usertype CHAR(1)
   END RECORD,
   sql_strg CHAR(80)

   DECLARE drop_user CURSOR FOR 
   SELECT username, usertype 
     FROM sysusers
    WHERE NOT EXISTS (SELECT username FROM o_sysusers
                      WHERE o_sysusers.username = sysusers.username)
    UNION
   SELECT username, usertype 
     FROM sysusers
    WHERE EXISTS (SELECT username FROM o_sysusers
                      WHERE o_sysusers.username = sysusers.username
                        AND o_sysusers.usertype != sysusers.usertype)

   DECLARE add_user CURSOR FOR 
   SELECT username, usertype 
     FROM o_sysusers
    WHERE NOT EXISTS (SELECT username FROM sysusers
                      WHERE o_sysusers.username = sysusers.username)
    UNION
   SELECT username, usertype 
     FROM o_sysusers
    WHERE EXISTS (SELECT username FROM sysusers
                      WHERE o_sysusers.username = sysusers.username
                        AND o_sysusers.usertype != sysusers.usertype)

   INITIALIZE usr_rec.* TO NULL

   CALL op_log("Generating REVOKE DATABASE PRIVILEGES")
   FOREACH drop_user INTO usr_rec.username, usr_rec.usertype
      CASE usr_rec.usertype
         WHEN "D"
            LET sql_strg= "REVOKE DBA FROM ", usr_rec.username CLIPPED, ";"
         WHEN "C"
            LET sql_strg= "REVOKE CONNECT FROM ", usr_rec.username CLIPPED, ";"
         WHEN "R"
            LET sql_strg= "REVOKE RESOURCE FROM ", usr_rec.username CLIPPED, ";"
      END CASE
      CALL psh_strg("END",sql_strg,";")
   END FOREACH

   INITIALIZE usr_rec.* TO NULL

   CALL op_log("Generating GRANT DATABASE PRIVILEGES")
   FOREACH add_user INTO usr_rec.username, usr_rec.usertype
      CASE usr_rec.usertype
         WHEN "D"
            LET sql_strg = "GRANT DBA TO ", usr_rec.username CLIPPED, ";"
         WHEN "C"
            LET sql_strg = "GRANT CONNECT TO ", usr_rec.username CLIPPED, ";"
         WHEN "R"
            LET sql_strg = "GRANT RESOURCE TO ", usr_rec.username CLIPPED, ";"
      END CASE
      CALL psh_strg("END",sql_strg,"")
   END FOREACH

   FREE drop_user
   FREE add_user

END FUNCTION


###########################################################################
# Change table authority/permissions
# compare tables and drop old or different.  Add new or different
###########################################################################

FUNCTION chg_auth()

DEFINE fn_rec RECORD
          grantor CHAR(8), 
          grantee CHAR(8), 
          tabauth CHAR(8), 
          tabname CHAR(18), 
          ntabauth CHAR(8)
       END RECORD,
   sql_strg CHAR(80)

# Problem, tabid is not guaranteed to be the same between databases (and is
# really not important in that context) - we need tabname

   DECLARE drop_tab CURSOR FOR
   SELECT grantee, tabname
     FROM systabauth, systables
    WHERE systabauth.tabid = systables.tabid
      AND USER = grantor		# can't revoke those you didn't give
      AND NOT EXISTS (SELECT grantor, grantee, tabname
                        FROM o_systabauth, o_systables
                       WHERE o_systabauth.tabid = o_systables.tabid
                         AND o_systabauth.grantor = systabauth.grantor 
                         AND o_systabauth.grantee = systabauth.grantee 
                         AND o_systables.tabname = systables.tabname)
    UNION
   SELECT grantee, tabname
     FROM systabauth, systables
    WHERE systabauth.tabid = systables.tabid
      AND USER = grantor		# can't revoke those you didn't give
      AND EXISTS (SELECT grantor, grantee, tabname
                    FROM o_systabauth, o_systables
                   WHERE o_systabauth.tabid = o_systables.tabid
                     AND o_systabauth.grantor = systabauth.grantor 
                     AND o_systabauth.grantee = systabauth.grantee 
                     AND o_systables.tabname = systables.tabname
                     AND o_systabauth.tabauth != systabauth.tabauth)

   DECLARE add_tab CURSOR FOR
   SELECT grantor, grantee, tabauth, tabname
     FROM o_systabauth, o_systables
    WHERE o_systabauth.tabid = o_systables.tabid
      AND NOT EXISTS (SELECT grantor, grantee, tabname
                        FROM systabauth, systables
                       WHERE systabauth.tabid = systables.tabid
                         AND o_systabauth.grantor = systabauth.grantor 
                         AND o_systabauth.grantee = systabauth.grantee 
                         AND o_systables.tabname = systables.tabname)
   UNION
   SELECT grantor, grantee, tabauth, tabname
     FROM o_systabauth, o_systables
    WHERE o_systabauth.tabid = o_systables.tabid
      AND EXISTS (SELECT grantor, grantee, tabname
                    FROM systabauth, systables
                   WHERE systabauth.tabid = systables.tabid
                     AND o_systabauth.grantor = systabauth.grantor 
                     AND o_systabauth.grantee = systabauth.grantee 
                     AND o_systables.tabname = systables.tabname
                     AND o_systabauth.tabauth != systabauth.tabauth)

   INITIALIZE fn_rec.* TO NULL

   CALL op_log("Generating REVOKE TABLE PRIVILEGES")
   FOREACH drop_tab INTO fn_rec.grantee, fn_rec.tabname
      LET sql_strg = "REVOKE ALL ON ", fn_rec.tabname clipped, " FROM ",
                      fn_rec.grantee clipped
      CALL psh_strg("END",sql_strg,";")
   END FOREACH

   INITIALIZE fn_rec.* TO NULL

   CALL op_log("Generating ADD TABLE PRIVILEGES")
   FOREACH add_tab INTO fn_rec.grantor, fn_rec.grantee, fn_rec.tabauth, 
                        fn_rec.tabname

# oh boy this is going to be fun! 8*2-1 values to check here.

   IF fn_rec.tabauth[1,1] MATCHES "[Ss]" THEN
      CALL priv_grant("SELECT", fn_rec.tabname, fn_rec.grantee, 
                      fn_rec.grantor, fn_rec.tabauth[1,1])
   END IF

   IF fn_rec.tabauth[2,2] MATCHES "[Uu]" THEN
      CALL priv_grant("UPDATE", fn_rec.tabname, fn_rec.grantee, 
                      fn_rec.grantor, fn_rec.tabauth[2,2])
   END IF

   IF fn_rec.tabauth[4,4] MATCHES "[Ii]" THEN
      CALL priv_grant("INSERT", fn_rec.tabname, fn_rec.grantee, 
                      fn_rec.grantor, fn_rec.tabauth[4,4])
   END IF

   IF fn_rec.tabauth[5,5] MATCHES "[Dd]" THEN
      CALL priv_grant("DELETE", fn_rec.tabname, fn_rec.grantee, 
                      fn_rec.grantor, fn_rec.tabauth[5,5])
   END IF

   IF fn_rec.tabauth[6,6] MATCHES "[Xx]" THEN
      CALL priv_grant("INDEX", fn_rec.tabname, fn_rec.grantee, 
                      fn_rec.grantor, fn_rec.tabauth[6,6])
   END IF

   IF fn_rec.tabauth[7,7] MATCHES "[Aa]" THEN
      CALL priv_grant("ALTER", fn_rec.tabname, fn_rec.grantee, 
                      fn_rec.grantor, fn_rec.tabauth[7,7])
   END IF

   IF fn_rec.tabauth[8,8] MATCHES "[Rr]" THEN
      CALL priv_grant("REFERENCES", fn_rec.tabname, fn_rec.grantee, 
                      fn_rec.grantor, fn_rec.tabauth[8,8])
   END IF

   END FOREACH

   FREE drop_tab
   FREE add_tab

END FUNCTION


###########################################################################
# Generate grant statement
###########################################################################
FUNCTION priv_grant(priv, tabname, grantee, grantor, g_opt)

DEFINE
	priv CHAR(10),
	tabname CHAR(18),
	grantee, grantor CHAR(8),
	g_opt CHAR(1),
        sql_strg CHAR(80)

      LET sql_strg = "GRANT ", priv clipped, " ON ", tabname clipped, 
                     " TO ", grantee clipped

			# if it is upper case (ASCII 65-90) then they
			# have grant option.

      IF g_opt < ASCII(91) THEN 
         LET sql_strg=sql_strg clipped, " WITH GRANT OPTION"
      END IF

			# creator is a grantee, but grantor is blank!
      IF LENGTH(grantor) > 0 THEN
         LET sql_strg=sql_strg clipped, " AS ", grantor clipped, ";"
      ELSE
         LET sql_strg=sql_strg clipped, ";"
      END IF

      CALL psh_strg("END",sql_strg,"")

END FUNCTION


#########################################################################
#
# This is not going to be pretty.  A constraint is defined across multiple
# tables identified by multiple keys which are integral to the current
# database and do not necessarily match the other database (constrid, tabid,
# idxname, colno) the only way to ensure that we get the right ones and
# only the right ones is to work out the full constraint definition and
# then compare it to the other databases definitions - sort of like the
# way indexes were done.  This time though, I'm going to express it in 
# sql so that I don't need to make a second pass to generate it.
# jp 10/30/94
#
#########################################################################
FUNCTION chg_constr()

DEFINE constr_rec RECORD
	constr_id   INTEGER,
	constr_name CHAR(18),
	owner	    CHAR(8),
	tabid	    INTEGER,
	constrtype  CHAR(1),
	idxname	    CHAR(18),
	tabname	    CHAR(18),
	primary	    INTEGER
       END RECORD,
	sel_stmt    CHAR(2000),
	stmt1, sql_stmt    CHAR(500),
	s1	    CHAR(100),
	i, oldnew SMALLINT

# get some work space.
   CREATE TEMP TABLE tmp_constr (constrname CHAR(18), tabname CHAR(18),
		    old_new SMALLINT, constr_def CHAR(500)) WITH NO LOG

# 1 - identify each old constraint.
# handle checks seperately

   IF ONLINE_SW THEN
      LET sel_stmt = "SELECT o_sysconstraints.constrid, constrname, ",
           "o_sysconstraints.owner, o_sysconstraints.tabid, constrtype, ",
           "o_sysconstraints.idxname, tabname, prim ",
      "FROM o_sysconstraints, o_systables, OUTER o_sysreferences ",
     "WHERE o_sysconstraints.tabid = o_systables.tabid ",
       "AND o_sysconstraints.constrid = o_sysreferences.constrid ",
       "AND constrtype != 'C'"
   ELSE      
      LET sel_stmt = "SELECT o_sysconstraints.constrid, constrname, ",
           "o_sysconstraints.owner, o_sysconstraints.tabid, constrtype, ",
           "o_sysconstraints.idxname, tabname, prim ",
      "FROM o_sysconstraints, o_systables, o_sysindexes,OUTER o_sysreferences ",
     "WHERE o_sysconstraints.tabid = o_systables.tabid ",
       "AND o_sysconstraints.constrid = o_sysreferences.constrid ",
       "AND constrtype != 'C'"
   END IF

   PREPARE s1 FROM sel_stmt
   DECLARE r_cons_o CURSOR FOR s1

   # While we're at it, lets make the cursor for the new ones too.

   CALL translate("o_","",sel_stmt) RETURNING sel_stmt
   CALL translate("prim","primary",sel_stmt) RETURNING sel_stmt

   PREPARE s2 FROM sel_stmt
   DECLARE r_cons_n CURSOR FOR s2

   CALL op_log("Parsing old constraints")
   FOREACH r_cons_o INTO constr_rec.*
      CALL pars_cons(constr_rec.*, 0)
   END FOREACH

# Do it again for the new ones
   CALL op_log("Parsing new constraints")
   FOREACH r_cons_n INTO constr_rec.*
      CALL pars_cons(constr_rec.*, 1)
   END FOREACH

# select unique ones (NOT a SELECT UNIQUE situation mind you)
   CALL op_log("Comparing constraints")
   DECLARE cm_curs CURSOR FOR
   SELECT constr_def 
     FROM tmp_constr
    GROUP BY constr_def
   HAVING COUNT(*) = 1

# we couldn't grab everything and group by all of them because the old_new
# switch is going to be different

   FOREACH cm_curs INTO sql_stmt 

      SELECT constrname, tabname, old_new 
        INTO constr_rec.constr_name, constr_rec.tabname, oldnew
        FROM tmp_constr
       WHERE constr_def = sql_stmt

# drop or add according to oldnew switch
      IF oldnew = 0 THEN
         CALL fold_and_push(sql_stmt, 1)
      ELSE
         LET msg = "ALTER TABLE ", constr_rec.tabname clipped, 
		   " DROP CONSTRAINT ", constr_rec.constr_name clipped, ";"
         OUTPUT TO REPORT dump_sql(msg)
         OUTPUT TO REPORT dump_sql("")
      END IF
   END FOREACH

# Handle syschecks, since they may be VERY long they won't work with the 
# comparison style we just used, match them up against one another into a 
# temp table, things not matched into the table need fixing.

				# find the id of all macthing ones
   CALL op_log("Parsing check constraints")
 SELECT o_syschecks.constrid old_id, syschecks.constrid new_id
   FROM o_syschecks, syschecks 
  WHERE o_syschecks.type = 'B'
    AND syschecks.type = 'B'
    AND o_syschecks.seqno=syschecks.seqno
    AND o_syschecks.checktext=syschecks.checktext
   INTO TEMP duppedchecks WITH NO LOG;

				# these ones aren't there
DECLARE new_checks CURSOR FOR 
 SELECT unique constrid 
   FROM syschecks
  WHERE NOT EXISTS (SELECT new_id FROM duppedchecks)

				# nor are these ones
DECLARE old_checks CURSOR FOR 
 SELECT unique constrid 
   FROM o_syschecks
  WHERE NOT EXISTS (SELECT old_id FROM duppedchecks)

				# and we'll need the text for the old ones.
LET s1 = "SELECT checktext, seqno FROM o_syschecks WHERE constrid = ? AND ",
	 "type = 'T' ORDER BY seqno"

PREPARE ock FROM s1
DECLARE oldchecks CURSOR FOR ock
  
   CALL op_log("Generating drops for old check constraints")
			# Lose the bad ones
   FOREACH new_checks INTO constr_rec.constr_id

      SELECT constrname, tabname
        INTO constr_rec.constr_name, constr_rec.tabname
        FROM sysconstraints, systables
       WHERE sysconstraints.tabid = systables.tabid
         AND constrid = constr_rec.constr_id

      LET sql_stmt = 'ALTER TABLE ', constr_rec.tabname clipped, 
                     ' DROP CONSTRAINT ', constr_rec.constr_name clipped, ';'

      OUTPUT TO REPORT dump_sql(sql_stmt)
   END FOREACH

			# add the new ones
   CALL op_log("Generating adds for new check constraints")
   FOREACH old_checks INTO constr_rec.constr_id
      SELECT tabname
        INTO constr_rec.tabname
        FROM o_sysconstraints, o_systables
       WHERE o_sysconstraints.tabid = o_systables.tabid
         AND constrid = constr_rec.constr_id

      LET sql_stmt = 'ALTER TABLE ', constr_rec.tabname clipped, 
                     ' ADD CONSTRAINT CHECK'

      OPEN oldchecks USING constr_rec.constr_id
			# probably not broken on space boundaries....

# FIX THIS.  This duplicates the functionaility of fold_and_push

      FOREACH oldchecks INTO stmt1
         LET sql_stmt = sql_stmt clipped, stmt1
         IF LENGTH(sql_stmt) > 450 THEN
            FOR i = LENGTH(sql_stmt) TO 1 STEP -1
               IF sql_stmt[i,i] = ' ' THEN	# break it here
                  LET stmt1 = sql_stmt[i,500]	# put trailer into stmt1
                  LET sql_stmt = sql_stmt[1,i]	# get first part
                  CALL fold_and_push(sql_stmt, 1)	# fold and push
                  LET sql_stmt = stmt1		# reset 
                  EXIT FOR			# outta here
               END IF
            END FOR
         END IF
      END FOREACH

      LET sql_stmt=sql_stmt clipped, ";"
      CALL fold_and_push(sql_stmt, 1)

   END FOREACH

END FUNCTION

###########################################################################
# A common routine to generate the text of a constraint (except for checks)
###########################################################################
FUNCTION pars_cons(constr_rec, o_n)

DEFINE constr_rec RECORD
	constr_id   INTEGER,
	constr_name CHAR(18),
	owner	    CHAR(8),
	tabid	    INTEGER,
	constrtype  CHAR(1),
	idxname	    CHAR(18),
	tabname	    CHAR(18),
	primary	    INTEGER
       END RECORD,
	o_n SMALLINT,
	sql_stmt    CHAR(500),
	i, j        SMALLINT,
	p_colname   CHAR(18),
	p_tabname   CHAR(18),
	col_strng   CHAR(330)   #	16*20+10_just_in_case

				# base - all have this
   LET sql_stmt = 'ALTER TABLE ', constr_rec.tabname clipped,
                     ' ADD CONSTRAINT'
      
				# constraint type
   CASE constr_rec.constrtype
      WHEN 'P' LET sql_stmt = sql_stmt clipped, ' PRIMARY KEY'
      WHEN 'U' LET sql_stmt = sql_stmt clipped, ' UNIQUE'
      WHEN 'R' LET sql_stmt = sql_stmt clipped, ' FOREIGN KEY'
   END CASE

				# constraint columns
   CALL idx_parts(constr_rec.idxname,o_n) RETURNING col_strng

				# add parens
   IF i > 2 THEN
      LET col_strng = "(", col_strng clipped, ")"
   END IF

				# add the string to the SQL stmt
   LET sql_stmt = sql_stmt clipped, col_strng clipped
      
				# if an 'R' then add on 'REFERENCES' clause
   IF constr_rec.constrtype = 'R' THEN

      LET sql_stmt = sql_stmt clipped, ' REFERENCES'

      IF o_n = 0 THEN			# OLD
         SELECT idxname
           INTO p_colname
          FROM o_sysconstraints, o_sysreferences
         WHERE o_sysconstraints.constrid = prim
           AND o_sysreferences.constrid = constr_rec.constr_id

         SELECT tabname
           INTO p_tabname
           FROM o_sysreferences, o_systables
          WHERE o_sysreferences.ptabid = o_systables.tabid
            AND o_sysreferences.constrid = constr_rec.constr_id
      ELSE				# NEW
         SELECT idxname
           INTO p_colname
          FROM sysconstraints, sysreferences
         WHERE sysconstraints.constrid = primary 
           AND sysreferences.constrid = constr_rec.constr_id

         SELECT tabname
           INTO p_tabname
           FROM sysreferences, systables
          WHERE sysreferences.ptabid = systables.tabid
            AND sysreferences.constrid = constr_rec.constr_id
      END IF

					# get column names
      CALL idx_parts(p_colname,o_n) RETURNING col_strng
      LET sql_stmt = sql_stmt clipped, " ", p_tabname clipped, " (", 
				       col_strng clipped, ")"
   END IF

					# add the ";"
   LET sql_stmt = sql_stmt CLIPPED, ";"

					# drop into table
   INSERT INTO tmp_constr VALUES(constr_rec.constr_name, constr_rec.tabname,
				  o_n, sql_stmt)

END FUNCTION



###########################################################################
# Change Triggers	(Kerry Sainsbury)
# Compare contents of systriggers and o_systriggers,
#     and contents of systrigbody and o_systrigbody
#    drop old or different.  Add new or different
###########################################################################
FUNCTION chg_trigs()
DEFINE   l_trigname    CHAR(18),
         l_owner       CHAR(8),
         sql_strg      CHAR(80),
         l_o    RECORD
            trigid    INT,
            trigname  CHAR(18),
            owner     CHAR(8),
            tabid     INT,
            event     CHAR,
            old       CHAR(18),
            new       CHAR(18),
            tabname   CHAR(18)
         END RECORD,
         l_a    RECORD
            trigid    INT,
            trigname  CHAR(18),
            owner     CHAR(8),
            tabid     INT,
            event     CHAR,
            old       CHAR(18),
            new       CHAR(18),
            tabname   CHAR(18)
         END RECORD,
         l_os   RECORD
            datakey   CHAR,
            seqno     INT,
            data      CHAR(256)
         END RECORD,
         l_as   RECORD
            datakey   CHAR,
            seqno     INT,
            data      CHAR(256)
         END RECORD,
         l_acnt       INTEGER,
         l_ocnt       INTEGER

-- Drop all triggers that are not in o_systriggers...

   DECLARE drop_trigs CURSOR FOR
    SELECT systriggers.trigname, systriggers.owner, systables.tabname
      FROM systriggers, systables
     WHERE systriggers.tabid = systables.tabid
       AND systriggers.tabid > 99 
       AND tabname MATCHES tb_name
       AND NOT EXISTS (SELECT trigname FROM o_systriggers
                        WHERE o_systriggers.trigname = systriggers.trigname
                          AND o_systriggers.owner = systriggers.owner)

   CALL op_log("Generating DROP TRIGGERs")
   FOREACH drop_trigs INTO l_trigname, l_owner
      LET sql_strg= "drop trigger '", l_owner CLIPPED, "'.", 
                                      l_trigname CLIPPED,";"
      CALL psh_strg("END",sql_strg,";")
   END FOREACH
   FREE drop_trigs

-- Now build a list of all triggers that're in both databases...
-- with the same trigger name and owner

   DECLARE more_drop_trigs CURSOR FOR
    SELECT o.trigid, o.trigname, o.owner, o.tabid,
           o.event, o.old, o.new, p.tabname,
           a.trigid, a.trigname, a.owner, a.tabid,
           a.event, a.old, a.new, b.tabname
      FROM o_systriggers o, systriggers a, o_systables p, systables b
     WHERE o.trigname = a.trigname
       AND o.owner = a.owner
       AND o.tabid = p.tabid
       AND a.tabid = b.tabid

   FOREACH more_drop_trigs INTO l_o.*, l_a.*

-- Do a cheap n nasty check to see if triggers are different, based on the
-- number of lines in each...

      LET l_acnt = 0
      LET l_ocnt = 0
      SELECT COUNT(*) INTO l_acnt FROM systrigbody
       WHERE trigid = l_a.trigid
         AND datakey IN ("D", "A")

      SELECT COUNT(*) INTO l_ocnt FROM o_systrigbody
       WHERE trigid = l_o.trigid

      IF l_acnt != l_ocnt THEN    # Different number of lines in trigger
         LET sql_strg= "drop trigger '", l_a.owner CLIPPED, "'.", 
                                         l_a.trigname CLIPPED,";"
         CALL psh_strg("END",sql_strg,";")
      ELSE

-- Check for a subtle change (line count same, but content different)

         DECLARE subtle_curs CURSOR FOR
          SELECT o.datakey, o.seqno, o.data,
                 a.datakey, a.seqno, a.data
            FROM o_systrigbody o, systrigbody a
           WHERE a.trigid = l_a.trigid
             AND o.trigid = l_o.trigid
             AND o.seqno = a.seqno
             AND o.datakey = a.datakey
           ORDER BY o.datakey, o.seqno

          FOREACH subtle_curs INTO l_os.*, l_as.*

-- If text of trigger differs...

             IF l_os.data    != l_as.data THEN

-- then throw the old trigger away...

                LET sql_strg= "drop trigger '", l_a.owner CLIPPED, "'.", 
                                                l_a.trigname CLIPPED,";"
                CALL psh_strg("END",sql_strg,";")

-- ... and recreate it in o_'s image...
                CALL create_trigger(l_o.trigid, l_o.trigname)
                EXIT FOREACH
             END IF
          END FOREACH
          FREE subtle_curs
      END IF
   END FOREACH
   FREE more_drop_trigs

-- Finally: Any triggers in o_systriggers that aren't in systriggers
--          need to be created...

   CALL op_log("Creating missing TRIGGERs")

   DECLARE fin_curs CURSOR FOR
    SELECT trigid, trigname
      FROM o_systriggers
     WHERE NOT EXISTS (SELECT trigname, owner FROM systriggers
                        WHERE systriggers.trigname = o_systriggers.trigname
                          AND systriggers.owner = o_systriggers.owner)
   FOREACH fin_curs INTO l_o.trigid, l_o.trigname
      CALL create_trigger(l_o.trigid, l_o.trigname)
   END FOREACH
   FREE fin_curs

END FUNCTION


FUNCTION create_trigger(l_trigid, l_trigname)
DEFINE l_trigid     INT,
       l_trigname   CHAR(18)

DEFINE sql_strg     CHAR(1024),
       l_data       CHAR(4096),
       l_indata     CHAR(256),
       i, j         SMALLINT,
       l_lth        SMALLINT,
       l_ascii10    CHAR(1)

   LET l_ascii10 = ASCII(10)  # Informix Bug Alert!!

   DECLARE build_trig_curs CURSOR FOR
    SELECT data, datakey, seqno FROM o_systrigbody
     WHERE trigid = l_trigid
     ORDER BY datakey DESC, seqno

   OUTPUT TO REPORT dump_SQL("")
   LET sql_strg = ""
   FOREACH build_trig_curs INTO l_indata
      LET l_data[l_lth+1,4096]=l_indata
      LET l_lth = LENGTH(l_data)
      WHILE l_lth > 0
         FOR i = 1 TO l_lth
            IF l_data[i] = l_ascii10 THEN   #<<<----- What about MSDOS?
               IF i > 1 THEN
                  LET sql_strg = l_data[1,i-1]
                  CALL fold_and_push(sql_strg,0)
               END IF
               IF i != l_lth THEN
                  LET l_data = l_data[i+1, l_lth]
               END IF
               LET l_lth = l_lth - i
               EXIT FOR
            END IF
         END FOR
         IF i > l_lth THEN  # Can't find an end of line
            EXIT WHILE      # so get another chunk of trigger definition
         END IF
      END WHILE
   END FOREACH
   LET sql_strg = l_data
   CALL fold_and_push(sql_strg,1)

   FREE build_trig_curs
END FUNCTION 


###########################################################################
# Change Stored Procedures	(Kerry Sainsbury)
# Compare contents of sysprocbody and o_sysprocbody
#    drop old or different.  Add new or different
###########################################################################
FUNCTION chg_spl()
DEFINE   sql_strg      CHAR(80),
         l_ocnt        INTEGER,
         l_odata       CHAR(256),
         l_oprocname   CHAR(18),
         l_oprocid     INTEGER,

         l_acnt        INTEGER,
         l_adata       CHAR(256),
         l_aprocname   CHAR(18),
         l_aprocid     INTEGER,
         l_aowner      CHAR(8)

-- Drop all spls that are not in o_systriggers...

   DECLARE drop_spls CURSOR FOR
    SELECT procname, owner
      FROM sysprocedures
     WHERE NOT EXISTS (SELECT procname FROM o_sysprocedures
                        WHERE o_sysprocedures.procname=sysprocedures.procname
                          AND o_sysprocedures.owner = sysprocedures.owner)

   CALL op_log("Generating DROP PROCEDUREs")
   FOREACH drop_spls INTO l_aprocname, l_aowner
      LET sql_strg= "DROP PROCEDURE '", l_aowner CLIPPED, "'.", 
                                        l_aprocname CLIPPED,";"
      CALL psh_strg("END",sql_strg,";")
   END FOREACH
   FREE drop_spls

-- Now build a list of all procedures that're in both databases...
-- with the same procedure name and owner

   DECLARE more_drop_spls CURSOR FOR
    SELECT o.procid, o.procname,
           a.procid, a.procname, a.owner
      FROM o_sysprocedures o, sysprocedures a
     WHERE o.procname = a.procname
       AND o.owner = a.owner

   FOREACH more_drop_spls INTO l_oprocid, l_oprocname,
                               l_aprocid, l_aprocname, l_aowner

-- Do a crass check to see if the stored procedures are different
-- (based on the number of lines in each procedure)...

      LET l_ocnt = 0
      LET l_acnt = 0
      SELECT COUNT(*) 
        INTO l_ocnt
        FROM o_sysprocbody
       WHERE procid = l_oprocid

      SELECT COUNT(*) 
        INTO l_acnt
        FROM sysprocbody
       WHERE procid = l_aprocid
         AND datakey = "T"

      IF l_ocnt != l_acnt THEN
         LET sql_strg= "DROP PROCEDURE '", l_aowner CLIPPED, "'.", 
                                           l_aprocname CLIPPED,";"
         CALL psh_strg("END",sql_strg,";")
         CALL create_procedure(l_oprocid, l_oprocname)
      ELSE

-- Now check for a subtle change (line count stays the same, but content
-- differs)

         DECLARE subtle_splcurs CURSOR FOR
          SELECT o.data,
                 a.data, o.seqno
            FROM o_sysprocbody o, sysprocbody a
           WHERE a.procid = l_aprocid
             AND o.procid = l_oprocid
             AND a.seqno = o.seqno
             AND a.datakey = "T"
           ORDER BY o.seqno

          FOREACH subtle_splcurs INTO l_odata, l_adata
             IF l_odata != l_adata THEN
                LET sql_strg= "DROP PROCEDURE '", l_aowner CLIPPED, "'.", 
                                                  l_aprocname CLIPPED,";"
                CALL psh_strg("END",sql_strg,";")
                CALL create_procedure(l_oprocid, l_oprocname)
                EXIT FOREACH
             END IF
          END FOREACH
          FREE subtle_splcurs
      END IF
   END FOREACH
   FREE more_drop_spls

-- Finally: Any procedures in o_sysprocedures that aren't in sysprocedures
--          need to be created...

   CALL op_log("Creating missing PROCEDUREs")

   DECLARE sfin_curs CURSOR FOR
    SELECT procid, procname
      FROM o_sysprocedures
     WHERE NOT EXISTS (SELECT procname, owner FROM sysprocedures
                        WHERE sysprocedures.procname = o_sysprocedures.procname
                          AND sysprocedures.owner = o_sysprocedures.owner)
   FOREACH sfin_curs INTO l_oprocid, l_oprocname
      CALL create_procedure(l_oprocid, l_oprocname)
   END FOREACH
   FREE sfin_curs

END FUNCTION


FUNCTION create_procedure(l_procid, l_procname)
DEFINE l_procid     INT,
       l_procname   CHAR(18)

DEFINE sql_strg     CHAR(1024),
       l_data       CHAR(4096),
       l_indata     CHAR(256),
       i, j         INTEGER,
       l_lth        INTEGER,
       l_ascii10    CHAR(1),
       junk         SMALLINT

   LET l_ascii10 = ASCII(10)  # Informix Bug Alert!!

   DECLARE build_spl_curs CURSOR FOR
    SELECT data, seqno FROM o_sysprocbody
     WHERE procid = l_procid
     ORDER BY seqno

   OUTPUT TO REPORT dump_SQL("")
   LET sql_strg = ""

# stick line together and flush to report all in one fell swoop
# If we try to pass a CR to fold_and_push we get a dropped core.

   FOREACH build_spl_curs INTO l_indata, junk
      LET l_data[l_lth+1,4096]=l_indata
      LET l_lth = LENGTH(l_data)
      FOR i = 1 TO l_lth
         IF l_data[i,i] = l_ascii10 THEN     	#<<<----- What about MSDOS?
            IF i > 1 THEN                       # has data
               LET sql_strg = l_data[1,i-1]	# = line - CR
               CALL fold_and_push(sql_strg,0)   # flush the line
            ELSE 			     	# skip over SINGLE CR
               LET l_data = l_data[i+1,l_lth]	# lose the CR
               LET i = 0		     	# start FOR over
               LET l_lth = LENGTH(l_data)    	# reset length

# All I want for christmas is to duplicate that CR we dropped.
# But every time I try I drop core.
#               OUTPUT TO REPORT dump_SQL("")

               CONTINUE FOR
            END IF				# flushing the line
            IF i != l_lth THEN
               LET l_data = l_data[i+1,l_lth]
               LET i = 0		     	# start FOR over
               LET l_lth = LENGTH(l_data)    	# reset length
            END IF 				# resetting after CR
         END IF 				# found a CR
      END FOR					# scanning the string


# Kerry code. drops core on my box.
#      LET l_data[l_lth+1,4096]=l_indata
#      LET l_lth = LENGTH(l_data)
#      WHILE l_lth > 0
#         FOR i = 1 TO l_lth
#            IF l_data[i] = l_ascii10 THEN   #<<<----- What about MSDOS?
#               IF i > 1 THEN
#                  LET sql_strg = l_data[1,i-1]
#                  CALL fold_and_push(sql_strg,0)
#               END IF
#               IF i != l_lth THEN
#                  LET l_data = l_data[i+1, l_lth]
#               END IF
#               LET l_lth = l_lth - i
#               EXIT FOR
#            END IF
#         END FOR
#         IF i > l_lth THEN  # Can't find an end of line
#            EXIT WHILE      # so get another chunk of procedure definition
#         END IF
#      END WHILE

   END FOREACH
   LET sql_strg = l_data
   CALL fold_and_push(sql_strg,1)

   FREE build_spl_curs
END FUNCTION 
         
######################### Generic functions ##############################

###########################################################################
# Convert coltype/length into an SQL descriptor string
###########################################################################
FUNCTION col_cnvrt(coltype, collength)

   DEFINE coltype, collength, NONULL SMALLINT,
          SQL_strg CHAR(40),
	  tmp_strg CHAR(4)

   LET coltype = coltype + 1		# datatype[] is offset by one
   LET NONULL  = coltype/256		# if > 256 then is NO NULLS
   LET coltype = coltype MOD 256	# lose the NO NULLS determinator

   LET SQL_strg = datatype[coltype] 

   CASE coltype

      WHEN 1				# char
         LET tmp_strg = collength using "<<<<"
         LET SQL_strg = SQL_strg clipped, " (", tmp_strg clipped, ")"

# SQL syntax supports float(n) - Informix ignores this
#      WHEN 4    			# float
#         LET SQL_strg = SQL_strg clipped, " (", ")"

      WHEN 6    			# decimal
         LET SQL_strg = SQL_strg clipped, " (", fix_nm(collength,0) clipped, ")"

# Syntax supports serial(starting_no) - starting_no is unavaliable
#      WHEN 7    			# serial
#         LET SQL_strg = SQL_strg clipped, " (", ")"

      WHEN 9    			# money
         LET SQL_strg = SQL_strg clipped, " (", fix_nm(collength,0) clipped, ")"

      WHEN 11 				# datetime
         LET SQL_strg = SQL_strg clipped, " ", fix_dt(collength) clipped 

      WHEN 14 				# varchar
         LET SQL_strg = SQL_strg clipped, " (", fix_nm(collength,1) clipped, ")"

      WHEN 15 				# interval
         LET SQL_strg = SQL_strg clipped, " ", fix_dt(collength) clipped

   END CASE

   IF NONULL THEN
      LET SQL_strg = SQL_strg clipped, " NOT NULL"
   END IF
      
   RETURN SQL_strg

END FUNCTION

###########################################################################
# Turn collength into two numbers - return as string
###########################################################################
FUNCTION fix_nm(num,tp)

DEFINE num integer,
       tp smallint,
       strg CHAR(8),
       i, j   SMALLINT,
       strg1, strg2 char(3)

   LET i = num / 256
   LET j = num MOD 256
   LET strg1 = i using "<<&"
   LET strg2 = j using "<<&"
   IF tp = 0 THEN
      IF j > i THEN
         LET strg = strg1 clipped
      ELSE
         LET strg = strg1 clipped, ", ", strg2 clipped
      END IF
   ELSE		# varchar is just the opposite
      IF i = 0 THEN
         LET strg = strg2 clipped
      ELSE
         LET strg = strg2 clipped, ", ", strg1 clipped
      END IF
   END IF

   RETURN strg

END FUNCTION

###########################################################################
# Turn collength into meaningful date info - return as string
###########################################################################
FUNCTION fix_dt(num)
DEFINE num integer,
       i, j, k, len   SMALLINT,
       strg CHAR(30)

   LET i   = (num mod 16) + 1		# offset again
   LET j   = ((num mod 256) / 16) + 1	# offset again
   LET k   = num / 256			# length of value

# If this is an interval then life gets interesting, 'k' is the length of 
# the entire string.  So a YEAR TO DAY is YYYYMMDD or 8.  A DAY(3) TO 
# MINUTE is DDDHHMM or 7.  We don't know how long the first one is, but 
# we can work it out by computing the 'should be length' of the string
# and then adding/subtracting the result from the 'should be length' of 
# the major element.
#
# Keep in mind --->    YYYYMMDDHHMMSSFFFFF
#     vs.         j =    1  2 3 4 5 678901
#
# I was just working an algorithm to do this, 4 notepads, 90 minutes, and 50
# lines into it I realized that I was creating something impossible to test
# or maintain.  Therefore I am opting for something a lot simpler.
#
# In the globals I have created an ARRAY of RECORD with start and end points
# for the major and minor pieces.  By subtracting the START point of the 
# major element from the END point of the minor element I get the 'should be
# length'
#

   LET len = intvray[i].end_point - intvray[j].start_point

# len should match k.	e.g.:
#				DAY(5) TO MINUTE  ==> k = 9, len = 6
#				YEAR(6) TO HOUR   ==> k = 12, len = 14

   LET len = k - len 		        # add len to the major

   IF len = 0 OR j > 11 THEN		# is the default
					# 12 on have the precision alrdy coded

      LET strg = datetype[j] clipped, " TO ", datetype[i] clipped

   ELSE				# isn't the default

				# uh-oh, how long IS the default major?
      LET k = intvray[j].end_point - intvray[j].start_point 

				# add in the extra
      LET k = k + len

      LET strg = datetype[j] clipped, "(", k using "<<", ")", " TO ", 
                 datetype[i] clipped
   
   END IF

   RETURN strg

END FUNCTION

###########################################################################
# process user args
###########################################################################
FUNCTION proc_arg()

DEFINE	errusage SMALLINT,
	opt_ind   SMALLINT,
	curr_opt  CHAR(20)

   LET errusage     = 0
   LET ONLINE_SW    = 1
   LET db_old       = ""
   LET db_new       = ""
   LET SEG1         = 1
   LET SEG2         = 1
   LET ofile_name   = "/tmp/mod_db.sql"
   LET DEBUG        = 0
   LET AUTH_SW      = 0			# check table permissions
   LET USER_SW      = 1			# Check users
   LET tb_name      = "*"
   LET CONST_SW     = 1			# check constraints
   LET SPL_SW       = 1			# Check SPLs
   LET TRIG_SW      = 0
   LET QUIET_SW     = 0
   LET serverfle    = "dbd.servers"
   LET logfile      = "dbdiff2.log"
   LET LOG_SW       = 0

  LET opt_ind = 0
  WHILE (opt_ind <= num_args())

     LET opt_ind = opt_ind + 1
     LET curr_opt = UPSHIFT(arg_val(opt_ind))

     CASE curr_opt

        WHEN "-DB"
           LET opt_ind = opt_ind + 1
           LET curr_opt = UPSHIFT(arg_val(opt_ind))
           IF curr_opt = "SE" THEN
              LET ONLINE_SW = 0
              LET msg = "SE mode selected"
           ELSE
              LET msg = "Online mode selected"
           END IF
           CALL op_log(msg)

        WHEN "-OD"
           LET opt_ind = opt_ind + 1
           LET db_old = arg_val(opt_ind)
           LET msg = "Old database: ", db_old
           CALL op_log(msg)

        WHEN "-ND"
           LET opt_ind = opt_ind + 1
           LET db_new = arg_val(opt_ind)
           LET msg = "New database: ", db_new
           CALL op_log(msg)

        WHEN "-C"
           LET CONST_SW = 0 
           LET msg = "Constraints off. "
           CALL op_log(msg)

        WHEN "-O"
           LET opt_ind = opt_ind + 1
           LET ofile_name = arg_val(opt_ind)
	   LET msg = "Output file: ", ofile_name clipped
           CALL op_log(msg)

        WHEN "-S1" 
           LET SEG2 = 0
	   LET msg = "Segment two turned off"
           CALL op_log(msg)

        WHEN "-S2" 
           LET SEG1 = 0
	   LET msg = "Segment one turned off"
           CALL op_log(msg)

        WHEN "-DBG" 
           LET DEBUG = 1
	   LET msg = "Debug mode turned on"
           CALL op_log(msg)

        WHEN "-A" 
           LET AUTH_SW = 1
	   LET msg = "Authority mode turned on"
           CALL op_log(msg)

        WHEN "-U" 
           LET USER_SW = 0
	   LET msg = "User mode turned off"
           CALL op_log(msg)

        WHEN "-SPL" 
           LET SPL_SW = 0
	   LET msg = "Stored Procedures mode turned off"
           CALL op_log(msg)

        WHEN "-TRG" 
           LET TRIG_SW = 1
	   LET msg = "Triggers mode turned on"
           CALL op_log(msg)

        WHEN "-Q" 
           LET QUIET_SW = 1
	   LET msg = "Quiet mode turned on"
           CALL op_log(msg)

        WHEN "-SVR" 
           LET opt_ind = opt_ind + 1
           LET serverfle = arg_val(opt_ind)
	   LET msg = "Server file name : ", serverfle clipped
           CALL op_log(msg)

        WHEN "-LG"
           LET LOG_SW = 1
	   LET msg = "Logging turned on : "
           CALL op_log(msg)

        WHEN "-LGF" 
           LET opt_ind = opt_ind + 1
           LET logfile = arg_val(opt_ind)
           LET LOG_SW = 1
	   START REPORT logg TO logfile
	   LET msg = "Log file name : ", logfile clipped
           CALL op_log(msg)

        WHEN "-T" 
           LET opt_ind = opt_ind + 1
           LET tb_name = arg_val(opt_ind)
	   LET msg = "Table name : ", tb_name clipped
           CALL op_log(msg)

        WHEN "-ALL" 
           LET CONST_SW = 1 
           LET DEBUG = 1
           LET AUTH_SW = 1
           LET USER_SW = 1
           LET SPL_SW = 1
           LET TRIG_SW = 1

	OTHERWISE
           IF LENGTH(curr_opt) > 0 THEN
              LET errusage=1
              EXIT WHILE
           END IF

     END CASE

  END WHILE


					# check for probs
   IF SEG1 THEN
      CALL db_check(db_old) RETURNING errusage
   END IF

   IF SEG2 THEN
      CALL db_check(db_new) RETURNING errusage
   END IF

   IF LOG_SW = 1 THEN
      START REPORT logg TO logfile
   END IF

  IF NOT errusage THEN
     RETURN
  END IF

   DISPLAY 'usage:'
   DISPLAY 'dbutil [-db SE|OL]   engine type - SE or Online (def: OL)'
   DISPLAY '       [-od]         old database name '
   DISPLAY '       [-nd]         new database name '
   DISPLAY '       [-s1]         Only do segment 1'
   DISPLAY '       [-s2]         Only do segment 2'
   DISPLAY '       [-dbg]        debug mode'
   DISPLAY '       [-o]	         output file name  (def: /tmp/mod_db.sql)'
   DISPLAY '       [-a]          Authority tables (permissions)'
   DISPLAY '       [-c]          Constraints'
   DISPLAY '       [-u]          Users'
   DISPLAY '       [-spl]        Stored Procedures'
   DISPLAY '       [-trg]        Triggers'
   DISPLAY '       [-t]	         table name specification '
   DISPLAY '       [-q]	         quiet mode - no interaction '
   DISPLAY '       [-svr]	 server file name (dbd.servers) '
   DISPLAY '       [-lg]	 turn on logging '
   DISPLAY '       [-lgf]	 Log file name (dbdiff2.log) '
   EXIT PROGRAM 0

END FUNCTION


#######################################################################
# ensure we can open database
#######################################################################
FUNCTION db_check(dbname)

DEFINE dbname CHAR(64),
       err  SMALLINT

     LET err = 0

     IF LENGTH(dbname) = 0 THEN
        LET msg = "Must specify database"
        CALL op_log(msg)
        LET err = 1
     END IF

     WHENEVER ERROR CONTINUE

     DATABASE dbname
     IF status != 0 THEN
        LET msg = "Can't open:", dbname clipped
        CALL op_log(msg)
        LET msg = "Status: ", status
        CALL op_log(msg)
        LET err = 1
     ELSE
        IF ONLINE_SW THEN
           SET LOCK MODE TO WAIT 30

           IF STATUS THEN
              LET ONLINE_SW = 0
              LET msg = "RESETTING TO SE !!!!! - ", dbname clipped
              CALL op_log(msg)
           END IF

        END IF
     END IF

     CLOSE DATABASE

     RETURN err

END FUNCTION



###########################################################################
# misc housekeeping - init stuff.
###########################################################################
FUNCTION hskpng()

DEFINE i SMALLINT, lne CHAR(129), retcode INTEGER

   LET datatype[1]  = "CHAR"
   LET datatype[2]  = "SMALLINT"
   LET datatype[3]  = "INTEGER"
   LET datatype[4]  = "FLOAT"
   LET datatype[5]  = "SMALLFLOAT"
   LET datatype[6]  = "DECIMAL"
   LET datatype[7]  = "SERIAL"
   LET datatype[8]  = "DATE"
   LET datatype[9]  = "MONEY"
   LET datatype[10] = "UNKNOWN"
   LET datatype[11] = "DATETIME"
   LET datatype[12] = "BYTE"
   LET datatype[13] = "TEXT"
   LET datatype[14] = "VARCHAR"
   LET datatype[15] = "INTERVAL"
   LET datatype[16] = "UNKNOWN"		# little room for growth
   LET datatype[17] = "UNKNOWN"
   LET datatype[18] = "UNKNOWN"
   LET datatype[19] = "UNKNOWN"
   LET datatype[20] = "UNKNOWN"

   LET datetype[1] = "YEAR"
   LET intvray[1].start_point = 1
   LET intvray[1].end_point = 5		# offset by one for easy math

   LET datetype[3] = "MONTH"
   LET intvray[3].start_point = 5
   LET intvray[3].end_point = 7		

   LET datetype[5] = "DAY"
   LET intvray[5].start_point = 7
   LET intvray[5].end_point = 9		

   LET datetype[7] = "HOUR"
   LET intvray[7].start_point = 9
   LET intvray[7].end_point = 11		

   LET datetype[9] = "MINUTE"
   LET intvray[9].start_point = 11
   LET intvray[9].end_point = 13		

   LET datetype[11] = "SECOND"
   LET intvray[11].start_point = 13
   LET intvray[11].end_point = 15		

   LET datetype[12] = "FRACTION(1)"
   LET intvray[12].start_point = 15
   LET intvray[12].end_point = 16

   LET datetype[13] = "FRACTION(2)"
   LET intvray[13].start_point = 16
   LET intvray[13].end_point = 17		

   LET datetype[14] = "FRACTION(3)"
   LET intvray[14].start_point = 17
   LET intvray[14].end_point = 18		

   LET datetype[15] = "FRACTION(4)"
   LET intvray[15].start_point = 18
   LET intvray[15].end_point = 19		

   LET datetype[16] = "FRACTION(5)"
   LET intvray[16].start_point = 19
   LET intvray[16].end_point = 20		


   CALL proc_arg()

   START report dump_SQL TO ofile_name

   LET second_round=0			# switch for psh_strg()

					# load file of server swaps
   FOR max_servers = 1 TO 20
      INITIALIZE servers[max_servers].* TO NULL
   END FOR

   LET max_servers=20                   # number of array elements

   CALL fle_acc(serverfle,LENGTH(serverfle) + 1) RETURNING retcode
   IF retcode = 0 THEN
      LET i = 1
      LET retcode = 0
      WHILE retcode = 0
         CALL read_line(serverfle,LENGTH(serverfle) + 1) RETURNING lne, retcode
         IF retcode = 0 THEN
            LET servers[i].old_server = ex_field(lne,1)
            LET servers[i].new_server = ex_field(lne,2)
         END IF
         LET i = i + 1
      END WHILE
   ELSE
      LET msg = "Cannot open server file - ", retcode, " ", serverfle clipped
      CALL op_log(msg)
   END IF

					# switch to indicate new servers added
   LET WRITE_SRV = 0

END FUNCTION


############################################################################
# Easy way to dump to file
############################################################################

REPORT dump_SQL(SQL_line)

   DEFINE SQL_line CHAR(80)

   OUTPUT
     LEFT MARGIN 0
     RIGHT MARGIN 80
     TOP MARGIN 0
     BOTTOM MARGIN 0
     PAGE LENGTH 1			# no blank lines please

   FORMAT

      ON EVERY ROW
     print SQL_line clipped

END REPORT


############################################################################
# A function to handle commas and start/end pieces
# strg = string to put into statement
# strt = starting syntax e.g. "ALTER TABLE "
# end  = ending stuff to tack on to each line e.g. ","
# if strg = "END" then this is the last time we'll be here.
############################################################################
FUNCTION psh_strg(strg, strt, end_)

   DEFINE strg CHAR(80),
          strt, end_ CHAR(80),
	  i, j, k   SMALLINT

   IF strg = "END" THEN
      IF sql_idx > 1 THEN
         LET k = sql_idx-1
         LET sql_strgs[k] = sql_strgs[k] clipped, end_ clipped
         FOR i = 1 TO sql_idx - 1
            OUTPUT TO REPORT dump_SQL(sql_strgs[i])
            INITIALIZE sql_strgs[i] TO NULL
         END FOR
         LET sql_idx=1
         OUTPUT TO REPORT dump_SQL("")	# blank line
      ELSE
         LET strt = strt, end_ clipped 
         OUTPUT TO REPORT dump_SQL(strt)
         OUTPUT TO REPORT dump_SQL("")	# blank line
      END IF

   ELSE

      IF sql_idx > 2 OR second_round THEN
         LET k = sql_idx-1
         LET sql_strgs[k] = sql_strgs[k] clipped, end_
	 LET second_round=0
      END IF

      IF sql_idx = 1 THEN		# this is the first time
         LET sql_strgs[1] = strt
         LET sql_strgs[2] = "   ", strg	# indent
         LET sql_idx = 2
      ELSE
         LET sql_strgs[sql_idx] = "   ", strg	
      END IF

      LET sql_idx = sql_idx + 1

      IF sql_idx > 50 THEN		# array overflow imminent!
         FOR i = 1 TO sql_idx - 2	# save last line - move to [1]
            OUTPUT TO REPORT dump_SQL(sql_strgs[i])
            INITIALIZE sql_strgs[i] TO NULL
         END FOR
         LET sql_strgs[1]=sql_strgs[50]	
	#######################################################################
	# NUTS!! we specifically don't add $end to [1].  At the same time there
	# is no gaurantee that we don't have exactly 49 fields - in which case
	# this could REALLY be the last line and we don't want to stick $end 
        # on it we won't know till the next time thru.  Need a switch - how 
        # I hate fixing logic with switches.....
	#######################################################################
	 LET second_round=1
         LET sql_idx = 2
      END IF
   END IF

END FUNCTION


#############################################################################
# the last word in string may be truncated - so cut off everything up to
# that last word and return it, also return the remainder.
#############################################################################
FUNCTION clip_strg(strg)

DEFINE 	strg, rmdr CHAR(80),
        i,j,k SMALLINT

   LET j = LENGTH(strg)

   IF j > 60 THEN
      FOR i = j TO 1 STEP -1
				# need to look for space OR comma,
         IF strg[i,i] = " " OR strg[i,i] = "," THEN 
            EXIT FOR
         END IF
      END FOR

       IF i = j THEN
          LET rmdr = ""
       ELSE
         LET k    = i+1
         LET rmdr = strg[k,j]
         LET strg = strg[1,i]
       END IF
   ELSE
      LET rmdr = ""
   END IF

   RETURN strg, rmdr

END FUNCTION

###########################################################################
# operations logs - store
###########################################################################
FUNCTION op_log(txt)

DEFINE txt CHAR(80),
	i,j SMALLINT

   IF LOG_SW THEN
      OUTPUT TO REPORT logg(txt)
   END IF

   LET op_idx = op_idx + 1

   IF op_idx > 500 THEN

      FOR i = 401 to 500
         LET j=i-400
         LET oplog[j]=oplog[i]
      END FOR

      LET op_idx = 102
      LET oplog[101]="Log overflow - dropping first 400 msgs"

   END IF

   LET oplog[op_idx]=txt

   IF DEBUG THEN
      DISPLAY txt
   END IF

END FUNCTION

###########################################################################
# operations logs - view
###########################################################################

FUNCTION view_log()

   OPTIONS NEXT KEY CONTROL-N,
           PREVIOUS KEY CONTROL-P,
           ACCEPT KEY CONTROL-M

   OPEN WINDOW w_log AT 10,2 WITH FORM "w_log" ATTRIBUTE(BORDER)
   CALL set_count(op_idx)
   DISPLAY ARRAY oplog TO s_log.*

END FUNCTION


###########################################################################
# display errors - option to view log
###########################################################################
FUNCTION disp_err()

   DEFINE op CHAR(1)

   CALL op_log(msg)

   IF NOT QUIET_SW THEN

      WHILE TRUE
      LET op=button_at(5, 5, msg, "View [L]og, [E]xit, [C]ontinue", "[LEC]")

         CASE op
            WHEN "L"
               CALL view_log()
            WHEN "E"
               EXIT PROGRAM
            WHEN "C"
               EXIT WHILE
         END CASE
      END WHILE

   END IF

END FUNCTION


###########################################################################
# Try to resolve old synonym server vs desired server.
###########################################################################
FUNCTION get_server(old_server, tabname)

DEFINE old_server, tabname CHAR(20),
       i          SMALLINT

FOR i = 1 TO max_servers

					# already matched?
   IF old_server = servers[i].old_server THEN
      EXIT FOR
   END IF

					# new match
   IF  LENGTH(servers[i].old_server) = 0 THEN
      LET servers[i].old_server = old_server
      CALL accpt_server(servers[i].old_server, tabname) 
		  RETURNING servers[i].new_server
      EXIT FOR
   END IF

   IF i = 20 THEN
      CALL op_log("Server array overflow")
      RETURN old_server
   END IF

END FOR

RETURN servers[i].new_server

END FUNCTION


###########################################################################
# can't resolve it, ask.
###########################################################################
FUNCTION accpt_server(serv_name, tabname)

DEFINE serv_name, tabname CHAR(20)

   IF NOT QUIET_SW THEN

      OPEN WINDOW w_serv AT 7,9 WITH FORM "server" ATTRIBUTE(BORDER)
      DISPLAY tabname TO formonly.tabname
      INPUT serv_name WITHOUT DEFAULTS FROM formonly.serv_name
      CLOSE WINDOW w_serv
      LET WRITE_SRV = 1
      RETURN serv_name

   END IF

END FUNCTION

###########################################################################
# Reformat an extralong sql statement and push it out to the report.
# Note - this does not handle a string with no spaces.  To do that add a
# test for a "," in the IF statement.
# FIX THIS.
# This routine also can break a quoted string which is undesirable at
# times.
###########################################################################

FUNCTION fold_and_push(stmt,brk)

DEFINE stmt, stmt2 CHAR(1024),
	brk, i, j SMALLINT

# brk is a switch to indicate give or not give a blank line after end.

   WHILE LENGTH(stmt) > 80	   # while still over 80
      FOR i = 80 TO 1 STEP -1	   # search backwards for a break
         IF stmt[i,i] = " " THEN   # found - cut it
            LET stmt2 = stmt[1,i]  # grab first part...
            OUTPUT TO REPORT dump_sql(stmt2)   # and write it
            LET j=i+1              # start of next part
            LET i = LENGTH(stmt)   # end of whole string
            LET stmt2 = stmt[j,i]  # grab remaining string
            LET stmt = stmt2       # put it back into starting string
            EXIT FOR               # start search again
         END IF
      END FOR
   END WHILE

   OUTPUT TO REPORT dump_sql(stmt)   	   # don't forget last part

   IF brk THEN
      OUTPUT TO REPORT dump_sql("")	   # add a blank line
   END IF

END FUNCTION



############################################################################
# idx_parts(idxname, old_new)
# I grow weary of the same code in multiple locations.  This routine reads
# the parts[] structure from a sysindexes table and builds a column list
# thence.  It is called for indices and constraints.  Since constraints don't
# use the 'DESC' verb the parts structure should never have a negative value
# so don't worry about it.
############################################################################

FUNCTION idx_parts(p_idxname, old_new)

DEFINE	p_idxname 	CHAR(18),
	p_tabname 	CHAR(18),
	idxrec RECORD 
	   tabid     INTEGER, 
	   tabname   CHAR(18),
	   idxtype   CHAR(1), 
	   clustered CHAR(1)
        END RECORD,
	parts ARRAY [16] OF SMALLINT,
	i            SMALLINT,
	p_colname    CHAR(24),
	desc_sw      SMALLINT,
	strg, tmp_strg CHAR(80),
	idx_strng    CHAR(500),
	old_new SMALLINT

   IF old_new = 0 THEN		# old
      IF ONLINE_SW THEN
         SELECT o_systables.tabid, o_systables.tabname, idxtype, clustered,  
	     part1, part2, part3, part4, part5, part6, part7, part8, 
            part9, part10, part11, part12, part13, part14, part15, part16
         INTO idxrec.*, parts[1], parts[2], parts[3], parts[4],
                       parts[5], parts[6], parts[7], parts[8], 
                       parts[9], parts[10], parts[11], parts[12],
                       parts[13], parts[14], parts[15], parts[16] 
         FROM o_sysindexes, o_systables
         WHERE idxname = p_idxname
         AND o_sysindexes.tabid = o_systables.tabid 
      ELSE
         SELECT o_systables.tabid, o_systables.tabname, idxtype, clustered, 
            part1, part2, part3, part4, part5, part6, part7, part8 
         INTO idxrec.*, parts[1], parts[2], parts[3], parts[4], 
                       parts[5], parts[6], parts[7], parts[8] 
         FROM o_sysindexes, o_systables
         WHERE idxname = p_idxname
         AND o_sysindexes.tabid = o_systables.tabid 
      END IF
   ELSE				# new
      IF ONLINE_SW THEN
         SELECT systables.tabid, systables.tabname, idxtype, clustered,  
	     part1, part2, part3, part4, part5, part6, part7, part8, 
            part9, part10, part11, part12, part13, part14, part15, part16
         INTO idxrec.*, parts[1], parts[2], parts[3], parts[4],
                       parts[5], parts[6], parts[7], parts[8], 
                       parts[9], parts[10], parts[11], parts[12],
                       parts[13], parts[14], parts[15], parts[16] 
         FROM sysindexes, systables
         WHERE idxname = p_idxname
         AND sysindexes.tabid = systables.tabid 
      ELSE
         SELECT systables.tabid, systables.tabname, idxtype, clustered, 
            part1, part2, part3, part4, part5, part6, part7, part8 
         INTO idxrec.*, parts[1], parts[2], parts[3], parts[4], 
                       parts[5], parts[6], parts[7], parts[8] 
         FROM sysindexes, systables
         WHERE idxname = p_idxname
         AND sysindexes.tabid = systables.tabid 
      END IF
   END IF

   LET idx_strng = ""
					# add columns
   FOR i = 1 TO max_parts

      LET desc_sw = 0			# switch for descending sort

      IF parts[i] = 0 THEN
         EXIT FOR
      ELSE IF parts[i] < 0 THEN		# negative indicates a DESC
            LET desc_sw =1 
            LET parts[i]=parts[i] * (-1)	# reset to get col
         END IF
      END IF
      
      IF old_new = 0 THEN		# old
         SELECT colname 		# get column name
           INTO p_colname
           FROM o_syscols
          WHERE tabid = idxrec.tabid
            AND colno = parts[i]
      ELSE
         SELECT colname 		# get column name
           INTO p_colname
           FROM syscolumns
          WHERE tabid = idxrec.tabid
            AND colno = parts[i]
      END IF
   
      IF desc_sw THEN			# check for descending and fix
         LET p_colname = p_colname CLIPPED, " DESC"
      END IF

      LET idx_strng = idx_strng CLIPPED, " ", p_colname CLIPPED, ","

   END FOR

   LET i=LENGTH(idx_strng) - 1
   LET idx_strng = idx_strng[1,i]
   RETURN idx_strng

END FUNCTION

#############################################################################
# extract a "|" delimited field from a string
#############################################################################
FUNCTION ex_field(strng, fld)

DEFINE strng CHAR(130), fld, i, j, beg_pos, end_pos SMALLINT

   LET beg_pos = 1
   LET end_pos = 0

   LET i = 1		# index to string
   LET j = 0		# number of flds found so far

   WHILE j != fld	# while we haven't found the fld

      WHILE strng[i,i] != "|" AND i <= LENGTH(strng)
         LET i = i + 1
      END WHILE

      LET j = j + 1	# found one.

      IF j = (fld - 1) THEN   # the right one too
			      # note, will skip first time, which is ok
			      # since we initialized beg_pos to 1
         LET beg_pos = i + 1  # start point
      END IF

      IF j = fld THEN	      # end point
         LET end_pos = i - 1
      END IF

      LET i = i + 1
   END WHILE		# j <= fld

   IF end_pos = 0 THEN 
      LET end_pos = LENGTH(strng)
   END IF

   RETURN strng[beg_pos, end_pos]

END FUNCTION

###########################################################################
# write out the server names so we have them the next time
###########################################################################

REPORT save_server(servers)

DEFINE  servers  RECORD	# alternate servers
              old_server    CHAR(20),
              new_server    CHAR(20)
           END RECORD

OUTPUT
     LEFT MARGIN 0
     RIGHT MARGIN 80
     TOP MARGIN 0
     BOTTOM MARGIN 0
     PAGE LENGTH 1			# no blank lines please


FORMAT
ON EVERY ROW
   PRINT servers.old_server clipped, "|", servers.new_server

END REPORT

############################################################################
# Easy way to dump to file
############################################################################

REPORT logg(logmsg)

   DEFINE logmsg CHAR(80)

   OUTPUT
     LEFT MARGIN 0
     RIGHT MARGIN 80
     TOP MARGIN 0
     BOTTOM MARGIN 0
     PAGE LENGTH 1			# no blank lines please

   FORMAT

      ON EVERY ROW
     print logmsg clipped

END REPORT

{/* $Log:	dbdiff2.4gl,v $
Revision 1.4  95/06/14  12:49:50  12:49:50  jparker (Jack Parker)
s1/s2 switches made to work again (Cathy Kipp)
Typo in incorporating triggers/procedures o_systabs should be o_systables
Triggers and Procedures changed to use fold_and_push()
column can now be added BEFORE the first existing column  (Kerry)
Typo line 1033 (John Fowler)
log() changed to logg() (J Fowler)
Intervals with a precision (e.g. DAY(3) TO DAY) are now handled (jp)
Defaulted Constraints, User permissions and SPLs to ON.
Bug fix - when doing USER and not AUTH would crash - corrected.
SPL function no longer drops core.
General - added support for SPL, triggers and constraints.  Some bug fixes.

Revision 1.3  94/09/01  13:39:25  13:39:25  jparker (Jack Parker)
Corrected DATETIME/INTERVAL to print proper end points
Corrected DATETIME/INTERVAL to not include parens
Now handles DECIMAL(n) and DECIMAL(n,0)
Now handles DESCending indices.
Corrected syntax for BEFORE in the ALTER TABLE clause

Revision 1.2  94/05/30  16:05:31  16:05:31  jparker (Jack Parker)
now supports systabauth (-a) and sysusers (-u)
now supports individual or groups of tables (-t table_spec)
now supports on the fly changing of server names
Parens added to ALTER statement to support other versions of ISQL
Added status to db_check routine

 */}
@EOF

chmod 444 dbdiff_old.4gl

echo x - dialg_lib.4gl
cat >dialg_lib.4gl <<'@EOF'
###############################################################################
#
# Dialogue box library courtesy of Alan Popiel - Denver Co.
#
###############################################################################

GLOBALS

DEFINE	atcol	SMALLINT,	{ column position of left edge of box }
		ident 		char(80),
	atrow	SMALLINT,	{ row position of top edge of box }
	ncols	SMALLINT,	{ computed number of columns in box }
	nrows	SMALLINT,	{ computed number of rows in box }
	nlines	SMALLINT,	{ number of lines of msg_text }
	textline ARRAY[10] OF CHAR(74)	{ separated lines of msg_text }

END GLOBALS


{ module util_box.4gl - Dialog box utility functions
  author:	R. Alan Popiel,  President, Popiel Computing
  version:	2.10
  date:		04 Sep 1992


  NOTE:		This software is hereby placed in the public domain.  Popiel 
		Computing retains no rights or responsibility to this software.

		*** USE OF THIS SOFTWARE IS ENTIRELY AT YOUR OWN RISK. ***

		While Popiel Computing has made reasonable efforts to ensure 
		that these functions operate correctly, we make no claims as 
		their merchantability or fitness for any particular purpose.


  purpose:	This module contains utility functions for displaying dialog
		boxes, etc., on the screen.  All functions in this module 
		display a dialog box similar to this on the computer screen:

    +--------------------------+    upper left corner at 10,nn* or 'rw','cl'
    |     Centered 'title'     |    title and blank line omitted, if title = ""
    |                          |
    | 'msg_text', line 1       |   *nn will be computed to approximately
    | 'msg_text', line 2, etc. |    center the box horizontally in 80 cols.
    |                          |
    | 'ask_for' prompt string  |    'alert' does not use 'ask_for'
    +--------------------------+

  functions included:

    FUNCTION alert	   - no 'ask_for' or value, 3 second delay, auto close
    FUNCTION alert_at	   - same as above, with positioning

    FUNCTION button	   - generalized button box handler
    FUNCTION button_at	   - same as above, with positioning

    FUNCTION dialog	   - generalized dialog box handler, no validation on
				return value
    FUNCTION dialog_at	   - same as above, with positioning

    FUNCTION notify	   - 'ask_for' = "Press any key.", no return value
    FUNCTION notify_at     - same as above, with positioning

    FUNCTION accept_cancel - 'ask_for' = accept/cancel buttons, value in [AC]
    FUNCTION accept_cancel_at - same as above, with positioning

    FUNCTION screen_print  - 'ask_for' = screen/print/exit buttons, value in [SPX]
    FUNCTION screen_print_at - same as above, with positioning

    FUNCTION yes_no	   - 'ask_for' = yes/no buttons, value in [YN]
    FUNCTION yes_no_at     - same as above, with positioning
}


###############################################################################

FUNCTION alert ( title, msg_text )

{ purpose:	Display an alert box with message, then close it.
  inputs:	1. title:    (optional) text of title for alert box.
		2. msg_text: text of message, with | between lines.
  output:	displayed alert box.
}
DEFINE	title	CHAR(64),
	msg_text CHAR(512)

CALL alert_at ( 10, -1, title, msg_text )

END FUNCTION { alert }


FUNCTION alert_at ( rw, cl, title, msg_text )

{ purpose:	Display an alert box with message, then close it.
  inputs:	1. rw:       row location of top edge of dialog box. 
		2. cl:       column location of left edge of dialog box
		3. title:    (optional) text of title for dialog box.
		4. msg_text: text of message, with | between lines.
  output:	displayed dialog box.
}
DEFINE	rw	SMALLINT,
	cl	SMALLINT,
	title	CHAR(64),
	msg_text CHAR(512)

let ident = "@(#)$Header: dialg_lib.4gl,v 1.2 94/01/26 16:06:25 hmgr Exp $"
LET atrow = rw
LET atcol = cl
LET ncols = LENGTH ( title ) + 2

IF ncols = 2 THEN		{ Special case for alerts without titles: }
    LET msg_text = "|", msg_text CLIPPED, "||"
END IF				{ Prevent one-line box which fouls up menus. }

CALL parse_text ( msg_text )
				{ Compute box dimensions. }
LET ncols = ncols  + 2
LET nrows = nlines
IF LENGTH ( title ) > 0 THEN
    LET nrows = nrows + 2
END IF

CALL open_dialog_box ( title )

SLEEP 3

CALL close_dialog_box ()

END FUNCTION { alert_at }

###############################################################################

FUNCTION button ( title, msg_text, ask_for, okay ) { RETURNING CHAR(1) }

{ purpose:      Display dialog box with buttons.
  inputs:	1. title:    (optional) text of title for dialog box.
                2. msg_text: text of message, with | between lines.
		3. ask_for:  text of prompt message.
		4. okay:     list of acceptable input chars.  format: "[ABC]".
  outputs:      1. displayed dialog box.
                2. function value: character entered by user, validated against
		   "okay" characters.
}
DEFINE  title   CHAR(64),
        msg_text CHAR(512),
        ask_for CHAR(64),
	okay	CHAR(8)

RETURN button_at ( 10, -1, title, msg_text, ask_for, okay )

END FUNCTION { button }


FUNCTION button_at ( rw, cl, title, msg_text, ask_for, okay )
		{ RETURNING CHAR(1) }

{ purpose:      Display dialog box with buttons.
  inputs:	1. rw:       row location of top edge of dialog box. 
		2. cl:       column location of left edge of dialog box
		3. title:    (optional) text of title for dialog box.
                4. msg_text: text of message, with | between lines.
		5. ask_for:  text of prompt message.
		6. okay:     list of acceptable input chars.  format: "[ABC]".
  outputs:      1. displayed dialog box.
                2. function value: character entered by user, validated against
		   "okay" characters.
}
DEFINE  rw	SMALLINT,
	cl	SMALLINT,
	title   CHAR(64),
        msg_text CHAR(512),
        ask_for CHAR(64),
	okay	CHAR(8),

        answer  CHAR(1),	{ single character, input by user }
	last_ok	SMALLINT	{ position of last okay character }

LET atrow = rw
LET atcol = cl
LET ncols = LENGTH ( title ) + 2

CALL parse_text ( msg_text )
LET ask_for = buttons ( ask_for )

				{ Compute box dimensions. }
LET ncols = ncols  + 2
LET nrows = nlines + 2
IF LENGTH ( title ) > 0 THEN
    LET nrows = nrows + 2
END IF
 
CALL open_dialog_box ( title )
				{ Prompt for and get user response. }
LET INT_FLAG = FALSE
LET answer   = " "
LET okay     = UPSHIFT ( okay )
LET last_ok  = LENGTH  ( okay ) - 1
WHILE TRUE
    PROMPT ask_for CLIPPED, " " FOR CHAR answer

    CASE
    WHEN INT_FLAG
        LET INT_FLAG = FALSE
        LET answer   = okay[last_ok]
    WHEN LENGTH ( answer ) < 1 
        LET answer   = okay[2]
    OTHERWISE
        LET answer   = UPSHIFT ( answer )
    END CASE

    IF answer MATCHES okay THEN
        EXIT while
    ELSE
        ERROR ""
    END IF
END WHILE

CALL close_dialog_box ()

RETURN answer

END FUNCTION { button_at }

###############################################################################

FUNCTION dialog ( title, msg_text, ask_for ) { RETURNING CHAR(1) }

{ purpose:	Display generalized dialog box.
  inputs:	1. title:    (optional) text of title for dialog box
		2. msg_text: text of message, with | between lines
		3. ask_for:  (optional) text of prompt message
  outputs:	1. displayed dialog box
		2. function value: character entered by user, unvalidated
}
DEFINE	title	 CHAR(64),
	msg_text CHAR(512),
	ask_for	 CHAR(64)

RETURN dialog_at ( 10, -1, title, msg_text, ask_for )

END FUNCTION { dialog }


FUNCTION dialog_at ( rw, cl, title, msg_text, ask_for ) { RETURNING CHAR(1) }

{ purpose:	Display generalized dialog box.
  inputs:	1. rw:       row location of top edge of dialog box. 
		2. cl:       column location of left edge of dialog box
		3. title:    (optional) text of title for dialog box
		4. msg_text: text of message, with | between lines
		5. ask_for:  (optional) text of prompt message
  outputs:	1. displayed dialog box
		2. function value: character entered by user, unvalidated
}
DEFINE	rw	SMALLINT,
	cl	SMALLINT,
	title	CHAR(64),
	msg_text CHAR(512),
	ask_for	CHAR(64),

	answer	CHAR(1)		{ single character, input by user }

LET atrow = rw
LET atcol = cl
LET ncols = LENGTH ( title ) + 2

CALL parse_text ( msg_text )
				{ Set default prompt string, if necessary. }
IF LENGTH ( ask_for ) = 0 THEN
    LET ask_for = " Press any key to continue."
ELSE
    IF ask_for[1] != " " THEN
        LET ask_for = " ", ask_for CLIPPED
    END IF
END IF
				{ Compute box dimensions. }
IF LENGTH ( ask_for ) > ncols THEN
    LET ncols = LENGTH ( ask_for )
END IF
LET ncols = ncols  + 2
LET nrows = nlines + 2
IF LENGTH ( title ) > 0 THEN
    LET nrows = nrows + 2
END IF

CALL open_dialog_box ( title )
				{ Prompt for and get user response. }
PROMPT ask_for CLIPPED, " " FOR CHAR answer

CALL close_dialog_box ()

RETURN answer

END FUNCTION { dialog_at }

###############################################################################

FUNCTION notify ( title, msg_text )

{ purpose:	Display generalized dialog box.
  inputs:	1. title:    (optional) text of title for dialog box.
		2. msg_text: text of message, with | between lines.
  output:	displayed dialog box.
}
DEFINE  title   CHAR(64),
        msg_text CHAR(512)

CALL notify_at ( 10, -1, title, msg_text )

END FUNCTION { notify }
 

FUNCTION notify_at ( rw, cl, title, msg_text )

{ purpose:	Display generalized dialog box.
  inputs:	1. title:    (optional) text of title for dialog box.
		2. msg_text: text of message, with | between lines.
  output:	displayed dialog box.
}
DEFINE	rw	SMALLINT,
	cl	SMALLINT,
	title	CHAR(64),
	msg_text CHAR(512),

	answer	CHAR(1)		{ single character, input by user }

LET answer = dialog_at ( rw,cl, title, msg_text, " Press space bar to continue." )

END FUNCTION { notify }
 
###############################################################################

{ Generally useful button box routines. }
 
###############################################################################
 
FUNCTION accept_cancel ( title, msg_text ) { RETURNING CHAR(1) }

{ purpose:      Display dialog box with accept/cancel buttons.
  inputs:	1. title:    (optional) text of title for dialog box
                2. msg_text: text of message, with | between lines
  outputs:      1. displayed dialog box
                2. function value: character entered by user, either "A" or "C"
}
DEFINE  title   CHAR(64),
        msg_text CHAR(512)

RETURN button_at ( 10, -1, title, msg_text, " ((Accept))  (Cancel)", "[AC]" )

END FUNCTION { accept_cancel }


FUNCTION accept_cancel_at ( rw, cl, title, msg_text ) { RETURNING CHAR(1) }

{ purpose:      Display dialog box with accept/cancel buttons.
  inputs:	1. rw:       row location of top edge of dialog box. 
		2. cl:       column location of left edge of dialog box
		3. title:    (optional) text of title for dialog box
                4. msg_text: text of message, with | between lines
  outputs:      1. displayed dialog box
                2. function value: character entered by user, either "A" or "C"
}
DEFINE	rw	SMALLINT,
	cl	SMALLINT,
	title   CHAR(64),
        msg_text CHAR(512)

RETURN button_at ( rw, cl, title, msg_text, " ((Accept))  (Cancel)", "[AC]" )

END FUNCTION { accept_cancel_at }

###############################################################################
 
FUNCTION screen_print ( title, msg_text ) { RETURNING CHAR(1) }

{ purpose:      Display dialog box with screen/print/exit buttons.
  inputs:	1. title:    (optional) text of title for dialog box
                2. msg_text: text of message, with | between lines
  outputs:      1. displayed dialog box
                2. function value: character entered by user, one of "S","P","X"
}
DEFINE  title   CHAR(64),
        msg_text CHAR(512)

RETURN button_at ( 10, -1, title, msg_text, 
		 " ((Screen))  (Print)  (eXit)", "[SPX]" )

END FUNCTION { screen_print_at }


FUNCTION screen_print_at ( rw, cl, title, msg_text ) { RETURNING CHAR(1) }

{ purpose:      Display dialog box with screen/print/exit buttons.
  inputs:	1. rw:       row location of top edge of dialog box. 
		2. cl:       column location of left edge of dialog box
		3. title:    (optional) text of title for dialog box
                4. msg_text: text of message, with | between lines
  outputs:      1. displayed dialog box
                2. function value: character entered by user, one of "S","P","X"
}
DEFINE  rw	SMALLINT,
	cl	SMALLINT,
	title   CHAR(64),
        msg_text CHAR(512)

RETURN button_at ( rw, cl, title, msg_text,
		 " ((Screen))  (Print)  (eXit)", "[SPX]" )

END FUNCTION { screen_print_at }

###############################################################################
 
FUNCTION yes_no ( title, msg_text ) { RETURNING CHAR(1) }

{ purpose:      Display dialog box with yes/no buttons.
  inputs:	1. title:    (optional) text of title for dialog box
                2. msg_text: text of message, with | between lines
  outputs:      1. displayed dialog box
                2. function value: character entered by user, either "Y" or "N"
}
DEFINE  title    CHAR(64),
        msg_text CHAR(512)

RETURN button_at ( 10, -1, title, msg_text, "  ((Yes))  (No)", "[YN]" )
 
END FUNCTION { yes_no }

 
FUNCTION yes_no_at ( rw, cl, title, msg_text ) { RETURNING CHAR(1) }

{ purpose:      Display dialog box with yes/no buttons.
  inputs:	1. rw:       row location of top edge of dialog box. 
		2. cl:       column location of left edge of dialog box
		3. title:    (optional) text of title for dialog box
                4. msg_text: text of message, with | between lines
  outputs:      1. displayed dialog box
                2. function value: character entered by user, either "Y" or "N"
}
DEFINE  rw	SMALLINT,
	cl	SMALLINT,
	title   CHAR(64),
        msg_text CHAR(512)

RETURN button_at ( rw, cl, title, msg_text, "  ((Yes))  (No)", "[YN]" )
 
END FUNCTION { yes_no_at }

###############################################################################

{ Internal routines.  Not intended for external use. }

###############################################################################

FUNCTION buttons ( ask_for ) { RETURNING CHAR(64) }

{ purpose:	Construct a prompt string containing centered "buttons".
  inputs:	1. ask_for: simulated buttons, as prompt string.
		2. ncols: number of columns in box.
  outputs:	1. functions value: centered prompt string.
		2. ncols: possibly incremented number of columns in box.
  NOTE: This function is intended for use ONLY by routines in this module. 
}
DEFINE	ask_for CHAR(64),
	blanks	CHAR(64),
	n	SMALLINT

IF LENGTH ( ask_for ) > ncols THEN
    LET ncols = LENGTH ( ask_for )
END IF
LET blanks = "                                                                "
LET n = ( ncols - LENGTH ( ask_for ) ) / 2
IF n > 0 THEN
    LET ask_for = blanks[1,n], ask_for CLIPPED
END IF
RETURN ask_for

END FUNCTION { buttons }

###############################################################################

FUNCTION close_dialog_box ()
 
{ purpose:      Close window and reset position of PROMPT LINE.
  input:        none.
  outputs:      1. closed dialog box.
                2. OPTIONS reset.
  NOTE: This function is intended for use ONLY by routines in this module. 
}
CLOSE WINDOW dialog_box
OPTIONS PROMPT LINE FIRST

END FUNCTION { close_dialog_box }

###############################################################################

FUNCTION open_dialog_box ( title )

{ purpose:      Open window and display title, if any, and message text.
  input:        title:    (optional) text of title for dialog box
  outputs:      1. displayed dialog box.
                2. function value: 0 or 2, used as affset for positioning the
		   rest of the line in the dialog box.
  NOTE: This function is intended for use ONLY by routines in this module. 
}
DEFINE  title   CHAR(64),
 
	j, k, n	SMALLINT,	{ scratch indices used for positioning, etc. }
        topline CHAR(64)	{ variable used to construct title line }

IF atrow <= 0 OR atrow >= 50 THEN { Test for uninitialized row. }
    LET atrow = 10
END IF
IF atcol <= 0 OR atcol >= 70 THEN { Test for "center box" flag: atcol <= 0 }
    LET atcol = (80 - ncols) / 2 + 1
END IF
OPEN WINDOW dialog_box AT atrow,atcol WITH nrows ROWS, ncols COLUMNS
    ATTRIBUTE (BORDER, PROMPT LINE LAST)
IF LENGTH ( title ) > 0 THEN
    LET topline =
        "                                                                "
    LET n = ( ncols - LENGTH ( title ) ) / 2
    LET topline = topline[1,n], title CLIPPED
    DISPLAY topline AT 1,1
    LET n = 2
ELSE
    LET n = 0
END IF
				{ Display message text. }
FOR j = 1 TO nlines
    LET k = j + n
    DISPLAY textline[j] AT k,2
END FOR

END FUNCTION { open_dialog_box }

###############################################################################

FUNCTION parse_text ( msg_text )

{ purpose:	Parse msg_text into lines.
  inputs:	1. msg_text: text of message, with | between lines
		2. ncols: (minimum) number of columns in box
  outputs:	1. ncols: (possibly) updated number of columns in box, based
			  on widest line in textline array
		2. nlines: number of lines in textline array 
		3. textline: array containing separated lines of msg_text
  NOTE: This function is intended for use ONLY by routines in this module. 
}
DEFINE	msg_text CHAR(512),	{ text of message, with | between lines }

	j, k, n	SMALLINT,	{ indexes used to parse msg_text into line }
	len	SMALLINT	{ length of msg_text string }

LET len    = LENGTH ( msg_text )
LET n      = 0
LET nlines = 0

WHILE n < len AND nlines < 10
				{ Search for line separator. }
    LET j = n + 1
    LET k = j
    WHILE msg_text[k] != "|" AND k < len
        LET k = k + 1
    END WHILE
    IF k - j > 72 THEN
        LET k = j + 72
    END IF
				{ Separator found.  Check for special cases. }
    LET n = k
    IF msg_text[k] = "|" THEN
        LET k = k - 1
    END IF
				{ Move line into array of lines. }
    LET nlines = nlines + 1
    IF k <= j THEN
        LET textline[nlines] = ""
    ELSE
        LET textline[nlines] = msg_text[j,k]
    END IF
    IF LENGTH ( textline[nlines] ) > ncols THEN
        LET ncols = LENGTH ( textline[nlines] )
    END IF
END WHILE	

END FUNCTION { parse_text }

###############################################################################
@EOF

chmod 664 dialg_lib.4gl

echo x - history
cat >history <<'@EOF'
History:

Views added 
ALTER TABLE syntax corrected 
Limitation on number of columns in a table removed.  (that is upped from 
50 to 500).

Online/SE detection made to work (Thank you DAS)
now handles 50+ SQL code lines correctly (DAS)

Now handles working against one DB at a time in two invocations.
(use -S1 and -S2 switches) (Thank you Walt)

Now handles SE better.

Messages cleaned to not appear so devastating (Thank you Paul P.)
global change of 'end' variable to 'end_' - reserved word (Paul)
Output file name cleaned a bit so it doesn't overflow - still limited to 
char(64) (Paul)

Added -dbg option to run in debug mode - logs status messages and allows
user to view on error.

Added Alan Popiel's dialogue window functions to make interaction nicer during
error routine.

Added debug log and error routine to view latest actions.  Only available after
an error.

Many thanks also to Jonathan Leffler - who answered my incessantly trivial
questions - with real answers instead of just "RTFM".

Corrected a syntax error in the CREATE INDEX statement.  
(Thank you Robert Minter)

Corrected a sometimes syntax error in the ALTER TABLE statement.  
(Thank you Robert Minter)

Corrected a problem wherein dbdiff2 attempts to (incorrectly) resolve 
constraints.
(Thank you Martin Andrews)

Thanks to John Brown for the table specific concept and the code to make 
it work.

It appears that I did not previously include the form w_log.per which is
used in error mode for viewing the log.  It is now included.

1.3

Corrected switch so that user permission could be performed w/o also running
for table permissions.  (thank you masked man (i.e. I dropped the mail you sent
and so lost your name))

Corrected to handle for DESC indices (negative values in part[n] of sysindexes)
(Thank you Mike Reetz & Kerry Sainsbury)

Corrected DATETIME/INTERVAL syntax (Parens and end points - Scott Holmes & 
Masked Man)

Corrected DECIMAL syntax (Kerry Sainsbury & John Brown)

Now handles BEFORE properly in the ALTER TABLE clause 
			(Kerry Sainsburry & Scott Holmes)

Removed the ALTER INDEX TO NOT CLUSTER.  (Scott Holmes)

Changed spelling of CLUSTERED to CLUSTER >oops<.  (Scott Holmes)

1.4 (Beta)

Corrected syntax of CREATE INDEX statement

Corrected parsing of VARCHAR datatype

clip_strg() now handles 60 char length strings correctly (John Brown)

Kerry adds support for Triggers and Store Procedures (-spl/-trg)

JP adds support for constraints	(-c)

Quiet mode now supported (-q)

Servers conversions can now be pre-entered in a file (-svr filename)

Logging can now be sent to a file (-lg, -lgf)

Since a number of new 'c' code segments have been added, they have all been
thrown into a single source file called lib_c.c.

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

$Log:       dbdiff2.4gl,v $
Revision 1.2  94/05/30  16:05:31  16:05:31  jparker (Jack Parker)
now supports systabauth (-a) and sysusers (-u)
now supports individual or groups of tables (-t table_spec)
now supports on the fly changing of server names
Parens added to ALTER statement to support other versions of ISQL
Added status to db_check routine

Revision 1.3  94/09/01  13:39:25  13:39:25  jparker (Jack Parker)
Corrected DATETIME/INTERVAL to print proper end points
Corrected DATETIME/INTERVAL to not include parens
Now handles DECIMAL(n) and DECIMAL(n,0)
Now handles DESCending indices.
Corrected syntax for BEFORE in the ALTER TABLE clause

------------------------------------
Revision 1.4 (Beta)

Added support for Triggers	(Kerry Sainsbury)
		  Stored Procedures (Kerry again)
		  Constraints	(JParker)
Varchar wouldn't print lengths in the proper sequence - corrected
Fixed create index syntax
clip_strg() corrected (John Brown)
Added a 'quiet' mode.
Added support for a file of server names.
adding logging of status messages.

------------------------------------
1.4 Beta corrections:

s1/s2 switches made to work again (Cathy Kipp)

Typo in incorporating triggers/procedures o_systabs should be o_systables
	(Kerry)

Triggers and Procedures changed to use fold_and_push() 

column can now be added BEFORE the first existing column  (Kerry)

Not resolved:

Kerry points out that fold_and_push() double spaces output and can
potentially break a line within quotes.  This means constraints which use
the CHECK clause may mess up.  Since I just changed triggers and procedures
to use this routine they have the same potential problem.

	Handled.

CLUSTERing does not first ALTER INDEX xxxx TO NOT CLUSTER an existing
CLUSTERed index.  (back burnered).

Kerry also provided me with RDS versions of the three 'c' routines and the
makefile.

	Handled.

This listing split out of the READ.ME file.

Typo line 1033 (John Fowler)

log() changed to logg() (J Fowler)

Makefile dependencies changed to make dialg_lib.o and lib_c.o

Intervals with a precision (e.g. DAY(3) TO DAY) are now handled (jp)

Defaulted Constraints, User permissions and SPLs to ON.

Bug fix - when doing USER and not AUTH would crash - corrected.

SPL creation dropping core.  I've spent about 6 hours on this section and
can by adding a single line of code drop the core quite easily.  I don't
fully understand it - but have commented out that line of code.  This means
that the SPL loses all white space between code lines.

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

Future (prioritized):

Permissions (authority tables)	( in process - haven't done columns yet )
Support for sysdefaults.
fold_and_push - constrain for quotes.
non-self-excluded index prob.
Rebuild table option when column order is hosed.
Full Support for ANSI mode
Form driven option

@EOF

chmod 664 history

echo x - lib_c.c
cat >lib_c.c <<'@EOF'
/* Author:  R. Alan Popiel
 * Date:    12 Nov 1993
 *
 * usage:
 *   CALL translate ( old, new, strin ) RETURNING strout
 *   LET strout = translate ( old, new, strin )
 *
 * translate()  replaces all occurences of 'old' in 'strin' with 'new'
 *
 * maximum lengths of input and output strings:
 *   old, new are limited to 256 characters
 *   strin, strout are limited to 512 characters
 */

#define SPACE ' '
#define NULL  '\0'
#include <string.h>
#include <stdio.h>

translate (nparms)
int     nparms;

{
  char  old[257];       /* old pattern to seek */
  char  new[257];       /* new pattern to replace 'old' */
  char  strin[2001];     /* string in which to do replacement */

  char  strout[2001];    /* resulting string */

  int   lo, ln, ls;     /* string lengths */
  char  *sin, *sout;    /* pointers for 'strin', 'strout' */
  char  *pfound;        /* pointer to 'old' in 'strin' */
  int   offset;         /* number of chars between sin and pfound */

                        /* fetch arguments */
  popquote( strin, sizeof(strin) );
  popquote(   new, sizeof(new) );
  popquote(   old, sizeof(old) );

                        /* initializations */
  ls = strlen( strin );
  ln = strlen( new );
  lo = strlen( old );

  while ( strin[ls-1] == SPACE ) ls--;  strin[ls] = NULL;
  while (   new[ln-1] == SPACE ) ln--;    new[ln] = NULL;
  while (   old[lo-1] == SPACE ) lo--;    old[lo] = NULL;

  sin  = strin;
  sout = strout;
  strout[0] = NULL;

                        /* loop until input string is exhausted */
  while ( *sin != NULL ) {
    pfound = strstr( sin, old );
    if ( pfound != NULL ) { /* 'old' found in 'strin' */
      offset = pfound - sin;

      strncpy( sout, sin, offset ); /* copy up to 'old' */
      sout = sout + offset;
      strncpy( sout, new, ln ); /* copy 'new' to 'strout' */
      sout = sout + ln;
      *sout = NULL;             /* terminate 'strout' */

      sin = pfound + lo;        /* position past 'old' in 'strin' */
    }
    else {                      /* no more 'old' exist in 'strin' */
      strcpy( sout, sin );      /* copy the end of 'strin' to 'strout' */
      break;
    }
  } /* end while loop on input string */

  retquote( strout );
  return(1);
}

/******************************************************************************
*
*	read_line.c	Read a line from any file and return it
*
*			jparker 12/5/92
*
*			(C) Copyright Hewlett Packard 1992
*
******************************************************************************/

FILE	*fopen(), *fp;
char c;				/* global, so we can check for EOF */

char *get_strg();

int read_line(narg)
int narg;
{
char	fle[130], fl_ln[120]; 
int	lenf, ret_code;

				/* get file name */
   popint(&lenf); popquote(fle, lenf);

				/* If the file isn't open do it */
   if (!fp) fp = fopen(fle, "r");

   ret_code=0;			/* init to 0 */
   fl_ln[0]='\0';

   strcpy(fl_ln,get_strg());	/* read line */

				/* If eof, close file and set ret_code */
   if (c==EOF)
	{
	fclose(fp);
        fp='\0';
	fl_ln[0]='\0';
        ret_code=1;
	}

				/* Return data */
   retquote(fl_ln);
   retint(ret_code);
   return(2);

}

char *get_strg()
{
char	strg[120];
int	i;
			/* Read a char until end of line, or file */
  i=0;
  while ((c=getc(fp)) != EOF && c != '\n') 
	strg[i++]=c;

  strg[i]='\0';
  return strg;
}


/*
 fle_acc(), check access permission/existence of a file
*/

fle_acc(argc)
{
char    fle[60];
int     retcode, siz;

   popint(&siz);                        /* get dir name */
   popquote(fle,siz);

   retcode=access(fle,4);               /* test read access & existence*/

   retint(retcode);                     /* return retcode */

   return(1);
}
@EOF

chmod 664 lib_c.c

echo x - rds_lib.4gl
cat >rds_lib.4gl <<'@EOF'
# lib_4gl.4gl
# 4GL-RDS routines which emulate the lib_c.c routines.
# Kerry Sainsbury 1994.

FUNCTION translate(l_old, l_new, l_in)
DEFINE   l_old            CHAR(256),
         l_new            CHAR(256),
         l_in             CHAR(512),
         l_cnt            SMALLINT,
         l_oldlth         SMALLINT,
         l_difflth        SMALLINT,
         l_for            SMALLINT,
         i                SMALLINT

   LET l_cnt = LENGTH(l_in)
   LET l_oldlth = LENGTH(l_old)
   LET l_difflth = l_old - LENGTH(l_new)
   LET l_for = l_cnt - l_oldlth
   FOR i = 1 TO l_for
      IF l_in[i, i+l_oldlth-1]=l_old CLIPPED THEN
         IF i > 1 THEN
            LET l_in = l_in[1,i-1],l_new CLIPPED,
                l_in[i+l_oldlth,l_cnt] CLIPPED
         ELSE
            LET l_in = l_new CLIPPED,
                l_in[i+l_oldlth,l_cnt] CLIPPED
         END IF
         LET l_for = l_for - l_difflth
         LET l_cnt = l_cnt - l_difflth
         LET i = i - l_difflth
      END IF
   END FOR
   RETURN l_in
END FUNCTION


FUNCTION fle_acc(serverfle, dummy) 
DEFINE   serverfle        CHAR(80),     # dbdiff2 compatability parameters
         dummy            INTEGER

DEFINE   l_unix_cmd       CHAR(300)

DEFINE   l_text           CHAR(300),
         l_tempfile       CHAR(100),
         l_delimiters     CHAR(6),
         l_delim          CHAR(1),
         i                INTEGER

   LET l_unix_cmd = "cat ", serverfle CLIPPED," 2>&1 > /dev/null | grep shutup"
   RUN l_unix_cmd RETURNING i
   IF i !=0 THEN
      RETURN 1
   END IF

   LET l_unix_cmd = "cat ", serverfle CLIPPED

   WHENEVER ANY ERROR CONTINUE  # It's a bit ugly I know..
   DROP TABLE picklist
   CREATE TEMP TABLE picklist
         (ptext   char(300),
          pline   serial) WITH NO LOG
   WHENEVER ANY ERROR STOP

   LET l_tempfile = "/tmp/",fgl_getenv("LOGNAME") CLIPPED, TIME,".tmp"

   LET l_delimiters = ASCII(94),ASCII(92),ASCII(96),ASCII(124),ASCII(126),
                      ASCII(95)
   FOR i = 1 TO 6
      LET l_delim = l_delimiters[i]
      LET l_text = l_unix_cmd CLIPPED,
                 " | sed -e 's/$/",l_delim,"0",l_delim,"/' > ", l_tempfile
      RUN l_text
      WHENEVER ANY ERROR CONTINUE
      LOAD FROM l_tempfile DELIMITER l_delim
      INSERT INTO picklist 
      WHENEVER ANY ERROR STOP
      IF status =0 THEN
         LET i = 999
         EXIT FOR
      END IF
   END FOR

   IF i != 999 THEN
      ERROR "ERROR: open_flat() is unable to process ASCII file ", l_tempfile CLIPPED
      SLEEP 5
#     LET l_unix_cmd = "ERROR: open_flat() is unable to process an ASCII file ",
#                      "which contains all of the following characters: ",
#                       l_delimiters CLIPPED,". Please advise your support ",
#                       "company of this error and suggest they look in the ",
#                       "file ",l_tempfile CLIPPED,"."
#     CALL message_prompt(l_unix_cmd, "")
      RETURN 1
   END IF

   DECLARE flat_curs CURSOR FOR
    SELECT ptext, pline FROM picklist
     ORDER BY pline
   OPEN flat_curs
   IF status !=0 THEN
      RETURN 1
   END IF

   LET l_text = "rm -f ",l_tempfile
   RUN l_text WITHOUT WAITING
   RETURN 1

END FUNCTION


FUNCTION read_line(dummy1, dummy2)
DEFINE   dummy1           CHAR(80),
         dummy2           INTEGER
DEFINE   l_text           CHAR(300),
         l_retcode        SMALLINT

   FETCH flat_curs INTO l_text
   IF status !=0 THEN
      LET l_text = ""
      LET l_retcode = 0
   ELSE
      LET l_retcode = 1
   END IF
   IF l_text IS NULL THEN LET l_text = " " END IF

   RETURN l_text, l_retcode
END FUNCTION
@EOF

chmod 644 rds_lib.4gl

echo x - server.per
cat >server.per <<'@EOF'
database formonly
screen {
I am going to point [f000              ]
To the server       [f001                ]
Press (RETURN) to accept, or change it
}
end
attributes
f000 = formonly.tabname type text, noentry, noupdate;
f001 = formonly.serv_name type text;
end
@EOF

chmod 664 server.per

echo x - w_log.per
cat >w_log.per <<'@EOF'
database formonly
screen 
{
[f001                                                        ]
[f001                                                        ]
[f001                                                        ]
[f001                                                        ]
[f001                                                        ]
[f001                                                        ]
[f001                                                        ]
[f001                                                        ]
}
end
attributes
f001=formonly.log_msg TYPE CHAR;
end
instructions
DELIMITERS "  "
SCREEN RECORD s_log[8](formonly.log_msg)
@EOF

chmod 664 w_log.per

exit 0