n0n1 / jssql

CommonJS wrapper around Ti.Database that makes accessing SQLite Databases feel more like accessing JavaScript objects

JSSQL

With this library you'll be able to use SQLite databases in your Titanium apps using a JavaScript interface. I personally feel comfortable with SQL Syntax, but prefer to use this library because makes the code more readable.

This is not a replacement for Alloy Models.

If you're looking for a full-blown ORM for Titanium you might want to check out Joli.js.

Usage

// require the module
var DBH=require('com.alcoapps.dbhelper');

// create an instance with your local db
var db=new DBH.dbhelper('/alco.sqlite','alco');

SQL SELECT

Returns a JSON object with the full result set.

Option 1

Returns JSON.

var myTable=db.get({
	fields 	: '*',
	table 	: 'events',
	where 	: 'country like "U%"',
	order 	: 'id DESC'
});

Option 2

Send a callback function .

db.get({
	fields 	: '*',
	table 	: 'events',
	where 	: 'country="US"',
	order 	: 'id DESC'
},function(evt){
	console.log(evt);
});

SQL SELECT with JOIN

Returns JSON.

db.get({
    joiner : 'e'
    fields 	: 'e.*',
    table 	: 'events e, parties p',
    where 	: 'e.country="US" AND p.event_id=e.id',
    order 	: 'eid DESC'
},function(evt){
    console.log(evt);
});

SQL LAST ID

Returns the Id of the last inserted row.

db.getLastId({
	table: 'events'
},function(evt){
     console.log(evt);
});

SQL LAST ID

Returns the last inserted row object.

db.getLastEntry({
    table: 'events'
},function(evt){
     console.log(evt);
});

SQL INSERT

Returns the Id of the last inserted row.

var rowId=db.set({
	table: 'events',
	data : {
		country 	: 'Puerto Rico',
		name 		: 'TiConf PR'
	}
});

SQL UPDATE

Returns the amount of rows affected by the edit.

var rowsAffected=db.update({
	table 	: 'events',
	data 	:{
		name : 'xTiConf NY',
		country : 'PR'
	},
	where 	: 'id = 1'
});

or

var rowsAffected=db.update({
		table 	: 'events',
		data 	:{
			name : 'xTiConf NY',
			country : 'PR'
		},
		id: 1
	});

SQL DELETE

Returns the amount of rows affected by the delete.

var rowsAffected=db.delete({
	table 	: "events",
	where 	: 'name="xTiConf PR"'
});

EXEC

Takes an SQL String and returns a JSON object with the result set

var myTable=db.exec('SELECT * FROM events where id > 5');

GET IMAGE FROM BLOB

Returns an image from a Blob column. In this example, assuming you have a column named 'images' inside a table named 'myimages', grab the image with ID=1

var img=db.getImage({
	field: 'image',
	table: 'myimages',
	where: 'id=1'
});
win.backgroundImage=img;

or

db.getImage({
	field: 'image',
	table: 'myimages',
	where: 'id=1'
},function(img){
	win.backgroundImage=img;
});

GET ENTRY

Used when you only want to get a single value from a database record

var userEmail=db.getEntry({
	table: 	'users',
	id: 	'5'
	field: 	'email'
});

or

db.getEntry({
	table: 	'users',
	id: 	'5'
	field: 	'email'
},function(value){
	console.log(value);
});

COUNT ROWS

Returns the amount of rows returned by a give query

var totalActiveUsers=db.countRows({
	table: 	'users',
	where:	'active=true'
});

or

db.countRows({
	table: 	'users',
	where:	'active=true'
},function(value){
	console.log(value);
});

or with join

db.countRows({
    joiner: 'u',
	table: 	'users u, images i',
	where:	'u.active=true AND u.id = i.image_id'
},function(value){
	console.log(value);
});	

CREATE FROM JSON

Takes a flat JSON string and creates a table. Good for times when you get data from a Web Service and then need to search or manipulate the data in any way.

db.createFromJSON(jsonString,'mytable');

DROP

Deletes a table

db.drop('myTable');

ADD COLUMN

Alter a table if the field doesn't exist

db.addColumn('user', 'street', 'VARCHAR');

TABLE EXISTS

Checks if a given table exists.

var exists=db.tableExists('myTable');

CLOSE

Closes the database connection

db.close();

Contributors

• Ricardo Alcocer - Creator and maintainer
• Sebastian Klaus
• Rick Blalock

License

MIT - http://alco.mit-license.org