Re: Updating 1.3 RFC and small bugs

[John Locke <..hidden..>]

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