Some notes in case any one else decides to take this path -
dumped sqlite3 using -
sqlite3 ~/.prodigy/prodigy.db .dump > prodigy_sqlite3.dump
edit prodigy_sqlite3.dump and remove any create statements, and begin\commit.
regex replace —
:s/\,X\'/\,E\'\\\\x/g
to convert blob (bit) to bytea
sudo su - postgresuser
createdb prodigy
psql prodigy
CREATE TABLE dataset (
id integer NOT NULL,
name character varying(255) NOT NULL,
created integer NOT NULL,
meta bytea NOT NULL,
session integer NOT NULL
);
ALTER TABLE ONLY dataset
ADD CONSTRAINT dataset_pkey PRIMARY KEY (id);
CREATE TABLE example (
id integer NOT NULL,
input_hash bigint NOT NULL,
task_hash bigint NOT NULL,
content bytea NOT NULL
);
ALTER TABLE ONLY example
ADD CONSTRAINT example_pkey PRIMARY KEY (id);
CREATE TABLE public.link (
id integer NOT NULL,
example_id integer NOT NULL,
dataset_id integer NOT NULL
);
ALTER TABLE ONLY link
ADD CONSTRAINT link_pkey PRIMARY KEY (id);
ALTER TABLE ONLY link
ADD CONSTRAINT link_dataset_id_fkey FOREIGN KEY (dataset_id) REFERENCES dataset(id);
ALTER TABLE ONLY link
ADD CONSTRAINT link_example_id_fkey FOREIGN KEY (example_id) REFERENCES example(id);
\q
cat prodigy_sqlite3.dump | psql prodigy
ALTER TABLE dataset ALTER COLUMN session DROP DEFAULT;
ALTER TABLE dataset ALTER session TYPE bool USING CASE WHEN session=0 THEN FALSE ELSE TRUE END;
ALTER TABLE dataset ALTER COLUMN session SET DEFAULT FALSE;
CREATE SEQUENCE dataset_id_seq;
ALTER TABLE dataset ALTER id SET DEFAULT NEXTVAL('dataset_id_seq');
CREATE SEQUENCE example_id_seq;
ALTER TABLE example ALTER id SET DEFAULT NEXTVAL('example_id_seq');
CREATE SEQUENCE link_id_seq;
ALTER TABLE link ALTER id SET DEFAULT NEXTVAL('link_id_seq');
SELECT id FROM (each table - dataset, example, link) ORDER BY id DESC limit 1;
ALTER SEQUENCE (each sequence - dataset, example, link) RESTART WITH (id from above + 1);
whew…prodigy started - no warranty implied - your mileage may vary.
