Created
December 13, 2018 00:29
-
-
Save kselax/1abc00c505f80cde35628de3322fe786 to your computer and use it in GitHub Desktop.
work with tables
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
'use strict'; | |
const uuid = require('uuid'); | |
function getJoin(joins, tableName) { | |
let output = ''; | |
if (Array.isArray(joins)) { | |
joins.forEach((item) => { | |
output += `${item[0]} JOIN ${item[1]} ON ${tableName}.${item[2]} = ${item[1]}.${item[3]} `; | |
}); | |
} | |
return output; | |
} | |
function getSerializeFileds(fields, st) { | |
let str = ''; | |
const arr = []; | |
const keys = Object.keys(fields); | |
keys.forEach((key, i, array) => { | |
str += `${key} = ?`; | |
if (i < array.length - 1) { | |
str += st; | |
} | |
arr.push(fields[key]); | |
}); | |
if (keys.length > 1 && st.indexOf('AND') !== -1) { | |
str = `( ${str} )`; | |
} | |
return [str, arr]; | |
} | |
class Table { | |
constructor(tableName, query) { | |
this.tableName = tableName.toLowerCase(); | |
this.query = query; | |
} | |
count(fields) { | |
const serializeFields = getSerializeFileds(fields, ' AND '); | |
const whereStr = serializeFields[0]; | |
const fieldsArr = serializeFields[1]; | |
const queryStr = `SELECT COUNT(*) as cnt FROM ${this.tableName} WHERE ${whereStr}`; | |
return this.query(queryStr, fieldsArr); | |
} | |
insertOne(data) { | |
const dataItem = data; | |
if (!dataItem.id) { | |
dataItem.id = uuid.v4(); | |
} | |
const queryStr = `INSERT INTO ${this.tableName} SET ?`; | |
return this.query(queryStr, dataItem); | |
} | |
insertIgnore(data) { | |
const dataItem = data; | |
if (!dataItem.id) { | |
dataItem.id = uuid.v4(); | |
} | |
const queryStr = `INSERT IGNORE INTO ${this.tableName} SET ?`; | |
return this.query(queryStr, dataItem); | |
} | |
findOne(fields) { | |
const serializeFields = getSerializeFileds(fields, ' AND '); | |
const whereStr = serializeFields[0]; | |
const fieldsArr = serializeFields[1]; | |
const queryStr = `SELECT * FROM ${this.tableName} WHERE ${whereStr} LIMIT 1`; | |
return this.query(queryStr, fieldsArr); | |
} | |
findLimit(fields, limit, order) { | |
const serializeFields = getSerializeFileds(fields, ' AND '); | |
const whereStr = serializeFields[0]; | |
const fieldsArr = serializeFields[1]; | |
const queryStr = | |
`SELECT * FROM ${this.tableName} | |
WHERE ${whereStr} | |
${order ? `ORDER BY ${order} DESC` : ''} | |
${limit ? `LIMIT ${limit}` : ''}`; | |
return this.query(queryStr, fieldsArr); | |
} | |
find(fields) { | |
const serializeFields = getSerializeFileds(fields, ' AND '); | |
const whereStr = serializeFields[0]; | |
const fieldsArr = serializeFields[1]; | |
const queryStr = `SELECT * FROM ${this.tableName} WHERE ${whereStr}`; | |
return this.query(queryStr, fieldsArr); | |
} | |
findFields(onlyFields, fields) { | |
const serializeFields = getSerializeFileds(fields, ' AND '); | |
const whereStr = serializeFields[0]; | |
const fieldsArr = serializeFields[1]; | |
const queryStr = `SELECT ${onlyFields.join(',')} FROM ${this.tableName} WHERE ${whereStr}`; | |
return this.query(queryStr, fieldsArr); | |
} | |
findAll() { | |
return this.query(`SELECT * FROM ${this.tableName}`); | |
} | |
findFull(obj, joins) { | |
const queryStr = | |
`SELECT ${obj.fields.join(', ')}, ${this.tableName}.id FROM ${this.tableName} | |
${getJoin(joins, this.tableName)} | |
WHERE ${this.tableName}.id = ${obj.id}`; | |
return this.query(queryStr); | |
} | |
findFullList(fields, fieldsWhere, joins, order) { | |
const serializeFields = getSerializeFileds(fieldsWhere, ' AND '); | |
const whereStr = serializeFields[0]; | |
const fieldsArr = serializeFields[1]; | |
let queryStr = | |
`SELECT ${fields.join(', ')}, ${this.tableName}.id FROM ${this.tableName} | |
${getJoin(joins, this.tableName)}`; | |
if (whereStr) { | |
queryStr = `${queryStr} WHERE ${whereStr}`; | |
} | |
if (order) { | |
queryStr = `${queryStr} ORDER BY ${order} DESC`; | |
} | |
return this.query(queryStr, fieldsArr); | |
} | |
findLastInsert(fields) { | |
const serializeFields = getSerializeFileds(fields, ' AND '); | |
const whereStr = serializeFields[0]; | |
const fieldsArr = serializeFields[1]; | |
const queryStr = `SELECT * FROM ${this.tableName} | |
WHERE ${whereStr} | |
ORDER BY id DESC | |
LIMIT 1`; | |
return this.query(queryStr, fieldsArr); | |
} | |
findAllJoin(obj, joins, limit, order) { | |
const queryStr = | |
`SELECT ${obj.fields.join(', ')}, ${this.tableName}.id FROM ${this.tableName} | |
${getJoin(joins, this.tableName)} | |
${order ? `ORDER BY ${order} DESC` : ''} | |
${limit ? `LIMIT ${limit}` : ''}`; | |
return this.query(queryStr); | |
} | |
deleteOne(fields) { | |
const serializeFields = getSerializeFileds(fields, ' AND '); | |
const whereStr = serializeFields[0]; | |
const fieldsArr = serializeFields[1]; | |
const queryStr = | |
`DELETE FROM ${this.tableName} WHERE ${whereStr} LIMIT 1`; | |
return this.query(queryStr, fieldsArr); | |
} | |
delete() { | |
const serializeFields = getSerializeFileds(fields, ' AND '); | |
const whereStr = serializeFields[0]; | |
const fieldsArr = serializeFields[1]; | |
const queryStr = | |
`DELETE FROM ${this.tableName} WHERE ${whereStr}`; | |
return this.query(queryStr, fieldsArr); | |
} | |
deleteAll() { | |
return this.query(`DELETE FROM ${this.tableName}`); | |
} | |
updateOne(fields, data) { | |
const serializeFields = getSerializeFileds(fields, ' AND '); | |
const whereStr = serializeFields[0]; | |
const fieldsArr = serializeFields[1]; | |
const serializeData = getSerializeFileds(data, ', '); | |
const dataStr = serializeData[0]; | |
const dataArr = serializeData[1]; | |
const queryStr = | |
`UPDATE ${this.tableName} SET ${dataStr} WHERE ${whereStr} LIMIT 1`; | |
return this.query(queryStr, dataArr.concat(fieldsArr)); | |
} | |
update(fields, data) { | |
const serializeFields = getSerializeFileds(fields, ' AND '); | |
const whereStr = serializeFields[0]; | |
const fieldsArr = serializeFields[1]; | |
const serializeData = getSerializeFileds(data, ', '); | |
const dataStr = serializeData[0]; | |
const dataArr = serializeData[1]; | |
const queryStr = | |
`UPDATE ${this.tableName} SET ${dataStr} WHERE ${whereStr}`; | |
return this.query(queryStr, dataArr.concat(fieldsArr)); | |
} | |
updateFiledSum(fields, field, sum) { | |
const serializeFields = getSerializeFileds(fields, ' AND '); | |
const whereStr = serializeFields[0]; | |
const fieldsArr = serializeFields[1]; | |
const queryStr = | |
`UPDATE ${this.tableName} SET ${field} = ${field} + ${sum} WHERE ${whereStr} LIMIT 1`; | |
return this.query(queryStr, fieldsArr); | |
} | |
} | |
module.exports = Table; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment