Lindorm:DDL

Overview

Lindorm CQL stores data in tables. Each table belongs to a keyspace, which groups related tables together. Keyspaces support options such as replication policy, but Lindorm CQL uses fixed default values for these settings—you cannot customize them.

Lindorm CQL supports two index types:

• Secondary indexes: index one or more columns for targeted queries.

• Search indexes: built on a search engine; support multi-dimensional queries, column-based sorting, and fuzzy matching.

CREATE KEYSPACE

Creates a keyspace.

Syntax

CREATE KEYSPACE [ IF NOT EXISTS ] keyspace_name WITH options

Parameters

• keyspace_name: The name of the keyspace. Example: testks.

• options: One or both of the following:

  • replication: A map. Specifies the replication strategy. Lindorm CQL always uses two replicas; this setting cannot be changed.

  • durable_writes: A boolean. Specifies whether writes are durable. Always true; this setting cannot be changed.

In Lindorm, keyspaces function as logical namespaces. Replication and durable_writes settings are accepted for compatibility with standard CQL clients but have no effect—Lindorm CQL always applies its defaults.

Example

CREATE KEYSPACE testks WITH replication = {'class': 'SimpleStrategy', 'replication_factor': 1};

ALTER KEYSPACE

Modifies keyspace options.

Syntax

ALTER KEYSPACE keyspace_name WITH options

Parameters

Same options as CREATE KEYSPACE. Because keyspace-level options are fixed defaults in Lindorm CQL, this statement is accepted for compatibility but has no practical effect.

Example

ALTER KEYSPACE testks WITH replication = {'class': 'SimpleStrategy', 'replication_factor': 1};

USE

Sets the active keyspace for the current session. Objects such as tables, user-defined types (UDTs), and functions are scoped to a keyspace.

Syntax

USE keyspace_name

Parameters

• keyspace_name: The keyspace to activate. Example: testks.

Example

USE testks;

DROP KEYSPACE

Deletes a keyspace and all its contents permanently.

Syntax

DROP KEYSPACE [ IF EXISTS ] keyspace_name

• IF EXISTS: Suppresses the error if the keyspace does not exist. Without this clause, dropping a non-existent keyspace returns an error.

• Only the root user can drop keyspaces.

Parameters

• keyspace_name: The keyspace to delete. Example: testks.

Example

DROP KEYSPACE testks;

CREATE TABLE

Creates a table.

Syntax

CREATE TABLE [ IF NOT EXISTS ] table_name
    '('
         column_definition
         ( ',' column_definition )*
         [ ',' PRIMARY KEY '(' primary_key ')' ]
    ')' [ WITH table_options ]

column_definition  ::=  column_name cql_type [ PRIMARY KEY ]
primary_key        ::=  partition_key [ ',' clustering_columns ]
partition_key      ::=  column_name
                        | '(' column_name ( ',' column_name )* ')'
clustering_columns ::=  column_name ( ',' column_name )*
table_options      ::=  CLUSTERING ORDER BY '(' clustering_order ')' [ AND options ]
                        | options
clustering_order   ::=  column_name (ASC | DESC) ( ',' column_name (ASC | DESC) )*

Description

Every table must define exactly one primary key. The primary key has two parts:

• Partition key: The first element of the primary key. A single column, or multiple columns enclosed in parentheses to form a composite partition key. Every table has at least one partition key column.

• Clustering columns: The remaining columns in the primary key, listed after the partition key. They define how rows are ordered within a partition. Clustering columns are optional.

Primary key examples:

For example, given this table and data:

CREATE TABLE personinfo (
    a int,
    b int,
    c int,
    d int,
    PRIMARY KEY ((a, b), c, d)
);

SELECT * FROM personinfo;
   a | b | c | d
  ---+---+---+---
   0 | 0 | 0 | 0    -- partition: (a=0, b=0)
   0 | 0 | 1 | 1    -- partition: (a=0, b=0)
   0 | 1 | 2 | 2    -- partition: (a=0, b=1)
   0 | 1 | 3 | 3    -- partition: (a=0, b=1)
   1 | 1 | 4 | 4    -- partition: (a=1, b=1)

