Dataserver module for MySQL in V8CGI

dataserver.js

/* dataserver.jss

USE:
  sql = new SQL();
  sql.connect();
  num   = sql.getValue('SELECT count(1) FROM customers')
  rec   = sql.getRecord('SELECT * FROM customers WHERE id=123456')
  recs  = sql.getRecords('SELECT * FROM customers')
  list  = sql.getList('SELECT DISTINCT name FROM customers')
  table = sql.getTable('SELECT * FROM customers')
  id    = sql.execute('INSERT INTO customers(name,phone) VALUES("H4x0r","555-1234")')
  sql.disconnect();

You can also pass values to the query in placeholders as hash or array:
  rec = sql.getRecord('SELECT * FROM customers WHERE id={id}', {id:12345})
  rec = sql.getRecord('SELECT * FROM customers WHERE state={0} and country={1}', ['FL','US'])

Remember to sanitize your queries!
*/

var MySQL = require('mysql').MySQL;

function SQL(){
  var db      = null;
  var numRows = null;
  var lastId  = null;
}

SQL.prototype.connect=function(){
  if(!this.db){
    this.db = new MySQL().connect('localhost','root','pass','database');
  }
}

SQL.prototype.disconnect=function(){
  if(this.db){ this.db.close(); }
}

SQL.prototype.getValue=function(sql,values){
  var query,rows,data=null;
  query = this.query(sql,values);
  rows  = query.fetchArrays();
  if(rows.length>0){ data = rows[0][0]; }
  query.close();
  return data;
}

SQL.prototype.getRecord=function(sql,values){
  var query,rows,data=null;
  query = this.query(sql,values);
  rows  = query.fetchObjects();
  if(rows.length>0){ data = rows[0]; }
  query.close();
  return data;
}

SQL.prototype.getRecords=function(sql,values){
  var query,rows,data=null;
  query = this.query(sql,values);
  rows  = query.fetchObjects();
  if(rows.length>0){ data = rows; }
  query.close();
  return data;
}

SQL.prototype.getList=function(sql,values){
  var query,rows,data=null;
  query = this.query(sql,values);
  rows  = query.fetchArrays();
  if(rows.length>0){
    data = [];
    for(i in rows){ data.push(rows[i][0]); } 
  }
  query.close();
  return data;
}

SQL.prototype.getTable=function(sql,values){
  var query,rows,data=null;
  query = this.query(sql,values);
  rows  = query.fetchArrays();
  if(rows.length>0){ data = rows; }
  query.close();
  return data;
}

SQL.prototype.execute=function(sql,values){ /* Insert, Update, Delete */
  var query,rows,data=null;
  var command = sql.substr(0,6).toUpperCase();
  this.query(sql,values);
  this.numRows = this.db.affectedRows();
  if     (command=="INSERT"){ data = this.lastId = this.db.insertId(); }
  else if(command=="UPDATE"){ data = this.numRows; }
  else if(command=="DELETE"){ data = this.numRows; }
  return data;
}

SQL.prototype.query=function(sql,values){
  var query;
  this.connect();
  this.sql = sql = this.parse(sql,values);
  query = this.db.query(sql);
  return query;
}

SQL.prototype.parse=function(sql,values){
  if(!values){ return sql; }
  for(var i in values){ sql=sql.replace(new RegExp("{("+i+")}","g"),values[i]); }
  return sql;
}

exports.SQL = SQL;