九、Waterline的删除和SQL事务

JimmyTsai75

已于 2022-12-29 10:14:07 修改

阅读量164

点赞数

分类专栏： Sailsjs从零开始文章标签：数据库 javascript node.js mysql

于 2022-12-16 09:00:16 首次发布

本文链接：https://blog.csdn.net/JimmyTsai75/article/details/128329984

版权

Sailsjs从零开始专栏收录该内容

12 篇文章 2 订阅

订阅专栏

文章目录

数据删除
归档式删除的实现
SQL 事务操作

数据删除

删除数据并不难，但是有时候出于法律规定的考虑或是为了能够支持撤销操作等其他需求，我们不能真是的把数据删除掉。这就要求删除数据的时候，要执行软删除（逻辑删除，物理不删除）。

软删除的选择

通常的做法有两种，字段标记和归档删除：

字段标记：设置一个布尔类型的字段 IsDelete，该字段默认默认为false，要删除的时候把记录的IsDelete 改写为true，这种做法实际上是修改数据的做法。
归档删除：删除之前，把要删除的记录都保存到一个专门用来归档的表当中，Sails把这个表命名为Archive 。保存数据之后再执行真正的删除动作。Archive的字段设计如下：
- createdAt：记录归档时间
- fromModel：哪个表的数据
- originalRecord：原始记录（json格式）
- originalRecordId：原始记录的Id值

其中 originalRecord里面保存被删除数据的信息，举例如下：

{
	"createdAt": "2022-12-14 19:10:58.270",
	"updatedAt": 1671016258270,
	"id": 16,
	"email": "1671016258248@gmail.com",
	"password": "YWVzLTI1Ni1nY20kJGsyMDIyNA==$HHekV753kKPFIDI/$Ibw5cVSF$Z4R+IxDlCSNMVSsKhbkxNA",
	"nickname": "G52kA",
	"age": 73
}

可以轻松的看得出，这个是把查询出来的对象，JSON.stringify 转换成字符串并保存到数据中。
两种做法我们该怎么选择，我们需要对其进行比较：

字段标记

优点
- 操作简单：字段标记法不执行删除，删除动作变成修改动作。
- 恢复方便：如果经常需要恢复已经删除的数据，也只需要修改一个标记就可以实现。
- 被删除数据检索方便：对于已经删除的数据，依然可以使用查询语句进行查询，比如我需要查找一个email 为"zs@gmail.com" 的已经删除掉的记录，我们可以这样写SQL查询语句：

select * from user where email="zs@gmail.com" and isDelete=1

缺点
- 干扰正常数据：因为已经删除的数据实际上还在数据库，所以我们每次查询的时候，都需要加上一条 and isDelete=0 ，无论是一般数据检索还是数据报表需要的各种数据的统计，都不能遗漏，出Bug的概率更高。
- 每个表都有数据冗余：和归档删除不同的是，已经删除掉的数据不是在统一的一个归档表里面，而是分散在原来的表里面。一些在运营中可能会比较频繁删除的表，或出现已经删除的数据比正常数据还多的情况，从而影响查询的速度。

归档删除

优点
- 不影响正常数据：已经删除的数据，在“表”这个级别，它是物理删除的。对一个表的操作和正常删除没有什么不同，这样也不会提高程序出Bug的概率。
- 不影响查询速度：没有数据冗余：同上，这种删除也不会导致某个表过于庞大，因为Archive表平时不用，查询较少。这个表也许后面会变得很大，但是并不影响最经常使用的其他表的查询速度。
缺点
- 删除麻烦：一个删除操作分解成两个动作，如果中间和数据库的连接有中断会导致数据不准确，需要考虑使用事务操作。
- 恢复麻烦：恢复数据也是一样分解成两个动作。同样需要考虑使用数据库的事务级锁表。
- 被删除数据难于检索：对于已经删除的数据，我们只能通过时间，管理的表或是旧的id号来检索，但是如果需要用已经删除的数据里面的字段来检索就没有那么方便，比如我们没有办法法方便的按照email来检索已经删除的用户记录。当然一定要做也可以，mariaDB和mySql 都是支持Json字段类型的。

