SQL Format¶

This appendix specifies the SQL format used by Yamcs for its internal database.

Identifiers¶

identifier   ::=  letter+ ( digit | letter | specialchars )*
letter       ::=  "A"..."Z"
specialchars ::=  "$" | "_" | "# | "."

Integer Literals¶

integer    ::=  decinteger | hexinteger
decinteger ::=  digit+
hexinteger ::=  "0" "X" hexdigit+
hexdigit   ::=  digit | "A"..."F"
digit      ::=  "0"..."9"

Float Literals¶

float    ::=  digit* "." digit+ [ exponent ]
              | digit+ exponent
exponent ::=  [ "+" | "-" ] [ "E" ] digit+

String Literals¶

string     ::=  "'" stringchar*  "'" ( "'" stringchar* "'" )*
stringchar ::=  <any character except newline or quote>

Concatenation

Adjacent string literals (delimited by whitespace) are allowed, and concatenated at compile time.

Operators¶

addOp     ::=  "+" | "-" | "||"
multOp    ::=  "*" | "/" | "MOD"
relOp     ::=  "=" | "!=" | ">=" | ">" | "<>" | "<=" | "&&" | "<"
bitWiseOp ::=  "&" | "|" | "^" | "<<" | ">>"

Object Names¶

objectName             ::=  identifier | doubleQuotedIdentifier
doubleQuotedIdentifier ::=  '"' stringchar* '"'

Expressions¶

simpleExpression         ::=  additiveExpression ( bitwiseOp additiveExpression )*
additiveExpression       ::=  multiplicativeExpression ( addOp multiplicativeExpression )*
multiplicativeExpression ::=  exponentExpression ( multOp multiplicativeExpression )*
exponentExpression       ::=  unaryExpression [ "**" unaryExpression ]
unaryExpression          ::=  [ "+" | "-" ] primaryExpression
primaryExpression        ::=  integer
                              | float
                              | string
                              | "?"
                              | "(" simpleExpression ")"
                              | "ARRAY" "[" expressionList "]"
                              | functionCall
                              | objectName
expression               ::=  andExpression ( "OR" andExpression )*
andExpression            ::=  unaryLogicalExpression
                              | "(" expression ")" ( "AND" (
                                  unaryLogicalExpression
                                  | "(" expression ")"
                                ) )*
unaryLogicalExpression   ::=  [ "NOT" ] relationalExpression
relationalExpression     ::=  simpleExpression
                              [
                                  relOp simpleExpression
                                  | inClause
                                  | betweenClause
                                  | likeClause
                                  | isNullClause
                              ]
expressionList           ::=  expression ( "," expression )*
inClause                 ::=  [ "NOT" ] "IN" "(" expressionList ")"
betweenClause            ::=  [ "NOT" ] "BETWEEN" simpleExpression "AND" simpleExpression
likeClause               ::=  [ "NOT" ] "LIKE" ( STRING | "?" )
isNullClause             ::=  "IS" [ "NOT" ] "NULL"
functionCall             ::=  objectName "(" [ expressionList | "*" ] ")"

selectExpression      ::=  "SELECT" selectList
                           "FROM" tupleSourceExpression
                           [ "[" windowSpecification "]" ]
                           [ "WHERE" expression ]
                           [ "ORDER" [ "ASC" | "DESC" ] ]
                           [ "LIMIT" [ offset "," ] rowCount ]
mergeExpression       ::=  "MERGE" tupleSourceExpression ( "," tupleSourceExpression )*
                           "USING" columnName
                           [ "ORDER" [ "ASC" | "DESC" ] ]
                           [ "LIMIT" [ offset "," ] rowCount ]
selectList            ::=  selectItem ( "," selectItem )*
selectItem            ::=  "*"
                           | simpleExpression [ [ "AS" ] columnName ]
tupleSourceExpression ::=  objectName [ "HISTOGRAM" "(" columnName [ "," mergeTime ] ")" ]
                           | "(" streamExpression ")"
windowSpecification   ::=  "SIZE" integer "ADVANCE" integer windowMode
windowMode            ::=  "TIME" | "TUPLES" | "ON" columnName
offset                ::=  integer
rowCount              ::=  integer
mergeTime             ::=  integer

Statements¶

ALTER SEQUENCE Statement¶

alterSequenceStatement ::=  "ALTER" "SEQUENCE" objectName "RESTART" [ "WITH" restart ]
restart                ::=  integer

Changes the properties of an existing sequence generator.

ALTER TABLE Statement¶

alterTableStatement ::=  "ALTER" "TABLE" objectName "RENAME" "TO" objectName

Changes table properties. Currently this is limited to renaming.

CLOSE STREAM Statement¶

