Add sequences

Sequences

A sequence is a construct that generates a sequence of numeric values, one at a time.  In general, each time the next value is requested for a sequence the increment is added to the last previously returned value and the sum is returned.  The increment may be negative for a descending sequence.  If a sequence is defined to not permit cycling, an error is returned once it reaches its maximum or minimum (descending sequences) value.  If it is defined to cycle, then the value wraps to the opposite end of the range.

A sequence's attributes may be altered including to restart from a particular value or to change magnitude and direction of the increment.

NOTE: A sequence that permits cycling may produce duplicate values.  Similarly if a sequence is restarted with a value that leads to overlapping with previously generated values, duplicates will be produced.  Altering a sequence to change the direction of the increment will too, without care, likely result in duplicate values being produced.

Sequences operate most effectively with an in memory cache of values.  The size of this cache is determined by the cache attribute.  A cache-size block of values is reserved by a node and requests for values are satisfied from this cache.  When exhausted, a new block of values is reserved.  Reserving a cache-block involves updating persistent storage hence caching offering a performance boost as it allows servicing values directly from memory.  It should however be noted that should a node shutdown or the sequence be altered, the unused values in the cached block are "lost"; on restart (or next value request) a new block will be reserved.  Along with expected usage patterns, cache sizes should be chosen with these facts in mind.

In a cluster with multiple Query service nodes, each node reserves an independent cache block to service its requests and thus values are not in global (i.e. cluster wide) strict order, though they are globally unique and in per-node order. 

Sequences are stored in the bucket's system scope so backup and restore operations are handled automatically with bucket backup and restore.

Sequences may not be used in WHERE or ON clauses (semantic error 3100).

CREATE SEQUENCE [IF NOT EXISTS] <name> [IF NOT EXISTS] [ WITH <options>]
CREATE SEQUENCE [IF NOT EXISTS] <name> [IF NOT EXISTS] [START WITH <num>][INCREMENT BY <num>][MAXVALUE <num>|NO MAXVALUE][MINVALUE <num>|NO MINVALUE][CYCLE|NO CYCLE][CACHE <num>|NO CACHE]

Create a new sequence.  The two syntax forms are equivalent.  IF NOT EXISTS may appear either before or after the sequence name, but not in both locations.

<name>  Sequence name, resolving to namespace:bucket.scope.sequence.  (Typically resolution is via the query_context.)  Sequence names must be unique within a scope.

<options> An object with the following optional fields:

   start     Number     (default: 0)
      The starting value for the sequence.
      Equivalent to: START WITH <num>

   increment Number     (default: 1)
      The step size for the sequence.
      Equivalent to: INCREMENT BY <num>

   max       Number     (default: signed 64-bit integer maximum)
      The maximum value allowed to be produced by the sequence.
      Equivalent to: MAXVALUE <num>

   min       Number     (default: signed 64-bit integer minimum)
      The minimum value allowed to be produced by the sequence.
      Equivalent to: MINVALUE <num>

   cycle     boolean    (default: false)
      If the sequence should permit cycling past its limits or not.
      Equivalent to: CYCLE or NO CYCLE

   cache     Number > 0 (default:50)
      The cache size.
      Equivalent to: CACHE <num>

NO MAXVALUE is the same as MAXVALUE (signed 64-bit integer maximum)
NO MINVALUE is the same as MAXVALUE (signed 64-bit integer minimum)
NO CACHE is the same as CACHE 1

Example:

   CREATE SEQUENCE IF NOT EXISTS myBucket.myScope.ordNum START WITH 1000 CACHE 100;

ALTER SEQUENCE <name> [WITH <options>]
{{{}ALTER SEQUENCE <name> [RESTART [WITH <num>]][INCREMENT BY <num>][MAXVALUE <num>|NO MAXVALUE][MINVALUE <num>|NO MINVALUE][CYCLE|NO CYCLE][CACHE <num>|NO CACHE{}}}]

