Calculation/test/app/lib/sql_builder.test.js

/**
 * 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);
    });
});