napp.alloy.adapter.restsql

Description

SQL & RestAPI Sync Adapter for Titanium Alloy Framework. The idea is to combine Restful API with a local sql database.

Response Codes

The adapter has been desinged with the following structure.

200: The request was successful.
201: The resource was successfully created.
204: The request was successful, but we did not send any content back.
304: The request was not modified.
400: The request failed due to an application error, such as a validation error.
401: An API key was either not sent or invalid.
403: The resource does not belong to the authenticated user and is forbidden.
404: The resource was not found.
500: A server error occurred.

Installation

Manual

Grab the latest version from the repo and save the file to PROJECT_FOLDER/app/lib/alloy/sync

Appc NPM

This library can also be installed as NPM dependency. Add it by running in the root of your PROJECT_FOLDER.

npm install alloy-sync-sqlrest --save

This will install the library in PROJECT_FOLDER/app/vendor/alloy/sync. Vendor is being used so you'll be able to ignore this dependency from version control.

How To Use

Simple add the following to your model in PROJECT_FOLDER/app/models/.

exports.definition = {
	config : {
		"columns": {
			"id":"INTEGER PRIMARY KEY",
			"title":"text",
			"modified":"text"
		},
		"URL": "http://urlPathToRestAPIServer.com/api/modelname",
		"debug": 1, //debug mode enabled
		"useStrictValidation":1, // validates each item if all columns are present
		"adapter" : {
			"type" : "sqlrest",
			"collection_name" : "modelname",
			"idAttribute" : "id",
			"deletedAttribute": "my_custom_deleted_variable",
			
			// optimise the amount of data transfer from remote server to app
			"addModifedToUrl": true,
			"lastModifiedColumn": "modified"
		},
		
		//optional
		"headers": { //your custom headers
            "Accept": "application/vnd.stackmob+json; version=0",
	        "X-StackMob-API-Key": "your-stackmob-key"
        },
        
        // delete all models on fetch
		"deleteAllOnFetch": true
	},
	extendModel : function(Model) {
		_.extend(Model.prototype, {});
		return Model;
	},
	extendCollection : function(Collection) {
		_.extend(Collection.prototype, {});
		return Collection;
	}
}

Then add the sqlrest.js to PROJECT_FOLDER/app/assets/alloy/sync/. Create the folders if they dont exist.

Use the debug property in the above example to get logs printed with sql statements and server respose to debug your usage of the sqlrest adapter.

If you plan on storing a field call id in your database table that maps to your model, be sure to specify idAttribute. It is not assumed that model.id will be the primary id in your DB table. If idAttribute is not specified an auto generated GUID will be set as the id along with an alloy_id.

Special Properties

Request Params

Change the url dynamically by using {} e.g. {album_id}.

exports.definition = {
    config : {
        "URL": "http://urlPathToRestAPIServer.com/api/albums/{album_id}/modelname",
	}
}

Use it like this in your controller:

collection.fetch({
	requestparams: {
		album_id: 22
	}
});

Will result in: http://urlPathToRestAPIServer.com/api/albums/22/modelname

Last Modified

Save a timestamp for each model in the database. Use the lastModifiedColumn property in the adapter config to send the HTTP Header "Last-Modifed" with the newest timestamp. This is great for improving the amount of data send from the remote server to the app.

"adapter" : {
	...
	"lastModifiedColumn": "modified"
},

This is tell the adapter which column to store a timestamp every time a model has been changed.

Custom Headers

Define your own custom headers. E.g. to add a BaaS API

"headers": {
	"Accept": "application/vnd.stackmob+json; version=0",
	"X-StackMob-API-Key": "your-stackmob-key"
}

Nested Result Objects

Lets say you are a REST API where the results are nested. Like the Twitter search API. It has the found feeds in a results object. Use the parentNode to specify which child object you want to parse.

config: {
	...
	"parentNode" : "results"
}

It has support for nested objects.

config: {
	...
	"parentNode" : "news.domestic"
}

You can also specify this as a function instead to allow custom parsing the feed. Here is an example:

Feed: http://www.google.com/calendar/feeds/developer-calendar@google.com/public/full?alt=json&orderby=starttime&max-results=15&singleevents=true&sortorder=ascending&futureevents=true

Custom parsing:

