Combine tables with different foreign key values for same data (firefox)



  • 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;
    


  • I ended up using https://github.com/crazy-max/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.




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2