Titanium Mobile Database Class

database.js

/**
 * Class name: database.js
 * Author: Codeboxed
 * URL: http://www.codeboxed.com
 * Date: June 6, 2011
 * Platform: Titanium
 */

var Database = function(dbPath, dbName, table, tableFields){    
    var db_load_ = Ti.Database.install(dbPath, dbName);

    var schema_ = [];

    /**
     * Private method used to execute queries on the given table
     * @param {string} sql The query string that needs to be executed on the table
     * @returns Database.ResultSet
     */
    var execSql_ = function(sql){
        return db_load_.execute(sql);
    };

    /**
     * Loads the schema of the table
     * 
     */
    var load_ = function(){
        schema_ = tableFields_(table);
    };

    /**
     * Excutes a query in the table given at init time
     * @param {array} fields The array that contains the fields to be extracted from the select query
     * @param {string} limit The lower limit of the returned results
     * @param {string} limit2 The upper limit used to filter the result of the select query
     * @returns {array} An array containing the result of the select query
     */
    var doQuery_ = function(fields, limit, limit2){
        var outputRes = [];

        if (fields == "all") {
             getFields = "*";
        } else {
             if (typeof fields == "object") {
                    getFields = fields.join(', ');
             } else {
                    Ti.API.error("fields should be an array");
                    // Get all fields;
                    getFields = "*";
             }
        }
        
        if ((limit != null) && (limit2 == null)) {
        	limitString = " LIMIT " + limit;
        } else if ((limit != null) && (limit2 != null)) {
        	limitString = " AND ((id>" + limit + ") AND (id<" + limit2 + "))";
        } else {
        	limitString = "";
		}
        
        var sql = 'SELECT ' + getFields + ' FROM ' + table + ' WHERE 1=1 ' + limitString;
        var rows = execSql_(sql);

        while (rows.isValidRow()){

            var rowRes = {};
            for (columns in schema_){
                rowRes[schema_[columns]] = rows.fieldByName(schema_[columns]);
            }
            outputRes.push(rowRes);
            rows.next();
        }

        return outputRes;

    };

    /**
     * Updates a field in the table
     * @param {string} table The table name
     * @param {string} field The field to be updated
     * @param {string} filter The field used to filter the query
     * @param {string} value The value of the filter
     */
    var updateField_ = function(table, field, data, filter, filterValue){
        var sql = 'UPDATE ' + table + ' SET ' + field + '=' + data + ' WHERE '+filter+'=\'' + filterValue + '\'';

        var resultUpdate = execSql_(sql);
    };
    
    /**
     * Get all the table fields from a given table
     * @param {string} tableName This is the table name
     * @returns An array containing the field names from the given table
     */
    var tableFields_ = function(tableName){
        return tableFields;
    };

    // Public
    return{
        init: function(){
            load_();
        },

        updateRow: function(table, field, data, filter, filterValue){
        	updateField_(table, field, data, filter, filterValue);
        },

        getRows: function(fields, limit, limit2){
            return doQuery_(fields, limit, limit2);
        },

        getFields: function(tableName){
            return tableFields_(tableName);
        }

    };
};


//USAGE
var tableFields = ["field1", "field2", "field3"];

var db = new Database("test.db", "test", "mytable", tableFields); // test.db has to be relative to the database.js file
db.init();

var result = db.getRows("all", 30, null); //This will get only 30 results from the query
var result1 = db.getRows("all", 30, 40); //This will get all the rows that have the id between 30 and 40

db.updateRow("mytable", "name", "newname", "id", "15");

var myfields = db.getFields("mytable");