奋斗的宽

01/**
02 * mysql helper
03 * author: luzhongk@126.com
04 */
05
06const db = require('../utils/mysql.js') // knex
07const moment = require('moment')
08
09function formatData(data = []) {
10  const FORMAT = 'YYYY-MM-DD HH:mm:ss'
11  data.forEach(item => {
12    if (item.create_time) {
13      item.create_time = moment(item.create_time).format(FORMAT)
14    }
15    if (item.update_time) {
16      item.update_time = moment(item.update_time).format(FORMAT)
17    }
18  })
19  return data
20}
21
22/**
23 * 分页查询
24 * @param ctx (koa ctx对象)
25 * @param dbName （数据库名字）
26 * @param condition (查询条件)
27 * @returns {Promise<Object>}
28 */
29const list = async (ctx, dbName, condition = {}) => {
30  let { page = 1, size = 1000 } = ctx.query
31    if (page < 1) page = 1
32  const getContent = db(dbName)
33    .select()
34    .limit(size)
35    .offset((page - 1) * size)
36    .where(condition)
37    .orderBy('create_time', 'desc')
38  const getTotal = db(dbName).where(condition).count('id as total')
39  const [content, total] = await Promise.all([getContent, getTotal])
40
41  const data = { content: formatData(content), total: total.length ? total[0].total : 0 }
42  ctx.state.data = data
43  return data
44}
45
46/**
47 * 添加数据
48 * @param ctx
49 * @param dbName (数据库成功)
50 * @param body (插入内容，或者使用发送的body)
51 * @returns {Promise<Number>} (id)
52 */
53const add = async (ctx, dbName, body) => {
54  const data = await db(dbName).insert(body || ctx.request.body)
55  ctx.state.data = data
56  return data
57}
58
59/**
60 * 更新数据
61 * @param ctx
62 * @param dbName
63 * @param body (插入内容，或者使用发送的body)
64 * @returns {Promise<null Number>}
65 */
66const update = async (ctx, dbName, body) => {
67  const data = body || ctx.request.body
68  const { id } = data
69  if (!id) ctx.throw(400, 'id not found')
70  delete data.id
71  const res = await db(dbName).update(data).where({ id })
72  ctx.state.data = res
73  return res
74}
75
76/**
77 * 删除数据
78 * @param ctx
79 * @param dbName
80 * @param removeId
81 * @returns {Promise<*>}
82 */
83const remove = async (ctx, dbName, removeId) => {
84  const { id } = {removeId} || ctx.params || ctx.query
85  if (!id) ctx.throw(400, 'id not found')
86  if (id === 'all') {
87    const data = await db(dbName).truncate() // 清空表
88    ctx.state.data = '成功'
89    return data
90  } else {
91    const data = await db(dbName).where('id', id).del()
92    ctx.state.data = data
93    return data
94  }
95}
96
97module.exports = {
98  list,
99  add,
100  update,
101  remove
102}
103

01// 注入工具类
02app.context.dbHelper = dbHelper

01/**
02 * 临时日志
03 */
04
05const dbName = 'kuan_logger';
06
07module.exports = {
08  async list(ctx) {
09    await ctx.dbHelper.list(ctx, dbName);
10  },
11  async remove(ctx) {
12    await ctx.dbHelper.remove(ctx, dbName);
13  },
14  async add(ctx) {
15    await ctx.dbHelper.add(ctx, dbName);
16  }
17};