CastleModel数据库操作类

封装的Sequelize作为数据库操作底层，对Sequelize的部分操作进行了简化，语法结构类似于ThinkPHP3.2结构。 需要依赖于 castle-config/castle-controller/sequelize，其它依赖请参见相关库的依然范围

典型基础用法

1//获取模型对象
2let model = await this.M('Sex');
3//启动事务 =1
4await this.startTrans();
5//添加数据
6// INSERT INTO sex(UID,Sex) VALUES(6,1);
7await model.add({ UID: 6, Sex: 1 });
8//批量添加
9// INSERT INTO sex(UID,Sex) VALUES(6,1),(8,1);
10await model.addAll([
11    {
12        UID: 6, Sex: 1
13    },
14    {
15        UID: 8, Sex: 1
16    },
17])
18//更新数据
19// UPDATE sex SET Sex=100 WHERE UID > 7 LIMIT 1;
20await model.where({ UID: { gt: 7 } }).limit(1).save({ Sex: 100 });
21//自增自减处理,UID>7的Sex全部-1,UID+1
22// UPDATE sex SET Sex=Sex-1,UID=UID+1 WHERE UID > 7;
23await model.where({ UID: { gt: 7 } }).incOrDec({ Sex: -1, UID: 1 })
24//当存在DTime时自动做软删除，否则就是硬删除
25// DELETE FROM sex WHERE UID > 8;
26// UPDATE sex SET DTime = now() WHERE UID > 8;
27await model.where({ UID: { gt: 8 } }).del()
28//查询单个
29// SELECT * FROM sex WHERE UID > 8;
30await model.where({ UID: { gt: 8 } }).find()
31//分页查询多个
32// SELECT * FROM sex WHERE UID > 1 LIMIT 1,10;
33await model.where({ UID: { gt: 1 } }).page(1, 10).select()
34// 查询并统计
35// SELECT * FROM sex WHERE UID > 1;
36// SELECT COUNT(*) FROM sex WHERE UID > 1;
37await model.where({ UID: { gt: 1 } }).selectAndCount();
38//指定字段查询
39// SELECT UID FROM sex LIMIT 1;
40await model.fields('UID').find()
41//排除字段查询
42// SELECT Sex FROM sex LIMIT 1;
43await model.fields('UID', true).find()
44//批量条件更新，仅支持MySQL
45// UPDATE sex SET Sex = CASE UID WHEN 1 THEN 2 WHEN 5 THEN 10 WHEN 7 THEN Sex+5 ELSE Sex END WHERE UID IN (1,5,7);
46await model.caseSave([{ field: { case: 'UID', save: "Sex" }, data: { 1: 2, 5: 10, 7: "`Sex`+5" } }])
47//执行自定义SQL查询，通过__DB_PREFIX__注入表前缀
48await model.query(`SELECT * FROM __DB_PREFIX__sex`)
49//执行自定义SQL，
50await model.exec(`UPDATE Sex SET UID=UID+1`, 'UPDATE')
51//执行存储过程或函数
52await model.exec(`CALL reset();`, 'RAW')
53//查询单个字段且只要一个，返回值为单个字段的值
54// SELECT Sex FROM sex LIMIT 1;
55await model.getFields('Sex');
56//查询单个字段且返回数组，返回值为该字段的数据
57// SELECT Sex FROM sex;
58await model.getFields('Sex', true);
59//查询多个字段，以第一个字段为键返回，
60// SELECT UID,Sex FROM sex;
61let rs:{
62    [index:string]:{
63        UID:number,Sex:number
64        }
65} = await model.getFields('UID,Sex', true);
66//支持排序
67// SELECT * FROM sex ORDER BY UID DESC;
68await model.order('UID DESC').select();
69//支持group操作
70// SELECT SUM(UID) AS UID FROM sex GROUP BY UID;
71await model.group(['UID']).fields([[Sequelize.fn('sum', Sequelize.col('UID')), 'UID']]).select()
72//支持SUM等统计函数处理
73// SELECT SUM(UID) AS UID FROM sex GROUP BY Sex;
74await model.fnField(DbFn.SUM, 'UID', 'UID').group(['Sex']).select();
75//支持limit，不适用page方法时
76// SELECT * FROM sex LIMIT 1;
77await model.limit(1).select();
78//支持直接封装的SUM操作
79// SELECT SUM(UID) AS UID FROM sex GROUP BY UID;
80await model.group(['UID']).sum('UID')
81//支持自动检测是否存在，若不存在则自动添加
82// SELECT * FROM sex WHERE UID=10 AND Sex=1;
83// INSERT INTO sex(UID,Sex) VALUE(10,1);
84await model.addIfNotExist({ UID: 10, Sex: 1 })
85//也可以自定义存在检测条件
86// SELECT * FROM sex WHERE UID=11;
87// INSERT INTO sex(UID,Sex) VALUE(10,1);
88await model.addIfNotExist({ UID: 11, Sex: 1 }, { UID: 11 })
89//提交事务，两种方式都行，此处的this指向 BaseController
90// -1 == 0 具体执行commit，否则直接跳过
91await this.commit();
92await model.commit()
93//回滚事务，两种方式都行
94// 具体执行rollback，否则直接跳过
95await this.rollback();
96await model.rollback()
97//支持嵌套事务 +1
98await this.startTrans();
99await this.startTrans();
100await this.startTrans();
101await this.commit();
102await this.commit();
103await this.commit();
104//当提交次数=开起次数时最后一次提交，之后的commit会报错
105await this.commit();
106//若中途发生一次rollback调用则会直接抛出错误
107await this.rollback()