On Fri, Jul 15, 2011 at 10:03 AM, John Locke <m...@freelock.com> wrote:
>> 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? Yeah. > > 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). <snip> > 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. So how would this work exactly? Are you thinking one DDL file per table? I guess I am wondering if this would create more maintenance problems than it would solve. I mean you have to store the DDL somewhere, or you have to generate it from some other format which is semantically reducible to the SQL DDL as supported by PostgreSQL. Quite frankly if we are going that route, we should be using SQL DDL instead. > > Hmm. Is there any way in Postgres to define column ordering via an alter > statement? This is something MySQL supports ;-) Nope: http://wiki.postgresql.org/wiki/Alter_column_position Apparently nobody wants this badly enough to actually do the work required. > > 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? Do we want to be limited to always adding new fields to the end of the table? I would rather have the base DDL files focused on clarity for developers if given the choice because it means fewer errors and omissions. Another issue that we'd run into has to do with self-joins. In general the backup/restore processes from PostgreSQL are only guaranteed to work in the event you dump both the schema and data together in one file. This is an issue for tables like menu_node. It's certainly possible to get around this problem (by dropping the fkey, loading the data, recreating it) but it means additional effort and complexity and I think we'd have to weigh the benefit of doing so against the benefit of using well tested tools to dump data in a reloadable format rather than maintaining that by hand. So I guess I think we should start from a few more basic questions: Now that the menus should be pretty stable for the duration of 1.3 (absent bugs which you'd probably want flagged), what would you expect such an output to look like that you can't get from a diff on various versions of Pg-database? I mean, what specifically is missing? Table names? Something else? Or is it a matter of noise from comparing dumps? Best Wishes, Chris Travers ------------------------------------------------------------------------------ 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
