Skip to content

sql-reference.md

Latest commit

 

History

History
1730 lines (1339 loc) · 114 KB

sql-reference.md

File metadata and controls

1730 lines (1339 loc) · 114 KB
Raw
layout default
title SQL 参考
description 当前版本真实支持的数据面与控制面 SQL 语法、限制和示例。
permalink /sql-reference/

想直接复制完整场景化示例，可先看 [SQL Cookbook]({{ '/sql-cookbook/' | relative_url }})；本页更偏向能力边界与精确语法说明。

数据面 SQL

标识符大小写合同（GH-Issue #211）

关系表与 measurement 保留创建时的名称拼写，不折叠大小写。普通 SQL 引用按 OrdinalIgnoreCase 匹配；双引号引用按 Ordinal 精确匹配，与当前语言区域及数据 collation 无关。

创建名称 catalog 名称 引用方式
CREATE TABLE Device (...) Device Device、DEVICE、device 或 "Device"；"device" 不匹配
CREATE TABLE "Device" (...) Device 与上行相同；双引号在引用时要求精确拼写
已存在 Device 时创建 device 或 "device" 拒绝 禁止新增仅大小写不同的同作用域名称

该规则适用于 SQL 表名、measurement 名称、视图及物化视图名称、关系表列名、measurement 的 TAG/FIELD 列名，以及别名、CTE 名和相应的限定符。表、measurement、视图和物化视图共享 SQL 数据源命名空间。双引号还允许空格、特殊字符和保留字；其中 "" 表示名称内的一个 "。SHOW、DESCRIBE 和结果列元数据返回已保存的原名。旧 catalog 若已有仅大小写不同的名称，普通引用报歧义，须用双引号精确检查并显式迁移；数据库不自动改写或合并。跨模型精确同名无法由引号消歧时，须先用指明对象类型的 DDL 迁移冲突对象。

Point、Line Protocol 等摄取方式的 measurement/TAG/FIELD 名称解析到已有 schema 的拼写，避免仅因大小写变化新增列或 series。此规则不改变字符串数据值、列数据 collation、JSON 文档属性键或其他数据模型的数据键语义。单引号表示字符串值，双引号表示 SQL 标识符。选择依据与 PostgreSQL/Oracle 的差别见标识符决策记录。

标量表达式与数值语义

SELECT 投影和关系表 UPDATE ... SET 支持由列、字面量、括号、标量函数及以下运算符组成的数值表达式：

SELECT 2 * 5 + 1 AS constant_value;
SELECT value + 1 AS next_value, (high - low) * 0.5 AS adjusted FROM readings;
UPDATE counters SET value = value + 1 WHERE id = 1;
  • 支持二元 +、-、*、/、%、整数按位 & / | 和一元 +、-；优先级为一元运算 > 乘除取模 > 加减 > 按位与 & > 按位或 |，括号可显式改变顺序。
  • 整数加、减、乘、取模保留 Int64；任一操作数为浮点时返回 Float64；除法始终返回 Float64，所以 5 / 2 返回 2.5。
  • 关系表 SUM(INT) 保留 Int64 返回类型；任何累加中间结果超出有符号 Int64 范围时返回执行错误，不会转为 Float64。需要更大的有限精度结果时，可显式对输入使用 CAST(value AS DECIMAL)，但 DECIMAL 也受其精度范围约束。
  • DECIMAL / NUMERIC 操作数保持 System.Decimal 精度；DECIMAL(p,s) 的精度范围为 1..38 且 0 <= s <= p，省略括号时使用 (38,28)。关系表中的值以 16 字节 decimal payload 持久化，schema format v9 保存声明的 precision/scale；超出 Decimal 范围或声明精度/小数位无效时拒绝执行。
  • 按位 & / | 只接受整数操作数并返回 Int64，任一操作数为 NULL 时结果为 NULL；浮点、字符串或其他非整数操作数会返回包含“只支持整数操作数”的中文执行错误。
  • 任一算术操作数为 NULL 时结果为 NULL。除数或模数为 0 时抛出执行错误，不返回 Infinity / NaN。
  • + 只做数值加法，不把字符串隐式转成数字，也不做字符串拼接。字符串连接使用 concat(...)；其中 NULL 参数按空字符串处理。
  • 支持聚合结果外包算术或标量函数，例如 count(*) + 1、round(avg(value) + 0.25, 2)；关系表、measurement 和 document collection 查询遵循同一规则。
  • 普通关系表和 measurement 投影还支持 searched CASE WHEN、比较、inclusive BETWEEN / NOT BETWEEN、LIKE / ILIKE、AND / OR / NOT、IS [NOT] NULL 及不含子查询的 IN / NOT IN，并按 SQL 三值逻辑保留 UNKNOWN；BOOL 列也可在 UPDATE SET 右值中直接接收这类谓词结果。
  • 支持无 FROM 的常量表达式查询，例如 SELECT 2 * 5 + 1，便于探活和计算。
  • 相同基础投影语义也适用于 JOIN、JSON 虚拟表、向量/混合搜索、INFORMATION_SCHEMA 以及内置 forecast(...) / knn(...) 表值函数。
  • a++、a--、a += 1、a -= 1 不是 SQL 赋值语法，不支持；应写 SET a = a + 1。SET a = +2 合法，但含义只是把正数 2 赋给 a。

显式类型转换 CAST

使用 CAST(expr AS type) 进行显式转换。当前支持 INT、FLOAT、DECIMAL/NUMERIC、BOOL、STRING、DATETIME、TIME、BLOB 和 JSON：

SELECT CAST('42' AS INT), CAST(1 AS STRING), CAST(1704067200000 AS DATETIME);
SELECT CAST('9007199254740993.125' AS DECIMAL(20,3));
SELECT CAST('12:34:56.1234567' AS TIME);
SELECT CAST(text_value AS INT) FROM values_table WHERE CAST(text_value AS INT) > 0;

NULL 转换到任一已支持目标类型仍为 NULL。DECIMAL / NUMERIC 转换保持十进制值，不经过 Float64；括号中的 precision/scale 仅用于校验目标声明，未声明时使用 (38,28)。TIME 只接受不带日期/时区的 HH:mm[:ss[.fffffff]] 文本、TimeOnly 或小于 24 小时的 TimeSpan，以 ticks 精度保存；24:00:00、负值、跨日值以及 DATETIME/DateTimeOffset 到 TIME 的隐式取时钟转换均拒绝。整数转换使用不变量格式并截断有限浮点的小数部分；布尔转换接受 TRUE/FALSE 或数值 0/1；DATETIME 输入可以是 UTC/ISO-8601 文本或 Unix 毫秒，结果统一为 UTC；BLOB 的字符串输入按 UTF-8 编码，JSON 当前保留字符串文本。格式错误、溢出、非有限浮点和非法布尔值会返回执行错误。VECTOR 与 GEOPOINT 作为 CAST 目标暂不支持，不会隐式构造对应模型值。

关系表 TIME

关系表列可声明 TIME，返回 CLR TimeOnly，并按 TimeOnly.Ticks 以 8 字节持久化；schema format v10 兼容读取旧版 schema。取值范围为 [00:00:00, 24:00:00)，支持 7 位小数秒。ORDER BY、等值/范围谓词使用当天的时间顺序，不携带日期或时区；跨日区间、24:00:00 和远程协议中无法携带列类型的 typed TIME 恢复暂不属于合同。

ANSI 窗口函数 OVER

关系表查询支持 row_number()、count、sum、avg、min、max 的 ANSI OVER 规格：

SELECT id, tenant_id,
       row_number() OVER (PARTITION BY tenant_id ORDER BY created_at, id) AS row_no,
       count(*) OVER (PARTITION BY tenant_id) AS tenant_count,
       sum(value) OVER (PARTITION BY tenant_id ORDER BY created_at) AS running_total
FROM devices ORDER BY id;

PARTITION BY 可包含多个关系行标量表达式；省略时全结果集是一个分区。窗口按 WHERE 过滤后的行计算，最终 ORDER BY、LIMIT 和 OFFSET 在窗口求值之后生效。row_number() 必须指定窗口内 ORDER BY；多个排序项和升降序均可使用。升序排序时 NULL 在前，降序时在后。排序键完全相同的行按本次关系输入顺序编号，该顺序不是跨执行保证；需要稳定编号时把唯一键加入窗口排序。

无窗口 ORDER BY 的聚合为每行返回完整分区的聚合结果。指定排序时使用默认 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 语义，相同排序键的所有同行共享同一个累计结果。COUNT(*) 计入 NULL 行，COUNT(expr) 忽略 NULL；其他聚合沿用关系聚合的 NULL、Int64 与 DECIMAL 语义。空输入返回零行。当前仅支持顶层窗口投影，不支持窗口函数嵌套表达式、与普通聚合/GROUP BY/HAVING 同层混用或显式 ROWS/RANGE frame。执行会物化过滤后的关系行并按窗口分区排序；有序累计聚合逐 peer 前缀复算，最坏为 O(n²)，不适用于大分区低延迟工作负载。

时序 measurement 查询也支持显式 ANSI 窗口规格，并复用每个 series 的时间升序行流：

SELECT time,
       row_number() OVER (ORDER BY time ASC) AS row_no,
       running_sum(value) OVER () AS running_total
FROM readings
ORDER BY time;

measurement 合同包括空 OVER ()、OVER (ORDER BY time ASC) 以及 row_number()（必须带 OVER）。已有 difference、running_sum、moving_average 等时序窗口函数也可以使用显式规格；省略 OVER 的旧语法保持兼容。每个 measurement series 天然构成一个分区，因此 row_number() 会在每个 series 从 1 重新编号。

measurement 路径仍拒绝 PARTITION BY、非 time 或降序排序，以及 ROWS/RANGE frame 子句。关系表与 measurement 窗口各自独立；文档集合和向量搜索路径不支持窗口投影。上述不支持形状返回错误，不会静默退化为另一种窗口语义。

关系表单条 UPDATE 还遵循以下规则：

  • 同一个 SET 中的所有右值都读取更新前的原行，因此 SET a = b, b = a 会正确交换两列。
  • 表达式先对所有命中行求值并校验，再统一提交；任一行除零、溢出或类型不匹配时，整条语句不留下部分更新。
  • 非显式事务中的单条 UPDATE 把候选扫描、表达式求值和提交放在同一个表管理锁内，并发执行 SET value = value + 1 不会丢更新。
  • 通过 Tsdb.Functions 注册的用户标量函数属于任意应用回调，UPDATE 会在表管理锁外执行它，避免回调等待其他 SQL 线程时死锁；该分支需要并发冲突检测时应使用 ROWVERSION。
  • ROWVERSION 由数据库自动维护，不能出现在 SET 左侧；需要乐观并发控制时在 WHERE 中携带旧版本值。

关系表锁定读边界与乐观并发

关系表不提供用户可见的悲观行锁：SELECT ... FOR UPDATE、FOR UPDATE NOWAIT、FOR UPDATE SKIP LOCKED 和 FOR SHARE 均在解析阶段返回 sql_locking_read_unsupported，不会退化为普通 SELECT。单独尾随的 NOWAIT、SKIP LOCKED 也拒绝。不存在可供业务依赖的行锁粒度、持有生命周期、锁等待/超时、死锁检测或提交/回滚释放行为；不存在的目标行同样不能锁定。取消已请求的命令仍遵循普通 ADO.NET 取消合同，锁定读本身不会进入等待状态。

需要“读取后更新”时，使用 INT ROWVERSION 列和条件写入：

CREATE TABLE jobs (id INT, status STRING, version INT ROWVERSION, PRIMARY KEY (id));
SELECT status, version FROM jobs WHERE id = @id;
UPDATE jobs SET status = 'running' WHERE id = @id AND version = @version;

调用方先读版本，再把同一版本放入 UPDATE 或 DELETE 的谓词；版本过期时可能返回 table_concurrency_conflict，目标行消失时可返回 0 受影响行。此时应重读并重试整个业务事务，而不是把先前 SELECT 当作锁定快照。成功 UPDATE 自动递增版本，INSERT 初值为 1。BeginTransaction 的 ReadCommitted/Serializable 轻事务不提供悲观锁；队列写入在提交时仍校验最初读取的行状态，冲突可能在写入或 COMMIT 暴露。ADO.NET 同步和异步锁定读在嵌入式及远程模式均抛 SqlParseException，其 Code 为 sql_locking_read_unsupported；直接 REST SQL 返回同一错误码。锁定读被拒绝后事务仍可由调用方正常 ROLLBACK 或 COMMIT。

Modbus 32 位寄存器解码

以下标量函数把两只已经由 Modbus 协议层解析为 0..65535 的 16 位寄存器，还原成一个 32 位值：

函数 返回类型 说明
modbus_int32(first_register, second_register, byte_order) Int64 按有符号 32 位二补码解码
modbus_uint32(first_register, second_register, byte_order) Int64 按无符号 32 位解码；完整覆盖 0..4294967295
modbus_float32(first_register, second_register, byte_order) Float64 按 IEEE-754 binary32 解码，再提升为 SQL Float64

byte_order 不区分大小写，表示设备送来的四字节源布局：

源布局 第一个寄存器 第二个寄存器 还原动作
ABCD AB CD 保持原序
BADC BA DC 各 16 位字内交换字节
CDAB CD AB 交换两个 16 位字
DCBA DC BA 反转全部四字节

例如以下四个表达式都把源寄存器还原为 0x12345678，返回十进制 305419896：

SELECT modbus_uint32(4660, 22136, 'ABCD');
SELECT modbus_uint32(13330, 30806, 'BADC');
SELECT modbus_uint32(22136, 4660, 'CDAB');
SELECT modbus_uint32(30806, 13330, 'DCBA');

有符号与浮点示例：

SELECT modbus_int32(65535, 65534, 'ABCD'); -- -2
SELECT modbus_float32(16256, 0, 'ABCD');   -- 1.0

任一参数为 NULL 时结果为 NULL；寄存器越界、寄存器含小数、顺序名未知或参数类型错误时会抛出执行错误。

Modbus TCP 内建映射、master/slave 与写治理

当前版本已经支持 CREATE MODBUS SOURCE、CREATE MODBUS ENDPOINT、列级 FROM MODBUS / EXPOSE AS MODBUS、表级 USING MODBUS，以及 SHOW/DESCRIBE MODBUS 本地元数据查询。DDL 会把连接定义和规范化地址持久化到独立的版本化 catalog，并在建表时校验四类地址空间、跨度、访问权限、字节序、缩放及 wire type。

Server 的 Modbus runtime 默认关闭；只有 SonnetDBServer:Modbus:Enabled=true 与对象自身 ENABLED TRUE 同时满足时，对应 worker 才会运行。TCP master 会批量轮询四类地址并把采样写入本地 LATEST/HISTORY 表；source timeout、取消、按 RETRY 指数退避、断线重连和指标均在后台执行。INT QUALITY 列公开 GOOD/STALE/BAD/PARTIAL/NO_VALUE 位，最终失败按 KEEP_LAST/NULL/SKIP/MARK_BAD 落表并更新稳定诊断。TCP slave 会按 endpoint 固定 ROW KEY 响应 0x01～0x04 读取，并接收 0x05/0x06/0x0F/0x10 写请求；REJECT 直接拒绝，默认 STAGED 只有 durable 入队后才返回协议成功，表值须经 Write + Admin 审批后按 STAGE_ONLY/UPDATE_TABLE 处理。普通 SELECT 始终只读取本地状态，不同步访问 PLC。Source 受限写提供 preview/confirm，SHOW MODBUS WRITE AUDIT 统一查询两条写路径的脱敏事件。创建 source、endpoint 或映射表需要当前数据库 Admin，SHOW/DESCRIBE MODBUS 与 Modbus 概览只需 Read；Endpoint 队列和审计需要 Admin。

完整语法、安全边界、地址归一化、类型表和示例见 [Modbus TCP 内建映射表合同]({{ site.docs_baseurl | default: '/help' }}/modbus-tcp/)。

CREATE TABLE

定义关系表 schema。关系表 MVP 使用 KV-backed rowstore 存放在数据库目录的 tables/ 下，不修改时序 .SDBWAL / .SDBSEG 格式。

CREATE TABLE devices (
    id INT AUTO_INCREMENT,
    site_id INT NULL,
    name STRING NOT NULL,
    enabled BOOL NOT NULL DEFAULT TRUE,
    retry_count INT DEFAULT 0,
    version INT ROWVERSION,
    installed_at DATETIME NULL,
    metadata JSON NULL,
    payload BLOB NULL,
    PRIMARY KEY (id),
    FOREIGN KEY (site_id) REFERENCES sites (id),
    CONSTRAINT ck_devices_name CHECK (name IN ('pump', 'fan', 'valve'))
)

规则：

  • 当前必须声明 PRIMARY KEY (...)；主键列会强制为 NOT NULL。
  • 支持类型：INT、FLOAT、DECIMAL(p,s) / NUMERIC(p,s)、BOOL、STRING、DATETIME、TIME、BLOB、JSON。DECIMAL/NUMERIC 的 precision 范围为 1..38，scale 范围为 0..precision；省略 (p,s) 时默认为 (38,28)。TIME 以 TimeOnly ticks 保存，不含日期/时区。
  • DATETIME 可写 Unix 毫秒整数或 ISO-8601 字符串，查询时返回 UTC DateTime。
  • BLOB 可写 base64 字符串；ADO.NET 参数可直接传 byte[]。
  • JSON 当前按 UTF-8 字符串存储；可用 json_value(json_col, '$.path') 做 path 投影和过滤。
  • VECTOR(dim) 与 GEOPOINT 只作为 measurement 的 FIELD SQL 类型提供，不支持关系表 CREATE TABLE、ALTER TABLE ADD COLUMN 或 ALTER COLUMN ... TYPE；这些声明会确定性拒绝且不创建半成品表/列。文档集合 JSON 数组和专用向量 API 不构成关系类型支持。ADO.NET GetSchema("DataTypes") 通过 SupportsRelationalColumn=false、SupportsMeasurementField=true 明示这两个类型的边界；该表是 SDK 本地能力元数据，不是远端版本协商。完整[类型矩阵与官方关系实体替代模型]({{ site.docs_baseurl | default: '/help' }}/relation-type-boundary/)给出标量经纬度加显式 JSON 载荷，以及关系主数据加 companion measurement 两种建模方式；ORM 不得静默改为 JSON 或时序字段。
  • 普通列可声明 DEFAULT <expr>；默认表达式支持字面量、常量算术和内置标量函数，不能引用列、参数、聚合或子查询。ROWVERSION 列不能声明默认值。
  • INSERT 省略带默认值的列，或在 VALUES 对应位置写 DEFAULT 时，会在每一行写入时求值并应用目标列的默认表达式；显式写入 NULL 不会改用默认值。目标列没有声明显式默认值时，DEFAULT 按 SQL 常规语义产生隐式 NULL：可空列成功，非空列由现有约束拒绝。
  • INSERT INTO table DEFAULT VALUES 会为每个非 ROWVERSION 列使用其默认值；没有显式默认值的列产生隐式 NULL。UPDATE table SET column = DEFAULT WHERE ... 会按每个命中行重新求值默认表达式，轻事务路径保持相同语义。
  • VALUES(DEFAULT)、DEFAULT VALUES 和 UPDATE SET ... = DEFAULT 只适用于关系表；measurement 与文档集合会明确拒绝。
  • 二级索引使用 CREATE INDEX 单独声明。
  • FOREIGN KEY (...) REFERENCES parent (...) 第一版只支持表级声明，引用列必须等于被引用表 PRIMARY KEY；外键列任一为 NULL 时跳过校验。
  • CHECK (expression) 支持命名或未命名表级约束；表达式可引用当前表列、字面量、基础运算、IN、IS NULL、CASE 和当前关系执行器支持的标量函数，不支持限定列名、参数、聚合或子查询。
  • CHECK 按 SQL 三值逻辑执行：只有明确 FALSE 拒绝写入，TRUE 和由 NULL 传播得到的 UNKNOWN 均通过。
  • ROWVERSION 只能声明在一个 INT 列上；INSERT 自动写入 1，UPDATE 自动递增，禁止通过 SET 显式赋值，可用 WHERE id = ... AND version = ... 获得乐观并发冲突检测。
  • 每张表最多可有一个 INT AUTO_INCREMENT 列；兼容拼写 AUTOINCREMENT 和 IDENTITY。该列隐式为 NOT NULL，不能同时声明 NULL、DEFAULT 或 ROWVERSION。
  • INSERT 省略自增列、写入 NULL 或 DEFAULT 时，从 1 开始分配单调递增值；显式整数仍可写入，且高于当前高水位时会推进后续分配。自增属性本身不创建唯一约束，需要唯一性时仍应把该列加入 PRIMARY KEY 或唯一索引。
  • 自增列由数据库维护，不允许通过 UPDATE SET 显式修改；底层 TableStore 批量 mutation 若写入更大的显式值，仍会推进高水位以保护后续分配。
  • 自增高水位持久化在表的 KV/WAL 中，并在并发写入前预留；约束失败、触发器失败或事务回滚可能留下间隙，已分配值不会复用。DELETE 不重置序列，TRUNCATE TABLE 切换 generation 后从 1 重新开始；超过 INT 的 Int64 上限会明确报错。
  • 普通 INSERT 在提交阶段的表管理锁内分配自增值；事务或触发器为了向 NEW 行提前暴露生成值而进行的预留会记录当时的 generation。若并发 TRUNCATE TABLE 已切换 generation，陈旧事务会在提交前明确失败，不会把重置前的预留值写入新 generation。
  • 关系表 INSERT 支持 RETURNING column [, ...] 和 RETURNING *；返回行使用完成默认值、ROWVERSION 与 AUTO_INCREMENT 生成后的最终值，多行结果保持 VALUES 的插入顺序。首版只允许列名或 *，不支持表达式和别名；measurement 与文档集合会明确拒绝 RETURNING。
  • ADO.NET 可用 ExecuteScalar("INSERT ... RETURNING id") 取得本条语句生成的首个 ID，作为语句级 last-insert-id；ExecuteReader 可读取完整返回行，同时 RecordsAffected 保留实际插入行数。SonnetDB 不维护连接级 LAST_INSERT_ID() 状态。
  • 完整的 INSERT ... RETURNING 类型、列属性和跨嵌入式/REST/Frame HTTP/2 ADO 回落合同属于下一未发布版本，首次交付版本号待发布标签确定；已发布的 3.1.0 不应据此视为具备该完整合同。Frame SQL 查询端点仍只读，Protocol=frame-http2 的 ADO 写入经同一 HTTP/2 连接回落 REST SQL 端点。
  • EF Core provider 会把常规 int / long ValueGenerated.OnAdd 列建为 INT AUTO_INCREMENT，INSERT 不发送临时跟踪键，并通过 RETURNING 把数据库生成值回填实体；ValueGeneratedNever() 仍按显式客户端键处理。

关系查询可在投影和谓词中使用以下日期标量函数：

SELECT DATE_ONLY(installed_at),
       DATE_PART('year', installed_at),
       DATE_ADD(installed_at, 7, 'day'),
       DATE_DIFF('day', installed_at, CURRENT_UTC_DATETIME()),
       DATE_FORMAT(installed_at, 'yyyy-MM-dd HH:mm:ss'),
       TO_UNIX_MILLISECONDS(installed_at)
FROM devices
WHERE installed_at <= CURRENT_UTC_DATETIME();
  • CURRENT_DATETIME() / CURRENT_UTC_DATETIME() 返回服务器本地时间 / UTC 时间；CURRENT_DATETIME_OFFSET() / CURRENT_UTC_DATETIME_OFFSET() 返回对应的 DateTimeOffset。
  • DATE_ONLY(value) 返回日期零点。
  • DATE_PART(part, value) 支持 year、quarter、month、day、day_of_year、day_of_week、hour、minute、second、millisecond、microsecond、nanosecond；day_of_week 与 .NET 一致，星期日为 0。
  • DATE_ADD(value, amount, part) 支持 year、month、day、hour、minute、second、millisecond、microsecond、tick；year、month、tick 要求整数增量。
  • DATE_DIFF(part, start, end)（兼容 DATE_DIFF(start, end, part)）返回两个时间之间按指定分量截断的有符号整数；支持 year、quarter、month、week、day、hour、minute、second、millisecond、microsecond、tick。DATEDIFF 为同义别名。
  • DATE_FORMAT(value, format) / FORMAT_DATETIME(value, format) / TO_CHAR(value, format) 使用 invariant .NET 日期格式；STRFTIME(format, value) 额外支持 %Y、%m、%d、%H、%i、%s、%f 等常用标记。格式字符串最多 128 个字符，未知 % 标记会拒绝。
  • TO_UNIX_MILLISECONDS(value) / TO_UNIX_SECONDS(value) 返回 Unix 时间。
  • 日期函数接受 DATETIME 或 Unix 毫秒；输入为 NULL 时结果为 NULL。时序分桶仍使用 GROUP BY time(1m) 等语法，不使用 DATE_TRUNC。

ALTER TABLE

关系表支持新增、修改、删除和重命名列，以及重命名表：

ALTER TABLE devices ADD COLUMN region STRING NOT NULL DEFAULT 'north';
ALTER TABLE devices ALTER COLUMN retry_count TYPE FLOAT;
ALTER TABLE devices ALTER COLUMN region SET DATA TYPE STRING;
ALTER TABLE devices ALTER COLUMN region SET NOT NULL;
ALTER TABLE devices ALTER COLUMN region DROP NOT NULL;
ALTER TABLE devices ALTER COLUMN region SET DEFAULT 'east';
ALTER TABLE devices ALTER COLUMN region DROP DEFAULT;
ALTER TABLE devices RENAME COLUMN region TO site;
ALTER TABLE devices DROP COLUMN site;
ALTER TABLE devices RENAME TO managed_devices;

ALTER COLUMN 的完整语法为：

ALTER TABLE table_name ALTER [COLUMN] column_name
    [TYPE data_type | SET DATA TYPE data_type | data_type]
    [NULL | NOT NULL | SET NOT NULL | DROP NOT NULL]
    [SET DEFAULT expression | DROP DEFAULT];
  • 每条语句至少要指定一种变更；类型、空值约束和默认值动作各自最多出现一次，可以组合在同一条语句中。
  • TYPE FLOAT 与 SET DATA TYPE FLOAT 是两种等价的类型变更写法；省略 TYPE 的 FLOAT 形式用于 SQL Server 风格组合，例如 ALTER TABLE devices ALTER COLUMN retry_count FLOAT NOT NULL。
  • 类型变更会转换全部存量非空值并重建派生索引。当前不支持 USING expression 自定义转换；任一值无法转换，或转换后违反唯一索引、CHECK、NOT NULL 等约束时，在进程内整条 DDL 恢复原 schema、数据和索引。行 payload 与 catalog 发布之间尚无迁移 journal，因此不将该恢复边界解释为进程终止或掉电原子性。
  • 真正改变物理类型的迁移当前使用单个 KV 原子 batch，受 KvOptions.MaxOverlayEntries 和 KvOptions.MaxWalBytes 限制（默认分别为 100,000 条 mutation 和 256 MiB WAL）。超出预算时会在写 WAL 前拒绝；应先提高预算，或通过新表分批迁移。SET/DROP DEFAULT、DROP NOT NULL 和列重命名只发布元数据，不受该 batch 行数限制。
  • SET NOT NULL 会分页扫描并验证全部存量行，但不会重写 row payload；DROP NOT NULL 与直接写 NULL 等价，NOT NULL 与 SET NOT NULL 等价。
  • SET DEFAULT 只影响之后省略该列的 INSERT，不会回填存量行；DROP DEFAULT 只移除后续插入的默认行为。目标类型或空值约束发生变化时，保留或新设的默认表达式也会按新定义重新校验。
  • PRIMARY KEY 列不能修改类型或改为可空；ROWVERSION 列不能修改类型、空值约束或默认值；参与外键的引用列或被引用列不能修改类型，必须先删除相应外键约束。
  • 被逻辑视图、物化视图、存储过程或触发器依赖的表仍受既有依赖保护，必须先移除依赖对象再修改 schema。
  • ADD COLUMN ... NOT NULL 当前即使目标表为空也要求 DEFAULT；有入站外键引用的父表不能 RENAME TABLE 或 DROP TABLE，必须先删除子表外键。

现有关系表可在事务外追加生成列，并在空表上重定义主键：

ALTER TABLE devices ADD COLUMN id INT AUTO_INCREMENT;
ALTER TABLE devices ADD COLUMN version INT ROWVERSION;
ALTER TABLE devices ADD CONSTRAINT pk_devices PRIMARY KEY (id);
-- 等价的无约束名写法：ALTER TABLE devices ALTER PRIMARY KEY (id);
  • ADD COLUMN ... AUTO_INCREMENT 对已有行按旧主键编码顺序分配从 1 开始的值；新插入行从现有最大值加一继续。序列高水位与回填行在同一 KV batch 持久化，正常关闭并重开后继续增长。每张表只能有一个 INT AUTO_INCREMENT；显式 NULL、DEFAULT、ROWVERSION 组合及非 INT 类型被拒绝。
  • ADD COLUMN ... ROWVERSION 把已有行初始化为 1，后续 UPDATE 自动加一；每张表只能有一个 INT ROWVERSION，且该列不可空、不可声明默认值或手动赋值。
  • CREATE TABLE 可暂不声明主键，形成仅供 schema 演进的无主键空表；该表可以查询和追加列，但补齐主键前任何行写入均以 table_schema_evolution_unsupported 拒绝，不分配身份序列。上述 CREATE TABLE devices (code STRING NOT NULL) 后追加 id、主键与 version 的路线可执行；补齐主键后才能开始写入。
  • ADD [CONSTRAINT pk_<table>] PRIMARY KEY (...) 和 ALTER PRIMARY KEY (...) 仅支持空表。复合主键可用；有任意存量行、入站外键或自定义约束名时返回 table_schema_evolution_unsupported。已有行需要在事务外创建带目标主键的新表、分批回填并验证唯一性及约束，再切换应用访问；当前没有原子切换协议。
  • 生成列追加复用行改写和索引重建路径，保留既有默认值、CHECK 和外键约束；DDL 不允许放在活动轻事务中。行改写受单个 KV batch 预算限制，并沿用上述进程终止/掉电非原子性边界。成功后新 schema 对新的 DESCRIBE TABLE 和 ADO.NET GetSchema("Columns") 读取可见；失败不更新 catalog。

