Sqlite merging databases into one, with unique values, preserving foregin key relation

Question

There are two solutions provided (see links at the bottom), but both fail in what i'm trying to do:

1. Given tables (identical) structure in databases like for example this:

DB1

Fruit

--------------
| id | name  |
--------------
| 1  | Apple |
| 2  | Lemon |
| 3  | Kiwi  |
| 4  | Banana|
--------------

Juice

----------------
| id | name    |
----------------
| 1  | Juice A |
| 2  | Juice B |
----------------

Recipe (Junction Table)

----------------------------
| id | juice_id | fruit_id |
----------------------------
| 1  | 1        | 1        |
| 2  | 1        | 2        |
| 3  | 2        | 1        |
| 4  | 2        | 3        |
----------------------------

DB2

Fruit

---------------
| id | name   |
---------------
| 1  | Kiwi   |
| 2  | Lemon  |
| 3  | Apple  |
| 4  | Orange |
| 5  | Lime   |
---------------

Juice

----------------
| id | name    |
----------------
| 1  | Juice C |
| 2  | Juice D |
----------------

Recipe (Junction Table)

----------------------------
| id | juice_id | fruit_id |
----------------------------
| 1  | 1        | 1        |
| 2  | 1        | 3        |
| 3  | 2        | 2        |
| 4  | 2        | 4        |
----------------------------

2. convert them into this:

DB3

Fruit

|----------------|
| id | name      |
|----------------|
| 1  | Kiwi      |
| 2  | Lemon     |
| 3  | Apple     |
| 4  | Orange    |
| 5  | Lime      |
| 6  | Banana    |
------------------

Juice

|--------------|
| id | name    |
|--------------|
| 1  | Juice A |
| 2  | Juice B |
| 3  | Juice C |
| 4  | Juice D |
----------------

Recipe (Junction Table)

----------------------------
| id | juice_id | fruit_id |
----------------------------
| 1  | 1        | 3        |
| 2  | 1        | 2        |
| 3  | 2        | 3        |
| 4  | 2        | 1        |
++++++++++++++++++++++++++++
| 5  | 3        | 1        |
| 6  | 3        | 3        |
| 7  | 4        | 2        |
| 8  | 4        | 4        |
----------------------------

Please notice that fruit id's changed accordingly, preserving relations despite change in fruit ID. ID is primary integer autoincrementing key, and Recipe contains two foregin keys (plus own primary integer autoincrementing key).

3. only two proposed solutions are:

a)

$sqlite3 database1.db '.dump' >> tmp
$sqlite3 database2.db '.dump' >> tmp
$sqlite3 database3.db '.import tmp'
$ #sometimes sqlite3 database3.db < 'tmp' instead of last row

from 4, and simmilar questions, and:

b)

$ sqlite3 newdb
attach 'b.db' as toMerge;   
BEGIN; 
insert into newdb select * from toMerge.sometable; 
COMMIT;

Other answers ie. 1, 2, 3, 4, 5, 6, 7 just repeat same pattern. Perhaps 6 might be solution but i couldnt tell, the lenght of that query frightened me...

Moreover, as 1 states there is no gui compare & merge solution. At least besides 2011 sqlitecompare, which probably works in Windows anyway (i'm looking for Linux tool) Some opinions in 8 also confirm that hardly any userfriendly gui has merge option.

So, question is. How to merge sqlite DB in Linux, keeping relations, but not keeping duplicate values from tags / notes? I'd thought that keeping organized structure is the main reason for keeping things in database, but it seems I fail to understand, why should i keep two same fruits in one basket if combining from two baskets. My basket should contain unique fruits and recipies, I want to eat that kiwi :)

Answer 1

PRAGMA foreign_keys = on;

ATTACH DATABASE 'db1.sqlite' AS db1;

ATTACH DATABASE 'db2.sqlite' AS db2;

BEGIN;

CREATE TABLE Fruit      (
                          id            INTEGER PRIMARY KEY NOT NULL,
                          name          TEXT    UNIQUE ON CONFLICT IGNORE
                          )
                          ;

CREATE TABLE Juice      (
                          id            INTEGER PRIMARY KEY NOT NULL,
                          name          TEXT    UNIQUE ON CONFLICT IGNORE
                        )
                        ;

CREATE TABLE Recipe     (
                          id            INTEGER PRIMARY KEY NOT NULL,
                          juice_id      INTEGER NOT NULL,
                          fruit_id      INTEGER NOT NULL,
                          FOREIGN KEY   ( juice_id ) REFERENCES Juice ( id )
                                        ON UPDATE CASCADE
                                        ON DELETE CASCADE,
                          FOREIGN KEY   ( fruit_id ) REFERENCES Fruit ( id )
                                        ON UPDATE CASCADE
                                        ON DELETE CASCADE
                        )
                        ;


INSERT INTO Fruit  ( id, name )               SELECT id, name FROM db1.Fruit;
INSERT INTO Juice  ( id, name )               SELECT id, name FROM db1.Juice;
INSERT INTO Recipe ( id, juice_id, fruit_id ) SELECT id, juice_id, fruit_id FROM db1.Recipe;

INSERT INTO Fruit ( name ) SELECT name FROM db2.Fruit;
INSERT INTO Juice ( name ) SELECT name FROM db2.Juice;

CREATE TEMPORARY TABLE Recipe_tmp AS
                                    SELECT Juice.name AS j_name, Fruit.name AS f_name
                                      FROM db2.Recipe, db2.Fruit, db2.Juice
                                        WHERE db2.Recipe.juice_id = db2.Juice.id AND db2.Recipe.fruit_id = db2.Fruit.id
;

INSERT INTO Recipe ( juice_id, fruit_id ) SELECT j.id, f.id
                                            FROM Recipe_tmp AS r, Juice AS j, Fruit AS f
                                              WHERE r.j_name = j.name AND r.f_name = f.name
;


DROP TABLE Recipe_tmp;

COMMIT;

DETACH DATABASE db1;
DETACH DATABASE db2;