Sequences and auto increment

Starting from v2.2, OrientDB supports sequences like most of RDBMS. What’s a sequence? It’s a structure that manage counters. Sequences are mostly used when you need a number that always increments. Sequence types can be:

  • ORDERED: each call to .next() will result in a new value.
  • CACHED: the sequence will cache N items on each node, thus improving the performance if many .next() calls are required. However, this may create holes.

To manipulate sequences you can use the Java API or SQL commands.

Create a sequence

Create a sequence with Java API

  1. OSequenceLibrary sequenceLibrary = database.getMetadata().getSequenceLibrary();
  2. OSequence seq = sequenceLibrary.createSequence("idseq", SEQUENCE_TYPE.ORDERED, new OSequence.CreateParams().setStart(0));

SQL CREATE SEQUENCE

  1. CREATE SEQUENCE idseq
  2. INSERT INTO account SET id = sequence('idseq').next()

For more information look at SQL CREATE SEQUENCE.

Using a sequence

Using a sequence with Java API

  1. OSequence seq = graph.getRawGraph().getMetadata().getSequenceLibrary().getSequence("idseq");
  2. graph.addVertex("class:Account", "id", seq.next());

Using a sequence from SQL

You can use a sequence from SQL with the following syntax:

  1. sequence('<sequence>').<method>

Where:

  • method can be:
    • next() retrieves the next value
    • current() gets the current value
    • reset() resets the sequence value to it’s initial value

Example

  1. INSERT INTO Account SET id = sequence('mysequence').next()

Alter a sequence

Alter a sequence with Java API

  1. graph.getRawGraph().getMetadata().getSequenceLibrary().getSequence("idseq").updateParams( new OSequence.CreateParams().setStart(1000) );

SQL ALTER SEQUENCE

  1. ALTER SEQUENCE idseq START 1000

For more information look at SQL ALTER SEQUENCE.

Drop a sequence

Drop a sequence with Java API

  1. graph.getRawGraph().getMetadata().getSequenceLibrary().dropSequence("idseq");

SQL DROP SEQUENCE

  1. DROP SEQUENCE idseq

For more information look at SQL DROP SEQUENCE.

OrientDB before v2.2

OrientDB before v2.2 doesn’t support sequences (autoincrement), so you can manage your own counter in this way (example using SQL):

  1. CREATE CLASS counter
  2. INSERT INTO counter SET name='mycounter', value=0

And then every time you need a new number you can do:

  1. UPDATE counter INCREMENT value = 1 WHERE name = 'mycounter'

This works in a SQL batch in this way:

  1. BEGIN
  2. let $counter = UPDATE counter INCREMENT value = 1 return after $current WHERE name = 'mycounter'
  3. INSERT INTO items SET id = $counter.value[0], qty = 10, price = 1000
  4. COMMIT