109 lines
3.0 KiB
JavaScript
109 lines
3.0 KiB
JavaScript
/**
|
||
* DMP 数据库初始化
|
||
*
|
||
* 📚 性能设计要点：
|
||
* 1. 位图索引（Bitmap Index）
|
||
* - 每个标签对应一个位数组，bit[i]=1 表示第 i 个用户拥有该标签
|
||
* - 多标签交叉计算 = 位数组 AND/OR 运算 → O(n/64) 复杂度
|
||
* - 这是 OLAP 数据库（ClickHouse / Druid）的核心思想
|
||
*
|
||
* 2. 预聚合
|
||
* - 标签人数在写入时就维护好，查询时直接读取
|
||
* - 避免 COUNT(*) 全表扫描
|
||
*
|
||
* 3. 连接管理
|
||
* - WAL 模式支持读写并发
|
||
* - busy_timeout 避免锁等待超时
|
||
*/
|
||
|
||
const Database = require('better-sqlite3');
|
||
const path = require('path');
|
||
|
||
const DB_PATH = path.join(__dirname, '..', 'dmp.db');
|
||
|
||
function getDb(dbSuffix = '') {
|
||
const dbFile = dbSuffix ? `dmp_${dbSuffix}.db` : 'dmp.db';
|
||
const dbPath = path.join(__dirname, '..', dbFile);
|
||
const db = new Database(dbPath);
|
||
db.pragma('journal_mode = WAL');
|
||
db.pragma('foreign_keys = ON');
|
||
db.pragma('busy_timeout = 5000');
|
||
db.pragma('cache_size = -64000'); // 64MB cache
|
||
db.pragma('synchronous = NORMAL');
|
||
return db;
|
||
}
|
||
|
||
function initializeDatabase(dbSuffix = '') {
|
||
const db = getDb(dbSuffix);
|
||
|
||
// 用户表 — 宽表设计，一行一个用户
|
||
db.exec(`
|
||
CREATE TABLE IF NOT EXISTS users (
|
||
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
uid TEXT UNIQUE NOT NULL,
|
||
name TEXT,
|
||
email TEXT,
|
||
created_at TEXT DEFAULT (datetime('now')),
|
||
extra_json TEXT DEFAULT '{}'
|
||
)
|
||
`);
|
||
|
||
// 标签分类（列）
|
||
db.exec(`
|
||
CREATE TABLE IF NOT EXISTS tag_categories (
|
||
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
key TEXT UNIQUE NOT NULL,
|
||
name TEXT NOT NULL,
|
||
sort_order INTEGER DEFAULT 0,
|
||
color TEXT DEFAULT '#6366f1'
|
||
)
|
||
`);
|
||
|
||
// 标签定义
|
||
db.exec(`
|
||
CREATE TABLE IF NOT EXISTS tags (
|
||
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
key TEXT UNIQUE NOT NULL,
|
||
name TEXT NOT NULL,
|
||
category_id INTEGER NOT NULL REFERENCES tag_categories(id),
|
||
description TEXT DEFAULT '',
|
||
coverage INTEGER DEFAULT 0,
|
||
coverage_rate REAL DEFAULT 0,
|
||
trend REAL DEFAULT 0,
|
||
sort_order INTEGER DEFAULT 0
|
||
)
|
||
`);
|
||
|
||
// 用户-标签关联（核心表，需要最强索引）
|
||
db.exec(`
|
||
CREATE TABLE IF NOT EXISTS user_tags (
|
||
user_id INTEGER NOT NULL,
|
||
tag_id INTEGER NOT NULL,
|
||
PRIMARY KEY (user_id, tag_id)
|
||
) WITHOUT ROWID
|
||
`);
|
||
// WITHOUT ROWID = clustered index on PK，查询更快
|
||
|
||
db.exec(`
|
||
CREATE INDEX IF NOT EXISTS idx_user_tags_tag ON user_tags(tag_id, user_id);
|
||
`);
|
||
|
||
// 数据导入批次记录
|
||
db.exec(`
|
||
CREATE TABLE IF NOT EXISTS import_batches (
|
||
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
source TEXT NOT NULL,
|
||
record_count INTEGER DEFAULT 0,
|
||
status TEXT DEFAULT 'pending',
|
||
error_message TEXT,
|
||
started_at TEXT DEFAULT (datetime('now')),
|
||
finished_at TEXT
|
||
)
|
||
`);
|
||
|
||
console.log(`✅ 数据库表初始化完成 [${dbSuffix || 'default'}]`);
|
||
db.close();
|
||
}
|
||
|
||
module.exports = { getDb, initializeDatabase };
|