SET @db_name = DATABASE();
|
|
-- ----------------------------
|
-- APP 我的课程相关表结构升级
|
-- 目标：
|
-- 1. 养生课程播放记录表新增续播位置字段 last_play_position_seconds
|
-- 2. 新增养生课程下载记录表 tcm_health_course_download_record
|
-- ----------------------------
|
|
SET @play_record_table_exists = (
|
SELECT COUNT(1)
|
FROM information_schema.TABLES
|
WHERE TABLE_SCHEMA = @db_name
|
AND TABLE_NAME = 'tcm_health_course_play_record'
|
);
|
|
SET @sql_assert_play_record_table = IF(
|
@play_record_table_exists = 0,
|
'SELECT ''skip: tcm_health_course_play_record not exists''',
|
'SELECT 1'
|
);
|
PREPARE stmt_assert_play_record_table FROM @sql_assert_play_record_table;
|
EXECUTE stmt_assert_play_record_table;
|
DEALLOCATE PREPARE stmt_assert_play_record_table;
|
|
SET @has_last_play_position_seconds = (
|
SELECT COUNT(1)
|
FROM information_schema.COLUMNS
|
WHERE TABLE_SCHEMA = @db_name
|
AND TABLE_NAME = 'tcm_health_course_play_record'
|
AND COLUMN_NAME = 'last_play_position_seconds'
|
);
|
|
SET @sql_add_last_play_position_seconds = IF(
|
@play_record_table_exists = 1 AND @has_last_play_position_seconds = 0,
|
'ALTER TABLE `tcm_health_course_play_record`
|
ADD COLUMN `last_play_position_seconds` INT NOT NULL DEFAULT 0 COMMENT ''本次结束时播放位置（秒）'' AFTER `is_completed`',
|
'SELECT 1'
|
);
|
PREPARE stmt_add_last_play_position_seconds FROM @sql_add_last_play_position_seconds;
|
EXECUTE stmt_add_last_play_position_seconds;
|
DEALLOCATE PREPARE stmt_add_last_play_position_seconds;
|
|
SET @sql_fill_last_play_position_seconds = IF(
|
@play_record_table_exists = 1,
|
'UPDATE `tcm_health_course_play_record`
|
SET `last_play_position_seconds` = 0
|
WHERE `last_play_position_seconds` IS NULL',
|
'SELECT 1'
|
);
|
PREPARE stmt_fill_last_play_position_seconds FROM @sql_fill_last_play_position_seconds;
|
EXECUTE stmt_fill_last_play_position_seconds;
|
DEALLOCATE PREPARE stmt_fill_last_play_position_seconds;
|
|
SET @download_record_table_exists = (
|
SELECT COUNT(1)
|
FROM information_schema.TABLES
|
WHERE TABLE_SCHEMA = @db_name
|
AND TABLE_NAME = 'tcm_health_course_download_record'
|
);
|
|
SET @sql_create_download_record_table = IF(
|
@download_record_table_exists = 0,
|
'CREATE TABLE `tcm_health_course_download_record` (
|
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
|
`app_user_id` BIGINT UNSIGNED NOT NULL COMMENT ''用户ID'',
|
`course_id` BIGINT UNSIGNED NOT NULL COMMENT ''养生课程ID'',
|
`course_video_id` BIGINT UNSIGNED NOT NULL COMMENT ''视频小节ID'',
|
`course_title_snapshot` VARCHAR(128) NOT NULL DEFAULT '''' COMMENT ''课程标题快照'',
|
`chapter_name_snapshot` VARCHAR(128) NOT NULL DEFAULT '''' COMMENT ''章节标题快照'',
|
`cover_img_snapshot` VARCHAR(255) NOT NULL DEFAULT '''' COMMENT ''封面图快照'',
|
`video_url_snapshot` VARCHAR(512) NOT NULL DEFAULT '''' COMMENT ''视频地址快照'',
|
`duration_seconds_snapshot` INT NOT NULL DEFAULT 0 COMMENT ''视频时长快照（秒）'',
|
`deleted` TINYINT(1) NOT NULL DEFAULT 0,
|
`created_by` VARCHAR(64) NOT NULL DEFAULT ''系统初始化'' COMMENT ''创建人'',
|
`updated_by` VARCHAR(64) NOT NULL DEFAULT ''系统初始化'' COMMENT ''更新人'',
|
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
`updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
PRIMARY KEY (`id`),
|
KEY `idx_health_course_download_user_deleted_created` (`app_user_id`, `deleted`, `created_at`),
|
KEY `idx_health_course_download_user_video_deleted` (`app_user_id`, `course_video_id`, `deleted`)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT=''养生课程下载记录表''',
|
'SELECT 1'
|
);
|
PREPARE stmt_create_download_record_table FROM @sql_create_download_record_table;
|
EXECUTE stmt_create_download_record_table;
|
DEALLOCATE PREPARE stmt_create_download_record_table;
|
