Source: sql.js

var persist = require('persist'),
    Q = require('q'),
    _ = require('underscore-data'),
    extend = require('..').extend,
    util = require('util'),
    BaseBackend = require('./base'),
    rql = require('../rql/sql');

/** @class SqlBackend 
    @extends BaseBackend */
var SqlBackend = BaseBackend.extend(
/** @lends SqlBackend# */
{
    /** @method */
    initialize: function(options) {
        options = _.extend({driver: 'mysql'}, SqlBackend.defaults, options || {});
        this.options = options;

        this._opened = null;
    },

    /** @method */
    objectStoreNames: function() {
        var sql = (this.options.driver === 'sqlite3') ? 
              "SELECT name FROM sqlite_master WHERE type='table' ORDER BY name;"
            : "SHOW TABLES;";
        return this.runSqlAll(sql);
    },

    /** @method */
    objectStore: function(name, options) {
        return new SqlStore(this, name, options);
    },

    /** @method */
    createObjectStore: function(name, options) {
        throw 'Not implemented - please use CREATE TABLE query';
    },

    /** @method */
    deleteObjectStore: function(name) {
        return this.runSql('DROP TABLE ' + SqlStore.prototype.escapeIdentifier(name));
    },

    /** @method */
    open: function() {
        if (!this._opened) {
            this._opened = Q.ninvoke(persist, 'connect', this.options);
        }
        return this._opened;
    },

    /** @method */
    close: function() {
        throw 'Not implemented!';
    },

    /** @method */
    isClosed: function() {
        return Q.defer()
                .resolve(!!this._opened);
    },

    /** @method */
    runSql: function(sql, values) {
        return this.open().then(function(connection) {
            return Q.ninvoke(connection, 'runSql', sql, values||[]);
        });
    },

    /** @method */
    runSqlAll: function(sql, values) {
        return this.open().then(function(connection) {
            return Q.ninvoke(connection, 'runSqlAll', sql, values||[]);
        });
    },
});

SqlBackend.defaults = {
    host: 'localhost',
    port: 3306,
    database: 'default'
};


/** @class SqlStore
    @extends BaseStore */
var SqlStore = BaseBackend.BaseStore.extend(
/** @lends SqlStore# */
{
    /** @method */
    get: function(directives) {
        var key = this._getObjectKey({}, directives),
            sql = util.format('SELECT * FROM %s WHERE %s = ?;',
                              this.escapeIdentifier(this.name),
                              this.escapeIdentifier(this.keyPath));
        
        return this.runSqlAll(sql, [key])
            .then(function(result) {
                return result[0] || {};
            });
    },

    /** @method */
    add: function(object, directives) {
        var args = [],
            placeholders = [],
            self = this;

        for (var k in object) {
            args.push(object[k]);
            placeholders.push(this.escapeIdentifier(k));
        }

        var sql = util.format('INSERT INTO %s (%s) VALUES (%s);',
                              this.escapeIdentifier(this.name),
                              placeholders.join(','),
                              args.map(function() {return '?';}).join(','));

        return this.runSql(sql, args)
            .then(function(result) {
                // handle autoincrement
                if (result.insertId) {
                    // MySQL
                    object[self.keyPath] = result.insertId;
                } else if (result.lastId) {
                    // Sqlite
                    object[self.keyPath] = result.lastId;
                }
                return object;
            });
    },

    /** @method */
    put: function(object, directives) {
        var key = this._getObjectKey(object, directives),
            args = [],
            placeholders = [];

        for (var k in object) {
            args.push(object[k]);
            placeholders.push(this.escapeIdentifier(k)+'=?');
        }

        args.push(key);

        var sql = util.format('UPDATE %s SET %s WHERE %s = ?;',
                              this.escapeIdentifier(this.name),
                              placeholders.join(','),
                              this.escapeIdentifier(this.keyPath));

        return this.runSql(sql, args)
            .then(function(result) {
                return object;
            });
    },

    /** @method */
    'delete': function(directives) {
        var key = this._getObjectKey({}, directives),
            sql = util.format('DELETE FROM %s WHERE %s = ?;',
                              this.escapeIdentifier(this.name),
                              this.escapeIdentifier(this.keyPath));

        return this.runSql(sql, [key])
            .then(function(result) {
                // return number of affected rows
                var ret = 0;
                if ('affectedRows' in result) {
                    // MySQL
                    ret = result.affectedRows;
                } else if ('changes' in result) {
                    // Sqlite
                    ret = result.changes;
                }
                return ret;
            });
    },

    /** Execute RQL query */
    query: function(query) {
        var sql = this.parse(query);

        return this.runSqlAll(sql, [])
            .then(function(result) {
                if (result[0] && result[0][0]) {
                  // sqlite
                  result = result[0];
                }
                return result || [];
            });
    },

    /** Get connection object */
    connection: function() {
        return this.backend.open();
    },

    /** Delete all items */
    clear: function() {
        var sql = util.format('DELETE FROM %s;', this.escapeIdentifier(this.name));
        return this.runSql(sql);
    },

    /** Escape table or column name
        @method */
    escapeIdentifier: rql.sqlEscapeIdentifier,

    /** @method */
    runSql: function(sql, values) {
        return this.backend.runSql(sql, values);
    },

    /** @method */
    runSqlAll: function(sql, values) {
        return this.backend.runSqlAll(sql, values);
    },

    /**
     * Parse RQL query
     * @function
     */
    parse: function(query) {
        return rql.rql2sql(_.rql(query), {table: this.name});
    }
});

/** @module sql */

SqlBackend.SqlStore = SqlStore;
SqlBackend.rql2sql = rql.rql2sql;

module.exports = SqlBackend;