Merge SQLite databases.
Budget: $30 – $250 USD
I am combining my history in firefox from two computers with the following commands:
ATTACH 'filename' AS toMerge
INSERT or IGNORE INTO moz_origins SELECT * FROM toMerge.moz_origins;
INSERT or IGNORE INTO moz_places SELECT * FROM toMerge.moz_places;
INSERT or IGNORE INTO moz_inputhistory SELECT * FROM toMerge.moz_inputhistory;
INSERT or IGNORE INTO moz_historyvisits SELECT * FROM toMerge.moz_historyvisits;
Below is the schemas for those four tables (from DB Browser). The databases are in sqlite.
CREATE TABLE moz_origins ( id INTEGER PRIMARY KEY, prefix TEXT NOT NULL, host TEXT NOT NULL, frecency INTEGER NOT NULL, UNIQUE (prefix, host) )
CREATE TABLE moz_places ( id INTEGER PRIMARY KEY, url LONGVARCHAR, title LONGVARCHAR, rev_host LONGVARCHAR, visit_count INTEGER DEFAULT 0, hidden INTEGER DEFAULT 0 NOT NULL, typed INTEGER DEFAULT 0 NOT NULL, favicon_id INTEGER, frecency INTEGER DEFAULT -1 NOT NULL, last_visit_date INTEGER , guid TEXT, foreign_count INTEGER DEFAULT 0 NOT NULL, url_hash INTEGER DEFAULT 0 NOT NULL, description TEXT, preview_image_url TEXT, origin_id INTEGER REFERENCES moz_origins(id))
CREATE TABLE moz_inputhistory ( place_id INTEGER NOT NULL, input LONGVARCHAR NOT NULL, use_count INTEGER, PRIMARY KEY (place_id, input))
CREATE TABLE moz_historyvisits ( id INTEGER PRIMARY KEY, from_visit INTEGER, place_id INTEGER, visit_date INTEGER, visit_type INTEGER, session INTEGER)
My problem is a Foreign Key constraint issue where moz_origins of the main table and table to merge contain the same host but have different id.
moz_origins:
id prefix host
-------------------------------
13 https:// www.youtube.com
756 https:// youtube.com
toMerge.moz_origins:
id prefix host
-------------------------------
1512 https:// www.youtube.com
5854 https:// youtube.com
My goal is to merge the moz_origins tables, then the moz_places tables.
Let me reiterate the core challenges:
- merge moz_places tables, which requires an associated host entry in moz_origins
- merge moz_places rows that have an existing host but unrelated origin_id (same host, different id)
- merge moz_places rows that have an existing id but unrelated host (same id, different host)
I have included two dbs that show the problem as it exists, but not all of the data.
ATTACH 'filename' AS toMerge
INSERT or IGNORE INTO moz_origins SELECT * FROM toMerge.moz_origins;
INSERT or IGNORE INTO moz_places SELECT * FROM toMerge.moz_places;
INSERT or IGNORE INTO moz_inputhistory SELECT * FROM toMerge.moz_inputhistory;
INSERT or IGNORE INTO moz_historyvisits SELECT * FROM toMerge.moz_historyvisits;
Below is the schemas for those four tables (from DB Browser). The databases are in sqlite.
CREATE TABLE moz_origins ( id INTEGER PRIMARY KEY, prefix TEXT NOT NULL, host TEXT NOT NULL, frecency INTEGER NOT NULL, UNIQUE (prefix, host) )
CREATE TABLE moz_places ( id INTEGER PRIMARY KEY, url LONGVARCHAR, title LONGVARCHAR, rev_host LONGVARCHAR, visit_count INTEGER DEFAULT 0, hidden INTEGER DEFAULT 0 NOT NULL, typed INTEGER DEFAULT 0 NOT NULL, favicon_id INTEGER, frecency INTEGER DEFAULT -1 NOT NULL, last_visit_date INTEGER , guid TEXT, foreign_count INTEGER DEFAULT 0 NOT NULL, url_hash INTEGER DEFAULT 0 NOT NULL, description TEXT, preview_image_url TEXT, origin_id INTEGER REFERENCES moz_origins(id))
CREATE TABLE moz_inputhistory ( place_id INTEGER NOT NULL, input LONGVARCHAR NOT NULL, use_count INTEGER, PRIMARY KEY (place_id, input))
CREATE TABLE moz_historyvisits ( id INTEGER PRIMARY KEY, from_visit INTEGER, place_id INTEGER, visit_date INTEGER, visit_type INTEGER, session INTEGER)
My problem is a Foreign Key constraint issue where moz_origins of the main table and table to merge contain the same host but have different id.
moz_origins:
id prefix host
-------------------------------
13 https:// www.youtube.com
756 https:// youtube.com
toMerge.moz_origins:
id prefix host
-------------------------------
1512 https:// www.youtube.com
5854 https:// youtube.com
My goal is to merge the moz_origins tables, then the moz_places tables.
Let me reiterate the core challenges:
- merge moz_places tables, which requires an associated host entry in moz_origins
- merge moz_places rows that have an existing host but unrelated origin_id (same host, different id)
- merge moz_places rows that have an existing id but unrelated host (same id, different host)
I have included two dbs that show the problem as it exists, but not all of the data.