Skip to content

Instantly share code, notes, and snippets.

@codeboxed
Created March 5, 2011 18:55
Show Gist options
  • Star 1 You must be signed in to star a gist
  • Fork 1 You must be signed in to fork a gist
  • Save codeboxed/856610 to your computer and use it in GitHub Desktop.
Save codeboxed/856610 to your computer and use it in GitHub Desktop.
Connect with Titanium to your databases
/**
* Class name: database.js
* Author: Codeboxed
* URL: http://www.codeboxed.com
* Date: March 5, 2011
* Platform: Titanium
*/
var database = function(dbName, table){
var appDataDir = Titanium.Filesystem.getApplicationDataDirectory();
var separator = Titanium.Filesystem.getSeparator();
var dbPath = appDataDir + separator + dbName;
var db_load = Titanium.Database.openFile(dbPath);
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){
var outputRes = [];
var sql = 'PRAGMA table_info(' + tableName + ')';
var result = execSql(sql);
var rowCount = result.rowCount();
while (result.isValidRow()){
var fieldName = result.field(1);
outputRes.push(fieldName);
result.next();
}
return outputRes;
};
// 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 db = new database("test.db","mytable"); //test.db has to be in the data application folder of the app
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');
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment