/* db_module.js
 * A Gears worker that is kicked off once and then runs continuously in the background 
 * in support of the Akinity application.
 * This single-threaded worker handles messages from other worker modules requesting write access to the Gears Database.
 * It controls all blocking access to the Db. Read access is conducted directly from the appropriate module.
 * It mitigates deadlocks caused by multi-threaded worker modules simutaneously locking the Db. Required because of coarse locking granularity in sqlite.
 * This module is called by meiosis, synthesis and akin modules. It does not call upon any module. 
 */

function db_module(wp) {
/* constructor
 * 
 */
	return this.run_ (wp); // comment: kick off worker
}

db_module.prototype.run_ = function(wp){
	/* the code to run on the worker  
	 */
	try {
		var sql_wkr_Script = 
		'var lib_include 				= ' +	String(lib_include) 					+';'+
		'lib_include("http://ingendex.akinity.info/third_party/closure/closure/goog/base.js", null); '+
		'google.gears.workerPool.onmessage = ' + String(this.dbWorker) 					+';'+
//		'var goog 						= ' +	String(goog) 			+';' +

		'var module 					=  "db"; ' 										+	// module global
		'var main 						= ' +	String(this.main) 						+';'+
		'var create_objects 			= ' +	String(this.create_objects) 			+';'+
		'var update_culture 			= ' +	String(this.update_culture) 			+';'+
		'var insert_batch			 	= ' +	String(this.insert_batch) 				+';'+
		'var insert_batch_instructions 	= ' +	String(this.insert_batch_instructions) 	+';'+
		'var update_batch			 	= ' +	String(this.update_batch)			 	+';'+
		'var update_batch_instruction 	= ' +	String(this.update_batch_instruction) 	+';'+
		'var setFamSetting 				= ' +	String(this.setFamSetting) 				+';'+
		'var mount_culture 				= ' +	String(this.mount_culture) 				+';'+
		'var insert_binding 			= ' +	String(this.insert_binding) 			+';'+
		'var insert_conts 				= ' +	String(this.insert_conts) 				+';'+
		'var insert_dissimilar 			= ' +	String(this.insert_dissimilar) 			+';'+
		'var activate_binding 			= ' +	String(this.activate_binding) 			+';'+
		'var trx_caller	 				= ' + 	String(this.trx_caller) 				+';'+
// required Closure objects
/*
		'var goog 						= ' +	String(goog) 			+';' +
		'var googgearsDatabase 			= ' +	String(goog.gears.Database) 			+';' +
		'var googgearsBaseStore 		= ' +	String(goog.gears.BaseStore) 			+';' +
*/
		'var insert_debug 				= ' +	String(this.insert_debug) 				+';';

		var SQLWorkerId = wp.createWorker(sql_wkr_Script); 
		return {ID: SQLWorkerId	};
	}
	catch(e){

	}
}   
	 
db_module.prototype.dbWorker = function(a, b, message){

/*
	// Closure Library
	lib_include("http://ingendex.akinity.info/third_party/closure/closure/goog/base.js", main(message));
	// Closure dependencies of this Module
	goog.require('goog.gears.BaseStore');
	goog.require('goog.gears.Database');
	goog.provide('ingendexNS.db_mod.dbObject');
	goog.provide('ingendexNS.db_mod.dbObject.data');

*/	


	main(message)
}

