Russ Brown wrote:
> 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 is interesting... You'd want to be able to generate either a bunch
of CREATEs to create a schema from scratch or a 'patch' of ALTER
commands to move b/w arbitrary revisions or to a working copy (ie, a
live DB). This implies you need to store an intermediate (non-SQL)
representation in your repository (like the output of WBDiff mentioned
previously).
What's unusual is that your SQL-generating tool would need to checkout
*both* versions of the representations to generate the patch. I can't
think of any other problems that have this requirement - usually, you're
relying on your version control software to generate such diffs for you.
> 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.
As an uninformed, off-the-wall idea, could one compare snapshots of the
system tables to generate these diffs? I know next-to-nothing about
these, but it seems like they'd contain the info you'd need.
This still doesn't solve the problem of identifying which tables should
have their *data* included For that, I suppose one could create a table
for storing such metadata, or abuse COMMENT. It'd also be nice to be
able to specify the ability to exclude specified objects entirely - I'm
thinking of 3rd party modules (tsearch2, say) that one would want to
install by hand (to get the latest version or whatever).
I recently got a script to do something similar for MySQL by parsing the
output of 'show create table'. It's pretty simple & not full-featured.
I need to check with the author before posting it though.
If anyone is inclined to work on this, I might be able to route a few
dollars their way... please contact me off list.
--
Peter Fein pfein@xxxxxxxxx 773-575-0694
Basically, if you're not a utopianist, you're a schmuck. -J. Feldman
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
