One thing I'm curious about is how people doing incremental development on "live" sites handle changes in their database schemas.

The code behind this site sees semi-regular updates to the database tables which are used, slowly working towards a better structure than the historically bad one.

This means that code is updated, tested locally, then pushed out to the live site.

There is no facility for showing differences in the tables, etc. Just a static dump which is updated and stored in CVS - which is useful for a fresh installation, but not for people upgrading from older copies of the code.

Generally my development looks like this:

• Plan the development of a new feature.
• Make the required database changes, using "alter table", et al.
• Write a test case or two.
• Write the code.
• Commit the code + test case(s).
• Test.
• Update the live site from the CVS repository, manually running the upgrade SQL after running "cvs upgrade", flushing the memcache, and reloading Apache if there have been any new rewrite rules installed.

This works for me because I control both the test environment, the CVS repository, and the live site.

But for anybody else running the code they'll going to have to reverse engineer any upgrade statements from the static table dumps. Which may be non-trivial.

For example the upgrade I'm going to run tomorrow will look like this:

# give each user an ID.
ALTER TABLE users DROP PRIMARY KEY;
ALTER TABLE users ADD id int(11) NOT NULL AUTO_INCREMENT,  
  ADD PRIMARY KEY (id);

# Add the key on username, just because it is still heavily used.
ALTER TABLE users ADD KEY (`username`);

# create a generic preferences table.  Inefficient, but extensible.
CREATE TABLE `preferences` 
(
`id`        int(11) NOT NULL auto_increment,
 user_id    int(11),
 pref_name  varchar( 25 )  default '',
 pref_value varchar( 125 ) default '',
 PRIMARY KEY  (`id`),
 KEY( `user_id` )  ,
 KEY( `pref_name` )  
);

# migrate users stylesheet preferences.
insert into preferences (user_id,pref_name,pref_value)
   (SELECT id,"stylesheet", stylesheet from users WHERE stylesheet != '');
alter table users drop stylesheet;

What does that do? It:

• Adds a new "ID" column to the users table.
• Creates a "preferences" table which will be keyed on the new user id column.
• Inserts the current stylesheet setting for each user who is using a custom CSS file
• Removes the "stylesheet" column from the users table - where it should never have lived.

Why do I need the preferences table? So that I can store the users chosen text formatting option. Something I've been working on for a day or two. It is also the best place to store peoples advertising preferences, display options, etc.

I could imagine storing a database schema-version number somewhere and a collection of upgrade scripts - like Ruby on Rails does, but I wonder if there are other approaches I should investigate?

Any tips or pointers welcome.