SQLite 使用指南：轻量级嵌入式数据库

-- 获取当前日期时间
SELECT datetime('now');                    -- 2024-09-14 14:30:00
SELECT date('now');                         -- 2024-09-14
SELECT time('now');                         -- 14:30:00
SELECT strftime('%Y-%m-%d %H:%M:%S', 'now'); -- 2024-09-14 14:30:00

-- 时间计算
SELECT datetime('now', '+7 days');          -- 一周后
SELECT datetime('now', '-1 month');         -- 一个月前
SELECT datetime('now', 'start of year');    -- 今年第一天
SELECT datetime('now', 'start of month', '+1 month', '-1 day'); -- 本月最后一天

-- Unix 时间戳转换
SELECT datetime(1726300800, 'unixepoch');    -- 时间戳 → 日期时间
SELECT strftime('%s', 'now');               -- 日期时间 → 时间戳

-- 儒略日
SELECT julianday('now');
SELECT date(julianday('now'));

-- 日期差值
SELECT julianday('2024-12-31') - julianday('2024-09-14');  -- 108 天
SELECT strftime('%j', '2024-12-31') - strftime('%j', '2024-09-14'); -- 108

-- 周几
SELECT strftime('%w', '2024-09-14');  -- 6（周六，0=周日）

-- 格式化字符串
SELECT strftime('%Y年%m月%d日 %H时%M分%S秒', 'now');
-- 2024年09月14日 14时30分00秒

-- 实际建表示例
CREATE TABLE events (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    created_at TEXT NOT NULL DEFAULT (datetime('now', 'localtime')),
    updated_at TEXT NOT NULL DEFAULT (datetime('now', 'localtime'))
);

CREATE TRIGGER update_timestamp AFTER UPDATE ON events
BEGIN
    UPDATE events SET updated_at = datetime('now', 'localtime') WHERE id = NEW.id;
END;

-- 准备示例数据
CREATE TABLE sales (
    year INTEGER,
    quarter INTEGER,
    amount REAL
);
INSERT INTO sales VALUES
    (2023, 1, 1000), (2023, 2, 1500), (2023, 3, 1200), (2023, 4, 1800),
    (2024, 1, 1100), (2024, 2, 1600), (2024, 3, 1300), (2024, 4, 1900);

-- ROW_NUMBER：行号
SELECT year, quarter, amount,
    ROW_NUMBER() OVER (ORDER BY amount DESC) AS rank
FROM sales;

-- RANK / DENSE_RANK
SELECT year, quarter, amount,
    RANK() OVER (ORDER BY amount DESC) AS r,
    DENSE_RANK() OVER (ORDER BY amount DESC) AS dr
FROM sales;

-- LAG / LEAD：前后行访问
SELECT year, quarter, amount,
    LAG(amount) OVER (ORDER BY year, quarter) AS prev_quarter,
    LEAD(amount) OVER (ORDER BY year, quarter) AS next_quarter,
    amount - LAG(amount) OVER (ORDER BY year, quarter) AS growth
FROM sales;

-- 分区统计
SELECT year, quarter, amount,
    SUM(amount) OVER (PARTITION BY year) AS year_total,
    ROUND(amount * 100.0 / SUM(amount) OVER (PARTITION BY year), 2) AS pct
FROM sales;

-- 移动平均（过去 2 个季度 + 当前季度 = 3 期移动平均）
SELECT year, quarter, amount,
    ROUND(AVG(amount) OVER (
        ORDER BY year, quarter
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ), 2) AS moving_avg_3q
FROM sales;

-- FIRST_VALUE / LAST_VALUE
SELECT year, quarter, amount,
    FIRST_VALUE(amount) OVER (PARTITION BY year ORDER BY quarter) AS year_start,
    LAST_VALUE(amount) OVER (PARTITION BY year ORDER BY quarter
        RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS year_end
FROM sales;

-- NTILE：分桶（四分位）
SELECT year, quarter, amount,
    NTILE(4) OVER (ORDER BY amount DESC) AS quartile
FROM sales;

-- 准备表
CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    username TEXT NOT NULL UNIQUE,
    email TEXT NOT NULL UNIQUE,
    login_count INTEGER DEFAULT 0,
    updated_at TEXT DEFAULT (datetime('now'))
);

-- 方式一：INSERT OR REPLACE
-- 如果发生冲突，先 DELETE 旧行再 INSERT 新行
INSERT OR REPLACE INTO users (id, username, email, login_count)
VALUES (1, 'alice', 'alice@example.com', 5);
-- 注意：如果冲突由 UNIQUE 约束触发，旧行被删除，新行插入。
-- 这意味着未指定的列会恢复为默认值！

