Using SWC-DB SQL

Table of Contents


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:

Key Type Default Value Description
cid i64 NO_CID == 0 the Column ID
name string empty The Column Name
seq string VOLUME The Column Key Sequence, options LEXIC / VOLUME / FC_LEXIC / FC_VOLUME
type string PLAIN The Column Value Type, options PLAIN / COUNTER_I{64,32,16,8} / SERIAL
cell_versions i32 0 == 1 The Cell Versions
cell_ttl i32 0 == without The Time to Live in milliseconds
blk_encoding string DEFAULT = Rangers' default cfg The Block Encoding, options PLAIN / ZSTD / ZLIB / SNAPPY
blk_size i32 0 == Rangers' default cfg The Block Size in Bytes
blk_cells i32 0 == Rangers' default cfg The Number of Cells in a Block
cs_replication i8 0 == Rangers' default cfg The CellStore file Replication
cs_size i32 0 == Rangers' default cfg The CellStore file size in Byte
cs_max i8 0 == Rangers' default cfg The Max CellStores in a Range
log_rollout i8 0 == Rangers' default cfg The ratio of CommitLog-size on reached Write Fragment File
log_compact i8 0 == Rangers' default cfg The size of cointervaling Fragments for log compaction to apply
log_preload i8 0 == Rangers' default cfg The number of Fragments to be preloaded at scans and compaction
compact i8 0 == Rangers' default cfg The Compaction percentage Threshold for doing a range compaction
revision i64 0 == auto assigned on update/create The Schema's revision

The Comparators syntax

TOKEN syntax Logic Syntax Description
PF =^ starts-with [prefix]
GT > greater-than
GE >= greater-equal
EQ == = equal
LE <= lower-equal
LT < lower-than
NE != not-equal
RE r regular-expression
VGT v> volume greater-than
VGE v>= volume greater-equal
VLE v<= volume lower-equal
VLT v< volume lower-than
SBS %> subset
SPS <% superset
POSBS ~> eq/part ordered subset
POSPS <~ eq/part ordered superset
FOSBS <- eq/full ordered superset
FOSPS -> eq/full ordered superset

The Column Selector Syntax

The Column Selector Syntax is comma-separated value (name, cid, and a Comparator-Expresssion) such as nameOne, 2,r'test$' with or without word-separators or a single column by name or cid.


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:

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 and Flags 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)] )

  • 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 GT or GE.
    [ 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/DDD 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.

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,                  CID, ['K','E','Y']                         ),
    cell(DELETE_VERSION,          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 Flag:

    cell( DELETE , Column ID|NAME , Key )

  • A DELETE_VERSION Flag:

    cell( DELETE_VERSION , 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/DDD 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.