db_module.prototype.main = function(message) {

	var wp 			= google.gears.workerPool;
	var db 			= google.gears.factory.create('beta.database');

	var return_message;
	var mv;
	var im = message;

	function set_mod_state (message) {
		var trx_state = message.body[0];

		return {
			message : message,
			cu:	trx_state.cu,
			sy: trx_state.sy, // synthesis module
			me: trx_state.me, // meiosis module
			ak: trx_state.ak, // akin module
			se: trx_state.se, // settings module
			ba: trx_state.ba, // batch module
			from: {
				ID: 	message.sender,
				module: trx_state.from.module,
				action: trx_state.from.action,
				status: trx_state.from.status,
				message: trx_state.from.message
			},
			dest: {
				ID: 	trx_state.dest.ID,
				module: trx_state.dest.module,
				action: trx_state.dest.action,
				status: trx_state.dest.status,
				message: trx_state.dest.message
			}
		}
	};

	try {
		// comment: pull state from caller
		mv = set_mod_state(message);

		// annotate (debug) instructions unless just debug was specified
		if (mv.dest.action !== 'insert_debug') insert_debug(mv,'db module incoming. From: ' + mv.from.module + '.' + mv.from.action + '.' + mv.from.status + ' To: ' + mv.dest.module + '.' + mv.dest.action + '.' + mv.dest.status);
		
		switch (mv.dest.action) {
			case 'insert_debug':
				insert_debug(mv, mv.dest.message);
				break;
			case 'create_objects':
				create_objects(im, wp, db, mv);
				break;
			case 'activate_binding':
				activate_binding(im, wp, db, mv);
				break;
			case 'insert_dissimilar':
				insert_dissimilar(im, wp, db, mv);
				break;
			case 'mount_culture':
				mount_culture(im, wp, db, mv);
				break;
			case 'update_culture':
				update_culture(im, wp, db, mv);
				break;
			case 'insert_batch':
				insert_batch(im, wp, db, mv);
				break;
			case 'insert_batch_instructions':
				insert_batch_instructions(im, wp, db, mv);
				break;
			case 'update_batch':
				update_batch(im, wp, db, mv);
				break;
			case 'update_batch_instruction':
				update_batch_instruction(im, wp, db, mv);
				break;
			case 'setFamSetting':
				setFamSetting(im, wp, db, mv);
				break;
			case 'insert_binding':
				insert_binding(im, wp, db, mv);
				break;
			case 'insert_conts':
				insert_conts(im, wp, db, mv);
				break;
			default:
//				trx_caller(im, wp, mv, 'db unknown action: '+mv.dest.action, 'exception');
				break;
		}
	// comment: handle errors from this worker
		wp.onerror = function( err ){
		trx_caller (im, wp, mv, err.message, 'error');
		return;
		};
	} 
	catch (e) {
		trx_caller (im, wp, mv, e && e.message, 'exception');
	}
	finally {
		delete wp;
		delete db;
		delete return_message;
	}
};

db_module.prototype.setFamSetting = 	function(im, wp, dbsfs, mv) {
    /* Set the value of a single setting parameter in the profile database.
     */
    try {
		insert_debug(mv,'setFamSetting '+mv.se.param+': '+mv.se.value);
        dbsfs.open('UserSettings');
        dbsfs.execute('begin');
        var rssfs = dbsfs.execute(' update setting set value = ? where user_config = 1 and parameter = ?', [mv.se.value, mv.se.param]);
        dbsfs.execute('commit');
        dbsfs.close;
     } 
	catch (e)
	{
		trx_caller (im, wp, mv, e , ' setFamSetting exception');				
	}
    finally {
        dbsfs.close;
    }
};

db_module.prototype.mount_culture = function(im, wp, dbmc, mv) {

    var init_dt = new Date().getTime();
	var defaultcTag = '1';	// each culture is initated with a default cTag id =1
    try {

		dbmc.open('UserSettings');
		dbmc.execute('begin');
		dbmc.execute('insert into culture (name, last_used, latest_focus_id) values (?, ?, ?)', [mv.cu.name, init_dt, defaultcTag]);
		dbmc.execute('commit');
        dbmc.close;
// insert_debug (mv,"mounted "+mv.cu.name)
		trx_caller (im, wp, mv, init_dt , 'success');
     } 
	catch (e)
	{
		trx_caller (im, wp, mv, 'mount_culture error: '+e , 'error');				
	}
    finally {
        dbmc.close;
    }
}

db_module.prototype.update_culture = 	function(im, wp, dbuc, mv) {
    var init_dt = new Date().getTime();
    try {
        dbuc.open('UserSettings');
 		dbuc.execute('begin');
        dbuc.execute('update culture set last_used = ?, latest_focus_ID = ? where name = ?', [init_dt, mv.cu.focus_id, mv.cu.name]);
        dbuc.execute('commit');
        dbuc.close;
    } 
	catch (e)
	{
//		dbuc.execute('rollback');
		trx_caller (im, wp, mv, e , ' update_culture exception');				
	}
    finally {
        dbuc.close;
    }
};