已有表还可追加或删除外键和检查约束。追加约束前会扫描存量行；任一行违反约束时 DDL 失败且 catalog 保持原状。

ALTER TABLE devices
ADD CONSTRAINT fk_devices_site FOREIGN KEY (site_id) REFERENCES sites (id);

ALTER TABLE devices
ADD CONSTRAINT ck_devices_name CHECK (name IN ('pump', 'fan', 'valve'));

ALTER TABLE devices DROP CONSTRAINT fk_devices_site;
ALTER TABLE devices DROP CONSTRAINT ck_devices_name;

逻辑视图

逻辑视图保存一条 SELECT 定义，不保存查询结果。读取视图时，SonnetDB 将定义展开为派生表并复用现有查询执行器，因此视图可以基于关系表、measurement、document collection、其他视图以及当前已经支持的 JOIN、UNION 和子查询。

CREATE VIEW active_devices AS
SELECT id, name, site_id
FROM devices
WHERE enabled = TRUE;

CREATE VIEW IF NOT EXISTS north_devices AS
SELECT d.id, d.name
FROM active_devices d
JOIN sites s ON d.site_id = s.id
WHERE s.name = 'north';

SELECT name FROM north_devices ORDER BY name;

管理语法：

SHOW VIEWS;
DESCRIBE VIEW active_devices;
DROP VIEW active_devices;
DROP VIEW IF EXISTS active_devices;

SHOW VIEWS 返回 name、created_utc。DESCRIBE VIEW 返回单行 name、definition、逗号分隔的直接 dependencies 和 created_utc。information_schema.tables 以 table_type = 'VIEW' 列出视图，information_schema.views 提供 table_schema、table_name、view_definition、created_utc。

当前行为与限制：

  • 定义以独立的 views/views.sdbview 目录文件持久化，重启后重新解析；不修改表、measurement、document 或 Segment 的现有二进制格式。
  • 视图采用读取时展开和晚绑定数据语义，基础数据变更会立即反映到后续查询；视图本身不能作为 INSERT、UPDATE 或 DELETE 目标。
  • 持久化定义不能包含 ?、@name 或 :name 参数占位符；调用方参数只能用于查询视图的外层 SELECT。
  • 创建时会拒绝不存在的数据源、与基础对象重名的视图和自引用；运行时还会拒绝直接或间接循环，并限制最多 32 层展开。
  • 被视图引用的基础对象不能执行 DROP 或 schema ALTER；被其他视图引用的视图也不能删除。首版不支持 CASCADE、OR REPLACE 或跨数据库依赖，应先按依赖顺序显式删除视图。
  • EXPLAIN SELECT ... FROM view 将访问路径标记为 view_expansion；当前不会把展开后各基础扫描的估算值汇总到视图层。

物化视图

物化视图保存 SELECT 定义和最近一次成功刷新的物理结果。创建只登记定义，不隐式执行查询；首次读取前必须显式刷新：

CREATE MATERIALIZED VIEW active_device_cache AS
SELECT id, name, site_id
FROM active_devices;

REFRESH MATERIALIZED VIEW active_device_cache;
SELECT name FROM active_device_cache ORDER BY id;

基础数据后续变化不会自动进入已发布快照。再次执行 REFRESH MATERIALIZED VIEW 会全量计算一个新代际；新代际完整写入并落盘后才原子切换读指针。刷新期间读者继续读取旧代际，刷新失败也保留旧代际，并把状态改为 failed、记录错误。尚无成功代际时读取会明确提示先刷新。

管理语法：

CREATE MATERIALIZED VIEW IF NOT EXISTS active_device_cache AS SELECT * FROM active_devices;
SHOW MATERIALIZED VIEWS;
DESCRIBE MATERIALIZED VIEW active_device_cache;
DROP MATERIALIZED VIEW active_device_cache;
DROP MATERIALIZED VIEW IF EXISTS active_device_cache;

SHOW MATERIALIZED VIEWS 返回 name、status、definition_version、active_generation、row_count、最近成功刷新的 refreshed_utc 和 error。DESCRIBE MATERIALIZED VIEW 另返回 SELECT definition、直接 dependencies、created_utc、最近尝试结束时间 last_refresh_utc 与 last_successful_refresh_utc。状态为 uninitialized、refreshing、ready 或 failed。

information_schema.tables 以 table_type = 'MATERIALIZED VIEW' 列出物化视图；information_schema.materialized_views 提供相同定义和刷新元数据。EXPLAIN SELECT ... FROM materialized_view 使用 materialized_view_snapshot 访问路径并按活动代际行数估算扫描。

当前行为与限制：

  • 定义目录和物理结果位于独立的 materialized-views/；目录和快照均有版本与 CRC，不修改已有表、measurement、document 或 Segment 格式。
  • 物化快照是只读关系源，可参与外层过滤、投影、聚合、子查询和关系 JOIN；定义本身仍复用现有表、measurement、document、逻辑视图和当前 SELECT 执行路径。
  • 定义不能包含参数占位符、未知数据源或自引用；物化视图与基础对象、逻辑视图共用名称空间和依赖删除/ALTER 保护。
  • REFRESH 需要数据库写权限，不能在活动轻事务内执行，同一物化视图不允许并发刷新；SHOW、DESCRIBE 和读取只需要读权限并继续经过现有 Frame、MCP、Copilot 与审计入口。
  • 首版只支持显式全量刷新。不支持增量刷新、定时调度、后台自动刷新、OR REPLACE、CASCADE 或跨数据库依赖。

SQL 存储过程

存储过程首版只支持 LANGUAGE SQL、有序 IN 参数和静态 SQL body。参数类型为 INT、FLOAT、BOOL、STRING，body 通过 @参数名 引用参数；绑定发生在 AST 上，不执行文本替换。

CREATE PROCEDURE add_device (
    IN p_id INT,
    IN p_name STRING,
    IN p_enabled BOOL
)
LANGUAGE SQL AS BEGIN
    INSERT INTO devices (id, name, enabled)
    VALUES (@p_id, @p_name, @p_enabled);

    SELECT id, name, enabled
    FROM devices
    WHERE id = @p_id;
END;

CALL add_device(1, 'pump-01', TRUE);

管理语法：

SHOW PROCEDURES;
DESCRIBE PROCEDURE add_device;
DROP PROCEDURE add_device;
DROP PROCEDURE IF EXISTS add_device;

SHOW PROCEDURES 返回 name、parameters、language、传递计算后的 requires_write 和 created_utc。DESCRIBE PROCEDURE 返回 name、parameters、language、body、object_dependencies、procedure_dependencies、requires_write、created_utc。

执行合同：

  • body 允许 SELECT、INSERT、UPDATE、DELETE 和 CALL；写目标必须是关系表，首版不允许过程写 measurement 或 document collection。SELECT 继续复用当前支持的数据源与查询执行器。
  • 定义时解析全部语句并校验命名参数、数据对象和已存在的被调用过程。过程不能重载，不支持默认参数、OUT/INOUT、动态 SQL、DDL 或外部语言运行时。
  • 多语句过程只向调用方返回 body 最后一条语句的结果；中间 SELECT 参与结果行数治理，但不形成多个远程结果集。
  • 直接或传递包含写入的过程自动使用轻事务；失败时整次调用回滚。位于调用方已有事务中时使用保存点，只撤销该次失败调用新增的 mutation。
  • 默认单次调用链最多执行 64 条 body 语句、嵌套 8 层、累计产生 10,000 行结果（包含 INSERT RETURNING）；嵌套 CALL 的最终结果只计一次。SELECT 按剩余行预算加一行探测下推分页，超限失败，不静默截断；阻塞算子的中间数据仍受独立 SQL 内存预算约束。拒绝直接或间接递归，在语句边界、关系行扫描和提交前检查取消。
  • 写权限按完整调用图传递计算。只读凭据可调用只读过程，不能通过外层只读过程调用内层写过程提升权限；Frame SQL query 通道固定为只读，因此也只能调用只读过程。
  • CREATE/DROP PROCEDURE 不能在活动轻事务中执行。基础对象 DROP/ALTER 和被调用过程 DROP 会在仍有依赖时返回 routine_dependency。

过程与触发器定义共同保存在数据库目录的 routines/routines.sdbrtn。该目录使用独立版本、little-endian 编码、CRC32、大小/数量上限和临时文件原子替换；备份恢复自动包含该目录，打开时拒绝损坏或未知版本。当前 v4 在启用状态、执行顺序、时机、粒度和 transition table 别名之外持久化约束/延迟标志，兼容读取 v1/v2/v3（旧定义默认为立即触发器）。修改目录后写入 v4，旧引擎会拒绝打开，不可直接降级；未知标志或不一致组合也会被拒绝。主数据文件、KV/WAL 及关系事务 journal 格式不变。

SQL 触发器

关系表触发器支持 AFTER INSERT/UPDATE/DELETE FOR EACH ROW、AFTER INSERT/UPDATE/DELETE FOR EACH STATEMENT、受限 BEFORE INSERT/UPDATE FOR EACH ROW，以及下述提交阶段执行的延迟约束 AFTER ROW 触发器。每个定义只绑定一个事件；AFTER 中的 OLD / NEW 是只读行上下文，BEFORE 仅能通过下述受限赋值修改 NEW。

CREATE TRIGGER audit_device_insert
AFTER INSERT ON devices
FOR EACH ROW
WHEN (NEW.enabled = TRUE)
LANGUAGE SQL AS BEGIN
    INSERT INTO device_audit (event_id, device_id, action, old_name, new_name)
    VALUES (NEW.id * 10 + 1, NEW.id, 'insert', NULL, NEW.name);
END;

CREATE TRIGGER audit_device_update
AFTER UPDATE ON devices
FOR EACH ROW
WHEN (OLD.name != NEW.name)
LANGUAGE SQL AS BEGIN
    INSERT INTO device_audit (event_id, device_id, action, old_name, new_name)
    VALUES (NEW.id * 10 + 2, NEW.id, 'update', OLD.name, NEW.name);
END;

CREATE TRIGGER audit_device_delete
AFTER DELETE ON devices
FOR EACH ROW
LANGUAGE SQL AS BEGIN
    INSERT INTO device_audit (event_id, device_id, action, old_name, new_name)
    VALUES (OLD.id * 10 + 3, OLD.id, 'delete', OLD.name, NULL);
END;

管理语法：

SHOW TRIGGERS;
SHOW TRIGGERS ON devices;
DESCRIBE TRIGGER audit_device_insert;
ALTER TRIGGER audit_device_insert DISABLE;
ALTER TRIGGER audit_device_insert ENABLE;
ALTER TRIGGER audit_device_insert RENAME TO audit_insert;
EXPLAIN TRIGGER audit_insert;
EXPLAIN PROCEDURE add_device;
SHOW ROUTINE AUDIT FOR TRIGGER audit_insert;
SHOW ROUTINE STATS FOR PROCEDURE add_device;
DROP TRIGGER audit_insert;
DROP TRIGGER IF EXISTS audit_insert;

SHOW TRIGGERS 返回 name、table_name、event、when、created_utc；ON table 只保留指定关系表。DESCRIBE TRIGGER 返回 name、table_name、event、when、language、body、dependencies、created_utc。两者在末尾追加 enabled、execution_order。

当前语义与限制：

  • INSERT 事件只能引用 NEW，DELETE 事件只能引用 OLD，UPDATE 可同时引用两者。WHEN 中的列必须显式写为 OLD.column / NEW.column，不允许参数或子查询。
  • AFTER body 只允许以关系表为目标的 INSERT、UPDATE、DELETE，可在 DML 内嵌 SELECT，不允许独立 SELECT、CALL、DDL 或 measurement/document 写入；BEFORE body 使用下述 SET NEW 合同。
  • 执行顺序先按原 DML 行顺序，再按组内持久化执行顺序；新触发器默认追加。FOR EACH ROW FOLLOWS name / PRECEDES name 位于可选 WHEN 之前；也可用 ALTER TRIGGER name FOLLOWS other / PRECEDES other 原子移动到参照之前或之后。参照必须是另一个同表、同事件、同时机、同粒度、同执行阶段（立即或延迟）的已有触发器，禁用定义仍保留位置；重命名和启停保留创建时间与顺序。这是位置调整，不是随参照后续变化而自动移动的依赖边；没有额外的命名 order group 语法。触发器链共享语句数和深度预算，拒绝递归。
  • 原 DML 与全部触发动作使用同一轻事务提交边界。语句执行中的 WHEN 或 body 失败会撤销本条 DML 及其动作；调用方已有事务时使用内部保存点。COMMIT 阶段的延迟动作或最终约束失败则结束并回滚整笔事务。
  • 目标表或 body 依赖的关系表仍被触发器引用时，DROP/ALTER 会被阻断，包括已禁用的触发器。CREATE/DROP/ALTER TRIGGER 需要写权限，不能在活动轻事务中执行。每条 DML 固定使用进入时的定义快照，后续生命周期修改不会改变该条语句中途的触发集合。
  • 不支持 BEFORE DELETE、BEFORE STATEMENT、INSTEAD OF、多事件合并、Document 或 measurement 触发器；延迟约束触发器仅支持下述固定语法，不提供通用 deferred、INITIALLY IMMEDIATE 或 SET CONSTRAINTS。

语句级触发器与 Transition Tables（#335）

CREATE TABLE batch_totals (id INT, amount INT, PRIMARY KEY (id));
INSERT INTO batch_totals (id, amount) VALUES (1, 0);
CREATE TRIGGER sum_inserted AFTER INSERT ON orders
REFERENCING NEW TABLE AS incoming
FOR EACH STATEMENT LANGUAGE SQL AS BEGIN
    UPDATE batch_totals
    SET amount = amount + (SELECT COALESCE(SUM(amount), 0) FROM incoming)
    WHERE id = 1;
END;
  • 分发顺序为：对全部候选行执行 BEFORE 与缓冲准备、所有 AFTER ROW 动作、所有 AFTER STATEMENT 动作、提交时检查最终约束。同组遵循持久化顺序。后续链上 DML 是独立触发语句。
  • AFTER STATEMENT 对空影响集也执行一次；无匹配行的 UPDATE/DELETE 和空 INSERT SELECT 均属于空影响集。失败的源语句不继续分发 AFTER。
  • UPDATE 可声明 REFERENCING OLD TABLE AS previous NEW TABLE AS incoming；INSERT 只有 NEW TABLE，DELETE 只有 OLD TABLE。REFERENCING 位于 ON table 与 FOR EACH 之间，可省略；别名不能重复或与创建时已有数据对象重名，不能是 OLD、NEW 或目标表名。
  • 快照只含本条语句实际影响的行，保留该语句改写前/最终改写后的配对数据，包含 BEFORE 的修改及生成后的 ROWVERSION。它不包含同事务其他语句或后续触发器链的修改。没有隐式行序保证；有顺序需求时使用 ORDER BY。
  • 别名仅在所属触发器 body 内可见，嵌套触发器隔离并在返回后恢复调用方快照；不能作为 INSERT/UPDATE/DELETE 目标。可用于 SELECT 聚合、JOIN、子查询，以及 INSERT INTO table (columns) SELECT ...。UPDATE 赋值支持非相关的单列标量子查询，返回多行时报错；INSERT 的查询取值使用 INSERT SELECT，不支持 VALUES (子查询)。语句级触发器不提供 OLD/NEW 行变量或行级 WHEN，过滤放在查询内。
  • transition set 复用事务持有的不可变行图像，不额外复制整个 OLD/NEW 集合。默认调用链同时存活的集合最多 100,000 行（UPDATE 一对计一行）、64 MiB 保守内存估算，包含嵌套集合；超过任一上限返回 trigger_transition_limit 并撤销本条语句。此版本采用硬上限拒绝，不将 transition set 溢写到磁盘。
  • 关系表 INSERT SELECT 的源查询在普通表扫描、关系 JOIN/聚合/子查询、UNION 结果收集及外排归并输出时，按 MaxTriggerTransitionRows / MaxTriggerTransitionBytes 检查保留行数与估算字节；阻塞算子内存上限同时收紧到该字节预算。超限在目标写入前以 trigger_transition_limit 拒绝，整条 INSERT 只触发一次。多阶段保留会重复计数，因而可能保守拒绝；非关系源执行器和 UDF 内部分配不受此估算预算约束。它不扩展到 measurement 或 Document 写入。

