SQLite 关系型数据存储：一对多与多对多关系完整指南

-- 主表：部门表
CREATE TABLE departments (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name VARCHAR(100) NOT NULL UNIQUE,
    description TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- 从表：员工表（包含外键）
CREATE TABLE employees (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    department_id INTEGER NOT NULL,
    position VARCHAR(100),
    salary DECIMAL(10,2),
    hire_date DATE,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    -- 外键约束
    FOREIGN KEY (department_id) REFERENCES departments(id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

-- 优化：添加索引
CREATE INDEX idx_employees_department_id ON employees(department_id);
CREATE INDEX idx_employees_email ON employees(email);

npm install sqlite3 better-sqlite3
# 或者使用更现代的 better-sqlite3
npm install better-sqlite3

// database.js - 数据库连接管理
const Database = require("better-sqlite3")
const path = require("path")

class DatabaseManager {
  constructor(dbPath = "company.db") {
    this.db = new Database(dbPath)
    this.initializeTables()
  }

  initializeTables() {
    // 启用外键约束
    this.db.pragma("foreign_keys = ON")

    // 创建部门表
    this.db.exec(`
            CREATE TABLE IF NOT EXISTS departments (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                name VARCHAR(100) NOT NULL UNIQUE,
                description TEXT,
                created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
            );
        `)

    // 创建员工表
    this.db.exec(`
            CREATE TABLE IF NOT EXISTS employees (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                name VARCHAR(100) NOT NULL,
                email VARCHAR(255) UNIQUE NOT NULL,
                department_id INTEGER NOT NULL,
                position VARCHAR(100),
                salary DECIMAL(10,2),
                hire_date DATE,
                created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY (department_id) REFERENCES departments(id)
                    ON DELETE CASCADE
                    ON UPDATE CASCADE
            );
        `)

    // 创建索引
    this.db.exec(`
            CREATE INDEX IF NOT EXISTS idx_employees_department_id ON employees(department_id);
            CREATE INDEX IF NOT EXISTS idx_employees_email ON employees(email);
        `)
  }

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

module.exports = DatabaseManager

// departmentService.js - 部门服务
class DepartmentService {
  constructor(database) {
    this.db = database
  }

  // 创建部门
  createDepartment(departmentData) {
    const { name, description } = departmentData

    const stmt = this.db.prepare(`
            INSERT INTO departments (name, description)
            VALUES (?, ?)
        `)

    try {
      const result = stmt.run(name, description)
      return this.getDepartmentById(result.lastInsertRowid)
    } catch (error) {
      if (error.code === "SQLITE_CONSTRAINT_UNIQUE") {
        throw new Error(`部门 "${name}" 已存在`)
      }
      throw error
    }
  }

  // 获取部门及其员工
  getDepartmentWithEmployees(departmentId) {
    const stmt = this.db.prepare(`
            SELECT
                d.id as department_id,
                d.name as department_name,
                d.description,
                e.id as employee_id,
                e.name as employee_name,
                e.email,
                e.position,
                e.salary,
                e.hire_date
            FROM departments d
            LEFT JOIN employees e ON d.id = e.department_id
            WHERE d.id = ?
            ORDER BY e.name
        `)

    const rows = stmt.all(departmentId)

    if (rows.length === 0) {
      return null
    }

    // 重构数据结构
    const department = {
      id: rows[0].department_id,
      name: rows[0].department_name,
      description: rows[0].description,
      employees: []
    }

    rows.forEach((row) => {
      if (row.employee_id) {
        department.employees.push({
          id: row.employee_id,
          name: row.employee_name,
          email: row.email,
          position: row.position,
          salary: row.salary,
          hire_date: row.hire_date
        })
      }
    })

    return department
  }

  // 获取部门统计信息
  getDepartmentStats() {
    const stmt = this.db.prepare(`
            SELECT
                d.id,
                d.name,
                d.description,
                COUNT(e.id) as employee_count,
                AVG(e.salary) as average_salary,
                MAX(e.salary) as max_salary,
                MIN(e.salary) as min_salary
            FROM departments d
            LEFT JOIN employees e ON d.id = e.department_id
            GROUP BY d.id, d.name, d.description
            ORDER BY employee_count DESC
        `)

    return stmt.all()
  }

  // 更新部门
  updateDepartment(departmentId, updateData) {
    const { name, description } = updateData

    const stmt = this.db.prepare(`
            UPDATE departments
            SET name = COALESCE(?, name),
                description = COALESCE(?, description),
                updated_at = CURRENT_TIMESTAMP
            WHERE id = ?
        `)

    const result = stmt.run(name, description, departmentId)

    if (result.changes === 0) {
      throw new Error(`部门 ID ${departmentId} 不存在`)
    }

    return this.getDepartmentById(departmentId)
  }

  // 删除部门（级联删除员工）
  deleteDepartment(departmentId) {
    const stmt = this.db.prepare("DELETE FROM departments WHERE id = ?")
    const result = stmt.run(departmentId)

    if (result.changes === 0) {
      throw new Error(`部门 ID ${departmentId} 不存在`)
    }

    return { deleted: true, departmentId }
  }

  getDepartmentById(departmentId) {
    const stmt = this.db.prepare("SELECT * FROM departments WHERE id = ?")
    return stmt.get(departmentId)
  }
}

module.exports = DepartmentService

// employeeService.js - 员工服务
class EmployeeService {
  constructor(database) {
    this.db = database
  }

  // 创建员工
  createEmployee(employeeData) {
    const { name, email, department_id, position, salary, hire_date } = employeeData

    // 验证部门存在性
    const deptStmt = this.db.prepare("SELECT id FROM departments WHERE id = ?")
    const department = deptStmt.get(department_id)

    if (!department) {
      throw new Error(`部门 ID ${department_id} 不存在`)
    }

    const stmt = this.db.prepare(`
            INSERT INTO employees (name, email, department_id, position, salary, hire_date)
            VALUES (?, ?, ?, ?, ?, ?)
        `)

    try {
      const result = stmt.run(
        name,
        email,
        department_id,
        position,
        salary,
        hire_date
      )
      return this.getEmployeeById(result.lastInsertRowid)
    } catch (error) {
      if (error.code === "SQLITE_CONSTRAINT_UNIQUE") {
        throw new Error(`邮箱 "${email}" 已被使用`)
      }
      throw error
    }
  }

  // 获取员工详情（包含部门信息）
  getEmployeeWithDepartment(employeeId) {
    const stmt = this.db.prepare(`
            SELECT
                e.*,
                d.name as department_name,
                d.description as department_description
            FROM employees e
            JOIN departments d ON e.department_id = d.id
            WHERE e.id = ?
        `)

    return stmt.get(employeeId)
  }

  // 更新员工部门
  transferEmployee(employeeId, newDepartmentId) {
    // 验证新部门存在性
    const deptStmt = this.db.prepare("SELECT id FROM departments WHERE id = ?")
    const department = deptStmt.get(newDepartmentId)

    if (!department) {
      throw new Error(`部门 ID ${newDepartmentId} 不存在`)
    }

    const stmt = this.db.prepare(`
            UPDATE employees
            SET department_id = ?, updated_at = CURRENT_TIMESTAMP
            WHERE id = ?
        `)

    const result = stmt.run(newDepartmentId, employeeId)

    if (result.changes === 0) {
      throw new Error(`员工 ID ${employeeId} 不存在`)
    }

    return this.getEmployeeById(employeeId)
  }

  // 分页查询员工
  getEmployeesPaged(page = 1, pageSize = 10, departmentId = null) {
    const offset = (page - 1) * pageSize

    let whereClause = ""
    let params = []

    if (departmentId) {
      whereClause = "WHERE e.department_id = ?"
      params.push(departmentId)
    }

    const countStmt = this.db.prepare(`
            SELECT COUNT(*) as total
            FROM employees e
            ${whereClause}
        `)

    const dataStmt = this.db.prepare(`
            SELECT
                e.*,
                d.name as department_name
            FROM employees e
            JOIN departments d ON e.department_id = d.id
            ${whereClause}
            ORDER BY e.name
            LIMIT ? OFFSET ?
        `)

    const total = countStmt.get(...params)?.total || 0
    params.push(pageSize, offset)
    const data = dataStmt.all(...params)

    return {
      data,
      pagination: {
        page,
        pageSize,
        total,
        totalPages: Math.ceil(total / pageSize)
      }
    }
  }

  getEmployeeById(employeeId) {
    const stmt = this.db.prepare("SELECT * FROM employees WHERE id = ?")
    return stmt.get(employeeId)
  }
}

module.exports = EmployeeService

// app.js - 应用主文件
const DatabaseManager = require("./database")
const DepartmentService = require("./departmentService")
const EmployeeService = require("./employeeService")

async function main() {
  // 初始化数据库
  const dbManager = new DatabaseManager("company.db")
  const departmentService = new DepartmentService(dbManager.db)
  const employeeService = new EmployeeService(dbManager.db)

  try {
    // 1. 创建部门
    console.log("=== 创建部门 ===")
    const techDept = departmentService.createDepartment({
      name: "技术部",
      description: "负责产品研发和技术创新"
    })

    const salesDept = departmentService.createDepartment({
      name: "销售部",
      description: "负责产品销售和客户关系维护"
    })

    console.log("技术部创建成功:", techDept)
    console.log("销售部创建成功:", salesDept)

    // 2. 创建员工
    console.log("\n=== 创建员工 ===")
    const employee1 = employeeService.createEmployee({
      name: "张三",
      email: "zhangsan@company.com",
      department_id: techDept.id,
      position: "高级工程师",
      salary: 15000,
      hire_date: "2023-01-15"
    })

    const employee2 = employeeService.createEmployee({
      name: "李四",
      email: "lisi@company.com",
      department_id: techDept.id,
      position: "前端工程师",
      salary: 12000,
      hire_date: "2023-03-20"
    })

    const employee3 = employeeService.createEmployee({
      name: "王五",
      email: "wangwu@company.com",
      department_id: salesDept.id,
      position: "销售经理",
      salary: 10000,
      hire_date: "2022-11-10"
    })

    console.log("员工创建成功:", employee1, employee2, employee3)

    // 3. 查询部门及其员工
    console.log("\n=== 查询技术部及其员工 ===")
    const techDeptWithEmployees = departmentService.getDepartmentWithEmployees(
      techDept.id
    )
    console.log(JSON.stringify(techDeptWithEmployees, null, 2))

    // 4. 部门统计
    console.log("\n=== 部门统计 ===")
    const stats = departmentService.getDepartmentStats()
    console.log("部门统计:", stats)

    // 5. 分页查询员工
    console.log("\n=== 员工分页查询 ===")
    const employeesPage = employeeService.getEmployeesPaged(1, 2)
    console.log("员工分页数据:", employeesPage)

    // 6. 员工调岗
    console.log("\n=== 员工调岗 ===")
    const transferredEmployee = employeeService.transferEmployee(
      employee2.id,
      salesDept.id
    )
    console.log("员工调岗成功:", transferredEmployee)

    // 7. 验证调岗结果
    console.log("\n=== 验证调岗结果 ===")
    const updatedSalesDept = departmentService.getDepartmentWithEmployees(
      salesDept.id
    )
    console.log("销售部最新员工列表:", updatedSalesDept)
  } catch (error) {
    console.error("操作失败:", error.message)
  } finally {
    dbManager.close()
  }
}

// 运行应用
if (require.main === module) {
  main()
}

module.exports = { DatabaseManager, DepartmentService, EmployeeService }

-- 文章表
CREATE TABLE articles (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    title VARCHAR(200) NOT NULL,
    content TEXT,
    author VARCHAR(100),
    publish_date DATE,
    status VARCHAR(20) DEFAULT 'draft',
    view_count INTEGER DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- 标签表
CREATE TABLE tags (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name VARCHAR(50) NOT NULL UNIQUE,
    description TEXT,
    color VARCHAR(7) DEFAULT '#007bff',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- 文章标签关联表（中间表）
CREATE TABLE article_tags (
    article_id INTEGER NOT NULL,
    tag_id INTEGER NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (article_id, tag_id),
    FOREIGN KEY (article_id) REFERENCES articles(id)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
    FOREIGN KEY (tag_id) REFERENCES tags(id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

-- 优化索引
CREATE INDEX idx_article_tags_article_id ON article_tags(article_id);
CREATE INDEX idx_article_tags_tag_id ON article_tags(tag_id);
CREATE INDEX idx_articles_status ON articles(status);
CREATE INDEX idx_articles_publish_date ON articles(publish_date);

// articleService.js - 文章服务
class ArticleService {
  constructor(database) {
    this.db = database
  }

  // 创建文章（带标签）
  createArticle(articleData) {
    const { title, content, author, publish_date, status, tags } = articleData

    // 开始事务
    const transaction = this.db.transaction(() => {
      // 创建文章
      const articleStmt = this.db.prepare(`
                INSERT INTO articles (title, content, author, publish_date, status)
                VALUES (?, ?, ?, ?, ?)
            `)

      const articleResult = articleStmt.run(
        title,
        content,
        author,
        publish_date,
        status
      )
      const articleId = articleResult.lastInsertRowid

      // 处理标签
      if (tags && tags.length > 0) {
        this.addTagsToArticle(articleId, tags)
      }

      return this.getArticleById(articleId)
    })

    return transaction()
  }

  // 为文章添加标签
  addTagsToArticle(articleId, tagIds) {
    // 验证标签存在性
    const tagStmt = this.db.prepare(`
            SELECT id FROM tags WHERE id IN (${tagIds.map(() => "?").join(",")})
        `)
    const existingTags = tagStmt.all(...tagIds)

    if (existingTags.length !== tagIds.length) {
      throw new Error("部分标签不存在")
    }

    // 批量插入关联关系
    const insertStmt = this.db.prepare(`
            INSERT OR IGNORE INTO article_tags (article_id, tag_id)
            VALUES (?, ?)
        `)

    const insertMany = this.db.transaction((articleId, tagIds) => {
      for (const tagId of tagIds) {
        insertStmt.run(articleId, tagId)
      }
    })

    insertMany(articleId, tagIds)
  }

  // 获取文章详情（含标签）
  getArticleById(articleId) {
    // 获取文章基本信息
    const articleStmt = this.db.prepare("SELECT * FROM articles WHERE id = ?")
    const article = articleStmt.get(articleId)

    if (!article) {
      return null
    }

    // 获取关联标签
    const tagsStmt = this.db.prepare(`
            SELECT t.*
            FROM tags t
            JOIN article_tags at ON t.id = at.tag_id
            WHERE at.article_id = ?
            ORDER BY t.name
        `)

    const tags = tagsStmt.all(articleId)

    return {
      ...article,
      tags
    }
  }

  // 根据标签查询文章
  getArticlesByTag(tagId, page = 1, pageSize = 10) {
    const offset = (page - 1) * pageSize

    // 获取总数
    const countStmt = this.db.prepare(`
            SELECT COUNT(DISTINCT a.id) as total
            FROM articles a
            JOIN article_tags at ON a.id = at.article_id
            WHERE at.tag_id = ? AND a.status = 'published'
        `)

    // 获取分页数据
    const dataStmt = this.db.prepare(`
            SELECT DISTINCT a.*,
                   GROUP_CONCAT(t.name, ', ') as tag_names
            FROM articles a
            JOIN article_tags at ON a.id = at.article_id
            JOIN tags t ON at.tag_id = t.id
            WHERE at.tag_id = ? AND a.status = 'published'
            GROUP BY a.id
            ORDER BY a.publish_date DESC
            LIMIT ? OFFSET ?
        `)

    const total = countStmt.get(tagId)?.total || 0
    const data = dataStmt.all(tagId, pageSize, offset)

    return {
      data,
      pagination: {
        page,
        pageSize,
        total,
        totalPages: Math.ceil(total / pageSize)
      }
    }
  }

  // 获取热门标签
  getPopularTags(limit = 10) {
    const stmt = this.db.prepare(`
            SELECT
                t.*,
                COUNT(at.article_id) as article_count
            FROM tags t
            JOIN article_tags at ON t.id = at.tag_id
            JOIN articles a ON at.article_id = a.id
            WHERE a.status = 'published'
            GROUP BY t.id, t.name, t.description, t.color
            ORDER BY article_count DESC, t.name
            LIMIT ?
        `)

    return stmt.all(limit)
  }

  // 更新文章标签
  updateArticleTags(articleId, newTagIds) {
    const transaction = this.db.transaction(() => {
      // 删除现有标签关联
      const deleteStmt = this.db.prepare(
        "DELETE FROM article_tags WHERE article_id = ?"
      )
      deleteStmt.run(articleId)

      // 添加新标签
      if (newTagIds && newTagIds.length > 0) {
        this.addTagsToArticle(articleId, newTagIds)
      }

      return this.getArticleById(articleId)
    })

    return transaction()
  }

  // 删除文章
  deleteArticle(articleId) {
    const stmt = this.db.prepare("DELETE FROM articles WHERE id = ?")
    const result = stmt.run(articleId)

    if (result.changes === 0) {
      throw new Error(`文章 ID ${articleId} 不存在`)
    }

    return { deleted: true, articleId }
  }
}

module.exports = ArticleService

// tagService.js - 标签服务
class TagService {
  constructor(database) {
    this.db = database
  }

  // 创建标签
  createTag(tagData) {
    const { name, description, color } = tagData

    const stmt = this.db.prepare(`
            INSERT INTO tags (name, description, color)
            VALUES (?, ?, ?)
        `)

    try {
      const result = stmt.run(name, description, color)
      return this.getTagById(result.lastInsertRowid)
    } catch (error) {
      if (error.code === "SQLITE_CONSTRAINT_UNIQUE") {
        throw new Error(`标签 "${name}" 已存在`)
      }
      throw error
    }
  }

  // 批量创建标签
  createTagsBatch(tagsData) {
    const transaction = this.db.transaction(() => {
      const stmt = this.db.prepare(`
                INSERT OR IGNORE INTO tags (name, description, color)
                VALUES (?, ?, ?)
            `)

      const results = []
      for (const tagData of tagsData) {
        const result = stmt.run(tagData.name, tagData.description, tagData.color)
        if (result.changes > 0) {
          results.push(this.getTagById(result.lastInsertRowid))
        }
      }

      return results
    })

    return transaction()
  }

  // 获取标签及其文章
  getTagWithArticles(tagId) {
    // 获取标签信息
    const tagStmt = this.db.prepare("SELECT * FROM tags WHERE id = ?")
    const tag = tagStmt.get(tagId)

    if (!tag) {
      return null
    }

    // 获取关联文章
    const articlesStmt = this.db.prepare(`
            SELECT a.*
            FROM articles a
            JOIN article_tags at ON a.id = at.article_id
            WHERE at.tag_id = ? AND a.status = 'published'
            ORDER BY a.publish_date DESC
        `)

    const articles = articlesStmt.all(tagId)

    return {
      ...tag,
      articles,
      article_count: articles.length
    }
  }

  // 获取所有标签（含统计）
  getAllTagsWithStats() {
    const stmt = this.db.prepare(`
            SELECT
                t.*,
                COUNT(DISTINCT a.id) as article_count,
                MAX(a.publish_date) as latest_article_date
            FROM tags t
            LEFT JOIN article_tags at ON t.id = at.tag_id
            LEFT JOIN articles a ON at.article_id = a.id AND a.status = 'published'
            GROUP BY t.id, t.name, t.description, t.color
            ORDER BY article_count DESC, t.name
        `)

    return stmt.all()
  }

  // 搜索标签
  searchTags(query, limit = 10) {
    const stmt = this.db.prepare(`
            SELECT
                t.*,
                COUNT(at.article_id) as article_count
            FROM tags t
            LEFT JOIN article_tags at ON t.id = at.tag_id
            LEFT JOIN articles a ON at.article_id = a.id AND a.status = 'published'
            WHERE t.name LIKE ? OR t.description LIKE ?
            GROUP BY t.id, t.name, t.description, t.color
            ORDER BY article_count DESC, t.name
            LIMIT ?
        `)

    const searchPattern = `%${query}%`
    return stmt.all(searchPattern, searchPattern, limit)
  }

  // 更新标签
  updateTag(tagId, updateData) {
    const { name, description, color } = updateData

    const stmt = this.db.prepare(`
            UPDATE tags
            SET name = COALESCE(?, name),
                description = COALESCE(?, description),
                color = COALESCE(?, color)
            WHERE id = ?
        `)

    const result = stmt.run(name, description, color, tagId)

    if (result.changes === 0) {
      throw new Error(`标签 ID ${tagId} 不存在`)
    }

    return this.getTagById(tagId)
  }

  // 删除标签
  deleteTag(tagId) {
    const stmt = this.db.prepare("DELETE FROM tags WHERE id = ?")
    const result = stmt.run(tagId)

    if (result.changes === 0) {
      throw new Error(`标签 ID ${tagId} 不存在`)
    }

    return { deleted: true, tagId }
  }

  getTagById(tagId) {
    const stmt = this.db.prepare("SELECT * FROM tags WHERE id = ?")
    return stmt.get(tagId)
  }
}

module.exports = TagService

// blogApp.js - 博客应用示例
const Database = require("better-sqlite3")
const ArticleService = require("./articleService")
const TagService = require("./tagService")

class BlogManager {
  constructor(dbPath = "blog.db") {
    this.db = new Database(dbPath)
    this.db.pragma("foreign_keys = ON")
    this.initializeTables()

    this.articleService = new ArticleService(this.db)
    this.tagService = new TagService(this.db)
  }

  initializeTables() {
    // 创建文章表
    this.db.exec(`
            CREATE TABLE IF NOT EXISTS articles (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                title VARCHAR(200) NOT NULL,
                content TEXT,
                author VARCHAR(100),
                publish_date DATE,
                status VARCHAR(20) DEFAULT 'draft',
                view_count INTEGER DEFAULT 0,
                created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
            );
        `)

    // 创建标签表
    this.db.exec(`
            CREATE TABLE IF NOT EXISTS tags (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                name VARCHAR(50) NOT NULL UNIQUE,
                description TEXT,
                color VARCHAR(7) DEFAULT '#007bff',
                created_at DATETIME DEFAULT CURRENT_TIMESTAMP
            );
        `)

    // 创建关联表
    this.db.exec(`
            CREATE TABLE IF NOT EXISTS article_tags (
                article_id INTEGER NOT NULL,
                tag_id INTEGER NOT NULL,
                created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                PRIMARY KEY (article_id, tag_id),
                FOREIGN KEY (article_id) REFERENCES articles(id)
                    ON DELETE CASCADE
                    ON UPDATE CASCADE,
                FOREIGN KEY (tag_id) REFERENCES tags(id)
                    ON DELETE CASCADE
                    ON UPDATE CASCADE
            );
        `)

    // 创建索引
    this.db.exec(`
            CREATE INDEX IF NOT EXISTS idx_article_tags_article_id ON article_tags(article_id);
            CREATE INDEX IF NOT EXISTS idx_article_tags_tag_id ON article_tags(tag_id);
            CREATE INDEX IF NOT EXISTS idx_articles_status ON articles(status);
            CREATE INDEX IF NOT EXISTS idx_articles_publish_date ON articles(publish_date);
        `)
  }

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

// 演示应用
async function main() {
  const blogManager = new BlogManager("blog.db")

  try {
    console.log("=== 创建标签 ===")
    // 批量创建标签
    const tags = blogManager.tagService.createTagsBatch([
      { name: "JavaScript", description: "JavaScript 相关文章", color: "#f7df1e" },
      { name: "Node.js", description: "Node.js 技术文章", color: "#339933" },
      { name: "数据库", description: "数据库相关技术", color: "#336791" },
      { name: "前端", description: "前端开发技术", color: "#61dafb" },
      { name: "后端", description: "后端开发技术", color: "#764abc" },
      { name: "教程", description: "技术教程和指南", color: "#ff6b6b" }
    ])

    console.log("标签创建成功，数量:", tags.length)

    console.log("\n=== 创建文章 ===")
    // 创建文章并关联标签
    const article1 = blogManager.articleService.createArticle({
      title: "Node.js SQLite 完整指南",
      content: "SQLite 是一个轻量级的关系型数据库，非常适合 Node.js 应用...",
      author: "张三",
      publish_date: "2024-01-15",
      status: "published",
      tags: [1, 2, 3, 6] // JavaScript, Node.js, 数据库, 教程
    })

    const article2 = blogManager.articleService.createArticle({
      title: "前端性能优化最佳实践",
      content: "前端性能优化是提升用户体验的关键...",
      author: "李四",
      publish_date: "2024-01-20",
      status: "published",
      tags: [1, 4, 6] // JavaScript, 前端, 教程
    })

    console.log("文章创建成功:", article1.title, article2.title)

    console.log("\n=== 查询文章详情 ===")
    // 查询文章详情
    const detailedArticle = blogManager.articleService.getArticleById(1)
    console.log("文章详情:", JSON.stringify(detailedArticle, null, 2))

    console.log("\n=== 根据标签查询文章 ===")
    // 根据标签查询文章
    const jsArticles = blogManager.articleService.getArticlesByTag(1, 1, 10)
    console.log("JavaScript 标签的文章:", jsArticles)

    console.log("\n=== 获取热门标签 ===")
    // 获取热门标签
    const popularTags = blogManager.articleService.getPopularTags(5)
    console.log("热门标签:", popularTags)

    console.log("\n=== 更新文章标签 ===")
    // 更新文章标签
    const updatedArticle = blogManager.articleService.updateArticleTags(1, [1, 2, 6])
    console.log(
      "文章标签更新成功:",
      updatedArticle.tags.map((tag) => tag.name)
    )

    console.log("\n=== 搜索标签 ===")
    // 搜索标签
    const searchResults = blogManager.tagService.searchTags("Java")
    console.log("标签搜索结果:", searchResults)

    console.log("\n=== 获取所有标签统计 ===")
    // 获取所有标签统计
    const allTagsStats = blogManager.tagService.getAllTagsWithStats()
    console.log("标签统计:", allTagsStats)
  } catch (error) {
    console.error("操作失败:", error.message)
  } finally {
    blogManager.close()
  }
}

// 运行演示
if (require.main === module) {
  main()
}

module.exports = BlogManager

-- 分类层级结构（支持无限层级）
CREATE TABLE categories (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name VARCHAR(100) NOT NULL,
    parent_id INTEGER,
    level INTEGER DEFAULT 0,
    path VARCHAR(500),
    FOREIGN KEY (parent_id) REFERENCES categories(id) ON DELETE CASCADE
);

-- 创建索引
CREATE INDEX idx_categories_parent_id ON categories(parent_id);
CREATE INDEX idx_categories_path ON categories(path);

-- 用户-角色-权限三级关系
CREATE TABLE users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL
);

CREATE TABLE roles (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name VARCHAR(50) UNIQUE NOT NULL,
    description TEXT
);

CREATE TABLE permissions (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name VARCHAR(50) UNIQUE NOT NULL,
    resource VARCHAR(100),
    action VARCHAR(50)
);

-- 用户-角色关联
CREATE TABLE user_roles (
    user_id INTEGER NOT NULL,
    role_id INTEGER NOT NULL,
    assigned_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (user_id, role_id),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE CASCADE
);

-- 角色-权限关联
CREATE TABLE role_permissions (
    role_id INTEGER NOT NULL,
    permission_id INTEGER NOT NULL,
    PRIMARY KEY (role_id, permission_id),
    FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE CASCADE,
    FOREIGN KEY (permission_id) REFERENCES permissions(id) ON DELETE CASCADE
);

-- 复合索引
CREATE INDEX idx_articles_author_status_date ON articles(author, status, publish_date);

-- 部分索引（只对满足条件的数据建立索引）
CREATE INDEX idx_articles_published ON articles(publish_date)
WHERE status = 'published';

-- 覆盖索引（包含查询所需的所有字段）
CREATE INDEX idx_employees_covering ON employees(department_id, name, email, position);

// 使用子查询优化
const optimizedQuery = `
    SELECT a.*, u.username,
           (SELECT GROUP_CONCAT(t.name, ', ')
            FROM tags t
            JOIN article_tags at ON t.id = at.tag_id
            WHERE at.article_id = a.id) as tag_names
    FROM articles a
    JOIN users u ON a.author_id = u.id
    WHERE a.status = 'published'
    ORDER BY a.publish_date DESC
    LIMIT ? OFFSET ?
`

// 使用窗口函数（SQLite 3.25+）
const windowFunctionQuery = `
    SELECT
        a.*,
        ROW_NUMBER() OVER (PARTITION BY a.author_id ORDER BY a.publish_date DESC) as author_rank,
        COUNT(*) OVER (PARTITION BY a.author_id) as author_article_count
    FROM articles a
    WHERE a.status = 'published'
`

// 按时间分区存储
const partitionByMonth = (tableName, dateColumn) => {
  return `
        CREATE TABLE IF NOT EXISTS ${tableName}_${new Date().getFullYear()}_${(
    new Date().getMonth() + 1
  )
    .toString()
    .padStart(2, "0")} (
            LIKE ${tableName} INCLUDING ALL
        );

        CREATE INDEX IF NOT EXISTS idx_${tableName}_${new Date().getFullYear()}_${(
    new Date().getMonth() + 1
  )
    .toString()
    .padStart(2, "0")}_${dateColumn}
        ON ${tableName}_${new Date().getFullYear()}_${(new Date().getMonth() + 1)
    .toString()
    .padStart(2, "0")}(${dateColumn});
    `
}

class DatabaseError extends Error {
  constructor(message, code, sql) {
    super(message)
    this.name = "DatabaseError"
    this.code = code
    this.sql = sql
  }
}

const handleDatabaseError = (error, operation) => {
  console.error(`数据库操作失败: ${operation}`, {
    message: error.message,
    code: error.code,
    sql: error.sql,
    stack: error.stack
  })

  // 根据错误类型返回不同的错误信息
  switch (error.code) {
    case "SQLITE_CONSTRAINT_UNIQUE":
      throw new DatabaseError("数据已存在，无法重复创建", error.code, error.sql)
    case "SQLITE_CONSTRAINT_FOREIGNKEY":
      throw new DatabaseError("关联数据不存在，操作失败", error.code, error.sql)
    case "SQLITE_BUSY":
      throw new DatabaseError("数据库繁忙，请稍后重试", error.code, error.sql)
    default:
      throw new DatabaseError(
        `数据库操作失败: ${error.message}`,
        error.code,
        error.sql
      )
  }
}

// 启用查询日志
const enableQueryLogging = (db) => {
  db.on("trace", (sql) => {
    console.log("SQL执行:", sql)
  })

  db.on("profile", (sql, time) => {
    console.log(`SQL执行耗时: ${time}ms - ${sql}`)
  })
}

// 性能分析
const analyzePerformance = (db) => {
  // 分析查询计划
  const explainQuery = (sql) => {
    const explainStmt = db.prepare(`EXPLAIN QUERY PLAN ${sql}`)
    return explainStmt.all()
  }

  // 获取数据库统计信息
  const getDatabaseStats = () => {
    return {
      tableCount: db
        .prepare("SELECT COUNT(*) as count FROM sqlite_master WHERE type='table'")
        .get().count,
      indexCount: db
        .prepare("SELECT COUNT(*) as count FROM sqlite_master WHERE type='index'")
        .get().count,
      totalSize: db
        .prepare(
          "SELECT page_count * page_size as size FROM pragma_page_count(), pragma_page_size()"
        )
        .get().size
    }
  }

  return { explainQuery, getDatabaseStats }
}

// migration.js - 数据迁移工具
const migrateData = async (sourceDb, targetDb) => {
  try {
    // 获取所有表结构
    const tables = sourceDb
      .prepare(
        `
            SELECT name, sql
            FROM sqlite_master
            WHERE type='table' AND name NOT LIKE 'sqlite_%'
        `
      )
      .all()

    // 在目标数据库中创建表
    for (const table of tables) {
      targetDb.exec(table.sql)
    }

    // 复制数据
    for (const table of tables) {
      const tableName = table.name
      console.log(`迁移表: ${tableName}`)

      // 获取源数据
      const sourceData = sourceDb.prepare(`SELECT * FROM ${tableName}`).all()

      if (sourceData.length > 0) {
        // 构建插入语句
        const columns = Object.keys(sourceData[0])
        const placeholders = columns.map(() => "?").join(",")
        const insertStmt = targetDb.prepare(`
                    INSERT INTO ${tableName} (${columns.join(",")})
                    VALUES (${placeholders})
                `)

        // 批量插入
        const insertMany = targetDb.transaction((data) => {
          for (const row of data) {
            const values = columns.map((col) => row[col])
            insertStmt.run(...values)
          }
        })

        insertMany(sourceData)
        console.log(`迁移完成: ${tableName} - ${sourceData.length} 条记录`)
      }
    }

    console.log("数据迁移完成")
  } catch (error) {
    console.error("数据迁移失败:", error)
    throw error
  }
}

// backup.js - 备份工具
const fs = require("fs")
const path = require("path")

class BackupManager {
  constructor(dbPath, backupDir = "./backups") {
    this.dbPath = dbPath
    this.backupDir = backupDir
    this.ensureBackupDir()
  }

  ensureBackupDir() {
    if (!fs.existsSync(this.backupDir)) {
      fs.mkdirSync(this.backupDir, { recursive: true })
    }
  }

  // 创建备份
  createBackup() {
    const timestamp = new Date().toISOString().replace(/[:.]/g, "-")
    const backupFile = path.join(this.backupDir, `backup_${timestamp}.db`)

    try {
      // 使用 SQLite 的备份 API
      const sourceDb = new Database(this.dbPath)
      const backupDb = new Database(backupFile)

      // 执行备份
      sourceDb
        .backup(backupFile)
        .then(() => {
          console.log(`备份创建成功: ${backupFile}`)
          sourceDb.close()
          backupDb.close()

          // 清理旧备份（保留最近7天）
          this.cleanupOldBackups(7)
        })
        .catch((error) => {
          console.error("备份创建失败:", error)
          sourceDb.close()
          backupDb.close()
        })

      return backupFile
    } catch (error) {
      console.error("备份过程失败:", error)
      throw error
    }
  }

  // 清理旧备份
  cleanupOldBackups(daysToKeep) {
    const cutoffDate = new Date()
    cutoffDate.setDate(cutoffDate.getDate() - daysToKeep)

    const files = fs.readdirSync(this.backupDir)

    files.forEach((file) => {
      const filePath = path.join(this.backupDir, file)
      const stats = fs.statSync(filePath)

      if (stats.mtime < cutoffDate && file.startsWith("backup_")) {
        fs.unlinkSync(filePath)
        console.log(`删除旧备份: ${file}`)
      }
    })
  }

  // 恢复备份
  restoreBackup(backupFile) {
    try {
      // 验证备份文件存在
      if (!fs.existsSync(backupFile)) {
        throw new Error(`备份文件不存在: ${backupFile}`)
      }

      // 创建当前数据库的临时备份
      const tempBackup = this.dbPath + ".temp"
      if (fs.existsSync(this.dbPath)) {
        fs.copyFileSync(this.dbPath, tempBackup)
      }

      try {
        // 恢复备份
        fs.copyFileSync(backupFile, this.dbPath)
        console.log(`数据库恢复成功: ${backupFile}`)

        // 删除临时备份
        if (fs.existsSync(tempBackup)) {
          fs.unlinkSync(tempBackup)
        }
      } catch (error) {
        // 恢复失败时回滚
        if (fs.existsSync(tempBackup)) {
          fs.copyFileSync(tempBackup, this.dbPath)
          fs.unlinkSync(tempBackup)
        }
        throw error
      }
    } catch (error) {
      console.error("数据库恢复失败:", error)
      throw error
    }
  }
}

module.exports = BackupManager

// 在插入前验证关联数据存在性
const validateForeignKeys = (db, table, foreignKeys) => {
  for (const [column, referencedTable, referencedColumn] of foreignKeys) {
    const checkStmt = db.prepare(`
            SELECT 1 FROM ${referencedTable}
            WHERE ${referencedColumn} = ?
        `)

    if (!checkStmt.get(value)) {
      throw new Error(`${referencedTable} 中不存在 ${referencedColumn} = ${value}`)
    }
  }
}

// 使用延迟加载策略
const getArticlesWithTagsLazy = (articleIds) => {
  // 第一步：获取文章基本信息
  const articlesStmt = db.prepare(`
        SELECT * FROM articles WHERE id IN (${articleIds.map(() => "?").join(",")})
    `)
  const articles = articlesStmt.all(...articleIds)

  // 第二步：批量获取标签信息
  const tagsStmt = db.prepare(`
        SELECT at.article_id, t.*
        FROM article_tags at
        JOIN tags t ON at.tag_id = t.id
        WHERE at.article_id IN (${articleIds.map(() => "?").join(",")})
    `)
  const tags = tagsStmt.all(...articleIds)

  // 第三步：组合数据
  const tagsMap = {}
  tags.forEach((tag) => {
    if (!tagsMap[tag.article_id]) {
      tagsMap[tag.article_id] = []
    }
    tagsMap[tag.article_id].push(tag)
  })

  return articles.map((article) => ({
    ...article,
    tags: tagsMap[article.id] || []
  }))
}

// 使用乐观锁
const updateArticleWithVersion = (articleId, updateData, currentVersion) => {
  const transaction = db.transaction(() => {
    // 检查版本号
    const checkStmt = db.prepare("SELECT version FROM articles WHERE id = ?")
    const { version } = checkStmt.get(articleId)

    if (version !== currentVersion) {
      throw new Error("数据已被其他用户修改，请刷新后重试")
    }

    // 更新数据并增加版本号
    const updateStmt = db.prepare(`
            UPDATE articles
            SET title = ?, content = ?, version = version + 1, updated_at = CURRENT_TIMESTAMP
            WHERE id = ? AND version = ?
        `)

    const result = updateStmt.run(
      updateData.title,
      updateData.content,
      articleId,
      currentVersion
    )

    if (result.changes === 0) {
      throw new Error("更新失败，数据可能已被其他用户修改")
    }

    return { success: true, newVersion: currentVersion + 1 }
  })

  return transaction()
}

// 使用流式处理
const processLargeDataset = (db, table, batchSize = 1000, processor) => {
  return new Promise((resolve, reject) => {
    let offset = 0
    let hasMore = true

    const processBatch = () => {
      try {
        const stmt = db.prepare(`
                    SELECT * FROM ${table}
                    ORDER BY id
                    LIMIT ? OFFSET ?
                `)

        const rows = stmt.all(batchSize, offset)

        if (rows.length === 0) {
          hasMore = false
          resolve()
          return
        }

        // 处理当前批次
        processor(rows)

        offset += batchSize

        // 继续处理下一批次
        if (hasMore) {
          setImmediate(processBatch)
        }
      } catch (error) {
        reject(error)
      }
    }

    processBatch()
  })
}