Greetings!
I'm looking for tools/resources/ideas for making pg_dump's output compatible with SQLite v. 3.1.3.
Ideally, I'd love to be able to do something like this (Unix):
% rm -f mydatabase.db
% pg_dump --no-owner --inserts mydatabase | pg_dump2sqlite3 | sqlite3 mydatabase.db
...where pg_dump2sqlite3 stands for some program (or pipeline) that transforms the output of pg_dump as needed so that sqlite3 can digest it.
Among the tasks that the hypothetical pg_dump2sqlite3 program has to carry, IMO the hardest one to implement is to compute the foreign-key dependencies among the tables, and from this compute the sequential order in which the tables will be created and populated[1].
Am I correct? Is there a way around this?
TIA!
kj
[1] In pg_dump's output, the sequential ordering of the CREATE TABLE statements and of the COPY blocks that respectively define and populate the tables does not take into account dependencies, because the specification of these dependencies comes after all the CREATE TABLE and COPY commands, in the form of ALTER TABLE statements. AFAIK, however, sqlite3 does not allow adding foreign key constraints after the table has been created. This means that both the ordering of table creation and population must respect the dependencies among the tables.