# 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 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$( $@ 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 #include 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