MobiLink Synchronization User's Guide
Synchronization Techniques
Maintaining unique primary keys
Maintaining unique primary keys using global autoincrement
If you use the extraction utility to create your remote databases, you can write a stored procedure to automate the task. If you create a stored procedure named sp_hook_dbxtract_begin, it is called automatically by the extraction utility. Before the procedure is called, the extraction utility creates a temporary table named #hook_dict, with the following contents:
name | value |
---|---|
extracted_db_global_id | user ID being extracted |
If you write your sp_hook_dbxtract_begin procedure to modify the value column of the row, that value is used as the GLOBAL_DATABASE_ID option of the extracted database, and marks the beginning of the range of primary key values for GLOBAL DEFAULT AUTOINCREMENT values.
Consider extracting a database for remote user user2 with a user_id of 101. If you do not define an sp_hook_dbxtract_begin procedure, the extracted database will have GLOBAL_DATABASE_ID set to 101.
If you define a sp_hook_dbxtract_begin procedure, but it does not modify any rows in the #hook_dict then the option will still be set to 101.
If you set up the database as follows:
set option "PUBLIC"."Global_database_id" = '1'; create table extract_id ( next_id integer not null) ; insert into extract_id values( 1 ); create procedure sp_hook_dbxtract_begin as declare @next_id integer update extract_id set next_id = next_id + 1000 select @next_id = (next_id ) from extract_id commit update #hook_dict set value = @next_id where name = 'extracted_db_global_id'
Then each extracted or re-extracted database will get a different GLOBAL_DATABASE_ID. The first starts at 1001, the next at 2001, and so on.
To assist in debugging procedure hooks, dbxtract outputs the following when it is set to operate in verbose mode:
the procedure hooks found
the contents of #hook_dict before the procedure hook is called
the contents of #hook_dict after the procedure hook is called