Waterline 的做法和存在的问题

waterline 提供物理删除和归档式删除，并且两种指令都再分解到删除单条数据和删除多条数据（参照find和findOne）。

destory和 destroyOne

物理删除数据库中符合给定条件的记录（二者的区别在于是否多条记录）。跟踪发现，就是执行delete from table …的SQL语句。具体操作格式如下：

await Something.destroy(criteria);

其中，criteria和find或findOne是一样的。

archive 和 archiveOne

归档式删除数据库中符合给定条件的记录（二者的区别在于是否多条记录）。使用方法同destory类似，具体不再累述。跟踪Waterline的SQL语句，发现它的做法比较简单：以下以删除id=40的user表记录为例，执行:

await User.archive({id:40});

跟踪Waterline的SQL查询，发现SQL执行如下代码：

select `id` from `user` where `id` = 40 

insert into `archive` (`createdAt`, `fromModel`, `originalRecord`, `originalRecordId`) values (?, ?, ?, ?)

delete from `user` where `id` = 40

Waterline 的问题

无事务：我们跟踪发现，三条SQL语句直接发送到数据库，并且不是一次性发送三条指令（可以通过跟踪connection 的 multipleStatements 值），而是分三次发送。这样做的问题在于，如果插入语句执行完了，执行delete 语句的时候数据库连接中断了，那么就会出现归档表里面有数据，而原始数据还在的情况。
originalRecord 表：Waterline把被删除的数据转换成Json字符串之后保存到originalRecord字段里面。 Archive 表不需要我们在Api/models文件夹里面去添加，系统自动创建。但是如果我们需要在自动迁移的时候，在Archive表设置更多字段，Waterline不提供设置功能，设置还不允许用户添加同名的数据模型（也就是数据模型不能命名为Archive），除非在配置文件里面指定Archive表的名称。这个设计思路依然是为了减少程序员对细节的过多关注，但是也为自由设计带来麻烦。如果需要修改归档表的结构，我们只能手动修改数据库。另外，originalRecord 的数据类型是LONGTEXT mariaDB现在的版本已经提供Json的操作函数，也就是说他们的SQL语句是可以支持Json数据查询的。所以这个地方把字段类型设置为Json会更合理一些。

归档式删除的实现

综上，对于大部分的表我们选择归档式删除。当然包含物理删除在内的三种删除方式也可以根据实际需要进行混合使用。

baseDestroy 存储过程

为了实现数据删除，可以创建如下删除的存储过程：

DROP PROCEDURE IF EXISTS `baseDestroy`;
CREATE DEFINER = `root` @`localhost` PROCEDURE `baseDestroy`(
  IN `tableName` VARCHAR(200),  
  IN `criteria` VARCHAR(800)  
) 

LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER

COMMENT '基础删除操作' 

TOP: BEGIN 
IF LENGTH(TRIM(tableName)) = 0 THEN
  SELECT '表名称不能为空';
  LEAVE TOP;
END IF;
IF LENGTH(TRIM(criteria)) = 0 THEN
  SELECT '不可以无条件删除';
  LEAVE TOP;
END IF;
/*delete 语句*/
SET @sqlStr = CONCAT('delete from ',tableName, ' where ', criteria);
PREPARE stmt  FROM @sqlStr;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
/*还需要返回sql语句用于调试*/
SELECT @sqlStr AS sqlStr;
END

添加路由

在路由配置中增加一条：

'POST /api/userDel': {    
    action: 'User/del'
},
  //swagger 部分略

在UserController.ts里面添加userDel动作，代码片段如下：

export async function del(req: Api.SailsRequest, res: Api.Response, next: Function): Promise<any> {
  ....
 }

wlSimulate 中添加destroy代码

为了实现归档式删除，我们可以模拟Waterline，做三个动作，操作流程如下：

