clearlove2.1
1// Package database 负责数据库连接与表结构自动迁移。
2// 同时兼容 SQLite（modernc.org/sqlite，纯 Go 无 CGO）与 MySQL（go-sql-driver/mysql）。
3// 为保证两种数据库行为一致，时间字段统一使用 TEXT 存储 RFC3339 格式（UTC）。
4package database
5
6import (
7 "database/sql"
8 "fmt"
9 "os"
10 "path/filepath"
11 "strings"
12
13 _ "github.com/go-sql-driver/mysql"
14 _ "modernc.org/sqlite"
15
16 "clearlove/internal/config"
17 "clearlove/internal/util"
18)
19
20// DB 全局数据库句柄，所有处理器直接使用
21var DB *sql.DB
22
23// Driver 当前驱动名（sqlite / mysql）
24var Driver string
25
26// Connect 依据配置打开数据库连接
27func Connect() error {
28 var dsn string
29 Driver = config.Cfg.DBType
30 switch Driver {
31 case "mysql":
32 dsn = config.Cfg.MySQLDsn
33 case "sqlite", "":
34 Driver = "sqlite"
35 dsn = config.Cfg.SQLitePath
36 // 默认/历史默认路径跟随数据目录（支持 CLEARLOVE_DATA_DIR 自定义）
37 if dsn == "" || dsn == "data/clearlove.db" {
38 dsn = filepath.Join(config.Cfg.DataDir, "clearlove.db")
39 }
40 if dir := filepath.Dir(dsn); dir != "" && dir != "." {
41 if err := os.MkdirAll(dir, 0o755); err != nil {
42 return fmt.Errorf("创建数据库目录失败: %w", err)
43 }
44 }
45 default:
46 return fmt.Errorf("不支持的数据库类型: %s", Driver)
47 }
48 db, err := sql.Open(Driver, dsn)
49 if err != nil {
50 return err
51 }
52 // SQLite 写并发能力有限，限制连接数；MySQL 走默认连接池
53 if Driver == "sqlite" {
54 db.SetMaxOpenConns(1)
55 }
56 if err := db.Ping(); err != nil {
57 return err
58 }
59 DB = db
60 return nil
61}
62
63// schemaDDL 表结构模板。{PK}=自增主键 {I}=整型 {LT}=长文本，按驱动替换
64const schemaDDL = `
65CREATE TABLE IF NOT EXISTS users (
66 id {PK}, username VARCHAR(64) NOT NULL UNIQUE, email VARCHAR(128) NOT NULL UNIQUE,
67 password VARCHAR(128) NOT NULL, avatar VARCHAR(255) DEFAULT '', status {I} DEFAULT 1, created_at VARCHAR(40)
68);
69CREATE TABLE IF NOT EXISTS posts (
70 id {PK}, user_id {I} DEFAULT 0, nickname VARCHAR(64) DEFAULT '匿名', content {LT},
71 topic_id {I} DEFAULT 0, ip VARCHAR(64) DEFAULT '', fingerprint VARCHAR(128) DEFAULT '',
72 status {I} DEFAULT 1, like_count {I} DEFAULT 0, comment_count {I} DEFAULT 0,
73 is_admin {I} DEFAULT 0, badges VARCHAR(255) DEFAULT '', ai_reviewed {I} DEFAULT 0, created_at VARCHAR(40)
74);
75
76CREATE TABLE IF NOT EXISTS medias (
77 id {PK}, post_id {I} DEFAULT 0, type VARCHAR(10) DEFAULT 'image', path VARCHAR(255) DEFAULT '', created_at VARCHAR(40)
78);
79CREATE TABLE IF NOT EXISTS comments (
80 id {PK}, post_id {I} DEFAULT 0, user_id {I} DEFAULT 0, nickname VARCHAR(64) DEFAULT '匿名',
81 content {LT}, ip VARCHAR(64) DEFAULT '', fingerprint VARCHAR(128) DEFAULT '',
82 status {I} DEFAULT 1, ai_reviewed {I} DEFAULT 0, created_at VARCHAR(40)
83);
84CREATE TABLE IF NOT EXISTS likes (
85 id {PK}, post_id {I} DEFAULT 0, user_id {I} DEFAULT 0,
86 fingerprint VARCHAR(128) DEFAULT '', created_at VARCHAR(40)
87);
88CREATE TABLE IF NOT EXISTS topics (
89 id {PK}, name VARCHAR(64) NOT NULL UNIQUE, created_at VARCHAR(40)
90);
91CREATE TABLE IF NOT EXISTS reports (
92 id {PK}, post_id {I} DEFAULT 0, reason {LT}, reporter_ip VARCHAR(64) DEFAULT '',
93 status {I} DEFAULT 0, ai_result {LT}, created_at VARCHAR(40)
94);
95CREATE TABLE IF NOT EXISTS admins (
96 id {PK}, username VARCHAR(64) NOT NULL UNIQUE, password VARCHAR(128) NOT NULL,
97 role VARCHAR(20) DEFAULT 'custom', perms {LT}, created_at VARCHAR(40)
98);
99CREATE TABLE IF NOT EXISTS settings (k VARCHAR(64) PRIMARY KEY, v {LT});
100CREATE TABLE IF NOT EXISTS notices (id {PK}, title VARCHAR(128) DEFAULT '', content {LT}, created_at VARCHAR(40));
101CREATE TABLE IF NOT EXISTS apikeys (id {PK}, name VARCHAR(64) DEFAULT '', api_key VARCHAR(64) NOT NULL UNIQUE, status {I} DEFAULT 1, last_used VARCHAR(40) DEFAULT '', created_at VARCHAR(40));
102CREATE TABLE IF NOT EXISTS ai_logs (id {PK}, post_id {I} DEFAULT 0, action VARCHAR(16) DEFAULT '', reason {LT}, target VARCHAR(10) DEFAULT 'post', created_at VARCHAR(40));
103CREATE TABLE IF NOT EXISTS bans (id {PK}, btype VARCHAR(16) DEFAULT '', bvalue VARCHAR(128) DEFAULT '', created_at VARCHAR(40));
104CREATE TABLE IF NOT EXISTS verifications (id {PK}, email VARCHAR(128) NOT NULL, code VARCHAR(10) NOT NULL, expires VARCHAR(40));
105CREATE TABLE IF NOT EXISTS plugin_logs (id {PK}, plugin_slug VARCHAR(64) DEFAULT '', action VARCHAR(48) DEFAULT '', detail {LT}, duration_ms {I} DEFAULT 0, created_at VARCHAR(40));
106`
107
108// Migrate 安装时自动建表并创建索引，可重复执行（幂等）
109func Migrate() error {
110 ddl := schemaDDL
111 // MySQL 的 TEXT/MEDIUMTEXT 不允许设置默认值，长文本列统一不使用 DEFAULT
112 if Driver == "mysql" {
113 ddl = strings.NewReplacer("{PK}", "BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT",
114 "{I}", "INT", "{LT}", "MEDIUMTEXT").Replace(ddl)
115 } else {
116 ddl = strings.NewReplacer("{PK}", "INTEGER PRIMARY KEY AUTOINCREMENT",
117 "{I}", "INTEGER", "{LT}", "TEXT").Replace(ddl)
118 }
119 for _, stmt := range strings.Split(ddl, ";") {
120 stmt = strings.TrimSpace(stmt)
121 if stmt == "" {
122 continue
123 }
124 if _, err := DB.Exec(stmt); err != nil {
125 // MySQL 不支持 CREATE INDEX IF NOT EXISTS，重复创建索引时忽略报错
126 if !strings.Contains(strings.ToUpper(stmt), "CREATE INDEX") {
127 return fmt.Errorf("迁移失败: %v SQL: %.120s", err, stmt)
128 }
129 }
130 }
131 // 兼容旧库：posts 增加 is_admin / badges 列（列已存在时报错，忽略即可）
132 alter := "ALTER TABLE posts ADD COLUMN is_admin INTEGER DEFAULT 0"
133 if Driver == "mysql" {
134 alter = "ALTER TABLE posts ADD COLUMN is_admin INT DEFAULT 0"
135 }
136 _, _ = DB.Exec(alter)
137 _, _ = DB.Exec("ALTER TABLE posts ADD COLUMN badges VARCHAR(255) DEFAULT ''")
138 // 兼容旧库：apikeys.key 是 MySQL 保留字，统一改名为 api_key（列不存在/已改名时报错，忽略即可）
139 rename := `ALTER TABLE apikeys RENAME COLUMN "key" TO "api_key"`
140 if Driver == "mysql" {
141 rename = "ALTER TABLE apikeys RENAME COLUMN `key` TO `api_key`"
142 }
143 _, _ = DB.Exec(rename)
144
145 // AI 审核 v2：评论可见性/已审标记、帖子已审标记、日志目标类型（列已存在时报错，忽略即可）
146 aiType := "INTEGER"
147 if Driver == "mysql" {
148 aiType = "INT"
149 }
150 for _, stmt := range []string{
151 "ALTER TABLE comments ADD COLUMN status " + aiType + " DEFAULT 1",
152 "ALTER TABLE comments ADD COLUMN ai_reviewed " + aiType + " DEFAULT 0",
153 "ALTER TABLE posts ADD COLUMN ai_reviewed " + aiType + " DEFAULT 0",
154 "ALTER TABLE ai_logs ADD COLUMN target VARCHAR(10) DEFAULT 'post'",
155 } {
156 _, _ = DB.Exec(stmt)
157 }
158
159 // 兼容旧库：likes 去重后再建唯一索引，防止并发点赞产生重复行
160 _, _ = DB.Exec(`DELETE FROM likes WHERE id NOT IN (
161 SELECT min_id FROM (SELECT MIN(id) AS min_id FROM likes GROUP BY post_id, fingerprint) t)`)
162
163 // 常用索引（忽略已存在错误）
164 indexes := []string{
165 "CREATE INDEX idx_posts_created ON posts (created_at)",
166 "CREATE INDEX idx_posts_topic ON posts (topic_id)",
167 "CREATE INDEX idx_media_post ON medias (post_id)",
168 "CREATE INDEX idx_comments_post ON comments (post_id)",
169 "CREATE INDEX idx_likes_post ON likes (post_id)",
170 "CREATE UNIQUE INDEX idx_likes_uniq ON likes (post_id, fingerprint)",
171 }
172 for _, ix := range indexes {
173 _, _ = DB.Exec(ix)
174 }
175 util.Log("info", "数据库迁移完成（驱动: %s）", Driver)
176 return nil
177}