#!/usr/bin/env bash
# dbsql 三方言语法复核脚本（自包含）
# 用法：在仓库根目录执行  bash docs/dialects/verify.sh
# 依赖：target/release/dbsql 与 plugins/*.so 已构建。
set -u
CLI=${CLI:-./target/release/dbsql}
ROOT=${ROOT:-/tmp/dbsql-dialect-verify}

rm -rf "$ROOT"
mkdir -p "$ROOT"
for db in tdb pdb mdb; do "$CLI" --root "$ROOT" create database "$db" >/dev/null 2>&1; done

run() {
  local d="$1" db="$2" lb="$3" sql="$4"
  echo "### [$lb] ($d) $sql"
  "$CLI" --root "$ROOT" --dialect "$d" sql "$db" "$sql" 2>&1 \
    | grep -v '^\[插件\]' | sed 's/^/    /'
  echo
}

echo "================ T-SQL ================"
run standard tdb "建表（n 显式可空）" "CREATE TABLE tt (id INT PRIMARY KEY, v VARCHAR(5), n INT NULL)"
run standard tdb "插行" "INSERT INTO tt (id, v, n) VALUES (1, 'a', 1)"
run standard tdb "默认不可空：显式 NULL 也拒" "INSERT INTO tt (id, v, n) VALUES (2, NULL, NULL)"
run standard tdb "默认不可空：省略值" "INSERT INTO tt (id) VALUES (3)"
run standard tdb "显式 NULL 列可存 NULL" "INSERT INTO tt (id, v, n) VALUES (40, 'a', NULL)"
run standard tdb "VARCHAR 长度不校验" "INSERT INTO tt (id, v, n) VALUES (4, 'abcdefgh', 4)"
run standard tdb "大小写敏感" "SELECT COUNT(*) AS n FROM tt WHERE v = 'ABCDEFGH'"
run standard tdb "CHAR(n) 建表" "CREATE TABLE tc (id INT PRIMARY KEY, ch CHAR(3))"
run standard tdb "CHAR(n) 超长" "INSERT INTO tc (id, ch) VALUES (1, 'abcd')"
run standard tdb "CHAR 单字符可用" "INSERT INTO tc (id, ch) VALUES (2, 'x')"
run standard tdb "DECIMAL 建表" "CREATE TABLE td (id INT PRIMARY KEY, amt DECIMAL(5,2))"
run standard tdb "DECIMAL 超小数位" "INSERT INTO td (id, amt) VALUES (1, 1234.567)"
run standard tdb "双引号=字符串" "SELECT \"id\" AS x FROM tt LIMIT 1"
run standard tdb "TOP n" "SELECT TOP 1 id FROM tt ORDER BY id"
run standard tdb "OFFSET/FETCH" "SELECT id FROM tt ORDER BY id OFFSET 0 ROWS FETCH NEXT 2 ROWS ONLY"
run standard tdb "CROSS JOIN" "SELECT COUNT(*) AS n FROM tt a CROSS JOIN tt b"
run standard tdb "PARTITION BY" "SELECT id, ROW_NUMBER() OVER (PARTITION BY v ORDER BY id) AS rn FROM tt"
run standard tdb "TOP PERCENT" "SELECT TOP 50 PERCENT id FROM tt"
run standard tdb "dbo 前缀" "SELECT COUNT(*) AS n FROM dbo.tt"
run standard tdb "N 字符串" "SELECT N'中文' AS s"
run standard tdb "CONVERT" "SELECT CONVERT(INT, '12') AS n"
run standard tdb "GETDATE" "SELECT GETDATE() AS d"
run standard tdb "文本加号" "SELECT 'a' + 'b' AS s"
run standard tdb "ISNULL 不存在" "SELECT ISNULL(v, 'x') FROM tt"
run standard tdb "TRUNCATE" "TRUNCATE TABLE tt"
run standard tdb "CREATE UNIQUE INDEX" "CREATE UNIQUE INDEX ux ON tt (v)"
run standard tdb "外键" "CREATE TABLE tfk (id INT PRIMARY KEY, tid INT REFERENCES tt(id))"
run standard tdb "建审计表" "CREATE TABLE tt_audit (v VARCHAR(10))"
run standard tdb "BEFORE INSERT 触发器" "CREATE TRIGGER trg_b BEFORE INSERT ON tt AS RAISE '不允许插入'"
run standard tdb "触发器拦截" "INSERT INTO tt (id, v, n) VALUES (5, 'z', 5)"
run standard tdb "DROP TRIGGER" "DROP TRIGGER trg_b"
run standard tdb "多语句触发器" "CREATE TRIGGER trg_ai AFTER INSERT ON tt AS INSERT INTO tt_audit (v) VALUES (NEW.v); INSERT INTO tt_audit (v) VALUES ('x')"
run standard tdb "触发一次" "INSERT INTO tt (id, v, n) VALUES (6, 'c', 6)"
run standard tdb "审计行数" "SELECT COUNT(*) AS n FROM tt_audit"
run standard tdb "ROW_NUMBER" "SELECT id, ROW_NUMBER() OVER (ORDER BY id) AS rn FROM tt"
run standard tdb "递归 CTE" "WITH r (n) AS (SELECT 1 UNION ALL SELECT n + 1 FROM r WHERE n < 3) SELECT n FROM r"
run standard tdb "多行 VALUES" "INSERT INTO tt (id, v, n) VALUES (7, 'g', 7), (8, 'h', 8)"
run standard tdb "LEFT/FULL JOIN" "SELECT COUNT(*) AS n FROM tt a FULL JOIN tt b ON a.id = b.id"
run standard tdb "INTERSECT" "SELECT id FROM tt INTERSECT SELECT id FROM tt WHERE id = 1"
run standard tdb "EXCEPT" "SELECT id FROM tt EXCEPT SELECT id FROM tt WHERE id = 1"
run standard tdb "复合主键+CHECK" "CREATE TABLE tcp (a INT, b INT, v INT CHECK (v > 0), PRIMARY KEY (a, b))"
run standard tdb "复合主键首行" "INSERT INTO tcp (a, b, v) VALUES (1, 1, 5)"
run standard tdb "复合主键重复" "INSERT INTO tcp (a, b, v) VALUES (1, 1, 6)"
run standard tdb "CHECK 拦截" "INSERT INTO tcp (a, b, v) VALUES (1, 2, -1)"
run standard tdb "IDENTITY 建表" "CREATE TABLE tidt (id INT IDENTITY PRIMARY KEY, v VARCHAR(5))"
run standard tdb "IDENTITY 隐式列" "INSERT INTO tidt (v) VALUES ('a')"
run standard tdb "IDENTITY 值" "SELECT id, v FROM tidt"
run standard tdb "存储过程（游标）" "CREATE PROCEDURE pc () AS BEGIN DECLARE cur CURSOR FOR SELECT id FROM tt; DECLARE @i INT; OPEN cur; FETCH NEXT FROM cur INTO @i; CLOSE cur; DEALLOCATE cur; INSERT INTO tt (id, v, n) VALUES (100, 'p', 100); END"
run standard tdb "CALL 过程" "CALL pc()"
run standard tdb "过程结果" "SELECT COUNT(*) AS n FROM tt WHERE id = 100"
run standard tdb "动态 EXEC" "CREATE PROCEDURE pdyn () AS BEGIN EXEC('INSERT INTO tt (id, v, n) VALUES (11, ''y'', 11)') END"
run standard tdb "调用动态过程" "CALL pdyn()"
run standard tdb "PRINT 过程" "CREATE PROCEDURE ppr () AS BEGIN PRINT 'hello' END"
run standard tdb "调用 PRINT" "CALL ppr()"
run standard tdb "INSERT SELECT" "INSERT INTO tt (id, v, n) SELECT id + 1000, v, n FROM tt WHERE id < 50"
run standard tdb "UPDATE FROM" "UPDATE tt SET v = 'u' FROM tt WHERE tt.id = 1"
run standard tdb "SELECT INTO" "SELECT id, v INTO tt_copy FROM tt WHERE id = 1"
run standard tdb "SELECT INTO 结果" "SELECT COUNT(*) AS n FROM tt_copy"
run standard tdb "SET 白名单" "SET NOCOUNT ON"
run standard tdb "SET QUOTED_IDENTIFIER" "SET QUOTED_IDENTIFIER ON"
run standard tdb "顶层多条语句" "SELECT 1 AS x; SELECT 2 AS y"

