/var/www/myprog.com.ua/server/node/db
Edit: /var/www/myprog.com.ua/server/node/db/db_context.js (17030B)
const mysql = require('mysql');
const util = require('util')
const Role = require('../objects/role')
const Client = require('../objects/client')
const log = require('../logs/log')
let instance;
class DbContext {
constructor(config) {
if (!instance) {
instance = this;
}
this.mysql = mysql;
this.pool = mysql.createPool({
host: config.db_host,
user: config.db_user,
password: config.db_password,
database: config.db_name,
dateStrings: true,
connectionLimit: process.env.mysql_connection_pool_Limit
})
this.pool.getConnection((err, connection) => {
if (err) {
if (err.code === 'PROTOCOL_CONNECTION_LOST') {
log.error('DbContext', 'getConnection()', 'Database connection was closed.', err)
}
if (err.code === 'ER_CON_COUNT_ERROR') {
log.error('DbContext', 'getConnection()', 'Database has too many connections.', err)
}
if (err.code === 'ECONNREFUSED') {
log.error('DbContext', 'getConnection()', 'Database connection was refused.', err)
}
}
if (connection) connection.release()
return
})
this.pool.query = util.promisify(this.pool.query);
return instance;
}
async query(sql) {
return await this.pool.query(sql);
}
close() {
return new Promise((resolve, reject) => {
this.connection.end(err => {
if (err)
return reject(err);
resolve();
});
});
}
get_user_by_key(key, cb) {
var user = null;
// get client
var sql = "SELECT c.*, sa.activated FROM clients c \
INNER JOIN sms_activation sa ON c.id=sa.client_id \
WHERE sa.client_key='"+ key + "'";
this.connection.query(sql, (err, rows, fields) => {
if (!err) {
if (rows[0]) {
user = rows[0];
if (user.id > 0) {
// get client roles
var sql = "SELECT `r`.`role_id` as `id`, `r`.`role_name` as `name` " +
"FROM `user_role` `ur` " +
"LEFT JOIN `roles` `r` ON `ur`.`role_id`=`r`.`role_id` " +
"WHERE `ur`.`client_id`='" + user.id + "' LIMIT 1000;";
this.connection.query(sql, (err, rows, fields) => {
if (!err) {
user.roles = rows;
cb(user);
}
})
}
} else {
cb(null);
}
}
});
}
// get_config() {
// var _self = this;
// let sql = "SELECT * FROM `config` LIMIT 10000";
// return _self.query(sql)
// .then((rows) => {
// return rows; // RETURN RESULT
// })
// .catch(err => {
// return err;
// });
// }
sql_insert_or_update(object, tbl_name) {
let sql = '';
let keys = '';
let values = '';
let upd = '';
let count = 0;
try {
if (object.id == 0) {
object.get_db_fields_names().forEach(i => {
if (count == 0) {
values += `'${object[i]}'`;
keys += `\`${i}\``;
} else {
values += `, '${object[i]}'`;
keys += `, \`${i}\``;
}
count++;
});
count = 0;
sql = `INSERT INTO \`${tbl_name}\`(${keys}) VALUES (${values})`;
} else {
object.get_db_fields_names().forEach(i => {
if (i != 'id') {
if (count == 0) {
upd += `\`${i}\`='${object[i]}'`;
} else {
upd += `, \`${i}\`='${object[i]}'`;
}
count++;
}
});
sql = `UPDATE \`${tbl_name}\` SET ${upd} WHERE \`id\`='${object.id}'`;
}
} catch (e) {
log.error('DbContext', 'sql_insert_or_update()', e);
}
return sql;
}
//////////////////////////////////// NEW
async get_config() {
log.debug('DbContext', 'get_config')
let sql = `SELECT * FROM \`config\` LIMIT 10000;`
log.debug_sql('DbContext', 'get_config', sql)
let res = await this.query(sql)
return res
}
async get_mobile_operators() {
log.debug('DbContext', 'get_mobile_operators')
let sql = `SELECT * FROM \`mobile_operators\` LIMIT 100;`
log.debug_sql('DbContext', 'get_mobile_operators', sql)
let res = await this.query(sql)
return res
}
async get_client({ client_id, phone }) {
log.debug('DbContext', 'get_client', { client_id, phone })
if (!client_id && !phone) {
return
}
let sql = ''
if (client_id) {
sql = `SELECT * FROM \`clients\` WHERE \`id\`='${client_id}' LIMIT 1`
} else if (phone) {
sql = `SELECT \`c\`.* FROM \`clients_phones\` \`p\`
LEFT JOIN \`clients\` \`c\` ON \`p\`.\`client_id\`=\`c\`.\`id\`
WHERE \`p\`.\`phone\`='${phone}' GROUP BY \`c\`.\`id\` LIMIT 1`
}
log.debug_sql('DbContext', 'get_client', sql)
let res = await this.query(sql)
return res[0] ? new Client(res[0]) : undefined
}
async get_client_by_token(token) {
log.debug('DbContext', 'get_client_by_token', token)
if (!token) {
return
}
let sql = ''
sql = `SELECT \`c\`.* FROM \`clients\` \`c\`
LEFT JOIN \`clients_tokens\` \`ct\` ON \`ct\`.\`client_id\`=\`c\`.\`id\`
WHERE \`ct\`.\`token\`='${token}' LIMIT 1`
log.debug_sql('DbContext', 'get_client_by_token', sql)
let res = await this.query(sql)
return res[0] ? new Client(res[0]) : undefined
}
async get_clients({ limit }) {
log.debug('DbContext', 'get_clients', { limit })
let clients = []
//let sql = `SELECT * FROM \`clients\` LIMIT ${limit ? limit : 1000}`
let sql = `SELECT \`c\`.*, \`ci\`.\`city_name_ru\` as \`city_name\`, \`co\`.\`name\` as \`company_name\`
FROM \`clients\` \`c\`
LEFT JOIN \`cities\` \`ci\` ON \`c\`.\`city_id\`=\`ci\`.\`id\`
LEFT JOIN \`companies\` \`co\` ON \`c\`.\`company_id\`=\`co\`.\`id\`
LIMIT ${limit ? limit : 1000}`
log.debug_sql('DbContext', 'get_clients', sql)
let res = await this.query(sql)
for (let r of res) {
clients.push(new Client(r))
}
return clients
}
async get_client_phones({ client_id, limit }) {
log.debug('DbContext', 'get_client_phones', { limit })
let sql = `SELECT * FROM \`clients_phones\` WHERE \`client_id\`='${client_id}' LIMIT ${limit ? limit : 1000}`
log.debug_sql('DbContext', 'get_client_phones', sql)
let res = await this.query(sql)
return res
}
async client_add_phone({ client_id, phone }, connection) {
log.debug('DbContext', 'client_add_phone', { client_id, phone })
let sql = `INSERT INTO \`clients_phones\` (\`client_id\`, \`phone\`) VALUES ('${client_id}', '${phone}')`
log.debug_sql('DbContext', 'client_add_phone', sql)
let res = await this.query(sql, [], connection)
return res.insertId
}
async client_delete_phone({ client_id, phone }, connection) {
log.debug('DbContext', 'client_delete_phone', { client_id, phone })
let sql = `DELETE FROM \`clients_phones\` WHERE \`phone\`='${phone}' AND \`client_id\`='${client_id}'`
log.debug_sql('DbContext', 'client_delete_phone', sql)
let res = await this.query(sql, [], connection)
return res
}
async client_add_role({ client_id, role_id }, connection) {
log.debug('DbContext', 'client_add_role', { client_id, role_id })
let sql = `INSERT INTO \`clients_roles\` (\`client_id\`, \`role_id\`) VALUES ('${client_id}', '${role_id}')`
log.debug_sql('DbContext', 'client_add_phone', sql)
let res = await this.query(sql, [], connection)
return res.insertId
}
async client_delete_role({ client_id, role_id }, connection) {
log.debug('DbContext', 'client_delete_role', { client_id, role_id })
let sql = `DELETE FROM \`clients_roles\` WHERE \`client_id\`='${client_id}' AND \`role_id\`='${role_id}'`
log.debug_sql('DbContext', 'client_delete_role', sql)
let res = await this.query(sql, [], connection)
return res
}
async get_token({ client_id, app_id }) {
log.debug('DbContext', 'get_token', { client_id, app_id })
let where = ''
if (app_id) {
if (where.length === 0) {
where = ` WHERE `
} else {
where += ` AND `
}
where += ` \`app_id\`='${app_id}' `
}
if (client_id) {
if (where.length === 0) {
where = ` WHERE `
} else {
where += ` AND `
}
where += ` \`client_id\`='${client_id}' `
}
let sql = `SELECT * FROM \`clients_tokens\` ${where} LIMIT 1 `
log.debug_sql('DbContext', 'get_token', sql)
let res = await this.query(sql)
return res[0] ? res[0].token : undefined
}
async get_tokens({ client_id, app_id }) {
log.debug('DbContext', 'get_token', { client_id, app_id })
let where = ''
if (app_id) {
if (where.length === 0) {
where = ` WHERE `
} else {
where += ` AND `
}
where += ` \`app_id\`='${app_id}' `
}
if (client_id) {
if (where.length === 0) {
where = ` WHERE `
} else {
where += ` AND `
}
where += ` \`client_id\`='${client_id}' `
}
let sql = `SELECT * FROM \`clients_tokens\` ${where} LIMIT 1000`
log.debug_sql('DbContext', 'get_tokens', sql)
return await this.query(sql)
}
async insert_token({ client_id, app_id, token }, connection) {
log.debug('DbContext', 'insert_token', { client_id, app_id, token })
let sql = `INSERT INTO \`clients_tokens\` SET \`client_id\`='${client_id}', \`token\`='${token}', \`app_id\`='${app_id}' `
log.debug_sql('DbContext', 'insert_token', sql)
let res = await this.query(sql, [], connection)
return res.insertId
}
async delete_token({ id, token }, connection) {
log.debug('DbContext', 'delete_token', { id, token })
let where = ''
if (id) {
where = `WHERE \`id\` = '${id}'`
} else if (token) {
where = `WHERE \`token\` = '${token}'`
}
let sql = `DELETE FROM \`clients_tokens\` ${where}; `
log.debug_sql('DbContext', 'delete_token', sql)
let res = await this.query(sql, [], connection)
return res.insertId
}
async get_activation({ code, phone, app_id, type }) {
log.debug('DbContext', 'get_activation', { code, phone, app_id, type })
let where = ''
if (type) {
where += ` AND \`type\`='${type}' `
}
if (app_id) {
where += ` AND \`app_id\`='${app_id}' `
}
let sql = `SELECT * FROM clients_activation
WHERE phone='${phone}' AND code='${code}' ${where} LIMIT 1`
log.debug_sql('DbContext', 'get_activation', sql)
let res = await this.query(sql)
return res[0] ? res[0] : undefined
}
async insert_activation({ code, phone, app_id, type }, connection) {
log.debug('DbContext', 'insert_activation', { code, phone, app_id, type })
let sql = `INSERT INTO \`clients_activation\` SET \`code\`='${code}', \`phone\`='${phone}', \`app_id\`='${app_id}', \`type\`='${type}' `
log.debug_sql('DbContext', 'insert_activation', sql)
let res = await this.query(sql, [], connection)
return res.insertId
}
async set_activation({ id }, connection) {
log.debug('DbContext', 'set_activation', { id })
let sql = `UPDATE \`clients_activation\` SET \`activated\`= '1' WHERE \`id\`='${id}'`
log.debug_sql('DbContext', 'set_activation', sql)
return await this.query(sql, [], connection)
}
// END USER
// PHONES
async get_phone({ phone }) {
log.debug('DbContext', 'get_phone', { phone })
let sql = `SELECT * FROM \`clients_phones\` WHERE \`phone\`='${phone}' LIMIT 1`
log.debug_sql('DbContext', 'get_phone', sql)
let res = await this.query(sql)
return res[0] ? res[0] : undefined
}
// END PHONES
// ROLE
async create_role(role) {
let sql = 'INSERT INTO \`roles\` SET'
let count = 0
let cols = role.get_db_fields()
for (let i of cols) {
if (count == 0) {
sql += ` \`${i}\`='${role[i]}'`
} else {
sql += `, \`${i}\`='${role[i]}'`
}
count++
}
let res = await this.query(sql)
return res.insertId
}
async update_role(role) {
let changes = role.get_changes()
if (changes) {
let sql = `UPDATE \`roles\` SET `
let count = 0
for (let i in changes) {
if (i != 'id') {
if (count == 0) {
sql += `\`${i}\`='${changes[i]}'`
} else {
sql += `, \`${i}\`='${changes[i]}'`
}
count++
}
}
sql += ` WHERE \`id\`='${role.id}'`
log.debug_sql('DbContext', 'update_role', sql)
let res = await this.query(sql)
role.reset_changes()
}
return role
}
async get_role({ id }) {
log.debug('DbContext', 'get_role', { id })
let sql = `SELECT * FROM \`roles\` WHERE \`id\`='${id}' LIMIT 1`
log.debug_sql('DbContext', 'get_role', sql)
let res = await this.query(sql)
return res[0] ? new Role(res[0]) : undefined
}
async get_roles({ limit }) {
log.debug('DbContext', 'get_roles', { limit })
let roles = []
let sql = `SELECT * FROM \`roles\` LIMIT ${limit ? limit : 1000}`
log.debug_sql('DbContext', 'get_roles', sql)
let res = await this.query(sql)
for (let r of res) {
roles.push(new Role(r))
}
return roles
}
async get_client_roles({ limit, client_id }) {
log.debug('DbContext', 'get_client_roles', { limit })
let roles = []
let sql = `SELECT \`r\`.* FROM \`roles\` \`r\`
LEFT JOIN \`clients_roles\` \`cr\` ON \`r\`.\`id\`=\`cr\`.\`role_id\`
LEFT JOIN \`clients\` \`u\` ON \`cr\`.\`client_id\`=\`u\`.\`id\`
WHERE \`cr\`.\`client_id\`='${client_id}' LIMIT ${limit ? limit : 1000}`
log.debug_sql('DbContext', 'get_client_roles', sql)
let res = await this.query(sql)
for (let r of res) {
roles.push(new Role(r))
}
return roles
}
async get_role_clients({ limit, id }) {
log.debug('DbContext', 'get_role_clients', { limit })
let roles = []
let sql = `SELECT \`c\`.* FROM \`clients\` \`c\`
LEFT JOIN \`clients_roles\` \`cr\` ON \`c\`.\`id\`=\`cr\`.\`client_id\`
LEFT JOIN \`roles\` \`r\` ON \`cr\`.\`role_id\`=\`r\`.\`id\`
WHERE \`cr\`.\`role_id\`='${id}' LIMIT ${limit ? limit : 1000}`
log.debug_sql('DbContext', 'get_role_clients', sql)
let res = await this.query(sql)
for (let r of res) {
roles.push(new Role(r))
}
return roles
}
// END ROLE
}
require('./db_context_places')(DbContext);
require('./db_context_users')(DbContext);
require('./db_context_hosts')(DbContext);
require('./db_context_host_config')(DbContext);
require('./db_context_cameras')(DbContext);
require('./db_context_scenario')(DbContext);
require('./db_context_var')(DbContext);
require('./db_context_devices')(DbContext);
require('./db_context_events')(DbContext);
require('./db_context_commands')(DbContext);
require('./db_context_values')(DbContext);
require('./db_context_chat')(DbContext);
module.exports = DbContext;