# mm_sql

[中文](./README.md) | [English](./README_EN.md)

一个通用的SQL帮助类，支持通过切换db_type实现对不同数据库的操作，包括MySQL、SQLite和JSONDB等，提供统一的API接口。内置 **Dao 实时模型构建器**，支持嵌套对象属性修改后自动同步到数据库，特别适合游戏开发等层级数据场景。

## 安装

```bash
npm install mm_sql
```

## 依赖

- mm_admin: ^1.1.4
- mm_helper: ^1.0.0
- mm_jsondb: ^1.0.0
- mm_mysql: ^2.5.6
- mm_sqlite: ^1.5.5

## 示例

`example/` 目录提供了常用示例，可直接运行：

| 示例文件 | 说明 | 运行方式 |
|---------|------|---------|
| [basic_usage.js](./example/basic_usage.js) | 基本增删改查 | `node example/basic_usage.js` |
| [sql_query.js](./example/sql_query.js) | 原生 SQL 查询的三种调用方式 | `node example/sql_query.js` |
| [sql_template.js](./example/sql_template.js) | SQL 模板与参数过滤 | `node example/sql_template.js` |
| [dao_model.js](./example/dao_model.js) | Dao 实时模型构建器 | `node example/dao_model.js` |
| [database_switch.js](./example/database_switch.js) | 切换数据库类型 | `node example/database_switch.js` |
| [transaction.js](./example/transaction.js) | 事务提交与回滚 | `node example/transaction.js` |

## 测试

项目使用 [Jest](https://jestjs.io/) 编写单元测试，要求**测试覆盖率 100%**（语句、分支、函数、行）：

```bash
npm test
```

测试用例覆盖 `index.js`、`lib/sql.js`、`lib/dao.js` 的全部代码路径，包括多种数据库类型（MySQL/SQLite/JSONDB）的适配器创建、参数校验分支以及基于真实 SQLite 的集成测试。

## 使用方法

### 1. 基本使用

```javascript
// 引入模块
const { Sql, Dao } = require('mm_sql');

// 创建实例（默认使用MySQL）
const sql = new Sql({
  db_type: 'mysql',
  mysql: {
    host: 'localhost',
    port: 3306,
    user: 'root',
    password: 'password',
    database: 'test_db'
  }
});

// 执行查询SQL
const users = await sql.run('SELECT * FROM users WHERE status = 1');

// 执行更新SQL
const result = await sql.exec('UPDATE users SET status = 0 WHERE id = 1');

// 关闭连接
await sql.close();
```

### 2. 切换数据库类型

```javascript
// 创建MySQL实例
const sql = new Sql({
  db_type: 'mysql',
  mysql: {
    host: 'localhost',
    user: 'root',
    password: 'password',
    database: 'mysql_db'
  }
});

// 切换到SQLite（更换 scope 会创建新适配器；同 scope 命中缓存不会重建连接）
sql.setConfig({
  scope: 'other',
  db_type: 'sqlite',
  sqlite: {
    dir: '/db/',
    database: 'sqlite.db'
  }
});

// 使用SQLite进行操作
const data = await sql.run('SELECT * FROM users');
```

### 3. 主要方法

#### SQL执行方法

```javascript
// 执行查询操作（返回结果集）
const users = await sql.run('SELECT * FROM users WHERE status = 1');

// 执行带参数的查询
const user = await sql.run('SELECT * FROM users WHERE id = ?', [1]);

// 执行带超时的查询
const timeoutResult = await sql.run('SELECT * FROM users WHERE status = 1', [], 5000);

// 执行修改操作（返回影响行数）
const updateResult = await sql.exec('UPDATE users SET status = 0 WHERE id = 1');

// 执行带参数的修改
const updateWithParams = await sql.exec('UPDATE users SET name = ? WHERE id = ?', ['张三', 1]);

// 执行带超时的修改
const timeoutExec = await sql.exec('DELETE FROM users WHERE status = 0', [], 3000);

// 使用对象参数方式调用（键为 param，不是 params）
const result = await sql.run({
  sql: 'SELECT * FROM users WHERE name LIKE ?',
  param: ['张%'],
  timeout: 5000
});
```

#### 连接管理方法

```javascript
// 手动打开数据库连接
await sql.open();

// 手动关闭数据库连接
await sql.close();

// 动态修改配置
sql.setConfig({
  host: 'new_host',
  database: 'new_database'
});
```

#### 模板方法

```javascript
// SQL模板查询生成
const query = sql.tplQuery({ name: '张', age: 20 }, {
  name: '`name` LIKE "{0}%"',
  age: '`age` > {0}'
});
// 生成: "`name` LIKE \"张%\" AND `age` > 20"

// SQL模板数据生成
const body = sql.tplBody({ name: '李四', age: 25 }, {
  name: '`name` = {0}',
  age: '`age` = {0}'
});
// 生成: "`name` = '李四', `age` = 25"

// 参数过滤
const filteredParams = sql.filter({ id: 1, name: '张三', password: '123' }, ['password']);
// 结果: { id: 1, name: '张三' }
```

#### 数据库适配器方法

```javascript
// 获取底层数据库适配器
const dbAdapter = sql.db();
// 可用于直接操作底层数据库接口
db.table = "user_account";
const user = await db.get({
  user_id: 1
});
```

### 4. 配置选项

```javascript
const sql = new Sql({
  // 数据库类型（与内部 way 等价，构造函数自动兼容）
  db_type: 'mysql', // 可选值: 'mysql', 'sqlite', 'jsondb'（内部等价于 way）

  // 作用域配置：适配器按 scope 缓存，切换 scope 即创建新适配器
  scope: 'sys',

  // MySQL配置（按数据库类型嵌套）
  mysql: {
    host: 'localhost',
    port: 3306,
    user: 'root',
    password: '',
    database: ''
  },

  // SQLite配置（按数据库类型嵌套）
  sqlite: {
    dir: '/db/', // SQLite数据库文件存储目录
    database: 'mm' // SQLite数据库文件名
  },

  // JSONDB配置（按数据库类型嵌套，走 mm_jsondb）
  jsondb: {
    dir: '/db/', // JSON文件存储目录
    database: 'mm'
  }
});
```

> 命名约定：`way:'jsondb'`（或 `db_type:'jsondb'`）为主名称，与模块 `mm_jsondb`、工厂 `jsondbAdmin`、配置块 `jsondb` 对齐。
> 旧称 `way:'json'` / `db_type:'json'` 与配置块 `json` 仍兼容，构造函数会自动归一化为 `jsondb`，无需迁移既有配置。

## 高级用法

### 1. SQL模板

```javascript
// 定义SQL模板
const sqlTemplate = {
  name: '`name` LIKE "{0}%"',
  age: '`age` > {0}'
};

// 使用模板构建查询条件
const query = sql.tplQuery({ name: '张', age: 20 }, sqlTemplate);
// 生成的查询条件: "`name` LIKE \"张%\" AND `age` > 20"

// 使用模板构建更新语句
const body = sql.tplBody({ name: '李四', age: 25 }, {
  name: '`name` = {0}',
  age: '`age` = {0}'
});
// 生成的更新语句: "`name` = '李四', `age` = 25"

// 使用配置文件定义SQL模板
const config = {
  "query": {
    "name": "`name` like '%{0}%'"
  },
  "where": {
    "uid": "`uid` = {0}"
  },
  "update": {
    "name": "`name` = {0}"
  }
};

// 使用配置文件中的模板
const queryFromConfig = sql.tplQuery({ name: '张' }, config.query);
```

### 2. 自定义SQL执行

```javascript
// 执行自定义查询SQL
const result = await sql.run('SELECT * FROM users WHERE status = 1');

// 执行自定义更新SQL
const updateResult = await sql.exec('UPDATE users SET status = 0 WHERE id = 1');

// 执行带参数的SQL
const user = await sql.run('SELECT * FROM users WHERE id = ?', [1]);

// 执行带超时的SQL
const timeoutResult = await sql.run('SELECT * FROM users WHERE status = 1', [], 5000);

// 执行事务操作（MySQL）
const mysqlTransactionResult = await sql.exec(`
  START TRANSACTION;
  INSERT INTO users (name, age) VALUES ('测试用户', 30);
  UPDATE users SET status = 1 WHERE id = LAST_INSERT_ID();
  COMMIT;
`);

// 执行事务操作（SQLite）
const sqliteTransactionResult = await sql.exec(`
  BEGIN TRANSACTION;
  INSERT INTO users (name, age) VALUES ('测试用户', 30);
  UPDATE users SET status = 1 WHERE id = last_insert_rowid();
  COMMIT;
`);
```

## 实时模型（Dao）

Dao 是内置的实时模型构建器，用于处理**层级嵌套数据**。定义模型树后，可通过嵌套属性直接修改数据，修改会自动同步到数据库。

### 适用场景

| 场景 | 典型数据层级 | 说明 |
|------|-------------|------|
| 🎮 **游戏开发** | 账户 → 角色 → 背包/道具/属性/技能 | 玩家数据天然多层嵌套，修改道具属性即自动存档 |
| ⚙️ **配置管理** | 项目 → 模块 → 配置项 | 层级化配置，修改深层配置自动持久化 |
| 📝 **CMS系统** | 站点 → 栏目 → 文章 → 评论 | 树形内容结构，编辑嵌套内容自动保存 |
| 🛒 **电商系统** | 用户 → 订单 → 商品 → SKU | 关联数据层级，修改订单商品属性自动同步 |
| 📡 **IoT设备管理** | 网关 → 设备 → 传感器 | 层级设备树，修改传感器配置自动更新 |

### 基本使用

```javascript
const { Sql, Dao } = require('mm_sql');

const sql = new Sql({ db_type: 'mysql', ... });
const dao = new Dao(sql);

// 定义模型层级：账户 → 角色 → 道具
// 模型名用作关系名，table 指定实际表名
dao.model('account', {
  table: 'game_account',       // 实际表名（可选，默认等于模型名）
  key: 'username',
  children: {
    role: {
      table: 'game_role',      // 表名带前缀
      key: 'name',
      children: {
        tool: {
          table: 'game_tool',
          key: 'id'
        }
      }
    }
  }
});
```

对应三张表的建表语句：

```sql
CREATE TABLE game_account (
  username VARCHAR(50) PRIMARY KEY,
  password  VARCHAR(100),
  email     VARCHAR(100)
);

CREATE TABLE game_role (
  name              VARCHAR(50) PRIMARY KEY,
  game_account_id   VARCHAR(50),   -- 外键：父表名_id
  level             INT DEFAULT 1,
  exp               INT DEFAULT 0
);

CREATE TABLE game_tool (
  id          VARCHAR(50) PRIMARY KEY,
  game_role_id VARCHAR(50),         -- 外键：父表名_id
  name        VARCHAR(50),
  atk         INT DEFAULT 0,
  def         INT DEFAULT 0
);
```

现在可以像操作普通对象一样修改嵌套属性：

```javascript
const account = await dao.account('player1');

// 模型名用作关系名，table 才是实际表名
account.role['战士'].tool['sword_001'].atk = 100;  // → UPDATE game_tool SET atk=100 WHERE id='sword_001'
account.role['战士'].level = 15;                     // → UPDATE game_role SET level=15 WHERE name='战士'
account.email = 'new@test.com';                      // → UPDATE game_account SET email='new@test.com' WHERE username='player1'
```

### 工作原理

```
dao.account('player1')
  ├─ 查询 account 表 → 获取账户记录
  ├─ 批量查询 role 表（account_id = 'player1'）→ 预加载所有角色
  ├─ 批量查询 tool 表（role_id = '战士'）→ 预加载所有道具
  └─ 返回多层 Proxy 对象

// 修改属性时：
account.role['战士'].tool['sword_001'].atk = 100
  → ① 内存立即更新（同步）
  → ② 串行队列写入数据库（异步，防竞态）
```

### 配置选项

```javascript
dao.model('account', {
  table: 'game_account',       // 实际表名（可选，默认等于模型名）
  key: 'username',             // 主键字段名（必填）
  fk: 'parent_id',             // 外键字段名（可选，默认 父表名_id）
  children: {                  // 子模型定义（可选）
    role: {
      table: 'game_role',
      key: 'name',
      children: { ... }
    }
  }
});
```

### 注意事项

- **预加载机制**：`get()` 会递归预加载所有子孙节点，适合单条记录层级数据，不适合大批量数据
- **写入队列**：同一行的多次修改通过串行队列保证不丢失，但不同行之间并行
- **适用数据量**：单次加载的数据量建议控制在数百条以内，避免预加载开销过大
- **不适用场景**：大批量数据写入请使用 `table().setList()`，复杂 JOIN 查询请使用 `sql.run()`

## 注意事项

1. 系统会自动初始化连接，无需手动调用初始化方法
2. 使用完成后建议调用`close()`方法关闭连接，避免资源泄漏
3. 切换数据库类型时会重新创建适配器，原来的连接会被关闭
4. 不同数据库的SQL语法可能有所差异，请根据实际使用的数据库类型调整SQL语句
5. 事务操作需要根据具体数据库类型使用相应的SQL语法
6. 错误处理：所有方法都包含错误处理，会记录错误日志并抛出异常

## 错误处理

```javascript
try {
  const result = await sql.run('SELECT * FROM non_existent_table');
} catch (error) {
  console.error('SQL执行失败:', error);
  // 错误信息会被自动记录到日志
}
```

## 支持的数据库类型

- MySQL
- SQLite
- JSONDB（`way:'jsondb'`，文件存储，适合静态配置数据；旧称 `way:'json'` 仍兼容）

## API参考

### Sql类

#### constructor(config)
创建Sql实例
- `config`: 配置对象

#### run(sql, params, timeout)
执行查询SQL
- `sql`: SQL语句或选项对象
- `params`: 参数数组（可选）
- `timeout`: 超时时间（毫秒，可选）

#### exec(sql, params, timeout)
执行修改SQL
- `sql`: SQL语句或选项对象
- `params`: 参数数组（可选）
- `timeout`: 超时时间（毫秒，可选）

#### open()
手动打开数据库连接

#### close()
手动关闭数据库连接

#### setConfig(config)
动态修改配置
- `config`: 新的配置对象

#### tplQuery(paramDt, sqlDt)
生成SQL查询条件
- `paramDt`: 参数对象
- `sqlDt`: SQL模板对象

#### tplBody(paramDt, sqlDt)
生成SQL数据部分
- `paramDt`: 参数对象
- `sqlDt`: SQL模板对象

#### filter(paramDt, arr)
过滤参数对象
- `paramDt`: 参数对象
- `arr`: 需要过滤的键数组

#### db()
获取底层数据库适配器

### Dao类

#### constructor(sql)
创建 Dao 实例
- `sql`: Sql 实例

#### model(name, options)
注册模型树，同时在 dao 实例上挂载同名方法
- `name`: 模型名（即方法名）
- `options.key`: 主键字段名（必填）
- `options.fk`: 外键字段名（可选，默认 `父表名_id`）
- `options.children`: 子模型定义对象（可选）

```javascript
dao.model('account', {
  key: 'username',
  children: {
    role: { key: 'name', children: { tool: { key: 'id' } } }
  }
});

// 使用注册的方法
const account = await dao.account('player1');
```

#### [name](keyValue)
动态方法，根据 `model()` 注册的名称自动生成。调用 `ModelNode.get(keyValue)` 获取记录及其所有子孙数据。
- `keyValue`: 主键值
- 返回：Proxy 包装对象（不存在返回 null）

## 许可证

MIT