lwb6/legacyworlds-server-data/db-structure/parts/040-functions/147-empire-mining.sql

-- LegacyWorlds Beta 6
-- PostgreSQL database scripts
--
-- Functions that control and compute empire mining
--
-- Copyright(C) 2004-2010, DeepClone Development
-- --------------------------------------------------------


/*
 * Prepare for an update on empire mining settings
 * 
 * This function creates a temporary table mimicking the structure of
 * emp.mining_settings, and stores default settings into it.
 * 
 * Parameters:
 *		_empire_id		The empire's identifier
 *
 * Returns:
 *		?				True if the empire exists, false otherwise
 */
DROP FUNCTION IF EXISTS emp.mset_update_start( INT );
CREATE FUNCTION emp.mset_update_start( _empire_id INT )
		RETURNS BOOLEAN
		STRICT VOLATILE
		SECURITY DEFINER
	AS $mset_update_start$
BEGIN

	CREATE TEMPORARY TABLE mset_update(
		empire_id			INT ,
		resource_name		TEXT ,
		empmset_weight		INT
	) ON COMMIT DROP;

	INSERT INTO mset_update
		SELECT _mset.empire_id , _str.name , 2
			FROM emp.empires _empire
				INNER JOIN emp.mining_settings _mset
					ON _empire.name_id = _mset.empire_id
				INNER JOIN defs.strings _str
					ON _str.id = _mset.resource_name_id
			WHERE _empire.name_id = _empire_id
			FOR SHARE OF _empire
			FOR UPDATE OF _mset;

	RETURN FOUND;

END;
$mset_update_start$ LANGUAGE PLPGSQL;

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


/*
 * Prepare for an update on planet-specific mining settings
 * 
 * This function creates a temporary table mimicking the structure of
 * emp.planet_mining_settings, and stores default settings into it, using the
 * planet's list of resource provider as the source.
 * 
 * Parameters:
 *		_empire_id		The empire's identifier
 *		_planet_id		The planet's identifier
 *
 * Returns:
 *		?				True if the empire exists and owns the planet and if
 *							the planet is using planet-specific settings,
 *							false otherwise
 */
DROP FUNCTION IF EXISTS emp.mset_update_start( INT , INT );
CREATE FUNCTION emp.mset_update_start( _empire_id INT , _planet_id INT )
		RETURNS BOOLEAN
		STRICT VOLATILE
		SECURITY DEFINER
	AS $mset_update_start$
BEGIN

	CREATE TEMPORARY TABLE mset_update(
		empire_id			INT ,
		planet_id			INT ,
		resource_name		TEXT ,
		empmset_weight		INT
	) ON COMMIT DROP;
	
	PERFORM 1
		FROM emp.empires _empire
			INNER JOIN emp.planets _emp_planet
				ON _empire.name_id = _emp_planet.empire_id
			INNER JOIN verse.planets _planet
				ON _planet.name_id = _emp_planet.planet_id
			INNER JOIN verse.resource_providers _resprov
				ON _resprov.planet_id = _planet.name_id
			INNER JOIN emp.planet_mining_settings _mset
				ON _mset.planet_id = _planet.name_id
					AND _mset.empire_id = _empire.name_id
					AND _mset.resource_name_id = _resprov.resource_name_id
		WHERE _empire.name_id = _empire_id
				AND _planet.name_id = _planet_id
		FOR SHARE OF _empire, _emp_planet , _planet , _resprov
		FOR UPDATE OF _mset;
	IF NOT FOUND THEN
		RETURN FALSE;
	END IF;
	
	PERFORM 1
		FROM emp.planet_mining_settings
		WHERE empire_id = _empire_id AND planet_id = _planet_id
		FOR UPDATE;

	INSERT INTO mset_update
		SELECT _empire_id , _planet_id , _str.name , 2
			FROM verse.resource_providers
				INNER JOIN defs.strings _str
					ON _str.id = resource_name_id
			WHERE planet_id = _planet_id;

	RETURN TRUE;

END;
$mset_update_start$ LANGUAGE PLPGSQL;

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



/*
 * Update the weight of some resource
 * 
 * This function updates the weight of a resource in the temporary table. It
 * must be called after emp.mset_update_start() has initialised the table.
 *
 * Parameters:
 *		_resource			The resource's text identifier
 *		_weight				The setting's new value
 *
 * Returns:
 *		?					True if the resource exists, false otherwise.
 */
DROP FUNCTION IF EXISTS emp.mset_update_set( TEXT , INT );
CREATE FUNCTION emp.mset_update_set( _resource TEXT , _weight INT )
		RETURNS BOOLEAN
		STRICT VOLATILE
		SECURITY DEFINER
	AS $mset_update_set$
BEGIN

	UPDATE mset_update
		SET empmset_weight = _weight
		WHERE resource_name = _resource;
	RETURN FOUND;

END;
$mset_update_set$ LANGUAGE PLPGSQL;


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



/*
 * Apply a pending update to empire or planet mining settings
 * 
 * This function is called once mining settings (whether empire-wise or
 * planet-specific) have been uploaded. It will apply all changes to the
 * actual mining settings table.
 * 
 * Returns:
 *		?			True if the update was applied, false if one of the
 *						weights was invalid.
 */
