lwb6/legacyworlds-server-data/db-structure/parts/040-functions/145-resource-providers.sql

-- LegacyWorlds Beta 6
-- PostgreSQL database scripts
--
-- Resource providers functions
--
-- Copyright(C) 2004-2012, DeepClone Development
-- --------------------------------------------------------


/*
 * Compute resource provider regeneration
 * 
 * This function computes the quantity in a resource provider after it has
 * been regenerated.
 * 
 * Parameters:
 *		_quantity		The current quantity of resources in the provider
 *		_max			The maximal amount of resources supported by the
 *							provider
 *		_recovery_rate	The provider's recovery rate
 *
 * Returns:
 *		?				The new quantity of resources.
 */
CREATE OR REPLACE FUNCTION verse.compute_provider_regeneration(
				_quantity		DOUBLE PRECISION ,
				_max			DOUBLE PRECISION ,
				_recovery_rate	DOUBLE PRECISION )
		RETURNS DOUBLE PRECISION
		STRICT IMMUTABLE
		SECURITY INVOKER
	AS $compute_provider_regeneration$

DECLARE
	_uc_recovery	DOUBLE PRECISION;
	_uc_dampening	DOUBLE PRECISION;
	_uc_ticks		DOUBLE PRECISION;
	_result			DOUBLE PRECISION;

BEGIN
	_uc_recovery := sys.get_constant( 'game.resources.recovery' );
	_uc_dampening := sys.get_constant( 'game.resources.recoveryDampening' );
	_uc_ticks := 1440; -- FIXME: this should be a constant
	
	_result := ( 1 - _quantity / _max ) ^ _uc_dampening;
	_result := _quantity + _result * _recovery_rate * _uc_recovery / _uc_ticks;

	IF _result > _max THEN
		_result := _max;
	END IF;

	RETURN _result;
END;
$compute_provider_regeneration$ LANGUAGE PLPGSQL;


REVOKE EXECUTE
	ON FUNCTION verse.compute_provider_regeneration(
		DOUBLE PRECISION , DOUBLE PRECISION ,
		DOUBLE PRECISION )
	FROM PUBLIC;



/*
 * Mining settings view
 * ---------------------
 *
 * This view lists mining settings being used on planets owned by empires
 * for each resource providers. The settings are taken from planet-specific
 * settings if they are available, or from empire-wide settings.
 * 
 * Columns:
 *
 *		planet_id			The planet's identifier
 *		resource_name_id	The type of resources
 *		mset_weight			The setting to use for mining priorities
 *		mset_specific		True if the settings are specific for this planet,
 *								false if empire-wise settings are in use. 
 */
DROP VIEW IF EXISTS emp.mining_settings_view CASCADE;
CREATE VIEW emp.mining_settings_view
	AS SELECT planet_id , resource_name_id ,
				( CASE
					WHEN _pl_settings.planet_id IS NULL THEN
						_emp_settings.empmset_weight
					ELSE
						_pl_settings.emppmset_weight
				END ) AS mset_weight ,
				( _pl_settings.planet_id IS NOT NULL ) AS mset_specific
			FROM verse.resource_providers
				INNER JOIN emp.planets
					USING ( planet_id )
				INNER JOIN emp.mining_settings _emp_settings
					USING ( empire_id , resource_name_id )
				LEFT OUTER JOIN emp.planet_mining_settings _pl_settings
					USING ( planet_id , empire_id , resource_name_id );

GRANT SELECT
	ON emp.mining_settings_view
	TO :dbuser;



/*
 * Compute a resource provider's extraction factor
 * 
 * This function computes the extraction factor - a multiplier which makes
 * mining more costly if the difficulty is high or if a provider is almost
 * empty - based on a provider's fill ratio and difficulty.
 * 
 * The complete formula can be read on the Wiki:
 *		https://wiki.legacyworlds.com/wiki/Mining#Resource_provider_extraction
 *
 * Parameters:
 * 		_fill_ratio		The ratio between the provider's current and maximal
 *							quantities
 *		_difficulty		The provider's extraction difficulty.
 *
 * Returns:
 *		?				The provider's extraction factor
 */
DROP FUNCTION IF EXISTS verse.get_extraction_factor( 
	DOUBLE PRECISION , DOUBLE PRECISION );
