- Request #143 (P. Friberg):
----------------------------

        -----------------------------------------------------------------
        --> This is not a schema request per-se, but a request to VERSION
	    the schema some how ...

	Stored procs are downloaded by RSNs and loaded blindly and regularly
	but as the CISN schema evolves, if the stored procs get out of sync
	with the schema, bad things can happen.

	Maybe versioning can take place via SVN?
        -----------------------------------------------------------------


- Ideas exchanged via email:
----------------------------

* From Stephane:
-----------------------------------------------------------------
My proposal is to add two new tables to the schema:

* Schema_Version:
    version    STRING;
    installed  DATE;

This table tracks when a respective version of a schema has
been installed on a given database user.

* StoredProc_Comp
    sp_name     STRING;
    sp_version  STRING;
    sc_minver   STRING;
    sc_maxver   STRING;

This table defines for a given package (stored procedures) and
its version, the minimum and maximum schema versions that it is
compatible with. When a user installs/updates a package, the package
should check those 2 tables and make sure that the latest version
of the schema installed (Schema_Version table) is within the minimum
and maximum version specified in the StoredProc_Comp table.

This is a preliminary proposal and does not address many issues such
as who/what will populate/maintain those tables.
-----------------------------------------------------------------

* From Paul:
-----------------------------------------------------------------
I think the proposed table for schema_version would do the job nicely.

Not sure if we need the stored procedure version table as Allan introduced a
function called getPkgId() to every package that returns the SVN version
string of the stored proc.

    FUNCTION getPkgId RETURN VARCHAR2 AS
    BEGIN
      RETURN v_pkgId;
    END getPkgId;

where in the case of the wavefile.sql I see this on a recent checkout:

  v_pkgId VARCHAR2(64) := '$Id: wavefile_pkg.sql 6547 2013-03-13 22:06:26Z eyu $';


So the code in this case documents which version it is. 
-----------------------------------------------------------------

* From Pete:
-----------------------------------------------------------------
It's not obvious to me how anyone is going to fill in the proposed
Schema_version table.

When AQMS database tables are first created, it is normally done with the DDL
scripts provided in SVN. At that point it is pretty clear what the version
number would be for each table.

But once a table is in use, any newer versions of the DDL scripts do not get
applied directly to the database. For example, if a column gets added to a
table, you aren't going to do "CREATE OR REPLACE" on a table with existing
data. Instead, you use some complicated bunch to DDL commands to add column,
perhaps creating temporary tables on the way, etc. The table may end up with
columns in different order that specified in the DDL, or with different
constraints. Now it is no longer obvious what "version number" applies to the
modified table.

It seems to be that it is going to require a person to do the hard work of
running "describe table" on each AQMS table and then comparing the results
with the various known versions of the DDL scripts. That sounds like a
horrible job and one that is nearly certain to have errors.
---
Maybe we should step back and look at the problem you are trying to solve: how
can you be sure the database table structure meets the requirements of SQL or
java store procedures you want to install/upgrade; and how can you be sure the
table structure and installed stored procedures meet the requirements of the
applications you want to install or upgrade?

It seems to me that we could write some SQL queries that can make these tests
on table structure and stored procedures.
-----------------------------------------------------------------

* From Paul:
-----------------------------------------------------------------
One way to fill the schema_version table is that anyone that modifies the schema has to do 3 things:

    1. writes the new SQL modification from the prior version (as well as a any base creation changes)
    2. includes a version bump from the last one in the above SQL
    3. documents what the version bump change did...

Of course none of this will be doable till we start, and call the current version something....anything
....and yes that will require some basic table by table checking. Ellen has a script though.

I don't think the database creation sql in SVN has been as rigorously updated as it could be....but if
we follow a basic protocol I think we could easily make it a rote procedure.... 
-----------------------------------------------------------------

* From Ellen:
-----------------------------------------------------------------
Ok, how about this for an idea for version checking

1. We take all the create scripts in SVN (we assume that what is in SVN is current) --
and we run them against an empty database.

2. We re-create the scripts querying the database data dictionary. - Perhaps we check
these back into SVN as our first version.  (Btw, could we develop a tagging procedure --
I think this might help) -- the create script will only list table, columns, data types, and constraints

3. We create a schema checker script that will run against the target database (the one we are checking).
We do is run the same script from step 2 against the target database -- this will produce create scripts
of objects in the target database.

4. We diff the target create scripts with the SVN create scripts. 

OR for a more fine-honed option of step 4:

We query the dependencies views (user_dependencies for example as the stored procedure owner) -- and find
out all the objects used by that package and then we only compare those objects as opposed to the entire
schema. I would be more in favor of this option as I think it is the most practical.  

Problems:

Diff test will produce "false positives" if 
1. table columns are in different order
2. constraints have different names even though they have same conditions
3. some constraints are missing
-----------------------------------------------------------------

-----------------------------------------------------------------
==> Approved.
    A file changes.log in /DB (SVN repository) will be updated whenever
    a database object (table or stored proc) is updated or added.
-----------------------------------------------------------------