DDL

-- 5 列の LOG テーブル ctest を作成
CREATE TABLE ctest (id INTEGER, name VARCHAR(20), sipv4 IPV4, dipv6 IPV6, comment TEXT);

-- TAG テーブルを作成
CREATE TAG TABLE tag_time (name VARCHAR(20) PRIMARY KEY, time DATETIME BASETIME, value DOUBLE SUMMARIZED);
CREATE TAG TABLE tag_time_ext (name VARCHAR(20) PRIMARY KEY, time DATETIME BASETIME, value DOUBLE SUMMARIZED, value2 FLOAT, int_column INT);
CREATE TAG TABLE tag_distance (name VARCHAR(20) PRIMARY KEY, distance_m DOUBLE BASE DISTANCE, value DOUBLE, quality INT);
CREATE TAG TABLE tag_distance_meta (name VARCHAR(20) PRIMARY KEY, distance_m LONG BASEDISTANCE, value DOUBLE) METADATA (route_id VARCHAR(20));

CREATE TABLE special_tbl ( "with.dot" INTEGER );

-- c1 に NOT NULL を指定し、c2 には指定しない
CREATE TABLE t1(c1 INTEGER NOT NULL, c2 VARCHAR(200));

create volatile table t1111 (i1 integer);
Created successfully.
Mach> desc t1111;
 
----------------------------------------------------------------
NAME                          TYPE                LENGTH       
----------------------------------------------------------------
_ARRIVAL_TIME                 datetime            8              
I1                            integer             4              
 
Mach>insert into t1111 values (1);
1 row(s) inserted.
Mach>select _rid from t1111;
_rid                
-----------------------
0                   
[1] row(s) inserted.
 
Mach>select i1 from t1111 where _rid = 0;
i1         
--------------
1          
[1] row(s) selected.

-- VARCHAR に MINMAX_CACHE_SIZE=0 を指定することは可能
CREATE TABLE ctest (id INTEGER, name VARCHAR(100) PROPERTY(MINMAX_CACHE_SIZE = 0));
Created successfully.
Mach>
 
-- id 列にキャッシュを適用
CREATE TABLE ctest2 (id INTEGER PROPERTY(MINMAX_CACHE_SIZE = 10240), name VARCHAR(100) PROPERTY(MINMAX_CACHE_SIZE = 0));
Created successfully.
Mach>
 
-- id1、id2、id3 に適用
CREATE TABLE ctest3 (id1 INTEGER PROPERTY(MINMAX_CACHE_SIZE = 10240), name VARCHAR(100) PROPERTY(MINMAX_CACHE_SIZE = 0), id2 LONG PROPERTY(MINMAX_CACHE_SIZE = 1024), id3 IPV4 PROPERTY(MINMAX_CACHE_SIZE = 1024), id4 SHORT);
Created successfully.
Mach>
 
-- 列単位で MINMAX_CACHE_SIZE を指定、または 0 で無効化
CREATE TABLE ctest4 (id1 INTEGER PROPERTY(MINMAX_CACHE_SIZE=10240), name VARCHAR(100) PROPERTY(MINMAX_CACHE_SIZE=0), id2 LONG PROPERTY(MINMAX_CACHE_SIZE=10240), id3 IPV4 PROPERTY(MINMAX_CACHE_SIZE=0), id4 SHORT);
Created successfully.
Mach>

CREATE LOOKUP TABLE table_name (v1 LONG PROPERTY(SEQUENCE=1) PRIMARY KEY, v2 VARCHAR(10));

-- シーケンス列に nextval で次の値を挿入
INSERT INTO table_name (v1, v2) values (nextval(v1), 'aaaa');
   
-- シーケンス列に直接値を挿入
INSERT INTO table_name (v1, v2) values (100, 'aaaa');
   
-- シーケンス列に計算値を挿入
INSERT INTO table_name (v1, v2) values (100 + 1, 'aaaa');
   
-- シーケンス列を持つ Lookup の正常な検索
SELECT v1, v2 FROM table_name;
  
-- 不正な検索：nextval は INSERT 専用
SELECT nextval(v1), v2 FROM table_name;