Alter an existing sequence.  The two syntax forms are equivalent.

<name> Sequence name resolving to a full existing sequence name.

<options> An object with the following optional fields:

   restart   Number
      The value for the sequence to restart from.
      Equivalent to: RESTART WITH <num>

   increment Number
      The step size for the sequence.
      Equivalent to: INCREMENT BY <num>

   max       Number
      The maximum value allowed to be produced by the sequence.
      Equivalent to: MAXVALUE <num>

   min       Number
      The minimum value allowed to be produced by the sequence.
      Equivalent to: MINVALUE <num>

   cycle     boolean
      If the sequence should permit cycling past its limits or not.
      Equivalent to: CYCLE or NO CYCLE

   cache     Number > 0
      The cache size.
      Equivalent to: CACHE <num>

RESTART without specifying WITH <num> restarts the sequence from the starting value the sequence was created with.

Example:

   ALTER SEQUENCE myBucket.myScope.ordNum WITH {"max": 2000};

DROP SEQUENCE <name> [IF EXISTS]

<name> Sequence name resolving to a full existing sequence name.

Example:

   DROP SEQUENCE myBucket.myScope.ordNum IF EXISTS;

NEXT VALUE FOR <name>
NEXTVAL FOR <name>

(equivalents)

The NEXT VALUE FOR generates the next value for the sequence but only once per document.  The scope of the document is the scope of the statement.  Subqueries operate on independent documents to containing queries and therefore step the sequence independently.

Example:
   (with query_context set to default:myBucket.myScope)

   INSERT INTO orders VALUES (uuid(),{"num":NEXT VALUE FOR ordNum,"customer":"Sam"}) RETURNING *;

    "results": [

    {

        "orders": {

            "customer": "Sam",

            "num": 1000

        }

    }

    ],

    INSERT INTO orders VALUES (TO_STRING(NEXTVAL FOR ordNum),{"num":NEXTVAL FOR ordNum,"customer":"Bobby"}) RETURNING meta().id,*;

    "results": [

    {

        "id": "1002",

        "orders": {

            "customer": "Bobby",

            "num": 1003

        }

    }

    ],

   NOTE: The sequence was stepped twice here since the key is not part of the document. To achieve the likely intent here, use an INSERT-SELECT so both operations are part of the same document:

    INSERT INTO orders (KEY k, VALUE v) SELECT TO_STRING(NEXTVAL FOR ordNum) k,{"num":NEXTVAL FOR ordNum,"customer":"Bobby"} v RETURNING meta().id,*;    

    "results": [

    {

        "id": "1004",

        "orders": {

            "customer": "Bobby",

            "num": 1004

        }

    }

    ],

PREV VALUE FOR <name>
PREVIOUS VALUE FOR <name>
PREVVAL FOR <name>

(equivalents)

PREV VALUE FOR returns - in order of precedence - either:
   i) If one has been, the last value generated for the document.
  ii) If in a transaction and one has been, the last value generated in the transaction.
 iii) The last value generated for the sequence on the node.

Example:
   (with query_context set to default:myBucket.myScope)

   BEGIN WORK;
   INSERT INTO orders VALUES (uuid(),{"num":NEXT VALUE FOR ordNum,"customer":"Alex"});
   INSERT INTO items VALUES (uuid(),{"order_num":PREVVAL FOR ordNum,"item_num":1,"description":"Widget One"});
   INSERT INTO items VALUES (uuid(),{"order_num":PREVVAL FOR ordNum,"item_num":2,"description":"Widget Two"});
   COMMIT;

   SELECT o.customer, ARRAY_AGG(i.description) items FROM orders o, items i WHERE o.num = i.order_num GROUP BY o.num, o.customer;

    "results": [
   

    ],

Add to the reserved words list:

SEQUENCE, CACHE, RESTART, MAXVALUE, MINVALUE NEXT, PREV, PREVIOUS, NEXTVAL and PREVVAL done