db_module.prototype.activate_binding = 	function(im, wp, dbab, mv) {
	var pf  ;	// module prefix
	
	try {
		switch (mv.from.module) {
		 	case 'synthesis':
				pf 	= mv.sy;
			break;
		 	case 'meiosis':
				pf 	= mv.me.Z;
			break;
			default:
				trx_caller (im, wp, mv, 'invalid calling module', 'exception');				
				return;
		 }
 insert_debug (mv, "db activate_binding "+pf.binding_id)

		// comment: activate upon final lot insertion. Also set breadth and ID1_reversal (polarity flag)

		dbab.open	(mv.cu.name);
 		dbab.execute('begin');

/*
		dbab.execute('update binding set active_flag = (?), breadth = (?), id1_reversal = 1 ' +
		' where id = (?) '+
		' and id in '+
			' (select id from id_id1 where id = (?)' +
			'  and bit = bit1 '+
			'  group by id ' +
			'  having count(pos) < (?))'
		,[pf.act_flag, pf.breadth, pf.binding_id, pf.binding_id, Math.pow(2,+pf.breadth)/2]);

		if (dbab.rowsAffected !== 1) {

*/			dbab.execute('update binding set active_flag = (?), breadth = (?), id1_reversal = (?) ' +
			' where id = (?) '
			,[pf.act_flag, pf.breadth, pf.id1_reversal, pf.binding_id]);
//		}
	
		dbab.execute('commit');
		
		// Comment: Report back to caller upon completion
		if (dbab.rowsAffected === 1) 	status = 'success';
		else 							status = 'fail';
		
		dbab.close;
		trx_caller (im, wp, mv, null, status);				
	}
	catch (e)
	{
//		dbab.execute('rollback');
		trx_caller (im, wp, mv, e , 'exception');				
	}
	finally {
		dbab.close;
	}
};

db_module.prototype.insert_binding = function(im, wp, dbib, mv){
	var binding_id = false;
	var pf;
	
	try {
		switch(mv.from.module) {
			case 'synthesis':
				pf = mv.sy;
				break;
			case 'meiosis':
				pf = mv.me.Z;
				break;
			};
		insert_debug(mv, 'insert_binding from ' +pf.version+'  '+pf.breadth+'  '+pf.depth+'  '+pf.synth_flag+'  ' +pf.id1_reversal+'  ' +pf.create_dt+'  ' +pf.bv_type+'  ' +pf.act_flag);		

		dbib.open(mv.cu.name);
		dbib.execute('begin');
		dbib.execute(' insert into binding ' +
			' (xparent_id, id1_reversal, description, version, breadth, depth, synthetic_flag, ' +
			' bound_value, created_date, bv_type, active_flag) ' +
			' values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)', 
		[pf.xpar, +pf.id1_reversal || 0, pf.oos, pf.version, pf.breadth, pf.depth, +pf.synth_flag, pf.oos, pf.create_dt, pf.bv_type || 'text', +pf.act_flag]);

		if (binding_id 	= dbib.lastInsertRowId) {
			dbib.execute('end');
			mv.from.status = 'success';
			pf.binding_id = binding_id;
		}
		else throw ("no binding inserted");	

		dbib.close;

//		Comment: Report back to caller upon completion
		trx_caller (im, wp, mv, 'binding:' +binding_id, 'success');				
	} 
	catch (e) {
		mv.from.status = 'exception';
		trx_caller (im, wp, mv, e && e.message, mv.from.status);				
		dbib.execute('rollback');
	}
	finally {
		dbib.close;
	}
};

db_module.prototype.insert_conts =  function(im, wp, dbic, mv) {
	var lot_length;
	var lot_start;
	var pf;
	var pos;
	var skip_to_lot =0;


	try {
		switch (mv.from.module) {
		 	case 'synthesis':
				pf = mv.sy;
				if (typeof pf.skip_to_lot === 'number') skip_to_lot = pf.skip_to_lot;
		 	break;
		 	case 'meiosis':
				pf = mv.me.Z;
		 	break;
		 	default:
				throw ('invalid calling module');
		 	break;
		 }
		 		 
/* comment : do not insert when expansion (step_out) is underway and 
 * it is still expanding up to the previously inserted breadth
 */
		if (pf.lot >= skip_to_lot) {
			lot_length 	= Math.pow(2,pf.mos_breadth);
			lot_start 	= pf.lot * lot_length;
			pos 		= lot_length -1;

insert_debug(mv, 'db module. insert_conts. Lot '+pf.lot)
		
			dbic.open(mv.cu.name);
			dbic.execute ('begin');		
			while (pos >= 0) {
				dbic.execute(
					'insert into contents (id, pos, bit_value, offset) ' +
					' values(?,?,?,?) ',
					[pf.binding_id, lot_start + pos, pf.conts[pos], pf.offset[pos] || 0]);
				pos--;
			}
			dbic.execute ('commit');
//			dbic.close;

			/*Comment: Report back to caller upon completion
 */
			trx_caller (im, wp, mv, 'inserted', 'success');		
			insert_debug(mv, 'insert_conts success. '+lot_length)
		}
		else 	trx_caller (im, wp, mv, 'skipped', 'success');			
	} 
 	catch (e) {
		dbic.execute ('rollback');
		trx_caller (im, wp, mv, e && e.message, 'exception');				
	}
	finally {
		dbic.close;
	}
};

