lwb6/legacyworlds-server-data/db-structure/parts/050-updates/020-empire-research.sql

-- LegacyWorlds Beta 6
-- PostgreSQL database scripts
--
-- Game updates - empire research
--
-- Copyright(C) 2004-2012, DeepClone Development
-- --------------------------------------------------------


/*
 * Empire update data
 * -------------------
 * 
 * This type can be used to return empires along with their "on vacation"
 * status.
 * 
 * FIXME: it should probably be somewhere else, but for now this is the only
 * file that uses it.
 */
DROP TYPE IF EXISTS sys.empire_update_type CASCADE;
CREATE TYPE sys.empire_update_type AS (
	/* The empire's identifier */
	empire_id		INT ,

	/* TRUE if the player is on vacation, FALSE otherwise */
	on_vacation		BOOLEAN
);


/*
 * Lock records and list empires which require a research update
 * --------------------------------------------------------------
 * 
 * This function will lock all records needed for a research update, and
 * return the list of empires to be updated. These empires, in addition to
 * being marked for update, must possess planets and have in-progress
 * research.
 * 
 * Parameters:
 *		_tick		The identifier of the current update cycle
 *
 * Returns a set of:
 * 		empire_id	The empire's identifier
 *		on_vacation	TRUE if the player is on vacation, FALSE otherwise
 */
DROP FUNCTION IF EXISTS sys.gu_research_get_empires( BIGINT ) CASCADE;
CREATE FUNCTION sys.gu_research_get_empires( _tick BIGINT )
	RETURNS SETOF sys.empire_update_type
	LANGUAGE SQL
	STRICT VOLATILE
	SECURITY INVOKER
AS $gu_research_get_empires$

	SELECT DISTINCT * FROM (
		SELECT _empire.name_id AS empire_id ,
				( _vacation.status IS NOT NULL AND
					_vacation.status = 'PROCESSED' ) AS on_vacation
			FROM sys.updates _upd_sys
				INNER JOIN emp.empires_updates _emp_update
					USING ( updtgt_id , updtype_id , update_id )
				INNER JOIN emp.empires _empire
					USING ( name_id )
				INNER JOIN emp.technologies _emp_tech
					ON _emp_tech.empire_id = _empire.name_id
				INNER JOIN defs.technologies _tech
					USING ( technology_name_id )
				INNER JOIN emp.planets _emp_planet
					USING ( empire_id )
				INNER JOIN verse.planets _planet
					ON _emp_planet.planet_id = _planet.name_id
				INNER JOIN verse.planet_happiness _happiness
					USING ( planet_id )
				INNER JOIN naming.empire_names _emp_name
					ON _emp_name.id = _empire.name_id
				INNER JOIN users.credentials _user
					ON _user.address_id = _emp_name.owner_id
				LEFT OUTER JOIN users.vacations _vacation
					ON _vacation.account_id = _emp_name.owner_id
			WHERE _upd_sys.update_last = $1
				AND _upd_sys.update_state = 'PROCESSING'
				AND _emp_tech.emptech_state = 'RESEARCH'
			FOR UPDATE OF _upd_sys , _emp_update , _emp_tech
			FOR SHARE OF _empire , _tech , _emp_planet , _planet ,
				_happiness , _emp_name , _user
		) _sub;

$gu_research_get_empires$;

REVOKE EXECUTE
	ON FUNCTION sys.gu_research_get_empires( BIGINT )
	FROM PUBLIC;


/*
 * Update research for a single empire
 * ------------------------------------
 *
 * This stored procedure updates research points for all in-progress research
 * of a single empire.
 *
 * Parameters:
 * 		_empire_id		The empire's identifier
 *		_on_vacation	TRUE if the player is on vacation, FALSE otherwise
 */
DROP FUNCTION IF EXISTS sys.gu_research_update_empire( INT , BOOLEAN ) CASCADE;
CREATE FUNCTION sys.gu_research_update_empire( _empire INT , _on_vacation BOOLEAN )
		RETURNS VOID
		LANGUAGE PLPGSQL
		STRICT VOLATILE
		SECURITY INVOKER
	AS $gu_research_update_empire$

DECLARE
	_points		DOUBLE PRECISION;
	_record		RECORD;

BEGIN

	_points := emp.research_get_points( _empire , _on_vacation );	
	FOR _record IN
			SELECT _emp_tech.technology_name_id , _emp_tech.emptech_points , _emp_tech.emptech_priority ,
					_points * _weights.emptech_weight / ( _totals.emptech_total_weight * 1440 ) AS emptech_new_points ,
					_def.technology_points::DOUBLE PRECISION AS technology_points
				FROM emp.technologies _emp_tech
					INNER JOIN emp.research_weights_view _weights
						USING ( empire_id , technology_name_id )
					INNER JOIN emp.research_total_weights_view _totals
						USING ( empire_id )
					INNER JOIN defs.technologies _def
						USING ( technology_name_id )
				WHERE _emp_tech.empire_id = _empire
					AND _emp_tech.emptech_state = 'RESEARCH'
	LOOP

		IF _record.emptech_points + _record.emptech_new_points >= _record.technology_points THEN
			UPDATE emp.technologies
				SET emptech_state = 'PENDING' ,
					emptech_points = NULL ,
					emptech_priority = NULL
				WHERE technology_name_id = _record.technology_name_id
					AND empire_id = _empire;

		ELSE
			UPDATE emp.technologies 
				SET emptech_points = emptech_points + _record.emptech_new_points
				WHERE technology_name_id = _record.technology_name_id
					AND empire_id = _empire;
		END IF;

	END LOOP;

END;
$gu_research_update_empire$;

REVOKE EXECUTE
	ON FUNCTION sys.gu_research_update_empire( INT , BOOLEAN )
	FROM PUBLIC;



/*
 * Process a batch of empire research updates
 * -------------------------------------------
 * 
 * Update all empires in the batch which have both in-progress research and
 * planets.
 * 
 * Parameters:
 *		_tick		The identifier of the current update cycle
 */
DROP FUNCTION IF EXISTS sys.process_empire_research_updates( BIGINT ) CASCADE;
CREATE FUNCTION sys.process_empire_research_updates( _tick BIGINT )
	RETURNS VOID
	LANGUAGE SQL
	STRICT VOLATILE
	SECURITY INVOKER
AS $process_empire_research_updates$

	SELECT sys.gu_research_update_empire( empire_id , on_vacation )
		FROM sys.gu_research_get_empires( $1::BIGINT );

$process_empire_research_updates$;

REVOKE EXECUTE
	ON FUNCTION sys.process_empire_research_updates( BIGINT )
	FROM PUBLIC;


SELECT sys.register_update_type( 'Empires' , 'EmpireResearch' ,
		'Empire research points are being attributed to technologies.' ,
		'process_empire_research_updates'
	);