`SET` and `SETQ` data types

In a typical database, each field – the intersection of row and column – contains an individual value.

FeatureBase SET and SETQ data types are designed to contain an array of comma-separated values within each field.

This means:

data does not need to be normalized into separate tables,
import/ingest actions are faster because there are less rows to import,
queries are faster

Syntax

{ID | STRING}SET{Q TIMEQUANTUM '<date-unit>' [TTL '<int-value><time-unit>']}

Arguments

Argument	Description	Required?	Default	Additional information
`ID`	Values to be inserted conform to `ID` data type			ID data type
`STRING`	Values to be inserted conform to `STRING` data type			STRING data type
`SET`	Comma-separated array of values	Yes
`SETQ`	Comma-separated array of values identified by a Unix-epoch or ISO-8601 timestamp			TIMESTAMP data type
`TIMEQUANTUM`	`SETQ` constraint that creates views on `SETQ` data for each `<date-unit>`	Y		TIMEQUANTUM views * TIMEQUANTUM view deletion
`<date-unit>`	One or more sequential, descending date units, defined as `Y`, `M`, `D`, `H`	Y		TIMEQUANTUM views
`TTL`	Governs automatic deletion of `TIMEQUANTUM` views	Optional	`0s` (disables `TTL`)	* TTL (Time To Live) * TIMEQUANTUM view deletion
`'<int-value><time-unit>'`	String literal that requires a positive integer with time-unit	Y	`'0s'`	TTL (Time To Live)

Additional information

INSERT and UPSERT behaviour

INSERT or UPSERT behaviour occurs when any of the following are true:

Change type	`_id`	`TIMEQUANTUM`	`SET` values
INSERT	New	New	New
UPSERT	Existing	Existing	New

TIMEQUANTUM views

A TIMEQUANTUM view is created for each <date-unit> defined in a CREATE TABLE statement

Name	`<date-unit>` declaration	View timestamp
Year	`Y`	YYYY-01-01T00:00:00.000Z
Month	`M`	YYYY-MM-01T00:00:00.000Z
Day	`D`	YYYY-MM-DDT00:00:00.000Z
Hour	`H`	YYYY-MM-DDTHH:00:00.000Z

TIMEQUANTUM views:

improve the responsiveness of Range queries where a query uses the same combination of date units
increase storage overheads which may require the use of TTL(time-to-live)

TTL (Time To Live)

An integer and time unit are used to calculate the number of seconds before a TIMEQUANTUM view can be deleted.

TTL defaults to 0s which means:

No TIMEQUANTUM views are deleted,

Disk requirements will increase

FeatureBase recommends TTL '1h' for best results

Name	`<time-unit>` declaration
Hour	`h`
Minute	`m`
seconds (default)	`s`
milliseconds	`ms`
microseconds	`us`
nanoseconds	`ns`

`TIMEQUANTUM` view deletion

To determine when TIMEQUANTUM views are deleted, FeatureBase performs the following operations:

Timestamp values are converted to Unix-epoch seconds since 1970-01-01
TTL is converted to seconds, regardless of its <time-unit>

If the following equation is true, the TIMEQUANTUM view is deleted:

<database-timestamp> - <view-timestamp> >= <ttl-value>

TIMEQUANTUM view deletion may not occur immediately because:

the database timestamp is governed by the vendor region
views may contain large quantities of data

Value definition

Use the following syntax to INSERT or BULK INSERT values to SET and SETQ columns in a target table:

Column data type	Assignment	Additional information
`SET`	`[<value>,...]`
`SETQ`	`{<timestamp>,[<value>,...]}`

Use semicolons to separate values in a CSV data source intended for SET and SETQ columns. e.g., <previous-value>,<value>;...,<next-value>

GROUP BY issues on SET and SETQ data types

GROUP BY issues for SET and SETQ data types

Examples

create table allcoltypes
  (
    _id id,
    intcol int min 0 max 10000,
    boolcol bool,
    timestampcol timestamp timeunit 'ms',
    decimalcol decimal(2),
    stringcol string,
    stringsetcol stringset,
    stringsetcolq stringsetq timequantum 'YMD' ttl '24h',
    idcol id,
    idsetcol idset,
    idsetcolq idsetq timequantum 'YMD' ttl '24h',
    vectorcol vector(768)
  )
  with comment 'table containing all column types';

INSERT statement

INSERT INTO all-datatypes (_id, idsetcol, idsetqcol, stringsetcol, stringsetqcol) VALUES

(1, [10,20,30],
{1709706283,[90,80,70]},
['ten', 'twenty', 'thirty'],
{'2024-03-06T06:24:43.261Z',['ninety','eighty','seventy']});

SET and SETQ data types