Re: Database updates

[John Locke <..hidden..>]

Hi,


On 07/13/2011 03:15 PM, Chris Travers wrote:
> Ok, two pieces of quick feedback.
>
> First right now I would highly recommend that if you have to follow
> schema changes you join the ledger-smb-commits list on sourceforge.
> That will help a lot right there.

Well, with git I'm not having any trouble seeing what's changed. What I do:

1. create a tag of my current local branch
2. merge changes from upstream
3. git diff <tagname> sql/ and get all the schema changes.

Problem is, there have been quite a few! And so I've just been relying
on the upgrade scripts -- but some of these have failed, and I don't
think they're complete.

> Secondly, a tool to compare schemas will be helpful, and I would like
> to create one in the near future.  in the mean time, as a simple
> discussion draft, let me post the following bash code.
>
> rm ../testdump1.sql ../testdump2.sql
> for line in `grep -i "^create table" sql/Pg-database.sql | sed -e
> 's/(.*$//' | awk '{ print $3 }'`
>     do pg_dump -U postgres -s -t $line database1 | grep -v GRANT |
> grep -v REVOKE >> ../testdump1.sql
> done
>
> for line in `grep -i "^create table" sql/Pg-database.sql | sed -e
> 's/(.*$//' | awk '{ print $3 }'`
>     do pg_dump -U postgres -s -t $line database2 | grep -v GRANT |
> grep -v REVOKE >> ../testdump2.sql
> done
>
> diff ../testdump1.sql ../testdump2.sql
>
> If this sort of result is found to be acceptable, I could probably
> port the first two parts to Perl pretty easily.  Not sure about diffs
> though.

That sounds like a good start.

I'm thinking, though, that Pg-database.sql is still too big and
monolithic for this -- and the resulting dump file ought to be where you
do the initial schema load, don't you think?

Maybe have a separate file, sort of like an ini file or parsable
"loadorder" that can identify the type of relation and the name. e.g.

table acc_trans
table account
type xxxx_xx_xx
view chart

... etc.

This could then be fed to the dump script, instead of parsing
Pg-database.sql...

Then we can have the tables ordered in a way that will actually load
with foreign keys to tables previously defined, etc.

And as long as that's consistent, then I think diff is fine for now for
seeing what's changed... can just run the dump script, direct the output
to a tmp file, and compare against the one in the code tree.


Cheers,
John Locke
http://www.freelock.com