CREATE VIEW v_example AS
SELECT id, name
FROM t1;

DROP VIEW v_example;

drop_table_stmt ::= 'DROP TABLE' table_name

-- Example
DROP TABLE TableName;

create_tablespace_stmt ::= 'CREATE TABLESPACE' tablespace_name 'DATADISK' datadisk_list
datadisk_list ::= data_disk ( ',' data_disk )*
data_disk ::= disk_name data_disk_property
data_disk_property ::= '(' 'DISK_PATH' '=' '"' path '"' ( ',' 'PARALLEL_IO' '=' number )? ')'

-- 例
create tablespace tbs1 datadisk disk1 (disk_path=""); -- $MACHBASE_HOME/dbs/（この場所に作成）
create tablespace tbs1 datadisk disk1 (disk_path="tbs1_disk1"); -- $MACHBASE_HOME/dbs/tbs1_disk1（この場所に作成。tbs1_disk1 は事前作成が必要）
create tablespace tbs2 datadisk disk1 (disk_path="tbs2_disk1", parallel_io = 5);
create tablespace tbs1 datadisk disk1 (disk_path="tbs1_disk1", parallel_io = 10), disk2 (disk_path="tbs1_disk2"), disk3 (disk_path="tbs1_disk3");

drop_table_stmt ::= 'DROP TABLESPACE' tablespace_name

-- Example
DROP TABLESPACE TablespaceName;

create_index_stmt ::= 'CREATE' 'INDEX' index_name 'ON' table_name '(' column_name ')' index_type? table_space? index_property_list?
index_type ::= 'INDEX_TYPE' ( 'LSM' | 'KEYWORD' | 'BITMAP' | 'REDBLACK' )
table_space ::= 'TABLESPACE' table_space_name
index_property_list ::= ( 'MAX_LEVEL' | 'PAGE_SIZE' | 'BITMAP_ENCODE' | 'PART_VALUE_COUNT' ) '=' value

-- 例
-- c1 にインデックスを適用
CREATE INDEX index1 on table1 ( c1 );
-- LSM を明示的に適用
CREATE INDEX index_lsm on table1 ( c1 ) INDEX_TYPE LSM;
-- VARCHAR の var_column に KEYWORD を作成。page_size は 100000
CREATE INDEX index2 on table1 (var_column) INDEX_TYPE KEYWORD PAGE_SIZE=100000;

CREATE TAG TABLE tag_log (
    name  VARCHAR(40) PRIMARY KEY,
    time  DATETIME BASETIME,
    value JSON
);

-- JSON のドット省略記法
CREATE INDEX tag_log_sensor_idx ON tag_log (value.sensor.name);

-- 従来の JSONPath 矢印構文
CREATE INDEX tag_log_metric_idx ON tag_log (value->'$.metric');

-- 配列添字と二重引用符のキー
CREATE INDEX tag_log_item_idx ON tag_log (value.items[0]."product-id");

CREATE TAG TABLE tag_log_q (
    name    VARCHAR(40) PRIMARY KEY,
    time    DATETIME BASETIME,
    "value" JSON
);

CREATE INDEX tag_log_q_idx ON tag_log_q ("value"->'$.sensor.name');

CREATE TAG TABLE tag_log_kw (
    name   VARCHAR(40) PRIMARY KEY,
    time   DATETIME BASETIME,
    "LEFT" JSON
);

CREATE INDEX tag_log_kw_idx ON tag_log_kw (left->'$.sensor.name');

drop_index_stmt ::= 'DROP INDEX' index_name

-- Example
DROP INDEX IndexName;

alter_table_add_stmt ::= 'ALTER TABLE' table_name 'ADD COLUMN' '(' column_name column_type ( 'DEFAULT' value )? ')'

-- Example-1
alter table atest2 add column (id4 float);
 
-- Example-2
alter table atest2 add column (id6 double  default 5);
alter table atest2 add column (id7 ipv4  default '192.168.0.1');
alter table atest2 add column (id8 varchar(4) default 'hello');

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

-- Example
alter table atest2 drop column (id4);
alter table atest2 drop column (id8);

