You can use sequences with the SQL API. Sequences provide for an arbitrary number of increasing or decreasing integers that persist across database accesses. Use sequences if you need to create unique values in a highly efficient and persistent way.
To create and access a sequence, you must use SQL functionality that is unique to the BDB SQL interface; no corresponding functionality exists in SQLite. The sequence functionality is implemented using SQLite function plugins, as such it is necessary to use the 'select' keyword as a prefix to all sequence APIs.
The SQL API sequence support is a partial implementation of the sequence API defined in the SQL 2003 specification.
The following sections describe the BDB SQL interface sequence API.
Creates a new sequence. A name is required, all other parameters are optional. For example:
SELECT create_sequence("my_sequence", "start", 100, "incr", 10,
"maxvalue", 300);
This creates a sequence called my_sequence
starting at 100 and incrementing by 10 until it reaches
300.
SELECT create_sequence("my_decr_sequence", "incr", -100,
"minvalue", -10000);
This creates a sequence call my_decr_sequence
starting at 0 and decreasing by 100 until it reaches
-10000.
Parameters are:
name
Required parameter that provides the name of the sequence. It is an error to create a sequence with another name that is currently in use within the database.
start
The starting value for the sequence. If this
parameter is not provided it is set to
minvalue if
an incrementing sequence is used, and
maxvalue if a decrementing
sequence is used. If neither
of those parameters are set then
0 is used.
minvalue
The lowest value generated by the sequence. If this parameter is not provided and a decrementing sequence is created, then INT64_MIN is used.
maxvalue
The largest value generated by the sequence. If this parameter is not provided and an incrementing sequence is created, then INT64_MAX is used.
incr
The amount the sequence is incremented for each
get operation. This value can be positive or
negative. If this parameter is not provided,
then 1 is used.
cache
Causes each handle to keep a cache of sequence values. So long as there are values available in the cache, retrieving the next value is cheap and does not lead to contention between handles.
Sequences with caches cannot be created or dropped within an explicit transaction.
Operations on caching sequences are not transactionally protected. That is, a rollback will not result in a value being returned to the sequence.
Sequences with caches do not support the
currval function.
The parameter following the cache parameter must be an integer value specifying the size of the cache.
Retrieves the next value from the named sequence. For example:
SELECT nextval("my_sequence");
Retrieves the last value that was returned from the named sequence. For example:
SELECT currval("my_sequence");