CREATE FUNCTION verse.get_extraction_factor(
		_fill_ratio DOUBLE PRECISION ,
		_difficulty	DOUBLE PRECISION )
	RETURNS DOUBLE PRECISION
	STRICT IMMUTABLE
	SECURITY INVOKER
AS $get_extraction_factor$

	SELECT ( 1 - $2 * 0.5 ) * POW( $1 , 1.5 + 2 * $2 );

$get_extraction_factor$ LANGUAGE SQL;

REVOKE EXECUTE
	ON FUNCTION verse.get_extraction_factor( 
		DOUBLE PRECISION , DOUBLE PRECISION )
	FROM PUBLIC;


/*
 * Planet resources type
 * ----------------------
 * 
 * This type is used to transmit a planet's resources information to the game
 * server. It contains the resource's description, the planet's economic data
 * and, if there is a resource provider on the planet, the provider's
 * information and mining priority.
 */
DROP TYPE IF EXISTS emp.planet_resources_type CASCADE;
CREATE TYPE emp.planet_resources_type AS (
	/* Text identifier of the resource */
	resource_identifier		TEXT ,
	
	/* Internationalised name of the resource */
	resource_name			TEXT ,
	
	/* Internationalised description of the resource */
	resource_description	TEXT ,
	
	/* Internationalised name of the category the resource is a part of, or
	 * NULL if the resource is not in any category.
	 */
	resource_category		TEXT ,
	
	/* The planet's income for this resource, over a period of 12h RT/ 1 month
	 * GT.
	 */
	pres_income				BIGINT ,
	
	/* The planet's upkeep for this resource, over a period of 12h RT/ 1 month
	 * GT.
	 */
	pres_upkeep				BIGINT ,
	
	/* The current quantity of this resource invested in the planet's build
	 * queues.
	 */
	pres_invested			BIGINT ,
	
	/** The capacity of the resource provider, if there is one, or NULL if
	 * there is no provider.
	 */
	resprov_capacity		BIGINT ,
	
	/** The quantity of resources in the resource provider, if there is one,
	 * or NULL if there is no provider.
	 */
	resprov_quantity		BIGINT ,
	
	/** The extraction difficulty of the resource provider as a percentage, or
	 * NULL if there is no provider.
	 */
	resprov_difficulty		INT ,
	
	/* The mining priority for the resource in question, or NULL if there is no
	 * resource provider.
	 */
	mset_weight				INT
);



/*
 * Access all available information about a planet's resources
 *
 * This function retrieves resource information about an empire-owned planet,
 * and converts it to the format used in the game server (rounded quantities,
 * difficulty as percentage, internationalised strings).
 * 
 * FIXME:
 *		1) pres_invested is always set to 0 in the output
 *		2) time-related computations use hardcoded values
 * 
 * Parameters:
 *		_planet		The planet's identifier
 *
 * Returns:
 *		N/A			Resource information records, ordered using resource
 *						weights.
 */
DROP FUNCTION IF EXISTS emp.get_planet_resources( INT );
CREATE FUNCTION emp.get_planet_resources( _planet INT )
	RETURNS SETOF emp.planet_resources_type
	STRICT STABLE
	SECURITY DEFINER
