# Using Sequences in SQL

## Inserting and Updating Sequences with SQL

> Sequences can be inserted or updated through SQL in four different ways:

> > 1) As a scalar value:

> >         INSERT OR UPDATE INTO t VALUES (1, 1);

> > 2) As an array:

> >         INSERT OR UPDATE INTO t VALUES (1, [1,2,3]);

> > 3) As a string:

> >         INSERT OR UPDATE INTO t VALUES (1, '{1,2,3}');

> > 4) As a result of a subquery:

> >         INSERT OR UPDATE INTO t SELECT * FROM tt;

> In eXtremeSQL releases prior to 7.1, when methods 1 through 3 were used, the sequence was appended to, while in the 4th method (subquery) the sequence was overwritten. With release 7.1 and later this behavior has been changed and is now regulated through the `append_mode` parameter: if `append_mode == true`, the sequence is appended to (which is the default behavior), otherwise overwritten. The `append_mode` parameter is modified via the SQL set command:

>         set append_mode true

> The `INSERT OR UPDATE` statement can also be used to insert elements at random positions inside sequences (eXtremeSQL allowes out-of-order modifications of sequences). This feature is useful for filling the "gaps" in ordered sequences (e.g. when some records in a time series are missing and must be filled in later). However, it is subject to the following limitations:

> - the `append_mode` parameter must be set to `true`, otherwise the sequences will be overwritten with the new data;
> - the first field (or fields) of the table must be the primary key;
> - the first sequence must be ordered;
> - when inserting a range of elements, it must not overlap with existing elements;
> - as a corollary, multiple ranges cannot be inserted with a single query if they overlap with existing elements;
> - the elements may only be inserted from another (intermediate) table.

> Note that consistency of the sequences must be ensured by the application; eXtremeSQL does not enforce any constraints (apart from ordering where explicitly defined), which means that it is up to the application to define the semantics of the data. For example, given three sequences `timestamp`, `bid`, and `ask`, the application must see to it that all three of them are updated (appended to) when needed.

> The `seq_remove()` function may be used to remove elements from sequences. It accepts a single argument which must be a projection of elements to be removed from the sequence, and returns the number of elements removed.

> ## Inserting and Removing from Sequences Example

> ### Step 1: Create and populate table Quote

> Create and populate table Quote with `day`, `open` and `close` sequences, indexed by symbol name (`sym`). As per the aforementioned requirements, the first field of the table (`sym`) is the primary key, others are sequences; the first sequence (`day`) is ordered (ascending). Notice that the sequence data contains a gap between `20170505` and `20170509`, which we will fill in, in Step 2. Also notice that the parameter `append_mode` is explicitly set to `true`.

>         set append_mode true;
>         create table Quotes(sym string primary key,
>             day sequence(int asc),
>             open sequence(int),
>             close sequence(int));
>         insert into Quotes values('AAA',
>             [20170501, 20170502, 20170503, 20170504, 20170505, 20170509, 20170510],
>             [101, 102, 103, 104, 105, 109, 110],
>             [111, 112, 113, 114, 115, 119, 120]);
>         insert into Quotes values('BBB',
>             [20170501, 20170502, 20170503, 20170504, 20170505, 20170509, 20170510],
>             [201, 202, 203, 204, 205, 209, 210],
>             [211, 212, 213, 214, 215, 219, 220]);
>         insert into Quotes values('CCC',
>             [20170501, 20170502, 20170503, 20170504, 20170505, 20170509, 20170510],
>             [301, 302, 303, 304, 305, 309, 310],
>             [311, 312, 313, 314, 315, 319, 320]);

> ### Step 2: Create temporary table QuoteHorizontal

> Create a temporary table QuoteHorizontal containing the missing elements and populate table Quotes from QuoteHorizontal using the `insert or update` statement with `order by` clause (notice that the `order by day` clause is required to properly order the elements):

