Using SWC-DB SQL

Table of Contents

• The Syntax Structure of Forms/Types
  • The Schema Syntax
  • The Comparators syntax
  • The Column Selector Syntax
• The Available Commands
• The Commands
  • Create/Modify/Remove Columns
  • Get Columns
    • A get columns example
  • Compact Columns
    • A compact columns example
  • Select Query
    • The Select Query syntax
    • The Columns-Intervals syntax
    • The Column-Intervals syntax
    • The Cells-Interval syntax
    • The Condition-Range syntax
    • The Key-Intervals syntax
    • The Condition-Key-Interval syntax
    • The Condition-Key syntax
    • The Condition-Value syntax
    • The Condition-Value-Expression syntax
    • The Condition-Timestamp syntax
    • The Condition-Offset-Key syntax
    • The Condition-Offset-Revision syntax
    • The Flags syntax
    • The Update Options syntax
  • Update Query
    • The Update Query syntax
    • The Cell for Update syntax
    • A DELETE_LE Flag
    • A DELETE_EQ Flag
    • An INSERT Flag with auto-timestamp
    • An INSERT Flag with version-timestamp config
    • An INSERT Flag for a SERIAL Column Type
    • An INSERT Flag with Encoded-Value

Syntax Structure of Forms/Types

The Schema Syntax

The Schema Definition is a word-separator set of key=value in round-brackets (key=value ) .
The key fields:

The Comparators syntax

The Column Selector Syntax

The Column Selector Syntax is comma-separated values with available select options:

• cid - an exact match of column cid
• name - an exact match on column name
• comparator with name - a column name pattern selector
• tags - a comparator to square-brackets of tags-list and inner pattern comparator for every tag-string

The following Syntax, to the comma-separated values, rules applied:

1. space/tab/newline not in-quotes is ignored/skipped.
2. a digit-value without quotes is evaluated as cid.
3. a string-value without quotes is at first evaluated for Comparator and rest is value (eg. rrest is RE with value “rest” whereas if the required is -EQ “rrest” the string should be qouted).
4. an overall or a reserved, for non-qouted values, token for lookup is the tags token and each tag in the tags-list is evaluate by rule-3.

As example, the Selector nameOne, 2, =^'test', tags%>[2,v>"5",=1] result will be the columns: a column with column-name “nameOne”, a column with cid “2”, columns with column-name starts-with(prefix) “test” and columns with tags consist of tags “2”,”1” and above volumetric “5” and all the tags are being the superset to the subset.

Only the exact match of cid and name are options to be first probed on the cache of the application's(Clients) Schemas-Cache rest are made with a request to a Schemas Role Manager.

The Available Commands

• Columns Management Commands :
  • Create Column - [Add / Create] [Column / Schema] - Create a new Column.
  • Modify Column - [Modify / Update] [Column / Schema] - Modify an exting Column Schema.
  • Remove Column - [Remove / Delete] [Column / Schema] - Remove a Column.
  • Get Columns - [Get / List] [Column/s / Schema/s]/ - List all or the requested columns.
  • Compact Columns - Compact [Column/s / Schema/s] - Compact all or the requested columns.
• Data Commands:

  • Select [where_clause] - A Query command to scan and select cells.

  • Update [cell] - A Query command to update (insert/delete) cells.

The Commands:

Create/Modify/Remove Columns

These commands have aliases for Verb and for Noun:

• create == add
• modify == update
• remove == delete
• remove == delete
• column == schema

The Syntax of the Create/Modify/Remove Column command [The Command] [(The Schema Definition )]

On Error, the command returns/throw the associated Exception.

the minimal required definitons:

• Create Column requires column-name name=theColumnName
• Modify Column requires column-id and column-name cid=CID name=theNewOrExistingName
• Remove Column requires column-name and column-id name=theColumnName cid=CID

a create column example:

create column(
  name="anExampleColumnName"
  seq=FC_VOLUME
  type=PLAIN
)