AS $get_planet_resources$

	SELECT _name_str.name AS resource_identifier ,
			_name_trans.translated_string AS resource_name ,
			_desc_trans.translated_string AS resource_description ,
			_cat_trans.translated_string AS resource_category ,
			FLOOR( pres_income * 720.0 )::BIGINT AS pres_income ,
			CEIL( pres_upkeep * 720.0 )::BIGINT AS pres_upkeep ,
			0::BIGINT AS pres_invested ,
			ROUND( resprov_quantity_max )::BIGINT AS resprov_capacity ,
			ROUND( resprov_quantity )::BIGINT AS resprov_quantity ,
			ROUND( 100.0 * resprov_difficulty )::INT AS resprov_difficulty ,
			mset_weight

		FROM defs.ordered_resources_view
			INNER JOIN verse.planet_resources USING ( resource_name_id )
			INNER JOIN emp.planets USING ( planet_id )
			INNER JOIN naming.empire_names _emp_name
				ON _emp_name.id = empire_id
			INNER JOIN users.credentials _user
				ON _emp_name.owner_id = _user.address_id
			INNER JOIN defs.strings _name_str
				ON _name_str.id = resource_name_id
			INNER JOIN defs.translations _name_trans
				ON _name_trans.string_id = resource_name_id
					AND _name_trans.lang_id = _user.language_id
			INNER JOIN defs.translations _desc_trans
				ON _desc_trans.string_id = resource_description_id
					AND _desc_trans.lang_id = _user.language_id
			LEFT OUTER JOIN defs.translations _cat_trans
				ON _cat_trans.string_id = resource_category_id
					AND _cat_trans.lang_id = _user.language_id
			LEFT OUTER JOIN verse.resource_providers
				USING ( planet_id , resource_name_id )
			LEFT OUTER JOIN emp.mining_settings_view
				USING ( planet_id , resource_name_id )

	WHERE planet_id = $1
	
	ORDER BY resource_ordering;

$get_planet_resources$ LANGUAGE SQL;

REVOKE EXECUTE
	ON FUNCTION emp.get_planet_resources( INT )
	FROM PUBLIC;
GRANT EXECUTE
	ON FUNCTION emp.get_planet_resources( INT )
	TO :dbuser;



/*
 * Planet mining update data
 * --------------------------
 * 
 * This type is used by the records used by planet mining updates to compute a
 * planet's mining output.
 */
DROP TYPE IF EXISTS emp.planet_mining_type CASCADE;
CREATE TYPE emp.planet_mining_type AS (
	/* The planet's identifier */
	planet			INT ,

	/* The resource's identifier */
	resource		INT ,

	/* The provider's quantity of resources */
	quantity		DOUBLE PRECISION ,

	/* The provider's maximal quantity of resources */
	quantity_max	DOUBLE PRECISION ,

	/* The provider's extraction difficulty */
	difficulty		DOUBLE PRECISION ,

	/* The empire who owns the planet, or NULL if the planet is neutral */
	empire			INT ,

	/* The planet's happiness, or NULL if the planet is neutral */
	happiness		REAL ,

	/* The weight computed from the resource's extraction priority as
	 * set by either the empire in general or the planet-specific settings,
	 * or NULL if the planet is neutral.
	 */
	weight			DOUBLE PRECISION ,

	/* The total weight computed from either the empire-wide or the
	 * planet-specific mining settings, or NULL if the planet is neutral.
	 */
	total_weight	DOUBLE PRECISION
);


/*
 * Mining computation - Weights view
 * ----------------------------------
 * 
 * This view computes the actual values used in the mining computations for
 * each resource provider on all empire-owned planets.
 * 
 * Columns:
 *		planet_id			The planet's identifier
 *		resource_name_id	The resource type's identifier
 *		pmc_weight			The computed weight
 */
DROP VIEW IF EXISTS emp.scaled_mining_weights_view CASCADE;
CREATE VIEW emp.scaled_mining_weights_view
	AS SELECT planet_id , resource_name_id ,
				POW( sys.get_constant( 'game.resources.weightBase' ) ,
					mset_weight ) AS pmc_weight
			FROM emp.mining_settings_view;


/*
 * Mining computation - Total weights view
 * ----------------------------------------
 *
 * This view computes per-planet totals for actual mining weights.
 * 
 * Columns:
 *		planet_id			The planet's identifier
 *		pmc_total			The sum of all mining weights on the planet.
 */
DROP VIEW IF EXISTS emp.total_mining_weights_view CASCADE;
CREATE VIEW emp.total_mining_weights_view
	AS SELECT planet_id , SUM( pmc_weight ) AS pmc_total
			FROM emp.scaled_mining_weights_view
			GROUP BY planet_id;


/*
 * Compute the extracted quantity
 * -------------------------------
 *
 * Compute the quantity of resources that will be extracted from a resource
 * provider at the next game update. This function is used by the mining
 * update, obviously, but also by the various functions which control mining
 * settings and by ownership changes.
 *
 * Parameters:
 *		_input		Data about the resource provider to update
 *
 * Returns:
 * 		?			The quantity that will be extracted from the provider
 */