alter_table_metadata_add_stmt ::= 'ALTER TABLE' table_name 'METADATA ADD COLUMN' '(' column_name column_type ( 'DEFAULT' value )? ')'

-- 例：TAG にメタデータ列を追加
ALTER TABLE altertbl METADATA ADD COLUMN (m1 DOUBLE);
ALTER TABLE altertbl METADATA ADD COLUMN (m2 VARCHAR(100));
ALTER TABLE altertbl METADATA ADD COLUMN (m3 INTEGER DEFAULT 0);

alter_table_metadata_drop_stmt ::= 'ALTER TABLE' table_name 'METADATA DROP COLUMN' '(' column_name ')'

-- 例：TAG のメタデータ列を削除
ALTER TABLE altertbl METADATA DROP COLUMN (m1);
ALTER TABLE altertbl METADATA DROP COLUMN (m2);

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

-- Log の例
alter table atest2 rename column id7 to id7_rename;

-- Tag の例
alter table tag rename column v0001 to vmax;

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

ALTER TABLE table_name MODIFY COLUMN (column_name VARCHAR(new_size));

-- 例：次のテーブルがある場合
-- create table atest5 (id integer, name varchar(5), id3 double, id4 float);
 
-- エラー：別の型へ変更できない
alter table atest5 modify column (id varchar(10));
 
-- エラー：VARCHAR の長さは短縮できない
alter table atest5 modify column (name varchar(3));
 
-- エラー：VARCHAR の最大サイズは 32767 を超えられない
alter table atest5 modify column (name varchar(32768));
 
-- 成功
alter table atest5 modify column (name varchar(128));

ALTER TABLE table_name MODIFY COLUMN column_name SET MINMAX_CACHE_SIZE=value;

-- 例：次のテーブルがある場合
create table atest9 (id integer, name varchar(100));
 
-- エラー：VARCHAR には適用できない
alter table atest9 modify column name set minmax_cache_size=0;
[ERR-02139 : MINMAX CACHE is not allowed for VARCHAR column(NAME).]
 
-- 変更成功
alter table atest9 modify column id set minmax_cache_size=10240;

ALTER TABLE table_name MODIFY COLUMN column_name NOT NULL;

-- t1.c1 に NOT NULL を追加
alter table t1 modify column c1 not null;

ALTER TABLE table_name MODIFY COLUMN column_name NULL;

-- t1.c1 の NOT NULL を解除
alter table t1 modify column c1 null;

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

-- worker を employee に変更
ALTER TABLE worker RENAME TO employee;

alter_table_add_retention_stmt ::=  'ALTER TABLE' table_name 'ADD RETENTION' policy_name

ALTER TABLE tag ADD RETENTION policy_1d_1h;

alter_table_drop_retention_stmt ::=  'ALTER TABLE' table_name 'DROP RETENTION'

ALTER TABLE tag DROP RETENTION;

alter_tablespace_stmt ::= 'ALTER TABLESPACE' table_name 'MODIFY DATADISK' disk_name 'SET' 'PARALLEL_IO' '=' value

-- Example
ALTER TABLESPACE tbs1 MODIFY DATADISK disk1 SET PARALLEL_IO = 10;

truncate_table_stmt ::= 'TRUNCATE TABLE' table_name

-- ctest の全データを削除
Mach> truncate table ctest;
Truncated successfully.

create_rollup_stmt ::= 'CREATE ROLLUP' rollup_name 'ON' src_table_name '('src_table_column')' 'INTERVAL' number ('SEC' | 'MIN' | 'HOUR')

-- Tag の value 列を対象とするロールアップを作成
Mach> CREATE ROLLUP _rollup_tag_value_sec ON tag(value) INTERVAL 1 SEC;
Executed successfully

CREATE TAG TABLE tag_json (
    name  VARCHAR(40) PRIMARY KEY,
    time  DATETIME BASETIME,
    value JSON
);

-- JSON のドット省略記法
CREATE ROLLUP tag_json_metric_ru
ON tag_json (value.metric)
INTERVAL 1 SEC;

-- 従来の JSONPath 矢印構文
CREATE ROLLUP tag_json_metric_arrow_ru
ON tag_json (value->'$.metric')
INTERVAL 1 SEC;