db_module.prototype.insert_dissimilar = function(im, wp, dbid, mv){
	var bits		= mv.ak.result.bits;
	var lot			= mv.ak.result.lot;
	var mos_length	= Math.pow(2,mv.ak.low.mos_breadth);
	var high_ID ; var low_ID
	
	try {

		if (mv.ak.low.binding_id < mv.ak.high.binding_id) {
			high_ID 	= mv.ak.high.binding_id;
			low_ID 		= mv.ak.low.binding_id;
		}
		else {
			low_ID 	= mv.ak.high.binding_id;
			high_ID = mv.ak.low.binding_id;
		}

insert_debug(mv,'insert_dissimilar ' +low_ID+' '+high_ID+' '+lot+' ' +bits+' '+mos_length)

		dbid.open(mv.cu.name);
		dbid.execute('begin');

		if (mos_length === 256) {		
			dbid.execute(' insert into dissimilar_lot256 ' +
			' (low_ID, high_ID, lot, bits) ' +
			' values (?, ?, ?, ?)', [low_ID, high_ID, lot, bits]);
		}
		else {} // extend cTag versions here
	
		dbid.execute('commit');
		dbid.close;
	} 
	catch (e) {
		dbid.execute('rollback');
		trx_caller (im, wp, mv, e &&  e.message, 'exception');				
	}
	finally {
		dbid.close;
	}
};