DROP FUNCTION IF EXISTS emp.mset_update_apply( );
CREATE FUNCTION emp.mset_update_apply( )
		RETURNS BOOLEAN
		STRICT VOLATILE
		SECURITY DEFINER
	AS $mset_update_apply$

DECLARE
	_empire	INT;
	_planet	INT;

BEGIN
	BEGIN

		-- Get empire and planet identifier (will cause exception if this is
		-- not a planet settings update).
		SELECT INTO _empire , _planet
				empire_id , planet_id
			FROM mset_update
			LIMIT 1;

		-- Delete then re-insert all settings for the planet
		DELETE FROM emp.planet_mining_settings
			WHERE empire_id = _empire AND planet_id = _planet;
		INSERT INTO emp.planet_mining_settings (
					empire_id , planet_id , resource_name_id ,
					emppmset_weight
			) SELECT empire_id , planet_id ,
					_str.id , empmset_weight
				FROM mset_update
					INNER JOIN defs.strings _str
						ON _str.name = resource_name;

		-- Update the planet's extraction
		UPDATE verse.planet_resources
			SET pres_income = emp.mining_compute_extraction( _update_row )
			FROM verse.mining_get_input( _planet ) _update_row
			WHERE planet_id = _update_row.planet
				AND resource_name_id = _update_row.resource;

	EXCEPTION

		WHEN undefined_column THEN
		
			-- Get empire identifier
			SELECT INTO _empire empire_id
				FROM mset_update LIMIT 1;

			-- Update the empire's settings
			UPDATE emp.mining_settings _settings
				SET empmset_weight = _update.empmset_weight
				FROM mset_update _update
					INNER JOIN defs.strings _str
						ON _str.name = _update.resource_name
				WHERE _update.empire_id = _settings.empire_id
					AND _str.id = _settings.resource_name_id;

			-- Update extracted quantities for the whole empire
			UPDATE verse.planet_resources
				SET pres_income = emp.mining_compute_extraction( _update_row )
				FROM emp.mining_get_input( _empire ) _update_row
				WHERE planet_id = _update_row.planet
					AND resource_name_id = _update_row.resource;

	END;
	RETURN TRUE;

EXCEPTION

	-- One of the weights was invalid
	WHEN check_violation THEN
		RETURN FALSE;

END;
$mset_update_apply$ LANGUAGE PLPGSQL;


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


/*
 * Toggle the source of a planet's mining settings
 * ------------------------------------------------
 * 
 * This function causes a planet to be switched between empire-wide and
 * planet-specific mining settings.
 * 
 * Parameters:
 *		_empire		The empire's identifier
 *		_planet		The planet's identifier
 *
 * Returns:
 *		?			TRUE if the operation succeeded, FALSE if the planet
 *						didn't exist or wasn't owned by the specified empire.
 */
DROP FUNCTION IF EXISTS emp.mset_toggle_source( INT , INT );
CREATE FUNCTION emp.mset_toggle_source( _empire INT , _planet INT )
		RETURNS BOOLEAN
		LANGUAGE PLPGSQL
		STRICT VOLATILE
		SECURITY DEFINER
	AS $mset_toggle_source$
BEGIN
	PERFORM 1
		FROM emp.planets _ep
			INNER JOIN verse.resource_providers _rp
				USING ( planet_id )
			LEFT OUTER JOIN (
				SELECT * FROM emp.planet_mining_settings _pms
					WHERE planet_id = _planet
						AND empire_id = _empire
					FOR UPDATE
				) _pms USING ( planet_id , empire_id , resource_name_id )
		WHERE _ep.empire_id = _empire
			AND _ep.planet_id = _planet
		FOR UPDATE OF _ep;
	IF NOT FOUND THEN
		RETURN FALSE;
	END IF;

	PERFORM 1 FROM emp.planet_mining_settings _pms
		WHERE planet_id = _planet AND empire_id = _empire;
	IF FOUND THEN

		-- Remove planet-specific settings, then update extracted quantities
		DELETE FROM emp.planet_mining_settings
			WHERE planet_id = _planet AND empire_id = _empire;
		UPDATE verse.planet_resources
			SET pres_income = emp.mining_compute_extraction( _update_row )
			FROM verse.mining_get_input( _planet ) _update_row
			WHERE planet_id = _update_row.planet
				AND resource_name_id = _update_row.resource;

	ELSE

		-- Create planet-specific settings using empire-wide values as the
		-- defaults. Because of that, no extraction update is necessary.
		INSERT INTO emp.planet_mining_settings(
				empire_id , planet_id , resource_name_id , emppmset_weight
			) SELECT empire_id  , planet_id , resource_name_id , empmset_weight
				FROM verse.resource_providers _rp
					INNER JOIN emp.mining_settings
						USING ( resource_name_id )
				WHERE planet_id = _planet AND empire_id = _empire;

	END IF;
	
	RETURN TRUE;
END;
$mset_toggle_source$;

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