Created
March 5, 2011 18:55
-
-
Save codeboxed/856610 to your computer and use it in GitHub Desktop.
Connect with Titanium to your databases
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
/** | |
* 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