lwb6/legacyworlds-server-data/db-structure/parts/040-functions/043-computation.sql

-- LegacyWorlds Beta 6
-- PostgreSQL database scripts
--
-- Various functions for in-game computations
--
-- Copyright(C) 2004-2010, DeepClone Development
-- --------------------------------------------------------


/*
 * Random value with deviation
 * 
 * Parameters:
 *		_mean		the mean value
 *		_deviation	the deviation
 *
 * Returns:
 *		?			a random value between _mean - _deviation and
 *						_mean + _deviation, with a higher probability
 *						of a value that is close to _mean
 */
DROP FUNCTION IF EXISTS verse.random_deviation( DOUBLE PRECISION , DOUBLE PRECISION );
CREATE FUNCTION verse.random_deviation( _mean DOUBLE PRECISION , _deviation DOUBLE PRECISION )
		RETURNS DOUBLE PRECISION
		STRICT VOLATILE
		SECURITY INVOKER
	AS $random_deviation$

DECLARE
	_result	DOUBLE PRECISION;

BEGIN
	_result := _deviation * RANDOM( ) ^ 2.5;
	IF RANDOM() < 0.5 THEN
		_result := -_result;
	END IF;
	RETURN _result + _mean;
END;
$random_deviation$ LANGUAGE PLPGSQL;


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



/*
 * Randomly distribute some part of a total value
 * 
 * This function can be used when a total value must be distributed between
 * various items. It will compute the minimal and maximal values that may be
 * attributed, enforcing the fact that the whole value needs to be consumed
 * in the end, and that values must conform to a specific range expressed as
 * a mean value and a deviation.
 * 
 * The total value is assumed to be valid with regards to the mean and
 * deviation. That is:
 * 
 *		_parts * ( _mean - _deviation ) <= _quantity
 *		_parts * ( _mean + _deviation ) >= _quantity
 *
 * Parameters:
 *		_quantity		the total quantity left to distribute
 *		_parts			the amount of items left
 *		_mean			the result's mean value
 *		_deviation		the result's deviation
 *
 * Returns:
 * 		?				the value to attribute to the nex item
 */
DROP FUNCTION IF EXISTS verse.get_random_part( DOUBLE PRECISION , INT , DOUBLE PRECISION , DOUBLE PRECISION );
CREATE FUNCTION verse.get_random_part(
			_quantity DOUBLE PRECISION ,
			_parts INT ,
			_mean DOUBLE PRECISION ,
			_deviation DOUBLE PRECISION )
		RETURNS DOUBLE PRECISION
		STRICT VOLATILE
		SECURITY INVOKER
	AS $get_random_part$

DECLARE
	_min	DOUBLE PRECISION;
	_max	DOUBLE PRECISION;
	_n_mean	DOUBLE PRECISION;

BEGIN
	IF _parts = 1 THEN
		RETURN _quantity;
	END IF;

	_min := _quantity - ( _mean + _deviation ) * ( _parts - 1 );
	IF _min < _mean - _deviation THEN
		_min := _mean - _deviation;
	END IF;

	_max := _quantity - ( _mean - _deviation ) * ( _parts - 1 );
	IF _max > _mean + _deviation THEN
		_max := _mean + _deviation;
	END IF;
	
	IF _min = _max THEN
		RETURN _min;
	END IF;
	
	_n_mean := ( _min + _max ) * 0.5;
	RETURN verse.random_deviation( _n_mean , _n_mean - _min );
END;
$get_random_part$ LANGUAGE PLPGSQL;

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




--
-- sigma( x ) = exp( x ) / ( 1 + exp( x ) )
--

CREATE OR REPLACE FUNCTION verse.sigma( x REAL )
	RETURNS REAL
	STRICT IMMUTABLE SECURITY INVOKER
AS $$
	SELECT ( CASE
		WHEN $1 < -100 THEN 0
		WHEN $1 > 100 THEN 1
		ELSE ( exp( $1 ) / ( 1 + exp( $1 ) ) )::REAL
	END );
$$ LANGUAGE SQL;



