On 6/9/05, elein <elein@xxxxxxxxxxx> wrote: > On Thu, Jun 09, 2005 at 04:16:46PM -0500, John Browne wrote: > > How would you handle the migration of the data with these user > > scripts? Dump it to a temp table? > > > > If your scripts are correct, you should be able to load > your base scripts and apply each change script in order > and have the result be the exact same database schema. > > If they are not, checkpoint with a schema dump and start > again with the change scripts. Of course getting the > scripts wrong is against the point of the whole exercise, > but it is not easy and requires vigilance. > The big complexity for me is that the the database schema's state should be stored along with the code that uses it: i.e. in CVS or Subversion or whatever with the code. That way you have a consistent snapshot of your complete system database at any given point in time (minus the data itself). Developers will need to re-dump the schema whenever they make a change to the datbase and commit it along with everything else, but that's easily scriptable. Writing individual 'patch' scripts is fine for linear development, but breaks down when dealing with a development environment that involves branching. If two branches make changes to the database, each's patch file would be written against the original version, which may not be the case once the other patch has been apllied. What is needed is a tool which will compare any two revisions of the schema and generate a patch file that performs the migration. This would obviously have to be pretty damn clever. Amongs the difficulties would be ensuring that the patch applies changes in the correct order (e.g. add column before adding foreign key). It's hard, but I don't believe it's impossible. -- Russ ---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings
