Ok, here's a stake in the ground for #2, a new schema_version to insert
into the Defaults table, and a baseline schema version for each
module/*.sql file.
This strikes me as far more maintainable, especially if we continue to
have lots of sql changes.
Cheers,
--
John Locke
http://freelock.com
On 06/19/2011 09:07 AM, John Locke wrote:
> On 06/18/2011 04:23 PM, Chris Travers wrote:
>> On Sat, Jun 18, 2011 at 12:55 PM, Erik Huelsmann <..hidden..> wrote:
>>> Hi John,
>>>
>>> On Sat, Jun 18, 2011 at 8:10 PM, John Locke <..hidden..> wrote:
>>>> Hi,
>>>>
>>>> After a couple weeks away from bookkeeping, doing some catchup now. Just
>>>> pulled down the latest trunk, and merged them in.
>>>>
>>>> I see the new sql/upgrade directory, which strikes me as a great direction
>>>> to go. I like the naming of sql update scripts with svn id, and I'd like to
>>>> take it one step further: keep a value in the "defaults" table for the
>>>> latest db updates.
>>>>
>>>> I just did the following:
>>>>
>>>> INSERT INTO defaults VALUES ('schema_version', '3273');
>>>>
>>>> ... since that looks like the largest svn id with a change under sql.
>>>>
>>>> As the first part of scripting updates, it would be really great to have
>>>> each schema change dropped into an update sql script in sql/upgrade, with
>>>> the svn id in the file name. And part of the script would be updating the
>>>> schema_version in defaults (which should get initially set in
>>>> Pg-database.sql).
>>>>
>>>>
>>>> I just spent 2 hours applying schema updates... would be so much easier to
>>>> just import a few sql files. (granted, much of that was fixing underlying
>>>> data issues uncovered by adding unique keys...).
>>> Very nice idea. I wonder how to take this further: Chris seems to have
>>> started with that directory, but I have no idea how we should maintain
>>> it, or if there's anything we could do to make it easy or enforced to
>>> update the updates/ directory too.
>> My thinking is that any time DDL changes unless it is just the
>> internals of a function (where the sl file can be reloaded), we should
>> have a file to accommodate that change. Every biweekly release can
>> also have a psql script which loads changed function definitions since
>> last snapshot. I think the convention should be [svn
>> revi]_description.sql
> This much sounds good... but there's still the issue of knowing which
> sql files need to get reloaded. This is where I think we need a schema
> version, and some sort of convention for knowing when an sql file gets
> changed (other than timestamps/commit logs).
>
> I can think of two different reasonable approaches:
>
> 1. Parseable change log that can be used by an update function.
>
> 2. Index file of schema id of the last change to each sql file.
>
> First off, the schema version -- I propose we make it related to svn id,
> without requiring it to be exact. Committers will need to be careful
> when updating trunk to make sure they increase the number and include
> all changes -- which is where commit id makes sense. But in practice, in
> update scripts, I think the main thing to care about is whether the
> schema version is bigger than the one currently in the database.
>
> For approach 1, I would see writing update functions similar to Drupal,
> with the schema id in the name. The function could load any updated sql
> files, as well as insert new ones. Some controller function can parse
> the file, find all update functions bigger than the current schema file,
> and execute them.
>
> For approach 2, I see creating a hash of all the sql files, each with
> the most recent schema version containing a change to that file. Then
> our update function could just look for sql files with a bigger schema
> version and reload them.
>
> Thoughts?
>
>
> Cheers,
> John Locke
> http://freelock.com
>
> ------------------------------------------------------------------------------
> EditLive Enterprise is the world's most technically advanced content
> authoring tool. Experience the power of Track Changes, Inline Image
> Editing and ensure content is compliant with Accessibility Checking.
> http://p.sf.net/sfu/ephox-dev2dev
> _______________________________________________
> Ledger-smb-devel mailing list
> ..hidden..
> https://lists.sourceforge.net/lists/listinfo/ledger-smb-devel
Attachment:
schema_versions.pl
Description: Perl program