define_type:'loadBalancer',
	-priority	=	'replace',
	-description 	=	'	Lasso based datasource Load Balancer. 2007-04-25 '
				'	 - Works with multiple datasources'
				'	 - Checks that database and table exists before suggesting host'
				'	 - Offline / crashed datasources do not caused hold up '
				'	 - Makes weighted decisions when datasource is Mysql '
				'	 - falls back on default Lasso setup on error'

				'	Developed and extrapolated by Ke Carlton (designke.com)'
				'	Spawned from a concept by Jim VH. '
				'	Requires Lasso 8.5+ (Lasso 8 - changes should be minimal)'
				'	Please include the text when distributed / used.	
				'	Any bugs, comments or improvements please post to http://tagswap.net/loadBalancer'
				'	 - has been tested with Mysql and Filemaker 6.0 (with multiple web connectors)'
				'	 - test thoroughly before use - requires replicated datasources'
				
				'	Could possibly be improved by loading datasource info from Lasso internal DB'
				
				" 	Usage:
				
					//	Place type definition in startup folder or define within the global namespace.
					//	loadBalancer installs itself as a global when first called.
						
					//		use loadBalancer(-install) to re-install
					//		use loadBalancer(-uninstall) to un-install

					//	Hosts, can be specified within onCreate, or added after initialisation
					
					//	When loadBalancing different types of datasources as a safety precaution specify the -datasource
					//	eg. loadBalancer->use(-database='db',-table='table',-datasource='fmremotedatasource')		
				
											
					loadBalancer->add(	-name		= 'sq11',
								-username	= 'root',
								-password	= 'password');	
								
					loadBalancer->add(	-name		= 'sq12',
								-username	= 'root',
								-password	= 'password');	
							
					loadBalancer->add(	-name		= 'sq13',
								-username	= 'root',
								-password	= 'password';	
						
					inline:loadBalancer->use(-database='mysql',-table='db'),-findall;
						loadBalancer->info(action_params);
					/inline;
					
					inline:loadBalancer->use(-database='mysql',-table='db'),-findall;
						loadBalancer->info(action_params);
					/inline;
										
					";
					
	local:'_version'		= 0.5;
	local:'_global' 		= '__loadbalancer__';	// global to install loadBalancer
	local:'_datasource' 		= 'MySQLDS';		// default datasource
	local:'_datasources'		= 'MySQLDS,FMServerAdvanced,FMRemoteDataSource'->split(',');	// Tested datasources for automatic loading
	local:'_port' 			= '3306';		// default datasource port11
	local:'_frequency'		= 20000;		// milliseconds, interval between polling datasources (probably should be datasource dependent)
	local:'_timeout'		= 1000;			// milliseconds, time to wait for poll result before reverting to last used (again, probably should be datasource dependent)
	local:'_networkAllowance'	= 5;			// milliseconds, time to wait for poll results before deciding which to use 
	local:'_lastchecked'		= 0;			// timestamp, milliseconds	
	local:'_lastused'		= '';			// string, name of last used host
	local:'_offlineRecheck'		= 60000;		// milliseconds, interval wait to check if datasource back online (allows breathing time for restarts, crashed systems etc...)
	local:'_ranks'			= '';
	
	//	Common datasouces and ports	
	//	MySQLDS:		3306
	//	FMRemoteDataSource:	591
	//	FMServerAdvanced:	80
	
	private;
		local:'_hosts' 	= map;
		local:'_host'	= map;
		
		define_tag:'setLocals';
			self->properties->first->insertfrom(locals->iterator);
		/define_tag;	
		
		define_tag:'_unknowntag';
			return:@self->properties->first->find(tag_name);
		/define_tag;	
		
		define_tag:'tag',
			-description='Returns internal tag',
			-optional='tagname';
					
			return:self->properties->second->find(local('tagname'));
			
		/define_tag;
		
		
		define_tag:'auto',
			-description	= 'Loads all compatible datasources from lasso_internal (not recommended)',
			-Privileged;
			
			protect;
				auth_admin;

				local:'sql'='	select d.name as datasource,h.name,h.port,h.username,h.password 
						from  security_datasources d
						left join security_datasource_hosts h on h.id_datasource = d.id
						where d.enabled= "Y"';
						
				inline:-database='lasso_internal',-table='security_datasources',-sql=#sql;
					records;
						if:self->'_datasources' >> field('datasource');
							self->add(	-datasource	= field('datasource'),
									-name		= field('name'),
									-port		= field('port'),
									-username	= field('username'),
									-password	= field('password')
								);
						/if;
				
					/records;
				/inline;
			/protect;
		/define_tag;

	/private;	

		define_tag:'hosts';
			return:self->'_hosts';
		/define_tag;
						
	define_tag:'onCreate',
		-description	= 'Returns reference to itself when exists (this allows for continuous  tracking and removes the need to schedule any task), '
				  'Specifying -install will load a new loadBalancer into the global name space'
				  'Specifying -uninstall will un-install loadBalancer';

		if:local_defined('uninstall');
			global_remove(self->_global);
			self = null;

		else:global_defined(self->_global)&&!local_defined('install');
			self = @global(self->_global);

		else;
			global:	self->_global=@self;
		/if;
		
		if:local_defined('auto');
			// Load datasources from Lasso_internal
			self->auto;
		/if;
		
		//	Default hosts can be specified here.
		/*
			loadBalancer->add(	-name		= "sq11",
								-username	= "root",
								-password	= "password");	
	
			loadBalancer->add(	-name		= "sq12",
								-username	= "root",
								-password	= "password");	

			loadBalancer->add(	-name		= "sq13",
								-username	= "root",
								-password	= "password");	
		*/

		return;
		
	/define_tag;


	define_tag:'onSerialize';
		return:null;	
	/define_tag;

	define_tag:'version';
		return:self->'_version';		
	/define_tag;
	
	define_tag:'onConvert';
		// Throw error when converted
		 fail:-1,'Please call the "use" tag: loadBalancer->use(-database=\'db\', -table=\'table\'); // Version 'self->_version;
	/define_tag;

	
	define_tag:'check',
		-description = 'Returns true if it\'s time to check the database';
		
		if: self->_frequency < math_sub(_date_msec,self->_lastchecked) || self->_lastchecked == 0;
			self->_lastchecked = _date_msec;
			return: true;
		/if;
		return: false;
	/define_tag;


	define_tag:'info',
		-description	= 'Extracts result map from resulting inline. '
				  'Must be passed action_params: '
				  'loadBalancer->info(action_params); ',
		-optional	= 'info',
		-encodenone;

		protect;
			if:#info >> '-info';
				return: #info->get(#info->findposition('-info')->first)->second;
			/if;
		/protect;
	
		return:map;
		
	/define_tag;


	define_tag:'remove', 
		-description	= 'Removes host from internal host map'
				  'requires host name to be removed',
		-optional	= 'name';
				
		fail_if:!local_defined('name'),-1,'Remove host, requires host name';
		self->_hosts->remove(local('name'));		
				
	/define_tag;

	define_tag:'add', 
		-description	='Adds new host to internal host map. '
				  '-datasource is optional, if not specified defaults to self->_datasource'
				  '-port is optional, if not specified defaults to self->_port'
				  '-name must be unique, different datasources on the same host require different host names'
				  '-username and -password are optional - but recommended!',
					  
		-optional	= 'datasource',
		-optional	= 'name',
		-optional	= 'port',
		-optional	= 'username',
		-optional	= 'password';
		
		!local_defined('datasource')	? 	local:'datasource'	= self->_datasource;
		!local_defined('port')		? 	local:'port'		= self->_port;
		!local_defined('open')		? 	local:'open'		= 0;
		!local_defined('hits')		? 	local:'hits'		= 0;
		
		local:'checkedTables'		= map;
		
		self->_hosts->insert(#name=locals);
		
	/define_tag;
	
	define_tag:'use',
		-description	= 'Returns array of pairs: '
				  '	-host		= array, host to use (when successful)'
				  '	-database	= string, database name '
				  '	-table 		= string, table name '
				  '	-info 		= map, info about results '
				  'Specifying a -datasource will filter out other datasources from checks - recommended when working with multiple datasource types',
					  
		-optional	= 'datasource',
		-optional	= 'database',
		-optional	= 'table';
	
		fail_if: local('database')	=='', -1,'loadBalancer->use requires a valid -database name';
		fail_if: local('table')		=='', -1,'loadBalancer->use requires a valid -table name';

		//	Determine host to use	

		local: 'name' = self->'_hosts'->size > 0 ? 	@self->pick(	-datasource	= local('datasource'),
										-database	= local('database'),
										-table		= local('table'))
							 |	'default';
										
		local:'inline'		= array;
		local:'info'		= map;
		local:'database'	!= ''	? #inline->insert(	-database	= #database);
		local:'table'		!= ''	? #inline->insert(	-table		= #table);
		
		#info->insert('name'=#name);

		if:#name == 'default';
			#inline->insert(-info = #info);
			return:#inline;
		/if;
		
		local: 'host' 		= @self->_hosts->find(#name);		
		local: 'threads'	= integer(math_add(#host->find('threads'),1));
		local: 'speed'		= decimal(#host->find('speed'));
		local: 'time'		= decimal(#host->find('time'));
		local: 'sort'		= (#speed * #threads * #time);
		local: 'hits'		= math_add(#host->find('hits'),1);
		
		#info->insert('speed'	= #speed);
		#info->insert('threads'	= #host->find('threads'));
		#info->insert('hits'	= #hits);
		#info->insert('sort'	= #host->find('sort'));
		#info->insert('ranks'	= self->'_ranks');
		
		#inline->insert(-info	= #info);
		
		//	Add an open thread to the host (pushes it down the list)
		
		#host->insert('threads'	= integer(#threads));
		#host->insert('sort'	= decimal(#sort));
		#host->insert('hits'	= #hits);
			
		local: 'hostArray'	= array;
		local: 'params'		= 'datasource,name,port,username,password'->split(',');
		
		iterate:#params,local('p');
			#host->keys >> #p ? #hostArray->insert('-'#p=#host->find(#p));
		/iterate;
		
		#inline->insert(-host = #hostArray);				
			
		return:#inline;
			
				
	/define_tag;


	define_tag:'pick',
		-description	= 'Determines which host to use and whether to poll each host.'
				  'loadBalancer->test is called Asynchronously and the results are stored in '
				  'an array then sorted once one of two timeouts occur',
					  
		-optional	= 'datasource',
		-optional	= 'database',
		-optional	= 'table';
				
		local:'check' 	= self->check;
		local:'ranks'	= array;
		
		iterate:self->_hosts->values,local('host');	
			
			//	Does the table exist? - empty if hasn't been checked
			local:'exists'=string(#host->find('checkedTables')->find(#database'.'#table));
			
			if:local('datasource') =='' || local('datasource') == #host->find('datasource');
			
				//	Runs if database.table has not be checked or it's time to recheck datasource
				if:(#check || #exists == '');
				
					self->test(	-host		= @#host,
							-target		= @#ranks,
							-database	= @#database,
							-table		= @#table
						);	
									
				else: #exists == 'true' && #host->find('online') == 'true';
					#ranks->insert(#host->find('sort')=#host->find('name'));
				/if;
			/if;
		/iterate;

		local:'c'=0;
		
		// Wait for first response (allowing for _networkAllowance), if no response before _timeout give up.
		while:	#c < self->_timeout && (self->_networkAllowance > #c || #ranks->size  == 0);
			sleep:1;#c++;
		/while;
		
		#ranks->sort;
		self->'_ranks' = #ranks;

		#ranks->size > 0 ? return:@#ranks->get(1)->second;
		
		return: 'default';

	/define_tag;

	define_tag:'test',
		-description	= 'Tests if host is available, '
				  'scores host based on response time and load (when possible). '
				  'inserts result into referenced local, this allows tag to run with -Async, '
				  'and prevents offline databases from locking up page.',
						  
		-optional = 'host',
		-optional = 'target',
		-optional = 'database',
		-optional = 'table',
		-async;

		
		// Give up after 15 seconds
		lasso_executionTimeLimit:15;
		
		local:'speed'=null(since);
		local:'start' = _date_msec;
		local:'datasource' 	= #host->find('datasource');
		
		(local('testDatabase')	:= #host->find('testDatabase'))	== '' ? #testDatabase = local('database');
		(local('testTable')	:= #host->find('testTable')) 	== '' ? #testTable = local('table');

		local:'inline'=array;
		
		#inline->insert(-database = #testDatabase );
		#inline->insert(-table	  = #testTable);
			
		select:#datasource;
			case:'mysqlds';
				//	Mysql can function on an intelligent basis via processlist (Other SQL sources could probablyfunction in a similar manner)
				//	The first statement returns running processes
				//	The second statement checks if the database exists
				//	If the results set online contains 1 ResultSet_Count then the table does not exist and is considered offline
				#inline->insert(-sql = 'show full processlist;select * from `'#testDatabase'`.`'#testTable'` where 1=0;');	
							
			case;
				//	Utilise -show, as opposed to -findall
				#inline->insert(-show	= '');

		/select;
	
		inline:	#inline,
			-host = (:	-datasource 	= #host->find('datasource'),
					-name		= #host->find('name'),
					-port		= #host->find('port'),
					-username	= #host->find('username'),
					-password	= #host->find('password'));
			
			local:'online' = (field_names->size > 0);
			local:'exists' = (field_names->size > 0 && (#datasource !>> 'mysqlds' || ResultSet_Count == 2));
			
			local:'sort'	= 1;
			local:'speed'	= math_max(0.001,math_mult((_date_msec - #start),0.001));
			local:'threads'	= 1	;
			local:'time'	= 1.0;
			local:'found'	= found_count;
			
			if:#datasource == 'mysqlds';
			//	Process Mysql "show full processlist"
			//	Allows for weighted decision
			
				records;
					// This 
					if:field('Command')!='Sleep' && field('State')!='Waiting for INSERT' && field('info')!='show full processlist';
						local:'vals'=array;
						iterate:field_names,local('f');
							#vals->insert(#f'\t'field(#f));
						/iterate;
						#time = math_add(#time,field('time'));
						#threads+=1;
					
					/if; 
				/records;
			/if;
			
			
			//	Basic scoring calc, works but could be better/weighted
			#sort = #threads * #time * decimal(#speed);

			//	Update the referenced host object
			@#host->find('checkedTables')->insert(#database'.'#table=#exists);
			
			#host->insert(	'online'	= #online);	//	Host.Database online.
			#host->insert(	'exists'	= #exists);	//	Host.Database online.
			#host->insert(	'sort'		= #sort);	//	Decimal, host sort value - lower = higher priority
			#host->insert(	'time' 		= #time);	//	Decimal, total threads running time (defaults to 1)
			#host->insert(	'threads' 	= #threads);	//	Interger, total open threads (defaults to 1)
			#host->insert(	'speed' 	= #speed);	//	Decimal, Response time - seconds
			
			/*//	Un-comment to trouble shoot
			log_always: '['#host->find('name')']\r'(#online > 0 ? 'online' | 'offline')' - ('error_code') 'error_currenterror;
			log_always: 'sort	'	#sort;
			log_always: 'speed	'	#speed;
			log_always: 'threads	'(#threads-1);
			log_always: 'time	'	(#time-1);
			*/					
	
			//	Update referenced priority queue
			if:#exists==true;
				#target->insert(pair(#sort = #host->find('name')));
			/if;
		
		/inline;
						
	/define_tag;	

/define_type;