STK/Type Manual

STK/Type is work in progress. Please, be patient.

State of the art

The following Data Structures are currently implemented and working:

  • SEQUENCE: Full-featured, PostgreSQL-like sequences
  • SEQ: simple and quick sequences

SEQ

DDL

void seq_create(name CHAR(64), start_value BIGINT UNSIGNED)

Creates a new SEQ. name is the name of the SEQ. start_value is the current value, but it will never be used. If a SEQ called name already exists, produces an error.

void seq_replace(name CHAR(64), start_value BIGINT UNSIGNED)

Works exactly like seq_create(), except that if a SEQ called name already exists, it is replaced.

seq_clone(old_name CHAR(64), new_name CHAR(64))

Duplicates a SEQ. If a SEQ called old_name does not exist, or a SEQ called new_name already exists, this Routine produces an error.

void seq_drop(name CHAR(64))

Drops the specified SEQ. If a SEQ called name does not exist, produces an error.

void seq_rename(old_name CHAR(64), new_name CHAR(64))

Renames a SEQ called old_name to new_name. If a SEQ called old_name does not exist, or a SEQ called new_name already exists, produces an error.

BOOL seq_exists(name CHAR(64))

Returns TRUE if the specified SEQ exists, otherwise returns FALSE.

Usage

BIGINT UNSIGNED seq(name CHAR(64))

Increments the SEQ's current value by 1, and returns the new value. Note that the initial value will never be returned by this function.

Metadata

SEQs are stored in a table called SEQS, in stk_type database. That table is mirrored by the SEQS view, in the meta_schema database.

void show_seqs()

Shows a list of all existing SEQs.

void show_seqs(pattern TEXT)

Show a list of all SEQs matching to specified pattern. The pattern matching is performed via the LIKE operator.

TEXT seq_show_create(name VARCHAR(64))

Returns the SQL statement that can be used to re-create the specified SEQ, in its current state.

Additions to STK/Unit

Testing the use of SEQs in applications is made easier by some extra assert Procedures for STK/Unit. To add those Routines, the test/case/test_seq.sql file must be executed.

void assert_seq_exists(name CHAR(64), msg CHAR(255))

Asserts that the specified SEQ exists.

void assert_seq_not_exists(name CHAR(64), msg CHAR(255))

Asserts that the specified SEQ does not exist.

SEQUENCE

DDL

sequence_create(name CHAR(64), increment SMALLINT SIGNED, minvalue BIGINT SIGNED, maxvalue BIGINT SIGNED, cycle BOOLEAN, start BIGINT SIGNED, comment CHAR(64))

Creates a new SEQUENCE with the specified name, increment (that can be positive, negative, or even 0), minimum value, maximum value, initial value and comment. cycle means that the SEQUENCE is a loop, thus the value will rotate when it reaches the edge.

For parameters which have a default value, NULL can be passed to set the default. Default values are:

  • increment: +1
  • minvalue: 0 if increment is 0 or positive; -9223372036854775807 if increment is negative
  • maxvalue: 9223372036854775807 if increment is 0 or positive; -1 if increment is negative
  • cycle: FALSE
  • start: minvalue if increment is 0 or positive; maxvalue if increment is negative
  • comment: Empty string

If a SEQUENCE called name already exists, this Routine produces an error.

sequence_replace(name CHAR(64), increment SMALLINT SIGNED, minvalue BIGINT SIGNED, maxvalue BIGINT SIGNED, cycle BOOLEAN, start BIGINT SIGNED, comment CHAR(64))

Works exactly like sequence_create(), except that if a SEQUENCE called name already exists, it is replaced.

sequence_clone(old_name CHAR(64), new_name CHAR(64))

Duplicates a SEQUENCE. If a SEQUENCE called old_name does not exist, or a SEQUENCE called new_name already exists, this Routine produces an error.

void sequence_replace(name CHAR(64), start_value BIGINT UNSIGNED)

Works exactly like seq_create(), except that if a SEQUENCE called name already exists, it is replaced.

void sequence_drop(name CHAR(64))

Drops the SEQUENCE with the given name. If that SEQUENCE does not exist, returns an error.

void sequence_exists(name CHAR(64))

Returns TRUE if a SEQUENCE called name exists, otherwise returns FALSE.