closeStreamStatement ::=  "CLOSE" "STREAM" objectName

CREATE TABLE Statement¶

createTableStatement  ::=  "CREATE" "TABLE" [ "IF" "NOT" "EXISTS" ] tableName "("
                               tableColumnDefinition ( "," tableColumnDefinition )*
                               "," "PRIMARY" "KEY" "(" columnName ( "," columnName )* ")"
                               [ "," "INDEX" "(" columnName ( "," columnName )* ")" ]
                           ")"
                           [ "HISTOGRAM" "(" columnName ( "," columnName )* ")" ]
                           [ "ENGINE" engineName ]
                           [ "PARTITION" "BY" partitioningSpec ]
                           [ "TABLESPACE" tablespaceName ]
                           [ "TABLE_FORMAT" "=" "COMPRESSED" ]
tableColumnDefinition ::=  columnName dataType [ "AUTO_INCREMENT" ]
dataType              ::=  simpleDataType | arrayDataType
arrayDataType         ::=  simpleDataType "[]"
simpleDataType        ::=  : "BINARY"
                           | "BOOLEAN"
                           | "BYTE"
                           | "DOUBLE"
                           | "ENUM"
                           | "HRES_TIMESTAMP"
                           | "INT"
                           | "LONG"
                           | "PARAMETER_VALUE"
                           | "SHORT"
                           | "STRING"
                           | "PROTOBUF" "(" className ")"
                           | "TIMESTAMP"
                           | "UUID"
partitioningSpec      ::=  "TIME" "(" columnName [ "(" timePartitioning ")" ] ")"
                           | "VALUE" "(" columnName ")"
                           | "TIME_AND_VALUE" "("
                                 columnName [ "(" timePartitioning ")" ],
                                 columnName
                             ")"
className             ::=  string
columnName            ::=  objectName
timePartitioning      ::=  "'YYYY'" | "'YYYY/DOY'" | "'YYYY/MM'"

Partitioning

Partitioning allows to separate the data in different RocksDB databases (by time) and column families (by value).

Time partitioning allows the following schemes:

YYYY: one RocksDB database per year.
YYYY/DOY: one RocksDB database per combination year, and day of the year.
YYYY/MM: one RocksDB database per combination year, and month of the year.

Partitioning by time ensures that old data is frozen and not disturbed by new data coming in.

CREATE STREAM Statement¶

createStreamStatement  ::=  "CREATE" "STREAM" streamName (
                                "AS" streamExpression [ "NOFOLLOW" ]
                                | "(" streamColumnDefinition ( "," streamColumnDefinition )* ")"
                             )
streamExpression       ::=  selectExpression | mergeExpression
streamColumnDefinition ::=  columnName dataType

DELETE Statement¶

deleteStatement ::=  "DELETE" "FROM" objectName
                     [ "WHERE" expression ]
                     [ "LIMIT" integer ]

Delete records from a table.

DESCRIBE Statement¶

describeStatement ::=  "DESCRIBE" objectName

Obtain information about table or stream structure.

DROP TABLE Statement¶

dropTableStatement ::=  "DROP" "TABLE" [ "IF" "EXISTS" ] objectName

Remove a table.

INSERT Statement¶

insertStatement ::=  ( "INSERT" | "UPSERT" | "INSERT_APPEND" | "UPSERT_APPEND" | "LOAD" )
                     "INTO" objectName
                     (streamExpression | insertValues)
insertValues    ::=  "(" columnName ( "," columnName )* "VALUES" "(" selectList ")"

SELECT TABLE Statement¶

selectTableStatement ::=  "SELECT" selectList
                          "FROM" tupleSourceExpression
                          [ "[" windowSpecification "]" ]
                          [ "WHERE" expression ]
                          [ "ORDER" [ "ASC" | "DESC" ] ]
                          [ "LIMIT" [ offset "," ] rowCount ]

SHOW DATABASES Statement¶

showDatabasesStatement ::=  "SHOW" "DATABASES"

Lists the databases.

SHOW ENGINES Statement¶

showEnginesStatement ::=  "SHOW" "ENGINES"

Lists the server's storage engines.

SHOW SEQUENCES Statement¶

showSequencesStatement ::=  "SHOW" "SEQUENCES"

Lists the sequences in the current database.

SHOW STREAMS Statement¶

showStreamsStatement ::=  "SHOW" "STREAMS"

Lists the streams in the current database.

SHOW TABLES Statement¶

showTablesStatement ::=  "SHOW" "TABLES"

Lists the tables in the current database.

UPDATE Statement¶

updateStatement ::=  "UPDATE" "SET" columnName "=" simpleExpression
                     ( "," columnName "=" simpleExpression )*
                     [ "WHERE" expression ]
                     [ "LIMIT" integer ]