238 lines
12 KiB
MySQL
238 lines
12 KiB
MySQL
|
-- =====================================================
|
|||
|
|
-- Timeline 数据库迁移脚本
|
|||
|
|
-- 版本: V1.4.0
|
|||
|
|
-- 日期: 2024
|
|||
|
|
-- 描述: 个人用户增强功能 - 相册、反应、用户偏好、个人资料、统计
|
|||
|
|
-- =====================================================
|
|||
|
|
|
|||
|
|
-- -----------------------------------------------------
|
|||
|
|
-- 1. 相册系统表结构
|
|||
|
|
-- -----------------------------------------------------
|
|||
|
|
|
|||
|
|
-- 相册表
|
|||
|
|
CREATE TABLE IF NOT EXISTS album (
|
|||
|
|
id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '主键ID',
|
|||
|
|
instance_id VARCHAR(64) UNIQUE NOT NULL COMMENT '相册唯一标识',
|
|||
|
|
user_id VARCHAR(64) NOT NULL COMMENT '用户ID',
|
|||
|
|
name VARCHAR(200) NOT NULL COMMENT '相册名称',
|
|||
|
|
description TEXT COMMENT '相册描述',
|
|||
|
|
cover_photo_id VARCHAR(64) COMMENT '封面照片ID',
|
|||
|
|
photo_count INT DEFAULT 0 COMMENT '照片数量',
|
|||
|
|
create_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
|||
|
|
update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
|||
|
|
is_delete TINYINT DEFAULT 0 COMMENT '是否删除 0-否 1-是',
|
|||
|
|
INDEX idx_album_user (user_id, is_delete, create_time DESC),
|
|||
|
|
INDEX idx_album_name (name)
|
|||
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='相册表';
|
|||
|
|
|
|||
|
|
-- 相册照片关联表
|
|||
|
|
CREATE TABLE IF NOT EXISTS album_photo (
|
|||
|
|
id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '主键ID',
|
|||
|
|
album_id VARCHAR(64) NOT NULL COMMENT '相册ID',
|
|||
|
|
photo_id VARCHAR(64) NOT NULL COMMENT '照片ID（story_item_id）',
|
|||
|
|
sort_order INT DEFAULT 0 COMMENT '排序值，数值越小越靠前',
|
|||
|
|
added_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '添加时间',
|
|||
|
|
UNIQUE KEY uk_album_photo (album_id, photo_id),
|
|||
|
|
INDEX idx_album_photos (album_id, sort_order, added_time DESC),
|
|||
|
|
INDEX idx_photo_albums (photo_id, album_id)
|
|||
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='相册照片关联表';
|
|||
|
|
|
|||
|
|
-- -----------------------------------------------------
|
|||
|
|
-- 2. 反应系统表结构（扩展现有点赞系统）
|
|||
|
|
-- -----------------------------------------------------
|
|||
|
|
|
|||
|
|
-- 反应表（替代简单的点赞）
|
|||
|
|
CREATE TABLE IF NOT EXISTS reaction (
|
|||
|
|
id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '主键ID',
|
|||
|
|
entity_type VARCHAR(20) NOT NULL COMMENT '实体类型: STORY_ITEM/PHOTO',
|
|||
|
|
entity_id VARCHAR(64) NOT NULL COMMENT '实体ID',
|
|||
|
|
user_id VARCHAR(64) NOT NULL COMMENT '用户ID',
|
|||
|
|
reaction_type VARCHAR(20) NOT NULL COMMENT '反应类型: LIKE/LOVE/LAUGH/WOW/SAD',
|
|||
|
|
create_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
|||
|
|
update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
|||
|
|
UNIQUE KEY uk_entity_user (entity_type, entity_id, user_id),
|
|||
|
|
INDEX idx_reaction_entity (entity_type, entity_id, reaction_type),
|
|||
|
|
INDEX idx_reaction_user (user_id, create_time DESC)
|
|||
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='反应表';
|
|||
|
|
|
|||
|
|
-- -----------------------------------------------------
|
|||
|
|
-- 3. 评论系统扩展（支持多实体类型）
|
|||
|
|
-- -----------------------------------------------------
|
|||
|
|
|
|||
|
|
-- 通用评论表（扩展现有 story_comment）
|
|||
|
|
CREATE TABLE IF NOT EXISTS comment (
|
|||
|
|
id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '主键ID',
|
|||
|
|
instance_id VARCHAR(64) UNIQUE NOT NULL COMMENT '评论唯一标识',
|
|||
|
|
entity_type VARCHAR(20) NOT NULL COMMENT '实体类型: STORY_ITEM/PHOTO',
|
|||
|
|
entity_id VARCHAR(64) NOT NULL COMMENT '实体ID',
|
|||
|
|
user_id VARCHAR(64) NOT NULL COMMENT '评论用户ID',
|
|||
|
|
parent_id VARCHAR(64) DEFAULT NULL COMMENT '父评论ID',
|
|||
|
|
reply_to_user_id VARCHAR(64) DEFAULT NULL COMMENT '回复的用户ID',
|
|||
|
|
content TEXT NOT NULL COMMENT '评论内容',
|
|||
|
|
create_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
|||
|
|
update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
|||
|
|
is_delete TINYINT DEFAULT 0 COMMENT '是否删除 0-否 1-是',
|
|||
|
|
INDEX idx_comment_entity (entity_type, entity_id, is_delete, create_time ASC),
|
|||
|
|
INDEX idx_comment_user (user_id, create_time DESC),
|
|||
|
|
INDEX idx_comment_parent (parent_id)
|
|||
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='通用评论表';
|
|||
|
|
|
|||
|
|
-- -----------------------------------------------------
|
|||
|
|
-- 4. 用户偏好设置表
|
|||
|
|
-- -----------------------------------------------------
|
|||
|
|
|
|||
|
|
-- 用户偏好表
|
|||
|
|
CREATE TABLE IF NOT EXISTS user_preferences (
|
|||
|
|
id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '主键ID',
|
|||
|
|
user_id VARCHAR(64) UNIQUE NOT NULL COMMENT '用户ID',
|
|||
|
|
-- 主题设置
|
|||
|
|
theme_mode VARCHAR(20) DEFAULT 'auto' COMMENT '主题模式: light/dark/auto',
|
|||
|
|
color_scheme VARCHAR(50) DEFAULT 'default' COMMENT '配色方案',
|
|||
|
|
-- 布局设置
|
|||
|
|
gallery_layout VARCHAR(20) DEFAULT 'grid' COMMENT '画廊布局: grid/list',
|
|||
|
|
timeline_layout VARCHAR(20) DEFAULT 'grid' COMMENT '时间线布局: grid/list',
|
|||
|
|
album_layout VARCHAR(20) DEFAULT 'grid' COMMENT '相册布局: grid/list',
|
|||
|
|
card_size VARCHAR(20) DEFAULT 'medium' COMMENT '卡片大小: small/medium/large',
|
|||
|
|
-- 时间线显示设置
|
|||
|
|
timeline_display_mode VARCHAR(20) DEFAULT 'chronological' COMMENT '时间线显示模式: chronological/grouped/masonry',
|
|||
|
|
create_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
|||
|
|
update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
|||
|
|
INDEX idx_preferences_user (user_id)
|
|||
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户偏好设置表';
|
|||
|
|
|
|||
|
|
-- -----------------------------------------------------
|
|||
|
|
-- 5. 用户个人资料扩展表
|
|||
|
|
-- -----------------------------------------------------
|
|||
|
|
|
|||
|
|
-- 用户个人资料表
|
|||
|
|
CREATE TABLE IF NOT EXISTS user_profile (
|
|||
|
|
id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '主键ID',
|
|||
|
|
user_id VARCHAR(64) UNIQUE NOT NULL COMMENT '用户ID',
|
|||
|
|
cover_photo_url VARCHAR(500) COMMENT '封面照片URL',
|
|||
|
|
bio VARCHAR(500) COMMENT '个人简介',
|
|||
|
|
create_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
|||
|
|
update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
|||
|
|
INDEX idx_profile_user (user_id)
|
|||
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户个人资料表';
|
|||
|
|
|
|||
|
|
-- 用户自定义字段表
|
|||
|
|
CREATE TABLE IF NOT EXISTS user_custom_field (
|
|||
|
|
id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '主键ID',
|
|||
|
|
instance_id VARCHAR(64) UNIQUE NOT NULL COMMENT '字段唯一标识',
|
|||
|
|
user_id VARCHAR(64) NOT NULL COMMENT '用户ID',
|
|||
|
|
field_name VARCHAR(100) NOT NULL COMMENT '字段名称',
|
|||
|
|
field_value VARCHAR(500) NOT NULL COMMENT '字段值',
|
|||
|
|
visibility VARCHAR(20) DEFAULT 'public' COMMENT '可见性: public/private',
|
|||
|
|
sort_order INT DEFAULT 0 COMMENT '排序值',
|
|||
|
|
create_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
|||
|
|
update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
|||
|
|
INDEX idx_custom_field_user (user_id, sort_order),
|
|||
|
|
INDEX idx_custom_field_visibility (user_id, visibility)
|
|||
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户自定义字段表';
|
|||
|
|
|
|||
|
|
-- -----------------------------------------------------
|
|||
|
|
-- 6. 智能收藏集元数据表
|
|||
|
|
-- -----------------------------------------------------
|
|||
|
|
|
|||
|
|
-- 智能收藏集表
|
|||
|
|
CREATE TABLE IF NOT EXISTS smart_collection (
|
|||
|
|
id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '主键ID',
|
|||
|
|
instance_id VARCHAR(64) UNIQUE NOT NULL COMMENT '收藏集唯一标识',
|
|||
|
|
user_id VARCHAR(64) NOT NULL COMMENT '用户ID',
|
|||
|
|
collection_type VARCHAR(20) NOT NULL COMMENT '收藏集类型: DATE/LOCATION/PERSON',
|
|||
|
|
name VARCHAR(200) NOT NULL COMMENT '收藏集名称',
|
|||
|
|
criteria_json JSON NOT NULL COMMENT '筛选条件JSON',
|
|||
|
|
content_count INT DEFAULT 0 COMMENT '内容数量',
|
|||
|
|
thumbnail_url VARCHAR(500) COMMENT '缩略图URL',
|
|||
|
|
create_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
|||
|
|
update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
|||
|
|
INDEX idx_collection_user (user_id, collection_type, create_time DESC),
|
|||
|
|
INDEX idx_collection_type (collection_type)
|
|||
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='智能收藏集表';
|
|||
|
|
|
|||
|
|
-- -----------------------------------------------------
|
|||
|
|
-- 7. 离线同步变更记录表
|
|||
|
|
-- -----------------------------------------------------
|
|||
|
|
|
|||
|
|
-- 离线变更记录表
|
|||
|
|
CREATE TABLE IF NOT EXISTS offline_change_record (
|
|||
|
|
id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '主键ID',
|
|||
|
|
instance_id VARCHAR(64) UNIQUE NOT NULL COMMENT '变更记录唯一标识',
|
|||
|
|
user_id VARCHAR(64) NOT NULL COMMENT '用户ID',
|
|||
|
|
entity_type VARCHAR(20) NOT NULL COMMENT '实体类型: STORY/ALBUM/PHOTO',
|
|||
|
|
entity_id VARCHAR(64) NOT NULL COMMENT '实体ID',
|
|||
|
|
operation VARCHAR(20) NOT NULL COMMENT '操作类型: CREATE/UPDATE/DELETE',
|
|||
|
|
change_data JSON NOT NULL COMMENT '变更数据JSON',
|
|||
|
|
client_timestamp BIGINT NOT NULL COMMENT '客户端时间戳',
|
|||
|
|
synced TINYINT DEFAULT 0 COMMENT '是否已同步 0-否 1-是',
|
|||
|
|
synced_time DATETIME COMMENT '同步时间',
|
|||
|
|
conflict TINYINT DEFAULT 0 COMMENT '是否有冲突 0-否 1-是',
|
|||
|
|
error_message TEXT COMMENT '错误信息',
|
|||
|
|
create_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
|||
|
|
INDEX idx_change_user_sync (user_id, synced, create_time),
|
|||
|
|
INDEX idx_change_entity (entity_type, entity_id)
|
|||
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='离线变更记录表';
|
|||
|
|
|
|||
|
|
-- -----------------------------------------------------
|
|||
|
|
-- 8. 统计数据缓存表（扩展现有 user_stats_cache）
|
|||
|
|
-- -----------------------------------------------------
|
|||
|
|
|
|||
|
|
-- 为现有 user_stats_cache 表添加新字段
|
|||
|
|
ALTER TABLE user_stats_cache
|
|||
|
|
ADD COLUMN total_albums INT DEFAULT 0 COMMENT '总相册数',
|
|||
|
|
ADD COLUMN total_photos INT DEFAULT 0 COMMENT '总照片数',
|
|||
|
|
ADD COLUMN total_storage_bytes BIGINT DEFAULT 0 COMMENT '总存储字节数',
|
|||
|
|
ADD COLUMN monthly_uploads_json JSON COMMENT '月度上传统计JSON',
|
|||
|
|
ADD COLUMN yearly_uploads_json JSON COMMENT '年度上传统计JSON',
|
|||
|
|
ADD COLUMN storage_breakdown_json JSON COMMENT '存储分类统计JSON';
|
|||
|
|
|
|||
|
|
-- -----------------------------------------------------
|
|||
|
|
-- 9. 索引优化
|
|||
|
|
-- -----------------------------------------------------
|
|||
|
|
|
|||
|
|
-- 为 story_item 表添加照片相关索引
|
|||
|
|
CREATE INDEX IF NOT EXISTS idx_story_item_photo
|
|||
|
|
ON story_item(user_id, is_delete, create_time DESC);
|
|||
|
|
|
|||
|
|
-- 为 notification 表添加复合索引
|
|||
|
|
CREATE INDEX IF NOT EXISTS idx_notification_entity
|
|||
|
|
ON notification(related_type, related_id, create_time DESC);
|
|||
|
|
|
|||
|
|
-- -----------------------------------------------------
|
|||
|
|
-- 数据迁移说明
|
|||
|
|
-- -----------------------------------------------------
|
|||
|
|
|
|||
|
|
-- 1. 新表均为空表，无需数据迁移
|
|||
|
|
-- 2. user_stats_cache 新增字段默认值为 0 或 NULL，现有数据无需迁移
|
|||
|
|
-- 3. 建议在低峰期执行此脚本
|
|||
|
|
-- 4. Redis 缓存配置需要单独设置
|
|||
|
|
|
|||
|
|
-- -----------------------------------------------------
|
|||
|
|
-- 回滚脚本 (如需回滚，请执行以下语句)
|
|||
|
|
-- -----------------------------------------------------
|
|||
|
|
|
|||
|
|
/*
|
|||
|
|
-- 删除新增字段
|
|||
|
|
ALTER TABLE user_stats_cache DROP COLUMN total_albums;
|
|||
|
|
ALTER TABLE user_stats_cache DROP COLUMN total_photos;
|
|||
|
|
ALTER TABLE user_stats_cache DROP COLUMN total_storage_bytes;
|
|||
|
|
ALTER TABLE user_stats_cache DROP COLUMN monthly_uploads_json;
|
|||
|
|
ALTER TABLE user_stats_cache DROP COLUMN yearly_uploads_json;
|
|||
|
|
ALTER TABLE user_stats_cache DROP COLUMN storage_breakdown_json;
|
|||
|
|
|
|||
|
|
-- 删除新增索引
|
|||
|
|
DROP INDEX IF EXISTS idx_story_item_photo ON story_item;
|
|||
|
|
DROP INDEX IF EXISTS idx_notification_entity ON notification;
|
|||
|
|
|
|||
|
|
-- 删除新增表
|
|||
|
|
DROP TABLE IF EXISTS offline_change_record;
|
|||
|
|
DROP TABLE IF EXISTS smart_collection;
|
|||
|
|
DROP TABLE IF EXISTS user_custom_field;
|
|||
|
|
DROP TABLE IF EXISTS user_profile;
|
|||
|
|
DROP TABLE IF EXISTS user_preferences;
|
|||
|
|
DROP TABLE IF EXISTS comment;
|
|||
|
|
DROP TABLE IF EXISTS reaction;
|
|||
|
|
DROP TABLE IF EXISTS album_photo;
|
|||
|
|
DROP TABLE IF EXISTS album;
|
|||
|
|
*/
|