Sequences

Many applications require non-repeating sequences of integer numbers. While some database engines provide structures for this purpose, others, such as MySQL and SQLite do not.

Alternatively, you may wish to have a more global source of unique integer identifiers that you share across several database servers.

For either of these cases, you can use the sequence support provided by mortar_rdb.

Usage is very simple. During application configuration, register sequences at the same time as you register the session to use for a database:

from mortar_rdb import register_session, get_session
from mortar_rdb.sequence import register_sequence

import transaction

register_session(db_url)
with transaction.manager:
    session = get_session()
    register_sequence('seq1', session)
    register_sequence('seq2', session)

You’ll notice that you need to manage a database transaction when you call register_sequence(), either using the transaction package or by manually calling commit() after the call. This is because registering a sequence, certainly in the default SequenceImplementation, may require creating a row in a table.

Once registered, whenever you need some unique integers in application code, get hold of the sequence and call its next() method:

>>> from mortar_rdb import get_session
>>> from mortar_rdb.sequence import get_sequence
>>> seq1 = get_sequence('seq1')
>>> seq2 = get_sequence('seq2')
>>> session = get_session()
>>> seq1.next(session)
1
>>> seq1.next(session)
2
>>> seq1.next(session)
3
>>> seq2.next(session)
1

Note

The default implementation used by register_sequence() is mortar_rdb.sequence.generic.SequenceImplementation.

This uses a table called sequences in the database which needs to be created before you first call register_sequence(). If you are using mortar_rdb.controlled then you can use the source from mortar_rdb.sequence.generic.source as part of your Config to take care of table creation.

Also, please note that this implementation may cause contention on the table in question. If your database engine provides native sequences, an implementation that used those would be gratefully received!