From: lebrun@ll.mit.edu (Steven F. LeBrun)
Subject: Re: Database Schema SCCS?
Keywords: Database
Organization: MIT Lincoln Laboratory, Lexington, MA
Date: Thu, 16 Apr 92 11:47:12 GMT


>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@ll.mit.edu (Internet) |          Lexington, MA 02173-9108         |
| lebruns@rigel     (internal) |  Phone:(617)-981-5742, FAX:(617)-981-0991 |
+--------------------------------------------------------------------------+
|             "I am not a number, I am a free man!" -- No. 6               |
+--------------------------------------------------------------------------+