dbdiff2 - database schema aligner (Part 2 of 2)

==== Inserted between parts 1 and 2. WH ===================================== # 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 ntabname = tabrec.tabname IF LENGTH(synrec.servername) > 0 THEN LET strg = "CREATE SYNONYM ", tabrec.tabname clipped, " FOR ", synrec.dbname clipped, "@", synrec.servername 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_systabs 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 FREE get_col ################################## # 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_systabs.tabid, o_systabs.tabname FROM o_systabs, "informix".systables WHERE "informix".systables.tabname = o_systabs.tabname AND "informix".systables.tabtype = 'T' PREPARE o_c FROM "SELECT colname, colno, coltype, collength FROM o_syscols WHERE tabid = ? ORDER BY colno" DECLARE o_cols CURSOR FOR o_c 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 o_cols USING tabrec.tabid FOREACH o_cols INTO chk_cols[j].o_colname, chk_cols[j].o_colno, chk_cols[j].o_coltype, chk_cols[j].o_collength 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 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) END IF CALL psh_strg(strg, tmp_strg, ",") ELSE # o_colname = n_colname - # check type/length/colno IF chk_cols[i].n_colno !=