Appcelerator Titanium Alloy SQLLite Example

app.tss

".container": {
	backgroundColor:"white",
	fullscreen: true
},
"Label": {
	width: Ti.UI.SIZE,
	height: Ti.UI.SIZE,
	color: "#000"
},
"TextField": {
	left: "10",
	width: Ti.UI.FILL,
	color: "black",
	clearButtonMode:Titanium.UI.INPUT_BUTTONMODE_ONFOCUS,
	//returnKeyType: Titanium.UI.RETURNKEY_DONE,
	autocapitalization:Ti.UI.TEXT_AUTOCAPITALIZATION_NONE,
	autocorrect: false
},
"TableViewRow": {
	height: "40",
	hasChild: true
},
"TableView": {
	height: Ti.UI.FILL
}

dataRow.js

var args = arguments[0] || {};

// Ti.API.info("row created, args.name = "+args.name);

$.nameLBL.text = args.name || "<empty>";
//$.idLBL.text = args.id || "<empty>";

$.dataTVR.id = args.id || null;

dataRow.tss

".container" : {
  
}

dataRow.xml

<Alloy>
	<TableViewRow id="dataTVR" class="dataTVR" layout="vertical" height="40">
		<Label id="nameLBL" left="10"/>
	</TableViewRow>
</Alloy>

demodata.js

exports.data = [
	{id: 0, category: "Checking", name: "Total Checking", selected: false, image: '/images/prod0'+Math.floor((Math.random() * 9))+'.PNG', description: "FREE Debit Card\n\nFREE Online Banking and Bill Pay\n\nFREE Mobile banking\n\nFREE Account Alerts\n\nFREE Access to more than 17,000 ATMs and 5,500 branches nationwide.\n\n\n\nMinimum Deposit to Open\n\n$25\n\nNo Monthly Service Fee*\n\nWith monthly direct deposits totaling $500 or more made to this account.**\n\nOR with a $1,500 minimum daily balance\n\nOR, with an average daily balance of $5,000 or more in linked deposits2/investments.\n\nOtherwise, a $12 Monthly Service Fee applies."},
	{id: 1, category: "Checking", name: "Premier Plus Checking", selected: false, image: '/images/prod0'+Math.floor((Math.random() * 9))+'.PNG', description: "All the benefits of Total Checking, plus:\n\nEarns interest\n\nFREE designer checks\n\nNo ATM fee charges for the first four Inquiries, Transfers and Withdrawals each statement period for using another institution«s ATM\n\nFREE Safe Deposit Box\n\nNo fee for money orders, official checks, travelers checks and gift cards\n\nPlus Savings with no Monthly Service Fee and higher interest rates\n\nTwo additional Premier Plus Checking accounts with no Monthly Service Fee\n\nBusinessSelect Checking account with no Monthly Service Fee\n\n\n\nMinimum Deposit to Open\n\n$25\n\nNo Monthly Service Fee\n\nWith an average daily balance of $15,000 or more in linked deposits/investments\n\nOtherwise a $25 monthly Service Fee"},
	{id: 2, category: "Checking", name: "Premier Platinum Checking", selected: false, image: '/images/prod0'+Math.floor((Math.random() * 9))+'.PNG', description: "All the benefits of Premier Checking, plus:\n\nEarns interest\n\nFREE Personal Style checks\n\nNo ATM fee charged for using another institution«s ATM\n\nNo Overdraft Protection Transfer Fee\n\nNo Incoming Wire Transfer Fee\n\nNo Stop Payment Fee\n\nPlus Savings with no Monthly Service Fee and higher interest rates\n\nNine additional Chase Premier Platinum Checking accounts with no Monthly Service Fee\n\nBusinessSelect Checking account with no Monthly Service Fee\n\nPriority telephone serviceÐyou«ll always speak to a specialist who can help you.\n\n\n\nMinimum Deposit to Open\n\n$100\n\nNo Monthly Service Fee\n\nWith an average daily balance of $75,000 or more in linked deposits/investments\n\nOtherwise a $35 monthly Service Fee applies"}
];

index.js

var data = [];