>         create table QuotesHorizontal(sym string , day int, open int, close int);
>         insert into QuotesHorizontal values('AAA', 20170506, 106, 116),
>             ('BBB', 20170506, 206, 216), ('CCC', 20170506, 306, 316);
>         insert into QuotesHorizontal values('AAA', 20170507, 107, 117),
>             ('BBB', 20170507, 207, 217), ('CCC', 20170507, 307, 317);
>         insert into QuotesHorizontal values('AAA', 20170508, 108, 118),
>             ('BBB', 20170508, 208, 218), ('CCC', 20170508, 308, 318);
>         insert or update into Quotes select * from QuotesHorizontal order by day;

> ### Step 3: Drop some data from the sequences

> At this point there are no gaps in the sequence data. We will use the `seq_remove()` function to drop some data from the sequences, e.g. from May 1st to May 3rd. Note that the `for update` clause is required to modify the sequences (see the SQL DML Select page for further details regarding the `for update` clause):

>         select !seq_search(day, 20170501, 20170503) as daterange,
>             seq_remove(day@daterange), seq_remove(open@daterange),
>             seq_remove(close@daterange)
>         from Quotes for update;

> (Note that the operator "!" is a shortcut for `seq_ignore()`. The ignored column may be used in other calculated columns but will not appear in the actual statement output.)

> ## Parallel processing with sequences

> Support for the parallel processing of sequences is implemented through a thread pool in the SQL engine that can optimize performance for sequence operations in multi-processor environments. The thread pool allows sequence functions to split sequences into equal parts across threads and perform calculations on the threads in parallel reducing the total execution time.

> The size of the thread pool can be regulated by the environment variable `MCO_CPU_NUMBER`. If no `MCO_CPU_NUMBER` environment variable is present, then the total number of CPU cores on the system is used as the pool size. If the hardware allows Hyper-Threading (HT) - and most modern CPUs do - the number of threads in the pool is equal to the number of HT cores (normally two per physical core). So if the machine has 4 real cores, the number of threads is 8. This can actually hurt performance sometimes.

> The xSQL configuration file the parameter `sql_use_connection_pool` can disable the use of the thread pool. Note that the default value of this parameter is true (meaning the thread pool is created on startup).

>         sql_use_connection_pool:false

> At runtime the function `seq_parallelism()` can be used to limit the number of threads utilized to run queries. The function accepts a number of threads to use as a parameter and returns the total number of threads in the pool.

>         seq_parallelism(<n_threads>)

> The value 1 for `n_threads` will effectively turn off the thread pool processing. For example, the following command sets the thread pool size to use 4 CPU cores:

>         XSQL> SELECT seq_parallelism(4);

> Note that function `seq_parallelism()` affects the entire server, not just a single client’s session. So `seq_parallelism()` should not be executed from different clients running concurrently. Also note that if the number of parallel threads requested by functions `seq_parallelism()` exceeds the size of the pool specified in the environment variable `MCO_CPU_NUMBER` (the default is the number of available cores), the SQL engine will use the number specified in `MCO_CPU_NUMBER`.

> ### A Note on thread pool management on Windows

> Support for the parallel processing of sequences is implemented thorough the class ThreadPool, which instantiates a global variable `ThreadPool`. Thus the destructor for the ThreadPool class is called upon exit from the `main()` automatically. The destructor obviously frees up all resources associated with the pool: memory, synchronization primitives, and tasks. This implementation presents a problem with the Windows C++ runtime. The Windows C++ runtime is implemented so that it stops all threads except for the main thread when the application exits from its `main().` The C runtime then calls the destructors for all global objects, which in the case of the `ThreadPool` tasks is too late - those tasks were already stopped by the ThreadPool destructor.

> A partial solution has been implemented to simply not call the ThreadPool destructor (only when running on Windows). It is remotely possible to introduce a resource leak in a scenario in which the database is created in the context of the xSQL server (or a .NET Framework or JNI server application) that loads the database DLL (`mcoxsql.dll` or `mcoxsql_debug.dll`) dynamically at runtime. If the database makes use of sequences and the server reloads the DLL multiple times, each time a resource leak is created.

> A more resilient solution for this problem is forthcoming in a future release, but at present Windows developers should be aware of this potential problem.

> ## Null Values in Sequences

> Null values can be inserted into or appended to sequences. For example:

>         XSQL>set append_mode true;
>         XSQL>create table hor(i integer, f float);
>         XSQL>insert into hor values(1, 100.0);
>         XSQL>insert into hor values(1, NULL);
>         XSQL>insert into hor values(1, 300.0);
>         XSQL>insert into hor values(1, NULL);
>         XSQL>create table ver(i integer primary key, f sequence(float));
>         XSQL>insert or update into ver select * from hor;

> Now the sequence field `f` in table `ver` has the expected values:

>         XSQL>select * from ver;
>         i	f
>         ----------------------------------------------------------------
>         1	{100, null, 300, null}

> Using the `FLATTENED` keyword, the sequence can be displayed as follows:

>         XSQL>select flattened * from ver;
>         i	f
>         ----------------------------------------------------------------
>         1	100
>         1	null
>         1	300
>         1	null

> Note that it is not allowed to insert null values into ordered sequences, which is expected behavior. An attempt to do so will cause the SQL compiler to emit an error. For example:

>         XSQL>insert into ver values(1, [1,2,3,null,5]);
>         ERROR: Sequence item is out of order

> ### Null Values with Sequence Functions

> Where it makes sense, sequence functions support null values. For example, using the table `ver` defined above with the same inserted values:

>         XSQL>select seq_last(f) from ver where i=1;
>         #1
>         ----------------------------------------------------------------
>         null

>         XSQL>select seq_first(f) from ver where i=1;
>         #1
>         ----------------------------------------------------------------
>         100

> The following SQL functions (and their xSQL counterparts) do not support sequences with null values, and hence will return `MCO_E_SEQ_NULL_VALUE` if called on a sequence with nulls:

> SQL | xSQL
> --- | ---
> mco_seq_window_agg*() | seq_window_agg*()
> mco_seq_group_agg*() | seq_group_agg*()
> mco_seq_diff() | seq_diff()
> mco_seq_map() | seq_map()
> mco_seq_union() | seq_union()
> mco_seq_cross() | seq_cross()
> mco_seq_extrema() | seq_extrema()
> mco_seq_stretch*() | seq_stretch*()
> mco_seq_asof_join() | seq_asof_join()

> ### Inserting Null Values with Array Syntax

> There is a use case which is potentially confusing when inserting null values into sequences: when a single literal null value is inserted using the `INSERT OR UPDATE` clause, it sets the sequence field's value to null, instead of appending the null to the sequence. For example:

>         XSQL>set append_mode true;
>         XSQL>create table ver(i integer primary key, f sequence(float));
>         XSQL>insert or update into ver values(1, [1,2,3]);
>         XSQL>insert or update into ver values(1, null);
>         XSQL>select * from ver;

>         i	f
>         ----------------------------------------------------------------
>         1	null

> If the null value needs to be appended to the sequence, the array syntax must be used. For example:

>         XSQL>set append_mode true;
>         XSQL>create table ver(i integer primary key, f sequence(float));
>         XSQL>insert or update into ver values(1, [1,2,3]);
>         XSQL>insert or update into ver values(1, [null]);
>         XSQL>select * from ver;

>         i	f
>         ----------------------------------------------------------------
>         1	{1, 2, 3, null}

> If the initial sequence is empty, the latter syntax will yield a sequence with single null element. For example:

>         XSQL>set append_mode true;
>         XSQL>create table ver(i integer primary key, f sequence(float));
>         XSQL>insert or update into ver values(1, [null]);
>         XSQL>select * from ver;
>         i	f
>         ----------------------------------------------------------------
>         1	{null}

> Please note how this is different from the very first case. The first case sets the sequence field's value to null, while using the array syntax results in a sequence with one null element.

Related Topics