[Req] Support for syntax NEXT VALUE FOR some_sequence

[ArsenShnurkov]

to create a new record, i want to execute the following sequence of SQL commands:

1. Declare variable
2. Generate new sequence number (to variable)
3. Insert this number into record (from variable)
4. Return this number (from variable) to the client (to EF for example)

DECLARE

ISO/IEC 9075-4: Persistent Stored Modules (SQL/PSM)
makes SQL computationally complete by specifying the syntax and semantics of additional SQL-statements.

Those include facilities for:
...

• The assignment of the result of expressions to variables
  ...

• The declaration of local variables

  DECLARE @myvar INTEGER
  

is defined in in SQL 2011, part 4, paragraph 14.4 "SQL variable declaration"

CREATE SEQUENCE

SQL 2003 standart introduces the way to create sequences to insert their values into PK columns.

the syntax (BNF-like) is described in ISO/IEC 9075-2:2003 (E)
paragraph 11.62

my simplified proposition (from http://www.hsqldb.org/doc/guide/ch09.html#create_sequence-section):

CREATE SEQUENCE <sequence_name>
 [AS [built_in_integer_type]]
 [START WITH <constant>]
 [INCREMENT BY <constant>]

NEXT VALUE FOR

paragraph 6.13 of ISO/IEC 9075-2:2003 (E) (-2 after 9075 means "part 2")
my simplified (in comparison with T-SQL syntax) proposition:

    NEXT VALUE FOR <sequence_name>

usage of local variable to store this value will help to avoid using nonstandart syntax similar to CURRENT VALUE FOR, SCOPE_IDENTITY(), @@IDENTITY, or call to ident_current(name) function - (short description of them)

   SELECT NEXT VALUE FOR myseq1 INTO @myvar;
   INSERT INTO mytable (field1, field2) VALUES (@myvar, 'some data');
   SELECT @myvar;

The last line will return identity of inserted record back to Entity Framework or similar DAL
this is typical scenario, described in wikipedia

What do you think about state of implementation of deveeldb for this scenario?

[tsutomi]

@ArsenShnurkov actually, the reference language for DeveelDB (at the moment) is SQL-99 definition (with extension to PL/SQL dialect).
Anyway, the feature you requested can be achieved (ideally: as you know, the new system is still under development) through a function named NEXT_VALUE().

The CREATE SEQUENCE statement was already in DeveelDB 1.x and it is under development for DeveelDB 2.0 (the system function exists: only the SQL statement is still missing).

The DECLARE var statement (to declare a variable), is now under testing and it works quite ok for the moment: although, since the PL/SQL development, it still needs to be verified against the nested execution blocks.

Implementing the NEXT VALUE FOR ... SQL statement is relatively simple, and it would be just a shortcut to SELECT NEXT_VALUE('sequence_name'), but before doing it, I want to review the whole definition set for the sequence commands in the SQL-2003 standard.

So, what you want to achieve is supposed to work in current DeveelDB like this:

DECLARE myvar INT
SELECT NEXT_VALUE('sequence') INTO myvar
SELECT myvar

Since some SQL statements are not verified to be working, I will write some tests for assessing the coverage.

[tsutomi]

By the way, since your goal, as I understand, is to return the current identity of a table, I can add some more information to it.

Sequences in DeveelDB are of two kinds:

• User-defined sequences (through the CREATE SEQUENCE statement)
• Native table sequences

The second ones handle an identity for rows in a table and the value cannot be manually controlled by users.
To name of the sequence is the name of the table: since a sequence is a Database Object (like tables, variables, cursors, etc.), it is not possible for a user to create a new object with the same name (that in this case is the table name).

To return the next unique value for a native sequence you should call the function UNIQUEKEY('table_name'), while to return the current identity you can use the function CURRENT_VALUE('table_name').

[tsutomi]

The implementation done supports "NEXT VALUE FOR" as a standalone statement and not as a function shortcut: using it as SELECT NEXT VALUE FOR sequence would fail.

Further development is needed to make it as an expression