dbdiff2 - database schema aligner (Part 1 of 2)

There's a mouthful. Here it is. Have fun. I'm out of town for a week so don't expect fast answers to questions. cheers j. _____________________________________________________________________________ Jack Parker | Hewlett Packard, BSMC Boise, Idaho, USA| Domini, Domini, Domini jparker@hpbs3645.boi.hp.com | you're all Catholic now. (208) 396-5388 (W) (208) 384-1623 (H) | - FT _____________________________________________________________________________ Any opinions expressed herein are my own and not those of my employers. _____________________________________________________________________________ --- cut here ---------------------------------------------------------------- # 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 Jack Parker <jparker@hpbs2651> on Fri Apr 22 16:35:25 1994 # # This archive contains: # READ.ME Makefile dialg_lib.4gl dbdiff2.4gl # LANG=""; export LANG PATH=/bin:/usr/bin:$PATH; export PATH echo x - READ.ME cat >READ.ME <<'@EOF' 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? ------------------------------------ Syntax is listed at the start of the source - 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. History: Views added ALTER TABLE syntax correctedLimitation on number of columns in a table removed. 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". Future: Full Support for ANSI mode Form driven option Rebuild table option when column order is hosed. Permissions (authority tables) support for sysprocedures & systriggers @EOF chmod 664 READ.ME echo x - Makefile cat >Makefile <<'@EOF' dbdiff2 : dbdiff2.4gl c4gl -o $@ dbdiff2.4gl dialg_lib.4gl sharfile : READ.ME dbdiff2.4gl shar READ.ME Makefile dialg_lib.4gl dbdiff2.4gl > dbdiff2.sh @EOF chmod 664 Makefile 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