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 SEQUENCE idseq INSERT INTO account SET id = sequence('idseq').next()
For more information look at SQL CREATE SEQUENCE.
Using a sequence from SQL
You can use a sequence from SQL with the following syntax:
next()retrieves the next value
current()gets the current value
reset()resets the sequence value to it's initial value
INSERT INTO Account SET id = sequence('mysequence').next()
Alter a sequence
ALTER SEQUENCE idseq START 1000
For more information look at SQL ALTER SEQUENCE.
Drop a sequence
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):
CREATE CLASS counter INSERT INTO counter SET name='mycounter', value=0
And then every time you need a new number you can do:
UPDATE counter INCREMENT value = 1 WHERE name = 'mycounter'
This works in a SQL batch in this way:
BEGIN let $counter = UPDATE counter INCREMENT value = 1 return after $current WHERE name = 'mycounter' INSERT INTO items SET id = $counter.value, qty = 10, price = 1000 COMMIT