Myles,

Congratulations, you have just discovered the tedious part of database
application development ;-)

This is how I do it (Your Mileage May Vary):

- Use use an ERM tool for data modelling (Datanamic DeZign in my case)
These tools will provide you with an SQL script to create a new,
empty database.

- When I make changes to my data model, I maintain an "update script",
which does all the necessary DDL and DML changes. This can be
everything from a few lines to a few kilobtyes, whatever it needs to
upgrade from the current to the new schema.

The new version of DeZign helps me, because it is also able to
produce a "delta" script. I also use the database comparer tool in
IbExpert to see if I didn't forget anything.
(As you said, this is about technique, not about tools, so you may
of course replace these tools with whatever you like)

I maintain a Schema Version (SV) number in my database. This is just
an integer that's incremented everytime there is a DDL update, no
matter how small or large it is. This has several advantages:

- By looking at the SV, I know the schema and can tell what must be
done to upgrade

- My application can decide if it wants to run again that schema. If
not, it refuses to do anything else with the database than reading the
SV.

- Upgrading from, say, SV9 to SV11 can be done by applying the
SV9-SV10 schema update first and then the SV10-SV11 update.

I store all files and old test/development databases from all Schema
Versions so I can support customers that have older versions than the
current one.

I also consider my client application to have a "schema version".
That's the Schema Version that the client knows about and is working
with.

There is also a MINIMUM_CLIENT_SCHEMA_VERSION in the database. Clients
that have a lower schema version MUST NOT connect to such a database
(i.e. they must disconnect immediately after reading the SV.)

HTH

Best Regards

Stefan

> I recently posted about this question and was directed to a number of tool
> products that might help, but I have a feeling that its more about technique
> than tools.

> I have a database that is being developed on a Linux development server.
> Separate to this, I have a Linux test server where users are testing the PHP
> application that works with the database. I'm attempting to do nightly
> updates of the test application & database schema changes to coincide with
> test reports coming back from my testers.

> I can update the PHP application pages with no issue. However since my
> users have created a lot of test data, I need to be able to update the
> database schema without affecting the data in the tables. I'm not an expert
> in SQL databases and how to do this, but I believe that I can do it with
> ALTER commands, etc. The problem is that I have 20+ tables in this
> database, and about 100 stored procedures, so all aspects of the database
> could change between updates.

> What is the best way to update the 'delta' between databases, but protect
> the data stored in the tables from being deleted out?

> Myles

--
Stefan Heymann
www.destructor.de/firebird