Using primary key pools

One efficient means of solving the problem of unique primary keys is to assign each user of the database a pool of primary key values that can be used as the need arises. For example, you can assign each sales representative 100 new identification values. Each sales representative can freely assign values to new customers from his or her own pool.

To implement a primary key pool

Add a new table to the consolidated database and to each remote database to hold the new primary key pool. Apart from a column for the unique value in the consolidated database, these tables should contain a column for a user name, to identify who has been given the right to assign the value.
In the consolidated database, write a stored procedure to ensure that each user is assigned enough new identification values. Assign more new values to remote users who insert many new entries or who synchronize infrequently.
Write a download_cursor script to select the new values assigned to each user and download them to the remote database. See Timestamp-based downloads.
Modify the application that uses the remote database so that when a user inserts a new row, the application uses one of the values from the pool. The application must then delete that value from the pool so it is not used a second time.
Write an upload_delete script to upload the deleted keys. The MobiLink server then deletes rows from the consolidated pool of values that a user has deleted from his personal value pool in the remote database.
Write an end_upload script to call the stored procedure that maintains the pool of values. Doing so has the effect of adding more values to the user's pool to replace those deleted during upload.

Example

The CustDB sample application allows remote users to add customers. It is essential that each new row has a unique primary key value, and yet each remote database is disconnected when data entry is occurring.

The ULCustomerIDPool holds a list of primary key values that can be used by each remote database. In addition, the ULCustomerIDPool_maintain stored procedure tops up the pool as values are used up. The maintenance procedures are called by a table-level end_upload script, and the pools at each remote database are maintained by download_cursor and upload_delete scripts.

The ULCustomerIDPool table in the consolidated database holds the pool of new customer identification numbers. It has no direct link to the ULCustomer table.

The ULCustomerIDPool_maintain procedure updates the ULCustomerIDPool table in the consolidated database. The following sample code is for a SQL Anywhere consolidated database.

CREATE PROCEDURE ULCustomerIDPool_maintain ( IN syncuser_id INTEGER )
BEGIN
    DECLARE pool_count INTEGER;

    -- Determine how may ids to add to the pool
    SELECT COUNT(*) INTO pool_count
    FROM ULCustomerIDPool
    WHERE pool_emp_id = syncuser_id;
 
    -- Top up the pool with new ids
    WHILE pool_count < 20 LOOP
      INSERT INTO ULCustomerIDPool ( pool_emp_id )
      VALUES ( syncuser_id );
      SET pool_count = pool_count + 1;
    END LOOP;
END

This procedure counts the numbers that are currently assigned to the current user, and inserts new rows so that this user has enough customer identification numbers.

This procedure is called at the end of the upload, by the end_upload table script for the ULCustomerIDPool table. The script is as follows:

CALL ULCustomerIDPool_maintain( {ml s.username} )

The download_cursor script for the ULCustomerIDPool table downloads new numbers to the remote database.

SELECT pool_cust_id
FROM ULCustomerIDPool
WHERE pool_emp_id = {ml s.username}
AND last_modified >= {ml s.last_table_download}

To insert a new customer, the application using the remote database must select an unused identification number from the pool, delete this number from the pool, and insert the new customer information using this identification number. The following embedded SQL function for an UltraLite application retrieves a new customer number from the pool.

bool CDemoDB::GetNextCustomerID( void )
/*************************************/
{
    short ind;

    EXEC SQL SELECT min( pool_cust_id )
    INTO :m_CustID:ind FROM ULCustomerIDPool;
    if( ind < 0 ) {
        return false;
    }
    EXEC SQL DELETE FROM ULCustomerIDPool
    WHERE pool_cust_id = :m_CustID;
    return true;
}

Discuss this page in DocCommentXchange.