SonnetDB 视图、物化视图与 SQL 例程:保存查询还是保存结果

SonnetDB 的视图、物化视图和 SQL 例程解决的是三类不同问题:视图保存查询定义,物化视图保存最近一次成功刷新的结果,例程把受限的 SQL 语句组织成一次有边界的调用。本文按当前 SQL 参考说明这些入口的操作方式、依赖保护、权限和失败边界;它是本地重建稿,不能替代已发布

SonnetDB 的视图、物化视图和 SQL 例程解决的是三类不同问题:视图保存查询定义,物化视图保存最近一次成功刷新的结果,例程把受限的 SQL 语句组织成一次有边界的调用。本文按当前 SQL 参考说明这些入口的操作方式、依赖保护、权限和失败边界;它是本地重建稿,不能替代已发布页面的原始正文回读。

先选正确的对象

逻辑视图适合保存经常复用的 SELECT 定义。读取时,SonnetDB 展开定义并重新执行,所以基础数据变化会反映到后续查询。物化视图适合把一次成功刷新后的结果作为只读关系源使用;创建物化视图只登记定义,不会隐式执行首次刷新。

例程用于组织有限数量的 SQL 语句。它可以包含受支持的查询和关系表 DML,也可以和触发器一起参加关系事务。例程的执行预算、嵌套深度、结果行数和提交诊断都有独立边界,不能把它当成无限制的脚本执行器。

创建和读取逻辑视图

下面的例子保存一个关系查询定义:

CREATE VIEW active_devices AS
SELECT device_id, site, last_seen
FROM devices
WHERE enabled = TRUE;

SELECT device_id, site
FROM active_devices
WHERE site = 'north'
ORDER BY device_id;

视图可以基于当前支持的关系表、measurement、文档集合、其它视图、JOIN、UNION 和子查询。创建时会检查数据源存在、自引用和名称冲突;运行时还会限制直接或间接循环,展开层数最多 32 层。被视图依赖的基础对象不能直接 DROP 或执行受保护的 schema ALTER,当前版本不提供 CASCADE 或 OR REPLACE 的替代语义。

SHOW VIEWS 返回名称和创建时间,DESCRIBE VIEW 返回定义、直接依赖及创建时间。需要读取列声明时,可以使用零行查询:

SELECT * FROM active_devices LIMIT 0;

这个查询绑定声明而不执行视图的过滤、排序或 UDF。ADO.NET 读取方可以在尚未调用 Read() 时使用 GetFieldType、GetDataTypeName、GetSchemaTable 或 GetColumnSchema 取得列元数据。未知表达式的精度和长度保持未知,不能用样本值补造声明。

创建、刷新和使用物化视图

物化视图保存 SELECT 定义和最近一次成功刷新的物理结果:

CREATE MATERIALIZED VIEW active_device_cache AS
SELECT device_id, site, last_seen
FROM devices
WHERE enabled = TRUE;

REFRESH MATERIALIZED VIEW active_device_cache;

SELECT site, COUNT(*)
FROM active_device_cache
GROUP BY site;

创建后首次读取前必须显式刷新。刷新需要数据库写权限,不能放在活动轻事务中,同一个物化视图也不能并发刷新。刷新成功后才会发布新的活动代际;失败时保留旧代际,调用方应把刷新失败和“没有新结果”分开处理。

物化快照是只读关系源,可以参与外层过滤、投影、聚合、子查询和关系 JOIN。EXPLAIN 读取物化视图时会显示 materialized_view_snapshot 访问路径,并按活动代际的行数估算扫描。它仍然不是增量 rollup,也不会自动把每次基础表写入变成物化视图刷新。

SHOW MATERIALIZED VIEWS 和 DESCRIBE MATERIALIZED VIEW 可查看定义、依赖、活动代际、行数、最近成功刷新时间和错误状态。物化视图与基础表、逻辑视图共享 SQL 数据源命名空间,删除和 schema 修改继续受到依赖保护。

有界 SQL 例程

例程把一组受支持的语句放在一个命名入口中:

CREATE PROCEDURE add_device(
    IN p_device_id TEXT,
    IN p_site TEXT
)
BEGIN
    INSERT INTO devices(device_id, site, enabled)
    VALUES (p_device_id, p_site, TRUE);

    SELECT device_id, site
    FROM devices
    WHERE device_id = p_device_id;
END;

调用者可以使用 CALL add_device('device-001', 'north')。直接 Core 文本调用默认返回 body 最后一条语句的结果;类型化调用可以捕获完整结果。例程不能递归调用自己或形成间接递归。每次调用默认最多执行 64 条 body 语句、嵌套 8 层并累计产生 10000 行结果;中间结果也会消耗剩余预算,超限时失败而不会静默截断。

例程目录记录参数方向和版本。未知目录版本或方向会拒绝读取。当前 ADO 标准命令、完整 Reader 回写、远程协议和第三方 ORM 集成仍需要各自验收,Core 支持不能外推为所有客户端已经兼容。

触发器、outbox 和提交边界

关系表触发器可以在 INSERT、UPDATE 或 DELETE 前后运行受限 body。触发器有固定的执行顺序、启停和依赖保护;SHOW TRIGGERS、DESCRIBE TRIGGER 和 EXPLAIN TRIGGER 用于查看定义和诊断。触发器 body 不能调用任意 UDF、外部函数、文件入口或其它模型,也不能通过重入活动 SQL 事务绕过提交边界。

触发器 transition 行数和字节数有独立预算,INSERT SELECT 的源查询和目标 mutation 可能分别计费。超限会在目标写入前拒绝整条语句。把 outbox 写入源表触发器可以让源 DML 和 outbox 行处于同一关系提交边界;外部投递和 ACK 仍属于另一个边界,不能直接宣称 exactly-once。

例程诊断会区分 pending、committed、rolled_back、failed、unknown 和 completed。持久化决定开始后,未知结果必须通过关闭、重开和幂等键核对,不能根据客户端超时把它改写为确定失败。

预算、权限和恢复边界

视图读取、物化视图刷新、例程调用和触发器提交都继续遵守关系查询、阻塞算子和取消预算。物化预算是保守的物化准入,不等同 CLR 堆峰值硬上限;解析、绑定、存储页、索引缓存、事务日志和调用方持有的结果仍有各自资源成本。

CREATE、DROP、ALTER VIEW、REFRESH MATERIALIZED VIEW、例程和触发器 DDL 需要相应写权限;SHOW、DESCRIBE 和只读查询只需要读权限。单库备份也不自动覆盖 Server 实例资源、SonnetMQ、外部 outbox 投递或其它跨模型状态。

本文引用的能力状态是当前源码和文档的 partial 合同。没有在本轮运行真实数据库、远程协议、固定硬件、NativeAOT 安装或长期生产恢复演练;Graph 仍是 Beta,相关能力不应从本文外推为完整生产门禁。

参考:

原文地址:https://iotpaper.net/articles/sonnetdb/178-views-materialized-views-and-sql-routines/