a modify column example:

modify schema(
  cid=A-CID
  name="anExampleColumnName"
  seq=VOLUME
  type=PLAIN
  revision=input/auto-assigned
  compact=0
  cell_versions=1
  cell_ttl=0
  blk_encoding=ZSTD
  blk_size=0
  blk_cells=0
  cs_replication=0
  cs_size=0
  cs_max=0
  log_rollout=0
  log_compact=0
  log_preload=0
)

a remove column example:

remove column(
  cid=AN-ID
  name="anExampleColumnName"
)

Get Columns

The command has aliases for Verb and for Noun:

• get == list
• column == schema
• columns == schemas

The Syntax of the Get Column command [The Command] and, optionally in round-brackes () a Column Selector Syntax.

COMPARATOR'expr' is not applicable for SYS_ name-type columns.
The response is All columns if the Column Selector is not applied.

On Error, the command returns/throw the associated Exception.

a get columns example:

get columns 1,SYS_MASTER_VOLUME, =^test

The expected response will be schemas of column-cids 1, 2 and the ones matching on test prefix

Compact Columns

The command has aliases for Noun:

• columns == schemas

The Syntax of the Compact Columns command [The Command] and, optionally in round-brackes () Column Selector Syntax.

If the Column Selector is not applied, the request is to compact all columns.

a compact columns example:

get compact 1,SYS_MASTER_VOLUME

The expected response will be a list of Compact-Result [(cid=1 err=CODE), (cid=2 err=CODE)]

Select Query

The Select Query command perform scans and select cells by the applied query.

• An Example:

  select where
  col(ColNameA1) = (
    cells = (
      range >= ['1-'] AND [>='1-'] <= key = [<='1-1-', ="1" ] AND  value = "Value-Data-1"
      AND timestamp > “2010/05/29" AND offset_key = ["1-0"] AND offset_rev = 000111222
      limit=10 max_versions=2
    )
  )
  AND col(ColNameB1, ColNameB2) = (
    cells = ( [>='2-'] <= key = [<='2-2-',"1"] AND value = "Value-Data-2"  AND timestamp > "2010/05/29" )
    AND cells = ( key = [<='21-',"1"] AND timestamp > "2010/05/29" )
  )
  max_versions=1
  

• The Select Query syntax

  select where [Columns-Intervals] [Flags(global-scope)]

• The Columns-Intervals syntax

  The Columns-Intervals is a grouping of Column-Intervals with the TOKEN AND, if several Column-Intervals are required.
  [Column-Intervals] [AND] [Column-Intervals]

• The Column-Intervals syntax

  col(Column Selector Syntax ) = ( [Cells-Intervals] )

• The Cells-Intervals syntax

  The Cells-Intervals is a grouping of Cells-Interval with the TOKEN AND, if several Cells-Interval are required.
  [Cells-Interval] [AND] [Cells-Interval]

• The Cells-Interval syntax

  The Cells-Interval is a group of conditions, joined by the TOKEN AND plus Flags, for matching cells against it.
  All the Conditions, Flags and Options are optional, without a LIMIT and any Conditions the condition is equal to select all cells in the column/s .
  cells=( [ Condition-Range ] [AND] [ Key-Intervals ] [AND] [ Condition-Value ] [AND] [ Condition-Timestamp ] [AND] [ Condition-Offset-Key ] [AND] [ Condition-Offset-Revision ] [ Flags(cells-interval-scope)] [ Options] )

• The Condition-Range syntax

  The Condition of Range is an interval of from Key to Key, optionally to apply only one side with Key on the right side. It can be defined only by the Comparators GE and LE.
  Key [<=] range [<=] Key