db_module.prototype.create_objects =  function(im, wp, dbco, mv) {
// Create application objects if they do not already exist in the target database  

 try {
 		dbco.open(mv.cu.name);
		dbco.execute('begin');
// Permanent objects
		
	// binding table and index 
		dbco.execute('create table if not exists ' +
			' binding (' +
           		' id integer primary key autoincrement,' + 
           		' xparent_id integer, ' + 
		   		' description text, ' +
		   		' version text not null, ' +
		   		' breadth integer not null, ' +
		   		' depth integer not null, ' +
				' synthetic_flag text not null check (synthetic_flag in (0,1)),' +  
				' id1_reversal text check (id1_reversal in (0,1)),' +  
				' bv_type text check (bv_type in ("text","URI")), ' +
				' bound_value text, ' +
		   		' created_date integer not null, ' +
				' active_flag text not null check (active_flag in (0,1)))'  
			);

		dbco.execute('create unique index IF NOT EXISTS uk_bind_ID ON binding (id)') ;
		dbco.execute('create index IF NOT EXISTS ix_bind_par_ID ON binding (id, xparent_id)') ;
		dbco.execute('create view if not exists active_binding as select ' +
           		' id, xparent_id, description, version, breadth, depth, synthetic_flag, id1_reversal, bv_type text, bound_value, created_date ' +
				' from binding where active_flag = 1'  
			);

	// contents table and index
		dbco.execute('create table if not exists ' +
			' contents (' +
           		' id integer  , ' + 
           		' pos integer  , ' + 
           		' bit_value integer not null check (bit_value in (0,1)), ' +
           		' offset integer) '  
			);
		dbco.execute('create unique index IF NOT EXISTS uk_conts_ID_POS ON contents(ID, POS)');

	// use this view to compare a cTag's polarity with that of the cTag whose ID is 1. 
		dbco.execute('create view if not exists ' +
			' id_id1 as select ' +
				' a.id id, a.pos pos, a.bit_value bit, b.bit_value bit1 '+
				' from contents a, contents b '+
				' where a.pos = b.pos '+
				' and  a.id <> 1 '+
				' and  b.id = 1 '
			);

	// use this view to compare any cTags in aligned polarity. (all are more similar than dissimilar). 
		dbco.execute('create view if not exists ' +
			' aligned_contents as select ' +
				' a.id id, pos, bit_value, offset '+
				' from contents a, binding b '+
				' where a.id = b.id '+
				' and b.id1_reversal = 0 '+
				' union select ' +
				' a.id id, pos, (bit_value+1)%2, offset '+
				' from contents a, binding b '+
				' where a.id = b.id '+
				' and b.id1_reversal = 1 '
			);

/* Select all pos in order of their selectivity. 
	standard sqlite has no square root, so we remove the negative sign by squaring and let the program do the square root. 
	This view is used to determine the most selective pos for clustering. 
	The highest entropy pos are the most selective ones so are selected first in the view.
	For now, this calculation is only on the first 256 pos. 
*/
		dbco.execute(	
			' create view if not exists pos_selectivity as select '+
			' pos, count (bit_value) count_cTags, sum(bit_value) sum_bits, '+  
			' ( (count (bit_value+0.00000000)/2.00000000) - sum(bit_value +0.00000000) ) *  '+
			' ( (count (bit_value+0.00000000)/2.00000000) - sum(bit_value +0.00000000) ) sq_sel '+
			' from aligned_contents '+
			' where pos < 256 '+
			' group by pos '+
			' order by  sq_sel asc, pos asc '
		);

			
// (Temporary objects)
			
/* Dissimilar table and index
 * Populated dynamically by programs.  
 * Lot length is explicitly hard-coded; 256 pos.
 * high_id must be > low_id. However, no inference should be made about their precedence.
 * Queries on this table or view will know which id column to match, for a given pair.
 */

// note:  table should be temporary 
		dbco.execute('create table if not exists  ' +
//		dbco.execute('create temporary table if not exists  ' +
			' dissimilar_lot256 (' +
          		' low_id 		integer	not null, '		+
          		' high_id 		integer	not null, '		+
           		' lot		 	integer	not null, '		+
          		' bits 			integer	not null check (bits>=0 and bits<=256) '		+
           		' ) '  
			);
		dbco.execute(	' create unique index IF NOT EXISTS uk_dissim_ID_ID_lot ' +
						' on dissimilar_lot256 (low_id, high_id, lot) '
					);

/* Dissimilar view  
 * Lot length is implicit. 
 * Quality of data in view improves with additional processing (larger sample size in table)
 * up to the limit set by least breadth of the two bindings
 * Results are given as positive differences to the mean (256 /2). 
 * Positive implies reveral is operative.
 */


// When not temporary (for testing), must be consistent with a permanent table
		dbco.execute('create view if not exists  ' +
//		dbco.execute('create temporary view if not exists  ' +
			' dissimilar as select ' 			+
          		' low_id, '  					+
          		' high_id, '					+
           		' count(lot) lots, '			+
          		' max( sum(bits) - (count(lot) *128), (count(lot) *128) - sum(bits)  )  bits   '  			+
				' from dissimilar_lot256 ' 		+
				' group by low_id, high_id '	
			);
			
		dbco.execute('create table if not exists ' +																			// input to mass upload process
			' batch (' +
           		' id integer 	primary key autoincrement, ' + 
           		' def_breadth integer 		, ' + 																				//  default parameters for transactions in the batch
           		' max_depth integer 		, ' + 
           		' version text 		 		, ' + 
           		' mos_breadth integer 		, ' + 
           		' max_breadth integer 		, ' + 
         		' dbwID integer 		 	, ' + 
          		' sywID integer 		 	, ' + 
         		' mewID integer 		 	, ' + 
         		' wkrthrottle integer 		, ' + 
		   		' created_date integer not null, ' +
				' active_flag text not null check (active_flag in (0,1)))'  
			);

		dbco.execute('create view if not exists active_batch as select '+
           		' id, def_breadth, max_depth, mos_breadth, max_breadth, dbwID, sywID, mewID, wkrthrottle, created_date '+ 
				' from batch where active_flag = 1'  
			);

		dbco.execute('create table if not exists ' +
			' batch_instruction (' +
           		' id integer not null 		CONSTRAINT fk_batch_id REFERENCES active_batch(id), ' + 	// batch_id
				' seq integer not null , ' + 
           		' pid integer NOT NULL  check (pid is null or pid > 0), ' + 															// result pid
            	' z_binding_id 	integer 	CONSTRAINT fk_z_binding_id REFERENCES active_binding(id),' + // update upon completion of instruction
		   		' z_description text, ' +
           		' conc_type text not null ' +	// type of conception. meiosis or synthesis.
				' check ((conc_type = "me" and (y_pid is not null or y_binding_id is not null)) '+
				' or conc_type = "sy" and ( y_pid is not null or y_binding_id is not null or organelle is not null)), '+  				
           		' x_pid integer , ' + 																	// set to -1 for Mate Select												
           		' x_binding_id 	integer  CONSTRAINT fk_x_binding_id REFERENCES active_binding(id) , ' +  		
            	' organelle text check (organelle is null or (conc_type = "sy"))	 , ' +  
         		' y_pid 		integer , ' + 			
            	' y_binding_id 	integer 	CONSTRAINT fk_y_binding_id REFERENCES active_binding(id), '+
		   		' breadth		integer, ' +															// override batch default
		   		' depth			integer, ' +															// override batch default
				' constraint pk_bi primary key (id, seq))'  

			);

		dbco.execute('create unique index IF NOT EXISTS pk_bat_PID ON batch_instruction (id, pid)') ;
			
		//  integrity constraints that are enforced in sqlite

		dbco.execute("CREATE TRIGGER fkd_bi_batch_id " +
			"BEFORE DELETE ON batch " +
			"FOR EACH ROW BEGIN " +
			"SELECT RAISE(ROLLBACK, 'delete on table batch violates foreign key constraint fk_batch_id') " +
			"WHERE (SELECT id FROM batch_instruction WHERE id = OLD.id) IS NOT NULL; " +
			"END; " 			);
		dbco.execute("CREATE TRIGGER fki_bi_batch_id " +
			"BEFORE INSERT ON batch_instruction " +
			"FOR EACH ROW BEGIN " +
			"SELECT RAISE(ROLLBACK, 'insert on table batch_instruction violates foreign key constraint fk_batch_id') " +
			"WHERE NEW.id IS NOT NULL "+
			"AND  (SELECT id FROM active_batch WHERE id = NEW.id ) IS NULL ; " +
			"END; " 			);
		dbco.execute("CREATE TRIGGER fki_bi_x_binding_id " +
			"BEFORE INSERT ON batch_instruction " +
			"FOR EACH ROW BEGIN " +
			"SELECT RAISE(ROLLBACK, 'insert on table batch_instruction violates foreign key constraint fk_x_binding_id') " +
			"WHERE  ((SELECT id FROM active_binding WHERE id = NEW.x_binding_id ) or NEW.x_binding_id is null) IS NULL ; " +
			"END; " 			);
		dbco.execute("CREATE TRIGGER fki_bi_y_binding_id " +
			"BEFORE INSERT ON batch_instruction " +
			"FOR EACH ROW BEGIN " +
			"SELECT RAISE(ROLLBACK, 'insert on table batch_instruction violates foreign key constraint fk_y_binding_id') " +
			"WHERE  ((SELECT id FROM active_binding WHERE id = NEW.y_binding_id ) or NEW.y_binding_id is null) IS NULL ; " +
			"END; " 			);
		dbco.execute("CREATE TRIGGER fki_bi_z_binding_id " +
			"BEFORE UPDATE ON batch_instruction " +
			"FOR EACH ROW BEGIN " +
			"SELECT RAISE(ROLLBACK, 'update on table batch_instruction violates foreign key constraint fk_z_binding_id') " +
			"WHERE  ((SELECT id FROM active_binding WHERE id = NEW.z_binding_id ) or NEW.z_binding_id is null ) IS NULL ; " +
			"END; " 			);
		dbco.execute("CREATE TRIGGER fki_bi_x_pid " +	// if populated, x_pid must be a valid pid in this batch
			"BEFORE INSERT ON batch_instruction " +
			"FOR EACH ROW BEGIN " +
			"SELECT RAISE(ROLLBACK, 'insert on table batch_instruction violates foreign key constraint fk_x_pid') " +
			"WHERE  ((SELECT pid FROM batch_instruction WHERE  pid = NEW.x_pid and id = NEW.id ) or NEW.x_pid is null or NEW.x_pid = -1) IS NULL ; " +
			"END; " 			);
		dbco.execute("CREATE TRIGGER fki_bi_y_pid " +	// if populated, y_pid must be a valid pid in this batch
			"BEFORE INSERT ON batch_instruction " +
			"FOR EACH ROW BEGIN " +
			"SELECT RAISE(ROLLBACK, 'insert on table batch_instruction violates foreign key constraint fk_y_pid') " +
			"WHERE  ((SELECT pid FROM batch_instruction WHERE pid = NEW.y_pid and id = NEW.id ) or NEW.y_pid is null ) IS NULL ; " +
			"END; " 			);
		dbco.execute("CREATE TRIGGER null_bi_z_binding " +	
			"BEFORE INSERT ON batch_instruction " +
			"FOR EACH ROW BEGIN " +
			"SELECT RAISE(ROLLBACK, 'insert on table batch_instruction must have null z_binding_id') " +
			"WHERE NEW.z_binding_id is not null ; " +
			"END; " 			);

		dbco.execute('end');	
		dbco.close;
		trx_caller (im, wp, mv, 'created objects', 'success');		





// new stuff

		insert_debug(mv,'BaseStore = '+String(goog.gears.BaseStore));

/*
		ingendexNS.db_mod.dbObject.table = function(tablename, cols) { //objects elements are so named for use with goog.gears.BaseStore.createSchema
			this.type 	= goog.gears.BaseStore.SchemaType.TABLE;
		  	this.name	= tablename;
		  	this.columns = cols;
		};
		
		ingendexNS.db_mod.dbObject.data = function () {
			var tableObj = [];
			tableObj[0] = new ingendexNS.db_mod.dbObject.table ('test', ['col1','col2'])
			tableObj[1] = new ingendexNS.db_mod.dbObject.table ('test2', ['colu1','colu2'])
			return tableObj;
		};
		
		ingendexNS.db_mod.dbObject.create = function(objList, user, app){
			var db = new goog.gears.Database(user, app);
			var bs = new goog.gears.BaseStore(db) ;
			bs.createSchema(objList, true);
		}

*/

ingendexNS.db_mod.dbObject.create(ingendexNS.db_mod.dbObject.data(),'henchan', 'testdb');


 }
 catch (e) {
		trx_caller (im, wp, mv, e && e.message, 'exception');				
 }	
 finally {

		dbco.close;
 }		
};

