128 lines
6.8 KiB
SQL
128 lines
6.8 KiB
SQL
-- ============================================================
|
||
-- 视频模块 + 作品演示视频 数据库迁移脚本
|
||
-- 文件：server/scripts/migrate_video_module.sql
|
||
-- 说明：可重复执行（幂等），适用于已有 nl_blog 数据库的增量升级
|
||
-- 用法：mysql -u root -p your_database < scripts/migrate_video_module.sql
|
||
-- ============================================================
|
||
|
||
SET NAMES utf8mb4;
|
||
SET FOREIGN_KEY_CHECKS = 0;
|
||
|
||
-- ----------------------------
|
||
-- 1. 视频分类表
|
||
-- ----------------------------
|
||
CREATE TABLE IF NOT EXISTS `video_categories` (
|
||
`id` int UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '分类ID',
|
||
`name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '分类名称',
|
||
`slug` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'URL标识',
|
||
`description` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL COMMENT '分类描述',
|
||
`sort_order` int UNSIGNED NOT NULL DEFAULT 0 COMMENT '排序（越小越靠前）',
|
||
`deleted_at` bigint NOT NULL DEFAULT 0 COMMENT '软删除时间戳',
|
||
`created_at` bigint NOT NULL DEFAULT 0 COMMENT '创建时间（Unix秒）',
|
||
`updated_at` bigint NOT NULL DEFAULT 0 COMMENT '更新时间（Unix秒）',
|
||
PRIMARY KEY (`id`) USING BTREE,
|
||
UNIQUE INDEX `idx_slug`(`slug` ASC) USING BTREE
|
||
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci COMMENT = '视频分类表' ROW_FORMAT = DYNAMIC;
|
||
|
||
-- ----------------------------
|
||
-- 2. 视频专辑表
|
||
-- ----------------------------
|
||
CREATE TABLE IF NOT EXISTS `video_albums` (
|
||
`id` int UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '专辑ID',
|
||
`name` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '专辑名称',
|
||
`description` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL COMMENT '专辑描述',
|
||
`cover` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '专辑封面URL',
|
||
`category_id` int UNSIGNED NOT NULL DEFAULT 0 COMMENT '所属视频分类ID',
|
||
`is_active` tinyint(1) NOT NULL DEFAULT 1 COMMENT '是否启用（0否 1是）',
|
||
`sort_order` int UNSIGNED NOT NULL DEFAULT 0 COMMENT '排序（越小越靠前）',
|
||
`deleted_at` bigint NOT NULL DEFAULT 0 COMMENT '软删除时间戳',
|
||
`created_at` bigint NOT NULL DEFAULT 0 COMMENT '创建时间（Unix秒）',
|
||
`updated_at` bigint NOT NULL DEFAULT 0 COMMENT '更新时间（Unix秒）',
|
||
PRIMARY KEY (`id`) USING BTREE,
|
||
INDEX `idx_category_id`(`category_id` ASC) USING BTREE
|
||
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci COMMENT = '视频专辑表' ROW_FORMAT = DYNAMIC;
|
||
|
||
-- ----------------------------
|
||
-- 3. 视频表
|
||
-- ----------------------------
|
||
CREATE TABLE IF NOT EXISTS `videos` (
|
||
`id` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '视频唯一标识',
|
||
`title` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '视频标题',
|
||
`description` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL COMMENT '视频描述',
|
||
`video_url` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '视频文件URL',
|
||
`cover` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '封面图URL',
|
||
`poster` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '播放器封面URL',
|
||
`category_id` int UNSIGNED NOT NULL DEFAULT 0 COMMENT '所属视频分类ID',
|
||
`duration` int NOT NULL DEFAULT 0 COMMENT '时长（秒）',
|
||
`is_published` tinyint(1) NOT NULL DEFAULT 1 COMMENT '是否发布（0草稿 1已发布）',
|
||
`deleted_at` bigint NOT NULL DEFAULT 0 COMMENT '软删除时间戳',
|
||
`created_at` bigint NOT NULL DEFAULT 0 COMMENT '创建时间（Unix秒）',
|
||
`updated_at` bigint NOT NULL DEFAULT 0 COMMENT '更新时间（Unix秒）',
|
||
PRIMARY KEY (`id`) USING BTREE,
|
||
INDEX `idx_category_id`(`category_id` ASC) USING BTREE
|
||
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci COMMENT = '视频表' ROW_FORMAT = DYNAMIC;
|
||
|
||
-- ----------------------------
|
||
-- 4. 专辑-视频关联表
|
||
-- ----------------------------
|
||
CREATE TABLE IF NOT EXISTS `album_videos` (
|
||
`album_id` int UNSIGNED NOT NULL COMMENT '专辑ID',
|
||
`video_id` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '视频ID',
|
||
`sort_order` int UNSIGNED NOT NULL DEFAULT 0 COMMENT '专辑内排序',
|
||
`created_at` bigint NOT NULL DEFAULT 0 COMMENT '创建时间（Unix秒）',
|
||
PRIMARY KEY (`album_id`, `video_id`) USING BTREE,
|
||
INDEX `idx_video_id`(`video_id` ASC) USING BTREE
|
||
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci COMMENT = '专辑视频关联表' ROW_FORMAT = DYNAMIC;
|
||
|
||
-- ----------------------------
|
||
-- 5. 作品表新增演示视频字段（幂等添加）
|
||
-- ----------------------------
|
||
|
||
-- 5.1 hero_video：独立上传的视频 URL
|
||
SET @col_exists = (
|
||
SELECT COUNT(*) FROM information_schema.COLUMNS
|
||
WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'works' AND COLUMN_NAME = 'hero_video'
|
||
);
|
||
SET @sql = IF(@col_exists = 0,
|
||
'ALTER TABLE `works` ADD COLUMN `hero_video` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT ''演示视频URL'' AFTER `hero_img`',
|
||
'SELECT ''works.hero_video already exists'' AS info'
|
||
);
|
||
PREPARE stmt FROM @sql;
|
||
EXECUTE stmt;
|
||
DEALLOCATE PREPARE stmt;
|
||
|
||
-- 5.2 video_id：关联 videos 表 ID（从视频库选择时使用）
|
||
SET @col_exists = (
|
||
SELECT COUNT(*) FROM information_schema.COLUMNS
|
||
WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'works' AND COLUMN_NAME = 'video_id'
|
||
);
|
||
SET @sql = IF(@col_exists = 0,
|
||
'ALTER TABLE `works` ADD COLUMN `video_id` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT ''关联视频库ID'' AFTER `hero_video`',
|
||
'SELECT ''works.video_id already exists'' AS info'
|
||
);
|
||
PREPARE stmt FROM @sql;
|
||
EXECUTE stmt;
|
||
DEALLOCATE PREPARE stmt;
|
||
|
||
-- ----------------------------
|
||
-- 6. 视频模块后台权限（videos:read/create/update/delete）
|
||
-- ----------------------------
|
||
INSERT IGNORE INTO `permissions` (`name`, `resource`, `action`, `deleted_at`, `created_at`, `updated_at`) VALUES
|
||
('videos:read', 'videos', 'read', 0, UNIX_TIMESTAMP(), UNIX_TIMESTAMP()),
|
||
('videos:create', 'videos', 'create', 0, UNIX_TIMESTAMP(), UNIX_TIMESTAMP()),
|
||
('videos:update', 'videos', 'update', 0, UNIX_TIMESTAMP(), UNIX_TIMESTAMP()),
|
||
('videos:delete', 'videos', 'delete', 0, UNIX_TIMESTAMP(), UNIX_TIMESTAMP());
|
||
|
||
-- 为管理员角色（role_id = 1）授予 videos 权限
|
||
INSERT IGNORE INTO `role_permissions` (`role_id`, `permission_id`)
|
||
SELECT 1, p.id FROM `permissions` p
|
||
WHERE p.resource = 'videos' AND p.deleted_at = 0
|
||
AND NOT EXISTS (
|
||
SELECT 1 FROM `role_permissions` rp
|
||
WHERE rp.role_id = 1 AND rp.permission_id = p.id
|
||
);
|
||
|
||
SET FOREIGN_KEY_CHECKS = 1;
|
||
|
||
SELECT 'migrate_video_module.sql executed successfully' AS result;
|