When rows are deleted from the consolidated database, there needs to be a record of the row so it can be explicitly selected by a download_delete_cursor and removed from any remote databases that have the row. Two ways to do this are by using logical deletes or shadow tables.
Logical deletes With this method, the row is not deleted. Data that is no longer required is marked as inactive in a status column. The WHERE clause of the download_cursor and download_delete_cursor and most application queries must refer to the status of the row.
This technique is used in the CustDB sample application, in which the ULEmpCust.action column holds a D for Delete. The scripts use this value to delete records from the remote database, and delete records from the consolidated database at the end of the synchronization. CustDB also uses this technique for the ULOrder table, and the Contact sample uses the technique on the Customer, Contact, and Product tables.
The MobiLink synchronization model support for logical deletes assumes that a logical delete column is only on the consolidated database and not on the remote. When copying a consolidated schema to a new remote schema, leave out any columns that match the logical delete column in the model's synchronization settings. For a new model, the default column name is deleted.
To add the logical delete column name to the remote schema:
In the Create Synchronization Model Wizard, on the Download Deletes page, click Use logical deletes.
Rename the logical delete column so that it does not match any column names in the consolidated.
When the wizard is finished, update the remote schema and keep the default table selection. The logical delete column name appears in the schema change list and be added to remote schema.
You need to set the column mapping for the remote's logical delete column to the consolidated's logical delete column.
Shadow tables With this method, you create a shadow table that stores the primary key values of deleted rows. When a row is deleted, a trigger can populate the shadow table. The download_delete_cursor can use the shadow table to remove rows from remote databases. The shadow table only needs to contain the primary key columns from the real table.
Discuss this page in DocCommentXchange.
|Copyright © 2012, iAnywhere Solutions, Inc. - SQL Anywhere 12.0.1|