--
-- poly( x , a , b , c ) = ( a * x + b ) * x + c 
--

CREATE OR REPLACE FUNCTION verse.poly( x REAL , a REAL , b REAL , c REAL )
	RETURNS REAL
	STRICT IMMUTABLE SECURITY INVOKER
AS $$
	SELECT ( $2 * $1 + $3 ) * $1 + $4;
$$ LANGUAGE SQL;



--
-- Happiness curve, K1 constant
--

CREATE OR REPLACE FUNCTION verse.hcc_const_k1( xmax REAL , ymax REAL , xlimit REAL , ylimit REAL )
	RETURNS REAL
	STRICT IMMUTABLE SECURITY INVOKER
AS $$
	SELECT ( ( $4 - $2 ) / ( ( $3 - $1 ) ^ 2 ) )::REAL;
$$ LANGUAGE SQL;



--
-- Happiness curve, K2 constant
--

CREATE OR REPLACE FUNCTION verse.hcc_const_k2( ylimit REAL , yasymptote REAL )
	RETURNS REAL
	STRICT IMMUTABLE SECURITY INVOKER
AS $$
	SELECT ( 2 * ( $1 - $2 ) )::REAL;
$$ LANGUAGE SQL;



--
-- Happiness curve, K3 constant
--

CREATE OR REPLACE FUNCTION verse.hcc_const_k3( xmax REAL , ymax REAL , xlimit REAL , ylimit REAL , yasymptote REAL )
	RETURNS REAL
	STRICT IMMUTABLE SECURITY INVOKER
AS $$
	SELECT ( verse.hcc_const_k1( $1 , $2 , $3 , $4 ) * 4 * ( $3 - $1 ) / ( $5 - $4 ) ) ::REAL;
$$ LANGUAGE SQL;



--
-- Happiness curve, first part
--

CREATE OR REPLACE FUNCTION verse.hcc_part_1( x REAL , ymin REAL , ymax REAL , xmax REAL )
	RETURNS REAL
	STRICT IMMUTABLE SECURITY INVOKER
AS $$
DECLARE
	v	REAL;
BEGIN
	v := ( ymin - ymax ) / xmax;
	RETURN verse.poly( x , ( v / xmax )::REAL , ( -2 * v )::REAL , ymin );
END;
$$ LANGUAGE plpgsql;



--
-- Happiness curve, second part
--

CREATE OR REPLACE FUNCTION verse.hcc_part_2( x REAL , xmax REAL , ymax REAL , xlimit REAL , ylimit REAL )
	RETURNS REAL
	STRICT IMMUTABLE SECURITY INVOKER
AS $$
DECLARE
	k1	REAL;
BEGIN
	k1 := verse.hcc_const_k1( xmax , ymax , xlimit , ylimit );
	RETURN verse.poly( x , k1 , ( -2 * xmax * k1 )::REAL , ( ymax + k1 * xmax * xmax )::REAL );
END;
$$ LANGUAGE plpgsql;



--
-- Happiness curve, third part
--

CREATE OR REPLACE FUNCTION verse.hcc_part_3( x REAL , xmax REAL , ymax REAL , xlimit REAL , ylimit REAL , yasymptote REAL )
	RETURNS REAL
	STRICT IMMUTABLE SECURITY INVOKER
AS $$
DECLARE
	k2	REAL;
	k3	REAL;
BEGIN
	k2 := verse.hcc_const_k2( ylimit , yasymptote );
	k3 := verse.hcc_const_k3( xmax , ymax , xlimit , ylimit , yasymptote );
	RETURN yasymptote + k2 * ( 1 - verse.sigma( ( k3 * ( x - xlimit ) ) )::REAL );
END;
$$ LANGUAGE plpgsql;



--
-- Happiness curve
--

CREATE OR REPLACE FUNCTION verse.happiness_curve( x REAL , ymin REAL , xmax REAL , ymax REAL , xlimit REAL , ylimit REAL , yasymptote REAL )
	RETURNS REAL
	STRICT IMMUTABLE SECURITY INVOKER
