Sqlite将数据库合并为一个，具有唯一的值，保留了前缀关键字 [英] Sqlite merging databases into one, with unique values, preserving foregin key relation
问题描述
提供了两种解决方案（请参阅底部的链接），但两者在我想要执行的操作失败：
There are two solutions provided (see links at the bottom), but both fail in what i'm trying to do:
--------------
| id | name |
--------------
| 1 | Apple |
| 2 | Lemon |
| 3 | Kiwi |
| 4 | Banana|
--------------
果汁
b $ b
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
水果
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。将它们转换为：
DB3
水果
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 |
----------------------------
请注意，水果ID相应地改变，保持关系，尽管水果ID的变化。 ID是主整数自动递增键，Recipe包含两个前键（加上自己的主整数自动递增键）。
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).
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
从 4 和类似问题，以及：
b）
$ sqlite3 newdb
attach 'b.db' as toMerge;
BEGIN;
insert into newdb select * from toMerge.sometable;
COMMIT;
1 ， 2 ， 3 ，4 ， 5 ， 6 ， 7 只是重复同样的模式。也许 6 < a>可能是解决方案，但我不能告诉，该查询的长度惊吓了我...
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...
此外，作为 1 表示没有gui比较&合并解决方案。至少除了2011 sqlitecompare，这可能在Windows反正（我在寻找Linux工具）
在 8 也确认几乎没有任何用户友好的gui有合并选项。
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.
所以，问题是。
如何在Linux中合并sqlite数据库，保持关系，但不保留标签/注释的重复值？我认为保持有组织的结构是保持数据库中的东西的主要原因，但似乎我不明白，为什么我应该保持两个相同的水果在一个篮子，如果从两个篮子结合。我的篮子应该包含独特的水果和食谱，我想吃那个猕猴桃：）
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 :)
推荐答案
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;
这篇关于Sqlite将数据库合并为一个，具有唯一的值，保留了前缀关键字的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
