R524809
85 lines
5.4 KiB
PL/PgSQL
85 lines
5.4 KiB
PL/PgSQL
-- 已执行旧版 002 的库在完成 004 后执行；最新空库基线不执行 004/005。
|
|
-- 保留旧 ID/path、计数、配置和历史；不伪造资源条目或自动推断绘制方法。
|
|
BEGIN;
|
|
SET LOCAL TIME ZONE 'UTC';
|
|
SET LOCAL lock_timeout = '5s';
|
|
SET LOCAL search_path = public, pg_catalog;
|
|
|
|
ALTER TABLE public.worksheets
|
|
ADD COLUMN generation_mode varchar(16) NOT NULL DEFAULT 'procedural',
|
|
ADD COLUMN renderer_key varchar(64) COLLATE "C",
|
|
ADD COLUMN render_config jsonb NOT NULL DEFAULT '{}'::jsonb,
|
|
ADD CONSTRAINT worksheets_generation_mode_check CHECK (generation_mode IN ('procedural', 'resource')),
|
|
ADD CONSTRAINT worksheets_renderer_key_check CHECK (
|
|
renderer_key IS NULL OR length(btrim(renderer_key)) > 0
|
|
),
|
|
ADD CONSTRAINT worksheets_render_config_object_check CHECK (jsonb_typeof(render_config) = 'object');
|
|
|
|
ALTER TABLE public.assets DROP CONSTRAINT assets_purpose_check;
|
|
ALTER TABLE public.assets ADD CONSTRAINT assets_purpose_check CHECK (
|
|
purpose IN ('avatar', 'preview', 'category_icon', 'font', 'share', 'page_config', 'worksheet_source', 'other')
|
|
);
|
|
|
|
-- 资源条目代表一份完整可选择内容，不等于单个文件，也不是独立模板引擎。
|
|
CREATE TABLE public.worksheet_resources (
|
|
id varchar(128) COLLATE "C" PRIMARY KEY,
|
|
worksheet_id varchar(128) COLLATE "C" NOT NULL,
|
|
title varchar(100) NOT NULL,
|
|
preview_asset_id varchar(128) COLLATE "C" NOT NULL,
|
|
config_override jsonb,
|
|
status varchar(16) NOT NULL DEFAULT 'draft',
|
|
sort_order integer NOT NULL DEFAULT 0,
|
|
created_at timestamptz(3) NOT NULL DEFAULT now(),
|
|
updated_at timestamptz(3) NOT NULL DEFAULT now(),
|
|
CONSTRAINT worksheet_resources_worksheet_id_fkey FOREIGN KEY (worksheet_id)
|
|
REFERENCES public.worksheets (id) ON UPDATE RESTRICT ON DELETE RESTRICT,
|
|
CONSTRAINT worksheet_resources_preview_asset_id_fkey FOREIGN KEY (preview_asset_id)
|
|
REFERENCES public.assets (id) ON UPDATE RESTRICT ON DELETE RESTRICT,
|
|
CONSTRAINT worksheet_resources_status_check CHECK (status IN ('draft', 'active', 'hidden')),
|
|
CONSTRAINT worksheet_resources_config_object_check CHECK (
|
|
config_override IS NULL OR jsonb_typeof(config_override) = 'object'
|
|
)
|
|
);
|
|
|
|
CREATE INDEX worksheet_resources_worksheet_status_sort_idx
|
|
ON public.worksheet_resources (worksheet_id, status, sort_order, id);
|
|
CREATE INDEX worksheet_resources_preview_asset_idx ON public.worksheet_resources (preview_asset_id);
|
|
|
|
-- 首期仅支持线框和参考图；允许多个同用途文件，排序并列时按 id 确定顺序。
|
|
-- 文件格式由 assets 描述；是否打印、如何排列由 renderer 决定。
|
|
CREATE TABLE public.worksheet_resource_assets (
|
|
id varchar(128) COLLATE "C" PRIMARY KEY,
|
|
worksheet_resource_id varchar(128) COLLATE "C" NOT NULL,
|
|
asset_id varchar(128) COLLATE "C" NOT NULL,
|
|
role varchar(16) NOT NULL,
|
|
sort_order integer NOT NULL DEFAULT 0,
|
|
CONSTRAINT worksheet_resource_assets_resource_id_fkey FOREIGN KEY (worksheet_resource_id)
|
|
REFERENCES public.worksheet_resources (id) ON UPDATE RESTRICT ON DELETE RESTRICT,
|
|
CONSTRAINT worksheet_resource_assets_asset_id_fkey FOREIGN KEY (asset_id)
|
|
REFERENCES public.assets (id) ON UPDATE RESTRICT ON DELETE RESTRICT,
|
|
CONSTRAINT worksheet_resource_assets_role_check CHECK (role IN ('outline', 'reference')),
|
|
CONSTRAINT worksheet_resource_assets_resource_role_asset_key
|
|
UNIQUE (worksheet_resource_id, role, asset_id)
|
|
);
|
|
|
|
CREATE INDEX worksheet_resource_assets_resource_role_sort_idx
|
|
ON public.worksheet_resource_assets (worksheet_resource_id, role, sort_order, id);
|
|
CREATE INDEX worksheet_resource_assets_asset_idx ON public.worksheet_resource_assets (asset_id);
|
|
|
|
-- 与 worksheet_id 一样保留逻辑关联，允许缺失内容的历史仍可读取。
|
|
ALTER TABLE public.download_logs
|
|
ADD COLUMN worksheet_resource_id varchar(128) COLLATE "C";
|
|
|
|
COMMENT ON COLUMN public.worksheets.generation_mode IS 'procedural 算法生成；resource 完整内容资源驱动；旧数据默认 procedural，导入逐项核对';
|
|
COMMENT ON COLUMN public.worksheets.renderer_key IS '客户端绘制实现标识；多题型可共用；NULL 仅供旧版 ID/path 映射兼容，新功能发布必须明确';
|
|
COMMENT ON COLUMN public.worksheets.render_config IS '共用绘制参数；仅允许已定义参数，不存执行代码或密钥';
|
|
COMMENT ON TABLE public.worksheet_resources IS 'worksheet 下可选择的完整内容；独立预览及轻量配置覆盖';
|
|
COMMENT ON COLUMN public.worksheet_resources.preview_asset_id IS '内容列表预览图；与绘制参考图用途独立，允许引用同一资产';
|
|
COMMENT ON COLUMN public.worksheet_resources.config_override IS '可选参数覆盖；应用按 renderer 白名单校验，不支持任意深度合并';
|
|
COMMENT ON TABLE public.worksheet_resource_assets IS '完整内容的文件输入；outline 线框，reference 对照图；不保存文件级配置';
|
|
COMMENT ON COLUMN public.worksheet_resource_assets.role IS '业务用途，不是文件格式；用途对应的文件数量、类型和是否打印由 renderer 校验';
|
|
COMMENT ON COLUMN public.worksheet_resource_assets.sort_order IS '同用途文件的处理顺序，并列按 id 排序；不等于通用图层 z-index';
|
|
COMMENT ON COLUMN public.download_logs.worksheet_resource_id IS '可空资源条目逻辑关联；历史和算法生成可空；新写入校验属于同一 worksheet';
|
|
|
|
COMMIT;
|