The Referential Integrity Analyzer dbref (release 2)

Dear Colleagues, I added some new features in the 2-nd version of the dbref utility. The dbref utility analyzes Referential Integrity Rules and builds load/unload/delete scripts in accordance with these rules. It also displays all the referential constraints and/or all the erroneous constraints in the following format: Foreign table.column Primary table.column Constraint Name --------------------------------------------------------------- f_tabname.f_colname p_tabname.p_colname constr_name . . . . . . Only the references causing "cycling" of the RI "forest" are listed. The algorithm used is not very fast, but easy for understanding and can be applied for ordering of any hierarchical structure. It is very convenient to use this utility during the development phase of the project, when the database architecture changes often (since INFORMIX doesn't check constraints for their logic correctness, improperly built references may result in errors during unloading and recovering procedures), and during the testing phase, when testers have to reload the test database due to errors in software logic in order to bring test database to the previous state, or populate the database with manually prepared testing data. Mr Walt Hultgren updated the dbref source; now the new version of the dbref is available on matchs.emory.edu (file /pub/informix/pub/dbref) I also would be glad to send you this, if you have no access to FTP. Sincerely, E-mail: pavel@cso.spb.su Pavel Kazenin ph/fax: 7-812-316-3324 PS. Below is a dbref manual page. ------------------------------------------------------------------------------- dbref (INFORMIX-OnLine) Release 2 Name dbref \\- Referential Integrity Analyzer Syntax dbref [-eloru] [-d dbname] [-t [tabname]] The meanings of the available options are: -e Creates file containing list of bad RI constraints (LoadDB.err) -l Creates database "load" script (LoadDB.sql) -o Redirects the list of RI constraints to file (LoadDB.lst) -r Creates database "delete" script (DeleteDB.sql) -u Creates database "unload" script (UnloadDB.sql) -d Investigates database `dbname' ($DBNAME by default) -t Displays list of all RI constraints for table `tabname' or for all tables if `tabname' omitted. Description dbref (DataBase REFerences analyzer) analyzes the Referential Integrity Rules. It reads them from the "informix".sysreferences and "informix".sysconstraints Informix System catalog tables. Then it tries to order the Referential Integrity "Forest". dbref generates INFORMIX SQL scripts needed to reload a database with Referential Constraints according to RI rules, outputs list of the existing RI constraints, and in case of RI violations creates list of the RI constraints causing these violations in the format: Foreign table.column Primary table.column Constraint Name --------------------------------------------------------------- f_tabname.f_colname p_tabname.p_colname constr_name . . . . . . Compatibility dbref utility was tested on the following equipment: Computer Model: IBM PS/2 Model 95A Operating System: SCO UNIX, release 3.0.0 Filesystems: AFS, EAFS Informix Engine: Informix On-Line v.5.01 Database Log Mode: ANSI Diagnostics Exit status is: 0, if no RI violations are found; 1, if Informix System Error occured ( no CONNECT perms, etc ); 2, if command line syntax errors; 3, if any RI violations are found; See also dbexport (INFORMIX), dbimport (INFORMIX), dbload (INFORMIX) Author Pavel Kazenin, <pavel@cso.spb.su>