按照删除条件（Criteria）查询数据库，如果找不到直接返回
根据查询结果组织归档数据，并插入归档表
物理删除

在这里插入图片描述

部分源代码解释

buildArchiveRecord：根据表名称和原始记录数组，利用sqlstr库对值进行转义，生成插入归档表需要的数据：

protected buildArchiveRecord(tbName: string, originalRecord: [any]): any {
    let values = originalRecord.map(x => {
      let item = '';
      if (typeof x !== 'string') { item = JSON.stringify(x); } else item = x;
      return `(${SqlString.escape(new Date().getTime())},
      ${SqlString.escape(tbName)},
      ${SqlString.escape(item)},
      ${SqlString.escape(x.id)})`;
    });

    let res = {
      cols: '`createdAt`, `fromModel`, `originalRecord`, `originalRecordId`',
      values: values.join(',')
    }
    return res;
  }

baseDestroy 存储过程的调用：

let sqlTableName = this.sqlEscapeId(tableName);//对传过来的表名称进行转义处理
let where = this.getWhereQuery(tableName, OrmMethods.find, criteria);//和find一样，把查询条件转换成SQL
//生成调用存储过程的查询语句：
let sql=CALL baseDestroy("${sqlTableName}","${where}");

SQL 事务操作

令人遗憾的是，Waterline 里面并没有很好的实现对事务操作的支持，假如我们需要回滚，Waterline里面并没有明确的回滚语句，并且默认的数据库连接connection配置是把一次连接发送多条语句的开关关闭掉的，这样我们不能在同一个连接里面做回滚操作。如果需要我们也可以修改数据库连接配置，但是依然有许多不方便的地方。
所以我们应该自己设计SQL的事务处理，做好能够一次性解决SQL多条语句查询问题。这样的我们的代码后面就可以复用。
设计思路是这样的：

Waterline的底层是mysql 库，我们直接使用该库。
采用Waterline的数据库连接（避免多种数据库连接造成的不方便）
采用Promise 编程，避免无尽的回调（callback），每条SQL语句对应一个resolve
执行多条SQL语句，只要有一条SQL语句有问题，事务回滚。只有所以SQL没有问题，才可以Commit

创建SqlTransaction类

在utils文件夹中添加SqlTransaction.ts，代码如下：

import SqlConfig from 'typing/SqlConfig';
//sails 对事务支持不好，直接跳过它，使用它的底层库mysql 自己做
const mysql = require('mysql');
class SqlTransaction {
    constructor(config: SqlConfig.ConnectionConfig) {
        this.pool = mysql.createPool(config);
    }
    //#region 私有函数
    /**
     * sql 连接池
     */
    private pool: any;
    //#endregion
    /**
     * Sql 查询
     * @param sql 要查询的sql语句
     * @param params 查询参数
     * @returns 查询结果
     */
    public Query(connect: any, sql: string, params: any): Promise<any> {
        return new Promise((resolve, reject) => {
            connect.query(sql, params, (err: any, result: any, fields: any) => {
                if (err) return reject(err);
                resolve(result);
            });
        });
    };
    /**
     * Sql 事务处理
     * @param sqls 需要执行的sql语句
     * @param params 对应上面sql语句的参数,可以没有参数，如果有参数，数组长度要和sqls一致
     * @returns 返回一个Promise
     */
    public Transaction(sqls: Array<string>, params?: Array<any>): Promise<any> {
        return new Promise((resolve, reject) => {
            this.pool.getConnection((err: any, connection: any) => {
                if (err) return reject(err);
                // 如果 语句和参数数量不匹配 promise直接返回失败

                if (params && sqls.length !== params.length) {
                    connection.release(); // 释放掉
                    return reject(new Error("语句与传值不匹配，两个数组长度不一致"));
                }

                // 开始执行事务
                connection.beginTransaction((beginErr: any) => {
                    // 创建事务失败
                    if (beginErr) {
                        connection.release(); // 释放掉
                        return reject(beginErr);
                    }
                    // 返回一个promise 数组
                    let functionArray = sqls.map((sql, index) => {
                        return this.Query(connection, sql, params ? params[index] : null);
                    });
                    // 使用all 方法 对里面的每个promise执行的状态 检查
                    Promise.all(functionArray).then((arrResult) => {
                        // 若每个sql语句都执行成功了 才会走到这里 在这里需要提交事务，前面的sql执行才会生效
                        // 提交事务                    
                        connection.commit(function (commitErr: any, info: any) {
                            if (commitErr) {
                                // 事务回滚，之前运行的sql语句不生效
                                connection.rollback(function (rollbackErr: any) {
                                    if (rollbackErr) {
                                        connection.release();
                                        throw rollbackErr;
                                    }
                                });
                                // 返回promise失败状态
                                return reject(commitErr);
                            }
                            connection.release();
                            // 事务成功 返回 每个sql运行的结果 是个数组结构
                            resolve(arrResult);
                        });
                    }).catch((error) => {
                        // 多条sql语句执行中 其中有一条报错 直接回滚
                        connection.rollback(function () {
                            connection.release();
                            reject(error);
                        });
                    });
                });
            });
        });

    }
}
export = SqlTransaction;