受控 BEFORE（#336）

CREATE TRIGGER normalize_order BEFORE INSERT ON orders
FOR EACH ROW LANGUAGE SQL AS BEGIN
    SET NEW.customer = LOWER(COALESCE(NEW.customer, 'unknown'));
    SET NEW.amount = CASE WHEN NEW.amount < 0 THEN 0 ELSE NEW.amount END;
END;

典型用途是把订单标识/金额归一化为满足 NOT NULL、唯一性和 CHECK 的最终值，而不是在 AFTER 中再次写同一行。

  1. INSERT 输入先按目标列类型转换并计算 DEFAULT，然后预留 AUTO_INCREMENT；UPDATE 的原 SET 右值全部读取原行，先生成候选 NEW。
  2. 依次执行 BEFORE 的 WHEN 和 SET NEW.column = expression。同一 body 的后续赋值、后续触发器都读取前面修改后的 NEW；OLD 始终为原行。WHEN 为 false/NULL 时跳过该触发器。
  3. 生成 ROWVERSION 并检查最终 NOT NULL，然后缓冲最终行；INSERT 的版本为 1，UPDATE 在原版本上加一。生成后的 NEW 对 AFTER 可见。BEFORE 不允许读取尚未生成的 NEW.ROWVERSION，可以读取 OLD.ROWVERSION。
  4. 提交时仍检查主键、唯一索引、外键、CHECK 和乐观并发原行状态；NEW 主键或外键允许改写，但不能绕过这些检查。失败撤销源行及全部触发器动作；已有事务退回本语句保存点。自增预留沿用现有合同，失败可产生序列空洞。

BEFORE body 仅接受逐条 SET NEW.column = expression。表达式限字面量、OLD/NEW 列、算术/比较/逻辑、CASE、IS NULL、常量 IN 列表和 LOWER/UPPER/COALESCE；拒绝子查询、CALL、任意 DML、UDF/外部函数和 OLD 赋值。AUTO_INCREMENT 与 ROWVERSION 都是引擎生成列，不允许改写；任意计算生成列表达式仍未支持。预算、取消、递归保护和审计沿用例程调用链合同。

SqlExecutionOptions.MaxTriggerTransitionRows / MaxTriggerTransitionBytes 可设置嵌入式预算；服务器对应 SonnetDBServer:SqlExecution 下同名属性，分别限制为 1..100000 与 1..134217728，默认 100000/67108864，REST/Frame 使用相同服务端值。SHOW/DESCRIBE TRIGGER 在既有列后追加 timing、level、old_table、new_table。

执行前也检查 transition 别名冲突；若创建触发器后又创建同名数据对象，触发器返回 routine_dependency 并回滚源语句，避免优化器把快照误当成持久表。

延迟约束触发器（#338）

下面的一次余额转移由两条 UPDATE 组成。提交时两次检查均读取最终总额 100；若改为普通立即 AFTER 触发器，同一检查体会保存第一条 UPDATE 后的中间总额 90，使最终 CHECK 失败。示例检查表的主键仅用于这一次转移；实际业务应为每个检查事件设计独立键或更新同一检查记录。

CREATE TABLE accounts (id INT, balance INT, PRIMARY KEY (id));
INSERT INTO accounts (id, balance) VALUES (1, 40), (2, 60);
CREATE TABLE balance_checks (
    id INT, total INT, PRIMARY KEY (id), CHECK (total = 100));
CREATE CONSTRAINT TRIGGER balanced AFTER UPDATE ON accounts
DEFERRABLE INITIALLY DEFERRED FOR EACH ROW
LANGUAGE SQL AS BEGIN
    INSERT INTO balance_checks (id, total)
    SELECT NEW.id, total
    FROM (SELECT SUM(balance) AS total FROM accounts) AS totals;
END;
BEGIN;
UPDATE accounts SET balance = 30 WHERE id = 1;
UPDATE accounts SET balance = 70 WHERE id = 2;
COMMIT;

约束触发器必须同时声明 DEFERRABLE INITIALLY DEFERRED，仅支持 AFTER INSERT/UPDATE/DELETE ... FOR EACH ROW；FOR EACH ROW 后可接同阶段 FOLLOWS/PRECEDES 和 WHEN。普通触发器不能声明 DEFERRABLE；拒绝部分声明、INITIALLY IMMEDIATE、SET CONSTRAINTS、BEFORE/STATEMENT 约束触发器及其 transition tables，也没有 AFTER COMMIT SQL 语法。

  • 源 DML 按语句顺序、实际行顺序和组内持久化触发器顺序入队；空影响集不产生行事件。COMMIT 在持久化前按 FIFO 执行，body 新产生的事件追加到队尾。同一行被多次更新或最终删除也不会合并此前事件。
  • 每个事件捕获自己的 OLD/NEW，包括 BEFORE 改写及对应 ROWVERSION。WHEN 在 COMMIT 求值，但读取捕获图像；body 查询读取当时已提交基线、本事务最终缓冲和前面延迟动作的变更。已有 FK 级联先展开到缓冲，之后新增删除会再展开；自动级联动作仍不派发合成 SQL 行事件。延迟 body 随后重插父行不会恢复已执行 CASCADE/SET NULL 的子行变化。
  • 无显式事务的 DML 自动入队并提交。已有事务中的源语句或过程失败，其内部保存点同时撤销新增 mutation、事件和内存计费，保留更早成功语句；未增加 SQL SAVEPOINT 语法。COMMIT 期间的 body、预算、取消、依赖或约束失败回滚整笔事务；ROLLBACK 和被放弃的脚本清空队列。
  • 已入队事件保留定义、原 caller 和调用祖先链。随后启停、重命名、排序或删除定义只影响后续 DML；依赖表 schema 被替换、删除或重建时，提交以 routine_dependency 拒绝过期依赖。
  • body 只允许基础关系表 INSERT/UPDATE/DELETE 及受支持的纯 SQL 查询和内置函数；提交中间接执行的普通触发器也接受此校验。禁止 UDF、被用户覆盖的内置函数、应用表值回调、CALL、DDL、独立 SELECT、视图、文件/JSON_FILE/GRAPH_TABLE 及其他数据模型入口。
SqlExecutionOptions 选项 默认值 范围与用途
MaxDeferredTriggerInvocations 100000 1..100000；同事务保留事件数量上限
MaxDeferredTriggerBytes 67108864 正值；保守计费 OLD/NEW、定义和祖先链，Server 上限 134217728
TransactionCommitTimeoutMilliseconds 30000 1..120000；提交锁等待和协作执行期限

Server 使用 SonnetDBServer:SqlExecution 下同名设置，REST/Frame 一致生效。队列使用各源请求的最严格上限，COMMIT 可进一步收紧；延迟执行保留原调用链的语句、深度和结果行预算，不能用新的 COMMIT 重置计费或提高源请求上限。原请求与提交请求的取消均生效。队列或每次级联展开的 100000 行上限触发 trigger_deferred_limit，锁等待或执行期限超限触发 routine_commit_timeout。

最终状态检查与写入在同一 TableManager 提交锁内串行，仍检查 table_concurrency_conflict；这覆盖同实例、同库的 SQL/TableManager 提交路径，直接 TableStore 调用仍绕过 SQL 触发器。锁等待最多 2401 次、每次至多 50 ms，并有独立 120 秒上限；当前没有行锁死锁图。超时与取消为协作检查，不能强制终止同步 I/O。队列字节预算也不是整笔事务的峰值内存上限，既有级联候选扫描仍可能先物化数据，聚合/JOIN/排序使用独立 SQL 内存治理。

SHOW/DESCRIBE TRIGGER 在既有列后追加 is_constraint、initially_deferred。入队不代表 body 已执行成功；最终提交成功后才报告 committed。持久化或补偿结果未知时沿用下述 routine_commit_unknown 合同，直接嵌入式 COMMIT 也可能抛出 TableTransactionRecoveryException，须关闭重开并按幂等键核对。

提交后的显式 outbox（#338）

外部投递由宿主在源 SQL 结束后显式调用 SonnetDB.Routines.SqlOutboxWorker。构造器创建或校验专用 sql_outbox 关系表；生产者只写稳定的 event_id、topic 和 payload，工作器管理其余状态列。以下为执行一次生产事务并处理一个批次的 C# 示例：db 是已打开的 Tsdb，deliverIdempotently 是宿主提供的 Func<SqlOutboxMessage, CancellationToken, ValueTask>，必须按 EventId 幂等并遵守取消令牌。

using SonnetDB.Routines;
using SonnetDB.Sql.Execution;

var worker = new SqlOutboxWorker(db, new SqlOutboxWorkerOptions
{
    MaxBatchSize = 32,
    BatchTimeout = TimeSpan.FromSeconds(30),
    MaxAttempts = 5,
});
SqlExecutor.ExecuteScript(db, """
    CREATE TABLE published_orders (id INT, PRIMARY KEY (id));
    BEGIN;
    INSERT INTO published_orders (id) VALUES (1);
    INSERT INTO sql_outbox (event_id, topic, payload)
    VALUES ('order-created-1', 'order.created', '{"orderId":1}');
    COMMIT;
    """);
SqlOutboxBatchResult result = await worker.ProcessBatchAsync(
    deliverIdempotently, cancellationToken);

outbox 插入也可放入源表 SQL 触发器，与源 DML 原子提交；外部投递和 ACK 在另一个提交边界。构造器及 ProcessBatchAsync 禁止从活动 SQL 事务、SQL 函数或例程回调中重入。工作器在领取已提交并同步 WAL 后、数据库锁之外执行 handler，不启动后台线程或自动轮询。

状态为 pending、leased、done、dead，时间字段为 Unix 毫秒。领取持久化次数和租约，默认租约一分钟；ACK 必须匹配未过期的租约 token。失败按有上限的指数退避重试，默认最多领取五次，达到次数或正文上限进入 dead。领取、ACK、重试状态返回前均显式同步 outbox WAL；源事件持久性仍遵循源事务配置。状态同步失败返回未知结果并禁止继续使用该表，必须重开恢复。

投递成功但 ACK 前崩溃，或租约过期后重新领取，都可能重复投递相同 EventId，因此合同是 at-least-once；消费者须自行去重，旧 handler 不能确认新租约。done/dead 行保留供审计，清理与人工重投由宿主负责。取消后保留未确认租约以待过期恢复；超时只停止等待，无法强制终止不协作的 handler。

每批默认最多 32 个候选，范围 1..1000；默认 30 秒协作期限，最高两分钟。数量限制约束选出的候选和处理尝试，不限制历史行扫描量，查询仍可能扫描保留的 outbox 表。SqlOutboxBatchResult 分别报告 Claimed、Delivered、Retried、DeadLettered、LeaseLost。完整边界和本次验证状态见 #338 验收记录。

例程诊断、预算与恢复

诊断中的 outcome 区分 pending、committed、rolled_back、failed、unknown 和无需事务提交的 completed。待提交记录的 succeeded=false 不计作失败；外层事务失败、显式回滚和请求放弃会结算全部下游动作。审计最多保留 256 条，STATS 的样本数和 P50/P95/P99 只统计这个保留窗口，并非自创建以来的完整历史。RoutineManager.Diagnostics.ExportAudit(Stream, ...) 可把脱敏快照保存为 JSON，既不是持久审计后台服务，也不是 exactly-once 增量订阅；导出 pending 后应在提交结束后重新导出其最终状态。EXPLAIN 只校验定义与对象依赖，不执行 body，也不证明最终业务约束一定成立；没有 EXPLAIN ANALYZE 例程写入。其 transaction_boundary 对延迟约束触发器返回 relational_commit_before_persistence，普通写例程返回 relational_transaction_or_caller_savepoint，只读过程返回 caller_read_committed。

嵌入式沿用 SqlExecutionOptions。服务器配置 SonnetDBServer:SqlExecution:MaxRoutineStatements（1..100000）、MaxRoutineDepth（1..32）、MaxRoutineResultRows（1..100000）对 REST/Frame 一致生效，默认仍为 64/8/10000。每行一个触发动作的 10000 行批量请求需要显式提高语句预算，例如 10064；预算包含嵌套触发器链，必须按实际工作负载留量。提交锁等待及延迟执行期间检查取消；持久化决定开始后不再强行中断同步日志操作。

嵌入式调用可通过 RoutineManager.Diagnostics 读取最近 256 条不含参数值/行内容的调用审计和累计指标。Server /metrics 按数据库公开 sonnetdb_procedure_* 与 sonnetdb_trigger_* 调用、失败和累计耗时指标。稳定错误码包括 procedure_not_found、trigger_not_found、routine_invalid_arguments、routine_recursive_call、trigger_recursion、routine_depth_limit、routine_statement_limit、routine_result_row_limit、trigger_transition_limit、trigger_deferred_limit、routine_commit_timeout、routine_cancelled、routine_forbidden、routine_dependency、trigger_context 和 routine_execution_failed；表约束失败保留其 TableConstraintException.ErrorCode。

M39 #333 的证据入口位于 tests/SonnetDB.Benchmarks：--m39-trigger-evidence 会固定审计 outbox、 派生汇总和状态流转保护 journey，并以 1/100/10,000 行 INSERT、UPDATE、DELETE 记录无触发器、V1 行触发器和客户端候选 statement 参考的吞吐、关系表 WAL/rowstore 有符号差值、托管内存、分配与失败回滚成本。成功样本会核对源表和审计表 的精确内容，失败样本会逐行确认源值与审计 sentinel 均已恢复；候选路径仍只是显式事务参考实现，不增加 SQL 语法。 各关系表仍使用独立 WAL，多表提交现在由 tables/transaction.sdbtxn 协调：先同步带 CRC 的原值日志， 再应用并同步各表 WAL，最后同步完成标记。启动先撤销没有完整完成标记的事务，恢复可重复执行。 日志上限 128 MiB、1024 张表、每表 100 万个行/索引键；超限在应用事务数据前拒绝，生成键预留仍允许留下间隙。 提交或补偿 I/O 结果不明时返回 routine_commit_unknown，停止当前表管理器及已有表句柄的读写， 必须重启恢复并按业务幂等键核对结果；不要直接重试有外部副作用的操作。完成后的日志不得手工删改。 真进程强杀测试证明的是进程崩溃恢复，不替代物理断电、存储控制器故障或分布式 exactly-once 验证。

CREATE INDEX / DROP INDEX

关系表支持普通二级索引和唯一索引。索引声明随 table schema 持久化，索引内容从 rowstore 派生，打开表或 schema 变更时可重建。

