Skip to content

Instantly share code, notes, and snippets.

@kwhinnery
Created July 1, 2010 21:35
Show Gist options
  • Save kwhinnery/460607 to your computer and use it in GitHub Desktop.
Save kwhinnery/460607 to your computer and use it in GitHub Desktop.
// Using the JavaScript module pattern, create a persistence module for CRUD operations
// One tutorial on the Module Pattern: http://www.adequatelygood.com/2010/3/JavaScript-Module-Pattern-In-Depth
var db = (function() {
//create an object which will be our public API
var api = {};
//maintain a database connection we can use
var conn = Titanium.Database.open('todos');
//Initialize the database
conn.execute('CREATE TABLE IF NOT EXISTS todos (id INTEGER PRIMARY KEY, todo TEXT)');
conn.execute('DELETE FROM todos'); //This will delete all data from our table - probably wouldn't do this in production ;)
//Create a TODO - db.create(todo)
api.create = function(text) {
conn.execute('INSERT INTO todos (todo) VALUES(?)',text);
return conn.lastInsertRowId; //return the primary key for the last insert
};
//List all TODOs - db.read()
api.all = function() {
var results = [];
//Get TODOs from database
var resultSet = conn.execute('SELECT * FROM todos');
while (resultSet.isValidRow()) {
results.push({
id: resultSet.fieldByName('id'),
todo: resultSet.fieldByName('todo')
});
resultSet.next();
}
resultSet.close();
return results; //return an array of JavaScript objects reflecting the todo
};
//Get a todo by a specific ID
api.get = function(id) {
var result = null;
var resultSet = conn.execute('SELECT * FROM todos WHERE id = ?', id);
if (resultSet.isValidRow()) {
result = {
id: resultSet.fieldByName("id"),
todo: resultSet.fieldByName("todo")
};
}
resultSet.close();
return result;
};
//Update an exisiting TODO - db.update(todo)
api.update = function(todoItem) {
conn.execute("UPDATE todos SET todo = ? WHERE id = ?", todoItem.todo, todoItem.id);
return conn.rowsAffected; //return the number of rows affected by the last query
};
//Delete a TODO - db.del(todo)
api.del = function(id) {
conn.execute("DELETE FROM todos WHERE id = ?", id);
return conn.rowsAffected; //return the number of rows affected by the last query
};
//return our public API
return api;
}());
// ****************************
// BEGIN DATABASE TEST EXAMPLES
// ****************************
// CREATE AND GET
var lastInsert = db.create("Pick up the milk"); // this should create a TODO
var ourTodo = db.get(lastInsert); //this should fetch the TODO we just created
Ti.API.info("Just created a TODO (Should be 'Pick up the milk'): "+ourTodo.todo);
// ALL
var anotherTodo = db.create("Conquer the world");
var all = db.all();
Ti.API.info("Records in database (Should be 2): "+all.length);
// UPDATE
ourTodo.todo = "Pick up Mountain Dew instead of milk";
var rowsAffected = db.update(ourTodo);
Ti.API.info("Rows affected (Should be 1): "+rowsAffected);
ourTodo = db.get(ourTodo.id);
Ti.API.info("Just got our updated record (Should be 'Pick up Mountain Dew instead of milk'): "+ourTodo.todo);
//DELETE
db.del(ourTodo.id);
var stillThere = db.get(ourTodo.id);
Ti.API.info("Just tried to get a record that aint there. Result should be null: "+stillThere);
@richardvenneman
Copy link

Hi Kevin,

thanks, this code is great. I also love to write my queries on multiple lines, however I'm getting warnings in the console:
[WARN] JavaScript compiler reported "Bad escapement." at home.js:32
[WARN] JavaScript compiler reported "Unclosed string." at home.js:32
[WARN] JavaScript compiler reported "Stopping, unable to continue. (6% scanned)." at home.js:32

I'm getting these warnings but everything is working as expected.

@kwhinnery
Copy link
Author

if you want to do multi-line strings in JavaScript, you would do:

var str = 'My cool \
string goes on \
multiple lines';

@richardvenneman
Copy link

Thanks Kevin. Your example works as well but produces the same [WARN]ings in the console in Titanium Developer. This actually matters since the rest of the file won't be checked for errors.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment