DDL

create_table_stmt ::=
    'CREATE' table_type? 'TABLE' ['IF NOT EXISTS'] table_name
    '(' column_def ( ',' column_def )* ')'
    [ 'METADATA' '(' column_def ( ',' column_def )* ')' ]
    [ table_property_list ]
    [ 'TABLESPACE' tablespace_name ]
    [ 'WITH ROLLUP' rollup_interval_spec ]

table_type ::= 'LOG' | 'TAG' | 'VOLATILE' | 'LOOKUP' | 'TRANSACTION' | 'TXN'
-- Omitting table_type creates a TRANSACTION table.

column_def ::= column_name column_type
               [ 'PRIMARY KEY' ]
               [ 'NOT NULL' ]
               [ column_axis ]
               [ 'SUMMARIZED' ]
               [ 'DEFAULT' value ]
               [ 'PROPERTY' '(' column_property_list ')' ]

decimal_type ::= ( 'DECIMAL' | 'NUMERIC' | 'DEC' | 'FIXED' | 'NUMBER' )
                 [ '(' precision [ ',' scale ] ')' ]

array_type ::= ( 'SHORT' | 'INT16' | 'USHORT' | 'UINT16'
               | 'INTEGER' | 'INT' | 'INT32' | 'UINTEGER' | 'UINT32'
               | 'LONG' | 'INT64' | 'ULONG' | 'UINT64'
               | 'FLOAT' | 'DOUBLE' | decimal_type )
             '[' cardinality ']'

column_axis ::= 'BASETIME' | 'BASE TIME' | 'BASE DISTANCE' | 'BASEDISTANCE'

column_property_list ::=
    ( 'MINMAX_CACHE_SIZE' '=' number
    | 'PART_PAGE_COUNT'   '=' number
    | 'PAGE_VALUE_COUNT'  '=' number
    | 'MAX_CACHE_PART_COUNT' '=' number
    | 'SEQUENCE' '=' number )
    ( ',' column_property_list )*

table_property_list ::=
    ( 'TAG_PARTITION_COUNT'           '=' number
    | 'TAG_DATA_PART_SIZE'            '=' number
    | 'TAG_STAT_ENABLE'               '=' ( '0' | '1' )
    | 'TAG_DUPLICATE_CHECK_DURATION'  '=' number
    | 'VARCHAR_FIXED_LENGTH_MAX'      '=' number )
    ( ',' table_property_list )*

-- Create a LOG table with the explicit LOG keyword.
CREATE LOG TABLE sensor_log (
    id      INTEGER,
    name    VARCHAR(64),
    value   DOUBLE,
    status  VARCHAR(20)
);

-- Create a TAG table: BASETIME required; SUMMARIZED marks the ROLLUP column.
CREATE TAG TABLE tag (
    name  VARCHAR(40) PRIMARY KEY,
    time  DATETIME BASETIME,
    value DOUBLE SUMMARIZED
);

-- TAG table with metadata and properties
CREATE TAG TABLE sensors (
    name     VARCHAR(40) PRIMARY KEY,
    time     DATETIME BASETIME,
    value    DOUBLE SUMMARIZED
) METADATA (
    location VARCHAR(100),
    unit     VARCHAR(20)
) TAG_PARTITION_COUNT = 4;

-- LOOKUP table (PRIMARY KEY required)
CREATE LOOKUP TABLE devices (
    device_id  VARCHAR(40) PRIMARY KEY,
    ip         IPV4,
    status     VARCHAR(20)
);

-- VOLATILE table
CREATE VOLATILE TABLE cache_data (
    id    INTEGER PRIMARY KEY,
    value DOUBLE
);

-- Exact fixed-point columns in a TRANSACTION table
CREATE TRANSACTION TABLE invoice (
    id      LONG PRIMARY KEY,
    amount  DECIMAL(18,2),
    tax     NUMERIC(18,4)
);

-- Use IF NOT EXISTS
CREATE TAG TABLE IF NOT EXISTS tag (
    name  VARCHAR(40) PRIMARY KEY,
    time  DATETIME BASETIME,
    value DOUBLE SUMMARIZED
);

-- NOT NULL constraint
CREATE TABLE t1 (
    c1 INTEGER NOT NULL,
    c2 VARCHAR(200)
);

