Karsten Hilbert wrote:
> On Fri, Jun 10, 2005 at 10:38:52AM -0500, Peter Fein wrote:
>
>
>>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).
>
>
> I would think you would keep the CREATE scripts version
> controlled just as you do with your source code. It's text,
> after all that evolves linearly. You would tag them
> appropriately, say -1.0, -1.1, whatever. From those you can
> always generate two databases of version x and version x+y
> where X is the version in deployment and X+Y the schema
> version you want to migrate deployment to.
>
> From *there* one would want to run a tool
>
> create_migration_script.sh --from=db1 --to=db2
>
> which produces a patch script with appropriate ALTER
> statements.
>
> I *don't* think the patch scripts need to be kept in the
> versioning system.
I wasn't suggesting that they should be - quite the opposite in fact. It
seems cleaner to generate the patch from an intermediate representation
(which is what would be stored), rather than having to instantiate a DB
to create the patch, which is what you seem to be suggesting. You'd
also be able to transform the intermediate rep. into SQL to create a DB
from scratch.
See also the posts about distinguishing ALTER from DROP/CREATE for
columns for problems with storing plain CREATE scripts.
Come on, we can't be the first people with this problem? Version
control's been around for what, 20, 30 years? Somebody must have tried
to do this before, even if it's with something other than Postgres...
--
Peter Fein pfein@pobox.com 773-575-0694
Basically, if you're not a utopianist, you're a schmuck. -J. Feldman