CREATE INDEX idx_devices_tenant ON devices (tenant);
CREATE UNIQUE INDEX ux_devices_serial ON devices (serial);
CREATE INDEX IF NOT EXISTS idx_devices_site ON devices (site, name);

DROP INDEX idx_devices_site ON devices;

当前行为：

  • 索引名在单表内唯一。
  • 索引列必须存在，可包含 1 个或多个列。
  • 唯一索引会在 INSERT / UPDATE / 轻事务提交时校验现有数据和同批数据冲突。
  • SELECT / UPDATE / DELETE 会使用联合索引从首列开始的最长连续等值前缀；索引未覆盖的条件继续对候选行执行完整 WHERE 过滤。
  • 连续等值前缀后的下一列为 INT 或 DATETIME 时，<、<=、>、>= 可继续作为索引范围。例如索引 (a, b, c, d) 可服务 a = ? AND b = ? AND c >= ? AND c < ?，d 和其它条件作为残余过滤。
  • 联合索引缺少首列等值或范围条件时不能从中间列开始使用；遇到第一个未绑定列后，后续列只作为残余条件。
  • 索引内容不作为第二份权威数据保存；rowstore 是主数据，索引可重建。

关系表 DML

INSERT INTO devices (id, name, enabled)
VALUES (1, 'pump-01', TRUE), (2, 'fan-02', FALSE);

INSERT INTO devices (name, enabled)
VALUES ('valve-03', TRUE), ('motor-04', FALSE)
RETURNING id, version;

SELECT id, name
FROM devices
WHERE enabled = TRUE AND id > 1
ORDER BY id DESC
LIMIT 10;

UPDATE devices
SET name = 'pump-01b', retry_count = retry_count + 1
WHERE id = 1;

DELETE FROM devices
WHERE id = 2;

DELETE FROM acquisitions
WHERE guid IN (
    SELECT guid
    FROM acquisitions
    WHERE upload_time < @cutoff
    ORDER BY capture_time
    LIMIT 500
);

当前行为：

  • INSERT 按主键插入；主键已存在时返回错误，不会静默覆盖。
  • INSERT ... RETURNING 在同一语句中返回成功插入后的列值；RETURNING * 按表 schema 顺序返回全部列。未知列会在写入前报错，不留下部分数据。
  • INSERT ... VALUES ... ON CONFLICT (主键或唯一索引列) DO UPDATE SET column = excluded.column [WHERE predicate] [RETURNING ...] 对冲突目标行执行原子更新。excluded 指当前候选行，未限定列名指冲突前目标行；可在 WHERE 同时引用两者。谓词为 FALSE 或 NULL 时不更新、不返回该行，且不计入受影响行数。多行语句按输入顺序返回成功插入或更新的行；同一语句重复更新同一目标行会整句拒绝。ROWVERSION 更新由引擎递增，候选行的默认值与自动生成列在冲突检查前确定。
  • 已发布 v3.1.0 不支持关系表 ON CONFLICT；当前未发布的 main 提供 DO NOTHING 及上述 DO UPDATE，首次正式发布版本号仍待决定。DO UPDATE 必须显式指定主键或唯一索引列，复合键按索引声明的列顺序匹配。当前 main 的远程 ADO 轻事务使用服务端会话执行 DO UPDATE ... RETURNING，返回行来自实际待提交事务，提交不重放 SQL；实际发布前不能作为已发布包能力。服务端会话绑定数据库和凭据，活动租约为 2 分钟，最多 128 个活动会话；终态保留 2 分钟，可通过 GET /v1/db/{db}/sql/transactions/{id} 查询，重复提交或回滚同一终态不会再次执行。旧服务端无会话端点时，客户端保留旧协议且在入队前拒绝此语句。
  • 会话存放于单个 Server 进程内；跨实例路由需固定到创建会话的实例。服务端重启会丢失未提交缓冲及终态缓存，未收到提交响应时须按会话 ID 查询终态，若服务端不可达或缓存已过期则结果未知。已发布包和 FreeSql Provider 仍需按实际版本与部署形态单独验收。
  • UPDATE 支持把列、字面量、算术和标量函数组合成右值表达式；当前不支持更新主键或显式更新 ROWVERSION 列。
  • 关系表联接更新支持 UPDATE target AS t JOIN source AS s ON ... SET column = s.column WHERE ... 和 UPDATE target AS t SET column = s.column FROM source AS s WHERE ...；WHERE 必填，来源限关系表及 INNER/LEFT JOIN。重复来源按声明扫描顺序取首个匹配，RETURNING 与影响数每个目标主键只计一次；来源保持只读，触发器及约束按普通 UPDATE 执行。轻事务中目标表已有缓冲写时，后续联接更新会明确拒绝。
  • SELECT 支持 *、列投影、字面量投影、标量表达式投影，以及 WHERE 中的 AND / OR / NOT 和基础比较。
  • 关系表 JSON 列支持 json_value(metadata, '$.site') 这类 path 表达式；对象或数组结果会以紧凑 JSON 字符串返回。
  • WHERE 覆盖完整主键等值条件时会走主键读取；二级索引按最长连续左前缀选择，并可在首个未绑定的 INT / DATETIME 列继续做范围扫描；其它条件在候选行上过滤。
  • ORDER BY 支持结果集中的任意列名；LIMIT / OFFSET / FETCH 语法与 measurement 查询一致。
  • 关系表 UPDATE / DELETE 的 WHERE 支持非相关 IN (SELECT ...)；子查询必须只返回一列，可使用参数、JOIN、派生表、UNION、ORDER BY 和分页。结果会在修改目标行前物化，并保持 IN / NOT IN 对 NULL 和空集的三值逻辑。
  • 写操作的 IN 子查询当前只接受普通关系表或 measurement 来源，不支持 document、vector/hybrid search、表值函数或引用待修改外层行的相关子查询。轻事务中如果目标表已有缓冲写，也会明确拒绝对该表执行带 IN 子查询的后续 UPDATE / DELETE，避免子查询看到不一致视图。

关系表 JOIN

关系表查询支持 INNER JOIN、LEFT JOIN、RIGHT JOIN、FULL JOIN 和 CROSS JOIN。连接两侧可以是关系表、关系视图、物化视图或可解析为关系行的派生表；连接结果仍按关系查询的列投影、谓词和分页规则执行。列冲突、结果类型、NULL 排序及 ORM 能力发现见[标准 JOIN 合同]({{ site.docs_baseurl | default: '/help' }}/standard-join-contract/)。

measurement 与关系表只支持单个 INNER JOIN、TAG 与关系列的等值 ON；WHERE、标量投影、多键排序和分页支持参数绑定。ON 参数/附加谓词、外连接、多 JOIN 和聚合须由 Provider 生成前按 DataSourceInformation 的 MeasurementJoin* 字段检查。详见 GH-Issue #196 合同与验收。

SELECT d.id, d.name, s.name AS site_name
FROM devices AS d
RIGHT JOIN sites AS s ON d.site_id = s.id;

SELECT d.id, s.id AS site_id
FROM devices AS d
FULL JOIN sites AS s ON d.site_id = s.id;

SELECT d.id, r.retry_count
FROM devices AS d
CROSS JOIN retry_policies AS r;

当前语义与限制：

  • RIGHT JOIN 保留右侧的全部行；没有匹配的左侧列填充 NULL。FULL JOIN 保留两侧全部行，未匹配的一侧填充 NULL。CROSS JOIN 不使用连接谓词，返回两侧行的笛卡尔积；两侧任一为空时结果为空。
  • RIGHT / FULL 的未匹配行和匹配行按 FROM 中的声明顺序处理；同一查询不会为了选择算法而重排结果的声明顺序。CROSS JOIN 也按左侧行、再按右侧行的声明顺序产生结果。
  • RIGHT JOIN 和 FULL JOIN 当前使用有界的声明顺序嵌套循环，并保留 SQL NULL 扩展语义；INNER / LEFT 的 hash、索引和 merge 计划仍可按现有条件选择。CROSS JOIN 使用恒真连接条件，不应当用来替代缺失的过滤条件。
  • ON 连接条件使用 SQL 三值逻辑：条件为 UNKNOWN（包括连接列为 NULL）不算匹配；外连接随后按上述规则产生 NULL 扩展行。WHERE 在外连接完成后执行，因此对补出的列直接过滤可能把外连接结果收窄为内连接结果。
  • 单条语句仍受关系查询的行、内存和取消预算约束；笛卡尔积可能快速增长，应在 WHERE、LIMIT 或更窄的输入上使用它。

关系表轻事务

SqlExecutor.ExecuteScript(...) 和服务端 /sql/batch 支持关系表小批量 DML 轻事务：

BEGIN;
INSERT INTO devices (id, name, enabled) VALUES (3, 'valve-03', TRUE);
UPDATE devices SET enabled = FALSE WHERE id = 1;
DELETE FROM devices WHERE id = 2;
COMMIT;

也可以显式写 BEGIN TRANSACTION；ROLLBACK 会放弃当前轻事务中排队的变更。

当前边界：

  • 轻事务支持同一数据库内多个关系表的 INSERT / UPDATE / DELETE 原子提交与回滚。
  • 不支持嵌套事务、measurement / document 写入事务、DDL 事务、IMPORT JSON 或跨数据库事务。在事务上下文内执行 measurement（时序）INSERT / DELETE、文档集合写入、DDL 或文件导入会抛 NotSupportedException（这些操作不进入关系表事务缓冲，ROLLBACK 无法撤销，故显式拒绝而非静默执行造成"假回滚"）。
  • COMMIT 前会校验 NOT NULL、主键、唯一索引、外键、CHECK 和 ROWVERSION 乐观并发列；任一失败时，不会留下已应用的 rowstore / index 变更。
  • 同一轻事务内的后续 UPDATE 会读取并合并已缓冲变更，连续两次 SET value = value + 1 会累计两次。跨事务仍是 ReadCommitted；事务队列的 UPDATE/DELETE 保留最初读取的行状态，即使没有 ROWVERSION，也会在提交前拒绝已变化或消失的行并返回 table_concurrency_conflict。调用方应重试整个业务事务；跨请求的编辑冲突仍建议用 ROWVERSION 和显式版本谓词检测。
  • 同一轻事务内的 INSERT / UPDATE / DELETE 会按主键归并最终净变化，例如 INSERT→DELETE 不产生提交写入，UPDATE→DELETE 只提交删除，DELETE→INSERT 提交替换；每条多行 INSERT/DELETE 只有在全部行求值和校验成功后才写入事务缓冲。
  • 稳定约束错误码：table_unique_violation、table_foreign_key_violation、table_check_violation、table_concurrency_conflict。
  • 隔离级别边界：ADO.NET 仅接受默认 / ReadCommitted 轻事务；当前语义是单连接排队、提交时获取表管理器锁并一次性校验/应用，不提供 MVCC、可重复读、序列化隔离或跨进程长事务。

时序 JOIN 关系维表

MM4 第一版支持把 measurement 与一个关系表做内连接，用设备、资产、租户、站点等维表补充时序结果：

SELECT t.time, d.name, d.site, t.value
FROM temperature AS t
JOIN devices AS d ON t.device_id = d.id
WHERE d.tenant = 'tenant-1'
  AND t.time >= 1713676800000
ORDER BY t.time DESC
LIMIT 100;

当前行为：

  • 支持 JOIN / INNER JOIN，语义为 inner join。
  • JOIN 左侧必须是 measurement，右侧必须是关系表。
  • ON 当前仅支持一个等值条件，且 measurement 侧连接键必须是 TAG 列；关系表侧可连接主键列或普通列。
  • measurement 侧 tag = '...' 和 time 范围过滤会先下推到时序查询；关系表侧 WHERE 条件会先用主键、二级索引等值前缀或数值/时间范围取得候选行，再做完整过滤。
  • 输出投影支持 time、measurement tag / field、table 列、字面量及标量算术/函数表达式；有歧义的列名必须使用 alias.column。
  • ORDER BY 可引用 JOIN 结果中的 measurement 或 table 列，并在分页前执行。
  • EXPLAIN 支持 JOIN 查询，access_path 会显示 measurement 与 table 双侧下推路径，例如 measurement:tag_index;table:secondary_index;join:hash。

当前限制：

  • measurement JOIN 仍只支持 measurement INNER JOIN relation 的单个内连接；不支持 LEFT JOIN / RIGHT JOIN / FULL JOIN / CROSS JOIN，也不把关系表 JOIN 扩展为 measurement 的外连接或笛卡尔积。
  • 不支持 measurement 与 measurement JOIN、table 与 table JOIN、多表 JOIN、子查询 JOIN。
  • JOIN 查询暂不支持聚合、GROUP BY 或窗口函数。

JSON 文档集合

MM5 第一版支持 JSON 文档集合作为一等数据模型。集合主数据存放在数据库目录的 documents/ 下，使用 KV-backed 存储、独立 schema 文件和可重建 JSON path 索引；不修改时序 .SDBWAL / .SDBSEG 格式。

CREATE DOCUMENT COLLECTION device_docs;

INSERT INTO device_docs (id, document)
VALUES
  ('dev-1', '{"type":"pump","site":"north","metrics":{"temp":21.5}}'),
  ('dev-2', '{"type":"fan","site":"south","metrics":{"temp":18}}');

SELECT id,
       json_value(document, '$.type') AS type,
       json_value(document, '$.metrics.temp') AS temp
FROM device_docs
WHERE json_value(document, '$.site') = 'north';

UPDATE device_docs
SET document = '{"type":"pump","site":"north","metrics":{"temp":22}}'
WHERE id = 'dev-1';

DELETE FROM device_docs
WHERE id = 'dev-2';

元数据与索引：

SHOW DOCUMENT COLLECTIONS;
DESCRIBE DOCUMENT COLLECTION device_docs;

CREATE JSON INDEX idx_device_type ON device_docs ('$.type');
SHOW JSON INDEXES ON device_docs;
DROP JSON INDEX idx_device_type ON device_docs;
DROP DOCUMENT COLLECTION device_docs;

JSON 标量函数也可用于关系表的 JSON 列；path 可以使用 SQL 参数：

SELECT id,
       json_exists(metadata, @path) AS has_path,
       json_array_length(metadata, '$.tags') AS tag_count,
       json_contains(metadata, '$.tags', @tag) AS has_tag
FROM devices;

