518 lines
15 KiB
PL/PgSQL
518 lines
15 KiB
PL/PgSQL
-- LegacyWorlds Beta 6
|
|
-- PostgreSQL database scripts
|
|
--
|
|
-- Empire research functions and views
|
|
--
|
|
-- Copyright(C) 2004-2012, DeepClone Development
|
|
-- --------------------------------------------------------
|
|
|
|
|
|
|
|
/*
|
|
* Implements a technology
|
|
* ------------------------
|
|
*
|
|
* This stored procedure is called when an empire attempts to implement a
|
|
* technology. It will check the empire's resources and the technology itself,
|
|
* then mark it as implemented if necessary. It will also add new research
|
|
* entries if necessary.
|
|
*
|
|
* Parameters:
|
|
* _empire The empire's identifier
|
|
* _technology The string identifier for the technology to implement
|
|
*/
|
|
DROP FUNCTION IF EXISTS emp.technology_implement( INT , TEXT );
|
|
CREATE FUNCTION emp.technology_implement( _empire INT , _technology TEXT )
|
|
RETURNS BOOLEAN
|
|
LANGUAGE PLPGSQL
|
|
STRICT VOLATILE
|
|
SECURITY DEFINER
|
|
AS $technology_implement$
|
|
|
|
DECLARE
|
|
_impl_data RECORD;
|
|
|
|
BEGIN
|
|
|
|
-- Access and lock the records
|
|
SELECT INTO _impl_data
|
|
technology_name_id , technology_price
|
|
FROM emp.empires _emp
|
|
INNER JOIN emp.technologies _tech
|
|
ON _tech.empire_id = _emp.name_id
|
|
INNER JOIN defs.technologies _def
|
|
USING ( technology_name_id )
|
|
INNER JOIN defs.strings _name
|
|
ON _def.technology_name_id = _name.id
|
|
WHERE _emp.name_id = _empire
|
|
AND _name.name = _technology
|
|
AND _tech.emptech_state = 'PENDING'
|
|
AND _emp.cash >= _def.technology_price
|
|
FOR UPDATE OF _emp , _tech
|
|
FOR SHARE OF _def;
|
|
IF NOT FOUND THEN
|
|
RETURN FALSE;
|
|
END IF;
|
|
|
|
-- Implement the technology
|
|
UPDATE emp.empires
|
|
SET cash = cash - _impl_data.technology_price
|
|
WHERE name_id = _empire;
|
|
UPDATE emp.technologies
|
|
SET emptech_state = 'KNOWN'
|
|
WHERE empire_id = _empire
|
|
AND technology_name_id = _impl_data.technology_name_id;
|
|
|
|
-- Insert new research
|
|
INSERT INTO emp.technologies ( empire_id , technology_name_id )
|
|
SELECT _empire , _valid.technology_name_id
|
|
FROM ( SELECT _tech.technology_name_id ,
|
|
( COUNT(*) = COUNT(_emptech.emptech_state) ) AS emptech_has_dependencies
|
|
FROM defs.technologies _tech
|
|
INNER JOIN defs.technology_dependencies _deps
|
|
USING ( technology_name_id )
|
|
LEFT OUTER JOIN emp.technologies _emptech
|
|
ON _emptech.technology_name_id = _deps.technology_name_id_depends
|
|
AND _emptech.emptech_state = 'KNOWN'
|
|
AND _emptech.empire_id = _empire
|
|
GROUP BY _tech.technology_name_id ) _valid
|
|
LEFT OUTER JOIN emp.technologies _emptech
|
|
ON _emptech.empire_id = _empire
|
|
AND _emptech.technology_name_id = _valid.technology_name_id
|
|
WHERE _emptech.empire_id IS NULL AND _valid.emptech_has_dependencies;
|
|
|
|
RETURN TRUE;
|
|
END;
|
|
$technology_implement$;
|
|
|
|
REVOKE EXECUTE
|
|
ON FUNCTION emp.technology_implement( INT , TEXT )
|
|
FROM PUBLIC;
|
|
|
|
GRANT EXECUTE
|
|
ON FUNCTION emp.technology_implement( INT , TEXT )
|
|
TO :dbuser;
|
|
|
|
|
|
/*
|
|
* Compute a technology identifier
|
|
* --------------------------------
|
|
*
|
|
* This function returns the identifier of a technology as seen from the
|
|
* player's side. The identifier is either the string identifier for the
|
|
* technology's name, or a MD5 hash including both the empire's identifier
|
|
* and the string identifier for "unknown" technologies.
|
|
*
|
|
* Parameters:
|
|
* _empire The empire's identifier
|
|
* _technology The technology's string identifier
|
|
* _visible TRUE if the technology is supposed to be visible, FALSE
|
|
* otherwise
|
|
*
|
|
* Returns:
|
|
* ? The technology's client-side identifier
|
|
*/
|
|
DROP FUNCTION IF EXISTS emp.technology_make_identifier( INT , TEXT , BOOLEAN );
|
|
CREATE FUNCTION emp.technology_make_identifier(
|
|
_empire INT , _technology TEXT , _visible BOOLEAN )
|
|
RETURNS TEXT
|
|
LANGUAGE SQL
|
|
STRICT IMMUTABLE
|
|
SECURITY DEFINER
|
|
AS $technology_make_identifier$
|
|
|
|
SELECT ( CASE
|
|
WHEN $3 THEN
|
|
$2
|
|
ELSE
|
|
md5( $1::TEXT || ' (making hash less obvious) ' || $2 )
|
|
END );
|
|
|
|
$technology_make_identifier$;
|
|
|
|
REVOKE EXECUTE
|
|
ON FUNCTION emp.technology_make_identifier( INT , TEXT , BOOLEAN )
|
|
FROM PUBLIC;
|
|
|
|
GRANT EXECUTE
|
|
ON FUNCTION emp.technology_make_identifier( INT , TEXT , BOOLEAN )
|
|
TO :dbuser;
|
|
|
|
|
|
/*
|
|
* Initialise a research priorities update
|
|
* ----------------------------------------
|
|
*
|
|
* This stored procedure prepares a temporary table which is used to update
|
|
* an empire's research priorities.
|
|
*
|
|
* Parameters:
|
|
* _empire The empire's identifier
|
|
*
|
|
* Returns:
|
|
* ? TRUE if the empire exists, is not on vacation mode and has
|
|
* in-progress research, FALSE otherwise.
|
|
*/
|
|
DROP FUNCTION IF EXISTS emp.resprio_update_start( INT );
|
|
CREATE FUNCTION emp.resprio_update_start( _empire INT )
|
|
RETURNS BOOLEAN
|
|
LANGUAGE PLPGSQL
|
|
STRICT VOLATILE
|
|
SECURITY DEFINER
|
|
AS $resprio_update_start$
|
|
BEGIN
|
|
|
|
-- Create temporary table
|
|
CREATE TEMPORARY TABLE rprio_update(
|
|
_empire_id INT ,
|
|
_technology_name_id INT ,
|
|
_emptech_id TEXT ,
|
|
_emptech_priority INT
|
|
) ON COMMIT DROP;
|
|
|
|
-- Lock records and fill table
|
|
INSERT INTO rprio_update (
|
|
_empire_id , _technology_name_id , _emptech_id , _emptech_priority
|
|
) SELECT _emp.name_id , _tech.technology_name_id ,
|
|
emp.technology_make_identifier( empire_id , _str.name , emptech_visible ) ,
|
|
_etech.emptech_priority
|
|
FROM emp.empires _emp
|
|
INNER JOIN emp.technologies _etech
|
|
ON _etech.empire_id = _emp.name_id
|
|
AND _etech.emptech_state = 'RESEARCH'
|
|
INNER JOIN defs.technologies _tech
|
|
USING ( technology_name_id )
|
|
INNER JOIN emp.technology_visibility_view _vis
|
|
USING ( empire_id , technology_name_id )
|
|
INNER JOIN defs.strings _str
|
|
ON _str.id = _tech.technology_name_id
|
|
INNER JOIN naming.empire_names _ename
|
|
ON _ename.id = _emp.name_id
|
|
LEFT OUTER JOIN users.vacations _vac
|
|
ON _vac.account_id = _ename.owner_id
|
|
AND _vac.status = 'PROCESSED'
|
|
WHERE _emp.name_id = _empire AND _vac.account_id IS NULL
|
|
FOR UPDATE OF _emp , _etech
|
|
FOR SHARE OF _tech , _str , _ename;
|
|
|
|
RETURN FOUND;
|
|
END;
|
|
$resprio_update_start$;
|
|
|
|
REVOKE EXECUTE
|
|
ON FUNCTION emp.resprio_update_start( INT )
|
|
FROM PUBLIC;
|
|
|
|
GRANT EXECUTE
|
|
ON FUNCTION emp.resprio_update_start( INT )
|
|
TO :dbuser;
|
|
|
|
|
|
/*
|
|
* Set the priority of some research
|
|
* ----------------------------------
|
|
*
|
|
* This stored procedure updates the priority of some in-progress empire
|
|
* research. It will only function correctly if emp.resprio_update_start() was
|
|
* already executed.
|
|
*
|
|
* Parameters:
|
|
* _technology The client-side identifier of the technology, as
|
|
* returned by emp.technology_make_identifier()
|
|
* _priority The priority to assign to the technology
|
|
*
|
|
* Returns:
|
|
* ? TRUE if the technology was found, FALSE if it wasn't.
|
|
*/
|
|
DROP FUNCTION IF EXISTS emp.resprio_update_set( TEXT , INT );
|
|
CREATE FUNCTION emp.resprio_update_set( _technology TEXT , _priority INT )
|
|
RETURNS BOOLEAN
|
|
LANGUAGE PLPGSQL
|
|
STRICT VOLATILE
|
|
SECURITY DEFINER
|
|
AS $resprio_update_set$
|
|
BEGIN
|
|
|
|
UPDATE rprio_update
|
|
SET _emptech_priority = _priority
|
|
WHERE _emptech_id = _technology;
|
|
RETURN FOUND;
|
|
|
|
END;
|
|
$resprio_update_set$;
|
|
|
|
REVOKE EXECUTE
|
|
ON FUNCTION emp.resprio_update_set( TEXT , INT )
|
|
FROM PUBLIC;
|
|
|
|
GRANT EXECUTE
|
|
ON FUNCTION emp.resprio_update_set( TEXT , INT )
|
|
TO :dbuser;
|
|
|
|
|
|
/*
|
|
* Apply an update to research priorities
|
|
* ---------------------------------------
|
|
*
|
|
* This stored procedure applies changes listed in the temporary research
|
|
* priority table by updating the actual table with the new values.
|
|
*
|
|
* Returns:
|
|
* ? TRUE if the update was valid, FALSE if one of the updated
|
|
* values was incorrect
|
|
*/
|
|
DROP FUNCTION IF EXISTS emp.resprio_update_apply( );
|
|
CREATE FUNCTION emp.resprio_update_apply( )
|
|
RETURNS BOOLEAN
|
|
LANGUAGE PLPGSQL
|
|
STRICT VOLATILE
|
|
SECURITY DEFINER
|
|
AS $resprio_update_apply$
|
|
BEGIN
|
|
|
|
UPDATE emp.technologies
|
|
SET emptech_priority = _emptech_priority
|
|
FROM rprio_update
|
|
WHERE _empire_id = empire_id
|
|
AND _technology_name_id = technology_name_id;
|
|
RETURN TRUE;
|
|
|
|
EXCEPTION
|
|
|
|
WHEN check_violation THEN
|
|
RETURN FALSE;
|
|
|
|
END;
|
|
$resprio_update_apply$;
|
|
|
|
REVOKE EXECUTE
|
|
ON FUNCTION emp.resprio_update_apply( )
|
|
FROM PUBLIC;
|
|
|
|
GRANT EXECUTE
|
|
ON FUNCTION emp.resprio_update_apply( )
|
|
TO :dbuser;
|
|
|
|
|
|
|
|
/*
|
|
* Compute an empire's total research points
|
|
* ------------------------------------------
|
|
*
|
|
* Obtain an empire's total research points by adding the happiness-adjusted
|
|
* value for each planet, then applying the global modifier for vacation mode
|
|
* if necessary.
|
|
*
|
|
* FIXME: time factor is hard-coded
|
|
*
|
|
* Parameters:
|
|
* _empire The empire's identifier
|
|
* _on_vacation TRUE if the player is on vacation, FALSE otherwise
|
|
*
|
|
* Returns:
|
|
* ? The amount of research points.
|
|
*/
|
|
DROP FUNCTION IF EXISTS emp.research_get_points( INT , BOOLEAN );
|
|
CREATE FUNCTION emp.research_get_points( _empire INT , _on_vacation BOOLEAN )
|
|
RETURNS DOUBLE PRECISION
|
|
LANGUAGE SQL
|
|
STRICT STABLE
|
|
SECURITY INVOKER
|
|
AS $research_get_points$
|
|
|
|
SELECT SUM( verse.adjust_production(
|
|
_planet.population * sys.get_constant( 'game.research.basePoints' ) ,
|
|
_happiness.current / _planet.population
|
|
) ) * ( CASE
|
|
WHEN $2 THEN
|
|
sys.get_constant( 'game.research.vacation' )
|
|
ELSE
|
|
1.0
|
|
END )::DOUBLE PRECISION
|
|
FROM emp.planets _emp_planet
|
|
INNER JOIN verse.planets _planet
|
|
ON _emp_planet.planet_id = _planet.name_id
|
|
INNER JOIN verse.planet_happiness _happiness
|
|
USING ( planet_id )
|
|
WHERE _emp_planet.empire_id = $1;
|
|
|
|
$research_get_points$;
|
|
|
|
REVOKE EXECUTE
|
|
ON FUNCTION emp.research_get_points( INT , BOOLEAN )
|
|
FROM PUBLIC;
|
|
|
|
|
|
|
|
|
|
|
|
/*
|
|
* Technology visibility view
|
|
* ---------------------------
|
|
*
|
|
* This view can be used to determine whether entries from empires' research
|
|
* and technologies table are fully visible or only displayed as "unknown
|
|
* technologies".
|
|
*
|
|
* Columns:
|
|
* empire_id The empire's identifier
|
|
* technology_name_id The technology's identifier
|
|
* emptech_visible TRUE if the technology's details are visible,
|
|
* FALSE if they should be hidden
|
|
*/
|
|
DROP VIEW IF EXISTS emp.technology_visibility_view CASCADE;
|
|
CREATE VIEW emp.technology_visibility_view
|
|
AS SELECT empire_id , technology_name_id ,
|
|
( emptech_state <> 'RESEARCH'
|
|
OR emptech_points >= sys.get_constant( 'game.research.visibility.points' )
|
|
OR emptech_points / technology_points::DOUBLE PRECISION >= sys.get_constant( 'game.research.visibility.ratio' )
|
|
) AS emptech_visible
|
|
FROM emp.technologies
|
|
INNER JOIN defs.technologies
|
|
USING ( technology_name_id );
|
|
|
|
|
|
|
|
/*
|
|
* Research weights
|
|
* -----------------
|
|
*
|
|
* This view computes weights based on priorities for each in-progress
|
|
* research.
|
|
*
|
|
* Columns:
|
|
* empire_id The empire's identifier
|
|
* technology_name_id The technology's identifier
|
|
* emptech_weight The weight
|
|
*/
|
|
DROP VIEW IF EXISTS emp.research_weights_view CASCADE;
|
|
CREATE VIEW emp.research_weights_view
|
|
AS SELECT empire_id , technology_name_id ,
|
|
POW( sys.get_constant( 'game.research.weightBase' ) ,
|
|
emptech_priority ) AS emptech_weight
|
|
FROM emp.technologies
|
|
WHERE emptech_state = 'RESEARCH';
|
|
|
|
/*
|
|
* Total research weights
|
|
* -----------------------
|
|
*
|
|
* This view computes total research weights for each empire with in-progress
|
|
* research.
|
|
*
|
|
* Columns:
|
|
* empire_id The empire's identifier
|
|
* emptech_total_weight The total research weight
|
|
*/
|
|
DROP VIEW IF EXISTS emp.research_total_weights_view CASCADE;
|
|
CREATE VIEW emp.research_total_weights_view
|
|
AS SELECT empire_id , SUM( emptech_weight ) AS emptech_total_weight
|
|
FROM emp.research_weights_view
|
|
GROUP BY empire_id;
|
|
|
|
|
|
/*
|
|
* Empire research and technologies
|
|
* ---------------------------------
|
|
*
|
|
* This view lists empires' research and technologies, along with their
|
|
* current state.
|
|
*
|
|
* Columns:
|
|
* empire_id The empire's identifier
|
|
* emptech_id An identifier for the technology, which is either
|
|
* the string identifier of the technology's name
|
|
* or a MD5 hash if the technology is not
|
|
* supposed to be visible
|
|
* emptech_state The state of the technology, straight from the
|
|
* empire technology table
|
|
* emptech_visible Whether the technology is supposed to be visible
|
|
* or not
|
|
* technology_category The string identifier of the technology's category
|
|
* technology_name The string identifier of the technology's name,
|
|
* or NULL if the technology is not supposed to
|
|
* be visible
|
|
* technology_description The string identifier of the technology's name,
|
|
* or NULL if the technology is not supposed
|
|
* to be visible
|
|
* emptech_points The amount of points accumulated while researching
|
|
* the technology, or NULL if the technology is
|
|
* not supposed to be visible
|
|
* emptech_priority The current research priority, or NULL if the
|
|
* technology is no longer being researched
|
|
* emptech_ratio The percentage of points accumulated while
|
|
* researching the technology, or NULL if the
|
|
* technology is no longer being researched
|
|
* technology_price The monetary price of the technology, or NULL if
|
|
* the technology is not supposed to be visible
|
|
* technology_dependencies The technology's dependencies from the
|
|
* dependencies view
|
|
* technology_buildings The buildings which are unlocked when the
|
|
* technology is implemented
|
|
*/
|
|
DROP VIEW IF EXISTS emp.technologies_view CASCADE;
|
|
CREATE VIEW emp.technologies_view
|
|
AS SELECT empire_id ,
|
|
emp.technology_make_identifier( empire_id , _name_str.name , emptech_visible ) AS emptech_id ,
|
|
emptech_state ,
|
|
emptech_visible ,
|
|
_cat_str.name AS technology_category ,
|
|
( CASE
|
|
WHEN emptech_visible THEN
|
|
_name_str.name
|
|
ELSE
|
|
NULL::TEXT
|
|
END ) AS technology_name ,
|
|
( CASE
|
|
WHEN emptech_visible THEN
|
|
_descr_str.name
|
|
ELSE
|
|
NULL::TEXT
|
|
END ) AS technology_description ,
|
|
( CASE
|
|
WHEN emptech_state <> 'RESEARCH' then
|
|
technology_points
|
|
WHEN emptech_visible THEN
|
|
FLOOR( emptech_points )::BIGINT
|
|
ELSE
|
|
NULL::BIGINT
|
|
END ) AS emptech_points ,
|
|
emptech_priority ,
|
|
( CASE
|
|
WHEN emptech_state = 'RESEARCH' THEN
|
|
FLOOR( 100.0 * emptech_points / technology_points::DOUBLE PRECISION )::INT
|
|
ELSE
|
|
NULL::INT
|
|
END ) AS emptech_ratio ,
|
|
( CASE
|
|
WHEN emptech_visible THEN
|
|
technology_price
|
|
ELSE
|
|
NULL::INT
|
|
END ) AS technology_price ,
|
|
technology_dependencies ,
|
|
( CASE
|
|
WHEN emptech_visible THEN
|
|
technology_buildings
|
|
ELSE
|
|
''
|
|
END ) AS technology_buildings
|
|
FROM emp.technologies
|
|
INNER JOIN emp.technology_visibility_view
|
|
USING ( technology_name_id , empire_id )
|
|
INNER JOIN defs.technologies _tech
|
|
USING ( technology_name_id )
|
|
INNER JOIN defs.technology_dependencies_view
|
|
USING ( technology_name_id )
|
|
INNER JOIN defs.technology_buildings_view
|
|
USING ( technology_name_id )
|
|
INNER JOIN defs.strings _name_str
|
|
ON _name_str.id = _tech.technology_name_id
|
|
INNER JOIN defs.strings _cat_str
|
|
ON _cat_str.id = _tech.technology_category_id
|
|
INNER JOIN defs.strings _descr_str
|
|
ON _descr_str.id = _tech.technology_description_id;
|
|
|
|
GRANT SELECT
|
|
ON emp.technologies_view
|
|
TO :dbuser;
|