Using Sequences

create_sequence

nextval

currval

drop_sequence

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.

create_sequence

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 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.

nextval

Retrieves the next value from the named sequence. For example:

SELECT nextval("my_sequence");

currval

Retrieves the last value that was returned from the named sequence. For example:

SELECT currval("my_sequence");

drop_sequence

Removes the sequence. For example:

SELECT drop_sequence("my_sequence");