drop_table_stmt ::= 'DROP TABLE' table_name

DROP TABLE sensor_log;

alter_table_add_stmt ::=
    'ALTER TABLE' table_name [ 'METADATA' ] 'ADD COLUMN'
    '(' column_name column_type [ 'DEFAULT' value ] ')'

-- Add a column
ALTER TABLE sensor_log ADD COLUMN (quality FLOAT);

-- Add a TRANSACTION column
ALTER TABLE product_master ADD COLUMN (stock_qty INTEGER DEFAULT 0);

-- Add columns with defaults
ALTER TABLE sensor_log ADD COLUMN (flag INTEGER DEFAULT 0);
ALTER TABLE sensor_log ADD COLUMN (tag_ip IPV4 DEFAULT '192.168.0.1');

-- Add an ARRAY column with DEFAULT
ALTER TABLE sensor_log
    ADD COLUMN (channels INT32[3] DEFAULT [1, NULL, 3]);

-- Add a TAG METADATA ARRAY column
ALTER TABLE sensor_tag METADATA
    ADD COLUMN (limits DECIMAL(12,4)[2] DEFAULT [0.0000, NULL]);

alter_table_drop_stmt ::=
    'ALTER TABLE' table_name [ 'METADATA' ]
    'DROP COLUMN' '(' column_name ')'

ALTER TABLE sensor_log DROP COLUMN (quality);
ALTER TABLE product_master DROP COLUMN (stock_qty);
ALTER TABLE sensor_log DROP COLUMN (channels);
ALTER TABLE sensor_tag METADATA DROP COLUMN (limits);

alter_table_column_rename_stmt ::=
    'ALTER TABLE' table_name 'RENAME COLUMN' old_column_name 'TO' new_column_name

ALTER TABLE sensor_log RENAME COLUMN status TO device_status;
ALTER TABLE product_master RENAME COLUMN name TO product_name;

alter_table_modify_stmt ::=
    'ALTER TABLE' table_name 'MODIFY COLUMN'
    ( '(' column_name 'VARCHAR' '(' new_size ')' ')'
    | column_name ( 'NOT NULL' [ 'NOCHECK' ] | 'NULL'
                  | 'SET' 'MINMAX_CACHE_SIZE' '=' value ) )

-- Increase VARCHAR length (decreasing is unsupported)
ALTER TABLE sensor_log MODIFY COLUMN (name VARCHAR(128));

-- Add NOT NULL
ALTER TABLE sensor_log MODIFY COLUMN id NOT NULL;

-- Remove NOT NULL
ALTER TABLE sensor_log MODIFY COLUMN id NULL;

-- Change MINMAX_CACHE_SIZE
ALTER TABLE sensor_log MODIFY COLUMN id SET MINMAX_CACHE_SIZE = 10240;

alter_table_rename_stmt ::=
    'ALTER TABLE' table_name 'RENAME TO' new_name

-- Supported for TRANSACTION tables
ALTER TABLE product_master RENAME TO product_catalog;

truncate_table_stmt ::= 'TRUNCATE TABLE' table_name

TRUNCATE TABLE sensor_log;

create_tablespace_stmt ::=
    'CREATE TABLESPACE' tablespace_name 'DATADISK' datadisk_list

datadisk_list ::= data_disk ( ',' data_disk )*

data_disk ::= disk_name
    '(' 'DISK_PATH' '=' '"' path '"'
        [ ',' 'PARALLEL_IO' '=' number ]
    ')'

-- Single-disk tablespace
CREATE TABLESPACE tbs1 DATADISK disk1 (DISK_PATH="tbs1_disk1");

-- Configure parallel I/O
CREATE TABLESPACE tbs2 DATADISK disk1 (DISK_PATH="tbs2_disk1", PARALLEL_IO = 5);

-- Multiple disks
CREATE TABLESPACE tbs3
  DATADISK disk1 (DISK_PATH="tbs3_d1", PARALLEL_IO = 10),
           disk2 (DISK_PATH="tbs3_d2"),
           disk3 (DISK_PATH="tbs3_d3");

drop_tablespace_stmt ::= 'DROP TABLESPACE' tablespace_name

DROP TABLESPACE tbs1;

ALTER SESSION SET DDL_LOCK_TIMEOUT = 10;