3 var mariaSql = require('mariasql');
4 var JL = require('jsnlog').JL;
5 var logger = JL('dbquery.js');
6 var _ = require('lodash');
8 // var sequelize = require('./dbpool');
9 var dbpool = require('./dbpool');
11 var pool = new dbpool({});
12 // function fnCallback() {};
13 // setInterval(function () {
14 // pool.query('show tables', {});
15 // console.log(process.memoryUsage());
18 process.on('exit', function () {
19 pool.drain(function () {
34 var mariadb = mariadb || {};
38 'insert into tic_job ',
39 '(job_status, job_deleted, job_hasksfile, job_updater, job_uptime, job_register, job_regtime) ',
40 'values ("READY", false, false, "tic", now(), "tic", now());'
43 'select tic_job.job_id job_id, ',
44 'tic_job.job_status job_status, ',
45 'tic_job.job_image_id job_image_id, ',
46 'tic_image.image_name job_image_name, ',
47 'tic_image.image_size job_image_size, ',
48 'tic_job.job_hasksfile job_hasksfile, ',
49 'tic_job.job_uptime job_uptime ',
51 'left join tic_image on tic_job.job_image_id = tic_image.image_id ',
52 'where tic_job.job_deleted = false ',
53 'and job_id = <%= strJobId %>;'
55 'getJobsTotalCount': [
56 'select count(job_id) as total_count ',
58 'where job_deleted = false;'
61 'select tic_job.job_id job_id, ',
62 'tic_job.job_status job_status, ',
63 'tic_job.job_image_id job_image_id, ',
64 'tic_image.image_name job_image_name, ',
65 'tic_image.image_size job_image_size, ',
66 'tic_job.job_hasksfile job_hasksfile, ',
67 'tic_job.job_ks job_ks, ',
68 'tic_job.job_arch job_arch, ',
69 'tic_job.job_uptime job_uptime ',
71 'left join tic_image on tic_job.job_image_id = tic_image.image_id ',
72 'where tic_job.job_deleted = false ',
73 'order by job_id desc ',
74 'limit <%= startNum %> , 10;'
77 'select tic_job.job_image_id image_id, ',
78 'tic_job.job_id image_job_id, ',
79 'tic_image.image_name image_name, ',
80 'tic_image.image_size image_size, ',
81 'tic_job.job_hasksfile image_hasksfile, ',
82 'tic_job.job_ks image_ks, ',
83 'tic_job.job_arch image_arch, ',
84 'tic_job.job_status image_status, ',
85 'tic_job.job_uptime image_uptime ',
86 'from tic_job inner join tic_image ',
87 'where tic_job.job_image_id = tic_image.image_id ',
88 'and tic_job.job_deleted = false ',
89 'order by tic_job.job_id desc ',
90 'limit <%= startNum %> , 10;'
92 'getImagesTotalCount': [
93 'select count(image_id) as total_count ',
97 'insert into tic_image ',
104 '"<%= imageType %>", ',
105 '"<%= imageName %>", ',
106 '"<%= imageSize %>"',
110 'select user_email, user_group ',
112 'where user_email = "<%= userEmail %>" and user_password = "<%= userPassword %>" ',
116 'select count(user_id) as count ',
118 'where user_email = "<%= userEmail %>";'
122 mariadb.doQuery = function doQuery(queryString) {
123 return pool.query(queryString, []);
132 mariadb.editJob = function editJob(req, res) {
133 var queryString, strJobId, reqParam,
134 job_status, job_deleted, job_hasksfile, job_image_id, job_ks, job_arch;
136 function onSuccess(rows) {
137 logger.info('editJob.success');
138 res.json(rows.result);
141 strJobId = req.params.id;
143 job_status = reqParam.job_status;
144 job_deleted = reqParam.job_deleted;
145 job_hasksfile = reqParam.job_hasksfile;
146 job_image_id = reqParam.job_image_id;
147 job_ks = reqParam.job_ks;
148 job_arch = reqParam.job_arch;
150 queryString = 'update tic_job set';
152 queryString += ' job_image_id = "' + job_image_id + '",';
155 queryString += ' job_status = "' + job_status + '",';
158 queryString += ' job_deleted = ' + job_deleted + ',';
161 queryString += ' job_hasksfile = ' + job_hasksfile + ',';
164 queryString += ' job_ks = "' + job_ks + '",';
167 queryString += ' job_arch = "' + job_arch + '",';
169 queryString += ' job_updater = "tic",';
170 queryString += ' job_uptime = now()';
171 queryString += ' where job_id = ' + strJobId + ';';
173 logger.info('editJob: query = ' + queryString);
176 this.doQuery(queryString).then(onSuccess);
182 mariadb.addJob = function addJob(req, res) {
185 function onSuccess(rows) {
186 logger.info('addJob.success: {job_id: ' + rows.result.info.insertId + '}');
188 job_id: rows.result.info.insertId
192 queryString = _.join(this.queries['addJob'], '');
194 logger.info('addJob: query = ' + queryString);
197 this.doQuery(queryString).then(onSuccess);
203 mariadb.getJobById = function getJobById(req, res) {
204 var queryString, strJobId;
206 function onSuccess(rows) {
207 res.json(rows.result);
210 strJobId = req.params.id;
212 queryString = _.template(_.join(this.queries['getJobById'], ''))({
216 logger.info('getJobById: query = ' + queryString);
219 this.doQuery(queryString).then(onSuccess);
223 * Get Total Count of Job
225 mariadb.getJobsTotalCount = function getJobsTotalCount(req, res) {
228 function onSuccess(rows) {
229 res.json(rows.result);
232 queryString = _.join(this.queries['getJobsTotalCount'], '');
234 logger.info('getJobsTotalCount: query = ' + queryString);
237 this.doQuery(queryString).then(onSuccess);
243 mariadb.getJobsAllList = function getJobsAllList(req, res) {
244 var queryString, reqParam, pageNum, startNum;
246 function onSuccess(rows) {
247 res.json(rows.result);
252 pageNum = reqParam.pageNum;
253 startNum = (pageNum - 1) * 10;
255 queryString = _.template(_.join(this.queries['getJobsAllList'], ''))({
259 logger.info('getJobsAllList: query = ' + queryString);
262 this.doQuery(queryString).then(onSuccess);
273 mariadb.getImagesAllList = function getImagesAllList(req, res) {
274 var queryString, reqParam, pageNum, startNum;
276 function onSuccess(rows) {
277 res.json(rows.result);
282 pageNum = reqParam.pageNum;
283 startNum = (pageNum - 1) * 10;
285 queryString = _.template(_.join(this.queries['getImagesAllList'], ''))({
289 logger.info('getImagesAllList: query = ' + queryString);
292 this.doQuery(queryString).then(onSuccess);
296 * Get Total Count of Job
298 mariadb.getImagesTotalCount = function getImagesTotalCount(req, res) {
301 function onSuccess(rows) {
302 res.json(rows.result);
305 queryString = _.join(this.queries['getImagesTotalCount'], '');
307 logger.info('getImagesTotalCount: query = ' + queryString);
310 this.doQuery(queryString).then(onSuccess);
318 mariadb.addImage = function addImage (req, res) {
319 var queryString, paramObj;
321 function onSuccess(rows) {
322 var result = rows.result;
323 logger.info('addJob: result = {image_id: ' + result.info.insertId + '}');
325 image_id: result.info.insertId
331 queryString = _.template(_.join(this.queries['addImage'], ''))({
332 imageType: paramObj.image_type,
333 imageName: paramObj.image_name,
334 imageSize: paramObj.image_size
337 logger.info('addJob: query = ' + queryString);
340 this.doQuery(queryString).then(onSuccess);
344 mariadb.editImage = function editImage(req, res) {
345 var queryString, strJobId, reqParam,
346 job_status, job_deleted, job_hasksfile, job_image_id;
348 function onSuccess(rows) {
349 logger.info('editJob.success');
350 res.json(rows.result);
353 strJobId = req.params.id;
355 job_status = reqParam.job_status;
356 job_deleted = reqParam.job_deleted;
357 job_hasksfile = reqParam.job_hasksfile;
358 job_image_id = reqParam.job_image_id;
360 queryString = 'update tic_job set';
362 queryString += ' job_image_id = "' + job_image_id + '",';
365 queryString += ' job_status = "' + job_status + '",';
368 queryString += ' job_deleted = ' + job_deleted + ',';
371 queryString += ' job_hasksfile = ' + job_hasksfile + ',';
373 queryString += ' job_updater = "tic",';
374 queryString += ' job_uptime = now()';
375 queryString += ' where job_id = ' + strJobId + ';';
377 logger.info('editJob: query = ' + queryString);
380 this.doQuery(queryString).then(onSuccess);
390 * @param json object { email, password }
391 * @desc get user information
393 mariadb.getUser = function getUser(query, success, error) {
394 function onSuccess(rows) {
398 if (rows && rows.result && rows.result.length !== NO_DATA) {
399 resultObj.data = rows.result[0];
404 var queryString = _.template(_.join(this.queries['getUser'], ''))({
405 userEmail: query.email,
406 userPassword: query.password
409 logger.info('getUser: query = ' + queryString);
412 this.doQuery(queryString).then(onSuccess);
417 * @param json object { email }
420 mariadb.hasUser = function hasUser(query, success, error) {
421 function onSuccess(err, rows) {
430 var queryString = _.template(_.join(this.queries['hasUser'], ''))({
431 userEmail: query.email
434 logger.info('hasUser: query = ' + queryString);
437 this.doQuery(queryString).then(onSuccess);
446 mariadb.connectToClient = function connectToClient() {
447 return new Promise(function (resolve, reject) {
449 mariaSqlClient.connect(function (err) {
451 logger.error('connection error');
455 logger.info('connection success');
463 mariadb.createClient = function createClient() {
464 return new Promise(function (resolve, reject) {
465 logger.info('create client');
480 mariaSqlClient = new mariaSql(mariaSqlConfig);
488 mariadb.init = function init() {
493 .then(self.connectToClient);
498 module.exports = mariadb;