Contents Index Maintaining unique primary keys using key pools Handling conflicts

MobiLink Synchronization User's Guide
  Synchronization Techniques
    Maintaining unique primary keys

A primary key pool example


The 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 upload_cursor and download_cursor scripts.

  1. 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, ULEmployee, and ULCustomer tables.
  2. The ULCustomerIDPool_maintain procedure updates the ULCustomerIDPool table in the consolidated database. The following sample code is for an Adaptive Server 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 presently assigned to the current user, and inserts new rows so that this user has a sufficient supply of customer identification numbers.

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

    CALL ULCustomerIDPool_maintain( ? )
  3. 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 = ?
    AND last_modified > ?
  4. 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;
    }
  5. The upload_cursor script deletes numbers from the consolidated pool of numbers once they have been used and hence deleted from the remote pool.

    SELECT pool_cust_id
    FROM ULCustomerIDPool
    WHERE pool_cust_id = ?

Contents Index Maintaining unique primary keys using key pools Handling conflicts