-- 配列添字と二重引用符のキー
CREATE ROLLUP tag_json_item_ru
ON tag_json (value.items[0]."metric-id")
INTERVAL 1 SEC;

CREATE TAG TABLE tag_json_q (
    name    VARCHAR(40) PRIMARY KEY,
    time    DATETIME BASETIME,
    "value" JSON
);

CREATE ROLLUP tag_json_q_ru
ON tag_json_q ("value"->'$.metric')
INTERVAL 1 SEC;

CREATE TAG TABLE tag_json_kw (
    name   VARCHAR(40) PRIMARY KEY,
    time   DATETIME BASETIME,
    "LEFT" JSON
);

CREATE ROLLUP tag_json_kw_ru
ON tag_json_kw (left->'$.metric')
INTERVAL 1 SEC;

create_conditional_rollup_stmt ::= 'CREATE ROLLUP' rollup_name
                                   ( 'ON' src_table_name '('src_table_column')'
                                   | 'FROM' src_rollup_table_name )
                                   'INTERVAL' number ('SEC' | 'MIN' | 'HOUR')
                                   'WHERE' predicate

-- 条件付き：value2=0 の行だけを集計
Mach> CREATE ROLLUP _rollup_tag_value_min_ok
      ON tag(value)
      INTERVAL 1 MIN
      WHERE value2 = 0;
Executed successfully

create_custom_rollup_stmt ::= 'CREATE ROLLUP' rollup_name
                              'INTO' '(' dest_table_name ')'
                              'AS' '(' select_stmt ')'
                              'INTERVAL' number ('SEC' | 'MIN' | 'HOUR')
                              [ 'WAKEUP INTERVAL' number ('SEC' | 'MIN' | 'HOUR') ]

-- ユーザー定義集計を TAG に保存するカスタムロールアップ
Mach> CREATE ROLLUP rollup_stock_1m
      INTO (stock_rollup_1m)
      AS (
        SELECT code,
               DATE_TRUNC('minute', time) AS time,
               SUM(price)                 AS sum_price,
               COUNT(*)                   AS cnt
          FROM stock_tick
         GROUP BY code, time
      )
      INTERVAL 1 MIN;
Executed successfully

drop_rollup_stmt ::= 'DROP ROLLUP' rollup_name

-- ロールアップを削除
Mach> DROP ROLLUP _rollup_tag_value_sec;
Executed successfully

alter_rollup_start_stop_stmt ::= 'ALTER ROLLUP' rollup_name ( 'START' | 'STOP' )
alter_rollup_force_stmt      ::= 'ALTER ROLLUP' rollup_name 'FORCE'
alter_rollup_wakeup_stmt     ::= 'ALTER ROLLUP' rollup_name 'WAKEUP'
alter_rollup_wakeup_int_stmt ::= 'ALTER ROLLUP' rollup_name 'SET WAKEUP INTERVAL' number ( 'SEC' | 'MIN' | 'HOUR' )

-- ワーカーを開始、停止
ALTER ROLLUP _rollup_tag_value_sec START;
ALTER ROLLUP _rollup_tag_value_sec STOP;

-- 直ちに起動通知（待機しない）
ALTER ROLLUP _rollup_tag_value_sec WAKEUP;

-- 直ちに実行し、完了を待つ
ALTER ROLLUP _rollup_tag_value_sec FORCE;

-- 起動間隔を短縮（集計間隔を割り切る必要あり）
ALTER ROLLUP _rollup_tag_value_sec SET WAKEUP INTERVAL 1 SEC;

create_retention_stmt ::= 'CREATE RETENTION' policy_name 'DURATION' duration ( 'MONTH' | 'DAY' ) 'INTERVAL' interval ( 'DAY' | 'HOUR' )

-- Creates a retention policy.
Mach> CREATE RETENTION policy_1d_1h DURATION 1 DAY INTERVAL 1 HOUR;
Executed successfully

drop_retention_stmt ::= 'DROP RETENTION' policy_name

-- Drops the retention policy.
Mach> DROP RETENTION policy_1d_1h;
Executed successfully