Emmanuel BENOîT
3e109b13bc
* Added user mapping on the "remote" logging database for the administrative user. This allows calls to sys.write_sql_log() to succeed when they are executed by code with administrative privileges. * Added test suites for both the link to the database and the function itself. * Replaced RAISE NOTICE with actual logging in the universe generator
387 lines
12 KiB
PL/PgSQL
387 lines
12 KiB
PL/PgSQL
-- LegacyWorlds Beta 6
|
|
-- PostgreSQL database scripts
|
|
--
|
|
-- Functions for the resource provider generator
|
|
--
|
|
-- Copyright(C) 2004-2012, DeepClone Development
|
|
-- --------------------------------------------------------
|
|
|
|
|
|
|
|
/*
|
|
* Resource provider generator data
|
|
*
|
|
* This data type is used to store statistics about the existing resource
|
|
* providers. A single item of the type represents both statistics and
|
|
* parameters for a given resource type.
|
|
*/
|
|
DROP TYPE IF EXISTS verse.resprov_generator_type CASCADE;
|
|
CREATE TYPE verse.resprov_generator_type AS (
|
|
/* Type of natural resource */
|
|
resource_name_id INT ,
|
|
|
|
/* Planets in the universe */
|
|
planets DOUBLE PRECISION ,
|
|
|
|
/* Providers of this type in the universe */
|
|
providers DOUBLE PRECISION ,
|
|
|
|
/* Presence probability (from the resource's definition) */
|
|
presence DOUBLE PRECISION ,
|
|
|
|
/* Total maximal quantity of this type of resource in the whole
|
|
* universe.
|
|
*/
|
|
quantity DOUBLE PRECISION ,
|
|
|
|
/* Average quantity (from the resource's definition) */
|
|
quantity_avg DOUBLE PRECISION ,
|
|
|
|
/* Maximal deviation from the average quantity (from the resource's
|
|
* definition)
|
|
*/
|
|
quantity_dev DOUBLE PRECISION ,
|
|
|
|
/* Total extraction difficulty for this type of resource in the whole
|
|
* universe.
|
|
*/
|
|
difficulty DOUBLE PRECISION ,
|
|
|
|
/* Average difficulty (from the resource's definition) */
|
|
difficulty_avg DOUBLE PRECISION ,
|
|
|
|
/* Maximal deviation from the average difficulty (from the resource's
|
|
* definition)
|
|
*/
|
|
difficulty_dev DOUBLE PRECISION ,
|
|
|
|
/* Total recovery rate for this type of resource in the whole
|
|
* universe.
|
|
*/
|
|
recovery DOUBLE PRECISION ,
|
|
|
|
/* Average recovery rate (from the resource's definition) */
|
|
recovery_avg DOUBLE PRECISION ,
|
|
|
|
/* Maximal deviation from the average recovery rate (from the
|
|
*/
|
|
recovery_dev DOUBLE PRECISION
|
|
);
|
|
|
|
|
|
|
|
/*
|
|
* Collect resource provider statistics
|
|
*
|
|
* This procedure collects statistics about resource providers into a
|
|
* temporary table named resource_statistics, using the resprov_generator_type
|
|
* as the table's structure. The table will be dropped on commit.
|
|
*
|
|
* This function is necessary because the statistics must be collected before
|
|
* new planets are generated.
|
|
*/
|
|
DROP FUNCTION IF EXISTS verse.collect_resprov_statistics( );
|
|
CREATE FUNCTION verse.collect_resprov_statistics( )
|
|
RETURNS VOID
|
|
STRICT VOLATILE
|
|
SECURITY INVOKER
|
|
AS $collect_resprov_statistics$
|
|
|
|
BEGIN
|
|
CREATE TEMP TABLE rp_stats
|
|
OF verse.resprov_generator_type
|
|
ON COMMIT DROP;
|
|
|
|
INSERT INTO rp_stats
|
|
SELECT resource_name_id ,
|
|
_pcount.planets AS planets ,
|
|
( CASE
|
|
WHEN _rp_stats.providers IS NULL THEN 0
|
|
ELSE _rp_stats.providers
|
|
END ) AS providers ,
|
|
natres_p_presence AS presence ,
|
|
( CASE
|
|
WHEN _rp_stats.tot_quantity IS NULL THEN 0
|
|
ELSE _rp_stats.tot_quantity
|
|
END ) AS quantity ,
|
|
natres_quantity_avg AS quantity_avg ,
|
|
natres_quantity_dev AS quantity_dev ,
|
|
( CASE
|
|
WHEN _rp_stats.tot_difficulty IS NULL THEN 0
|
|
ELSE _rp_stats.tot_difficulty
|
|
END ) AS difficulty ,
|
|
natres_difficulty_avg AS difficulty_avg ,
|
|
natres_difficulty_dev AS difficulty_dev ,
|
|
( CASE
|
|
WHEN _rp_stats.tot_recovery IS NULL THEN 0
|
|
ELSE _rp_stats.tot_recovery
|
|
END ) AS recovery ,
|
|
natres_recovery_avg AS recovery_avg ,
|
|
natres_recovery_dev AS recovery_dev
|
|
FROM defs.natural_resources
|
|
LEFT OUTER JOIN (
|
|
SELECT resource_name_id ,
|
|
COUNT(*) AS providers ,
|
|
SUM( resprov_quantity_max ) AS tot_quantity ,
|
|
SUM( resprov_difficulty ) AS tot_difficulty ,
|
|
SUM( resprov_recovery ) AS tot_recovery
|
|
FROM verse.resource_providers
|
|
GROUP BY resource_name_id
|
|
) AS _rp_stats USING ( resource_name_id )
|
|
CROSS JOIN (
|
|
SELECT COUNT(*) AS planets
|
|
FROM verse.planets
|
|
) AS _pcount;
|
|
END;
|
|
$collect_resprov_statistics$ LANGUAGE PLPGSQL;
|
|
|
|
REVOKE EXECUTE
|
|
ON FUNCTION verse.collect_resprov_statistics( )
|
|
FROM PUBLIC;
|
|
|
|
|
|
|
|
/* Compute a random delta for one of the resource provider parameters
|
|
*
|
|
* This function computes the total change on one of the resource provider
|
|
* parameters. The resulting value can then be split amongst resource
|
|
* providers as they are created.
|
|
*
|
|
* Parameters:
|
|
* _existing Amount of existing resource providers
|
|
* _new Amount of resource providers being created
|
|
* _total Current total value for the parameter
|
|
* _p_average Average parameter value (from the definition)
|
|
* _p_deviation Parameter value deviation (from the definition)
|
|
*
|
|
* Returns:
|
|
* ? The total value to distribute amongst new resource
|
|
* providers
|
|
*/
|
|
DROP FUNCTION IF EXISTS verse.compute_rpp_delta( DOUBLE PRECISION , DOUBLE PRECISION ,
|
|
DOUBLE PRECISION , DOUBLE PRECISION , DOUBLE PRECISION );
|
|
CREATE FUNCTION verse.compute_rpp_delta(
|
|
_existing DOUBLE PRECISION ,
|
|
_new DOUBLE PRECISION ,
|
|
_total DOUBLE PRECISION ,
|
|
_p_average DOUBLE PRECISION ,
|
|
_p_deviation DOUBLE PRECISION )
|
|
RETURNS DOUBLE PRECISION
|
|
STRICT VOLATILE
|
|
SECURITY INVOKER
|
|
AS $compute_rpp_delta$
|
|
|
|
DECLARE
|
|
_result DOUBLE PRECISION;
|
|
|
|
BEGIN
|
|
_result := verse.random_deviation( _p_average , _p_deviation )
|
|
* ( _existing + _new ) - _total;
|
|
|
|
IF _result < ( _p_average - _p_deviation ) * _new THEN
|
|
_result := ( _p_average - _p_deviation ) * _new;
|
|
ELSIF _result > ( _p_average + _p_deviation ) * _new THEN
|
|
_result := ( _p_average + _p_deviation ) * _new;
|
|
END IF;
|
|
|
|
RETURN _result;
|
|
END;
|
|
$compute_rpp_delta$ LANGUAGE PLPGSQL;
|
|
|
|
REVOKE EXECUTE
|
|
ON FUNCTION verse.compute_rpp_delta( DOUBLE PRECISION , DOUBLE PRECISION ,
|
|
DOUBLE PRECISION , DOUBLE PRECISION , DOUBLE PRECISION )
|
|
FROM PUBLIC;
|
|
|
|
|
|
|
|
/*
|
|
* Create a single resource provider
|
|
*
|
|
* This function creates a single resource provider on some planet. It will
|
|
* return the updated values for the amount of providers left to handle and
|
|
* the totals.
|
|
*
|
|
* Parameters:
|
|
* _planet The identifier of the planet to create a provider on
|
|
* _data The statistics and parameters for the type of resource
|
|
* _providers_left The amount of resource providers that still need to be
|
|
* generated (including the current provider)
|
|
* _tot_quantity The total value left to distribute for the providers'
|
|
* maximal quantity
|
|
* _tot_difficulty The total value left to distribute for the providers'
|
|
* extraction difficulty
|
|
* _tot_recovery The total value left to distribute for the providers'
|
|
* recovery rate
|
|
*
|
|
* Returns:
|
|
* _providers_left The updated value for the amount of providers left
|
|
* _tot_quantity The updated value for the total maximal quantity to
|
|
* distribute
|
|
* _tot_difficulty The updated value for the total extraction difficulty
|
|
* to distribute
|
|
* _tot_recovery The updated value for the total recovery rate to
|
|
* distribute
|
|
*/
|
|
DROP FUNCTION IF EXISTS verse.create_resource_provider(
|
|
INT , verse.resprov_generator_type , INT , DOUBLE PRECISION ,
|
|
DOUBLE PRECISION , DOUBLE PRECISION );
|
|
CREATE FUNCTION verse.create_resource_provider(
|
|
_planet INT ,
|
|
_data verse.resprov_generator_type ,
|
|
INOUT _providers_left INT ,
|
|
INOUT _tot_quantity DOUBLE PRECISION ,
|
|
INOUT _tot_difficulty DOUBLE PRECISION ,
|
|
INOUT _tot_recovery DOUBLE PRECISION )
|
|
STRICT VOLATILE
|
|
SECURITY INVOKER
|
|
AS $create_resource_provider$
|
|
|
|
DECLARE
|
|
_quantity DOUBLE PRECISION;
|
|
_difficulty DOUBLE PRECISION;
|
|
_recovery DOUBLE PRECISION;
|
|
|
|
BEGIN
|
|
_quantity := verse.get_random_part( _tot_quantity , _providers_left ,
|
|
_data.quantity_avg , _data.quantity_dev );
|
|
_difficulty := verse.get_random_part( _tot_difficulty , _providers_left ,
|
|
_data.difficulty_avg , _data.difficulty_dev );
|
|
_recovery := verse.get_random_part( _tot_recovery , _providers_left ,
|
|
_data.recovery_avg , _data.recovery_dev );
|
|
|
|
PERFORM sys.write_sql_log( 'UniverseGenerator' , 'TRACE'::log_level ,
|
|
'Resource #' || _data.resource_name_id || ', planet #' || _planet
|
|
|| ': quantity ' || _quantity || ' , difficulty '
|
|
|| _difficulty || ' , recovery ' || _recovery );
|
|
|
|
INSERT INTO verse.resource_providers (
|
|
planet_id , resource_name_id , resprov_quantity_max ,
|
|
resprov_quantity , resprov_difficulty , resprov_recovery
|
|
) VALUES (
|
|
_planet , _data.resource_name_id , _quantity ,
|
|
_quantity , _difficulty , _recovery
|
|
);
|
|
|
|
_tot_quantity := _tot_quantity - _quantity;
|
|
_tot_difficulty := _tot_difficulty - _difficulty;
|
|
_tot_recovery := _tot_recovery - _recovery;
|
|
_providers_left := _providers_left - 1;
|
|
END;
|
|
$create_resource_provider$ LANGUAGE PLPGSQL;
|
|
|
|
REVOKE EXECUTE
|
|
ON FUNCTION verse.create_resource_provider( INT ,
|
|
verse.resprov_generator_type , INT , DOUBLE PRECISION ,
|
|
DOUBLE PRECISION , DOUBLE PRECISION )
|
|
FROM PUBLIC;
|
|
|
|
|
|
|
|
/*
|
|
* Create resource providers for a given type of resource
|
|
*
|
|
* This function will create resource providers for some specified type of
|
|
* resource in an area of the universe. It tries to balance the generated
|
|
* values according to the resource's definition.
|
|
*
|
|
* Parameters:
|
|
* _area The area to generate resource providers in
|
|
* _data The identifier, definition and statistics for the type of
|
|
* resource
|
|
*/
|
|
DROP FUNCTION IF EXISTS verse.create_resource_providers(
|
|
verse.generator_area_type , verse.resprov_generator_type );
|
|
CREATE FUNCTION verse.create_resource_providers(
|
|
_area verse.generator_area_type ,
|
|
_data verse.resprov_generator_type )
|
|
RETURNS VOID
|
|
STRICT VOLATILE
|
|
SECURITY INVOKER
|
|
AS $create_resource_providers$
|
|
|
|
DECLARE
|
|
_ncount INT;
|
|
_create INT;
|
|
_tot_quantity DOUBLE PRECISION;
|
|
_tot_difficulty DOUBLE PRECISION;
|
|
_tot_recovery DOUBLE PRECISION;
|
|
_planet INT;
|
|
|
|
BEGIN
|
|
_ncount := ( _area.x1 - _area.x0 + 1 ) * ( _area.y1 - _area.y0 + 1 ) * 5;
|
|
|
|
-- Determine the amount of providers to create
|
|
_create := FLOOR( ( _data.planets + _ncount ) * _data.presence - _data.providers )::INT;
|
|
IF _create <= 0 THEN
|
|
RETURN;
|
|
ELSIF _create > _ncount THEN
|
|
_create := _ncount;
|
|
END IF;
|
|
|
|
-- Compute the total delta for quantity, difficulty and recovery rate
|
|
_tot_quantity := verse.compute_rpp_delta( _data.providers , _create ,
|
|
_data.quantity , _data.quantity_avg , _data.quantity_dev );
|
|
_tot_difficulty := verse.compute_rpp_delta( _data.providers , _create ,
|
|
_data.difficulty , _data.difficulty_avg , _data.difficulty_dev );
|
|
_tot_recovery := verse.compute_rpp_delta( _data.providers , _create ,
|
|
_data.recovery , _data.recovery_avg , _data.recovery_dev );
|
|
|
|
PERFORM sys.write_sql_log( 'UniverseGenerator' , 'TRACE'::log_level ,
|
|
'Resource #' || _data.resource_name_id || ': ' || _create
|
|
|| ' new provider(s), quantity: ' || _tot_quantity || ' (avg. '
|
|
|| ( _tot_quantity / _create ) || ') , difficulty: '
|
|
|| _tot_difficulty || ' (avg. ' || ( _tot_difficulty / _create )
|
|
|| '), recovery: ' || _tot_recovery || ' (avg. '
|
|
|| _tot_recovery / _create || ')' );
|
|
|
|
-- Select random planets to add resource providers to
|
|
FOR _planet IN SELECT * FROM verse.list_random_planets_in( _area , _create )
|
|
LOOP
|
|
SELECT INTO _create , _tot_quantity , _tot_difficulty , _tot_recovery
|
|
* FROM verse.create_resource_provider( _planet , _data , _create ,
|
|
_tot_quantity , _tot_difficulty , _tot_recovery );
|
|
END LOOP;
|
|
|
|
END;
|
|
$create_resource_providers$ LANGUAGE PLPGSQL;
|
|
|
|
REVOKE EXECUTE
|
|
ON FUNCTION verse.create_resource_providers( verse.generator_area_type ,
|
|
verse.resprov_generator_type )
|
|
FROM PUBLIC;
|
|
|
|
|
|
|
|
/*
|
|
* Create resource providers in some area of the universe
|
|
*
|
|
* This function creates resource providers in the specified area using the
|
|
* statistics collected before the area was created to balance the resource
|
|
* providers' parameters.
|
|
*
|
|
* Parameters:
|
|
* _area The area to generate resource providers in
|
|
*/
|
|
DROP FUNCTION IF EXISTS verse.create_resource_providers( verse.generator_area_type );
|
|
CREATE FUNCTION verse.create_resource_providers( _area verse.generator_area_type )
|
|
RETURNS VOID
|
|
STRICT VOLATILE
|
|
SECURITY INVOKER
|
|
AS $create_resource_providers$
|
|
|
|
DECLARE
|
|
_rp_data verse.resprov_generator_type;
|
|
|
|
BEGIN
|
|
FOR _rp_data IN SELECT * FROM rp_stats
|
|
LOOP
|
|
PERFORM verse.create_resource_providers( _area , _rp_data );
|
|
END LOOP;
|
|
END;
|
|
$create_resource_providers$ LANGUAGE PLPGSQL;
|
|
|
|
|
|
REVOKE EXECUTE
|
|
ON FUNCTION verse.create_resource_providers( verse.generator_area_type )
|
|
FROM PUBLIC;
|