/** * sql拼接器单元测试 * * @author CaiAoLin * @date 2017/10/20 * @version */ 'use strict'; const { app, assert } = require('egg-mock/bootstrap'); const SqlBuilder = require('../../../app/lib/sql_builder'); describe('test/app/lib/sql_builder.test.js', () => { it('set where like', function* () { const sqlBuilder = new SqlBuilder(); sqlBuilder.setAndWhere('field', { value: app.mysql.escape('value%'), operate: 'like', }); const [sql, param] = sqlBuilder.build('table'); const finalSql = app.mysql.format(sql, param); // 最后组合后应该获得的数据 const matchSql = "SELECT * FROM `table` WHERE `field` like 'value%'"; assert(finalSql === matchSql); }); it('set normal sql', function* () { const sqlBuilder = new SqlBuilder(); sqlBuilder.columns = ['id']; sqlBuilder.limit = 10; sqlBuilder.offset = 1; sqlBuilder.orderBy = [['id', 'DESC']]; const [sql, param] = sqlBuilder.build('table'); const finalSql = app.mysql.format(sql, param); // 最后组合后应该获得的数据 const matchSql = 'SELECT `id` FROM `table` LIMIT 1,10 ORDER BY `id` DESC'; assert(finalSql === matchSql); }); it('where in sql', function* () { const sqlBuilder = new SqlBuilder(); sqlBuilder.columns = ['id']; sqlBuilder.limit = 10; sqlBuilder.offset = 1; sqlBuilder.orderBy = [['id', 'DESC']]; sqlBuilder.setAndWhere('id', { value: [1, 2, 3, 4], operate: 'in', }); const [sql, sqlParam] = sqlBuilder.build('table'); const finalSql = app.mysql.format(sql, sqlParam); const matchSql = 'SELECT `id` FROM `table` WHERE `id` IN (1,2,3,4) LIMIT 1,10 ORDER BY `id` DESC'; assert(matchSql === finalSql); }); it('update sql', function* () { const sqlBuilder = new SqlBuilder(); sqlBuilder.setUpdateData('create_time', { value: 1, selfOperate: '+', }); sqlBuilder.setUpdateData('office', { value: 2, }); sqlBuilder.setUpdateData('full_path', { value: ['`full_path`', app.mysql.escape('1.'), app.mysql.escape('2.')], literal: 'Replace', }); sqlBuilder.setAndWhere('group_id', { value: 1, operate: '>=', }); const [sql, sqlParam] = sqlBuilder.build('table', 'update'); const finalSql = app.mysql.format(sql, sqlParam); const matchSql = "UPDATE `table` SET `create_time` = IF(IsNull(`create_time`), 0, `create_time`) + 1, `office` = 2, `full_path` = Replace(`full_path`,'1.','2.') WHERE `group_id` >= 1"; assert(finalSql === matchSql); }); it('delete sql', function* () { const sqlBuilder = new SqlBuilder(); sqlBuilder.setAndWhere('office', { value: 2, operate: '=', }); const prePath = '1.2.3'; sqlBuilder.setAndWhere('path', { value: app.mysql.escape(prePath + '%'), operate: 'Like', }); const [sql, sqlParam] = sqlBuilder.build('table', 'delete'); const finalSql = app.mysql.format(sql, sqlParam); const matchSql = "DELETE FROM `table` WHERE `office` = 2 AND `path` Like '1.2.3%'"; assert(finalSql === matchSql); }); });