0

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. See here:

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

Is there a way where I can merge toMerge.moz_places into moz_places while rewriting any origin_id where the host is shared between both moz_origins tables?

The furthest I've gotten in understanding how to get there is these sets of commands:

SELECT url,origin_id FROM toMerge.moz_places WHERE origin_id NOT IN ( SELECT id FROM moz_origins);
SELECT host,id FROM toMerge.moz_origins WHERE host IN ( SELECT host FROM moz_origins);
SELECT A.[prefix],B.[host],A.[id] FROM moz_origins A INNER JOIN toMerge.moz_origins B ON A.host == B.host;
1
  • 1
    Do not post images of tables. Post the CREATE and INSERT statements as text.
    – sticky bit
    Feb 1, 2022 at 2:20

1 Answer 1

0

I ended up using Firefox History Merger (2.12.0 at the time of this post), and it merged my slightly older firefox database with my current one. I don't remember what my old firefox version was, but I am on Firefox v96.0.3 at the time of this post.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.