On 07/14/2011 02:42 PM, Chris Travers wrote:
> On Thu, Jul 14, 2011 at 2:31 PM, John Locke <m...@freelock.com> wrote:
>> Chris Travers wrote:
>>> 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?
> Ok, so if we are dealing with a single database server, we could I
> suppose do a load, and then dump schema dump
> and then compare against a schema dump from production (filtering out
> set commands and comments)
Is that a built-in PG command?
> That won't give you everything.... For example menu changes...... But
> with menu changes, if you have any custom entries (which are often
> created by addons), you can't simply import the different records, so
> the update scripts have to be relied on there. And typically the way
> this works is to run the update scripts ona new database, then dump
> the tables and copy them back into the Pg-database.
>
Right. We still need the update scripts...
>
>> 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.
> I would REALLY like to avoid having multiple authoritative sources of
> the database schema if possible. It's very easy for those to get out
> of sync.
I'm not suggesting storing the schema in such a file, just a list of
tables in an order they can be created that will load successfully
(taking into account foreign keys, types, etc).
>> 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...
> If that's what we want., I think we'd want to automatically generate
> it from the SQL files in question rather than maintain it separately.
>
Yes, that's the whole point of having a separate file listing this stuff
-- so the base schema can be generated automatically. And re-generated
to easily compare against what it should be.
Essentially that file replaces Pg-database.sql. But we need to keep the
data out of it, because that data may get changed after it's already in
the database, making the schema dump inconsistent/not-comparable.
So I'm suggesting:
1. Schema index file, parsed by Perl/Bash/whatever
2. base schema sql file, auto-generated from a clean install by a script
that parses #1
3. base sql data file, to populate menu tables, etc.
>> 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.
> Also there is a second issue here......
>
> Column ordering is not guaranteed when alter table commands are used.
> So no matter how we do it, there will be false positives that have to
> be reviewed and dismissed.
Hmm. Is there any way in Postgres to define column ordering via an alter
statement? This is something MySQL supports ;-)
This might be an issue for developers, but for regular users, I think we
just need to make sure we don't change the update scripts after creating
them -- as long as people follow the upgrade procedures, wouldn't new
columns get added to the end of the table? Or does this vary by Pg version?
Cheers,
John Locke
http://www.freelock.com
------------------------------------------------------------------------------
AppSumo Presents a FREE Video for the SourceForge Community by Eric
Ries, the creator of the Lean Startup Methodology on "Lean Startup
Secrets Revealed." This video shows you how to validate your ideas,
optimize your ideas and identify your business strategy.
http://p.sf.net/sfu/appsumosfdev2dev
_______________________________________________
Ledger-smb-devel mailing list
Ledger-smb-devel@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/ledger-smb-devel
