Transforming pg_dump output to be compatible with SQLite 3.x
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]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..
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.
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.
On Fri, Aug 29, 2014 at 9:06 AM, Kynn Jones <kynnjo@gmail.com> wrote:
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.
Read down in the man page for pg_dump. There are parameters such as
--inserts and --column-inserts which will help. And you might want
--quote-all-identifiers just in case some attribute (column name) is
an SQLite key word.
Example transcript:
pg_dump -c -t datedata -O -x --column-inserts --quote-all-identifiers
-d PostgreSQL_db | egrep -v '^SET '
--
-- PostgreSQL database dump
--
DROP TABLE "public"."datedata";
--
-- Name: datedata; Type: TABLE; Schema: public; Owner: -; Tablespace:
--
CREATE TABLE "datedata" (
"id" "text",
"date" "date",
"value" "text"
);
--
-- Data for Name: datedata; Type: TABLE DATA; Schema: public; Owner: -
--
INSERT INTO "datedata" ("id", "date", "value") VALUES ('a', '2000-01-01', 'x');
INSERT INTO "datedata" ("id", "date", "value") VALUES ('a', '2000-03-01', 'x');
INSERT INTO "datedata" ("id", "date", "value") VALUES ('b', '2000-11-11', 'w');
INSERT INTO "datedata" ("id", "date", "value") VALUES ('c', '2000-11-11', 'y');
INSERT INTO "datedata" ("id", "date", "value") VALUES ('c', '2000-10-01', 'y');
INSERT INTO "datedata" ("id", "date", "value") VALUES ('c', '2000-09-10', 'y');
INSERT INTO "datedata" ("id", "date", "value") VALUES ('c', '2000-12-12', 'z');
INSERT INTO "datedata" ("id", "date", "value") VALUES ('c', '2000-10-11', 'z');
INSERT INTO "datedata" ("id", "date", "value") VALUES ('d', '2000-11-11', 'w');
INSERT INTO "datedata" ("id", "date", "value") VALUES ('d', '2000-11-10', 'w');
--
-- PostgreSQL database dump complete
--
--
There is nothing more pleasant than traveling and meeting new people!
Genghis Khan
Maranatha! <><
John McKown
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On 08/29/2014 07:40 AM, John McKown wrote:
On Fri, Aug 29, 2014 at 9:06 AM, Kynn Jones <kynnjo@gmail.com> wrote:
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.Read down in the man page for pg_dump. There are parameters such as
--inserts and --column-inserts which will help. And you might want
--quote-all-identifiers just in case some attribute (column name) is
an SQLite key word.
Well I think the issue Kynn is referring to is Sqlites limited ability
to do ALTER TABLE. In a Postgres dump the basic structure of the table
is laid out using CREATE TABLE and then later ALTER TABLE commands are
used to finish adding the bells and whistles. Sqlite does not understand
those ALTER TABLE commands and fails on them. So to
get a dump to work you would need to create a complete CREATE TABLE
definition. The FOREIGN KEY ordering issue could be gotten around(I
believe) by toggling the foreign_keys PRAGMA in sqlite.
--
Adrian Klaver
adrian.klaver@aklaver.com
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
You're correct. It is Friday leading to a 3 day weekend here. And it
is a short work day too. So my brain has definitely already left the
building. Thanks for pointing that out. I use SQLite some, but just
for very basic stuff and am not really familiar with it. Perhaps Kynn
could show what, in particular, is causing his problem(s).
On Fri, Aug 29, 2014 at 9:58 AM, Adrian Klaver
<adrian.klaver@aklaver.com> wrote:
On 08/29/2014 07:40 AM, John McKown wrote:
On Fri, Aug 29, 2014 at 9:06 AM, Kynn Jones <kynnjo@gmail.com> wrote:
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.Read down in the man page for pg_dump. There are parameters such as
--inserts and --column-inserts which will help. And you might want
--quote-all-identifiers just in case some attribute (column name) is
an SQLite key word.Well I think the issue Kynn is referring to is Sqlites limited ability to do
ALTER TABLE. In a Postgres dump the basic structure of the table is laid out
using CREATE TABLE and then later ALTER TABLE commands are used to finish
adding the bells and whistles. Sqlite does not understand those ALTER TABLE
commands and fails on them. So to
get a dump to work you would need to create a complete CREATE TABLE
definition. The FOREIGN KEY ordering issue could be gotten around(I believe)
by toggling the foreign_keys PRAGMA in sqlite.--
Adrian Klaver
adrian.klaver@aklaver.com
--
There is nothing more pleasant than traveling and meeting new people!
Genghis Khan
Maranatha! <><
John McKown
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general