$.dataTV.addEventListener('click', function(e){
	Ti.API.debug("index: $.dataTV.addEventListener(click), e.row.id = "+JSON.stringify(e.row.id));
	
	// Delete item
	// var items = Alloy.createCollection("Items");
	// items.execute('DELETE FROM Items WHERE id=?',e.row.id);
	
	// var db = Ti.Database.open("_alloy_");
	// db.execute('DELETE FROM Items WHERE id=?',JSON.stringify(e.row.id));
	
	var m = Alloy.createModel("Items");
	m.fetch({query:"SELECT * FROM Items WHERE alloy_id='"+e.row.id+"'"});
	Ti.API.debug(m.toJSON());
	m.destroy();
	SQL2Table($.dataTV);
});

function loadSQLClick(e) {
	Ti.API.debug("index: loadSQLclick()");
	
	loadSQL(data);
}

function loadSQL(data) {
	Ti.API.debug("index: loadSQL(), data = "+JSON.stringify(data));
	
	for(var i=0; i<data.length; i++){
		
		var item = Alloy.createModel('items',{
            "name" : data[i].name
    		});	
    		
    		item.save();
    		Ti.API.debug(item.toJSON());
	}
}

function clearSQLClick(e) {
	Ti.API.debug("index: clearSQLclick()");
	
	clearSQL(data);
}

function clearSQL(data) {
	Ti.API.debug("index: clearSQL()");
	
	var items = Alloy.createCollection("Items");
	items.drop();
	
	var rd = [];
	$.dataTV.data=rd;
}

function SQL2TableClick(table) {
	Ti.API.debug("index: SQL2TableClick()");
	
	SQL2Table($.dataTV);
}

function SQL2Table(table) {
	Ti.API.debug("index: SQL2Table()");
	
	var rows = [];
	
	var items = Alloy.createCollection("Items");
    items.fetch();
    Ti.API.debug("items.length = "+items.models.length);
    
    _.each(items.models, function (item,i) {
    		var model = item.toJSON();
		Ti.API.debug(model.name);
		
		// Ti.API.debug(item.get("name"));
		rows.push(Alloy.createController('dataRow', {
				name: model.name,
				id: model.alloy_id
			}).getView());
	});
	
	table.setData(rows);
}

function loadDemoData(data) {
	Ti.API.debug("index: load data from demo data set, data = "+JSON.stringify(data));
	
	var demoData = require('demodata'); 
	for(var i=0;i<demoData.data.length;i++) {
		data.push(demoData.data[i]); 
	}
	
	Ti.API.debug("index: load data from demo data set done, data = "+JSON.stringify(data));
}

function loadTable(data) {
	Ti.API.debug("index: loadTable()");
	
	Ti.API.debug("data = "+data);
	Ti.API.debug("data.length = "+data.length);
	
    var rows = [];
    var i = 0;
    
    if(data.length>0){
        
        _.each(data, function(item) {
        		rows.push(Alloy.createController('dataRow', {
				name: item.name
			}).getView());
		});
    
	}
	else {
		alert("No data");
	}
	
	$.dataTV.setData(rows);
}

loadDemoData(data);
SQL2Table($.dataTV);

$.index.open();	

index.tss

".container": {
	backgroundColor:"white"
},
"Label": {
	width: Ti.UI.SIZE,
	height: Ti.UI.SIZE,
	color: "#000"
} 

"#label": {
	font: {
		fontSize: 12
	}
}

index.xml

<Alloy>
	<Window class="container">
		<View id="containerVW" layout="vertical">
			<View id="headerVW" height="40" backgroundColor="black">
				<Label id="titleLBL" color="white">Hello SQLLite</Label>
			</View>
			<View id="buttonVW" height="Ti.UI.SIZE">
				<View layout="horizontal" height="Ti.UI.SIZE">
					<Button left="30" onClick="loadSQLClick">LoadSQL</Button>
					<Button left="20" onClick="clearSQLClick">ClearSQL</Button>
					<Button left="20" onClick="SQL2TableClick">SQL->Table</Button>
				</View>
			</View>
			<TableView id="dataTV" height="Ti.UI.FILL" />
		</View>
	</Window>
</Alloy>

items.js

exports.definition = {
	config: {
		columns: {
		    "name": "TEXT"
		},
		adapter: {
			type: "sql",
			collection_name: "Items"
		}
	},
	extendModel: function(Model) {
		_.extend(Model.prototype, {
			// extended functions and properties go here
		});

		return Model;
	},
	extendCollection: function(Collection) {
		_.extend(Collection.prototype, {
			// extended functions and properties go here
			drop: function(){
				var db = Ti.Database.open("_alloy_");
				db.execute('DELETE from Items');
			}
		});

		return Collection;
	}
};