joola.io.engine/lib/connectors/mysql.js

/**
 *  joola.io
 *
 *  Copyright Joola Smart Solutions, Ltd. <info@joo.la>
 *
 *  Licensed under GNU General Public License 3.0 or later.
 *  Some rights reserved. See LICENSE, AUTHORS.
 *
 *  @license GPL-3.0+ <http://spdx.org/licenses/GPL-3.0+>
 */

var
  mysql = require('mysql');

var removeTrailing = function (sql) {
  if (sql.substring(sql.length - 1) == ';')
    return sql.substring(0, sql.length - 1);
  else
    return sql;
};

exports.open = function (query) {
  var printout = 'tcp://' + query.datasource.user + ':' + query.datasource.password.replace(/./g, '*') + '@' + query.datasource.host + ':' + (query.datasource.port || 3306) + '/' + query.datasource.dbname;
  joola.logger.debug('Open connection to MySQL: ' + printout);
  return mysql.createConnection({host: query.datasource.host, user: query.datasource.user, password: query.datasource.password, database: query.datasource.dbname});
};

exports.close = function (connection, query) {
  var printout = 'tcp://' + query.datasource.user + ':' + query.datasource.password.replace(/./g, '*') + '@' + query.datasource.host + ':' + (query.datasource.port || 3306) + '/' + query.datasource.dbname;
  joola.logger.silly('Close connection to MySQL: ' + printout);
  connection.end();
};

exports.executeQuery = function (query, callback) {
  var self = this;
  var connection = this.open(query);

  if (query.limit)
    query.sql = removeTrailing(query.sql) + ' LIMIT ' + query.limit + ';';

  query.sql = query.sql.replace(/"/gi, '');

  connection.query(query.sql, function (err, rows) {
    if (err)
      return callback(err);

    var _fields = [];
    var _rows = [];

    if (rows.length > 0) {
      _.each(rows[0], function (value, column) {
        _fields.push({name: column});
      })
    }

    _.each(rows, function (row) {
      var _row = {};
      _.each(row, function (value, column) {
        _row[column] = row[column];

      });
      _rows.push(_row);
    });

    var result = {
      fields: _fields,
      rows: _rows
    };

    self.close(connection, query);
    return callback(err, result);
  });
};