20 January 2016
20 January 2016,
 Off

This is a guest post by OrientDB contributor Matan Shukry.

Hi,

My name is Matan Shukry, and I’m a Programmer, DBA, and a Big Data engineer.

Today I’ll talk about my contribution to OrientDB, with emphasis on sequences.

The concept of sequences should be familiar to most people who used an RDBMS before. However, for those of you who aren’t familiar with it, I’ll give a short description on the topic.

Sequence is a database object that generates numbers sequentially. It is mostly used for automatically incremented columns.

Sounds simple, right? Well, here comes the tricky part:

    1. Sequences do not necessarily generate numbers in an ordered fashion. Assuming A and B are retrieve operations, where B happens after A, A may result in a number that is higher than B.
    2. Sequences do not necessarily generate numbers in a continuous fashion. Assuming A and B are retrieve operations, where B happens after A, the difference between the result of B and A may be bigger than 1. That is, there may be “holes” between sequence values.

 

Both of the above points happen due to a caching mechanism, where a range of numbers are kept in memory, and when requested are provided to the user. However, in some cases such as a transaction rollback or a server shutdown, the numbers are lost. Furthermore, in many cases there is also an option in the sequence to turn off caching in order to provide a sequence that generates ordered and continuous numbers.

Starting from version 2.2, a sequence object has been introduced to OrientDB. It contains two types (ordered and cached), and include ‘start’ and ‘increment’ fields.

The sequence object in OrientDB uses optimistic transaction (MVCC). When the sequence needs to allocate more numbers (either a range of them with cached sequence or a single one with ordered sequence), it will retrieve the document, change it’s properties, and attempt to save it (commit). If the sequence document is too old, meaning another connection changed the document and committed it in between our retrieve-and-save, the sequence will attempt to retry the operation again. If the operation fails a certain number of retries, an exception is thrown back to the user, after which the user decides what to do next. The entire process happens at the database layer, and it’s very quickly.

Also, an automatically-increment column type (which will rely on the sequence object, together with the default value feature) will probably be added in the near/distant future. As you probably figured out by now, this will result in inserting an automatically-incremented number into a specific column. 

Here are a few examples on how to use sequences. Consider a blog site where we would like each post to have a unique id. We would create the sequence as follows:

SQL

CREATE SEQUENCE postId TYPE CACHED START 101 INCREMENT 2 CACHE 20

Java

OSequence seq = database.getMetadata().getSequenceLibrary().createSequence(“postId”, OSequence.SEQUENCE_TYPE.CACHED, new OSequence.CreateParams().setStart(101).setIncrement(2).setCacheSize(20));

Each time we’ll want to insert a new post, we’ll use .next():

SQL

INSERT INTO Post SET id = sequence(“postId”).next(), title=”BTE - Best Title Ever”, body=”…”

Java (Graph API)

OSequence seq = graphDB.getRawGraph().getMetadata().getSequenceLibrary().getSequence(“postId”);
graphDB.addVertex(“class:Post”,
“id”, seq.next(), “title”, “BTE - Best Title Ever”, “body”, “…”);

Java (Document API)

OSequence seq = database.getMetadata().getSequenceLibrary().getSequence(“postId”); 
ODocument doc = new ODocument(“Post”); 
doc.fields(“id”, seq.next(), “title”, “BTE – Best Title Ever”, “body”, “…”); 
doc.save();

 

You can also change the sequence parameters (alter): 

SQL

ALTER SEQUENCE postId START 1001 INCREMENT 30 CACHE 40

Java

database.getMetadata().getSequenceLibrary().getSequence(“postId”).updateParams(new OSequence.CreateParams().setStart(1001).setIncrement(30).setCache(40));

 

If at some point we would like to retrieve the current value without incrementing it or reset it back to 0 (probably when playing around in your development environment):

SQL

SELECT sequence(“userId”).current()
SELECT sequence(“userId”).reset()

Java

OSequence seq = database.getMetadata().getSequenceLibrary().getSequence(“postId”); 
long value = seq.current(); 
seq.reset();

P.S.

There is a workaround in order to create an auto-increment fields in previous versions of OrientDB (<v2.2). Check out this page for more information.

 

Hope this comes in handy,

Matan Shukry
 _

Comments are closed.