Sequence abstraction

[beikov]

It is sometimes necessary to have something like a user defined sequence which is more business focused than technical. Since JPA/Hibernate only support sequences through identifiers which is most of the time good enough and offers no API to actually consume sequence values directly, we should step forward and provide an API for that.

I'd like to support transactional and non-transactional sequences i.e. value increments do locking and are only visible after commit or are immediatelly visible. The API should offer support for CRUD operations for sequences and support the following attributes.

• Sequence name
• Sequence min and max value
• Sequence increment value
• Sequence cache size

In addition to that, further metadata should be providable by the user like e.g. tenant_id. Apart from the metadata itself, it should be possible to get the current value and also get and increment the value i.e. nextval semantics.

The following strategies might be interesting to look at

• Native sequence - uses a DBMS native sequence and a additional table for metadata
• Table sequence - uses a table for the sequence value
  • Metadata can be held in a separate table or together with the value
  • Maybe allow a padding for DBMS that do locking?