DROP FUNCTION IF EXISTS emp.mining_compute_extraction( emp.planet_mining_type );
CREATE FUNCTION emp.mining_compute_extraction( _input emp.planet_mining_type )
		RETURNS DOUBLE PRECISION
		LANGUAGE PLPGSQL
		STRICT IMMUTABLE
		SECURITY INVOKER
	AS $resprov_compute_extraction$

DECLARE
	_extraction	DOUBLE PRECISION;
	_allocation	DOUBLE PRECISION;
	_production	DOUBLE PRECISION;
	_quantity	DOUBLE PRECISION;

BEGIN
	IF _input.empire IS NULL THEN
		RETURN 0;
	END IF;

	_extraction := verse.get_extraction_factor(
		_input.quantity / _input.quantity_max ,
		_input.difficulty );
	_allocation := _input.weight / _input.total_weight;
	_production := verse.adjust_production(
			verse.get_raw_production( _input.planet , 'MINE' ) ,
			_input.happiness )
		* sys.get_constant( 'game.resources.extraction' )
		/ 1440.0; -- FIXME: hardcoded!

	_quantity := _allocation * _production * _extraction;
	IF _quantity > _input.quantity THEN
		_quantity := _input.quantity;
	END IF;

	RETURN _quantity;
END; 
$resprov_compute_extraction$;

REVOKE EXECUTE
	ON FUNCTION emp.mining_compute_extraction( emp.planet_mining_type )
	FROM PUBLIC;


/*
 * Get resource extraction input for a single planet
 * --------------------------------------------------
 *
 * This function locks and retrieves all data required to update a single
 * planet's resource extraction quantities. It is used when a planet's
 * owner changes (including planet assignment) or when a planet's specific
 * mining settings are modified.
 * 
 * Parameters:
 *		_planet		The planet's identifier
 *
 * Returns:
 *		the set of emp.planet_mining_type records for the planet  
 */
DROP FUNCTION IF EXISTS verse.mining_get_input( _planet INT );
CREATE FUNCTION verse.mining_get_input( _planet INT )
	RETURNS SETOF emp.planet_mining_type
	LANGUAGE SQL
	STRICT VOLATILE
	SECURITY INVOKER
AS $mining_get_input$

		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 verse.planets _planet
			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 emp.planets _emp_planet
								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 _emp_planet.planet_id = $1

							FOR SHARE OF _emp_planet , _emp , _emset , _happ 
					) AS _owner
				USING ( planet_id , resource_name_id )
	
		WHERE _planet.name_id = $1
	
		FOR UPDATE OF _resprov , _pres
		FOR SHARE OF _planet ;

$mining_get_input$;

REVOKE EXECUTE
	ON FUNCTION verse.mining_get_input( _planet INT )
	FROM PUBLIC;


/*
 * Get resource extraction input for a whole empire
 * -------------------------------------------------
 *
 * This function retrieves all mining information for a whole empire. It is
 * used to recompute extracted quantities when global settings are updated.
 * 
 * Parameters:
 *		_empire		The empire's identifier
 *
 * Returns:
 *		the set of emp.planet_mining_type records for the empire  
 */
DROP FUNCTION IF EXISTS emp.mining_get_input( _empire INT );
CREATE FUNCTION emp.mining_get_input( _empire INT )
	RETURNS SETOF emp.planet_mining_type
	LANGUAGE SQL
	STRICT VOLATILE
	SECURITY INVOKER
AS $mining_get_input$

		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 ,
			_happ.current / _planet.population AS happiness ,
			pmc_weight AS weight ,
			pmc_total AS total_weight

		FROM emp.planets _emp_planet
			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.planets _planet
				ON _planet.name_id = _emp_planet.planet_id
			INNER JOIN verse.resource_providers _resprov
				USING ( planet_id , resource_name_id )
			INNER JOIN verse.planet_resources _pres
				USING ( planet_id , resource_name_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 _emp_planet.empire_id = $1
	
		FOR UPDATE OF _resprov , _pres
		FOR SHARE OF _emp_planet , _emp , _emset , _happ , _planet ;

$mining_get_input$;

REVOKE EXECUTE
	ON FUNCTION emp.mining_get_input( _empire INT )
	FROM PUBLIC;