68 lines
4.1 KiB
SQL
68 lines
4.1 KiB
SQL
-- 单表发布模型（方案 2）：取消草稿表，审核态由 status 承载，公开读走只读视图。
|
||
--
|
||
-- 本文件只做两件事：加列 / 建视图。幂等，可重复执行。
|
||
-- 存量行置已发布不在本文件里（它是只能执行一次的数据变更，见 2026-09-22-01b-publish-existing-rows.sql）。
|
||
-- 数据搬迁由一次性脚本 scripts/migrate_single_table/main.go 完成。该脚本已随本次改造从工作树删除
|
||
--（它 import 已删除的草稿模型，无法在改造后的代码树上编译）；取回方式与执行顺序见本目录 README.md。
|
||
-- 删草稿表见 2026-09-22-05-drop-draft-tables.sql。
|
||
|
||
-- 1) 正式表补审核态与溯源列。默认 'pending' 是刻意的（fail-closed）：
|
||
-- 任何漏赋值的行默认不可见，而不是意外对外发布。
|
||
ALTER TABLE brand_runways
|
||
ADD COLUMN IF NOT EXISTS status varchar(16) NOT NULL DEFAULT 'pending',
|
||
ADD COLUMN IF NOT EXISTS job_id bigint NOT NULL DEFAULT 0,
|
||
ADD COLUMN IF NOT EXISTS reviewer varchar(64) NOT NULL DEFAULT '',
|
||
ADD COLUMN IF NOT EXISTS reject_reason varchar(255) NOT NULL DEFAULT '';
|
||
|
||
ALTER TABLE street_snaps
|
||
ADD COLUMN IF NOT EXISTS status varchar(16) NOT NULL DEFAULT 'pending',
|
||
ADD COLUMN IF NOT EXISTS job_id bigint NOT NULL DEFAULT 0,
|
||
ADD COLUMN IF NOT EXISTS reviewer varchar(64) NOT NULL DEFAULT '',
|
||
ADD COLUMN IF NOT EXISTS reject_reason varchar(255) NOT NULL DEFAULT '',
|
||
-- 街拍标题中文化：保留既有 title 作英文源，仅加 title_cn 供后台人工翻译、前端按 locale 取用。
|
||
-- 与 brand_runways 用 title_en/title_cn 不同：street_snaps 上有 uq_ss_entity(city, year, COALESCE(title,''))，
|
||
-- 改名成本过高；pickLocale 在中文为空时回落英文，无需数据回灌。
|
||
ADD COLUMN IF NOT EXISTS title_cn text NOT NULL DEFAULT '';
|
||
|
||
-- 2) 存量行置已发布**不在本文件里**：它是数据变更且只能执行一次，见同目录
|
||
-- 2026-09-22-01b-publish-existing-rows.sql。
|
||
-- 为什么必须拆开：数据搬迁（scripts/migrate_single_table/main.go）会把 pending 草稿连原 created_at
|
||
-- 一起搬进正式表；若本文件含那条 UPDATE 且被重复执行（测试每次都会跑），
|
||
-- 这些待审内容会被误刷成已发布 —— 恰好是本设计要防的泄漏。
|
||
|
||
-- 3) 状态索引：审核列表与公开视图都按 status 过滤。
|
||
CREATE INDEX IF NOT EXISTS idx_br_status ON brand_runways (status);
|
||
CREATE INDEX IF NOT EXISTS idx_ss_status ON street_snaps (status);
|
||
|
||
-- 4) 公开只读视图：把「必须是已发布且未删除」固化成结构性保证，公开查询漏写过滤成为不可能。
|
||
-- 视图列集显式枚举（非 SELECT *）：只暴露对外需要的列，刻意排除审核/溯源列
|
||
-- （job_id / reviewer / reject_reason），避免敏感列随基表加列被无意暴露到公开读路径。
|
||
-- 图片子视图仍取 i.*（图片表无敏感列）；改动基表列集时按本文件同批重建视图。
|
||
DROP VIEW IF EXISTS public_brand_runway_images;
|
||
DROP VIEW IF EXISTS public_brand_runways;
|
||
DROP VIEW IF EXISTS public_street_snap_images;
|
||
DROP VIEW IF EXISTS public_street_snaps;
|
||
|
||
CREATE VIEW public_brand_runways AS
|
||
SELECT
|
||
id, brand_id, title_en, title_cn, description_en, description_cn,
|
||
cover, image_count, year, collection_type, season, season_code,
|
||
status, is_deleted, created_at, updated_at
|
||
FROM brand_runways WHERE status = 'published' AND is_deleted = 0;
|
||
|
||
CREATE VIEW public_brand_runway_images AS
|
||
SELECT i.* FROM brand_runway_images i
|
||
JOIN brand_runways r ON r.id = i.runway_id
|
||
WHERE i.is_deleted = 0 AND r.status = 'published' AND r.is_deleted = 0;
|
||
|
||
CREATE VIEW public_street_snaps AS
|
||
SELECT
|
||
id, title, title_cn, year, city, cover, image_count,
|
||
status, is_deleted, created_at, updated_at
|
||
FROM street_snaps WHERE status = 'published' AND is_deleted = 0;
|
||
|
||
CREATE VIEW public_street_snap_images AS
|
||
SELECT i.* FROM street_snap_images i
|
||
JOIN street_snaps s ON s.id = i.snap_id
|
||
WHERE i.is_deleted = 0 AND s.status = 'published' AND s.is_deleted = 0;
|