void sequence_alter(name CHAR(64), increment SMALLINT SIGNED, minvalue BIGINT SIGNED, maxvalue BIGINT SIGNED, cycle BOOLEAN, start BIGINT SIGNED, comment CHAR(64))

Modifies the SEQUENCE having the specified name. All other parameters modify a SEQUENCE's property. NULL can be set for those parameters to set their default values. For a list of the default values, see sequence_create(). If a SEQUENCE with called name does not exists, produces an error.

void sequence_rename(old_name CHAR(64), new_name CHAR(64))

Renames the SEQUENCE called old_name to new_name. If a SEQUENCE called old_name does not exist, or if a SEQUENCE called new_name already exists, produces an error.

Usage

nextval(name CHAR(64))

Increments the specified SEQUENCE's value and returns it. If the specified SEQUENCE does not exist, an error is produced.

BIGINT SIGNED setval(name CHAR(64), new_value BIGINT SIGNED, is_called BOOL)

Changes the specified SEQUENCE's current value to new_value. If is_called is FALSE, when nextval() is called, it will increment the value before returning it (as usual). If is_called is set to TRUE, nextval() will return the value without incrementing it. new_value is returned.

Note that new_value is not validated by this Function. If the specified SEQUENCE does not exist, an error is produced.

currval(name CHAR(64))

If at least one value for the specified SEQUENCE has been generated by this session, the last value generated by this session is returned by this Function. That value will not be the SEQUENCE's current value, if the most recent value has been generated by another session.

If no value has been generated for the specified SEQUENCE during the current session, NULL is returned.

If the specified SEQUENCE does not exist, an error is produced.

lastval()

Returns last value generated for any SEQUENCE by this session.

Metadata

SEQUENCEs are stored in a table called SEQUENCES, in stk_type database. That table is mirrored by the SEQUENCES view, in the meta_schema database.

sequence_get(IN name CHAR(64), OUT increment SMALLINT SIGNED, OUT minvalue BIGINT SIGNED, OUT maxvalue BIGINT SIGNED, OUT cycle BOOLEAN, OUT comment CHAR(64))

Returns the property of the SEQUENCE identified by name into the following OUT parameters. For details about the parameters meaning, see sequence_create().

void show_sequences()

Shows all details about existing SEQUENCEs.

void show_sequences_like(pattern TEXT)

Shows all details about existing SEQUENCEs matching the specified pattern.

TEXT sequence_show_create(name CHAR(64))

Returns the SQL statement that can be used to re-create the specified SEQUENCE.

Additions to STK/Unit

Testing the use of SEQUENCEs in applications is made easier by some extra assert Procedures for STK/Unit. To add those Routines, the test/case/test_sequence.sql file must be executed.

void assert_sequence_exists(name CHAR(64), msg CHAR(255))

Asserts that the specified SEQUENCE exists.

void assert_sequence_not_exists(name CHAR(64), msg CHAR(255))

Asserts that the specified SEQUENCE does not exist.

void assert_sequence_currval(name CHAR(64), val BIGINT SIGNED, msg CHAR(255))

Asserts that the last value generated by the current session for the specified SEQUENCE is val.

void assert_sequence_lastval(val BIGINT SIGNED, msg CHAR(255))

Asserts that the last value generated by the current session for any SEQUENCE is val.

STK/SEQUENCE and STK/SEQ vs MariaDB Sequence Storage Engine

For a description of MariaDB's Sequence Storage Engine, see the KnowledgeBase.

Question: "What is better for me - STK/SEQUENCE library, or MariaDB Sequence Storage Engine?". The answer is: if MariaDB's Sequence is good for you… just use it! The performance of a Stored Routines library simply can't compete with a Storage Engine.

However, there are some limitations that affect Sequence SE but not STK/SEQUENCE:

  • Sequence SE is stateless, because it's created on the fly
  • Sequence SE can't rotate its value when it reaches the maximum value

The second limitation also applies to STK/SEQ, that has also the following limitations:

  • STK/SEQ can't have an increment different from 1

However, STK/SEQ is faster than STK/SEQUENCE.

Both STK/SEQUENCE and STK/SEQ have a state information stored on-disk, they have metadata which can be queried, and they can be altered, renamed, exported.


Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License