Using GLOBAL AUTOINCREMENT in UltraLite

You can declare the default value of a column in an UltraLite database to be of type GLOBAL AUTOINCREMENT. However, before you can autoincrement these column IDs, you must first set the global database ID for the UltraLite database.

Caution

GLOBAL AUTOINCREMENT column values downloaded via MobiLink synchronization do not update the GLOBAL AUTOINCREMENT value counter. As a result, an error can occur should one MobiLink client insert a value into another client's partition. To avoid this problem, ensure that each copy of your UltraLite application inserts values only in its own partition.

 Declare GLOBAL AUTOINCREMENT columns in your UltraLite database

  1. Assign each copy of the database a unique global ID number.

    The global_database_id database option sets the value in your UltraLite database. When deploying UltraLite, you must assign a different identification number to each database.

  2. Allow UltraLite to supply default values for the column using the partition uniquely identified by the UltraLite database's number. UltraLite follows these rules:

    • If the column contains no values in the current partition, the first default value is pn + 1. p represents the partition size and n represents the global ID number.

    • If the column contains values in the current partition, but all are less than p(n + 1), the next default value will be one greater than the previous maximum value in this range.

    • Default column values are not affected by values in the column outside the current partition; that is, by numbers less than pn + 1 or greater than p(n + 1). Such values may be present if they have been replicated from another database via MobiLink synchronization.

      For example, if you assigned your UltraLite database a global ID of 1 and the partition size is 1000, then the default values in that database would be chosen in the range 1001-2000. Another copy of the database, assigned the identification number 2, would supply default values for the same column in the range 2001-3000.

    • Because you cannot set the global ID number to negative values, the values UltraLite chooses for GLOBAL AUTOINCREMENT columns are always positive. The maximum identification number is restricted only by the column data type and the partition size.

    • If you do not set a global ID value, or if you exhaust values from the partition, a NULL value is inserted into the column. Should NULL values not be permitted, the attempt to insert the row causes an error.

  3. If you exhaust or will soon exhaust available values for columns declared as GLOBAL AUTOINCREMENT, you need to set a new global database ID. UltraLite chooses GLOBAL AUTOINCREMENT values from the partition identified by the global ID number, but only until the maximum value is reached. If you exceed values, UltraLite begins to generate NULL values. By assigning a new global database ID number, you allow UltraLite to set appropriate values from another partition.

    One method of choosing a new global database ID is to maintain a pool of unused global database ID values. This pool is maintained in the same manner as a pool of primary keys.

    Tip

    UltraLite APIs provide means of obtaining the proportion of numbers that have been used. The return value is a SHORT in the range 0-100 that represents the percent of values used so far. For example, a value of 99 indicates that very few unused values remain and the database should be assigned a new identification number. The method of setting this identification number varies according to the programming interface you are using.

 See also
Copyright © 2012, iAnywhere Solutions, Inc. - SQL Anywhere 12.0.1