Question: Can MobiLink handle cascading updates to primary and foreign key values on the consolidated database?
Answer: Yes, if you add UNIQUE constraints using the technique described in Synchronizing Updated Primary Key Values With MobiLink.
Let's start with three simple tables on the consolidated: parent, child and grandchild, with a hierarchial foreign key structure:
CREATE TABLE parent (
p1 VARCHAR ( 10 ) NOT NULL,
data INTEGER NOT NULL DEFAULT 0,
last_updated TIMESTAMP NOT NULL DEFAULT TIMESTAMP,
PRIMARY KEY ( p1 ) );
CREATE TABLE child (
p1 VARCHAR ( 10 ) NOT NULL,
p2 VARCHAR ( 10 ) NOT NULL,
data INTEGER NOT NULL DEFAULT 0,
last_updated TIMESTAMP NOT NULL DEFAULT TIMESTAMP,
PRIMARY KEY ( p1, p2 ),
FOREIGN KEY ( p1 )
REFERENCES parent ( p1 ) ON UPDATE CASCADE );
CREATE TABLE grandchild (
p1 VARCHAR ( 10 ) NOT NULL,
p2 VARCHAR ( 10 ) NOT NULL,
p3 VARCHAR ( 10 ) NOT NULL,
data INTEGER NOT NULL DEFAULT 0,
last_updated TIMESTAMP NOT NULL DEFAULT TIMESTAMP,
PRIMARY KEY ( p1, p2, p3 ),
FOREIGN KEY ( p1, p2 )
REFERENCES child ( p1, p2 ) ON UPDATE CASCADE );
Step 1: Add new candidate key columns to each table: r1, r2 and r3, and the associated UNIQUE constraints.
CREATE TABLE parent (
p1 VARCHAR ( 10 ) NOT NULL,
data INTEGER NOT NULL DEFAULT 0,
r1 BIGINT NOT NULL DEFAULT AUTOINCREMENT,
last_updated TIMESTAMP NOT NULL DEFAULT TIMESTAMP,
PRIMARY KEY ( p1 ),
UNIQUE ( r1 ) );
CREATE TABLE child (
p1 VARCHAR ( 10 ) NOT NULL,
p2 VARCHAR ( 10 ) NOT NULL,
data INTEGER NOT NULL DEFAULT 0,
r1 BIGINT NOT NULL,
r2 BIGINT NOT NULL DEFAULT AUTOINCREMENT,
last_updated TIMESTAMP NOT NULL DEFAULT TIMESTAMP,
PRIMARY KEY ( p1, p2 ),
UNIQUE ( r1, r2 ),
FOREIGN KEY ( p1 )
REFERENCES parent ( p1 ) ON UPDATE CASCADE );
CREATE TABLE grandchild (
p1 VARCHAR ( 10 ) NOT NULL,
p2 VARCHAR ( 10 ) NOT NULL,
p3 VARCHAR ( 10 ) NOT NULL,
data INTEGER NOT NULL DEFAULT 0,
r1 BIGINT NOT NULL,
r2 BIGINT NOT NULL,
r3 BIGINT NOT NULL DEFAULT AUTOINCREMENT,
last_updated TIMESTAMP NOT NULL DEFAULT TIMESTAMP,
PRIMARY KEY ( p1, p2, p3 ),
UNIQUE ( r1, r2, r3 ),
FOREIGN KEY ( p1, p2 )
REFERENCES child ( p1, p2 ) ON UPDATE CASCADE );
Here are the original tables on the remote, before any changes; just the primary key and data columns, and as usual the last_updated column is omitted:
CREATE TABLE parent (
p1 VARCHAR ( 10 ) NOT NULL,
data INTEGER NOT NULL DEFAULT 0,
PRIMARY KEY ( p1 ) );
CREATE TABLE child (
p1 VARCHAR ( 10 ) NOT NULL,
p2 VARCHAR ( 10 ) NOT NULL,
data INTEGER NOT NULL DEFAULT 0,
PRIMARY KEY ( p1, p2 ),
FOREIGN KEY ( p1 )
REFERENCES parent ( p1 ) ON UPDATE CASCADE );
CREATE TABLE grandchild (
p1 VARCHAR ( 10 ) NOT NULL,
p2 VARCHAR ( 10 ) NOT NULL,
p3 VARCHAR ( 10 ) NOT NULL,
data INTEGER NOT NULL DEFAULT 0,
PRIMARY KEY ( p1, p2, p3 ),
FOREIGN KEY ( p1, p2 )
REFERENCES child ( p1, p2 ) ON UPDATE CASCADE );
Step 2: Add the new columns r1, r2 and r3 to the tables on the remote database. Give r1, r2 or r3 a GLOBAL AUTOINCREMENT default, use them as the PRIMARY KEY, and change the original PRIMARY KEY into a UNIQUE constraint.
Leave the FOREIGN KEY constraints alone, however: the target of a FOREIGN KEY constraint in a child table does not have to be the PRIMARY KEY of the parent table, it can be a UNIQUE constraint. By leaving the FOREIGN KEY constraints alone, the remote database continues to have exactly the same referential integrity rules as the consolidated database.
In other words, we're just switching the PRIMARY KEY and UNIQUE constraints on the remote database to accomodate MobiLink's insistence that primary key values must not be changed on the remote.
CREATE TABLE parent (
p1 VARCHAR ( 10 ) NOT NULL,
data INTEGER NOT NULL DEFAULT 0,
r1 BIGINT NOT NULL
DEFAULT GLOBAL AUTOINCREMENT ( 1000000000 ),
UNIQUE ( p1 ),
PRIMARY KEY ( r1 ) );
CREATE TABLE child (
p1 VARCHAR ( 10 ) NOT NULL,
p2 VARCHAR ( 10 ) NOT NULL,
data INTEGER NOT NULL DEFAULT 0,
r1 BIGINT NOT NULL,
r2 BIGINT NOT NULL
DEFAULT GLOBAL AUTOINCREMENT ( 1000000000 ),
UNIQUE ( p1, p2 ),
PRIMARY KEY ( r1, r2 ),
FOREIGN KEY ( p1 )
REFERENCES parent ( p1 ) ON UPDATE CASCADE );
CREATE TABLE grandchild (
p1 VARCHAR ( 10 ) NOT NULL,
p2 VARCHAR ( 10 ) NOT NULL,
p3 VARCHAR ( 10 ) NOT NULL,
data INTEGER NOT NULL DEFAULT 0,
r1 BIGINT NOT NULL,
r2 BIGINT NOT NULL,
r3 BIGINT NOT NULL
DEFAULT GLOBAL AUTOINCREMENT ( 1000000000 ),
UNIQUE ( p1, p2, p3 ),
PRIMARY KEY ( r1, r2, r3 ),
FOREIGN KEY ( p1, p2 )
REFERENCES child ( p1, p2 ) ON UPDATE CASCADE );
At this point, we are free to download cascading primary key changes (p1, p2, p3) from the consolidated database to the remote database, but not changes to r1, r2 or r3. Here are some assumptions:
- values inserted into parent.r1, child.r2 and grandchild.r3 are globally unique across all databases, both consolidated and remote,
- once a row is inserted in parent, child or grandchild, the values in r1, r2 and r3 are never changed by any process anywhere,
- except for inserts and MobiLink scripts the columns r1, r2 and r3 are ignored by all processes running on the consolidated database, and
- changes to p1, p2 and p3 are initiated by application code on the consolidated database only, and are downloaded to the remote.
Tip: If you forget to switch the PRIMARY KEY and UNIQUE clauses on the remote database tables, the MobiLink client dbmlsync.exe will produce an error message like this:
E. 2011-05-05 08:16:40. SQL statement failed: (-196) Index 'parent UNIQUE (r1)' for table 'parent' would not be unique
Step 3: Code the MobiLink scripts to deal with the schema differences:
---------------------------------------------------------------------
-- Delete and add MobiLink scripts for table parent.
CALL ml_add_table_script ( 'v1', 'parent', 'upload_insert', NULL );
CALL ml_add_table_script ( 'v1', 'parent', 'upload_insert', '
INSERT parent
( p1,
data,
r1 )
VALUES ( {ml r.p1},
{ml r.data},
{ml r.r1} )' );
CALL ml_add_table_script ( 'v1', 'parent', 'upload_update', NULL );
CALL ml_add_table_script ( 'v1', 'parent', 'upload_update', '
UPDATE parent
SET data = {ml r.data}
WHERE r1 = {ml r.r1}' );
CALL ml_add_table_script ( 'v1', 'parent', 'upload_delete', NULL );
CALL ml_add_table_script ( 'v1', 'parent', 'upload_delete', '
DELETE parent
WHERE r1 = {ml r.r1}' );
CALL ml_add_table_script ( 'v1', 'parent', 'download_cursor', NULL );
CALL ml_add_table_script ( 'v1', 'parent', 'download_cursor', '
SELECT p1,
data,
r1
FROM parent
WHERE last_updated >= {ml s.last_table_download}' );
CALL ml_add_table_script ( 'v1', 'parent', 'download_delete_cursor', NULL );
CALL ml_add_table_script ( 'v1', 'parent', 'download_delete_cursor', '--{ml_ignore}' );
---------------------------------------------------------------------
-- Delete and add MobiLink scripts for table child.
CALL ml_add_table_script ( 'v1', 'child', 'upload_insert', NULL );
CALL ml_add_table_script ( 'v1', 'child', 'upload_insert', '
INSERT child
( p1,
p2,
data,
r1,
r2 )
VALUES ( {ml r.p1},
{ml r.p2},
{ml r.data},
{ml r.r1},
{ml r.r2} )' );
CALL ml_add_table_script ( 'v1', 'child', 'upload_update', NULL );
CALL ml_add_table_script ( 'v1', 'child', 'upload_update', '
UPDATE child
SET data = {ml r.data}
WHERE r1 = {ml r.r1}
AND r2 = {ml r.r2}' );
CALL ml_add_table_script ( 'v1', 'child', 'upload_delete', NULL );
CALL ml_add_table_script ( 'v1', 'child', 'upload_delete', '
DELETE child
WHERE r1 = {ml r.r1}
AND r2 = {ml r.r2}' );
CALL ml_add_table_script ( 'v1', 'child', 'download_cursor', NULL );
CALL ml_add_table_script ( 'v1', 'child', 'download_cursor', '
SELECT p1,
p2,
data,
r1,
r2
FROM child
WHERE last_updated >= {ml s.last_table_download}' );
CALL ml_add_table_script ( 'v1', 'child', 'download_delete_cursor', NULL );
CALL ml_add_table_script ( 'v1', 'child', 'download_delete_cursor', '--{ml_ignore}' );
---------------------------------------------------------------------
-- Delete and add MobiLink scripts for table grandchild.
CALL ml_add_table_script ( 'v1', 'grandchild', 'upload_insert', NULL );
CALL ml_add_table_script ( 'v1', 'grandchild', 'upload_insert', '
INSERT grandchild
( p1,
p2,
p3,
data,
r1,
r2,
r3 )
VALUES ( {ml r.p1},
{ml r.p2},
{ml r.p3},
{ml r.data},
{ml r.r1},
{ml r.r2},
{ml r.r3} )' );
CALL ml_add_table_script ( 'v1', 'grandchild', 'upload_update', NULL );
CALL ml_add_table_script ( 'v1', 'grandchild', 'upload_update', '
UPDATE grandchild
SET data = {ml r.data}
WHERE r1 = {ml r.r1}
AND r2 = {ml r.r2}
AND r3 = {ml r.r3}' );
CALL ml_add_table_script ( 'v1', 'grandchild', 'upload_delete', NULL );
CALL ml_add_table_script ( 'v1', 'grandchild', 'upload_delete', '
DELETE grandchild
WHERE r1 = {ml r.r1}
AND r2 = {ml r.r2}
AND r3 = {ml r.r3}' );
CALL ml_add_table_script ( 'v1', 'grandchild', 'download_cursor', NULL );
CALL ml_add_table_script ( 'v1', 'grandchild', 'download_cursor', '
SELECT p1,
p2,
p3,
data,
r1,
r2,
r3
FROM grandchild
WHERE last_updated >= {ml s.last_table_download}' );
CALL ml_add_table_script ( 'v1', 'grandchild', 'download_delete_cursor', NULL );
CALL ml_add_table_script ( 'v1', 'grandchild', 'download_delete_cursor', '--{ml_ignore}' );
The upload_insert and download_cursor scripts look no different than they would if p1, p2 and p3 were still the primary key columns on the remote database: all columns uploaded, all columns downloaded (except for last_updated, which is omitted from the remote database).
The upload_update scripts ignore the p1, p2 and p3 columns because they are never changed on the remote. The WHERE clauses use r1, r2 and r3 to locate the rows to be updated, and that's OK because
- r1, r2 and r3 are UNIQUE on the consolidated and
- r1, r2 and r3 are never modified on the consolidated.
Here's a demonstration; first, three rows are inserted on the consolidated database, and dbmlsync is run to download those rows to the remote:
INSERT parent ( p1, data ) VALUES ( 'a', 0 );
INSERT child ( p1, p2, data, r1 ) VALUES ( 'a', 'b', 0, 1 );
INSERT grandchild ( p1, p2, p3, data, r1, r2 ) VALUES ( 'a', 'b', 'c', 0, 1, 1 );
COMMIT;
Here's the step-by-step with expected output:
---------------------------------------------------------------------------
-- 1. Run this on the consolidated and remote database to display the data.
SELECT * FROM parent;
SELECT * FROM child;
SELECT * FROM grandchild;
/* Expected output from the consolidated database...
p1,data,r1,last_updated
'a',0,1,'2011-05-05 08:26:42.946'
p1,p2,data,r1,r2,last_updated
'a','b',0,1,1,'2011-05-05 08:26:42.967'
p1,p2,p3,data,r1,r2,r3,last_updated
'a','b','c',0,1,1,1,'2011-05-05 08:26:43.109'
*/
/* Expected output from the remote database...
p1,data,r1
'a',0,1
p1,p2,data,r1,r2
'a','b',0,1,1
p1,p2,p3,data,r1,r2,r3
'a','b','c',0,1,1,1
*/
---------------------------------------------------------------------------
-- 2. Change the primary keys on the consolidated database.
UPDATE parent
SET p1 = 'aa'
WHERE p1 = 'a';
UPDATE child
SET p2 = 'bb'
WHERE p1 = 'aa'
AND p2 = 'b';
UPDATE grandchild
SET p3 = 'cc'
WHERE p1 = 'aa'
AND p2 = 'bb'
AND p3 = 'c';
COMMIT;
---------------------------------------------------------------------------
-- 3. Run a sync to download the new primary keys.
---------------------------------------------------------------------------
-- 4. Display the new "primary keys" on the remote database.
SELECT * FROM parent;
SELECT * FROM child;
SELECT * FROM grandchild;
/* Expected output from remote...
p1,data,r1
'aa',0,1
p1,p2,data,r1,r2
'aa','bb',0,1,1
p1,p2,p3,data,r1,r2,r3
'aa','bb','cc',0,1,1,1
*/
---------------------------------------------------------------------------
-- 5. Use the new "primary keys" to modify the data on the remote database.
UPDATE parent
SET data = 2
WHERE p1 = 'aa';
UPDATE child
SET data = 2
WHERE p1 = 'aa'
AND p2 = 'bb';
UPDATE grandchild
SET data = 2
WHERE p1 = 'aa'
AND p2 = 'bb'
AND p3 = 'cc';
COMMIT;
---------------------------------------------------------------------------
-- 6. Run a sync to upload the modified data.
---------------------------------------------------------------------------
-- 7. Display the modified data on the consolidated database.
SELECT * FROM parent;
SELECT * FROM child;
SELECT * FROM grandchild;
/* Expected output from consolidated..
p1,data,r1,last_updated
'aa',2,1,'2011-05-05 08:32:09.292'
p1,p2,data,r1,r2,last_updated
'aa','bb',2,1,1,'2011-05-05 08:32:09.294'
p1,p2,p3,data,r1,r2,r3,last_updated
'aa','bb','cc',2,1,1,1,'2011-05-05 08:32:09.296'
*/
No comments:
Post a Comment