Some people (not me, I'm not knowledgeable about this at all) have a
separate table with information like 'database schema version'. Then the
update program reads this information and does the required updates
(including updating the 'database schema version' field).

In my ears this sounds too complex for 'nightly updates' if that means
'daily', you must specifically think through and tell your update
program not only what it should do to upgrade from version 1.3b to 1.4,
but also what it should do if upgrading directly from 1.3a, 1.2c etc (or
you could 'serialize' your updates).

If your changes are limited to adding fields, making unindexed character
fields longer, adding indexes or modifying one procedure, you could of
course do things a lot simpler. But for a general answer fitting all
sorts of changes, I doubt there is any answer that doesn't force you to
think through how your update should be done. In some cases, you would
just change some fields, in other cases the change could include
deleting procedures, renaming tables, creating procedures and tables,
moving data from the old table to the new and then delete the renamed table.

If your databases contain a limited amount of data, I guess your update
program could simply delete all stored procedures, triggers etc., rename
all tables, create your new database schema and copy from the renamed
tables. Though that is normally too time consuming if your database
contains gigabytes of data.

Set

myles@... wrote:

> 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