echo "================ PL/SQL ================"
run procedural pdb "建表" "CREATE TABLE pt (id INT PRIMARY KEY, nm VARCHAR(10))"
run procedural pdb "插字面量" "INSERT INTO pt (id, nm) VALUES (1, 'a')"
run procedural pdb "插算术值" "INSERT INTO pt (id, nm) VALUES (1+3, 'c')"
run procedural pdb "插 NULL" "INSERT INTO pt (id, nm) VALUES (2, NULL)"
run procedural pdb "多行 VALUES" "INSERT INTO pt (id, nm) VALUES (20, 'x'), (21, 'y')"
run procedural pdb "空串不是 NULL" "INSERT INTO pt (id, nm) VALUES (5, '')"
run procedural pdb "空串 IS NULL" "SELECT id FROM pt WHERE nm IS NULL"
run procedural pdb "空串等值" "SELECT id FROM pt WHERE nm = ''"
run procedural pdb "NULL 排序" "SELECT id, nm FROM pt ORDER BY nm ASC"
run procedural pdb "ROWNUM 投影" "SELECT ROWNUM, id FROM pt"
run procedural pdb "ROWNUM 过滤" "SELECT id FROM pt WHERE ROWNUM = 1"
run procedural pdb "q 引用" "SELECT q'[it''s]' AS s FROM DUAL"
run procedural pdb "|| 拼接" "SELECT 'a' || 'b' AS s FROM DUAL"
run procedural pdb "无 FROM" "SELECT 1 AS x"
run procedural pdb "NVL" "SELECT NVL(nm, 'none') AS s FROM pt WHERE id = 2"
run procedural pdb "TO_CHAR" "SELECT TO_CHAR(123) AS s FROM DUAL"
run procedural pdb "SUBSTR" "SELECT SUBSTR('abcdef', 2, 3) AS s FROM DUAL"
run procedural pdb "SYSDATE" "SELECT SYSDATE AS d FROM DUAL"
run procedural pdb "DECODE 不存在" "SELECT DECODE(id, 1, 'a', 'b') FROM pt"
run procedural pdb "TO_DATE 不存在" "SELECT TO_DATE('2026-01-01', 'YYYY-MM-DD') AS d FROM DUAL"
run procedural pdb "INSERT 函数值" "INSERT INTO pt (id, nm) VALUES (6, UPPER('a'))"
run procedural pdb "INSERT SELECT" "INSERT INTO pt (id, nm) SELECT 9, 'q' FROM DUAL"
run procedural pdb "UPDATE 字面量" "UPDATE pt SET nm = 'z' WHERE id = 1"
run procedural pdb "UPDATE 列引用" "UPDATE pt SET nm = nm WHERE id = 1"
run procedural pdb "UPDATE 子查询" "UPDATE pt SET nm = 'q' WHERE id IN (SELECT id FROM pt WHERE id = 1)"
run procedural pdb "DELETE" "DELETE FROM pt WHERE id = 5"
run procedural pdb "匿名块" "BEGIN NULL; END;"
run procedural pdb "EXCEPTION" "BEGIN NULL; EXCEPTION WHEN OTHERS THEN NULL; END;"
run procedural pdb "MINUS" "SELECT id FROM pt MINUS SELECT id FROM pt WHERE id = 1"
run procedural pdb "DUAL 别名" "SELECT 1 AS x FROM DUAL d"
run procedural pdb "无 FROM 聚合" "SELECT COUNT(*) AS n FROM DUAL"
run procedural pdb "CTE" "WITH x AS (SELECT id FROM pt) SELECT COUNT(*) AS n FROM x"
run procedural pdb "递归 CTE" "WITH r (n) AS (SELECT 1 FROM DUAL UNION ALL SELECT n + 1 FROM r WHERE n < 3) SELECT n FROM r"
run procedural pdb "UNION" "SELECT id FROM pt UNION SELECT id FROM pt"
run procedural pdb "INTERSECT" "SELECT id FROM pt INTERSECT SELECT id FROM pt WHERE id = 1"
run procedural pdb "EXCEPT 不识别" "SELECT id FROM pt EXCEPT SELECT id FROM pt WHERE id = 1"
run procedural pdb "ROW_NUMBER" "SELECT id, ROW_NUMBER() OVER (ORDER BY id) AS rn FROM pt"
run procedural pdb "FETCH FIRST" "SELECT id FROM pt ORDER BY id FETCH FIRST 1 ROWS ONLY"
run procedural pdb "OFFSET+FETCH NEXT" "SELECT id FROM pt ORDER BY id OFFSET 1 ROWS FETCH NEXT 1 ROWS ONLY"
run procedural pdb "单独 FETCH NEXT" "SELECT id FROM pt ORDER BY id FETCH NEXT 1 ROWS ONLY"
run procedural pdb "存储过程（字符串）" "CREATE PROCEDURE pz () AS BEGIN INSERT INTO pt (id, nm) VALUES (10, 'p'); END"
run procedural pdb "CALL 过程" "CALL pz()"
run procedural pdb "过程结果" "SELECT COUNT(*) AS n FROM pt WHERE id = 10"
run procedural pdb "CREATE OR REPLACE VIEW" "CREATE OR REPLACE VIEW pv AS SELECT id FROM pt"
run procedural pdb "查视图" "SELECT COUNT(*) AS n FROM pv"
run procedural pdb "ALTER ADD" "ALTER TABLE pt ADD flag INT"
run procedural pdb "ALTER DROP COLUMN" "ALTER TABLE pt DROP COLUMN flag"
run procedural pdb "CREATE INDEX" "CREATE INDEX ix ON pt (nm)"
run procedural pdb "CREATE TRIGGER" "CREATE TRIGGER ptrg BEFORE INSERT ON pt AS RAISE 'no'"
run procedural pdb "CREATE SEQUENCE" "CREATE SEQUENCE s1 START WITH 1"
run procedural pdb "TRUNCATE" "TRUNCATE TABLE pt"
run procedural pdb "MERGE" "MERGE INTO pt USING pt ON (pt.id = pt.id) WHEN MATCHED THEN UPDATE SET nm = 'x'"
run procedural pdb "CONNECT BY" "SELECT id FROM pt CONNECT BY PRIOR id = id"
run procedural pdb "FOR UPDATE" "SELECT id FROM pt FOR UPDATE"
run procedural pdb "NUMBER 建表" "CREATE TABLE pn (id INT PRIMARY KEY, amt NUMBER(5,2))"
run procedural pdb "NUMBER 超小数位" "INSERT INTO pn (id, amt) VALUES (1, 1234.567)"
run procedural pdb "VARCHAR2 建表" "CREATE TABLE pv2 (id INT PRIMARY KEY, s VARCHAR2(3))"
run procedural pdb "VARCHAR2 超长" "INSERT INTO pv2 (id, s) VALUES (1, 'abcdef')"
run procedural pdb "CHAR 超长" "CREATE TABLE pc2 (id INT PRIMARY KEY, ch CHAR(3))"
run procedural pdb "CHAR 插入 abcd" "INSERT INTO pc2 (id, ch) VALUES (1, 'abcd')"
run procedural pdb "复合主键缺 NOT NULL" "CREATE TABLE pcp (a INT, b INT, v INT, PRIMARY KEY (a, b))"
run procedural pdb "DATE 建表" "CREATE TABLE pd (id INT PRIMARY KEY, d DATE)"
run procedural pdb "DATE 字面量" "INSERT INTO pd (id, d) VALUES (1, DATE '2026-01-02')"
run procedural pdb "DATE 写字符串" "INSERT INTO pd (id, d) VALUES (1, '2026-01-02')"
run procedural pdb "DATE 读回" "SELECT d FROM pd"
run procedural pdb "COMMIT" "COMMIT"
run procedural pdb "外键" "CREATE TABLE pfk (id INT PRIMARY KEY, tid INT REFERENCES pt(id))"
run standard tdb "建 T-SQL 过程（隔离用）" "CREATE PROCEDURE only_tsql () AS BEGIN INSERT INTO tt (id, v, n) VALUES (900, 'ok', 900); END"
run procedural pdb "PL/SQL 调 T-SQL 过程" "CALL only_tsql()"

