1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
| CREATE TABLE IF NOT EXISTS users (
username TEXT PRIMARY KEY,
password TEXT NOT NULL,
created_at INTEGER NOT NULL DEFAULT (strftime('%s', 'now'))
);
CREATE TABLE IF NOT EXISTS play_records (
id INTEGER PRIMARY KEY AUTOINCREMENT,
username TEXT NOT NULL,
key TEXT NOT NULL,
title TEXT NOT NULL,
source_name TEXT NOT NULL,
cover TEXT NOT NULL,
year TEXT NOT NULL,
index_episode INTEGER NOT NULL,
total_episodes INTEGER NOT NULL,
play_time INTEGER NOT NULL,
total_time INTEGER NOT NULL,
save_time INTEGER NOT NULL,
search_title TEXT,
UNIQUE(username, key)
);
CREATE TABLE IF NOT EXISTS favorites (
id INTEGER PRIMARY KEY AUTOINCREMENT,
username TEXT NOT NULL,
key TEXT NOT NULL,
title TEXT NOT NULL,
source_name TEXT NOT NULL,
cover TEXT NOT NULL,
year TEXT NOT NULL,
total_episodes INTEGER NOT NULL,
save_time INTEGER NOT NULL,
UNIQUE(username, key)
);
CREATE TABLE IF NOT EXISTS search_history (
id INTEGER PRIMARY KEY AUTOINCREMENT,
username TEXT NOT NULL,
keyword TEXT NOT NULL,
created_at INTEGER NOT NULL DEFAULT (strftime('%s', 'now')),
UNIQUE(username, keyword)
);
CREATE TABLE IF NOT EXISTS admin_config (
id INTEGER PRIMARY KEY DEFAULT 1,
config TEXT NOT NULL,
updated_at INTEGER NOT NULL DEFAULT (strftime('%s', 'now'))
);
CREATE TABLE IF NOT EXISTS skip_configs (
id INTEGER PRIMARY KEY AUTOINCREMENT,
username TEXT NOT NULL,
source TEXT NOT NULL,
id_video TEXT NOT NULL,
enable INTEGER NOT NULL DEFAULT 0,
intro_time INTEGER NOT NULL DEFAULT 0,
outro_time INTEGER NOT NULL DEFAULT 0,
UNIQUE(username, source, id_video)
);
-- 基本索引
CREATE INDEX IF NOT EXISTS idx_play_records_username ON play_records(username);
CREATE INDEX IF NOT EXISTS idx_favorites_username ON favorites(username);
CREATE INDEX IF NOT EXISTS idx_search_history_username ON search_history(username);
-- 复合索引优化查询性能
-- 播放记录：用户名+键值的复合索引，用于快速查找特定记录
CREATE INDEX IF NOT EXISTS idx_play_records_username_key ON play_records(username, key);
-- 播放记录：用户名+保存时间的复合索引，用于按时间排序的查询
CREATE INDEX IF NOT EXISTS idx_play_records_username_save_time ON play_records(username, save_time DESC);
-- 收藏：用户名+键值的复合索引，用于快速查找特定收藏
CREATE INDEX IF NOT EXISTS idx_favorites_username_key ON favorites(username, key);
-- 收藏：用户名+保存时间的复合索引，用于按时间排序的查询
CREATE INDEX IF NOT EXISTS idx_favorites_username_save_time ON favorites(username, save_time DESC);
-- 搜索历史：用户名+关键词的复合索引，用于快速查找/删除特定搜索记录
CREATE INDEX IF NOT EXISTS idx_search_history_username_keyword ON search_history(username, keyword);
-- 搜索历史：用户名+创建时间的复合索引，用于按时间排序的查询
CREATE INDEX IF NOT EXISTS idx_search_history_username_created_at ON search_history(username, created_at DESC);
-- 跳过片头片尾配置：用户名+源+视频ID的复合索引，用于快速查找特定配置
CREATE INDEX IF NOT EXISTS idx_skip_configs_username_source_id ON skip_configs(username, source, id_video);
-- 搜索历史清理查询的优化索引
CREATE INDEX IF NOT EXISTS idx_search_history_username_id_created_at ON search_history(username, id, created_at DESC);
|