-- 方式二：INSERT OR IGNORE
-- 冲突时直接忽略，不插入也不报错
INSERT OR IGNORE INTO users (id, username, email)
VALUES (2, 'bob', 'bob@example.com');

-- 方式三：ON CONFLICT ... DO NOTHING
INSERT INTO users (id, username, email)
VALUES (3, 'charlie', 'charlie@example.com')
ON CONFLICT(username) DO NOTHING;

-- 方式四（推荐）：ON CONFLICT ... DO UPDATE
-- 冲突时更新特定字段，不删除旧行
INSERT INTO users (id, username, email, login_count)
VALUES (1, 'alice', 'alice@new.com', 1)
ON CONFLICT(id) DO UPDATE SET
    email = excluded.email,
    login_count = excluded.login_count,
    updated_at = datetime('now');

-- 更复杂的场景：累加登录次数
INSERT INTO users (id, username, email, login_count)
VALUES (4, 'dave', 'dave@example.com', 1)
ON CONFLICT(username) DO UPDATE SET
    login_count = users.login_count + 1,
    updated_at = datetime('now');

-- 启用 FTS5 扩展（需要编译时支持）
-- 创建全文搜索虚拟表
CREATE VIRTUAL TABLE articles_fts USING fts5(
    title,
    body,
    content='articles',        -- 内容表名称（可选）
    content_rowid='id',       -- 内容表行 ID 列名
    tokenize='porter unicode61'  -- 分词器设置
);

-- 插入数据到原始表
CREATE TABLE articles (
    id INTEGER PRIMARY KEY,
    title TEXT NOT NULL,
    body TEXT NOT NULL,
    published_at TEXT
);

INSERT INTO articles VALUES
    (1, 'SQLite 入门', 'SQLite 是一个轻量级的嵌入式数据库引擎...', '2024-09-01'),
    (2, '全文搜索实战', '使用 FTS5 实现高效全文检索...', '2024-09-10'),
    (3, '数据库性能优化', '合理使用索引可以大幅提升查询性能...', '2024-09-14');

-- 填充 FTS 索引
INSERT INTO articles_fts(rowid, title, body)
SELECT id, title, body FROM articles;

-- 全文搜索
SELECT rowid, rank, title, snippet(articles_fts, 0, '<b>', '</b>', '...', 20)
FROM articles_fts
WHERE articles_fts MATCH 'SQLite OR 全文搜索'
ORDER BY rank;

-- 使用 bm25 排名函数
SELECT rowid, bm25(articles_fts) AS score, title
FROM articles_fts
WHERE articles_fts MATCH '数据库'
ORDER BY score;

-- 前缀搜索
SELECT * FROM articles_fts WHERE articles_fts MATCH '数据*';

-- 短语搜索
SELECT * FROM articles_fts WHERE articles_fts MATCH '"嵌入式数据库"';

-- 就近搜索（NEAR）
SELECT * FROM articles_fts WHERE articles_fts MATCH 'SQLite NEAR 嵌入式';

-- 删除 FTC 内容关联（当原始表更新时，需要同步更新 FTS 表）
-- 使用触发器保持同步
CREATE TRIGGER articles_ai AFTER INSERT ON articles BEGIN
    INSERT INTO articles_fts(rowid, title, body) VALUES (new.id, new.title, new.body);
END;

CREATE TRIGGER articles_ad AFTER DELETE ON articles BEGIN
    INSERT INTO articles_fts(articles_fts, rowid, title, body)
    VALUES ('delete', old.id, old.title, old.body);
END;

CREATE TRIGGER articles_au AFTER UPDATE ON articles BEGIN
    INSERT INTO articles_fts(articles_fts, rowid, title, body)
    VALUES ('delete', old.id, old.title, old.body);
    INSERT INTO articles_fts(rowid, title, body) VALUES (new.id, new.title, new.body);
END;

-- 外部内容表（content= 语法）：自动同步，无需触发器
-- 创建时指定 content=，FTS 表会自动从原始表读取内容
-- 但需要定期重建索引
INSERT INTO articles_fts(articles_fts) VALUES('rebuild');

-- 创建含 JSON 字段的表
CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    attributes TEXT  -- JSON 字符串
);