AS $$
	SELECT (CASE
		WHEN $1 < $3 THEN
			verse.hcc_part_1( $1 , $2 , $4 , $3 )
		WHEN $1 < $5 THEN
			verse.hcc_part_2( $1 , $3 , $4 , $5 , $6 )
		ELSE
			verse.hcc_part_3( $1 , $3 , $4 , $5 , $6 , $7 )
	END)
$$ LANGUAGE SQL;



--
-- Happiness computation
--

CREATE OR REPLACE FUNCTION verse.compute_happiness( population REAL , workers REAL , defence REAL , empsize INT )
		RETURNS REAL
		STRICT STABLE SECURITY INVOKER
	AS $$
DECLARE
	whappiness	REAL;
	dhappiness	REAL;
	shappiness	REAL;
BEGIN
	-- Work-related happiness
	whappiness := verse.happiness_curve(
		( workers / population )::REAL ,
		sys.get_constant( 'game.happiness.noEmployment' ) , 1.0 , 1.0 , 
		sys.get_constant( 'game.happiness.employmentLimit' ) , 0.5 , 0
	);

	-- Defence-related happiness
	dhappiness := verse.happiness_curve(
		( sys.get_constant( 'game.happiness.popPerDefencePoint' ) * defence / population )::REAL ,
		sys.get_constant( 'game.happiness.noDefence' ) , 1.0 , 1.0 , 
		sys.get_constant( 'game.happiness.defenceLimit' ) , 0.5 , 0
	);
	
	-- Influence of empire size
	shappiness := verse.happiness_curve(
		( empsize::REAL / sys.get_constant( 'game.happiness.idealEmpireSize' ) )::REAL ,
		sys.get_constant( 'game.happiness.smallEmpire' ) , 1.0 , 1.0 , 
		sys.get_constant( 'game.happiness.eSizeLimit' ) , 0.5 , 0
	);
	
	RETURN ( shappiness * ( whappiness + dhappiness ) / 2.0 )::REAL;
END;
$$ LANGUAGE plpgsql;



/*
 * Production adjustment
 * ----------------------
 *
 * Adjust productions depending on a planet's happiness ratio. When the
 * happiness ratio is greater than the game.happiness.strike constant, the
 * production is not affected. However, under that threshold, the production
 * decreases proportionally, until it reaches 0.
 * 
 * Parameters:
 *		_prod		The production's valule
 *		_h_ratio	The happiness ratio
 *
 * Returns:
 *		?			The adjusted production value
 */
DROP FUNCTION IF EXISTS verse.adjust_production( REAL , REAL ) CASCADE;
CREATE OR REPLACE FUNCTION verse.adjust_production( _prod REAL , _h_ratio REAL )
	RETURNS REAL
	LANGUAGE SQL
	STRICT IMMUTABLE
	SECURITY INVOKER
AS $adjust_production$

	SELECT ( CASE
		WHEN $2 < sys.get_constant( 'game.happiness.strike' ) THEN
			( $1 * $2 / sys.get_constant( 'game.happiness.strike' ) )::REAL
		ELSE
			$1
	END );

$adjust_production$;

REVOKE EXECUTE
	ON FUNCTION verse.adjust_production( REAL , REAL )
	FROM PUBLIC;
GRANT EXECUTE
	ON FUNCTION verse.adjust_production( REAL , REAL )
	TO :dbuser;


--
-- Income computation
--

CREATE OR REPLACE FUNCTION verse.compute_income( population REAL , happiness REAL , cashprod REAL )
		RETURNS REAL
		STRICT STABLE
		SECURITY INVOKER
	AS $$
DECLARE
	base	REAL;
	badj	REAL;
	cprod	REAL;
BEGIN
	badj := ( 1 - verse.adjust_production( 1.0 , happiness ) ) * sys.get_constant( 'game.work.strikeEffect' );
	base := floor( population ) * sys.get_constant( 'game.work.population' ) * ( 1 - badj );
	cprod := verse.adjust_production( cashprod , happiness ) * sys.get_constant( 'game.work.factory' );
	RETURN cprod + base;
END;
$$ LANGUAGE plpgsql;