Merging tables with diverging IDs

It is common to create numeric identifiers for records (rows) when inserting them in a table.

Merging two versions of a table modified in this way requires special attention, to avoid duplicate identifiers.

An example

Consider an Sqlite database directory.sqlite with the following schema:

 CREATE TABLE locations (id INTEGER PRIMARY KEY,street,city);
 CREATE TABLE org2loc (org_id INTEGER,loc_id INTEGER,
   FOREIGN KEY(org_id) REFERENCES org2loc(org_id),
   FOREIGN KEY(loc_id) REFERENCES locations(id));
 CREATE TABLE organizations (id INTEGER PRIMARY KEY,name);

That's a table listing locations, a table listing organizations, and a table linking organizations and locations (org2loc). Suppose the database holds the following data:

locations

id	street	city
1	305 Memorial Drive	Cambridge
2	Big Crater	Moon

org2loc

org_id	loc_id
1	2
2	1
3	1
3	2

organizations

id	name
1	Dracula Inc
2	The Firm
3	Omni Cooperative
4	Nonexistence Unlimited

See Getting the material used in this example for how to get a copy of this database to play with. This toy database contains organizations and locations, where each organization may have zero, one, or many locations. The link between organizations and locations is set up in the org2loc table. For example, "The Firm" is located in Cambridge, "Omni Cooperative" is in Cambridge and on the Moon, and "Nonexistence Unlimited" is not linked to any location.

Divergence

Suppose Alice and Bob both have a copy of directory.sqlite. Alice adds in a new organization to her copy, "Alice and Company", with a location in Denver:

locations

@@	id	street	city
	1	305 Memorial Drive	Cambridge
	2	Big Crater	Moon
+++	3	10 Ten Street	Denver

org2loc

@@	org_id	loc_id
	1	2
	2	1
	3	1
	3	2
+++	5	3

organizations

@@	id	name
	1	Dracula Inc
	2	The Firm
	3	Omni Cooperative
	4	Nonexistence Unlimited
+++	5	Alice and Company

Bob, in his copy, adds "Bob's World" located on Planet Bob and in Cambridge, and notes that "Omni Cooperative" also has a branch on Planet Bob:

locations

@@	id	street	city
	1	305 Memorial Drive	Cambridge
	2	Big Crater	Moon
+++	3	42 The Boblands	Planet Bob

org2loc

@@	org_id	loc_id
	1	2
	2	1
	3	1
	3	2
+++	3	3
+++	5	1
+++	5	3

organizations

@@	id	name
	1	Dracula Inc
	2	The Firm
	3	Omni Cooperative
	4	Nonexistence Unlimited
+++	5	Bob's World

Bob then sends a "diff" bob.tdiff to Alice with his changes, either in the highlighter format shown above, or as a tdiff file:

@@@ locations

+ |id:->3|street:->42 The Boblands|city:->Planet Bob|

@@@ org2loc

@ |org_id|loc_id|
+ |3|3|
+ |5|1|
+ |5|3|

@@@ organizations

+ |id:->5|name:->'Bob''s World'|

(produced with ssdiff --output bob.tdiff directory.sqlite directory_bob.sqlite).

Convergence

A naive application of Bob's changes to Alice's version of the database would result in garbage, due to conflicting IDs. But when Alice applies Bob's changes using sspatch, she gets a happier result:

locations

id	street	city
1	305 Memorial Drive	Cambridge
2	Big Crater	Moon
3	10 Ten Street	Denver
4	42 The Boblands	Planet Bob

org2loc

org_id	loc_id
1	2
2	1
3	1
3	2
5	3
3	4
6	1
6	4

organizations

id	name
1	Dracula Inc
2	The Firm
3	Omni Cooperative
4	Nonexistence Unlimited
5	Alice and Company
6	Bob's World

(Command: sspatch --inplace directory.sqlite bob.tdiff)

Notice how IDs have been modified appropriately. They are allocated by Sqlite rather than slavishly copied. If this is not the desired behavior, turn on the "--native" flag when calling sspatch.

If Bob didn't make a patch but instead sent a copy of his database, Alice could either use ssdiff followed by sspatch, or combine the two steps with ssmerge. Doing:

ssmerge --inplace directory.sqlite directory_alice.sqlite directory_bob.sqlite

Alice well get:

locations

id	street	city
1	305 Memorial Drive	Cambridge
2	Big Crater	Moon
3	10 Ten Street	Denver
4	42 The Boblands	Planet Bob

org2loc

org_id	loc_id
1	2
2	1
3	1
3	2
5	3
3	4
6	1
6	4

organizations

id	name
1	Dracula Inc
2	The Firm
3	Omni Cooperative
4	Nonexistence Unlimited
5	Alice and Company
6	Bob's World

For merging, it is important that Alice has a copy of the last "common ancestor" between her own database and Bob's.

Getting the material used in this example

If you want to follow along, you can get a copy of the database used in this example by doing:

sspatch --test-file directory.sqlite

If you have the sqlite3 tool installed, you can view the database's content with:

sqlite3 directory.sqlite .dump    # how to make the database
sqlite3 directory.sqlite .schema  # structural part of database only

If you don't, you can use COOPY:

ssdiff directory.sqlite    # how to make the database
ssformat directory.sqlite  # non-structural part of database only