INSERT INTO products VALUES
    (1, '笔记本', '{"brand":"联想","specs":{"cpu":"i7","ram":"16GB","storage":"512GB"},"price":5999,"colors":["银","灰"],"in_stock":true}'),
    (2, '手机', '{"brand":"华为","specs":{"cpu":"麒麟","ram":"8GB","storage":"256GB"},"price":3999,"colors":["黑","白","蓝"],"in_stock":true}'),
    (3, '平板', '{"brand":"苹果","specs":{"cpu":"M2","ram":"8GB","storage":"128GB"},"price":3499,"colors":["灰","金"],"in_stock":false}');

-- 提取 JSON 值
SELECT name,
    json_extract(attributes, '$.brand') AS brand,
    json_extract(attributes, '$.price') AS price
FROM products
WHERE json_extract(attributes, '$.in_stock') = true;

-- 使用 -> 和 ->> 运算符（3.38.0+）
SELECT name,
    attributes -> '$.brand' AS brand,       -- 返回 JSON 值（带引号的字符串）
    attributes ->> '$.specs.cpu' AS cpu      -- 返回 SQL 值（不带引号）
FROM products;

-- 嵌套 JSON 数组操作
SELECT name,
    json_extract(attributes, '$.colors[0]') AS first_color
FROM products;

-- JSON 数组包含判断
SELECT name
FROM products
WHERE json_extract(attributes, '$.colors') LIKE '%"银"%';

-- 或使用 json_each 展开数组
SELECT p.name, c.value AS color
FROM products p, json_each(p.attributes -> '$.colors') AS c
WHERE c.value = '银';

-- 更新 JSON 字段
UPDATE products
SET attributes = json_set(attributes, '$.price', 5499)
WHERE id = 1;

-- 合并 JSON
UPDATE products
SET attributes = json_patch(attributes, '{"warranty": "2年"}')
WHERE id = 1;

-- 删除 JSON 键
UPDATE products
SET attributes = json_remove(attributes, '$.colors')
WHERE id = 3;

-- 创建 JSON 对象和数组
SELECT json_object(
    'id', id,
    'name', name,
    'price', json_extract(attributes, '$.price')
) AS product_json
FROM products;

-- 聚合为 JSON 数组
SELECT json_group_array(
    json_object('id', id, 'name', name)
) AS all_products
FROM products;

-- JSON 验证
SELECT json_valid('[1,2,3]');  -- 1
SELECT json_valid('{bad json'); -- 0

-- 附加一个数据库
ATTACH DATABASE 'archive.db' AS archive;

-- 使用附加数据库中的表
CREATE TABLE archive.old_orders AS
SELECT * FROM main.orders WHERE order_date < '2023-01-01';

-- 跨库 JOIN
SELECT u.name, o.order_id, o.amount
FROM main.users u
JOIN archive.old_orders o ON u.id = o.user_id;

-- 附加更多数据库
ATTACH DATABASE 'reports.db' AS reports;
ATTACH DATABASE ':memory:' AS tempdb;

-- 查看所有已附加的数据库
SELECT * FROM pragma_database_list;
-- main|/path/to/mydb.db
-- archive|/path/to/archive.db
-- temp|/path/to/temp.db （SQLite 内部临时数据库）
-- reports|/path/to/reports.db
-- tempdb|（内存数据库）

-- 使用附加数据库名限定表
INSERT INTO tempdb.report_cache (query_name, result_json)
VALUES ('monthly_summary', '{"total": 12345}');

-- 分离数据库
DETACH DATABASE archive;
-- DETACH DATABASE tempdb;

// pubspec.yaml
// dependencies:
//   sqflite: ^2.3.0
//   path_provider: ^2.1.0

import 'package:sqflite/sqflite.dart';
import 'package:path/path.dart';

class DatabaseHelper {
  static final DatabaseHelper instance = DatabaseHelper._init();
  static Database? _database;

  DatabaseHelper._init();

  Future<Database> get database async {
    if (_database != null) return _database!;
    _database = await _initDB('app.db');
    return _database!;
  }

  Future<Database> _initDB(String filePath) async {
    final dbPath = await getDatabasesPath();
    final path = join(dbPath, filePath);

    return await openDatabase(
      path,
      version: 1,
      onCreate: _createDB,
      onUpgrade: _upgradeDB,
      // 优化设置
      readOnly: false,
      singleInstance: true,
    );
  }