Rows 1 and 2 share the same partition key (a=0, b=0) and are guaranteed to be in the same partition in standard Cassandra CQL. In Lindorm CQL, this guarantee does not apply.

Examples

-- Create a table with name as the primary key
CREATE TABLE tb (name text PRIMARY KEY, age int);

-- Set the default time-to-live (TTL) to 1,000 seconds
CREATE TABLE ttltb (name text PRIMARY KEY, age int) WITH default_time_to_live = 1000;

-- Use LZ4 compression (default is Snappy)
CREATE TABLE cptb (name text PRIMARY KEY, age int) WITH compression = {'class': 'LZ4Compressor'};

-- Set the hot-cold boundary to 10 seconds
CREATE TABLE hct (name text PRIMARY KEY, age int) WITH extensions = {'COLD_BOUNDARY': '10'};

-- LZ4 compression
CREATE TABLE personinfo (
    id int,
    name text,
    address text,
    PRIMARY KEY (id, name)
) WITH compression = {'class': 'LZ4Compressor'};

-- ZSTD compression
CREATE TABLE personinfo (
    id int,
    name text,
    address text,
    PRIMARY KEY (id, name)
) WITH compression = {'class': 'ZstdCompressor'};

-- Snappy compression
CREATE TABLE personinfo (
    id int,
    name text,
    address text,
    PRIMARY KEY (id, name)
) WITH compression = {'class': 'SnappyCompressor'};

-- Create a cold-storage table
CREATE TABLE personinfo (name text PRIMARY KEY, age int)
    WITH extensions = {'STORAGE_POLICY': 'COLD'};

-- Switch to hot storage
ALTER TABLE personinfo WITH extensions = {'STORAGE_POLICY': 'DEFAULT'};

-- Set a 1-day hot-cold boundary (86,400 seconds)
CREATE TABLE personinfo (name text PRIMARY KEY, age int)
    WITH extensions = {'COLD_BOUNDARY': '86400'};

-- Change the boundary to 1,000 seconds
ALTER TABLE personinfo WITH extensions = {'COLD_BOUNDARY': '1000'};

-- Disable hot-cold separation
ALTER TABLE personinfo WITH extensions = {'COLD_BOUNDARY': ''};

-- Create a table with strong consistency
CREATE TABLE personinfo (name text PRIMARY KEY, age int)
    WITH extensions = {'CONSISTENCY_TYPE': 'strong'};

-- Change to eventual consistency
ALTER TABLE personinfo WITH extensions = {'CONSISTENCY_TYPE': 'eventual'};

-- Set mutability to IMMUTABLE
CREATE TABLE personinfo (name text PRIMARY KEY, age int)
    WITH extensions = {'MUTABILITY': 'IMMUTABLE'};

-- Change mutability to MUTABLE_LATEST
ALTER TABLE personinfo WITH extensions = {'MUTABILITY': 'MUTABLE_LATEST'};

ALTER TABLE

Modifies a table's columns or options.

Syntax

ALTER TABLE table_name alter_table_instruction

alter_table_instruction ::=  ADD column_name cql_type ( ',' column_name cql_type )*
                             | WITH options

Description

• Add columns: Use ADD to add one or more columns. New columns cannot be part of the primary key, because primary keys cannot be changed after table creation.

• Modify options: Use WITH to change default_time_to_live, compression, or extensions. The CLUSTERING ORDER option cannot be modified after table creation.

• Delete columns: Not supported in Lindorm CQL.

Example

CREATE TABLE personinfo (name text PRIMARY KEY, age int);
ALTER TABLE personinfo ADD address text;

DROP TABLE

Deletes a table and all its data permanently.

Syntax

DROP TABLE [ IF EXISTS ] table_name

• IF EXISTS: Suppresses the error if the table does not exist. Without this clause, dropping a non-existent table returns an error.

• Only the root user can drop tables.

Parameters

• table_name: The table to delete. Example: personinfo.

Example

DROP TABLE personinfo;

TRUNCATE

Removes all rows from a table without deleting the table itself.

Syntax

TRUNCATE [ TABLE ] table_name

