selfrelease
a329d4906b
- 方案文档: AVCC 体系建设、IPTV TCS 需求(0-req)/PRD(1-prd)/任务(2-task)/二三四期任务 - tcs-iptv: Go 后端(哈希SDK/MA码生成/可信数据空间mock/业务编排/HTTP API+HMAC鉴权) - web-console: React+AntD 监管大屏(角色工作台/全流程演示/监管片库) - 一剧一码+集级哈希, 集级下架/恢复, 全栈测试通过
86 lines
3.8 KiB
PL/PgSQL
86 lines
3.8 KiB
PL/PgSQL
-- TCS-IPTV 初始化迁移
|
|
-- 说明：链上为权威数据源；PostgreSQL 存业务元数据与链上数据镜像，用于高效查询。
|
|
-- 对应需求：需求16（数据结构）、需求3/6/7（映射）、需求12（版本变更）
|
|
-- 注：CTID 概念列统一命名为 content_twin_id，避开 PostgreSQL 系统保留列名 ctid。
|
|
|
|
BEGIN;
|
|
|
|
-- 内容主表（Content Registry 镜像）
|
|
CREATE TABLE IF NOT EXISTS content_registry (
|
|
content_twin_id VARCHAR(64) PRIMARY KEY,
|
|
ma_code VARCHAR(128) NOT NULL UNIQUE,
|
|
ma_type VARCHAR(64),
|
|
title VARCHAR(256) NOT NULL,
|
|
episode_count INT DEFAULT 1,
|
|
status VARCHAR(32) NOT NULL DEFAULT 'pending',
|
|
issuer VARCHAR(128),
|
|
issue_date DATE,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
CREATE INDEX IF NOT EXISTS idx_content_status ON content_registry(status);
|
|
|
|
-- 哈希绑定表（Hash Binding 镜像）
|
|
CREATE TABLE IF NOT EXISTS hash_binding (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
content_twin_id VARCHAR(64) NOT NULL REFERENCES content_registry(content_twin_id),
|
|
hash_type VARCHAR(32) NOT NULL, -- file_sha256 / perceptual / transcoded
|
|
hash_value VARCHAR(128) NOT NULL,
|
|
merkle_root VARCHAR(128),
|
|
file_format VARCHAR(64),
|
|
resolution VARCHAR(32),
|
|
duration INT,
|
|
version VARCHAR(32) NOT NULL DEFAULT 'v1.0',
|
|
parent_hash VARCHAR(128), -- 转码版指向母版哈希（父子关系）
|
|
created_by VARCHAR(128),
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
CREATE INDEX IF NOT EXISTS idx_hash_ctid ON hash_binding(content_twin_id);
|
|
CREATE INDEX IF NOT EXISTS idx_hash_value ON hash_binding(hash_value);
|
|
|
|
-- 三方编码映射表（Identity Mapping 镜像）
|
|
CREATE TABLE IF NOT EXISTS identity_mapping (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
content_twin_id VARCHAR(64) NOT NULL REFERENCES content_registry(content_twin_id),
|
|
party VARCHAR(32) NOT NULL, -- cp / reviewer / operator
|
|
party_id VARCHAR(128) NOT NULL,
|
|
party_name VARCHAR(128),
|
|
cdn_endpoint VARCHAR(256),
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
UNIQUE (content_twin_id, party, party_id)
|
|
);
|
|
CREATE INDEX IF NOT EXISTS idx_mapping_ctid ON identity_mapping(content_twin_id);
|
|
CREATE INDEX IF NOT EXISTS idx_mapping_party ON identity_mapping(party, party_id);
|
|
|
|
-- 版本变更表（Version History 镜像）
|
|
CREATE TABLE IF NOT EXISTS version_history (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
content_twin_id VARCHAR(64) NOT NULL REFERENCES content_registry(content_twin_id),
|
|
version VARCHAR(32) NOT NULL,
|
|
change_reason TEXT,
|
|
prev_hash VARCHAR(128),
|
|
new_hash VARCHAR(128),
|
|
reaudit_required BOOLEAN NOT NULL DEFAULT TRUE,
|
|
reaudit_status VARCHAR(32) DEFAULT 'pending',
|
|
affected_episode INT,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
CREATE INDEX IF NOT EXISTS idx_version_ctid ON version_history(content_twin_id);
|
|
|
|
-- 链交易记录（异步上链确认）
|
|
CREATE TABLE IF NOT EXISTS chain_tx (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
content_twin_id VARCHAR(64),
|
|
tx_id VARCHAR(128) NOT NULL UNIQUE,
|
|
method VARCHAR(64) NOT NULL, -- issueMA / registerMapping / ...
|
|
status VARCHAR(16) NOT NULL DEFAULT 'pending', -- pending / confirmed / failed
|
|
block_height BIGINT,
|
|
payload_hash VARCHAR(128),
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
confirmed_at TIMESTAMPTZ
|
|
);
|
|
CREATE INDEX IF NOT EXISTS idx_tx_ctid ON chain_tx(content_twin_id);
|
|
CREATE INDEX IF NOT EXISTS idx_tx_status ON chain_tx(status);
|
|
|
|
COMMIT;
|