parentNode: function (data, options) {
	// check if its a collection
	if (_.isArray(data)) {	
		var entries = [];
		_.each(data.feed.entry, function(_entry) {
			var entry = {};
	
			entry.id = _entry.id.$t;
			entry.startTime = _entry.gd$when[0].startTime;
			entry.endTime = _entry.gd$when[0].endTime;
			entry.title = _entry.title.$t;
			entry.content = _entry.content.$t;
			entry.myCustomPropertyFromFetchOpts = options.query
	
			entries.push(entry);
		});
	
		return entries;
	} else {
		// its a model
		var _entry = data.feed.entry;
		
		var entry = {};
		entry.id = _entry.id.$t;
		entry.startTime = _entry.gd$when[0].startTime;
		entry.endTime = _entry.gd$when[0].endTime;
		entry.title = _entry.title.$t;
		entry.content = _entry.content.$t;
		return entry;
	}
}

useStrictValidation

Some times its important for the app to have some certainty that the data provided by the database is valid and does not contain null data. useStrictValidation runs through the fetch response data and only allow the items which have all columns in the object to be saved to the database.

config: {
	...
	"useStrictValidation":1
}

localOnly

If you want to load/save data from the local SQL database, then add the localOnly property.

collection.fetch({
	localOnly:true
});

initFetchWithLocalData

Set this property to true, if you want to get the local data immediately, and get the remote data when the server returns it.

Notice: This will trigger two fetch calls.

config: {
	...
	"initFetchWithLocalData" : true
}

returnErrorResponse

Set this property to true if you want the error response object from the remote server. Default behaviour is not to return this, but return the data stored locally.

config: {
	...
	"returnErrorResponse" : true
}

disableSaveDataLocallyOnServerError

This property is a way to control what the local sql database should do when the remote server sends back an error of some kind. It could be 401, 404, 500 etc. In these siatuations, sometimes you do not want the adapter to save the data what might be incorrect. Setting this property to true will disable saving data locally when errors occur.

config: {
	...
	"disableSaveDataLocallyOnServerError" : true
}

Extended SQL interface

You can perform a local query, and use a bunch of SQL commands without having to write the actual query. The query is also support btw. Use: select, where, wherenot, orderBy, limit, offset, union, unionAll, intersect, except, like, likeor

collection.fetch({  
	data: {  
		language: "English"
	},  
	sql: {  
		where: {  
			category_id: 2
		},
		wherenot: {
			title: "Hello World"
		},
		orderBy:"title",
		offset:20,
		limit:20,
		like: {
			description: "search query"
		}
	},
	localOnly:true
});

ETag

This feature will only work if your server supports ETags. If you have no idea what this is, then consult your server admin. Start by enabling this feature in the model config, like the following:

config: {
	...
	"eTagEnabled" : true
}

You do not have to do anything more. The adapter will send and recieve the ETag for every single request and store those locally in the Ti.App.Properties namespace.

The adapter uses the IF-NONE-MATCH header to send the newest ETag for the provided url to the server on each request. Once a succesful response is recieved by the adapter, it will store the new ETag automatically.

Notice: This may be a good idea not to use this while developing, because it will cache and store your ETag - which might end up in wrong situations while you are working

Extended Migration methods

createIndex

Adds more indexes to the database table, primary key is already an index. Both one column index and multiple column index are supported. Thanks to @moshemarciano for this feature.

migration.up = function(db) {
	db.createTable({
        columns: {
            id: 'INTEGER PRIMARY KEY',
            time: 'TEXT'
        }
	});
	
	// the new stuff
	db.createIndex({
		'firstIdx' : ['id', 'time'],   
	   	'timeIdx' : 'time'
	});
};

Example - Using infinite scrolling

In the below example - im showing how to use this adapter with alloy scroll widget by @FokkeZB

<TableView id="table">
	<Widget id="is" src="nl.fokkezb.infiniteScroll" onEnd="infiniteCallback" />
</TableView>

function infiniteCallback(e) {	
	// get length before fetch
	var ln = library.models.length;
	collection..fetch({
		// add to url params
		// result in e.g. example.com/todo?offset=0&limit=20
		urlparams : {
			limit : 20, // load 20 each iteration
			offset : ln
		},

		// Add to the collection - Don't reset it
		add : true,
		
		// lets keep the fetching under the radar
		silent : true,

		// return the exact results - so exact results
		returnExactServerResponse: true,
		
		// success callback
		success : function(col) {
			// if no new models have been added - lets call done.
			(col.models.length === ln) ? e.done() : e.success();
		},
		
		// error callback
		error : e.error
	});
}

Napp.alloy.adapter.restsql

Install / Use

README