  Future<void> _createDB(Database db, int version) async {
    await db.execute('''
      CREATE TABLE notes (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        title TEXT NOT NULL,
        content TEXT,
        created_at TEXT DEFAULT (datetime('now', 'localtime')),
        updated_at TEXT DEFAULT (datetime('now', 'localtime'))
      )
    ''');

    await db.execute('''
      CREATE INDEX idx_notes_created ON notes(created_at DESC)
    ''');
  }

  Future<int> insertNote(String title, String content) async {
    final db = await database;
    return await db.insert('notes', {
      'title': title,
      'content': content,
    });
  }

  Future<List<Map<String, dynamic>>> getAllNotes() async {
    final db = await database;
    return await db.query('notes', orderBy: 'created_at DESC');
  }
}

// build.gradle.kts
// implementation("androidx.room:room-runtime:2.6.1")
// annotationProcessor("androidx.room:room-compiler:2.6.1")

import androidx.room.*

@Entity(tableName = "tasks")
data class Task(
    @PrimaryKey(autoGenerate = true) val id: Int = 0,
    @ColumnInfo(name = "title") val title: String,
    @ColumnInfo(name = "completed") val completed: Boolean = false,
    @ColumnInfo(name = "created_at") val createdAt: Long = System.currentTimeMillis()
)

@Dao
interface TaskDao {
    @Query("SELECT * FROM tasks ORDER BY created_at DESC")
    suspend fun getAllTasks(): List<Task>

    @Insert(onConflict = OnConflictStrategy.REPLACE)
    suspend fun insertTask(task: Task): Long

    @Update
    suspend fun updateTask(task: Task)

    @Delete
    suspend fun deleteTask(task: Task)
}

@Database(entities = [Task::class], version = 1)
abstract class AppDatabase : RoomDatabase() {
    abstract fun taskDao(): TaskDao

    companion object {
        @Volatile
        private var INSTANCE: AppDatabase? = null

        fun getDatabase(context: Context): AppDatabase {
            return INSTANCE ?: synchronized(this) {
                val instance = Room.databaseBuilder(
                    context.applicationContext,
                    AppDatabase::class.java,
                    "tasks_database"
                ).build()
                INSTANCE = instance
                instance
            }
        }
    }
}

// Electron + better-sqlite3

const Database = require('better-sqlite3');
const path = require('path');
const { app } = require('electron');

class AppDatabase {
  constructor() {
    const dbPath = path.join(app.getPath('userData'), 'app.db');
    this.db = new Database(dbPath);
    this._init();
  }

  _init() {
    // 启用 WAL 模式以获得更好的并发性能
    this.db.pragma('journal_mode = WAL');
    this.db.pragma('foreign_keys = ON');

    this.db.exec(`
      CREATE TABLE IF NOT EXISTS projects (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        description TEXT,
        created_at TEXT DEFAULT (datetime('now')),
        updated_at TEXT DEFAULT (datetime('now'))
      );

      CREATE TABLE IF NOT EXISTS documents (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        project_id INTEGER NOT NULL,
        title TEXT NOT NULL,
        content TEXT,
        created_at TEXT DEFAULT (datetime('now')),
        updated_at TEXT DEFAULT (datetime('now')),
        FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE
      );

      CREATE INDEX IF NOT EXISTS idx_documents_project
        ON documents(project_id);
    `);
  }

  // 准备语句缓存 —— 提升重复查询性能
  _prepare(sql) {
    if (!this._stmtCache) this._stmtCache = new Map();
    if (!this._stmtCache.has(sql)) {
      this._stmtCache.set(sql, this.db.prepare(sql));
    }
    return this._stmtCache.get(sql);
  }

  getProjects() {
    return this._prepare('SELECT * FROM projects ORDER BY updated_at DESC').all();
  }

  createProject(name, description) {
    const info = this._prepare(
      'INSERT INTO projects (name, description) VALUES (?, ?)'
    ).run(name, description);
    return info.lastInsertRowid;
  }

  // 事务操作
  createDocumentWithTransaction(projectId, title, content) {
    const transaction = this.db.transaction(() => {
      const info = this._prepare(
        'INSERT INTO documents (project_id, title, content) VALUES (?, ?, ?)'
      ).run(projectId, title, content);

      this._prepare(
        'UPDATE projects SET updated_at = datetime(\'now\') WHERE id = ?'
      ).run(projectId);

      return info.lastInsertRowid;
    });

    return transaction();
  }

  close() {
    this.db.close();
  }
}

module.exports = AppDatabase;

// C 语言嵌入式 SQLite 示例（ESP32 / ARM Linux）

