Migration SQL Grammar

Overview

This grammar reference applies to migration scripts used for schema and data migrations in Kinotic. Migration scripts use a SQL dialect designed for Elasticsearch index management and data operations.

All statements must end with a semicolon (;). Identifiers must start with a letter or underscore and can contain letters, numbers, and underscores. Strings are enclosed in single quotes ('...').

Statements Overview

• CREATE TABLE
• CREATE COMPONENT TEMPLATE
• CREATE INDEX TEMPLATE
• REINDEX
• INSERT
• UPDATE
• DELETE
• Comments

CREATE TABLE

Creates an Elasticsearch index with the specified field mappings.

Syntax:

CREATE TABLE [IF NOT EXISTS] <index_name> (<column_name> <type> [, <column_name> <type>]*) ;

• IF NOT EXISTS (optional): Only create the index if it does not already exist.
• <type>: See Supported Types.

Example:

CREATE TABLE IF NOT EXISTS products (
    name TEXT,
    sku KEYWORD,
    price DOUBLE,
    inStock BOOLEAN,
    createdAt DATE
) ;

CREATE COMPONENT TEMPLATE

Creates a reusable component template that can be referenced by index templates.

Syntax:

CREATE COMPONENT TEMPLATE <template_name> (<definition> [, <definition>]*) ;

Definitions:

Example:

CREATE COMPONENT TEMPLATE base_settings (
    NUMBER_OF_SHARDS = 3,
    NUMBER_OF_REPLICAS = 1,
    createdAt DATE,
    updatedAt DATE
) ;

CREATE INDEX TEMPLATE

Creates an index template that applies settings and mappings to indices matching a pattern.

Syntax:

CREATE INDEX TEMPLATE <template_name> FOR '<pattern>' USING '<component_template>'
    [WITH (<definition> [, <definition>]*)] ;

• WITH (...) (optional): Additional definitions as in component templates.

Example:

CREATE INDEX TEMPLATE logs_template FOR 'logs-*' USING 'base_settings'
    WITH (NUMBER_OF_REPLICAS = 2, level KEYWORD) ;

REINDEX

Copies documents from one index to another with optional transformations.

Syntax:

REINDEX <source_index> INTO <dest_index> [WITH (<option> [, <option>]*)] ;

Options:

Example:

REINDEX old_products INTO new_products WITH (
    CONFLICTS = PROCEED,
    SLICES = AUTO,
    QUERY = 'status:active',
    SKIP_IF_NO_SOURCE = TRUE
) ;

If SKIP_IF_NO_SOURCE = TRUE, the reindex operation will be skipped (no error) if the source index does not exist. This is useful for idempotent migrations.

INSERT

Inserts a document into an index.

Syntax:

INSERT INTO <index_name> [(<column_name> [, <column_name>]*)]
    VALUES (<expression> [, <expression>]*) [WITH REFRESH] ;

• WITH REFRESH (optional): Immediately refresh the index after insert, making the document searchable.
• <expression>: Literal value, parameter (?), or field reference.

Example:

INSERT INTO products (name, sku, price)
    VALUES ('Widget', 'WDG-001', 9.99) WITH REFRESH ;

UPDATE

Updates documents matching a where clause.

Syntax:

UPDATE <index_name> SET <field> = <expression> [, <field> = <expression>]*
    WHERE <where_clause> [WITH REFRESH] ;

• WITH REFRESH (optional): Immediately refresh the index after update.
• <expression>: Literal, parameter, or binary expression (e.g., age + 1).

Example:

UPDATE products SET price = 12.99, updatedAt = '2024-01-15'
    WHERE sku == 'WDG-001' WITH REFRESH ;

DELETE

Deletes documents matching a where clause.

Syntax:

DELETE FROM <index_name> WHERE <where_clause> [WITH REFRESH] ;

• WITH REFRESH (optional): Immediately refresh the index after delete.

Example:

DELETE FROM products WHERE inStock == false WITH REFRESH ;

Comments

-- This is a comment

Comments start with -- and continue to the end of the line. Comments are ignored by the parser.

Where Clauses

Where clauses are used in UPDATE and DELETE statements.

Syntax:

<field> <operator> <value>
(<where_clause>)
<where_clause> AND <where_clause>
<where_clause> OR <where_clause>

Operators: ==, !=, <, >, <=, >=

Values: Literal, parameter (?), string, integer, boolean

Example:

WHERE status == 'archived' AND createdAt < '2023-01-01'
WHERE (category == 'electronics' OR category == 'appliances') AND price > 100

Supported Types

The NOT INDEXED variant of each type stores the value but excludes it from the inverted index and doc_values. This reduces storage and indexing overhead for fields that only need to be returned in results, not queried.

Expressions

• Literals: 'string', 123, true, false
• Parameters: ?
• Binary Expressions: <field> + <value>, <field> - <value>, etc.

Reserved Keywords

All keywords in the grammar are reserved and case-insensitive:

CREATE, TABLE, IF, NOT, EXISTS, COMPONENT, TEMPLATE, INDEX, FOR, USING, WITH, REINDEX, INTO, INSERT, VALUES, UPDATE, SET, DELETE, FROM, WHERE, AND, OR, REFRESH, TRUE, FALSE, NUMBER_OF_SHARDS, NUMBER_OF_REPLICAS, CONFLICTS, ABORT, PROCEED, MAX_DOCS, SLICES, AUTO, SIZE, SOURCE_FIELDS, QUERY, SCRIPT, WAIT, SKIP_IF_NO_SOURCE