Re: Database Schema SCCS?

>I'm looking for some software, either commercial or something someone has
>written, that will allow me to keep track of database schemas, and the
>changes made to the schema (add this table, delete this field, change this
>field from smallint to integer, etc) and allow me to version them.
>
>I'm looking for something more than just flat files under SCCS. Something
>more akin to a Data Dictionary that is maintained in the DBMS, along with
>descriptions of fields for documentation, and can allow me to say, "I want
>Ver 1.6 of the database for Customer X, and 2.6 for Customer Y."

I have come up with my own scheme for maintaining the history of changes to my database. It uses flat files (SQL Script files) and RCS. RCS was my choice for software configuration management tool. SCCS will work just as well. My scheme is called TCM (Table Configuration Management). I should also mention that at this point in time, TCM is still in its infancy and most operations still are run manually. TCM was also designed use with ORACLE databases.

Before I explain TCM I would like to go over the requirements that it needed to meet. First, TCM must be able to rebuild the database structure (tables, views, constraints, etc but not data). Also, we keep several developmental versions of the database. This means that we needed to be able to build older versions of the database. So, basically, I was mainly concerned with being able to rebuild any version of the database. This is slightly different from being able to track changes in the database structure, though that information is available with a little work from TCM.

TCM consists of seven different SQL Script files. Each RDB object (table, view and cluster) will have its own set of these seven files. Not all of the seven types of files are required for each RDB object and only the TCM Files needed exists. The seven types of TCM files are:

1. Create Cluster File: Contains `CREATE CLUSTER ...' and `CREATE INDEX ... ON CLUSTER ...' Statements.
2. Create Table File: Contains `CREATE TABLE ...', `CREATE UNIQUE INDEX ...' on primary key only, and `GRANT SELECT ... TO PUBLIC ...' statements. The Create Table statement contains the primary key clause but no foreign key clauses.
3. Create View File: Contains `CREATE VIEW ...' and `GRANT SELECT ... TO PUBLIC ...' statements.
4. DBA File: Contains `CREATE PUBLIC SYNONYM ...' statements. This is the only SQL script that must be run from a DBA account in ORACLE.
5. Load File: Contains `INSERT INTO ...' statements for tables that have fixed data. For example, if a table exists to define a foreign key, its data rarely changes so its data can be considered as constant.
6. Constraint File: Contains `ALTER TABLE ... ADD (FOREIGN KEY ... REFERENCES ...)', `ALTER TABLE ... ADD (CHECK ...)' and `ALTER TABLE ... ADD (UNIQUE ...)' (for non-primary key columns) statements. Only the alter statements that reference another table must be in this file. The other are here instead of being in the CREATE table file so that the Load File will run faster.
7. Index File: Contains `CREATE INDEX ...' statements.

Now, to use TCM, you maintain these SQL script files for each RDB object using your favorite SCM package (RCS, SCCS, CMS, ...). RCS allows you to apply symbolic names across a set of RCS Files. This allows you to extract all the TCM files associated with a common release of the database.

To rebuild the database or build a new copy, you run TCM SQL Script files in groups based on type such as Create Table files. The groups of TCM files are run in the order listed above. The ordering insures that all the necessary RDB objects are in place before they are used. For example, you need to run the Create Table scripts before you run the Create Views or the Constraint scripts. And the Create Cluster scripts before running the Create Tables scripts.

I also used a specific naming convention for the TCM script files to make life easier. The first three letters in the file names are coded so that the first letter represents the logical database, the second letter represents a functionally related group of tables in that logical database and the third letter denoted the TCM file type. The rest of the name represents the name of the object. This naming convention allows for easy extractions of the TCM Files (from RCS) by logical database, functional table group, TCM File type and/or object.

+--------------------------------------------------------------------------+
| Steven F. LeBrun             |      MIT Lincoln Laboratory, Group 91     |
|                              |        244 Wood Street, Room LI-136       |
| lebrun_at_ll.mit.edu (Internet) |          Lexington, MA 02173-9108         |
| lebruns_at_rigel     (internal) |  Phone:(617)-981-5742, FAX:(617)-981-0991 |
 +--------------------------------------------------------------------------+
|             "I am not a number, I am a free man!" -- No. 6               |
+--------------------------------------------------------------------------+