Lesson 2: Prepare the consolidated database

This tutorial assumes that you have installed the Order Entry (OE) sample database. Information about installing the sample schema can be found in the Oracle documentation or can be viewed online at [external link] http://www.oracle.com/technology/obe/obe1013jdev/common/OBEConnection.htm.

The OE database needs to be altered for use with MobiLink. Columns are dropped because they were created as user-defined types. You could translate these user-defined types into types that SQL Anywhere recognizes, but doing so is not relevant to this tutorial. Next, you must grant permission to the OE user to create triggers because MobiLink needs to create a few triggers using OE's credentials.

To prepare the consolidated database

Connect as the SYS user with SYSDBA privileges using the Oracle SQL Plus application. At a command prompt, run the following command:
sqlplus SYS/your password for sys as SYSDBA

To drop columns created as user-defined types, run the following commands:

ALTER TABLE OE.CUSTOMERS DROP COLUMN CUST_ADDRESS;
ALTER TABLE OE.CUSTOMERS DROP COLUMN PHONE_NUMBERS;
ALTER TABLE OE.CUSTOMERS DROP COLUMN CUST_GEO_LOCATION;
ALTER TABLE OE.PRODUCT_INFORMATION DROP COLUMN WARRANTY_PERIOD;

Unlock the OE user and set the password to sql. Run the following command:
ALTER USER OE IDENTIFIED BY sql ACCOUNT UNLOCK;
To grant permission to the OE user to create triggers, run the following command:
GRANT CREATE ANY TRIGGER TO OE;

To drop the orders_customer foreign key and create a new foreign key that references the customer_id in the customers table, run the following commands:

ALTER TABLE OE.ORDERS DROP CONSTRAINT ORDERS_CUSTOMER_ID_FK;
ALTER TABLE OE.ORDERS ADD CONSTRAINT ORDERS_CUSTOMER_ID_FK
  FOREIGN KEY (CUSTOMER_ID) REFERENCES OE.CUSTOMERS (CUSTOMER_ID);

Adding unique primary keys

In a synchronization system, the primary key of a table is the only way to uniquely identify a row in different databases and the only way to detect conflicts. Every table that is being mobilized must have a primary key. The primary key must never be updated. You must also guarantee that a primary key value inserted at one database is not inserted in another database.

There are several ways of generating unique primary keys. For simplicity, the method of composite primary keys is used in this tutorial. This method creates primary keys with multiple columns that are unique across consolidated and remote databases.

To add unique primary keys to the consolidated database

Connect as the SYS user with SYSDBA privileges using the Oracle SQL Plus application. At a command prompt, run the following command:
sqlplus SYS/your password for sys as SYSDBA
Values added to the SALES_REP_ID must be in the HR.EMPLOYEES table. The ORDERS_SALES_REP_FK foreign key enforces this rule. For simplicity, drop this foreign key by running the following command:
ALTER TABLE OE.ORDERS DROP CONSTRAINT ORDERS_SALES_REP_FK;
The SALES_REP_ID column cannot be added as a primary key because it contains null values. For the purposes of this tutorial, replace the null values with a value of 1. Run the following command:
UPDATE OE.ORDERS SET SALES_REP_ID = 1 WHERE SALES_REP_ID IS NULL;
The ORDER_ID column is the current primary key of the ORDERS table. To drop the current primary key, run the following command:
ALTER TABLE OE.ORDERS DROP PRIMARY KEY CASCADE;

The composite primary key consists of the SALES_REP_ID column and the ORDER_ID column. To add the composite primary key, run the following command:

ALTER TABLE OE.ORDERS
ADD CONSTRAINT salesrep_order_pk PRIMARY KEY (sales_rep_id, order_id);

After running these commands, the MobiLink server should have no trouble connecting to the consolidated database and setting it up for synchronization with any number of remotes.

Adding foreign keys

Unique primary keys across all databases

In Lesson 4, the remote schema is created from the consolidated schema. This means that the remote schema has the same primary keys as the consolidated schema.

Columns were specifically chosen to ensure unique primary keys for all databases. For the ORDERS table, the primary key consists of the SALES_REP_ID and ORDER_ID columns. Any inserted value into the remote sales table must have an unique order number (the SALES_REP_ID value is always the same). This ensures uniqueness in each remote ORDERS table. The primary key in the consolidated ORDERS table prevents conflicts if multiple salespeople upload data. Each upload from a salesperson is unique to another salesperson because their SALES_REP_ID values are different.