katelya
db08179eb0
文档更新: - 更新README中成人内容过滤部分，添加Cloudflare Pages配置要求 - 新增CLOUDFLARE_PAGES_ADULT_FILTER.md详细配置指南 - 更新D1_MIGRATION.md，修正user_settings表结构 数据库优化: - 修复scripts/d1-init.sql，添加缺失的user_settings表 - 更新表结构以匹配当前实现 - 添加必要的索引优化查询性能 问题修复: - 解决Cloudflare Pages部署时'获取用户设置失败'错误 - 明确说明不同部署平台的存储类型要求 - 提供详细的故障排除指南
128 lines
4.1 KiB
SQL
128 lines
4.1 KiB
SQL
-- D1 数据库初始化脚本
|
|
-- 用于创建 KatelyaTV 所需的数据表
|
|
|
|
-- 用户表
|
|
CREATE TABLE IF NOT EXISTS users (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
username TEXT UNIQUE NOT NULL,
|
|
password TEXT NOT NULL,
|
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
-- 播放记录表
|
|
CREATE TABLE IF NOT EXISTS play_records (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
user_id INTEGER NOT NULL,
|
|
record_key TEXT NOT NULL,
|
|
video_url TEXT,
|
|
current_time REAL DEFAULT 0,
|
|
duration REAL DEFAULT 0,
|
|
episode_index INTEGER DEFAULT 0,
|
|
episode_url TEXT,
|
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
|
|
UNIQUE (user_id, record_key)
|
|
);
|
|
|
|
-- 收藏表
|
|
CREATE TABLE IF NOT EXISTS favorites (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
user_id INTEGER NOT NULL,
|
|
favorite_key TEXT NOT NULL,
|
|
title TEXT,
|
|
cover_url TEXT,
|
|
rating REAL,
|
|
year TEXT,
|
|
area TEXT,
|
|
category TEXT,
|
|
actors TEXT,
|
|
director TEXT,
|
|
description TEXT,
|
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
|
|
UNIQUE (user_id, favorite_key)
|
|
);
|
|
|
|
-- 搜索历史表
|
|
CREATE TABLE IF NOT EXISTS search_history (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
user_id INTEGER NOT NULL,
|
|
keyword TEXT NOT NULL,
|
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
|
|
);
|
|
|
|
-- 跳过配置表
|
|
CREATE TABLE IF NOT EXISTS skip_configs (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
user_id INTEGER NOT NULL,
|
|
config_key TEXT NOT NULL,
|
|
start_time INTEGER DEFAULT 0,
|
|
end_time INTEGER DEFAULT 0,
|
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
|
|
UNIQUE (user_id, config_key)
|
|
);
|
|
|
|
-- 用户设置表
|
|
CREATE TABLE IF NOT EXISTS user_settings (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
user_id INTEGER NOT NULL,
|
|
username TEXT NOT NULL,
|
|
filter_adult_content BOOLEAN DEFAULT 1,
|
|
theme TEXT DEFAULT 'auto',
|
|
language TEXT DEFAULT 'zh-CN',
|
|
auto_play BOOLEAN DEFAULT 1,
|
|
video_quality TEXT DEFAULT 'auto',
|
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
|
|
UNIQUE (user_id, username)
|
|
);
|
|
|
|
-- 管理员配置表
|
|
CREATE TABLE IF NOT EXISTS admin_configs (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
config_key TEXT UNIQUE NOT NULL,
|
|
config_value TEXT,
|
|
description TEXT,
|
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
-- 插入默认管理员配置
|
|
INSERT OR IGNORE INTO admin_configs (config_key, config_value, description) VALUES
|
|
('site_name', 'KatelyaTV', '站点名称'),
|
|
('site_description', '高性能影视播放平台', '站点描述'),
|
|
('enable_register', 'true', '是否允许用户注册'),
|
|
('max_users', '100', '最大用户数量'),
|
|
('cache_ttl', '3600', '缓存时间（秒）');
|
|
|
|
-- 创建索引以提高查询性能
|
|
CREATE INDEX IF NOT EXISTS idx_play_records_user_id ON play_records(user_id);
|
|
CREATE INDEX IF NOT EXISTS idx_play_records_record_key ON play_records(record_key);
|
|
CREATE INDEX IF NOT EXISTS idx_favorites_user_id ON favorites(user_id);
|
|
CREATE INDEX IF NOT EXISTS idx_search_history_user_id ON search_history(user_id);
|
|
CREATE INDEX IF NOT EXISTS idx_skip_configs_user_id ON skip_configs(user_id);
|
|
CREATE INDEX IF NOT EXISTS idx_user_settings_user_id ON user_settings(user_id);
|
|
CREATE INDEX IF NOT EXISTS idx_user_settings_username ON user_settings(username);
|
|
|
|
-- 创建视图以简化查询
|
|
CREATE VIEW IF NOT EXISTS user_stats AS
|
|
SELECT
|
|
u.id,
|
|
u.username,
|
|
COUNT(DISTINCT pr.id) as play_count,
|
|
COUNT(DISTINCT f.id) as favorite_count,
|
|
COUNT(DISTINCT sh.id) as search_count,
|
|
u.created_at
|
|
FROM users u
|
|
LEFT JOIN play_records pr ON u.id = pr.user_id
|
|
LEFT JOIN favorites f ON u.id = f.user_id
|
|
LEFT JOIN search_history sh ON u.id = sh.user_id
|
|
GROUP BY u.id, u.username, u.created_at;
|