#include <sqlite3.h>
#include <stdio.h>

int main() {
    sqlite3 *db;
    char *err_msg = NULL;

    // 1. 打开数据库（存储在闪存或 SD 卡）
    int rc = sqlite3_open("/data/sensor.db", &db);
    if (rc != SQLITE_OK) {
        fprintf(stderr, "无法打开数据库: %s\n", sqlite3_errmsg(db));
        return 1;
    }

    // 2. 嵌入式环境优化 PRAGMA
    const char *pragmas[] = {
        "PRAGMA journal_mode = WAL",          // 减少写入放大
        "PRAGMA synchronous = OFF",           // 承受掉电风险时关闭同步
        "PRAGMA cache_size = -2000",          // 2MB 缓存（嵌入式够用）
        "PRAGMA temp_store = MEMORY",         // 临时数据放内存
        "PRAGMA page_size = 512",             // 小页面节省闪存写入
        "PRAGMA auto_vacuum = INCREMENTAL",   // 增量回收空间
    };

    for (int i = 0; i < sizeof(pragmas)/sizeof(pragmas[0]); i++) {
        rc = sqlite3_exec(db, pragmas[i], 0, 0, &err_msg);
        if (rc != SQLITE_OK) {
            fprintf(stderr, "PRAGMA 错误: %s\n", err_msg);
            sqlite3_free(err_msg);
        }
    }

    // 3. 创建表
    const char *sql_create = 
        "CREATE TABLE IF NOT EXISTS sensor_data ("
        "  id INTEGER PRIMARY KEY AUTOINCREMENT,"
        "  sensor_id INTEGER NOT NULL,"
        "  temperature REAL,"
        "  humidity REAL,"
        "  pressure REAL,"
        "  recorded_at TEXT DEFAULT (datetime('now'))"
        ");";

    rc = sqlite3_exec(db, sql_create, 0, 0, &err_msg);
    if (rc != SQLITE_OK) {
        fprintf(stderr, "建表错误: %s\n", err_msg);
        sqlite3_free(err_msg);
    }

    // 4. 批量插入（事务包裹）
    sqlite3_exec(db, "BEGIN TRANSACTION", 0, 0, 0);

    sqlite3_stmt *stmt;
    sqlite3_prepare_v2(db,
        "INSERT INTO sensor_data (sensor_id, temperature, humidity, pressure) "
        "VALUES (?, ?, ?, ?)",
        -1, &stmt, 0);

    // 模拟 100 条传感器读数
    for (int i = 0; i < 100; i++) {
        sqlite3_bind_int(stmt, 1, 1);
        sqlite3_bind_double(stmt, 2, 25.0 + (i % 10) * 0.5);
        sqlite3_bind_double(stmt, 3, 60.0 + (i % 20));
        sqlite3_bind_double(stmt, 4, 1013.25 + (i % 5));

        sqlite3_step(stmt);
        sqlite3_reset(stmt);
    }

    sqlite3_exec(db, "COMMIT", 0, 0, 0);
    sqlite3_finalize(stmt);

    // 5. 定期 VACUUM 控制闪存磨损
    // sqlite3_exec(db, "VACUUM", 0, 0, 0);

    sqlite3_close(db);
    return 0;
}

# Python 单元测试示例
import sqlite3
import unittest

class TestUserRepository(unittest.TestCase):

    def setUp(self):
        """每个测试方法前执行：创建内存数据库"""
        self.conn = sqlite3.connect(':memory:')
        self.conn.execute("PRAGMA foreign_keys = ON")
        self.conn.executescript('''
            CREATE TABLE users (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                username TEXT NOT NULL UNIQUE,
                email TEXT NOT NULL UNIQUE
            );
            CREATE TABLE posts (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                user_id INTEGER NOT NULL,
                title TEXT NOT NULL,
                content TEXT,
                FOREIGN KEY (user_id) REFERENCES users(id)
            );
        ''')

    def tearDown(self):
        """每个测试方法后执行：关闭连接"""
        self.conn.close()

    def test_insert_user(self):
        self.conn.execute(
            "INSERT INTO users (username, email) VALUES (?, ?)",
            ('alice', 'alice@example.com')
        )
        cursor = self.conn.execute("SELECT * FROM users WHERE username = ?", ('alice',))
        user = cursor.fetchone()
        self.assertIsNotNone(user)
        self.assertEqual(user[1], 'alice')

    def test_unique_username(self):
        self.conn.execute(
            "INSERT INTO users (username, email) VALUES (?, ?)",
            ('bob', 'bob@example.com')
        )
        with self.assertRaises(sqlite3.IntegrityError):
            self.conn.execute(
                "INSERT INTO users (username, email) VALUES (?, ?)",
                ('bob', 'bob2@example.com')
            )

    def test_foreign_key_cascade(self):
        # 插入用户
        self.conn.execute(
            "INSERT INTO users (username, email) VALUES (?, ?)",
            ('charlie', 'charlie@example.com')
        )
        user_id = self.conn.execute(
            "SELECT id FROM users WHERE username = ?", ('charlie',)
        ).fetchone()[0]

        # 插入文章
        self.conn.execute(
            "INSERT INTO posts (user_id, title) VALUES (?, ?)",
            (user_id, '测试文章')
        )

        # 验证级联删除（需要设置 PRAGMA foreign_keys = ON）
        self.conn.execute("DELETE FROM users WHERE id = ?", (user_id,))
        posts = self.conn.execute(
            "SELECT * FROM posts WHERE user_id = ?", (user_id,)
        ).fetchall()
        self.assertEqual(len(posts), 0)

