We use PowerDesigner to manage the schema. The CDM and PDM Files are in a classical CVS Repository.

Each Procedures, Function and View is stored a SQL Script.

Changes to the Database are always done by a update script. This scipt uses the read statement to load the scripts and updates the schema. Complex DDL (aka add fields, new tables ) is also scripted in the update file. Each update script is intended to update a database from version x to y. If you have a Database Version v you have to run update w, x and y to get to the current version.

All sql scripts are hold in a CVS Repository.

When we release a new version of the client Application we tag the Scripts and the PD Files in the CVS with the client version they are intended for.

Update_x_zzz.sql

-- --------------------------------------------------------------
-- Update.sql
-- Updates the database from x.yyy to x.zzz
-- $Id$
-- --------------------------------------------------------------

SETTING_CheckSchemaVersion('x.yyy');

-- ==============================================================
message 'DDL Changes' type info to client;
-- ==============================================================

-- ==============================================================
message 'TRIGGER' type info to client;
-- ==============================================================

-- ==============================================================
message 'STORED PROCEDURES' type info to client;
-- ==============================================================

-- ==============================================================
message 'FUNCTIONS' type info to client;
-- ==============================================================

-- ==============================================================
message 'User-defined error messages' type info to client;
-- ==============================================================

read "..\reload\UserDefinedError.sql";

-- ==============================================================
message 'User-defined views' type info to client;
-- ==============================================================

-- ==============================================================
-- Update schema version in the SETTING table
-- ==============================================================
insert into SETTING (SET_VARNAME, SET_VALUE, SET_TYPE)
on existing update
values ('SCHEMA_VERSION', 'x.zzz', 'S');

commit;

-- ==============================================================
message 'Data Changes' type info to client;
-- ==============================================================

commit;

The function SETTING_CheckSchemaVersion raises an error when the version in the Database is not equal to what we expect.

We write the DDL manually based on the PDM but you can also use the DDL Script Power Designer can generate and embed them by read "pdchanges.sql"

We use PowerDesigner too (PDM). For every build of our software there is a corresponding physical model, checked in using SourceSafe. We let PowerDesigner create differential DDL scripts to modify databases according to the changed model.

For updates we bundle the necessary scripts with a master script, that READs them im chronological order and checks against a column in a version table if it's ok to apply the changes.

For our new product line though we took another approach: all schema information is defined by meta data within the framework. Version informations including the data model are stored for each client version. For updates a wizard automatically creates all SQL statements for the target DBMS to update the client's db to the current model and generates an installer.

Since we develop 'package' software to be deployed to multiple sites, we implement schema changes via a script file to insure it is easily repeatable; the last line in that script updates a schema version setting (a particular row in a "settings" table).

We maintain an "expected schema version number" in our app and check that against the "schema version" in the db at app startup.

What program are you using to make the schema changes? DO you have a source control provider you are using with Delphi code?

We use PowerDesigner first off. Unfortunately, we don't have the Repository feature, so we handle the 'versioning' with good'ole Copy+Paste of the PDM file. We then have our application version stored in the database, and then when the application connects, it compares.

It has its faults, but it works out pretty well in the end.