具体理解可见注释。

获取Waterline的数据库连接配置

Waterline 提供一个transaction(during) 这个during的参数就是一个数据库连接，我们通过该函数获取连接配置，并供给SqlTransaction类做连接池，做法如下(utils/wlBase.ts)：

/**
   * 使用事务
   * @param during 回调函数
   * @returns 回调函数返回值的引用
   */
  protected usingTransaction(sqls: Array<string>, params?: Array<any>) {
    if (params) params = params.map(x => { return SqlString.escape(x); });
    return this.sa.getDatastore().transaction(async (db: any) => {
      let transaction = new SqlTransaction(db.config);//SqlTransaction 构造函数可以传递数据库连接参数
      let res = transaction.Transaction(sqls);
      return res;
    })
  }

现在，水到渠成。我们终于可以完整的实现数据的归档式删除了，代码片段如下（utils/wlSimulate.ts）：

let tmp = this.buildArchiveRecord(sqlTableName, result.rows);
let Sqls = new Array<string>();
Sqls.push(`CALL baseCreate("\`archive\`","${tmp.cols}", "${tmp.values}", 0);`);
Sqls.push(`CALL baseDestroy("${sqlTableName}","${where}");`);
let x = await this.usingTransaction(Sqls);

使用Postman测试删除

正确测试

测试正常删除，删除条件以id为38或39为例

在这里插入图片描述

观察数据库中user表和archive表，查看是否如预期，如果满足预期，user表里面的id为38,39的记录已经被删除，同时archive表里面多出两条归档记录。

错误测试

修改调用baseDestroy存储过程的代码，制造错误致使物理删除失败，看看archive的插入操作是否被回滚，为此修改调用语句如下（此处仅为示例，测试完请改回）：

在这里插入图片描述

重启Sails，再次修改postman的提交参数，制造一个user表里面有存在的记录来删除（比如id为45，为达到测试效果，请确保该数据在数据库里面是存在的）。再次提交请求：

在这里插入图片描述
postman返回错误，观察到数据库里面user表id=45的记录没有被删除，同时archive里面也没有原始记录originalRecordId是45并且fromModel是user的记录。

至此可见，我们的事务操作是成功的。

JimmyTsai75

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
九、Waterline的删除和SQL事务

令人遗憾的是，Waterline 里面并没有很好的实现对事务操作的支持，假如我们需要回滚，Waterline里面并没有明确的回滚语句，并且默认的数据库连接connection配置是把一次连接发送多条语句的开关关闭掉的，这样我们不能在同一个连接里面做回滚操作。这就要求删除数据的时候，要执行软删除（逻辑删除，物理不删除）。字段标记：设置一个布尔类型的字段 IsDelete，该字段默认默认为false，要删除的时候把记录的IsDelete 改写为true，这种做法实际上是修改数据的做法。
复制链接

扫一扫

专栏目录