51 lines
1.7 KiB
SQL
51 lines
1.7 KiB
SQL
-- Author: fruit.com.cn
|
|
-- 推荐榜：运营分类增加稳定语义、展示类型和版本，关联关系增加明确位置
|
|
|
|
ALTER TABLE drama_classification
|
|
ADD COLUMN IF NOT EXISTS code VARCHAR(64),
|
|
ADD COLUMN IF NOT EXISTS layout_type VARCHAR(16) NOT NULL DEFAULT 'GRID',
|
|
ADD COLUMN IF NOT EXISTS revision BIGINT NOT NULL DEFAULT 1;
|
|
|
|
UPDATE drama_classification
|
|
SET code = 'CLASSIFICATION_' || id
|
|
WHERE code IS NULL OR BTRIM(code) = '';
|
|
|
|
UPDATE drama_classification
|
|
SET code = 'RECOMMEND_RANKING',
|
|
layout_type = 'RANKING'
|
|
WHERE id = (
|
|
SELECT id
|
|
FROM drama_classification
|
|
WHERE name IN ('推荐榜', '榜单')
|
|
ORDER BY sort ASC, id ASC
|
|
LIMIT 1
|
|
);
|
|
|
|
CREATE UNIQUE INDEX IF NOT EXISTS uk_drama_classification_code
|
|
ON drama_classification (code);
|
|
|
|
ALTER TABLE drama_classification_drama
|
|
ADD COLUMN IF NOT EXISTS position INTEGER NOT NULL DEFAULT 0;
|
|
|
|
WITH ranked AS (
|
|
SELECT id,
|
|
ROW_NUMBER() OVER (
|
|
PARTITION BY classification_id
|
|
ORDER BY created_at DESC, id DESC
|
|
) AS next_position
|
|
FROM drama_classification_drama
|
|
)
|
|
UPDATE drama_classification_drama relation
|
|
SET position = ranked.next_position
|
|
FROM ranked
|
|
WHERE relation.id = ranked.id
|
|
AND relation.position = 0;
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_classification_drama_position
|
|
ON drama_classification_drama (classification_id, position, id);
|
|
|
|
COMMENT ON COLUMN drama_classification.code IS '稳定语义码，不随多语言名称变化';
|
|
COMMENT ON COLUMN drama_classification.layout_type IS '客户端布局：GRID/RANKING';
|
|
COMMENT ON COLUMN drama_classification.revision IS '内容或顺序版本，变更后递增';
|
|
COMMENT ON COLUMN drama_classification_drama.position IS '分类内展示位置，从 1 开始';
|