On 6/9/05, elein <elein@varlena.com> 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