lwb6/legacyworlds-server-data/db-structure/parts/050-updates/120-planet-mining.sql

-- LegacyWorlds Beta 6
-- PostgreSQL database scripts
--
-- Game updates - planet mining computations
--
-- Copyright(C) 2004-2012, DeepClone Development
-- --------------------------------------------------------


/*
 * Lock the rows and access the data used by the mining update
 * 
 * This function executes a single query which serves the dual purpose of
 * locking all rows from various tables used by the planet mining update
 * and returning the data needed by the computation.
 *
 * As far as locking is concerned, the following tables are locked: 
 *	- Update records are already locked, so we don't care.
 *	- Planets with resource providers are locked for share.
 *	- Resource providers and corresponding resource records are locked
 *		for update
 *	- Resource definitions are locked for share.
 *	- Owning empires, as well as their mining settings and any set of
 *		planet-specific settings for one of the planets we're inspecting,
 *		are locked for share.
 *
 * The data itself is returned as a set of rows using emp.planet_mining_type
 * 
 * Parameters:
 *		_tick			The identifier of the current game update
 */ 
DROP FUNCTION IF EXISTS sys.gu_pmc_get_data( BIGINT );
CREATE FUNCTION sys.gu_pmc_get_data( _tick BIGINT )
	RETURNS SETOF emp.planet_mining_type
	STRICT VOLATILE
	SECURITY INVOKER
AS $gu_pmc_get_data$
	SELECT planet_id AS planet ,
			resource_name_id AS resource,
			resprov_quantity AS quantity ,
			resprov_quantity_max AS quantity_max ,
			resprov_difficulty AS difficulty ,
			empire_id AS empire ,
			( happy_pop / _planet.population ) AS happiness ,
			pmc_weight AS weight ,
			pmc_total AS total_weight

		FROM sys.updates _upd_sys
			INNER JOIN verse.planets_updates _upd_verse
				USING ( update_id , updtype_id , updtgt_id )
			INNER JOIN verse.planets _planet
				USING ( name_id )
			INNER JOIN verse.resource_providers _resprov
				ON planet_id = name_id
			INNER JOIN verse.planet_resources _pres
				USING ( planet_id , resource_name_id )
			LEFT OUTER JOIN (
						SELECT _emp_planet.empire_id , _emp_planet.planet_id ,
								_emset.resource_name_id , pmc_weight , pmc_total ,
								_happ.current AS happy_pop
		
							FROM sys.updates _upd_sys
								INNER JOIN verse.planets_updates _upd_verse
									USING ( update_id , updtype_id , updtgt_id )
								INNER JOIN emp.planets _emp_planet
									ON _emp_planet.planet_id = _upd_verse.name_id 
								INNER JOIN emp.empires _emp
									ON _emp_planet.empire_id = _emp.name_id
								INNER JOIN emp.mining_settings _emset
									USING ( empire_id )
								INNER JOIN verse.planet_happiness _happ
									USING ( planet_id )
								INNER JOIN emp.scaled_mining_weights_view
									USING ( planet_id , resource_name_id)
								INNER JOIN emp.total_mining_weights_view
									USING ( planet_id )
								LEFT OUTER JOIN (
											SELECT * FROM emp.planet_mining_settings
												FOR SHARE
										) AS _pmset
									USING ( empire_id , planet_id , resource_name_id )
	
							WHERE _upd_sys.update_last = $1
								AND _upd_sys.update_state = 'PROCESSING'

							FOR SHARE OF _emp_planet , _emp , _emset , _happ 
					) AS _owner
				USING ( planet_id , resource_name_id )
	
		WHERE _upd_sys.update_last = $1
			AND _upd_sys.update_state = 'PROCESSING'
	
		FOR UPDATE OF _resprov , _pres
		FOR SHARE OF _planet ;
$gu_pmc_get_data$ LANGUAGE SQL;


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




/*
 * Planet mining game update
 * --------------------------
 * 
 * Lock all records involved with the update, then update the quantity of
 * resources in resource providers depending on what was mined this turn.
 * Finally update all extraction quantities.
 * 
 * Obtains all records to update for the current batch, then either set the
 * income to 0 without modifying the resource provider if the planet is
 * neutral, or call gu_pmc_update_resource() if there is an owner.
 * 
 * Parameters:
 *		_tick		The current tick's identifier
 */
DROP FUNCTION IF EXISTS sys.process_planet_mining_updates( BIGINT );
CREATE FUNCTION sys.process_planet_mining_updates( _tick BIGINT )
	RETURNS VOID
	LANGUAGE SQL
	STRICT VOLATILE
	SECURITY INVOKER
AS $process_planet_mining_updates$
	-- Lock all rows
	SELECT 1 FROM sys.gu_pmc_get_data( $1 );

	-- Execute the actual mining
	UPDATE verse.resource_providers _resprov
		SET resprov_quantity = resprov_quantity - _pres.pres_income
		FROM sys.updates _upd_sys
			INNER JOIN verse.planets_updates _upd_verse
				USING ( update_id , updtype_id , updtgt_id )
			INNER JOIN verse.planet_resources _pres
				ON _pres.planet_id = _upd_verse.name_id
		WHERE _upd_sys.update_last = $1
			AND _upd_sys.update_state = 'PROCESSING'
			AND _pres.planet_id = _resprov.planet_id
			AND _pres.resource_name_id = _resprov.resource_name_id;

	-- Update extraction data
	UPDATE verse.planet_resources
		SET pres_income = emp.mining_compute_extraction( _update_row )
		FROM sys.gu_pmc_get_data( $1 ) _update_row
		WHERE planet_id = _update_row.planet
			AND resource_name_id = _update_row.resource;

$process_planet_mining_updates$;


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

SELECT sys.register_update_type( 'Planets' , 'PlanetMining' ,
		'Resources are being extracted from mines.' ,
		'process_planet_mining_updates'
	);