• The Key-Intervals syntax

  The Key-Intervals are several or single Condition-Key-Interval, joined by the TOKEN AND. Whereas the given order is the matching order which let further matching of a cell-key, such as 1st(Condition-Key-Interval) is based on a sequential Comparators while the 2nd and followed(Condition-Key-Interval) include/involve non-sequential Comparators such as Regexp.
  The 1st Condition-Key-Interval is the main(after Condition-Range) Interval used by/for locators of Range & Range-Blocks.
  [ Condition-Key-Interval ] ... [AND] [ Condition-Key-Interval ] [AND] [ Condition-Key-Interval ]

• The Condition-Key-Interval syntax

  The Condition of Key Interval is an interval of from Condition-Key to Condition-Key, optionally to apply only one side with Condition-Key on the right side. It can be defined only by the Comparators EQ, GE and LE.
  The Exact Cell Key match condition is when Comparator is EQ and Condition-Key is set with EQ on all the Fractions.
  [ Condition-Key] [<=] key [<=] [ Condition-Key]

• The Condition-Key syntax

  The Condition of Key is the Fractions in square-brackets with each Fraction having an option to match on Comparator with Value/Expression. Without a Comparator applied to a Fraction the Comparator is auto-set to EQ.
  Optionally to select all the inner/deeper-level fractions including or excluding the prior Fraction by setting last fraction with Comparators FI or FIP.
  [ COMP"F1", COMP"F2", COMP"F3", COMP"F4", COMP"F5", COMP"F6", COMP"F7", :<"" ]

• The Condition-Value syntax

  The Condition of Value, a single or several Value Conditions joined by an AND TOKEN. The Comparator is auto-set to EQ, If no Comparator was applied.
  value Condition-Value-Expression... [AND] value Condition-Value-Expression [AND] value Condition-Value-Expression