• Only the root user can truncate tables.

Parameters

• table_name: The table to truncate. Example: personinfo.

Example

TRUNCATE TABLE personinfo;

CREATE INDEX

Creates a secondary index on one or more columns of a table.

Syntax

CREATE [ CUSTOM ] INDEX [ IF NOT EXISTS ] [ index_name ]
    ON table_name '(' index_identifier ')'
    [ USING string [ WITH OPTIONS = map_literal ] ]

index_identifier ::=  column_name
                      | '(' column_name ')'

Description

CREATE INDEX builds a secondary index on a column, enabling queries on that column. If the column already contains data, indexing happens asynchronously in the background. New data written to the column is indexed automatically.

• Standard CREATE INDEX supports indexing a single column.

• To index multiple columns, use CREATE CUSTOM INDEX with the Lindorm secondary index class.

• IF NOT EXISTS: Suppresses the error if the index already exists.

Examples

-- Index a single column with an explicit index name
CREATE INDEX myindex ON personinfo (c2);

-- Index a single column without specifying an index name
CREATE INDEX ON personinfo (c2);

-- Index multiple columns using a custom index
CREATE CUSTOM INDEX myindex ON personinfo (c1, c2)
    USING 'com.alibaba.lindorm.cserver.schema.index.LindormSecondaryIndex';

DROP INDEX

Deletes a secondary index.

Syntax

DROP INDEX [ IF EXISTS ] index_name

• IF EXISTS: Suppresses the error if the index does not exist. Without this clause, dropping a non-existent index returns an error.

• Optionally qualify the index name with a keyspace: keyspace_name.index_name.

Example

DROP INDEX myindex;

CREATE SEARCH INDEX

Creates a full-text search index on one or more columns of a table.

Syntax

CREATE SEARCH INDEX [ IF NOT EXISTS ] index_name ON [keyspace_name.]table_name
    [ WITH COLUMNS (column1, ..., columnn) ]
  | [ WITH COLUMNS (*) ]

Description

Search indexes enable multi-dimensional queries, column-based sorting with ORDER BY, and fuzzy matching.

Before creating a search index, enable full-text search and Lindorm Tunnel Service (LTS). For setup instructions, see Overview.

For tables deployed across multiple zones, set CONSISTENCY_TYPE and MUTABILITY in the table's extensions before creating the index. These settings are optional for single-zone deployments.

• WITH COLUMNS (column1, ..., columnn): Index the specified columns, separated by commas.

• WITH COLUMNS (*): Index all columns.

Example

CREATE SEARCH INDEX schidx ON personinfo WITH COLUMNS (c2, c3);

DROP SEARCH INDEX

Deletes a full-text search index.

Syntax

DROP SEARCH INDEX [IF EXISTS] ON [keyspace_name.]table_name;

Example

DROP SEARCH INDEX ON testks.personinfo;

REBUILD SEARCH INDEX

Activates a search index and indexes historical data.

Syntax

REBUILD SEARCH INDEX [ASYNC] [IF EXISTS] ON [keyspace_name.]table_name;

Description

After CREATE SEARCH INDEX, the index status is INACTIVE. Run REBUILD SEARCH INDEX to activate the index and index any existing data. Because indexing historical data can take time, use ASYNC to run the rebuild in the background.

Examples

-- Rebuild synchronously
REBUILD SEARCH INDEX ON personinfo;

-- Rebuild asynchronously
REBUILD SEARCH INDEX ASYNC ON personinfo;

ALTER SEARCH INDEX

Adds or removes columns from a search index.

Syntax

ALTER SEARCH INDEX SCHEMA [IF EXISTS] ON [keyspace_name.]table_name
    ( ADD FIELD column_name
    | DROP FIELD column_name );

• After modifying a search index, run REBUILD SEARCH INDEX to reactivate it.

• Columns added with ADD FIELD or removed with DROP FIELD must exist in the source table.

Examples

-- Add a column to the search index
ALTER SEARCH INDEX SCHEMA ON personinfo ADD (c3);

-- Remove a column from the search index
ALTER SEARCH INDEX SCHEMA ON personinfo DROP (c2);