Napp SQL for Alloy Framework

NappSQL

/*
 * Napp Framework
 * (c) 2012 Napp ApS
 * www.napp.dk
 * 
 * NAPP SQL ALLOY ADAPTER
 * Dual SQLite/SQL sync adapter which will store all models in
 * an on device database
 */

/*
 * USAGE:
 * config: {
		"columns": {
			"id":"primaryint",
			"title":"string",
			"bodytext":"string",
			"pubDate":"datetime"
			"lastModified":"datetime"
		},
		"adapter": {
			"type": "nappsql",
			"collection_name": "news"
		},
		"settings": {
			"createUrl": "http://www.example.com/create",
			"deleteUrl": "http://www.example.com/delete",
			"readUrl": "http://www.example.com/get",
			"updateUrl": "http://www.example.com/update",
			"modifiedDate":"lastModified",
			"sortBy": "pubDate DESC"
		}
	},	
 */

var _ = require('alloy/underscore')._, db;
var datetime = require('alloy/datetime');

var lastModifiedDateLogicEnabled = false;

function S4() {
   return (((1+Math.random())*0x10000)|0).toString(16).substring(1);
};

function guid() {
   return (S4()+S4()+'-'+S4()+'-'+S4()+'-'+S4()+'-'+S4()+S4()+S4());
};	

function InitAdapter(config) {
	if (!db) {
		if (Ti.Platform.osname === 'mobileweb' || typeof Ti.Database === 'undefined') {
			throw 'No support for Titanium.Database in MobileWeb environment.';
		}
		else {
			db = Ti.Database.open('_alloy_');
		}
		module.exports.db = db;
		
		// create the migration table in case it doesn't exist
		db.execute('CREATE TABLE IF NOT EXISTS migrations (latest TEXT, model TEXT)');
	}
	
	//SETTINGS
	if(!_.isUndefined(config.settings.modifiedDate) && config.settings.modifiedDate != ""){ //Modified date enabled
		lastModifiedDateLogicEnabled = true;	
		Ti.API.info("lastModifiedDateLogicEnabled active!");
	}
	
	return {};
}

function GetMigrationFor(table) {
	var mid;
	// get the latest migratino
	var rs = db.execute('SELECT latest FROM migrations where model = ?', table);
	if (rs.isValidRow()) {
		mid = rs.field(0);
	}
	rs.close();
	return mid;
}

function SQLiteMigrateDB() {
	//TODO: normalize columns at compile time - https://jira.appcelerator.org/browse/ALOY-222
	this.column = function(name)
	{
		switch(name)
		{
			case "primaryint":
			{
				return 'INTEGER PRIMARY KEY';
			}
			case 'string':
			case 'varchar':
			case 'text':
			{
				return 'TEXT';
			}
			case 'int':
			case 'tinyint':
			case 'smallint':
			case 'bigint':
			case 'integer':
			{
				return 'INTEGER';
			}
			case 'double':
			case 'float':
			case 'real':
			{
				return 'REAL';
			}
			case 'blob':
			{
				return 'BLOB';
			}
			case 'decimal':
			case 'number':
			case 'date':
			case 'datetime':
			case 'boolean':
			{
				return 'NUMERIC';
			}
			case 'null':
			{
				return 'NULL';
			}
		}
		return 'TEXT';
	};
	
	this.createTable = function(config) {
		Ti.API.debug('create table migration called for '+config.adapter.collection_name);
		
		var self = this,
			columns = [];
			
		for (var k in config.columns) {
			columns.push(k+" "+self.column(config.columns[k]));
		}
			
		var sql = 'CREATE TABLE '+config.adapter.collection_name+' ( '+columns.join(',')+' )';
		
		db.execute(sql);
	};
	
	this.dropTable = function(name) {
		Ti.API.debug('drop table migration called for '+name);
		db.execute('DROP TABLE IF EXISTS '+name);
	};
}


function GetMigrationForCached(t,m) {
	if (m[t]) {
		return m[t];
	}
	var v = GetMigrationFor(t);
	if (v) {
		m[t] = v;
	}
	return v;
}