db_module.prototype.insert_batch = function(im, wp, dbib, mv){
	var batch_id = false;
	try {
		var create_dt 		= new Date().getTime();

		dbib.open(mv.cu.name);
		dbib.execute('begin');
		insert_debug(mv,'db insert_batch. breadth='+mv.ba.def_breadth);
		dbib.execute(' insert into batch ' +
		' (def_breadth, max_depth, version, mos_breadth, max_breadth, active_flag, dbwID, sywID, mewID, created_date, wkrthrottle) ' +
		' values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ', 
		[mv.ba.def_breadth, mv.ba.max_depth, mv.ba.version, mv.ba.mos_breadth, 
		mv.ba.max_breadth, mv.ba.active_flag, mv.ba.dbwID, mv.ba.sywID, mv.ba.mewID, create_dt, mv.ba.wkrthrottle]);

		if (batch_id = dbib.lastInsertRowId) { //assignment fails if record was not inserted
			dbib.execute('end');
			insert_debug(mv,'inserted_batch '+batch_id);
			mv.ba.id = batch_id;
			mv.from.status = 'success';
		} 		else throw ("batch not inserted");	
		dbib.close;
		mv.dest.status = 'success';

//		Comment: Report back to caller upon completion
		trx_caller (im, wp, mv, 'batch:' +batch_id, 'success');			
			
	} 
	catch (e) {
		insert_debug(mv,'insert_batch error: '+e);
		trx_caller (im, wp, mv, e && e.message, 'exception');				
		dbib.execute('rollback');
	}
	finally {
		dbib.close;
	}
};

