Synchronizing Updated Primary Key Values With MobiLink

Question: Can MobiLink handle changes to primary key values on the consolidated database?

Answer: I've been telling people "No!" for so many years that I've come to believe it myself:

"No! You can't change primary key values if you're synchronizing with MobiLink!"

Epiphany

A couple of days ago someone asked the question again, someone who really wanted the answer to be "Yes!", so much so that I started thinking about "Why not?" and "How?"... and that's when I had this epiphany:

The rule is actually this: You can't change primary key values on the remote database if you're synchronizing with MobiLink.

MobiLink is extremely flexible, and it has been used for many years to handle schema differences between consolidated and remote databases. So what if the table on the remote database had a different column as PRIMARY KEY, and the original PRIMARY KEY column from the consolidated was defined as a UNIQUE constraint on the remote?

It Works!

Yes, you can change primary key values on the consolidated database when you're using MobiLink. Here's how it works:

Let's start with a simple table on the consolidated, one primary key column, one data column, and the usual last_updated:

CREATE TABLE t (
   p1            VARCHAR ( 10 ) NOT NULL,
   data          INTEGER NOT NULL DEFAULT 0,
   last_updated  TIMESTAMP NOT NULL DEFAULT TIMESTAMP,
   PRIMARY KEY ( p1 ) );

---

Step 1: Add a new column r1 to the table on the consolidated database, and make it UNIQUE. This column will not be used by any application code on the consolidated database, it's just there for MobiLink purposes.

CREATE TABLE t (
   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 ) );

---

Here's the corresponding table on the remote, before any changes; just the primary key and data columns, and as usual the last_updated column is omitted:

CREATE TABLE t (
   p1            VARCHAR ( 10 ) NOT NULL,
   data          INTEGER NOT NULL DEFAULT 0,
   PRIMARY KEY ( p1 ) );

---

Step 2: Add the new column r1 to the table on the remote database. Give r1 a GLOBAL AUTOINCREMENT default, make r1 the PRIMARY KEY, and change the original primary key column p1 into a UNIQUE constraint.

CREATE TABLE t (
   p1            VARCHAR ( 10 ) NOT NULL,
   data          INTEGER NOT NULL DEFAULT 0,
   r1            BIGINT NOT NULL 
                    DEFAULT GLOBAL AUTOINCREMENT ( 1000000000 ),
   PRIMARY KEY ( r1 ),
   UNIQUE ( p1 ) );

At this point, we are free to download primary key changes (p1) from the consolidated database to the remote database, but not changes to r1. Here are some assumptions:

• values inserted into t.r1 are globally unique across all databases, both consolidated and remote,

• once a row is inserted in t the value in t.r1 is never changed by any process anywhere,

• except for inserts and MobiLink scripts the column t.r1 is ignored by all processes running on the consolidated database, and

• changes to t.p1 are initiated by application code on the consolidated database only, and are downloaded to the remote.

The fourth assumption (t.p1 is only modified on the consolidated database) may or may not be a requirement, but this article doesn't answer that question one way or another.

---

Step 3: Code the MobiLink scripts to deal with the schema differences:

CALL ml_add_table_script  ( 'v1', 't', 'upload_insert', '
INSERT t
       ( p1,
         data,
         r1 )
VALUES ( {ml r.p1},
         {ml r.data},
         {ml r.r1} )' );

CALL ml_add_table_script  ( 'v1', 't', 'upload_update', '
UPDATE t
   SET data = {ml r.data}
 WHERE r1 = {ml r.r1}' );

CALL ml_add_table_script  ( 'v1', 't', 'upload_delete', '
DELETE t
 WHERE r1 = {ml r.r1}' );

CALL ml_add_table_script  ( 'v1', 't', 'download_cursor', '
SELECT p1,
       data,
       r1
  FROM t
 WHERE last_updated >= {ml s.last_table_download}' );

The upload_insert and download_cursor scripts look no different than they would if p1 was still the primary key on the remote database.

The upload_update script ignores the t.p1 column because it is never changed on the remote. The WHERE clause uses t.r1 to locate the row to be updated, and that's OK because

• t.r1 is UNIQUE on the consolidated and

• t.r1 is never modified on the consolidated.

The upload_delete also uses t.r1 in the WHERE clause, for the same reasons as the upload_update script.

---

Here's a demonstration; first, one row is inserted on the consolidated database, and dbmlsync is run to download that row to the remote:

INSERT t VALUES ( 'a', 0, DEFAULT, DEFAULT );
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 t;

/* Expected output from consolidated and remote...

p1,data,r1,last_updated
'a',0,1,'2011-05-04 11:39:55.523'

p1,data,r1
'a',0,1

*/

--------------------------------------------------------------------------- 
-- 2. Change the primary key on the consolidated database.

UPDATE t SET p1 = 'xxx' WHERE p1 = 'a';

COMMIT;

--------------------------------------------------------------------------- 
-- 3. Run a sync to download the new primary key.

--------------------------------------------------------------------------- 
-- 4. Display the new primary key on the remote database.

SELECT * FROM t;

/* Expected output from remote...

p1,data,r1
'xxx',0,1

*/

--------------------------------------------------------------------------- 
-- 5. Use the new primary key to modify the data on the remote database.

UPDATE t SET data = 1 WHERE p1 = 'xxx';

COMMIT;

--------------------------------------------------------------------------- 
-- 6. Run a sync to upload the modified data.

--------------------------------------------------------------------------- 
-- 7. Display the modified data on the consolidated database.

SELECT * FROM t;

/* Expected output from consolidated..

p1,data,r1
'xxx',1,1

*/