jhao
e5ba097b79
- 新增相册系统表结构（album、album_photo）支持照片分组管理 - 实现反应系统表（reaction）扩展点赞功能，支持多种反应类型 - 扩展评论系统表（comment）支持多实体类型评论和嵌套回复 - 新增用户偏好设置表（user_preferences）管理主题、布局等个性化配置 - 新增用户个人资料扩展表（user_profile、user_custom_field）支持自定义字段 - 新增智能收藏集表（smart_collection）支持按日期、位置、人物自动分类 - 新增离线同步变更记录表（offline_change_record）支持离线数据同步 - 新增用户统计表（user_statistics）追踪用户活跃度和内容统计数据
238 lines
12 KiB
SQL
238 lines
12 KiB
SQL
-- =====================================================
|
||
-- 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;
|
||
*/
|