db_module.prototype.insert_batch_instructions =  function(im, wp, dbibi, mv) {
	try {	 		 
			insert_debug(mv,'db module. insert_batch_instructions. ')
			var bi = [];
			var seq = 0;
			var batch_id = 	mv.ba.id;

			dbibi.open(mv.cu.name);
			dbibi.execute ('begin');	

			while (seq < mv.ba.bi.length) {
				bi = mv.ba.bi[seq];
				insert_debug(mv,'db module. insert_batch_instructions. batch : '+batch_id+'. bi.pid : '+bi.pid+'. bi.z_binding_id : '+bi.z_binding_id)
				insert_debug(mv,batch_id+' '+bi.pid+' '+ bi.z_binding_id+' '+ bi.z_description+' '+ bi.conc_type+' '+ bi.x_pid+' '+ bi.x_binding_id+' '+ bi.organelle+' '+ bi.y_pid+' '+ bi.y_binding_id+' '+ bi.breadth+' '+ bi.depth)
				var sqlstr = 	
					' insert into batch_instruction ' +
					' (id, seq, pid, z_binding_id, z_description, conc_type, x_pid, x_binding_id, organelle, y_pid, y_binding_id, breadth, depth) '+
					' values(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ';
				dbibi.execute(sqlstr, [batch_id, seq, bi.pid, bi.z_binding_id, bi.z_description, bi.conc_type, bi.x_pid, bi.x_binding_id, bi.organelle, bi.y_pid, bi.y_binding_id, bi.breadth, bi.depth]);
				seq++;
			}
			dbibi.execute ('end');
			dbibi.close;
			mv.from.status = 'success';

			// Comment: Report back to caller upon completion
			trx_caller (im, wp, mv, 'inserted bi', mv.from.status);		
	} 
 	catch (e) {
		dbibi.execute ('rollback');
		trx_caller (im, wp, mv, e && e.message, 'exception');				
	}
	finally {
		dbibi.close;
	}
};

