WebSQL ORM to easy transactions.

WarriorSQL.js

////////////////////////////////////////////////////////////////////////////////////////////////////
// WarriorSQL 
////////////////////////////////////////////////////////////////////////////////////////////////////
/**
 * WarriorSQL 
 * ORM object to easy consults to WebSQL databases.
 * On this version can do easy SQL Querys:
 *     - SELECT
 *     - DELETE
 *     - UPDATE
 *     - INSERT
 *     - CREATE TABLE
 *     - DROP TABLE
 *
 *  ================================================================   
 *  How to use:
 *  WarriorSQL.openConnection();
 *  WarriorSQL.use("informes").all().get(function(result){
 *       console.log(result);
 *  });
 * =================================================================
 * 
 * @author Mario M. https://github.com/ewiggin
 * @license MIT 
 * @version 0.7
 */
var WarriorSQL = {

    /**
     * Database connection object
     * @type {Object}
     */
    db: null,

    /**
     * Query object.
     * We build with this props an SQL Query String.
     * 
     * @type {Object}
     */
    query: {
        intention: 'SELECT',
        where: '',
        fields: '*',
        order_by: '',
        order_by_direction: 'asc',
        primary_key: 'id',
        limit: '',
        params: []
    },
    
    /**
     * Open connection object 
     * @return {void}
     */
    openConnection: function(){
        this.db = window.openDatabase("grangesDB", "1.0", "Granges DB", 10000000);
    },

    /**
     * Entity name (Table name)
     * @type {String}
     */
    entity: '',

    /**
     * Define entity to call Query's. In MySQL is a Table Name.
     * 
     * @param  {String} entity          Table name
     * @param  {String} primary_key     Column name to be Primary Key
     * @return {Object}                 this instance
     */
    use: function(entity, primary_key){
        this.entity = entity;
        if(typeof primary_key !== 'undefined') this.query.primary_key = primary_key;

        return this;
    },

    /**
     * Create a table with struct object param
     *
     *  SQLite.use("users").createTable({
     *      id: 'INTEGER PRIMARY KEY',
     *      name: 'TEXT',
     *      email: 'TEXT '
     *  });
     * 
     * @param  {Object} object  Table Struct definition
     * @return {void}
     */
    createTable: function(object){
        var strSQL = "CREATE TABLE IF NOT EXISTS ? (?)";
        var TABLE = this.entity;
        var VALUES = '';

        // Get all keys and values from Object
        var i = 0;
        for (var key in object) {
            if(i > 0) VALUES += ", ";
            VALUES += key + " " + object[key];
            i++;
        }
        
        // Execute Query
        self.db.transaction(function(tx){
            tx.executeSql(strSQL, [TABLE, VALUES], function(tx, result){
                fn(result);
            });
        });

    },

    /**
     * Drop a table from Database.
     * 
     * @param  {String} entity  Table name to drop
     * @return {void}
     */
    dropTable: function(entity) {
        var self = this;
        var strSQL = "DROP TABLE IF EXISTS " + entity;

        self.db.transaction(function(tx){
            tx.executeSql(strSQL);
        }, self.transactionError);
    },

    /**
     * Drop all items from Entity
     * 
     * @return {void}           
     */
    drop: function(){
        var self = this;
        var strSQL = "DELETE FROM " + self.entity;

        self.db.transaction(function(tx){
            tx.executeSql(strSQL);
        }, self.transactionError);
    },

    /**
     * Get `all` items from table. 
     * 
     * @param  {Function} fn    Callback with results
     * @return {Object}         this instance
     */
    all: function(fn){
        this.query.where = '';
        return this;
    },

    /**
     * Set liked fields to retrieve.
     * 
     * @param  {String} fields  String separated by `comma` with all fields
     * @return {void}
     */
    fields: function(fields){
        this.query.fields = fields;

        return this;
    },

    /**
     * Find by primary_key with limit 1.
     * 
     * @param  {Integer}    id   Id from row to search
     * @param  {Function}   fn   Callback with response
     * @return {Object}          this instance
     */
    find: function(id, fn){
        
        var primary_key = this.query.primary_key;
        this.query.where = ' WHERE ? = ?';
        this.query.params.push(primary_key);
        this.query.params.push(id);

       return this;
    },

    /**
     * Find rows by column user's defined 
     * 
     * @param  {String}   key       Column name to search
     * @param  {String}   value     Value to search
     * @param  {Function} fn        Callback with response
     * @return {Object}             this instance
     */
    findBy: function(key, value, fn){

        this.query.where = ' WHERE ? = ?';
        this.query.params.push(key);
        this.query.params.push(value);

        return this;
    },

    /**
     * Get a N firsts elelemts by entity
     * 
     * @return {Object}     this instance
     */
    first: function(){
        this.query.order_by = this.query.primary_key;
        this.query.order_by_direction = 'asc';

        return this;
    },

    /**
     * Get the N last elements by entity
     * 
     * @return {Object}     this instance
     */
    last: function(){
        this.query.order_by = this.query.primary_key;
        this.query.order_by_direction = 'desc';

        return this;        
    },

    /**
     * Define max num of rows returns.
     * 
     * @param  {Integer} limit  Limit of rows to return
     * @return {Object}         this instance
     */
    take: function(limit){
        this.query.limit = 1;

        return this;
    },

    /**
     * Insert array to Table. 
     * The object passed by param needs use the same schema and struct to table.
     * 
     * @param  {Object}   object        Object with all values
     * @param  {Function} fn            Callback with results
     * @return {void}
     */
    insert: function(object, fn){
        var self = this;

        var strSQL = "INSERT INTO ? (?) VALUES (?)";
        var TABLE = self.entity;
        var KEYS = "";
        var VALUES = "";

        // Get all keys and values from Object
        var i = 0;
        for (var key in object) {
            if(i > 0) {
                KEYS += ", ";
                VALUES += ", ";
            }
            KEYS += key;
            VALUES += object[key];
            i++;
        }
        
        // Execute Query
        self.db.transaction(function(tx){
            tx.executeSql(strSQL, [TABLE, KEYS, VALUES], function(tx, result){
                fn(result);
            });
        });
    },

    /**
     * Update table row 
     * 
     * @param  {Integer}    id      Id to find row
     * @param  {Object}     object  New object to update table
     * @param  {Function}   fn      Callback with results
     * @return {void}
     */
    update: function(id, object, fn) {
        var self = this;
        
        var strSQL = "UPDATE ? SET ? WHERE ? = ?";
        var TABLE = self.entity;
        var VALUES = '';
        var PK = self.query.primary_key;

        // Get all keys and values from Object
        var i = 0;
        for (var key in object) {
            if(i > 0) VALUES += ", ";
    
            VALUES += key + ' = "' + object[key] + '"';
            i++;
        }
        
        // Execute Query
        self.db.transaction(function(tx){
            tx.executeSql(strSQL, [TABLE, VALUES, PK, id], function(tx, result){
                fn(result);
            });
        });

    },

    /**
     * Delete row find by primary_key
     * 
     * @param  {Integer}  id    Id to find row to delete     
     * @param  {Function} fn    Callback with response
     * @return {void}         
     */
    delete: function(id, fn){

        var strSQL = 'DELETE FROM ? WHERE ? = ?';
        var self = this;
        self.db.transaction(function(tx){
            tx.executeSql(strSQL, [self.entity, self.query.primary_key, id], function(tx, result){
                fn(result);
            });
        });

    },

    /**
     * Execute SQL Statment and resturn a results.
     * 
     * @param  {Function} fn    Callback with results
     * @return {void}
     */
    get: function(fn){
        var self = this;
        self.db.transaction(function(tx){
            var Query = self.doQuery();
            var strSQL = Query.getString();
            var params = Query.getParams();
            tx.executeSql(strSQL, params, function(tx, result){
                if(result.rows.length > 0 && self.query.limit === 1) fn(result.rows[0]);
                else fn(result.rows);
            });
        }, self.transactionError);
    },
    
    /**
     * Build a Query SQL String with all parameters to use developer.
     * 
     * @return {Object}     2 method to return SQL String AND our Params.
     */
    doQuery: function(){
        
        var query = this.query;
        var entity = this.entity;

        // Set params
        var params = query.params;
        // Construct Query String
        var query_string = query.intention + " "; // SELECT
        query_string += query.fields + " "; // SELECT * 
        query_string += " FROM " + entity + " "; // SELECT * FROM {entity}
        
        if(query.where !== '')
            query_string += query.where; // SELECT * FROM entity WHERE ? = ?

        if(query.order_by !== '')
            query_string += " ORDER BY " + query.order_by + " " + query.order_by_direction; // SELECT * FROM entity WHERE ? = ?

        if(query.limit > 0)
            query_string += " LIMIT " + query.limit;

        return {
            // Get SQL String Generated with all parameters used
            getString: function(){
                return query_string;
            },
            // Get a params passed by methods called 
            getParams: function(){
                return params;
            }
        };
    },

    /**
     * Execute RawQuery
     * 
     * @param  {String}   strSQL    Query
     * @param  {Function} fn        Callback with results
     * @return {void}          
     */
    rawQuery: function(strSQL, params, fn){
         self.db.transaction(function(tx){
            tx.executeSql(strSQL, params, function(tx, result){
                fn(result.rows);
            });
        }, self.transactionError);
    },

    /**
     * ErrorHandle Method.
     * @param  {Object} err     WebSQL Error Exception Object
     * @return {void}
     */
    transactionError: function(err) {
        console.log(err);
    }
};