echo "================ MySQL ================"
run mysql mdb "建表" "CREATE TABLE mt (id INT NOT NULL PRIMARY KEY, v VARCHAR(5), n INT)"
run mysql mdb "插行" "INSERT INTO mt (id, v, n) VALUES (1, 'a', 1)"
run mysql mdb "多行 VALUES" "INSERT INTO mt (id, v, n) VALUES (3, 'c', 3), (4, 'd', 4)"
run mysql mdb "反引号" "SELECT \`id\` FROM \`mt\` LIMIT 0, 1"
run mysql mdb "LIMIT n OFFSET m" "SELECT id FROM mt LIMIT 1 OFFSET 0"
run mysql mdb "无 FROM" "SELECT 1 AS x"
run mysql mdb "CTE" "WITH x AS (SELECT id FROM mt) SELECT COUNT(*) AS n FROM x"
run mysql mdb "递归 CTE" "WITH RECURSIVE r (n) AS (SELECT 1 UNION ALL SELECT n + 1 FROM r WHERE n < 3) SELECT n FROM r"
run mysql mdb "UNION" "SELECT id FROM mt UNION SELECT id FROM mt"
run mysql mdb "INTERSECT" "SELECT id FROM mt INTERSECT SELECT id FROM mt WHERE id = 1"
run mysql mdb "EXCEPT" "SELECT id FROM mt EXCEPT SELECT id FROM mt WHERE id = 1"
run mysql mdb "ROW_NUMBER" "SELECT id, ROW_NUMBER() OVER (ORDER BY id) AS rn FROM mt"
run mysql mdb "CROSS JOIN" "SELECT COUNT(*) AS n FROM mt a CROSS JOIN mt b"
run mysql mdb "INSERT 函数值" "INSERT INTO mt (id, v, n) VALUES (2, UPPER('b'), 2)"
run mysql mdb "INSERT SELECT" "INSERT INTO mt (id, v, n) SELECT id + 10, v, n FROM mt"
run mysql mdb "INSERT SET" "INSERT INTO mt SET id = 9"
run mysql mdb "多表 UPDATE" "UPDATE mt, mt2 SET mt.v = 'm' WHERE mt.id = mt2.id"
run mysql mdb "AUTO_INCREMENT 缺 NOT NULL" "CREATE TABLE na (id INT AUTO_INCREMENT PRIMARY KEY, v VARCHAR(5))"
run mysql mdb "唯一键表" "CREATE TABLE mu (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, k INT, UNIQUE KEY uk (k))"
run mysql mdb "基础行" "INSERT INTO mu (k) VALUES (9)"
run mysql mdb "INSERT IGNORE 唯一键冲突" "INSERT IGNORE INTO mu (k) VALUES (9)"
run mysql mdb "INSERT IGNORE 主键冲突" "INSERT IGNORE INTO mu (id, k) VALUES (1, 10)"
run mysql mdb "ON DUPLICATE KEY UPDATE" "INSERT INTO mu (id, k) VALUES (1, 11) ON DUPLICATE KEY UPDATE k = 11"
run mysql mdb "ON DUPLICATE 结果" "SELECT id, k FROM mu WHERE id = 1"
run mysql mdb "REPLACE 主键" "REPLACE INTO mu (id, k) VALUES (1, 12)"
run mysql mdb "REPLACE 唯一键冲突" "REPLACE INTO mu (id, k) VALUES (2, 12)"
run mysql mdb "大小写敏感" "SELECT COUNT(*) AS n FROM mt WHERE v = 'ABCDEFGH'"
run mysql mdb "VARCHAR 不校验" "INSERT INTO mt (id, v, n) VALUES (5, 'abcdefgh', 5)"
run mysql mdb "SHOW COLUMNS" "SHOW COLUMNS FROM mu"
run mysql mdb "SHOW CREATE TABLE（只回显主键，丢 UNIQUE KEY）" "SHOW CREATE TABLE mu"
run mysql mdb "SHOW TABLES LIKE" "SHOW TABLES LIKE 'm%'"
run mysql mdb "SHOW DATABASES" "SHOW DATABASES"
run mysql mdb "SHOW CREATE 不存在的表" "SHOW CREATE TABLE nope"
run mysql mdb "SELECT @v" "SELECT @v"
run mysql mdb "GROUP_CONCAT 不存在" "SELECT GROUP_CONCAT(k) FROM mu"
run mysql mdb "NOW/UPPER" "SELECT NOW() AS t, UPPER('ab') AS u"
run mysql mdb "IFNULL" "SELECT IFNULL(v, 'none') AS s FROM mt WHERE id = 1"
run mysql mdb "过程（数字参数）" "CREATE PROCEDURE pnum (IN n INT) BEGIN INSERT INTO mu (k) VALUES (n); END"
run mysql mdb "CALL 数字过程" "CALL pnum(7)"
run mysql mdb "数字过程结果" "SELECT COUNT(*) AS n FROM mu WHERE k = 7"
run mysql mdb "过程（字符串字面量）" "CREATE PROCEDURE pstr () BEGIN INSERT INTO mt (v) VALUES ('x'); END"
run mysql mdb "CALL 字符串过程" "CALL pstr()"
run mysql mdb "过程（SELECT 字符串）" "CREATE PROCEDURE psel () BEGIN SELECT 'x' AS s; END"
run mysql mdb "CALL psel" "CALL psel()"
run mysql mdb "过程控制流" "CREATE PROCEDURE pctl (IN n INT) BEGIN DECLARE i INT; SET i = 0; WHILE i < n DO INSERT INTO mu (k) VALUES (100 + i); SET i = i + 1; END WHILE; END"
run mysql mdb "CALL 控制流" "CALL pctl(3)"
run mysql mdb "控制流结果" "SELECT COUNT(*) AS n FROM mu WHERE k >= 100 AND k < 110"
run mysql mdb "循环上限" "CREATE PROCEDURE ploop (IN n INT) BEGIN DECLARE i INT; SET i = 0; WHILE i < n DO SET i = i + 1; END WHILE; END"
run mysql mdb "CALL 10001 次" "CALL ploop(10001)"
run mysql mdb "表达式默认值" "CREATE TABLE ed (id INT NOT NULL PRIMARY KEY, v VARCHAR(5) DEFAULT UPPER('a'))"
run mysql mdb "CURRENT_TIMESTAMP 默认值" "CREATE TABLE dt (id INT NOT NULL PRIMARY KEY, ts DATETIME DEFAULT CURRENT_TIMESTAMP)"
run mysql mdb "生成列" "CREATE TABLE gc (id INT NOT NULL PRIMARY KEY, v INT, w INT GENERATED ALWAYS AS (v + 1))"
run mysql mdb "ON UPDATE CURRENT_TIMESTAMP" "CREATE TABLE mo (id INT NOT NULL PRIMARY KEY, ts DATETIME ON UPDATE CURRENT_TIMESTAMP)"
run mysql mdb "CREATE INDEX" "CREATE INDEX ix ON mt (v)"
run mysql mdb "DROP INDEX" "DROP INDEX ix ON mt"
run mysql mdb "ALTER ADD INDEX" "ALTER TABLE mt ADD INDEX ix2 (v)"
run mysql mdb "多子句 ALTER" "ALTER TABLE mt ADD COLUMN e INT, ADD COLUMN f INT"
run mysql mdb "单子句 ALTER" "ALTER TABLE mt ADD COLUMN e INT"
run mysql mdb "ALTER MODIFY" "ALTER TABLE mt MODIFY v VARCHAR(10)"
run mysql mdb "CREATE OR REPLACE VIEW" "CREATE OR REPLACE VIEW mv AS SELECT id FROM mt"
run mysql mdb "CREATE TRIGGER" "CREATE TRIGGER trg AFTER INSERT ON mt AS INSERT INTO mu (k) VALUES (1)"
run mysql mdb "外键" "CREATE TABLE mfk (id INT NOT NULL PRIMARY KEY, tid INT, FOREIGN KEY (tid) REFERENCES mt(id))"
run mysql mdb "DROP TABLE IF EXISTS 存在的表" "DROP TABLE IF EXISTS mt"
run mysql mdb "DROP TABLE IF EXISTS 不存在的表" "DROP TABLE IF EXISTS nope"
run mysql mdb "重建表" "CREATE TABLE mt (id INT NOT NULL PRIMARY KEY, v VARCHAR(5), n INT)"
run mysql mdb "TRUNCATE" "TRUNCATE TABLE mu"
run mysql mdb "TRUNCATE 后自增" "INSERT INTO mu (k) VALUES (1)"
run mysql mdb "自增重置" "SELECT id, k FROM mu"
run mysql mdb "MySQL 调 T-SQL 过程" "CALL only_tsql()"