453 行
18 KiB
PHP
453 行
18 KiB
PHP
<?php
|
|
class Database {
|
|
private static $instance = null;
|
|
private $connection;
|
|
private static $initialized = false;
|
|
|
|
private function __construct() {
|
|
try {
|
|
// 确保data目录存在
|
|
$dataDir = __DIR__ . '/data';
|
|
if (!is_dir($dataDir)) {
|
|
mkdir($dataDir, 0755, true);
|
|
}
|
|
|
|
$dbPath = $dataDir . '/parlz.db';
|
|
$this->connection = new SQLite3($dbPath);
|
|
|
|
// 只在第一次初始化时执行
|
|
if (!self::$initialized) {
|
|
$this->initialize();
|
|
self::$initialized = true;
|
|
}
|
|
} catch (Exception $e) {
|
|
// 处理异常，确保即使出现问题也能给出明确的错误信息
|
|
die('数据库初始化失败: ' . $e->getMessage());
|
|
}
|
|
}
|
|
|
|
public static function getInstance() {
|
|
if (self::$instance === null) {
|
|
self::$instance = new Database();
|
|
}
|
|
return self::$instance;
|
|
}
|
|
|
|
public function getConnection() {
|
|
return $this->connection;
|
|
}
|
|
|
|
/**
|
|
* 检查表是否存在
|
|
*/
|
|
private function tableExists($tableName) {
|
|
$result = $this->connection->query("SELECT name FROM sqlite_master WHERE type='table' AND name='$tableName'");
|
|
return $result && $result->fetchArray() !== false;
|
|
}
|
|
|
|
/**
|
|
* 检查列是否存在
|
|
*/
|
|
private function columnExists($tableName, $columnName) {
|
|
$result = $this->connection->query("PRAGMA table_info($tableName)");
|
|
while ($row = $result->fetchArray(SQLITE3_ASSOC)) {
|
|
if ($row['name'] === $columnName) {
|
|
return true;
|
|
}
|
|
}
|
|
return false;
|
|
}
|
|
|
|
/**
|
|
* 添加列到表
|
|
*/
|
|
private function addColumn($tableName, $columnDefinition) {
|
|
try {
|
|
$this->connection->exec("ALTER TABLE $tableName ADD COLUMN $columnDefinition");
|
|
return true;
|
|
} catch (Exception $e) {
|
|
// 忽略错误，因为列可能已经存在
|
|
return false;
|
|
}
|
|
}
|
|
|
|
/**
|
|
* 创建表（如果不存在）
|
|
*/
|
|
private function createTable($tableName, $tableDefinition) {
|
|
try {
|
|
$sql = "CREATE TABLE IF NOT EXISTS $tableName ($tableDefinition)";
|
|
$this->connection->exec($sql);
|
|
return true;
|
|
} catch (Exception $e) {
|
|
// 忽略错误，因为表可能已经存在
|
|
return false;
|
|
}
|
|
}
|
|
|
|
private function initialize() {
|
|
// 创建用户表
|
|
$this->createTable('users', "
|
|
id TEXT PRIMARY KEY,
|
|
username TEXT NOT NULL UNIQUE,
|
|
password TEXT NOT NULL,
|
|
created_at INTEGER NOT NULL,
|
|
last_login INTEGER NOT NULL,
|
|
online INTEGER DEFAULT 0,
|
|
avatar TEXT DEFAULT '',
|
|
is_admin INTEGER DEFAULT 0,
|
|
is_banned INTEGER DEFAULT 0
|
|
");
|
|
|
|
// 为 users 表添加缺失的列
|
|
// 确保 users 表包含所有必要的列，这一步必须在创建其他表之前执行
|
|
if (!$this->columnExists('users', 'is_admin')) {
|
|
$this->addColumn('users', 'is_admin INTEGER DEFAULT 0');
|
|
}
|
|
if (!$this->columnExists('users', 'is_banned')) {
|
|
$this->addColumn('users', 'is_banned INTEGER DEFAULT 0');
|
|
}
|
|
if (!$this->columnExists('users', 'avatar')) {
|
|
$this->addColumn('users', 'avatar TEXT DEFAULT \'\'');
|
|
}
|
|
if (!$this->columnExists('users', 'online')) {
|
|
$this->addColumn('users', 'online INTEGER DEFAULT 0');
|
|
}
|
|
if (!$this->columnExists('users', 'city')) {
|
|
$this->addColumn('users', 'city TEXT DEFAULT \'\'');
|
|
}
|
|
if (!$this->columnExists('users', 'province')) {
|
|
$this->addColumn('users', 'province TEXT DEFAULT \'\'');
|
|
}
|
|
if (!$this->columnExists('users', 'country')) {
|
|
$this->addColumn('users', 'country TEXT DEFAULT \'\'');
|
|
}
|
|
if (!$this->columnExists('users', 'getip')) {
|
|
$this->addColumn('users', 'getip INTEGER DEFAULT 1');
|
|
}
|
|
if (!$this->columnExists('users', 'email')) {
|
|
$this->addColumn('users', 'email TEXT DEFAULT \'\'');
|
|
}
|
|
if (!$this->columnExists('users', 'remember_token')) {
|
|
$this->addColumn('users', 'remember_token TEXT DEFAULT \'\'');
|
|
}
|
|
if (!$this->columnExists('users', 'remember_token_expires')) {
|
|
$this->addColumn('users', 'remember_token_expires INTEGER DEFAULT 0');
|
|
}
|
|
if (!$this->columnExists('users', 'email_verified')) {
|
|
$this->addColumn('users', 'email_verified INTEGER DEFAULT 0');
|
|
}
|
|
if (!$this->columnExists('users', 'verification_code')) {
|
|
$this->addColumn('users', 'verification_code TEXT DEFAULT \'\'');
|
|
}
|
|
if (!$this->columnExists('users', 'qq_openid')) {
|
|
$this->addColumn('users', 'qq_openid TEXT DEFAULT \'\'');
|
|
}
|
|
if (!$this->columnExists('users', 'qq_nickname')) {
|
|
$this->addColumn('users', 'qq_nickname TEXT DEFAULT \'\'');
|
|
}
|
|
if (!$this->columnExists('users', 'github_id')) {
|
|
$this->addColumn('users', 'github_id TEXT DEFAULT \'\'');
|
|
}
|
|
if (!$this->columnExists('users', 'github_username')) {
|
|
$this->addColumn('users', 'github_username TEXT DEFAULT \'\'');
|
|
}
|
|
if (!$this->columnExists('users', 'microsoft_id')) {
|
|
$this->addColumn('users', 'microsoft_id TEXT DEFAULT \'\'');
|
|
}
|
|
if (!$this->columnExists('users', 'microsoft_username')) {
|
|
$this->addColumn('users', 'microsoft_username TEXT DEFAULT \'\'');
|
|
}
|
|
if (!$this->columnExists('users', 'steam_id')) {
|
|
$this->addColumn('users', 'steam_id TEXT DEFAULT \'\'');
|
|
}
|
|
if (!$this->columnExists('users', 'steam_username')) {
|
|
$this->addColumn('users', 'steam_username TEXT DEFAULT \'\'');
|
|
}
|
|
if (!$this->columnExists('users', 'discord_id')) {
|
|
$this->addColumn('users', 'discord_id TEXT DEFAULT \'\'');
|
|
}
|
|
if (!$this->columnExists('users', 'discord_username')) {
|
|
$this->addColumn('users', 'discord_username TEXT DEFAULT \'\'');
|
|
}
|
|
if (!$this->columnExists('users', 'bio')) {
|
|
$this->addColumn('users', 'bio TEXT DEFAULT \'\'');
|
|
}
|
|
|
|
// 创建好友表
|
|
$this->createTable('friends', "
|
|
id TEXT PRIMARY KEY,
|
|
user_id TEXT NOT NULL,
|
|
friend_id TEXT NOT NULL,
|
|
created_at INTEGER NOT NULL,
|
|
status TEXT DEFAULT 'accepted',
|
|
FOREIGN KEY (user_id) REFERENCES users(id),
|
|
FOREIGN KEY (friend_id) REFERENCES users(id)
|
|
");
|
|
|
|
// 创建群组表
|
|
$this->createTable('groups', "
|
|
id TEXT PRIMARY KEY,
|
|
group_id TEXT NOT NULL UNIQUE,
|
|
name TEXT NOT NULL,
|
|
creator_id TEXT NOT NULL,
|
|
created_at INTEGER NOT NULL,
|
|
avatar TEXT DEFAULT '',
|
|
FOREIGN KEY (creator_id) REFERENCES users(id)
|
|
");
|
|
|
|
// 为现有表添加缺失的group_id列（如果不存在）
|
|
try {
|
|
// 检查groups表是否存在group_id列
|
|
if (!$this->columnExists('groups', 'group_id')) {
|
|
// 先添加可为空的列
|
|
$this->addColumn('groups', 'group_id TEXT');
|
|
// 更新现有记录的group_id值
|
|
$this->connection->exec("UPDATE groups SET group_id = 'G' || upper(substr(hex(randomblob(4)), 0, 8)) WHERE group_id IS NULL");
|
|
// 修改列为非空
|
|
$this->connection->exec("CREATE TABLE IF NOT EXISTS groups_new (
|
|
id TEXT PRIMARY KEY,
|
|
group_id TEXT NOT NULL UNIQUE,
|
|
name TEXT NOT NULL,
|
|
creator_id TEXT NOT NULL,
|
|
created_at INTEGER NOT NULL,
|
|
avatar TEXT DEFAULT '',
|
|
FOREIGN KEY (creator_id) REFERENCES users(id)
|
|
)");
|
|
$this->connection->exec("INSERT INTO groups_new SELECT * FROM groups");
|
|
$this->connection->exec("DROP TABLE groups");
|
|
$this->connection->exec("ALTER TABLE groups_new RENAME TO groups");
|
|
}
|
|
// 创建唯一索引
|
|
$this->connection->exec("CREATE UNIQUE INDEX IF NOT EXISTS idx_groups_group_id ON groups(group_id)");
|
|
} catch (Exception $e) {
|
|
// 忽略错误，因为列可能已经存在
|
|
}
|
|
|
|
// 创建群成员表
|
|
$this->createTable('group_members', "
|
|
group_id TEXT NOT NULL,
|
|
user_id TEXT NOT NULL,
|
|
role TEXT DEFAULT 'member',
|
|
joined_at INTEGER NOT NULL,
|
|
PRIMARY KEY (group_id, user_id),
|
|
FOREIGN KEY (group_id) REFERENCES groups(id),
|
|
FOREIGN KEY (user_id) REFERENCES users(id)
|
|
");
|
|
|
|
// 创建群公告表
|
|
$this->createTable('group_announcements', "
|
|
id TEXT PRIMARY KEY,
|
|
group_id TEXT NOT NULL,
|
|
content TEXT NOT NULL,
|
|
created_by TEXT NOT NULL,
|
|
created_at INTEGER NOT NULL,
|
|
FOREIGN KEY (group_id) REFERENCES groups(id),
|
|
FOREIGN KEY (created_by) REFERENCES users(id)
|
|
");
|
|
|
|
// 创建消息表
|
|
$this->createTable('messages', "
|
|
id TEXT PRIMARY KEY,
|
|
sender_id TEXT NOT NULL,
|
|
receiver_id TEXT NOT NULL,
|
|
content TEXT NOT NULL,
|
|
timestamp INTEGER NOT NULL,
|
|
read INTEGER DEFAULT 0,
|
|
delivered INTEGER DEFAULT 0,
|
|
is_image INTEGER DEFAULT 0,
|
|
is_group INTEGER DEFAULT 0,
|
|
recalled INTEGER DEFAULT 0,
|
|
FOREIGN KEY (sender_id) REFERENCES users(id)
|
|
");
|
|
|
|
// 创建消息引用表
|
|
$this->createTable('message_quotes', "
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
message_id TEXT NOT NULL,
|
|
sender TEXT NOT NULL,
|
|
content TEXT NOT NULL,
|
|
quoted_message_id TEXT DEFAULT NULL,
|
|
FOREIGN KEY (message_id) REFERENCES messages(id)
|
|
");
|
|
|
|
// 创建举报表
|
|
$this->createTable('reports', "
|
|
id TEXT PRIMARY KEY,
|
|
message_id TEXT NOT NULL,
|
|
reporter_id TEXT NOT NULL,
|
|
reason TEXT DEFAULT '',
|
|
status TEXT DEFAULT 'pending',
|
|
created_at INTEGER NOT NULL,
|
|
processed_by TEXT DEFAULT NULL,
|
|
processed_at INTEGER DEFAULT NULL,
|
|
FOREIGN KEY (message_id) REFERENCES messages(id),
|
|
FOREIGN KEY (reporter_id) REFERENCES users(id),
|
|
FOREIGN KEY (processed_by) REFERENCES users(id)
|
|
");
|
|
|
|
// 示例：添加新功能所需的表（例如用户设置表）
|
|
$this->createTable('user_settings', "
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
user_id TEXT NOT NULL,
|
|
setting_key TEXT NOT NULL,
|
|
setting_value TEXT DEFAULT '',
|
|
created_at INTEGER NOT NULL,
|
|
updated_at INTEGER NOT NULL,
|
|
FOREIGN KEY (user_id) REFERENCES users(id),
|
|
UNIQUE(user_id, setting_key)
|
|
");
|
|
|
|
// 创建备注表
|
|
$this->createTable('notes', "
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
user_id TEXT NOT NULL,
|
|
target_id TEXT NOT NULL,
|
|
target_type TEXT NOT NULL,
|
|
note TEXT DEFAULT '',
|
|
created_at INTEGER NOT NULL,
|
|
updated_at INTEGER NOT NULL,
|
|
FOREIGN KEY (user_id) REFERENCES users(id),
|
|
UNIQUE(user_id, target_id, target_type)
|
|
");
|
|
|
|
// 示例：添加新功能所需的表（例如聊天记录归档表）
|
|
$this->createTable('archived_messages', "
|
|
id TEXT PRIMARY KEY,
|
|
original_message_id TEXT NOT NULL,
|
|
sender_id TEXT NOT NULL,
|
|
receiver_id TEXT NOT NULL,
|
|
content TEXT NOT NULL,
|
|
timestamp INTEGER NOT NULL,
|
|
archived_at INTEGER NOT NULL,
|
|
FOREIGN KEY (original_message_id) REFERENCES messages(id),
|
|
FOREIGN KEY (sender_id) REFERENCES users(id)
|
|
");
|
|
|
|
// 创建系统设置表
|
|
$this->createTable('system_settings', "
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
setting_key TEXT NOT NULL UNIQUE,
|
|
setting_value TEXT DEFAULT '',
|
|
created_at INTEGER NOT NULL,
|
|
updated_at INTEGER NOT NULL
|
|
");
|
|
|
|
// 创建用户协议同意表
|
|
$this->createTable('user_terms_agreements', "
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
user_id TEXT NOT NULL,
|
|
terms_version TEXT NOT NULL,
|
|
agreed_at INTEGER NOT NULL,
|
|
FOREIGN KEY (user_id) REFERENCES users(id),
|
|
UNIQUE(user_id, terms_version)
|
|
");
|
|
|
|
// 为其他表添加缺失的列
|
|
// 确保 groups 表包含所有必要的列
|
|
if (!$this->columnExists('groups', 'group_id')) {
|
|
$this->addColumn('groups', 'group_id TEXT');
|
|
}
|
|
|
|
// 确保 messages 表包含所有必要的列
|
|
if (!$this->columnExists('messages', 'delivered')) {
|
|
$this->addColumn('messages', 'delivered INTEGER DEFAULT 0');
|
|
}
|
|
|
|
// 确保 users 表包含IP字段
|
|
if (!$this->columnExists('users', 'last_ip')) {
|
|
$this->addColumn('users', 'last_ip TEXT DEFAULT \'\'');
|
|
}
|
|
|
|
// 创建举报表索引
|
|
$this->connection->exec("CREATE INDEX IF NOT EXISTS idx_reports_message_id ON reports(message_id)");
|
|
$this->connection->exec("CREATE INDEX IF NOT EXISTS idx_reports_reporter_id ON reports(reporter_id)");
|
|
$this->connection->exec("CREATE INDEX IF NOT EXISTS idx_reports_status ON reports(status)");
|
|
$this->connection->exec("CREATE INDEX IF NOT EXISTS idx_reports_created_at ON reports(created_at)");
|
|
|
|
// 添加索引以优化查询性能
|
|
// 用户表索引
|
|
$this->connection->exec("CREATE INDEX IF NOT EXISTS idx_users_username ON users(username)");
|
|
$this->connection->exec("CREATE INDEX IF NOT EXISTS idx_users_online ON users(online)");
|
|
|
|
// 好友表索引
|
|
$this->connection->exec("CREATE INDEX IF NOT EXISTS idx_friends_user_id ON friends(user_id)");
|
|
$this->connection->exec("CREATE INDEX IF NOT EXISTS idx_friends_friend_id ON friends(friend_id)");
|
|
$this->connection->exec("CREATE INDEX IF NOT EXISTS idx_friends_status ON friends(status)");
|
|
|
|
// 群组表索引
|
|
$this->connection->exec("CREATE INDEX IF NOT EXISTS idx_groups_creator_id ON groups(creator_id)");
|
|
|
|
// 群成员表索引
|
|
$this->connection->exec("CREATE INDEX IF NOT EXISTS idx_group_members_group_id ON group_members(group_id)");
|
|
$this->connection->exec("CREATE INDEX IF NOT EXISTS idx_group_members_user_id ON group_members(user_id)");
|
|
$this->connection->exec("CREATE INDEX IF NOT EXISTS idx_group_members_role ON group_members(role)");
|
|
|
|
// 群公告表索引
|
|
$this->connection->exec("CREATE INDEX IF NOT EXISTS idx_group_announcements_group_id ON group_announcements(group_id)");
|
|
$this->connection->exec("CREATE INDEX IF NOT EXISTS idx_group_announcements_created_at ON group_announcements(created_at)");
|
|
|
|
// 消息表索引
|
|
$this->connection->exec("CREATE INDEX IF NOT EXISTS idx_messages_sender_id ON messages(sender_id)");
|
|
$this->connection->exec("CREATE INDEX IF NOT EXISTS idx_messages_receiver_id ON messages(receiver_id)");
|
|
$this->connection->exec("CREATE INDEX IF NOT EXISTS idx_messages_timestamp ON messages(timestamp)");
|
|
$this->connection->exec("CREATE INDEX IF NOT EXISTS idx_messages_is_group ON messages(is_group)");
|
|
$this->connection->exec("CREATE INDEX IF NOT EXISTS idx_messages_read ON messages(read)");
|
|
$this->connection->exec("CREATE INDEX IF NOT EXISTS idx_messages_sender_receiver ON messages(sender_id, receiver_id)");
|
|
$this->connection->exec("CREATE INDEX IF NOT EXISTS idx_messages_receiver_read ON messages(receiver_id, read)");
|
|
|
|
// 消息引用表索引
|
|
$this->connection->exec("CREATE INDEX IF NOT EXISTS idx_message_quotes_message_id ON message_quotes(message_id)");
|
|
}
|
|
|
|
public function query($sql, $params = []) {
|
|
$stmt = $this->connection->prepare($sql);
|
|
|
|
if (!$stmt) {
|
|
return false;
|
|
}
|
|
|
|
foreach ($params as $key => $value) {
|
|
$stmt->bindValue(':' . $key, $value);
|
|
}
|
|
|
|
$result = $stmt->execute();
|
|
|
|
if (!$result) {
|
|
return false;
|
|
}
|
|
|
|
$rows = [];
|
|
while ($row = $result->fetchArray(SQLITE3_ASSOC)) {
|
|
$rows[] = $row;
|
|
}
|
|
|
|
return $rows;
|
|
}
|
|
|
|
public function execute($sql, $params = []) {
|
|
$stmt = $this->connection->prepare($sql);
|
|
|
|
if (!$stmt) {
|
|
return false;
|
|
}
|
|
|
|
foreach ($params as $key => $value) {
|
|
$stmt->bindValue(':' . $key, $value);
|
|
}
|
|
|
|
$result = $stmt->execute();
|
|
return $result !== false;
|
|
}
|
|
|
|
public function lastInsertRowID() {
|
|
return $this->connection->lastInsertRowID();
|
|
}
|
|
|
|
public function close() {
|
|
if ($this->connection) {
|
|
$this->connection->close();
|
|
}
|
|
}
|
|
}
|
|
?>
|