33 lines
3.1 KiB
SQL
33 lines
3.1 KiB
SQL
-- 接收方独立数据库的初始示例表；不属于Orbit平台迁移，不创建外键或级联删除。
|
||
CREATE TABLE receiver_inbox (
|
||
consumer_name VARCHAR(64) CHARACTER SET ascii COLLATE ascii_bin NOT NULL COMMENT '固定消费者用途；同一用途重启或多实例必须复用',
|
||
queue_id CHAR(32) CHARACTER SET ascii COLLATE ascii_bin NOT NULL COMMENT '接收方绑定的队列标识；隔离不同来源范围',
|
||
event_id CHAR(32) CHARACTER SET ascii COLLATE ascii_bin NOT NULL COMMENT '原事件标识；跨连接及交付代次保持不变',
|
||
event_digest BINARY(32) NOT NULL COMMENT '规范化冻结事件JSON的SHA256；同身份不同内容拒绝确认',
|
||
event_json MEDIUMTEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL COMMENT '冻结事件及业务结果；包含业务数据须限制访问，不含租约证明',
|
||
applied BOOLEAN NOT NULL DEFAULT FALSE COMMENT '是否推进业务视图；迟到的旧版本只保留Inbox历史',
|
||
received_at DATETIME(6) NOT NULL COMMENT '本次业务事务受理事件的UTC时间，微秒精度',
|
||
PRIMARY KEY (consumer_name,queue_id,event_id),
|
||
CONSTRAINT ck_receiver_event_json CHECK (JSON_VALID(event_json)),
|
||
CONSTRAINT ck_receiver_applied CHECK (applied IN (0,1))
|
||
) ENGINE=InnoDB COMMENT='事务Inbox；去重记录与业务视图一起提交，禁止独立预写成功标志';
|
||
|
||
CREATE TABLE receiver_task_views (
|
||
consumer_name VARCHAR(64) CHARACTER SET ascii COLLATE ascii_bin NOT NULL COMMENT '固定消费者用途；与Inbox使用相同范围',
|
||
queue_id CHAR(32) CHARACTER SET ascii COLLATE ascii_bin NOT NULL COMMENT '固定队列来源；由处理器事务前验证归属',
|
||
task_id CHAR(32) CHARACTER SET ascii COLLATE ascii_bin NOT NULL COMMENT '原任务标识；每个任务建立一份版本化业务视图',
|
||
last_revision BIGINT UNSIGNED NOT NULL COMMENT '已应用最大任务版本；0仅为同事务中尚未更新的占位状态',
|
||
last_event_id CHAR(32) CHARACTER SET ascii COLLATE ascii_bin NULL COMMENT '推进当前视图的事件；占位行为空，提交后的有效视图非空',
|
||
last_state VARCHAR(24) CHARACTER SET ascii COLLATE ascii_bin NOT NULL COMMENT '冻结任务结果状态；UNINITIALIZED只用于同事务内占位',
|
||
result_json MEDIUMTEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL COMMENT '当前结果JSON；来源未提供结果时为空，保留精确数字',
|
||
applied_events BIGINT UNSIGNED NOT NULL COMMENT '实际推进视图的事件次数；重复和旧版本不增加',
|
||
created_at DATETIME(6) NOT NULL COMMENT '视图首次建立的UTC时间，微秒精度',
|
||
updated_at DATETIME(6) NOT NULL COMMENT '视图最后推进的UTC时间，微秒精度',
|
||
PRIMARY KEY (consumer_name,queue_id,task_id),
|
||
CONSTRAINT ck_receiver_revision CHECK (
|
||
(last_revision=0 AND last_event_id IS NULL AND last_state='UNINITIALIZED' AND applied_events=0)
|
||
OR (last_revision>0 AND last_event_id IS NOT NULL AND applied_events>0 AND last_state IN ('SUCCEEDED','FAILED','CANCELLED','NEEDS_REVIEW'))
|
||
),
|
||
CONSTRAINT ck_receiver_result CHECK (result_json IS NULL OR JSON_VALID(result_json))
|
||
) ENGINE=InnoDB COMMENT='业务侧任务结果视图；锁定单任务行后仅接受更高版本，防止迟到结果倒退';
|