function Migrate(migrations) {
	var prev;
	var sqlMigration = new SQLiteMigrateDB;
	var migrationIds = {}; // cache for latest mid by model name
	
	db.execute('BEGIN;');
	
	// iterate through all our migrations and call up/down and the last migration should
	// have the up called but never the down -- the migrations come in pre sorted from
	// oldest to newest based on timestamp
	_.each(migrations,function(migration) {
		var mctx = {};
		migration(mctx);
		var mid = GetMigrationForCached(mctx.name,migrationIds);
		Ti.API.debug('mid = '+mid+', name = '+mctx.name);
		if (!mid || mctx.id > mid) {
			Ti.API.debug('Migration starting to '+mctx.id+' for '+mctx.name);
			if (prev && _.isFunction(prev.down)) {
				prev.down(sqlMigration);
			}
			if (_.isFunction(mctx.up)) {
				mctx.down(sqlMigration);
				mctx.up(sqlMigration);
			}
			prev = mctx;
		}
		else {
			Ti.API.debug('skipping migration '+mctx.id+', already performed');
			prev = null;
		}
	});
	
	if (prev && prev.id) {
		db.execute('DELETE FROM migrations where model = ?', prev.name);
		db.execute('INSERT INTO migrations VALUES (?,?)', prev.id,prev.name);
	}
	
	db.execute('COMMIT;');
}



function Sync(model, method, opts) {
    var settings = model.config.settings;
    Ti.API.debug("NappSQL method " + method);
    switch (method) {
      case "create":
      
      	sqlCreate(model);
        request({
            url: settings.createUrl,
            action: "",
            method: "POST",
            params: model.attributes,
            success: function(_r) {
                var pResult = JSON.parse(_r.text);
                model.set(pResult);
                model.id = pResult["id"];
                opts.success && opts.success(model.toJSON());
            },
            error: function(_r) {
                opts.error && opts.error();
            }
        });
        break;
      case "read":
        if(Ti.Network.online){
        	model.id = opts.id || model.id;
	        request({
	            url: settings.readUrl,
	            action: (model.id ? "/" + model.id : ""),
	            method: "GET",
	            params: opts.params,
	            success: function(_r) {
	                var pResult = JSON.parse(_r.responseText);
	                var currentModels = sqlReadAllFieldIDs(model);
	                var returnArray = [];
	                if (model.id) {
	                    /*var m = new model.config.Model(pResult);
	                    m.id = pResult["id"];*/
                    	
                    	writeModel(model, currentModels, pResult);
                    	model = sqlRead(model, pResult["id"]); //always read from local db
	                } else {
	                	for (var i in pResult) {
		                    /*var m = new model.config.Model(pResult[i]);
		                    returnArray.push(m.toJSON());*/
		                    writeModel(model, currentModels, pResult[i]);
		                }
		                model = sqlRead(model); //always read from local db
		                
	                }
	                //opts.success && opts.success(returnArray);
	                model.trigger("fetch");
	                return;
	            },
	            error: function(_r) {
	                opts.error && opts.error();
	            }
	        });
        } else {
        	//local storage
        	sqlRead(model);
        	model.trigger("fetch");
        }
        
        break;
      case "update":
      	model.id = opts.id || model.id;
      	request({
            url: settings.updateUrl,
            action: (model.id ? "/" + model.id : ""),
            method: "UPDATE",
            params: model.attributes,
            success: function(_r) {
                var pResult = JSON.parse(_r.text);
                model.id = null, opts.success && opts.success(model.toJSON());
            },
            error: function(_r) {
                opts.error && opts.error();
            },
            headers: opts.headers
        });
        sqlUpdate(model);
        break;
      case "delete":
        model.id = opts.id || model.id;
        request({
            url: settings.deleteUrl,
            action: (model.id ? "/" + model.id : ""),
            method: "DELETE",
            params: model.attributes,
            success: function(_r) {
                var pResult = JSON.parse(_r.text);
                model.id = null, opts.success && opts.success(model.toJSON());
            },
            error: function(_r) {
                opts.error && opts.error();
            },
            headers: opts.headers
        });
        sqlDelete(model);
    }
}

var request = function(args) {
	if(!args.url || args.url === ""){ //only enable REST API if url is defined
		return;
	}
	if(Ti.Network.online){
		var headers = {};
		headers = _.extend(headers, args.headers);
	    var xhr = Ti.Network.createHTTPClient();
	    xhr.onload = function() {
	        try {
	        	args.success(this);
	        }
	        catch(e) {
	        	args.error(e);
	        	return;
	        }
	    };
		xhr.onerror = function(e) {
	        args.error(e);
	        return;	
	    };
	    
	    if (args.method === "PUT" || args.method === "POST") {
	    	headers = _.extend(headers, {
		        "Content-Type": "application/json"
		    });
	    }
	    
	    xhr.open(args.method, args.url+args.action); 
	    xhr.setTimeout(15000); //default: 15 seconds
	    xhr.send(); 
   	} else {
   		//offline 
   		args.error("offline");
   	}
}