当前行为：

  • 文档集合固定暴露 id 和 document / json 两个伪列；SELECT * 展开为 id, document。
  • INSERT 需要提供 id 与 document 或 json，JSON 文本会用 System.Text.Json 校验并规范化为紧凑 JSON。
  • SQL UPDATE 使用 SET document = '<json>' 做整体替换；局部更新操作符通过 Document HTTP API 或 SndbDocumentClient.UpdateOneAsync/UpdateManyAsync 调用。
  • 单条 SQL UPDATE / DELETE 会先完成候选规划，再用一个 KV 原子 batch 提交；取消、校验失败或批次预算拒绝不会留下部分修改。该 batch 受 KvOptions.MaxOverlayEntries 和 KvOptions.MaxWalBytes 限制，超限时会在追加 WAL 前整体拒绝；引擎不会为绕过限制自动拆批，因为拆批会破坏语句原子性。
  • json_value(document, '$.path') 支持 $、点属性、$['property'] 和数组下标，例如 $.metrics.temp、$['display-name']、$.tags[0]。
  • json_exists(json, path) 返回 path 是否存在；path 指向 JSON null 时仍返回 TRUE，path 缺失返回 FALSE。JSON 或 path 参数为 NULL 时返回 SQL NULL。
  • json_array_length(json, path) 返回 path 指向数组的元素数（BIGINT）；path 缺失或指向 JSON null 时返回 SQL NULL，指向非数组值时返回执行错误。JSON 或 path 参数为 NULL 时返回 SQL NULL。
  • json_contains(json, path, candidate) 判断 path 结果是否包含候选值。候选字符串按普通 SQL 字符串比较；以 { 或 [ 开头的字符串会按 JSON 对象或数组解析。数组候选按无序子集匹配，对象候选按字段子集匹配；对对象传入普通字符串还可判断字段名是否存在。path 缺失或指向 JSON null 返回 FALSE，任一参数为 NULL 返回 SQL NULL。
  • 这些函数只接受字符串 JSON/path（候选值可为 SQL 标量），并执行固定资源边界：JSON 文本最多 8,388,608 个 UTF-16 字符，path 最多 1024 个字符和 64 个片段，嵌套/比较深度最多 64 层，被计算长度或参与包含比较的单个数组/对象最多 100,000 个元素/字段；每次 json_contains 最多执行 1,000,000 次匹配扫描与结构比较。超限或 JSON/path 无效会返回执行错误，不会静默截断。完整的候选编码、NULL、重复元素、访问容器预算和 JSON path 索引可用条件见[JSON 查询合同]({{ site.docs_baseurl | default: '/help' }}/json-query-contract/)。
  • CREATE JSON INDEX 建立基础 path 等值索引；WHERE json_value(document, '$.type') = 'pump' 可走该索引，EXPLAIN 的 access_path 会显示 json_path_index。
  • id = '...' 会走文档 ID 读取；其它条件走集合扫描后过滤。
  • 不提供 MongoDB wire/Driver 兼容 API 或跨文档复杂事务；集合 validator 支持 SonnetDB 自有 required/type/range/enum/pattern 子集。

Document Store 也提供私有 JSON HTTP API 与 SndbDocumentClient，用于不想拼 SQL 的应用代码。端点统一位于 /v1/db/{db}/documents/{collection} 下，读操作需要数据库 Read 权限，写操作需要 Write 权限：

POST   /v1/db/{db}/documents/{collection}
DELETE /v1/db/{db}/documents/{collection}
POST   /v1/db/{db}/documents/{collection}/insert-one
POST   /v1/db/{db}/documents/{collection}/insert-many
POST   /v1/db/{db}/documents/{collection}/find
POST   /v1/db/{db}/documents/{collection}/find-one
POST   /v1/db/{db}/documents/{collection}/update-one
POST   /v1/db/{db}/documents/{collection}/update-many
POST   /v1/db/{db}/documents/{collection}/update-preview
POST   /v1/db/{db}/documents/{collection}/delete-one
POST   /v1/db/{db}/documents/{collection}/delete-many
POST   /v1/db/{db}/documents/{collection}/count
POST   /v1/db/{db}/documents/{collection}/distinct
POST   /v1/db/{db}/documents/{collection}/aggregate
POST   /v1/db/{db}/documents/{collection}/indexes
DELETE /v1/db/{db}/documents/{collection}/indexes/{index}
POST   /v1/db/{db}/documents/{collection}/indexes/validate
POST   /v1/db/{db}/documents/{collection}/change-feed
// insert-one
{ "id": "dev-1", "document": { "site": "north", "kind": "pump" } }

// find：支持 id/ids 快捷条件、filter AST、projection、sort、limit/skip
{ "id": "dev-1" }
{ "ids": ["dev-1", "dev-2"] }
{ "limit": 100, "skip": 0 }
{
  "filter": {
    "and": [
      { "path": "$.site", "op": "eq", "value": "north" },
      { "path": "$.score", "op": "gte", "value": 5 },
      { "path": "$.tags", "op": "contains", "value": "hot" }
    ]
  },
  "projection": [
    { "name": "_id", "path": "_id" },
    { "name": "temp", "path": "$.metrics.temp" }
  ],
  "sort": [{ "path": "$.score", "descending": true }],
  "limit": 20,
  "skip": 0
}

// update-one：整文档替换
{ "id": "dev-1", "document": { "site": "north", "kind": "pump", "status": "ok" } }

// update-preview / update-one / update-many：同一套局部更新执行器
{
  "filter": { "path": "$.site", "op": "eq", "value": "north" },
  "update": { "set": { "$.status": "active" }, "inc": { "$.revision": 1 } },
  "many": true,
  "limit": 20,
  "upsert": false
}

// compound + unique + sparse + partial index
{
  "name": "ux_site_serial",
  "paths": ["$.site", "$.serial"],
  "isUnique": true,
  "isSparse": true,
  "partialFilter": { "path": "$.active", "operator": "eq", "valueScalar": "true" }
}

// change feed：首请求从 now 或 beginning 开始，后续原样回传 resumeToken
{ "startAt": "now", "operations": ["insert", "update", "delete"], "limit": 100 }
{ "resumeToken": "<opaque token>", "operations": ["insert", "update", "delete"], "limit": 100 }

// distinct：按 JSON path 返回标量 distinct 值
{ "path": "$.site" }

// aggregate：SonnetDB-native JSON aggregation pipeline
{
  "pipeline": [
    { "$match": { "path": "$.score", "op": "gte", "value": 5 } },
    {
      "$group": {
        "keys": [{ "name": "site", "path": "$.site" }],
        "accumulators": [
          { "name": "count", "op": "count" },
          { "name": "total", "op": "sum", "path": "$.score" },
          { "name": "avgScore", "op": "avg", "path": "$.score" }
        ]
      }
    },
    { "$sort": [{ "path": "$.total", "descending": true }] }
  ]
}

filter 操作符支持 eq/ne/gt/gte/lt/lte/in/nin/exists/contains 与 and/or/not 组合；path 可写 _id / id、document / json 或 JSON path。exists 会区分 path 缺失与 JSON null：path 存在且值为 null 时仍视为存在。 aggregate 支持 $match / $project / $group / $sort / $limit / $skip / $unwind / $count / $distinct 等价阶段；$group.accumulators[].op 支持 count、sum、avg、min、max、first、last、distinct。SQL 侧也可以直接在 document collection 上使用 GROUP BY json_value(document, '$.path') 与 count/sum/avg/min/max/first/last。

find 支持 cursor 分页：首个请求传 limit（服务器最大 batch size 为 1000），响应中的 continuationToken 不为空时，把它原样放入下一次 find 请求即可继续读取；续页 token 绑定 collection、查询形状、只读快照版本和 15 分钟过期时间，不能与 skip 混用。写入导致集合版本变化后，旧 token 会被拒绝，需要重新发起首个 find 请求。

update-preview 使用与真实提交相同的服务端局部更新执行器，但不写 WAL、不维护索引、不推进 change feed；响应提供逐文档 before/after/isUpsert/changed。索引管理契约支持 compound、unique、sparse、partial 与 TTL 声明，indexes/validate 只读比对主文档与索引条目。Change feed 记录所有通过 Document store 写入点产生的 insert/update/delete，持久化于集合 KV/WAL，事件保留 7 天；resume token 绑定数据库、集合、操作过滤和 document ID 过滤，有效期 24 小时，超出保留窗口返回 410 resume_token_expired。大于 256 KiB 的单个前后镜像会标记 payloadTruncated，但事件元数据与续传序号仍保留。

当前 Document API 契约刻意不实现 MongoDB wire protocol / BSON command，也不承诺官方 MongoDB Driver 直连。OpenAPI 片段见 document-api.yaml。

JSON 文件虚拟表与导入

MM5 第二批支持把本地 JSON 文件作为只读虚拟表查询，或导入到 document collection / 关系表。JSON 文件能力用于临时查询、迁移和批量导入；导入完成后的主数据仍由 SonnetDB 的 document collection 或 table 托管。

SELECT id,
       json_value(document, '$.site') AS site
FROM json_each('/data/devices.json', 'array', '$.id')
WHERE json_value(document, '$.enabled') = TRUE;

EXPLAIN SELECT id FROM json_each('/data/devices.ndjson', 'lines');

json_each(...) 和兼容别名 json_table(...) 暴露三列：

  • ordinal：文件内从 0 开始的行号。
  • id：默认读取 $.id；也可用第 3 个参数指定 ID path；缺失时使用 ordinal。
  • document：规范化后的紧凑 JSON 文本。

导入语法：

CREATE DOCUMENT COLLECTION device_docs;
IMPORT JSON '/data/devices.ndjson'
INTO device_docs
FORMAT LINES
ID PATH '$.device.id';

CREATE TABLE devices (
  id INT,
  name STRING,
  metadata JSON,
  PRIMARY KEY (id)
);

IMPORT JSON '/data/devices.json'
INTO devices
FORMAT ARRAY;

当前行为：

  • 格式支持 AUTO、ARRAY 和 LINES；AUTO 会识别顶层数组 / 单对象 / JSON Lines。
  • 导入 document collection 时每条记录整体写入 document，ID 来自 ID PATH、默认 $.id 或 ordinal。
  • 导入 table 时要求每条记录是对象，并按列名映射到表列；对象 / 数组可写入 JSON 列。缺失属性按关系 INSERT 的 DEFAULT 语义处理，显式 JSON null 仍写入 NULL。
  • table 导入复用普通关系 INSERT 的单批提交和触发器路径，会统一校验 NOT NULL、主键、唯一索引、外键与 CHECK，并自动生成 ROWVERSION；JSON 中显式提供 ROWVERSION 属性会拒绝整批导入。任一记录或触发器失败时不会留下部分关系行。document collection 导入仍按记录执行 upsert，不承诺整文件原子性。
  • JSON 文件虚拟表不维护索引；EXPLAIN 的 access_path 显示 json_file_virtual_table。

关系表 JSON path 索引

关系表 JSON 列也支持基础 path 等值索引：

CREATE TABLE devices (
  id INT,
  metadata JSON,
  PRIMARY KEY (id)
);

CREATE JSON INDEX idx_devices_site
ON devices (metadata, '$.site');

SELECT id
FROM devices
WHERE json_value(metadata, '$.site') = 'north';

当前行为：

  • 关系表 JSON path 索引只能引用一个 JSON 列和一个 JSON path。
  • 仅支持 json_value(json_col, '$.path') = literal 形式的等值下推；其它谓词仍会扫描过滤。
  • path 缺失或结果为 null 的行不写入 path 索引。
  • SHOW INDEXES ON <table> 的 columns 会显示为 json_col->$.path；EXPLAIN 的 access_path 会显示 json_path_index。

文档全文索引

MM6 第一批把 SonnetDB 内置全文引擎接入 JSON 文档集合，全文索引是从 document collection 主数据派生出的可重建索引。当前实现的索引目录由 SonnetDB 托管在 documents/fulltext/ 下，主数据仍以文档集合为准。

CREATE FULLTEXT INDEX ft_logs_message
ON logs ('$.message')
USING unicode;

SHOW FULLTEXT INDEXES ON logs;
DROP FULLTEXT INDEX ft_logs_message ON logs;

字段和分词器：

  • 字段可写 document / json，表示索引整份 JSON；也可写字符串 JSON path，例如 '$.message'、'$.title'。
  • 支持分词器：unicode、cjk、jieba。不写 USING 时默认 unicode。jieba 使用 SonnetDB.Core 内置中等中文词库；外部词库加载、.dat 编译和索引重建要求见 全文中文词库。
  • 同一个全文索引可包含多个字段，搜索时可指定某个字段，或用 * 搜索该索引内全部字段。

查询示例：

SELECT id, bm25_score() AS score
FROM logs
WHERE match(ft_logs_message, '$.message', 'pump alarm', 20)
ORDER BY score DESC
LIMIT 20;

SELECT id
FROM logs
WHERE match(ft_logs_all, *, 'pump', 20);

当前行为：

  • match(index_name, field, query[, topK]) 必须作为 WHERE 中独立的 AND 谓词使用；当前一个查询只支持一个全文谓词。
  • topK 省略时默认取 100；带分页时会按 OFFSET + FETCH/LIMIT 预取候选，再执行完整 WHERE、排序和分页。
  • bm25_score() 只能在包含 match(...) 的文档集合查询中用于投影或排序，返回 SonnetDB 全文引擎的 BM25 相关性分数。
  • INSERT / UPDATE / DELETE 会同步维护全文索引；索引目录缺失时会从 document collection 主数据重建。
  • EXPLAIN SELECT ... WHERE match(...) 的 access_path 会显示 fulltext_index，index_name 会显示命中的全文索引名。

文档 Hybrid Search

MM8 第一批支持在 document collection 上用全文 BM25 与 JSON embedding 数组做融合排序。文档主数据仍归 document collection 管理；全文索引由 SonnetDB 内置全文引擎派生维护，JSON 向量字段按查询时计算距离。

SELECT id,
       bm25_score() AS text_score,
       vector_distance() AS distance,
       hybrid_score() AS score
FROM hybrid_search(
  source => logs,
  text_index => ft_logs_message,
  text_field => '$.message',
  text => 'pump alarm',
  vector_field => '$.embedding',
  vector => [1, 0, 0],
  k => 20,
  text_weight => 0.6,
  vector_weight => 0.4
)
WHERE site = 'north'
ORDER BY score DESC;

也可以在 measurement KNN + 知识文档融合结果上连接一个关系维表。Planner 会先把 d.tenant = ... 下推给关系表索引，再用命中的 d.id 收窄 measurement measurement_join_tag 的候选 series：

SELECT measurement.device_id AS device,
       d.site AS site,
       document_id,
       hybrid_score() AS score
FROM hybrid_search(
  source => incidents,
  documents => knowledge,
  vector_field => embedding,
  vector => [1, 0, 0],
  measurement_join_tag => device_id,
  document_join_path => '$.device_id',
  text => 'pump alarm'
)
JOIN devices d ON measurement.device_id = d.id
WHERE d.tenant = 'tenant-1'
  AND measurement.time >= 1713676800000
  AND category = 'fault'
ORDER BY score DESC;

当前行为：

  • source 必须是 document collection；text_index 可省略但集合中必须只有一个全文索引。
  • text 是全文查询文本，vector 是查询向量；vector_field 默认 $.embedding，目标 JSON 值必须是 number array。
  • text_field 默认 *，可指定全文索引中的 JSON path 字段。
  • metric 可选，支持 'cosine'、'l2'、'inner_product'；默认 'cosine'。
  • hybrid_score = text_weight * normalized_bm25 + vector_weight * vector_score；不写权重时两者各占 0.5。
  • 结果伪列支持 bm25_score()、vector_distance()、vector_score()、hybrid_score()，也可直接投影 id、document/json 和 JSON 顶层字段名。
  • WHERE 支持对结果伪列或 JSON 顶层字段做基础比较，例如 site = 'north'；复杂文档过滤可用 json_value(document, '$.path')。
  • EXPLAIN 的 access_path 会显示 hybrid_search，index_name 显示使用的全文索引。
  • document collection 内融合只读取 document collection 主数据和派生全文索引，不会把文档主数据交给外部全文或向量数据库。

文档向量搜索

vector_search(...) 用于在 document collection 上执行纯向量检索，不要求全文索引或文本查询。它主要服务 SonnetDB.Data.VectorData adapter，也可直接在 SQL 中使用。

SELECT id,
       json_value(document, '$.title') AS title,
       vector_distance() AS distance,
       vector_score() AS score
FROM vector_search(
  source => logs,
  vector_field => '$.embedding',
  vector => [1, 0, 0],
  k => 20,
  metric => 'cosine'
)
WHERE site = 'north'
ORDER BY distance;

当前行为：

  • source 必须是 document collection；vector_search 不把通用记录映射到 measurement。
  • vector_field 默认 $.embedding，目标 JSON 值必须是 number array，并且维度必须与查询向量一致。
  • metric 可选，支持 'cosine'、'l2'、'inner_product'；默认 'cosine'。
  • 结果伪列支持 vector_distance()、vector_score()，也可投影 id、document/json 和 JSON 顶层字段名。
  • WHERE 支持对结果伪列或 JSON 顶层字段做基础比较；复杂路径可用 json_value(document, '$.path')。
  • EXPLAIN 的 access_path 会显示 document_vector_scan（全表暴力扫）或 document_vector_index（命中持久 ANN 索引），index_name 显示使用的 JSON vector path 或索引名。

持久向量索引（HNSW ANN）

默认 vector_search 对整个 collection 做 O(N·dim) 暴力扫。为集合声明持久向量索引后，无 WHERE、按距离升序（默认或 ORDER BY distance）的查询会走 HNSW ANN，亚线性加速：

CREATE VECTOR INDEX idx_logs_embedding ON logs ('$.embedding')
  WITH (dimensions = 384, metric = 'cosine', m = 16, ef_construction = 200, ef_search = 64);

DROP VECTOR INDEX idx_logs_embedding ON logs;
  • dimensions 必填，须与文档向量数组长度一致；metric 支持 'cosine'/'l2'/'inner_product'（默认 cosine）；m/ef_construction/ef_search 为可选 HNSW 参数（默认 16/200/64）。
  • 索引在 insert/update/delete 时增量维护，随集合重开从主数据的持久化向量重建图（崩溃自愈）。缺少该 path、维度不匹配或坏向量字段的文档不进索引。
  • 走索引的条件：查询 vector_field 等于索引 path、metric 与维度均匹配、且无 WHERE、无自定义降序 ORDER BY。有 WHERE 时回落暴力扫——暴力扫先按谓词过滤再取 Top-K，ANN 先取 Top-K 会漏掉被过滤器排除但更近的行，语义不等价。

Measurement KNN 与知识文档融合

MM8 第二批支持以 measurement 的 VECTOR 字段做 KNN 召回，再通过 measurement tag 与 document collection JSON path 关联知识条目，并可叠加知识文档全文 BM25 与可选知识向量评分：

SELECT measurement.device_id AS device,
       document_id,
       json_value(document, '$.title') AS title,
       measurement_distance() AS m_distance,
       bm25_score() AS text_score,
       hybrid_score() AS score
FROM hybrid_search(
  source => incidents,
  documents => knowledge,
  vector_field => embedding,
  vector => [1, 0, 0],
  k => 20,
  measurement_join_tag => device_id,
  document_join_path => '$.device_id',
  document_join_index => idx_knowledge_device,
  text_index => ft_knowledge_body,
  text_field => '$.body',
  text => 'pump alarm overheating',
  measurement_weight => 0.7,
  text_weight => 0.3
)
WHERE time >= 1713676800000 AND category = 'fault'
ORDER BY score DESC;

当前行为：

  • source 是带 VECTOR 字段的 measurement；documents 是关联的 document collection。
  • vector_field 默认 embedding，也可写 measurement_vector_field；vector 必须与该列维度一致。
  • measurement_join_tag / join_tag 指定 measurement TAG，document_join_path 指定知识文档 JSON path；若有同 path 的 JSON index 或显式 document_join_index，关联会优先走索引。
  • text 可选；提供时会用 text_index / text_field 读取知识文档全文 BM25。未提供 text 时仅做 measurement KNN + 关联文档融合。
  • document_vector_field 可选；提供时会对知识文档中的 JSON number array 再计算一次向量分数。
  • 结果伪列支持 measurement_distance()、measurement_score()、bm25_score()、text_score()、document_vector_distance()、document_vector_score() 和 hybrid_score()；vector_distance() / vector_score() 在该模式下兼容指向 measurement KNN 分数。
  • WHERE 中 measurement time / tag 谓词会下推给 KNN；关系维表谓词会先走主键 / 二级索引候选行并收窄 measurement join tag；剩余谓词可过滤知识文档顶层字段、json_value(document, '$.path') 或融合分数。
  • EXPLAIN 的 access_path 会显示 hybrid_search_measurement_knn_documents；带关系维表过滤时会追加 relation_filter:<table_access_path>。

CREATE MEASUREMENT

定义 measurement schema：

CREATE MEASUREMENT IF NOT EXISTS cpu (
    host TAG,
    region TAG STRING,
    usage FIELD FLOAT NULL,
    count FIELD INT,
    ok FIELD BOOL,
    label FIELD STRING NOT NULL
)

规则：

  • TAG 列默认为字符串，TAG 和 TAG STRING 等价。
  • FIELD 列支持 FLOAT、INT、BOOL、STRING、VECTOR(N)、GEOPOINT。
  • schema 中至少要有一个 FIELD 列。
  • time 不属于 schema 定义的一部分。
  • IF NOT EXISTS 提供并发安全的幂等创建；同名 measurement 已存在时直接成功并保留现有 schema，不使用本次列定义覆盖它。
  • NULL / NOT NULL 可作为 DDL 兼容修饰符出现在列类型后；当前仅保留在 SQL AST 中，执行层不把它持久化为 catalog 约束，也不强制 NOT NULL。
  • DEFAULT <expr> 目前会被 parser 接受，但执行 CREATE MEASUREMENT 时会返回明确的 DEFAULT 暂不支持错误。

稀疏字段语义：

  • SonnetDB 的 field 是稀疏的：同一个 measurement 的不同时间点可以携带不同 field 集合。
  • 如果某个时间点没有写入某个 field，查询该列时结果为 NULL；这表示“该时间点未记录该字段”，不是 schema 约束失败。
  • measurement 不支持关系表 DML 的 DEFAULT 形式；VALUES(DEFAULT) 与 DEFAULT VALUES 会明确拒绝。表达缺值时请省略该 field，或在应用侧写入具体值；显式 NULL 也不是 field 的默认值。

ADO.NET VECTOR(N) 参数使用 float[]、Memory<float> 或 ReadOnlyMemory<float>；DbType 与 GetSchemaTable().ProviderType 为 DbType.Object，结果的 GetValue() 为 float[]，GetFieldType() 和 GetSchemaTable().DataType 为 typeof(float[])。各元素是有限的 IEEE 754 float32，空数组和 NaN/Infinity 在客户端拒绝；维度与 VECTOR(N) 不一致时服务端报 sql_error，消息包含“维度不匹配”。null/DBNull.Value 绑定为 SQL NULL；measurement 的 VECTOR 写入要求非空向量，缺值应省略该字段，并与同点其他 field 一起查询以取得 DBNull.Value。centroid(VECTOR) 也返回 float[]。

嵌入式参数直接绑定为向量字面量；REST/NDJSON 与 Protocol=frame-http2 ADO 写入将有限向量格式化为 SQL 数值数组。HTTP/2 ADO 写入经 /v1/db/{db}/sql，原生 /v1/frame SQL 端点只读。只读 Frame 请求支持 float32 little-endian VECTOR 命名参数，结果使用 VECTOR 值标记；Frame 单请求 payload 上限为 132 MiB，SQL 文本上限为 1 MiB。ADO 写入的向量会先展开为 SQL 文本，因此还受该路径请求体与服务端资源限制约束；大向量应按目标服务的请求预算验证，不存在独立的 ADO VECTOR 维度硬上限。measurement 的 UPDATE ... SET embedding = @vector 仅支持已有 VECTOR FIELD 点与 TAG/time 条件，单句最多 256 行、存活替换记录最多 4096 条及 128 MiB 估算字节量（不是 CLR 堆峰值硬上限）；稀疏目标行和字段残差明确拒绝，同键 INSERT 在替换后也明确拒绝。关系表仍不支持 VECTOR 列；原生 Frame SQL 写入仍只读。

INSERT INTO ... VALUES

INSERT INTO cpu (time, host, region, usage, count, ok, label)
VALUES
    (1713676800000, 'server-01', 'cn-hz', 0.71, 10, TRUE, 'ok'),
    (1713676860000, 'server-01', 'cn-hz', 0.73, 11, TRUE, 'ok')

规则：

  • 未加引号的 time 是保留伪列，表示 Unix 毫秒时间戳；双引号名称（例如 "Time"）按实际 TAG/FIELD 名精确解析，不作为时间戳伪列。
  • time 省略时会使用当前 UTC 毫秒时间。
  • 每一行至少需要提供一个 FIELD 列值。
  • TAG 列必须是字符串字面量。
  • FIELD FLOAT 可以接受整数或浮点字面量。
  • 目标 measurement 不存在时，INSERT 会按列值自动创建 schema；已有 measurement 缺失列时也会自动补齐。
  • SQL INSERT 的未知列默认推断为 FIELD，包括字符串列。需要新 TAG 时，在列列表中写 host TAG；也可以用 message FIELD 显式标注字符串 field。已有列始终按 schema 解释，提示与现有角色不一致时拒绝写入，time 不能带提示。Bulk VALUES 快路径采用相同规则；关系表和文档集合不接受列角色提示。
  • 已有 INT 字段遇到浮点值时会提升为 FLOAT；已有 FLOAT 字段接收整数时会转换为浮点保存，不会降级为 INT。
  • NULL 不能作为当前 INSERT 的显式列值；要表达某个 field 在该时间点缺失，请从列列表中省略它。

原始查询 SELECT

查询所有列：

SELECT * FROM cpu WHERE host = 'server-01'

显式投影：

SELECT time, host, usage
FROM cpu
WHERE host = 'server-01' AND time >= 1713676800000 AND time < 1713677400000
ORDER BY time ASC

标量函数与算术投影：

SELECT abs(-usage), round(usage / 3, 2), sqrt(count), log(count, 10), coalesce(label, 'n/a')
FROM cpu
WHERE host = 'server-01'

SELECT usage + 1 AS next_usage, 2 * 5 + 1 AS constant_value
FROM cpu
WHERE host = 'server-01'

单表别名与限定列名：

SELECT c.time, c.host, c."usage"
FROM cpu AS c
WHERE c.host = 'server-01'
ORDER BY c.time DESC
LIMIT 10

兼容常见探活查询的字面量投影：

SELECT 1 AS ok FROM cpu LIMIT 1

当前行为：

  • SELECT * 会展开为 time + 所有 tag 列 + 所有 field 列。
  • 支持字面量投影（如 SELECT 1 ... LIMIT 1），会按匹配到的时间轴返回常量列。
  • 当某个时间点缺少某个 field 时，结果列会返回 NULL。
  • 标量函数支持 abs、round、sqrt、log、coalesce、concat、lower、upper、trim、ltrim、rtrim、length / char_length、substring / substr、position(search IN value)、replace、left、right、starts_with / ends_with / contains、regexp_like、date_diff / datediff、date_format / format_datetime / strftime / to_char、modbus_int32、modbus_uint32、modbus_float32 及上述日期函数。
  • 字符串函数对字符串参数使用 Ordinal 规则；除 concat 将 NULL 参数视为空字符串外，字符串参数或位置/长度参数为 NULL 时返回 NULL。substring 使用从 1 开始的位置，省略长度时截取到末尾；位置必须大于等于 1，长度和 left / right 的字符数不能为负数。length 返回 .NET UTF-16 字符数（long），不是 UTF-8 字节数。position(search IN value) 返回按 UTF-16 计数的 1 基位置，未找到返回 0，空搜索串返回 1；关系表、Document 和时序查询可通过共用的标量函数求值器调用。

常用字符串函数示例：

SELECT trim(label) AS clean_label,
       substring(label, 1, 12) AS prefix,
       replace(label, '-', '_') AS normalized,
       length(label) AS label_length
FROM devices
WHERE starts_with(label, 'pump-') AND contains(label, 'east')
  • 标量函数可嵌套并接收算术表达式参数；算术表达式也可直接作为顶层投影。
  • 支持 FROM measurement [AS] alias 单表别名，以及 alias.column / alias."Column" 限定列名；执行前会校验限定符必须匹配当前别名。
  • coalesce(...) 只会在当前结果行存在时参与求值；它不会额外扩展原始查询的时间轴。
  • 结果按时间升序返回。

非递归公共表表达式 WITH

关系查询支持一个或多个非递归 CTE。CTE 按声明顺序展开为现有派生表执行路径， 因此可以在主查询或 IN / EXISTS 子查询中使用，也可以让后一个 CTE 引用前一个 CTE：

WITH west (device_id) AS (
    SELECT id FROM devices WHERE region = 'west'
), selected (device_id) AS (
    SELECT device_id FROM west WHERE device_id > 100
)
SELECT d.id
FROM devices AS d
WHERE d.id IN (SELECT device_id FROM selected)
ORDER BY d.id;

当前边界：

  • 非递归 WITH ... AS (SELECT ...) 不支持自引用；递归形式见下一节。
  • CTE 名称按声明顺序解析，后一个 CTE 可以引用前一个 CTE；重复名称会被拒绝。
  • 可用 WITH name (column, ...) AS (...) 按位置重命名输出列；列数必须等于查询结果列数，名称保留拼写且不得仅大小写不同，普通引用忽略大小写，双引号引用精确匹配。即使查询返回空行也校验列数。重命名发生在 CTE 自身排序、分页和集合运算之后；后续 CTE、JOIN、IN 与 EXISTS 使用新列名。
  • CTE 不新增独立 planner，继续复用 FROM (SELECT ...)、关系 JOIN 以及 IN / EXISTS 子查询的现有执行和相关性语义。
  • measurement 和文档 SELECT 可作为 CTE 来源；不支持的来源/查询组合仍遵守各自 SELECT 执行路径的限制。跨服务端 REST/Frame parity 未在此处声明。

有界递归公共表表达式 WITH RECURSIVE

一个递归 CTE 可按 anchor UNION ALL recursive_member 逐层遍历关系表。UNION ALL 保留重复行；UNION 对全部已见行去重，可使包含环的图收敛：

WITH RECURSIVE device_tree (id, depth) AS (
    SELECT id, 0 AS depth FROM devices WHERE id = 1
    UNION ALL
    SELECT child.id, device_tree.depth + 1 AS depth
    FROM devices AS child
    JOIN device_tree ON child.parent_id = device_tree.id
)
SELECT id, depth FROM device_tree ORDER BY id;

递归成员每轮只读取上一层结果；最终 SELECT 读取所有层。支持空 anchor、分支、参数绑定、最终结果排序和分页。同一 WITH RECURSIVE 中可在递归定义之前或之后声明普通 CTE；前置普通 CTE 可供 anchor 和递归成员引用，后置普通 CTE 可读取最终递归结果，名称按声明顺序解析。CTE 输出列名列表的列数必须与 anchor 和递归成员一致，非空值的列类型必须保持一致。每个查询最多展开 64 层、累计保留 100,000 行和约 32 MiB 行数据；每轮候选输出最多 100,000 行，超限时报错，不返回部分结果。EXPLAIN 报告递归工作表及这些上限。

当前只支持一个递归 CTE 定义，以及恰好一次直接自引用的普通 SELECT/JOIN/WHERE 递归成员；不支持互递归、成员内子查询、聚合、DISTINCT、ORDER BY 或分页。递归定义内也不能排序或分页；应在最终 SELECT 指定。UNION ALL 遇到环会达到上限并报错；需要收敛时使用 UNION 或在递归成员中限制访问条件。每轮候选上限通过结果分页提前探测。关系表和关系 JOIN 路径在保留投影行、JOIN 构建行和普通 CTE 派生表副本前，还按保守行大小估算检查单轮 32 MiB 与当前查询/数据库内存预算；取消会在逐行处理时生效。此预算不等于 CLR 堆峰值承诺，表读取与表达式求值可在收费前分配临时对象；仍须遵守实例级资源治理与调用方超时。

SELECT 集合运算

关系查询支持 UNION、UNION ALL、INTERSECT 和 EXCEPT。各分支必须返回相同的列数；复合结果可以在最后一个分支之后使用统一的 ORDER BY、LIMIT 或 OFFSET/FETCH：

SELECT id FROM active_devices
UNION ALL
SELECT id FROM recently_seen
INTERSECT
SELECT id FROM managed_devices
ORDER BY id;

UNION、INTERSECT 和 EXCEPT 会按集合语义去重，UNION ALL 保留重复行。INTERSECT 优先于 UNION / EXCEPT，同优先级运算从左到右应用。INTERSECT ALL 和 EXCEPT ALL 不在本次合同内。

UNION ALL 在没有最终 ORDER BY 时按分支书写顺序保留各分支的行顺序；有最终排序时以排序结果为准。INTERSECT 和 EXCEPT 按整行逐列比较，两个 NULL 在集合去重中视为相等。结果列名取第一个分支；各分支列数必须相同，声明类型或可推断的非 NULL 值类型必须逐列相同。NULL 可与任意已知列类型组合；不同类型请显式 CAST 为同一类型，避免隐式数值转换造成精度损失。空结果分支也按声明类型检查。

集合运算的保留行及去重/交差所用哈希集合受查询或数据库的阻塞算子内存预算约束；超限时报错，不返回部分结果，此路径不使用 spill。预算根据行值与集合结构估算，在分支执行完成后对结果准入，并不是 CLR 堆峰值硬上限；分支扫描与表达式计算仍受各自执行路径的资源约束。

分页子句（兼容两种风格）：

-- SQL 标准风格
SELECT time, host, usage
FROM cpu
WHERE host = 'server-01'
ORDER BY time ASC
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

-- MySQL/PostgreSQL 常见风格
SELECT time, host, usage
FROM cpu
WHERE host = 'server-01'
ORDER BY time DESC
LIMIT 10 OFFSET 20;

说明：

  • 支持 ORDER BY time [ASC|DESC]，排序会在分页前应用；当前要求查询结果中包含 time 列。
  • 支持 OFFSET n（仅跳过，不限制返回行数）。
  • 支持 FETCH FIRST|NEXT n ROW|ROWS ONLY。
  • 支持 LIMIT n [OFFSET m] 兼容语法。
  • ORDER BY/OFFSET/FETCH/LIMIT 作用在最终结果集（投影/聚合之后）。

聚合查询

聚合函数按自身语义声明可接受的 FIELD 类型：

聚合函数 可接受类型 结果与规则
count(*) / count(field) 所有 FIELD 类型 返回行数或字段存在数（long）。
first(field) / last(field) Float64、Int64、Boolean、String、Vector、GeoPoint 按时间戳选择首值/末值，保留原始结果类型。
min(field) / max(field) Float64、Int64、Boolean、String 字符串固定使用 StringComparison.Ordinal；布尔按 false < true。
mode(field) Float64、Int64、Boolean、String 出现次数相同则选择最小值；字符串仍按 Ordinal。
distinct_count(field) Float64、Int64、Boolean、String 返回 HyperLogLog 基数估算（long）。
sum / avg / stddev / variance / spread Float64、Int64、Boolean Boolean 延续历史数值转换规则（false=0、true=1）。
median / percentile / p50 / p90 / p95 / p99 Float64、Int64、Boolean 数值分位统计。
histogram / tdigest_agg / pid / pid_estimate Float64、Int64、Boolean 数学统计或控制聚合，不接受字符串。
centroid Vector 返回各维均值向量。
trajectory_* GeoPoint 返回轨迹距离、中心、边界框或速度统计。

选择器与分类聚合可以直接用于状态字符串和布尔遥测：

SELECT time,
       first(status) AS first_status,
       last(status) AS last_status,
       mode(status) AS common_status,
       distinct_count(status) AS status_kinds
FROM device_state
WHERE device_id = 'device-01'
  AND time >= 1713676800000
  AND time <= 1713680400000
GROUP BY time(60s)

sum(status)、avg(status) 等数学聚合仍会报错，并明确提示该函数需要数值字段。

示例：

SELECT sum(usage), avg(usage), min(usage), max(usage)
FROM cpu
WHERE host = 'server-01'

count(*) 与 SQL 兼容写法 count(1) 也受支持：

SELECT count(*) FROM cpu WHERE host = 'server-01'
SELECT count(1) FROM cpu WHERE host = 'server-01'
SELECT count(*) + 1 AS rows_with_sentinel FROM cpu WHERE host = 'server-01'
SELECT round(avg(usage) + 0.25, 2) AS adjusted_average FROM cpu WHERE host = 'server-01'

count(*) 计的是行/时刻数：同一 series 下多个 field 列写在同一时间戳算作一行，不同时间戳（含只写了部分 field 的稀疏行）取并集去重。多 series 场景下不同 series 的同一时间戳属于不同的行。count(field) 则只计该 field 列有值的时刻数。

标准聚合支持单字段 DISTINCT 输入：count(DISTINCT field)、sum(DISTINCT field)、avg(DISTINCT field)、min(DISTINCT field) 和 max(DISTINCT field) 会在每个查询结果/时间桶中先按 SQL 值相等规则去重，再忽略 NULL 求值；COUNT(DISTINCT *) 明确拒绝。多表达式 DISTINCT、排序集合聚合和窗口 DISTINCT 不在当前合同内。

GROUP BY time(...)

按时间桶聚合：

SELECT avg(usage) AS mean, count(usage)
FROM cpu
WHERE host = 'server-01'
GROUP BY time(1m)

当前限制和真实行为：

  • 仅支持 GROUP BY time(duration)。
  • 仅可用于聚合查询。
  • 不支持 GROUP BY host 这类按列分组。
  • 可在投影中显式写 time（或 time AS bucket）返回桶起始时间；不会自动添加该列。
  • duration 例子：1000ms、30s、1m。

DELETE FROM ... WHERE ...

DELETE FROM cpu
WHERE host = 'server-01' AND time >= 1713676800000 AND time <= 1713677400000

也可以只按 tag 或只按时间范围删除：

DELETE FROM cpu WHERE host = 'server-01'
DELETE FROM cpu WHERE time >= 1713676800000 AND time <= 1713677400000

当前删除语义：

  • 删除底层通过 tombstone 实现，不会原地改写旧 segment。
  • 后续查询会过滤 tombstone 覆盖的点。
  • compaction 会逐步消化已删除数据。

常见保留策略也可以直接写成相对时间：

DELETE FROM cpu
WHERE time >= now() - 30d

WHERE 子句的当前限制

虽然解析器支持更多表达式形态，但当前执行器的稳定支持范围是：

  • tag 等值条件，例如 host = 'server-01'
  • time 的范围比较，例如 time >= 1713676800000 AND time < 1713763200000，或者 time >= now() - 1d AND time < now() + 1d
  • 多个条件使用 AND 连接
  • 关系表和 measurement 过滤支持 value BETWEEN lower AND upper / NOT BETWEEN（上下界 inclusive；任一边或 value 为 NULL 时结果为 UNKNOWN）。
  • 字符串过滤支持 ILIKE / NOT ILIKE，使用 invariant、OrdinalIgnoreCase 规则并保留 % / _ / \\ LIKE 通配符语义。

当前不建议在生产示例中使用：

  • OR
  • tag 不等式
  • field 条件过滤，例如 usage > 0
  • 混合聚合列与普通列，例如 SELECT host, sum(usage) ...

这些写法中的不少在当前版本会直接报错。

元数据查询

SHOW MEASUREMENTS / SHOW TABLES / SHOW VIEWS / SHOW MATERIALIZED VIEWS / SHOW PROCEDURES / SHOW TRIGGERS

SHOW MEASUREMENTS 列出当前数据库中所有时序 measurement，SHOW TABLES 列出当前数据库中所有关系表，两者都按字典序升序返回单列 name。SHOW VIEWS 按名称升序返回 name 与 created_utc。SHOW MATERIALIZED VIEWS 返回名称、刷新状态、定义/活动代际、行数、最近成功刷新时间与错误。SHOW PROCEDURES 与 SHOW TRIGGERS [ON table] 的列合同见前文对应章节。

SHOW MEASUREMENTS;
SHOW TABLES;
SHOW VIEWS;
SHOW MATERIALIZED VIEWS;
SHOW PROCEDURES;
SHOW TRIGGERS ON devices;
name
cpu
mem

SHOW INDEXES ON <table>

列出指定关系表的二级索引：

SHOW INDEXES ON devices;
列 类型 说明
index_name string 索引名
is_unique bool 是否唯一索引
columns string 逗号分隔的索引列
created_utc string UTC ISO-8601 创建时间

DESCRIBE TABLE <name>

描述指定关系表的列结构，按 CREATE TABLE 声明顺序返回：

列 类型 说明
column_name string 列名
data_type string int64 / float64 / boolean / string / datetime / blob / json
is_nullable bool 是否允许 NULL
is_primary_key bool 是否属于主键
ordinal int64 声明顺序
column_default string/null 规范化默认表达式；没有默认值时为 NULL
is_auto_increment bool 是否为数据库自动分配递增整数的列 
DESCRIBE TABLE devices;

DESCRIBE VIEW <name>

返回逻辑视图的名称、SELECT 定义、直接依赖和 UTC 创建时间：

DESCRIBE VIEW active_devices;
列 类型 说明
name string 视图名称
definition string 不含 CREATE VIEW ... AS 前缀的 SELECT SQL
dependencies string 按字典序排列、逗号分隔的直接数据源
created_utc datetime UTC 创建时间

DESCRIBE MATERIALIZED VIEW <name>

返回物化视图定义、依赖、活动代际和最近刷新状态；完整列合同见前文“物化视图”。

DESCRIBE MATERIALIZED VIEW active_device_cache;

DESCRIBE [MEASUREMENT] <name> / DESC <name>

描述指定 measurement 的列结构，按 CREATE MEASUREMENT 声明顺序返回三列：

列 类型 说明
column_name string 列名
column_type string tag 或 field
data_type string float64 / int64 / boolean / string

关键字 MEASUREMENT 可省略，DESC 是 DESCRIBE 的兼容别名。

DESCRIBE MEASUREMENT cpu;
DESCRIBE cpu;       -- 等价
DESC cpu;           -- 等价
column_name column_type data_type
host tag string
usage field float64

若指定 measurement 不存在，会抛出 InvalidOperationException。

EXPLAIN <read-only statement>

EXPLAIN 返回一组 key / value 结果行，用于估算查询会扫描的 series、segment、block 与行数。

EXPLAIN SELECT usage
FROM cpu
WHERE host = 'server-01' AND time >= now() - 1d;

EXPLAIN SHOW MEASUREMENTS;
EXPLAIN SHOW INDEXES ON devices;
EXPLAIN DESCRIBE MEASUREMENT cpu;

当前支持范围：

  • SELECT ...
  • SHOW MEASUREMENTS / SHOW TABLES / SHOW VIEWS / SHOW MATERIALIZED VIEWS / SHOW DOCUMENT COLLECTIONS
  • SHOW INDEXES ON <table> / SHOW JSON INDEXES ON <collection> / SHOW FULLTEXT INDEXES ON <collection>
  • DESCRIBE [MEASUREMENT] <name> / DESC <name>
  • DESCRIBE TABLE <name>
  • DESCRIBE VIEW <name>
  • DESCRIBE MATERIALIZED VIEW <name>
  • DESCRIBE DOCUMENT COLLECTION <name>

当前不支持对 INSERT、DELETE、CREATE、DROP、用户/授权/Token 控制面 SQL 做 EXPLAIN。 返回字段包括 database、statement_type、measurement、matched_series_count、estimated_segment_count、estimated_block_count、estimated_scanned_rows、estimated_memtable_rows、estimated_segment_rows、has_time_filter、tag_filter_count、access_path 与 index_name。关系表查询的 access_path 可能是 primary_key、secondary_index、secondary_index_prefix、secondary_index_range、json_path_index 或 table_scan；文档集合查询可能是 document_id、json_path_index、fulltext_index 或 document_scan；JSON 文件虚拟表会显示 json_file_virtual_table。

控制面 SQL

控制面 SQL 仅在服务端模式可用。

用户与密码

CREATE USER alice WITH PASSWORD 'pa$$'
CREATE USER admin2 WITH PASSWORD 'secret' SUPERUSER
ALTER USER alice WITH PASSWORD 'new-password'
DROP USER alice

数据库

CREATE DATABASE metrics
DROP DATABASE metrics
SHOW DATABASES

授权

GRANT READ ON DATABASE metrics TO alice
GRANT WRITE ON DATABASE metrics TO alice
GRANT ADMIN ON DATABASE * TO admin2
REVOKE ON DATABASE metrics FROM alice

查询用户、授权与 Token

SHOW USERS
SHOW GRANTS
SHOW GRANTS FOR alice
SHOW TOKENS
SHOW TOKENS FOR alice
ISSUE TOKEN FOR alice
REVOKE TOKEN 'tok_abcdef'

说明：

  • SHOW TOKENS 只返回 Token 元数据，不返回明文。
  • ISSUE TOKEN FOR ... 会在结果里一次性返回明文 Token。
  • REVOKE TOKEN 'tok_xxx' 按 token id 吊销。

HTTP 端点

端点 用途
POST /v1/db/{db}/sql 单条 SQL，主要用于数据面；admin 也可通过它执行部分控制面语句
POST /v1/db/{db}/sql/batch 批量 SQL 脚本
POST /v1/sql 专用控制面 SQL 端点，仅 admin
GET /v1/db/{db}/modbus Modbus runtime、source、endpoint 与 binding 概览，需要数据库 Read
GET /v1/db/{db}/modbus/writes Endpoint 外部写待审批队列，需要数据库 Admin
GET /v1/db/{db}/modbus/write-audit Endpoint 写治理事件，需要数据库 Admin
POST /v1/db/{db}/modbus/writes/{requestId}/approve 批准 staged 写，需要数据库 Write + Admin
POST /v1/db/{db}/modbus/writes/{requestId}/reject 拒绝 staged 写，需要数据库 Admin

角色与权限

  • readonly：仅查询
  • readwrite：可写入和查询
  • admin：可管理数据库、执行控制面 SQL、进入完整管理能力

相关页面

  • [批量写入]({{ site.docs_baseurl | default: '/help' }}/bulk-ingest/)
  • [ADO.NET 参考]({{ site.docs_baseurl | default: '/help' }}/ado-net/)
  • [CLI 参考]({{ site.docs_baseurl | default: '/help' }}/cli-reference/)