if __name__ == '__main__':
    unittest.main()

<!-- 浏览器端 HTML + sql.js 示例 -->
<!DOCTYPE html>
<html lang="zh-CN">
<head>
    <meta charset="UTF-8">
    <title>sql.js 浏览器数据库演示</title>
    <script src="https://cdn.jsdelivr.net/npm/sql.js@1.11/dist/sql-wasm.js"></script>
</head>
<body>
    <h1>浏览器内 SQLite 数据库</h1>

    <div>
        <button onclick="initDB()">初始化数据库</button>
        <button onclick="queryData()">查询数据</button>
        <button onclick="exportDB()">导出数据库文件</button>
        <button onclick="importDB()">导入数据库文件</button>
        <input type="file" id="fileInput" accept=".db" style="display:none">
    </div>

    <pre id="output" style="background:#f4f4f4; padding:16px; margin-top:16px; font-size:14px;"></pre>

    <script>
        let db = null;

        function log(msg) {
            document.getElementById('output').textContent += msg + '\n';
        }

        async function initDB() {
            const SQL = await initSqlJs({
                locateFile: file => `https://cdn.jsdelivr.net/npm/sql.js@1.11/dist/${file}`
            });

            db = new SQL.Database();

            // 执行迁移
            db.run(`
                CREATE TABLE IF NOT EXISTS todos (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    title TEXT NOT NULL,
                    completed INTEGER DEFAULT 0,
                    created_at TEXT DEFAULT (datetime('now'))
                )
            `);

            // 插入示例数据
            db.run("INSERT INTO todos (title) VALUES ('学习 SQLite')");
            db.run("INSERT INTO todos (title) VALUES ('学习 sql.js')");
            db.run("INSERT INTO todos (title) VALUES ('写一篇技术文章')");

            log('✅ 数据库已初始化');
            queryData();
        }

        function queryData() {
            if (!db) {
                log('❌ 请先初始化数据库');
                return;
            }

            const stmt = db.prepare("SELECT * FROM todos ORDER BY created_at DESC");
            log('\n📋 待办事项列表:');
            while (stmt.step()) {
                const row = stmt.getAsObject();
                log(`  [${row.id}] ${row.title} — ${row.completed ? '✅ 已完成' : '⬜ 待完成'} (${row.created_at})`);
            }
            stmt.free();
        }

        function exportDB() {
            if (!db) return;
            const data = db.export();
            const blob = new Blob([data], {type: 'application/x-sqlite3'});
            const url = URL.createObjectURL(blob);

            const a = document.createElement('a');
            a.href = url;
            a.download = 'browser_data.db';
            a.click();

            URL.revokeObjectURL(url);
            log('💾 数据库已导出为 browser_data.db');
        }

        function importDB() {
            document.getElementById('fileInput').click();
        }

        document.getElementById('fileInput').addEventListener('change', async (e) => {
            const file = e.target.files[0];
            if (!file) return;

            const reader = new FileReader();
            reader.onload = async function() {
                const SQL = await initSqlJs({
                    locateFile: file => `https://cdn.jsdelivr.net/npm/sql.js@1.11/dist/${file}`
                });

                const buffer = new Uint8Array(this.result);
                db = new SQL.Database(buffer);
                log('📂 已导入数据库文件');
                queryData();
            };
            reader.readAsArrayBuffer(file);
        });
    </script>
</body>
</html>