function writeModel(model, currentModelsArray, data){
	var settings = model.config.settings;
	if(_.indexOf(currentModelsArray, Number(data["id"])) != -1){ //does model exist in db
		if(lastModifiedDateLogicEnabled){ //Modified date logic enabled
			var newDate = datetime.stringToDate(data[settings.modifiedDate]);
			var oldDate = sqlReadFieldByID(model, settings.modifiedDate, data["id"]);
			if(newDate > oldDate){
				//no logic enabled - just update model
				sqlUpdate(model, data);
				return;
			}
		}else {
			sqlUpdate(model, data); //no logic enabled - just update model
			return;
		}	
    }else {
    	sqlCreate(model, data); //create new model
    	return;
    }
}

/*
 * SQL INTERFACE
 */

/**
 * sqlReadFieldByID get field name by ID
 * @param {Object} model
 * @param {String} fieldTag
 * @param {int} id
 * @return {int} id
 */
function sqlReadFieldByID(model, fieldTag, id){
	var table =  model.config.adapter.collection_name;
	var sql = 'SELECT '+fieldTag+' FROM '+table+' WHERE id='+id;
	var rs = db.execute(sql);
	var output;
	while(rs.isValidRow()) {
		output = rs.fieldByName(fieldTag); 
		rs.next();
	}
	rs.close();
	return output;
}

/**
 * Get all ids from db
 * @param {Object} model
 * @return {array} ids
 */
function sqlReadAllFieldIDs(model){
	var table =  model.config.adapter.collection_name;
	var sql = 'SELECT id FROM '+table;
	var rs = db.execute(sql);
	var len = 0;
	var output = [];
	while(rs.isValidRow())
	{
		output.push(rs.fieldByName("id")); 
		len++;
		rs.next();
	}
	rs.close();
	return output;
}


function sqlCreate(model, data){
	var table =  model.config.adapter.collection_name;
	var columns = model.config.columns;
	var names = [];
	var values = [];
	var q = [];
	for (var k in columns) {
		names.push(k);
		values.push(data[k]);
		q.push('?');
	}
	var sql = 'INSERT INTO '+table+' ('+names.join(',')+') VALUES ('+q.join(',')+')';
	
	db.execute(sql, values);
}

function sqlRead(model, id){
	var table =  model.config.adapter.collection_name;
	var columns = model.config.columns;
	var sql = 'SELECT * FROM '+table;
	if(id){
		sql += ' WHERE id='+id;
	}
	if(model.config.settings.sortBy){
		sql += ' ORDER BY ' + model.config.settings.sortBy;	
	}
	var rs = db.execute(sql);
	var len = 0;
	while(rs.isValidRow())
	{
		var o = {};
        var fc = 0;

        // Pre-3.0.0, Android supported fieldCount as a property, iOS
        // supported it as a function. Post-3.0.0, both are a property.
		fc = _.isFunction(rs.fieldCount) ? rs.fieldCount() : rs.fieldCount;

		_.times(fc,function(c){
			var fn = rs.fieldName(c);
			o[fn] = rs.fieldByName(fn);
		});
		var m = new model.config.Model(o);
		model.models.push(m);
		len++;
		rs.next();
	}
	rs.close();
	model.length = len;
	return model;
}

function sqlUpdate(model, data){
	var table =  model.config.adapter.collection_name;
	var columns = model.config.columns;
	var names = [];
	var values = [];
	var q = [];
	for (var k in columns)
	{
		names.push(k+'=?');
		values.push(data[k]);
		q.push('?');
	}
	var sql = 'UPDATE '+table+' SET '+names.join(',')+' WHERE id=?';
	
    var e = sql +','+values.join(',')+','+data["id"];
    values.push(data["id"]);
	db.execute(sql,values);
}

function sqlDelete(model){
	var table =  model.config.adapter.collection_name;
	var sql = 'DELETE FROM '+table+' WHERE id=?';
	db.execute(sql, model.id);
	model.id = null;
}


////////////////////////////
// EXPORT
////////////////////////////
module.exports.sync = Sync;

module.exports.beforeModelCreate = function(config) {
	config = config || {};

    InitAdapter(config);

	return config;
};

module.exports.afterModelCreate = function(Model) {
	Model = Model || {};

	
	Model.prototype.config.Model = Model; // needed for fetch operations to initialize the collection from persistent store

	Migrate(Model.migrations);

	return Model;
};