dbdiff2 1.3

All, Thanks to Scott and Kerry for the corrections to this one. I actually did work on column permissions for a while, but have left it long enough, there are enough changes to warrant a new copy. It does currently do table and user permissions. I have sent this 'release' to Walt to live in the archives until such time as you choose to grab it. Enclosed is the READ.ME file for this 'release'. You can skip straight to '1.3' if youre already familiar with dbdiff2. As always, this is collaborative shareware. Send me your poor huddled changes yearning to be applied. Just plain complaints are ok too. cheers j. --- cut here --- 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. ------------------------------------ History: Views added ALTER TABLE syntax correctedLimitation 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) ------------------------------------ $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 ------------------------------------ Future (prioritized): Permissions (authority tables) ( in process - haven't done columns yet ) Rebuild table option when column order is hosed. support for constraints support for sysprocedures & systriggers 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