/*
WarriorSQL: Yes! My Lord.

                     .-'`-.
                    / | |  \
                   /  | |   \
                  |___|_|__  |
                  ||<o>| <o>`|
                  ||   J_   )|
                  `|`-'__`-'|/
                   |  `--'  |
                 .-|        |_
              .-'  \     /  | |`-.
           .-'      `.     /| |   \
          /           ````' | |    \
         |_____             | |     L
      .-' ___   `-.         F F  |  |
    .'.-'  |  `-.  `.      J J   /  |
   / /|    |    |`.  \     | |   |/ |
  / / |    |    |  `. `.   F F   |  |
 J /  |    |    |    \  L J J    |  |
 FJ   |    |    |    |L J/ /     |   \
J |() | () | () | () | J L/      |   |
| F   | .-'_ \  |    |  LJ       | /  L
| L   | /    \\ |    |  | L      |    |
| L   ||     ):||    |  | |     /|     L
J |   ||:._.'::||    |  | |----' |     |
J |   |J:::::::||    |  | |    _/\     |
 LJ   | \:::::/ |    |  | |---'\  |    |
 J L  |  `-:-'  |    |  | F  | \  |    J
  LJ()| () | () | () |  F F  |  \  \--._L
  J \ |    |    |    | J J     \    |  |
   \ \|    |    |    | / /    |     |  |
    \ \    |    |    |/ /|     |    | .-'|
     `.`.  |    |   .'.' |     |    |/ /`L
       `.`-.____|.-'.-'  |     |    | <`. \
         `-.______.-'    |    \|    |_`::\ `.
          |  |           |     |    /   \::. \
          |--|           |     _.--|     `::\ `.
          |\\|-.____     |__.-'    |       \::. \
          | >||      `--' J        |        `::\ `.
          |//JJ        |   L       |          \::. \
          |< |J        |   |       (           `::\ `.
          |\\|J        |   /        )            \::. \
          |--|J        |   \       /              `::\ `.
          |  |L `      )   )` `' '|                 \::. \
          |  L \    '  /   / ' |  |                  `::\ |
          F  F J``  -'|    | | |  |                    \:_|
          `-'  | ""   |    J `    |
               |      |     L     |
               |      |     \     |
               J      |      `.   |
                L     F       )`---\
                |    J        /     `.
                J    J       (        `-.
                `-.__/       `---.       `.
                |   J             `.       )
                /   |               `-----'
               /    F
              J    J
              J    |
               `-.-'

 */