packServer/backend/database/migrations/20261109230000-doc-two-level-structure.js
liufumingren b82b07b84f feat(doc): 栏目内容两级结构与悬浮目录
Co-authored-by: Copilot <223556219+Copilot@users.noreply.github.com>
2026-11-10 04:22:52 +08:00

137 lines
8.2 KiB
JavaScript
Raw Permalink Blame History

This file contains ambiguous Unicode characters

This file contains Unicode characters that might be confused with other characters. If you think that this is intentional, you can safely ignore this warning. Use the Escape button to reveal them.

'use strict';
/**
* 文档站层级从「分类 → 栏目 → 内容页」收紧成两级:**栏目 → 内容页**
*
* - 原来的「分类」整表升级成「栏目」:`doc_categories` 改名 `doc_sections`,
* `code` / `name` / `description` / `sort` / `status` 全部保留,页面地址 `/doc/<code>/<slug>.html` 不变;
* - 原来的二级「栏目」表删掉,底下的内容页直接挂到顶层(`section_id` 改指原 `category_id`);
* - `doc_articles.category_id` 不再需要(栏目就是顶层),一并删除。
*
* 内容页一条不丢:只是从「分类 → 栏目 → 文章」压平成「栏目 → 文章」。
*/
/** 索引存在才删(生产环境可能有人手工调整过) */
async function removeIndexIfExists(queryInterface, table, name) {
const [rows] = await queryInterface.sequelize.query(
'SELECT 1 FROM information_schema.statistics WHERE table_schema = DATABASE() AND table_name = ? AND index_name = ? LIMIT 1',
{ replacements: [ table, name ] },
);
if (rows.length) await queryInterface.removeIndex(table, name);
}
module.exports = {
async up(queryInterface) {
// 1. 内容页改挂在顶层上(原分类 id),随后二级栏目表就没用了
await queryInterface.sequelize.query('UPDATE doc_articles SET section_id = category_id');
await removeIndexIfExists(queryInterface, 'doc_articles', 'idx_doc_articles_tree');
await removeIndexIfExists(queryInterface, 'doc_articles', 'idx_doc_articles_slug');
await queryInterface.removeColumn('doc_articles', 'category_id');
await queryInterface.dropTable('doc_sections');
// 2. 原「分类」表改名成「栏目」表(文章的 section_id 已经指过来了)
await removeIndexIfExists(queryInterface, 'doc_categories', 'idx_doc_categories_status_sort');
await queryInterface.renameTable('doc_categories', 'doc_sections');
// 3. 表 / 字段注释跟着改语义(只改文案,不动类型与索引)
await queryInterface.sequelize.query("ALTER TABLE `doc_sections` COMMENT = '文档栏目表'");
await queryInterface.sequelize.query(
"ALTER TABLE `doc_sections` MODIFY COLUMN `name` VARCHAR(64) NOT NULL COMMENT '栏目名称(顶部 tab 文案)', MODIFY COLUMN `description` VARCHAR(255) NULL COMMENT '栏目说明(可选)'",
);
await queryInterface.sequelize.query(
"ALTER TABLE `doc_articles` MODIFY COLUMN `section_id` BIGINT UNSIGNED NOT NULL COMMENT '所属栏目 doc_sections.id'",
);
// 4. 索引按新层级重建
await queryInterface.addIndex('doc_sections', [ 'status', 'sort' ], { name: 'idx_doc_sections_status_sort' });
await queryInterface.addIndex('doc_articles', [ 'section_id', 'sort' ], { name: 'idx_doc_articles_section_sort' });
await queryInterface.addIndex('doc_articles', [ 'section_id', 'slug' ], { name: 'idx_doc_articles_slug' });
// 5. 演示内容跟着新文案走(只改还有旧措辞的那一页)
await queryInterface.sequelize.query(
`UPDATE doc_articles
SET content = REPLACE(
REPLACE(
REPLACE(content,
'- 顶部 tab 是「分类」,点一下就能切换;', '- 顶部 tab 是「栏目」,点一下就能切换;'),
'- 左侧是分类下的「栏目 → 内容页」;', '- 左侧直接列出当前栏目的内容页;'),
'在「文档管理」里新建分类 / 栏目 / 内容页;', '在「文档管理」里新建栏目,再往栏目里加内容页;')
WHERE slug = 'welcome' AND deleted_at IS NULL`,
);
},
async down(queryInterface, Sequelize) {
const { BIGINT, STRING, INTEGER, TINYINT, DATE } = Sequelize;
// ---- 先按新结构把数据复制回「分类」,再重建二级栏目 ----
await queryInterface.addColumn('doc_articles', 'category_id', {
type: BIGINT.UNSIGNED, allowNull: true, comment: '所属分类(冗余,列表用)',
});
await queryInterface.sequelize.query('UPDATE doc_articles SET category_id = section_id');
await queryInterface.createTable('doc_categories', {
id: { type: BIGINT.UNSIGNED, primaryKey: true, autoIncrement: true, allowNull: false },
code: { type: STRING(32), allowNull: false, unique: true, comment: 'URL 片段(/doc/<code>/)' },
name: { type: STRING(64), allowNull: false, comment: '分类名称(tab 文案)' },
description: { type: STRING(255), allowNull: true, comment: '分类说明(可选)' },
sort: { type: INTEGER.UNSIGNED, allowNull: false, defaultValue: 0, comment: '排序(小的在前)' },
status: { type: TINYINT.UNSIGNED, allowNull: false, defaultValue: 1, comment: '状态:1 展示 0 隐藏' },
created_at: { type: DATE, allowNull: false, defaultValue: Sequelize.literal('CURRENT_TIMESTAMP') },
updated_at: { type: DATE, allowNull: false, defaultValue: Sequelize.literal('CURRENT_TIMESTAMP') },
deleted_at: { type: DATE, allowNull: true },
}, { comment: '文档分类表' });
await queryInterface.sequelize.query(
`INSERT INTO doc_categories (id, code, name, description, sort, status, created_at, updated_at, deleted_at)
SELECT id, code, name, description, sort, status, created_at, updated_at, deleted_at FROM doc_sections`,
);
await queryInterface.dropTable('doc_sections');
await queryInterface.createTable('doc_sections', {
id: { type: BIGINT.UNSIGNED, primaryKey: true, autoIncrement: true, allowNull: false },
category_id: { type: BIGINT.UNSIGNED, allowNull: false, comment: '所属分类 doc_categories.id' },
name: { type: STRING(64), allowNull: false, comment: '栏目名称(侧边栏分组标题)' },
sort: { type: INTEGER.UNSIGNED, allowNull: false, defaultValue: 0, comment: '排序(小的在前)' },
status: { type: TINYINT.UNSIGNED, allowNull: false, defaultValue: 1, comment: '状态:1 展示 0 隐藏' },
created_at: { type: DATE, allowNull: false, defaultValue: Sequelize.literal('CURRENT_TIMESTAMP') },
updated_at: { type: DATE, allowNull: false, defaultValue: Sequelize.literal('CURRENT_TIMESTAMP') },
deleted_at: { type: DATE, allowNull: true },
}, { comment: '文档栏目表' });
// 每个分类补一个同名栏目,内容页都挂上去
await queryInterface.sequelize.query(
`INSERT INTO doc_sections (category_id, name, sort, status, created_at, updated_at)
SELECT id, name, 10, status, created_at, updated_at FROM doc_categories`,
);
await queryInterface.sequelize.query(
`UPDATE doc_articles a
JOIN doc_sections s ON s.category_id = a.category_id
SET a.section_id = s.id`,
);
await queryInterface.changeColumn('doc_articles', 'category_id', {
type: BIGINT.UNSIGNED, allowNull: false, comment: '所属分类(冗余,列表用)',
});
// ---- 索引恢复成三级结构 ----
await removeIndexIfExists(queryInterface, 'doc_articles', 'idx_doc_articles_section_sort');
await removeIndexIfExists(queryInterface, 'doc_articles', 'idx_doc_articles_slug');
await removeIndexIfExists(queryInterface, 'doc_sections', 'idx_doc_sections_status_sort');
await queryInterface.addIndex('doc_categories', [ 'status', 'sort' ], { name: 'idx_doc_categories_status_sort' });
await queryInterface.addIndex('doc_sections', [ 'category_id', 'sort' ], { name: 'idx_doc_sections_category_sort' });
await queryInterface.addIndex('doc_articles', [ 'category_id', 'section_id', 'sort' ], { name: 'idx_doc_articles_tree' });
await queryInterface.addIndex('doc_articles', [ 'category_id', 'slug' ], { name: 'idx_doc_articles_slug' });
await queryInterface.sequelize.query(
`UPDATE doc_articles
SET content = REPLACE(
REPLACE(
REPLACE(content,
'- 顶部 tab 是「栏目」,点一下就能切换;', '- 顶部 tab 是「分类」,点一下就能切换;'),
'- 左侧直接列出当前栏目的内容页;', '- 左侧是分类下的「栏目 → 内容页」;'),
'在「文档管理」里新建栏目,再往栏目里加内容页;', '在「文档管理」里新建分类 / 栏目 / 内容页;')
WHERE slug = 'welcome' AND deleted_at IS NULL`,
);
},
};