64 lines
3.7 KiB
SQL
64 lines
3.7 KiB
SQL
-- User-device lifecycle and device-initiated unbinding support (MySQL 8+).
|
|
CREATE TABLE app_device_binding (
|
|
id bigint NOT NULL,
|
|
device_no varchar(64) NOT NULL COMMENT '设备编号',
|
|
user_id bigint NOT NULL COMMENT '用户ID',
|
|
binding_id varchar(64) NOT NULL COMMENT '本次绑定标识',
|
|
binding_status varchar(16) NOT NULL COMMENT 'BINDING/ACTIVE/BIND_FAILED/UNBINDING/UNBOUND',
|
|
binding_started_time datetime NULL COMMENT '本次绑定开始时间',
|
|
last_bind_command_time datetime NULL COMMENT '最近一次绑定命令下发时间',
|
|
device_name_snapshot varchar(255) NULL COMMENT '列表显示名称快照',
|
|
unbound_time datetime NULL COMMENT '解绑完成时间',
|
|
unbind_source varchar(16) NULL COMMENT 'DEVICE/APP/ADMIN',
|
|
unbind_command_id varchar(64) NULL COMMENT '触发解绑的命令编号',
|
|
cleanup_attempts int NOT NULL DEFAULT 0 COMMENT '清理尝试次数',
|
|
cleanup_error varchar(500) NULL COMMENT '最近清理错误',
|
|
create_dept bigint NULL,
|
|
create_by bigint NULL,
|
|
create_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
update_by bigint NULL,
|
|
update_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
exclusive_device_no varchar(64) GENERATED ALWAYS AS (
|
|
CASE WHEN binding_status IN ('BINDING', 'ACTIVE', 'UNBINDING') THEN device_no ELSE NULL END
|
|
) STORED,
|
|
PRIMARY KEY (id),
|
|
UNIQUE KEY uk_app_device_binding_user_device (user_id, device_no),
|
|
UNIQUE KEY uk_app_device_binding_binding_id (binding_id),
|
|
UNIQUE KEY uk_app_device_binding_exclusive (exclusive_device_no),
|
|
KEY idx_app_device_binding_device_status (device_no, binding_status),
|
|
KEY idx_app_device_binding_recovery (binding_status, binding_started_time, last_bind_command_time)
|
|
) COMMENT='用户设备绑定生命周期';
|
|
|
|
CREATE TABLE app_device_unbind_command (
|
|
id bigint NOT NULL,
|
|
device_no varchar(64) NOT NULL COMMENT '设备编号',
|
|
command_id varchar(64) NOT NULL COMMENT '设备解绑命令编号',
|
|
binding_id varchar(64) NOT NULL COMMENT '命令目标绑定标识',
|
|
command_status varchar(16) NOT NULL COMMENT 'PROCESSING/APPLIED/STALE/FAILED',
|
|
message varchar(500) NULL COMMENT '处理结果',
|
|
attempt_count int NOT NULL DEFAULT 0 COMMENT '处理次数',
|
|
completed_time datetime NULL COMMENT '完成时间',
|
|
create_dept bigint NULL,
|
|
create_by bigint NULL,
|
|
create_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
update_by bigint NULL,
|
|
update_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (id),
|
|
UNIQUE KEY uk_app_device_unbind_command (device_no, command_id),
|
|
KEY idx_app_device_unbind_binding (device_no, binding_id)
|
|
) COMMENT='设备解绑命令处理记录';
|
|
|
|
-- Existing bindings must re-establish the device-side bindingId before becoming ACTIVE.
|
|
INSERT INTO app_device_binding (
|
|
id, device_no, user_id, binding_id, binding_status, device_name_snapshot,
|
|
binding_started_time, cleanup_attempts, create_time, update_time
|
|
)
|
|
SELECT
|
|
UUID_SHORT(), device_no, user_id, REPLACE(UUID(), '-', ''), 'BINDING', device_name,
|
|
NOW(), 0, NOW(), NOW()
|
|
FROM app_device
|
|
WHERE user_id IS NOT NULL;
|
|
|
|
-- Keep the legacy column during the rollout, but remove it as a relationship source.
|
|
UPDATE app_device SET user_id = NULL WHERE user_id IS NOT NULL;
|