Re: Metadata versioning

From: Kyle A. Miller (kyle_at_millerdevelopmentXREMOVE.info)
Date: 10/23/03

  • Next message: Frederick C. Wilt: "Re: Borland, Let Delphi Live !" 
    Date: Thu, 23 Oct 2003 13:48:27 -0500

    Chris Clark wrote:
    > I would be interested in the way people handle upgrading metedata within
    > growing database applications as clients evolve with versions.
    >

    We have databases the user creates and uses like spread*** files. As
    time goes on, the structure inside these database files change. I use a
    centralized database (You can use any centralized data store you want.)
    to store the metadata version information. Each version of metadata is
    cataloged. Inside each database is a version label of that database's
    metadata version.

    I created a set of classes to completely describe the metadata or schema
    of a database. The classes supports all type of db objects and is
    multivendor aware. (Database vendor specific data, like vendor field
    types, comment begin, comment end, current timestamp, delimeters, etc.
    is stored in the central database.) When describing table relations, the
    table objects all know who is related to who in either direction.

    Given these classes, I can load them with either metadata, which is
    loaded from the metadata catalog tables, or schema information, the
    physical makeup of a database, by reading the database's system tables.
    (I read the system tables because not DB Express or any other db access
    component library I found can provide complete and accurate information
    about a database's makeup.)

    Here is where the cool part is (IMO). With these classes, metadata
    catalog tables, and schema reading routines, I can move between metadata
    versions, up or down, and the system will generate the appropriate delta
    sql for any supported DB vendor, currently Firebird/IB, MSSQL, and Oracle.

    So there you have it. Catalog your metadata versions in a data store,
    write classes which mimic the makeup of a database's structure, an
    almight metadata class, table objects with field objects, and on and on.
    Have routines to read your metadata catalog and fill instance of your
    metadata class. Having routines to read your database schema. You can
    then compare your two metadata instances and have them wisely generated
    a delta script.

    Oh, one more thing. I do not attempt to perform alter tables. If a table
    differs, I create a backup of the existing, drop the existing, create
    the new, and restore the backup data. If the update fails, re-create the
    old table and restore the data from backup. This method is preferred
    because not all database systems support the ALTER TABLE command at the
    same level. Firebird/IB seems to be the most advanced I've used. :)

  • Next message: Frederick C. Wilt: "Re: Borland, Let Delphi Live !"
    • Quantcast