PgDumpLikeUtility

Posted by Karsten on gnumed-devel May 26, 2005

To write a pg_dump-like utility that creates an SQL script from a given identity.id value.

The idea:

Given a primary key of a table (say, identity) trawl the schema graph by following foreign key links stopping branching at A) already visited tables, B) circular dependancies (which really is the same as A) and C) predefined stop tables.

The use case:

We would want to be able to programmatically say: "Dump patient X in a way that can be replayed later in this or another GNUmed database" (given a matching schema, of course). This is useful so we can easily and safely migrate patient data across database bootstraps. It may be a useful foundation for migrating data on database upgrades.

Considerations:

When replaying this dump file into a database we cannot guarantee that the original primary keys are still unused. However, primary keys are not supposed to carry business meaning. Hence the dump file may be constructed in such a way that first a new row is created in the "root" table (eg identity) and the new "root" primary key is gotten from that and used in the process to write appropriate dependant insert queries. Likewise with further keyed dependancies ...

Oh, and:

- Preferably client_encoding unicode should be used to cleanly get across all text data. - The dump file should be runnable inside a transaction.

This does not have to be ready in time for 0.1 but must IMO exist when 0.1+x is released (eg I don't think 0.1+x should happen without it).