db_module.prototype.update_batch =  function(im, wp, dbub, mv) {
	try {
			batch = mv.ba;	 		 
			insert_debug(mv,'update_batch. ');
			dbub.open(mv.cu.name);
			dbub.execute ('begin');		
			dbub.execute('update batch set active_flag = 0 where  id = ? ', [batch.id]);
			dbub.execute ('commit');

			trx_caller (im, wp, mv, 'updated batch', 'success');		
			insert_debug(mv,'update_batch success. ')
	} 
 	catch (e) {
		insert_debug(mv,'update_batch exception. '+e || e.message)
	}
	finally {
		dbub.close;
	}
};

db_module.prototype.update_batch_instruction =  function(im, wp, dbubi, mv) {
	try {
			bi = mv.ba.bi;	 		 
			insert_debug(mv,'db module. update_batch_instruction. ')
			dbubi.open(mv.cu.name);
			dbubi.execute ('begin');		
			dbubi.execute(
				'update batch_instruction set x_binding_id = ? '+
				' where  id = ? and x_pid = ? and x_binding_id is null ',
				[bi.z_binding_id, bi.id, bi.pid]);
			dbubi.execute(
				'update batch_instruction set y_binding_id = ? '+
				' where  id = ? and y_pid = ? and y_binding_id is null ',
				[bi.z_binding_id, bi.id, bi.pid]);
			dbubi.execute(
				'update batch_instruction set z_binding_id = ? '+
				' where  id = ? and pid = ? and z_binding_id is null ',
				[bi.z_binding_id, bi.id, bi.pid]);
			dbubi.execute ('commit');

			/*Comment: Report back to caller upon completion
 */
			trx_caller (im, wp, mv, 'updated bi', 'success');		
			insert_debug(mv,'update_batch_instruction success. ')
	} 
 	catch (e) {
		insert_debug(mv,'update_batch_instruction exception. '+e || e.message)
/*
		dbubi.execute ('rollback');
		trx_caller (im, wp, mv, e && e.message, 'exception');				

*/	}
	finally {
		dbubi.close;
	}
};

db_module.prototype.insert_debug = function(mv, dbg_text){

	var ldbd		= google.gears.factory.create('beta.database');

	try {
		ldbd.open('UserSettings');
		ldbd.execute('insert into debug (batch, module, action, status, msg) values (?, ?, ?, ?, ?)', [mv.ba.id, mv.from.module, mv.from.action, mv.from.status, dbg_text]);
		ldbd.close;
	} 
	catch (e) {
	}
	finally {
		ldbd.close;
	}
};

db_module.prototype.trx_caller = function(im, wp, mv, msg, status){
/* Messaging function. 
 * msg and status are optional
 */
//	var wp 		= google.gears.workerPool; 	

	try {
/*
		function reply_call(mv){ // reverse from/call details
			var temp = mv.from;
			mv.from = mv.dest;
			mv.dest = temp;
			delete temp;
		};
		// db module never initiates transactions, so just reply to caller as returning child	
		reply_call(mv);

*/
		mv.dest.module	= mv.from.module;
		mv.from.module	= 'db';
		mv.from.action	= mv.dest.action;
		mv.from.status 	= status;
		mv.dest.action	= null;
		mv.dest.status 	= null;
		insert_debug(mv,'db replying. From: ' + mv.from.module + '.' + mv.from.action + '.' + mv.from.status + '.'+ msg + ' To: ' + mv.dest.module + '.' + mv.dest.action + '.' + mv.dest.status+ '. Worker ID:' + im.sender);
		wp.sendMessage([mv], mv.from.status === 'success' ? im.sender : 0);
	}
	catch (e) {
		insert_debug(mv,'trx_caller error: " '+e);
	}	
};