• The Condition-Value-Expression syntax

  The Expression of Value Condition dependable on the Schema's column value type.

  • PLAIN:
    COMP "VALUE" - applicable with Extended Comparators

  • COUNTER:
    COMP "VALUE" - not supported Comparators PF, RE, POSBS and POSPS

  • SERIAL:
    [ID:TYPE:COMP "VALUE", ... ] - in square-brackets a comma-separated sets, a set is separated by colon with Field-ID, Field-Type and a Comparator with a Value.
    The applicable Comparators depend on the Field-Type: BYTES(B) as PLAIN, INT64(I)/DOUBLE(D) as COUNTER, KEY(K) with KeySeq(LEXIC/VOLUME) followed by a Condition-Key and in list-syntax COMP[COMP VALUE, .. ] LIST_INT64(LI) Value as COUNTER and LIST_BYTES(LB) Value as PLAIN.
    The SERIAL match requires all field-definitions matching ID+TYPE+COND, whereas Field-ID can have multiple Field-Type and Value definitions.
    A data-set Example, a cell-value:
    TS KEY [0:I:1, 1:I:5, 2:I:1, 3:D:1.0, 4:B:"aBcdef", 5:K:[abc,def,ghi,4,5], 6:LI:[1,2,3,4,5,6,7], 7:LB:[abc,def], 8:B:"More-Bytes]
    can have the following Condition-Value syntax:
    value == [0:I:==1, 1:I:>4, 2:I:<5, 3:D:>0.123456, 4:B:=^aBc, 5:K:VOLUME[abc,def,ghi,>=""], 6:LI:<=[1,2,3,>0,5,6,==7,0], 7:LB:%>[~>ef,~>ac] ]

    • In this case Field with ID=8 of BYTES type does not require the expression match.

• The Condition-Timestamp syntax

  The Condition of Timestamp is an interval of from Timestamp to Timestamp, optionally to apply only one side with VALUE on right. It can be defined only by the Comparators NE, EQ, GT, LT, GE and LE. The VALUE can be a Timestamp in nanoseconds or a Date and Time in format "YYYY/MM/DD HH:mm:ss.nanoseconds".
  VALUE [COMP] timestamp [COMP] VALUE

• The Condition-Offset-Key syntax

  The Condition of the Offset Key is a Key with one Comparator option Equal. The Cell of the Key will be evaluated for select-match if Offset Revision was not specified.
  offset_key = Key

• The Condition-Offset-Revision syntax

  The Condition of the Offset Revision is the Cell Timestamp with one Comparator option Equal. The Timestamp is in Nanoseconds or Date & Time, the offset_rev is the timestamp of the last cell that will be skipped before evaluating for select-match.
  offset_rev = Timestamp

• The Flags syntax

  The following flags, token and key=value, are available:
  LIMIT=I64 OFFSET=I64 MAX_VERSIONS=I64 MAX_BUFFER=I64 ONLY_KEYS ONLY_DELETES.

  • On global-scope the flags applied to all Cells-Interval without a Cells-Interval-scope flags.
  • On Cells-Interval-scope the flags applied only to the Cells-Interval scope.

• The Update Options syntax

  The Select Query can be applied with DELETE_MATCHING or UPDATE [OP] (TIMESTAMP, VALUE, ENC) options for updating a cells-interval.

  • The supported UPDATE operations(OP) :

    syntax	name	supported column-types	description
    =	REPLACE	PLAIN, SERIAL, COUNTER	replace with the update value (default as well if other OP not supported by the col-type)
    +=	APPEND	PLAIN, SERIAL	appends the update value to the cell's current value
    =+	PREPEND	PLAIN, SERIAL	prepends the update value to the cell's current value
    +:#	INSERT	PLAIN	inserts the update value at position in current value (appends if pos above value)
    =:#	OVERWRITE	PLAIN	overwrites the current value at position with new value (appends if pos above value)
    ~=	SERIAL	SERIAL	update is done by the inner serial-fields defintions (UPDATE[OP](TS,[ID:TYPE:[OP]val, ..]))

  • The supported UPDATE operations(OP) for numberic with MATH-op in the inner SERIAL fields:

    syntax	name	supported field-types	description
    =	EQUAL	INT64, DOUBLE	set field value to the new value (default)
    +=	PLUS	INT64, DOUBLE	plus new value to field's value (negative number allowed)
    *=	MULTIPLY	INT64, DOUBLE	multiply current value by update value
    /=	DIVIDE	INT64, DOUBLE	divide current value by the new value (ignored at zero)
    !	CTRL_NO_ADD_FIELD	INT64, DOUBLE	Applicable infront of other OPs. A control operation, in case a field for update does not exist, to not add a new field
    DEL	CTRL_DELETE_FIELD	INT64, DOUBLE	delete the given field (without value after OP)
    I	CTRL_VALUE_SET	INT64, DOUBLE	add/set if not exists (only available with used by in List at BY_UNIQUE OR BY_COND)
    O	CTRL_VALUE_DEL	INT64, DOUBLE	delete any that exist (only available with used by in List at BY_UNIQUE OR BY_COND)

  • The supported UPDATE operations(OP) for array/list/bytes with LIST-op in the inner SERIAL fields:

    syntax	name	supported field-types	description
    =	REPLACE	BYTES, LIST_BYTES, LIST_INT64	replace with the update value (default)
    +=	APPEND	BYTES, LIST_BYTES, LIST_INT64	appends the update value to a field value
    =+	PREPEND	BYTES, LIST_BYTES, LIST_INT64	prepends the update value to a field value
    +:#	INSERT	BYTES, LIST_BYTES, LIST_INT64	insert the update value at position in a field value (appends if pos above value)
    =:#	OVERWRITE	BYTES, LIST_BYTES, LIST_INT64	overwrite a field value at position with new value (appends if pos above value)
    -:#	ERASE	BYTES, LIST_BYTES, LIST_INT64	erases the position in a field value
    !	CTRL_NO_ADD_FIELD	BYTES, LIST_BYTES, LIST_INT64	except for BY_INDEX, Applicable infront of other OPs, a control operation, in case a field for update does not exist, to not add a new field
    DEL	CTRL_DELETE_FIELD	BYTES, LIST_BYTES, LIST_INT64	delete the given field (without value after OP)
    I	CTRL_VALUE_SET	BYTES	add/set if not exists (only available with used by in List at BY_UNIQUE OR BY_COND)
    O	CTRL_VALUE_DEL	BYTES	delete any that exist (only available with used by in List at BY_UNIQUE OR BY_COND)
    U~	BY_UNIQUE	LIST_BYTES, LIST_INT64	the field value items have CTRL_VALUE_SET/DEL OP
    C~	BY_COND	LIST_BYTES, LIST_INT64	the field value items have CTRL_VALUE_SET/DEL OP and [COMP] (UPDATE~=(TS,[ID:LI:C~[[OP][COMP]val, [OP][COMP]val, ..] ]))
    ?:	BY_INDEX	LIST_BYTES, LIST_INT64	the field value is with IDX & OP in items (UPDATE~=(TS,[ID:LI:?:[ [IDX][OP]val, [IDX][OP]val, ..] ]))

The cells of the response are the fetched cells before update has been applied.
These options let the use of a column in a synchronized manner that open usage possibilities to cases such as:

• auto-increment value for purpose such as an unique ID.
• the data to be considered and processed as a Queue, whether to delete or to update the value with a corresponding queue track data.

Update Query

The Update Query command performs cells write to the column/s and the designated range/s.

• The Update Query syntax

  The Update Query sytrax consists the ‘update' command followed by Cell or comma-separated Cell/s UPDATE Cell , Cell

• An Example:

  UPDATE
  cell(DELETE_LE,               CID, ['K','E','Y']                         ),
  cell(DELETE_EQ,               CID, ['K','E','Y'], TS                     ),
  cell(INSERT,                  CID, ['K','E','Y'], ASC, TS, 'DATA'        ),
  cell(INSERT,                  CID, ['K','E','Y'], DESC,    'DATA'        ),
  cell(INSERT,                 NAME, ['K','E','Y'], '',      'DATA', 'ENC' );
  

• The Cell for Update syntax

  The Syntax depends on the Flags and available definitions.

• A DELETE_LE Flag:

  cell( DELETE_LE , Column ID|NAME , Key )

• A DELETE_EQ Flag:

  cell( DELETE_EQ , Column ID|NAME , Key , TIMESTAMP )

• An INSERT Flag with auto-timestamp:

  cell( INSERT , Column ID|NAME , Key , "" , VALUE-DATA )

• An INSERT Flag with version-timestamp config:

  cell( INSERT , Column ID|NAME , Key , TIME_ORDER , TIMESTAMP , VALUE-DATA )

  • TIME_ODER options are ASC/DESC - empty-entry, defaults to DESC .
  • TIMESTAMP in nanoseconds or a Date and Time in format "YYYY/MM/DD HH:mm:ss.nanoseconds", empty-field is auto-assign.

• An INSERT Flag for a SERIAL Column Type:

  As other INSERT with or without timestamp whereas VALUE-DATA defined in a serialization format.
  The VALUE-DATA is a square-brackets of a field-sets [ID:TYPE:VALUE, ... ]. The field-sets are unordered makes the less accessed field to be preferred as last.
  An expectation of Field ID is to be an unique ID per each type, It is OK to have [1:I:123, 1:D:0.123, 1:B:"123", 1:K:[1,2,3]], 1:LI:[1,2,3], 1:LB:[ab,cd,ef]

  • The Field-ID is a UINT24_T - max 16,777,215 possible Field-IDs.

  • The Available Field-Type:

    INT64 / I

    DOUBLE / D

    BYTES / B

    KEY / K

    LIST_INT64 / LI

    LIST_BYTES / LB

• An INSERT Flag with Encoded-Value:

  The last definition of cell is set with the ENCODER.
  cell( INSERT , Column ID/NAME , Key , "" , VALUE-DATA , ENCODER )

  • The available encoding for cell-value: ZLIB, SNAPPY, ZSTD - default without.