This repository has been archived on 2025-01-04. You can view files and clone it, but cannot push or open issues or pull requests.
lwb6/legacyworlds-server-data/db-structure/parts/040-functions/030-tech.sql
Emmanuel BENOîT 071257786c Renamed technology tables and views
* Removed the _v2 suffix from some tables and views.
2012-04-09 15:01:04 +02:00

670 lines
16 KiB
PL/PgSQL

-- LegacyWorlds Beta 6
-- PostgreSQL database scripts
--
-- Functions and views for technologies and buildables
--
-- Copyright(C) 2004-2010, DeepClone Development
-- --------------------------------------------------------
/*
* Return type for technology management functions
* ------------------------------------------------
*
* This enumerated type is used for the return values of all technology
* management functions. This includes defs.uoc_technology() of course,
* but also defs.techdep_add() and defs.techdep_remove().
*/
DROP TYPE IF EXISTS defs.technology_update_result CASCADE;
CREATE TYPE defs.technology_update_result
AS ENUM(
/* The technology definition or dependency was created */
'CREATED' ,
/* The technology definition was updated */
'UPDATED' ,
/* The dependency was deleted */
'DELETED' ,
/* The specified dependency does not exist */
'MISSING' ,
/* One (or more) of the numeric parameters is invalid */
'BAD_VALUE' ,
/* The name, description, discovery or category string identifiers
* were not valid string identifiers.
*/
'BAD_STRINGS' ,
/* The specified description and/or discovery string was in use by
* another technology.
*/
'DUP_STRING' ,
/* The dependency would cause a cycle */
'CYCLE' ,
/* The dependency would be redundant */
'REDUNDANT'
);
/*
* Update or create a technology definition
* -----------------------------------------
*
* This stored procedure can be used to update existing technology definitions
* or create new records. It will not affect technology dependencies: no
* depedencies will be added when creating a new technology, and existing
* dependencies will be conserved when updating.
*
* If a technology already exists, check for empires researching that
* technology, and scale their current progress accordingly.
*
* Parameters:
* _name Text identifier of the name string
* _category Text identifier of the category string
* _discovery Text identifier of the discovery string
* _description Text identifier of the description string
* _price Monetary cost to implement the technology
* _points Amount of points required to research teh technology
*
* Returns:
* ? One of the following return codes: CREATED, UPDATED,
* BAD_VALUE, BAD_STRINGS, DUP_STRING
*/
DROP FUNCTION IF EXISTS defs.uoc_technology(
TEXT , TEXT , TEXT , TEXT , BIGINT , BIGINT ) CASCADE;
CREATE FUNCTION defs.uoc_technology(
_name TEXT ,
_category TEXT ,
_discovery TEXT ,
_description TEXT ,
_price BIGINT ,
_points BIGINT )
RETURNS defs.technology_update_result
LANGUAGE PLPGSQL
STRICT VOLATILE
SECURITY DEFINER
AS $uoc_technology$
DECLARE
_name_id INT;
_disc_id INT;
_desc_id INT;
_cat_id INT;
_old_points BIGINT;
_multi DOUBLE PRECISION;
BEGIN
-- Get all string identifiers
SELECT INTO _name_id id FROM defs.strings WHERE name = _name;
IF NOT FOUND THEN
RETURN 'BAD_STRINGS';
END IF;
SELECT INTO _desc_id id FROM defs.strings WHERE name = _description;
IF NOT FOUND THEN
RETURN 'BAD_STRINGS';
END IF;
SELECT INTO _disc_id id FROM defs.strings WHERE name = _discovery;
IF NOT FOUND THEN
RETURN 'BAD_STRINGS';
END IF;
SELECT INTO _cat_id id FROM defs.strings WHERE name = _category;
IF NOT FOUND THEN
RETURN 'BAD_STRINGS';
END IF;
-- Try inserting the record
BEGIN
INSERT INTO defs.technologies (
technology_name_id , technology_category_id ,
technology_discovery_id , technology_description_id ,
technology_price , technology_points
) VALUES (
_name_id , _cat_id ,
_disc_id , _desc_id ,
_price , _points
);
RETURN 'CREATED';
EXCEPTION
WHEN unique_violation THEN
-- Continue, we can't determine which error this
-- was about at this point.
END;
-- Lock existing definition and empire research records
PERFORM 1
FROM defs.technologies _def
LEFT OUTER JOIN (
SELECT technology_name_id
FROM emp.technologies _tech
WHERE technology_name_id = _name_id
AND emptech_state = 'RESEARCH'
FOR UPDATE OF _tech
) _emps USING ( technology_name_id )
WHERE technology_name_id = _name_id
FOR UPDATE OF _def;
IF NOT FOUND THEN
RETURN 'DUP_STRING';
END IF;
-- Get old value for research points
SELECT INTO _old_points technology_points
FROM defs.technologies
WHERE technology_name_id = _name_id;
-- Update the record
BEGIN
UPDATE defs.technologies
SET technology_category_id = _cat_id ,
technology_discovery_id = _disc_id ,
technology_description_id = _desc_id ,
technology_price = _price ,
technology_points = _points
WHERE technology_name_id = _name_id;
EXCEPTION
WHEN unique_violation THEN
RETURN 'DUP_STRING';
END;
-- Update empire research if necessary
IF _old_points <> _points THEN
_multi := _points::DOUBLE PRECISION / _old_points::DOUBLE PRECISION;
UPDATE emp.technologies
SET emptech_points = emptech_points * _multi
WHERE technology_name_id = _name_id
AND emptech_points IS NOT NULL;
END IF;
RETURN 'UPDATED';
EXCEPTION
WHEN check_violation THEN
RETURN 'BAD_VALUE';
END;
$uoc_technology$;
REVOKE EXECUTE
ON FUNCTION defs.uoc_technology(
TEXT , TEXT , TEXT , TEXT , BIGINT , BIGINT )
FROM PUBLIC;
GRANT EXECUTE
ON FUNCTION defs.uoc_technology(
TEXT , TEXT , TEXT , TEXT , BIGINT , BIGINT )
TO :dbuser;
/*
* Add a technology dependency
* ----------------------------
*
* This stored procedure attempts to create a dependency between two
* technologies by looking them up by name then inserting the pair in the
* dependency table.
*
* Parameters:
* _dependent The name of the dependent technology
* _dependency The name of the dependency
*
* Returns:
* ? One of the following return codes: CREATED,
* BAD_STRINGS, CYCLE, REDUNDANT
*/
DROP FUNCTION IF EXISTS defs.techdep_add( TEXT , TEXT );
CREATE FUNCTION defs.techdep_add( _dependent TEXT , _dependency TEXT )
RETURNS defs.technology_update_result
LANGUAGE PLPGSQL
STRICT VOLATILE
SECURITY DEFINER
AS $techdep_add$
DECLARE
_tech1_id INT;
_tech2_id INT;
BEGIN
SELECT INTO _tech1_id , _tech2_id _str1.id , _str2.id
FROM defs.strings _str1
CROSS JOIN defs.strings _str2
WHERE _str1.name = _dependent
AND _str2.name = _dependency;
IF NOT FOUND THEN
RETURN 'BAD_STRINGS';
END IF;
INSERT INTO defs.technology_dependencies (
technology_name_id , technology_name_id_depends
) VALUES ( _tech1_id , _tech2_id );
RETURN 'CREATED';
EXCEPTION
WHEN foreign_key_violation THEN
RETURN 'BAD_STRINGS';
WHEN unique_violation THEN
RETURN 'REDUNDANT';
WHEN check_violation THEN
IF SQLERRM LIKE '%Cycle detected%' THEN
RETURN 'CYCLE';
END IF;
RETURN 'REDUNDANT';
END;
$techdep_add$;
REVOKE EXECUTE
ON FUNCTION defs.techdep_add( TEXT , TEXT )
FROM PUBLIC;
GRANT EXECUTE
ON FUNCTION defs.techdep_add( TEXT , TEXT )
TO :dbuser;
/*
* Remove a technology dependency
* -------------------------------
*
* This stored procedure removes a dependency from a technology to another.
*
* Parameters:
* _dependent The name of the dependent technology
* _dependency The name of the dependency
*
* Returns:
* ? One of the following return codes: DELETED, MISSING
*/
DROP FUNCTION IF EXISTS defs.techdep_remove( TEXT , TEXT );
CREATE FUNCTION defs.techdep_remove( _dependent TEXT , _dependency TEXT )
RETURNS defs.technology_update_result
LANGUAGE PLPGSQL
STRICT VOLATILE
SECURITY DEFINER
AS $techdep_remove$
DECLARE
_dep_id INT;
BEGIN
SELECT INTO _dep_id techdep_id
FROM defs.technology_dependencies _td
INNER JOIN defs.strings _str1
ON _str1.id = _td.technology_name_id
INNER JOIN defs.strings _str2
ON _str2.id = _td.technology_name_id_depends
WHERE _str1.name = _dependent
AND _str2.name = _dependency
FOR UPDATE OF _td;
IF NOT FOUND THEN
RETURN 'MISSING';
END IF;
DELETE FROM defs.technology_dependencies
WHERE techdep_id = _dep_id;
RETURN 'DELETED';
END;
$techdep_remove$;
REVOKE EXECUTE
ON FUNCTION defs.techdep_remove( TEXT , TEXT )
FROM PUBLIC;
GRANT EXECUTE
ON FUNCTION defs.techdep_remove( TEXT , TEXT )
TO :dbuser;
/*
* Remove all dependencies for a technology
* -----------------------------------------
*
* This stored procedure removes all dependencies and reverse dependencies for
* some technology.
*
* Parameters:
* _technology The name of the technology
*/
DROP FUNCTION IF EXISTS defs.techdep_clear( TEXT );
CREATE FUNCTION defs.techdep_clear( _technology TEXT )
RETURNS VOID
LANGUAGE SQL
STRICT VOLATILE
SECURITY DEFINER
AS $techdep_clear$
DELETE FROM defs.technology_dependencies
WHERE technology_name_id = (
SELECT id FROM defs.strings
WHERE name = $1
);
DELETE FROM defs.technology_dependencies
WHERE technology_name_id_depends = (
SELECT id FROM defs.strings
WHERE name = $1
);
$techdep_clear$;
REVOKE EXECUTE
ON FUNCTION defs.techdep_clear( TEXT )
FROM PUBLIC;
GRANT EXECUTE
ON FUNCTION defs.techdep_clear( TEXT )
TO :dbuser;
-- ********************************************************
-- OLD CODE BELOW
-- ********************************************************
--
-- "Basic" buildables view (buildables that do not depend on any technology)
--
CREATE VIEW tech.basic_buildables
AS SELECT * FROM tech.buildables
WHERE technology_name_id IS NULL;
--
-- Buildings view
--
CREATE VIEW tech.buildings_view
AS SELECT b.name_id , b.description_id , b.technology_name_id ,
b.cost , b.work , b.upkeep ,
bld.workers , bld.output_type , bld.output
FROM tech.buildables b
INNER JOIN tech.buildings bld
ON b.name_id = bld.buildable_id;
/*
* Buildings / technology view
* ----------------------------
*
* This view generates a parseable list of buildings unlocked by a technology
* for each technology.
*
* Columns:
* technology_name_id The technology's name
* technology_buildings A list of comma-separated building identifiers
*/
DROP VIEW IF EXISTS defs.technology_buildings_view CASCADE;
CREATE VIEW defs.technology_buildings_view
AS SELECT technology_name_id ,
array_to_string( array_agg( _name.name ) , ',' ) AS technology_buildings
FROM defs.technologies _tech
LEFT OUTER JOIN tech.buildings_view _building
USING ( technology_name_id )
LEFT OUTER JOIN defs.strings _name
ON _name.id = _building.name_id
GROUP BY technology_name_id;
--
-- Ships view
--
CREATE VIEW tech.ships_view
AS SELECT b.name_id , b.description_id , b.technology_name_id ,
b.cost , b.work , b.upkeep ,
s.flight_time , s.power
FROM tech.buildables b
INNER JOIN tech.ships s
ON b.name_id = s.buildable_id;
--
-- Creates or updates a buildable definition
--
-- Parameters:
-- bdn Buildable name
-- bdd Buildable description
-- bdc Cost
-- bdw Work
-- bdu Upkeep
-- _tech Technology dependency
--
-- Returns:
-- the buildable's identifier
--
CREATE OR REPLACE FUNCTION tech.uoc_buildable( bdn TEXT , bdd TEXT , bdc INT , bdw INT , bdu INT , _tech TEXT )
RETURNS INT
STRICT
VOLATILE
SECURITY INVOKER
AS $$
DECLARE
nid INT;
did INT;
_tech_id INT;
BEGIN
-- Get the various translations
SELECT INTO nid id FROM defs.strings WHERE name = bdn;
SELECT INTO did id FROM defs.strings WHERE name = bdd;
IF _tech <> '' THEN
SELECT INTO _tech_id technology_name_id
FROM defs.technologies
INNER JOIN defs.strings s
ON s.id = technology_name_id
WHERE s.name = _tech;
ELSE
_tech_id := NULL;
END IF;
-- Create or update the definition
BEGIN
INSERT INTO tech.buildables ( name_id , description_id , technology_name_id , cost , work , upkeep )
VALUES ( nid , did , _tech_id , bdc , bdw , bdu );
EXCEPTION
WHEN unique_violation THEN
UPDATE tech.buildables
SET description_id = did , technology_name_id = _tech_id ,
cost = bdc , work = bdw , upkeep = bdu
WHERE name_id = nid;
END;
RETURN nid;
END;
$$ LANGUAGE plpgsql;
--
-- Update or create a building definition (no tech dependency)
--
-- Parameters:
-- bdn Buildable name
-- bdd Buildable description
-- bdc Cost
-- bdw Work
-- bdu Upkeep
-- bdwk Workers
-- bdot Output type
-- bdo Output
--
CREATE OR REPLACE FUNCTION tech.uoc_building( bdn TEXT , bdd TEXT , bdc INT , bdw INT ,
bdu INT , bdwk INT , bdot building_output_type , bdo INT )
RETURNS VOID
STRICT
VOLATILE
SECURITY DEFINER
AS $$
DECLARE
bdid INT;
BEGIN
bdid := tech.uoc_buildable( bdn , bdd , bdc , bdw , bdu , '' );
PERFORM buildable_id FROM tech.ships WHERE buildable_id = bdid;
IF FOUND THEN
RAISE EXCEPTION 'Trying to transform a ship into a building';
END IF;
BEGIN
INSERT INTO tech.buildings (buildable_id, workers, output_type, output)
VALUES (bdid , bdwk , bdot , bdo);
EXCEPTION
WHEN unique_violation THEN
UPDATE tech.buildings SET workers = bdwk , output_type = bdot , output = bdo
WHERE buildable_id = bdid;
END;
END;
$$ LANGUAGE plpgsql;
GRANT EXECUTE ON FUNCTION tech.uoc_building( TEXT , TEXT , INT , INT , INT , INT , building_output_type , INT ) TO :dbuser;
--
-- Update or create a building definition (with tech dependency)
--
-- Parameters:
-- bdn Buildable name
-- bdd Buildable description
-- bdc Cost
-- bdw Work
-- bdu Upkeep
-- bdwk Workers
-- bdot Output type
-- bdo Output
-- _tech Technology dependency
--
CREATE OR REPLACE FUNCTION tech.uoc_building( bdn TEXT , bdd TEXT , bdc INT , bdw INT ,
bdu INT , bdwk INT , bdot building_output_type , bdo INT ,
_tech TEXT )
RETURNS VOID
STRICT
VOLATILE
SECURITY DEFINER
AS $$
DECLARE
bdid INT;
BEGIN
bdid := tech.uoc_buildable( bdn , bdd , bdc , bdw , bdu , _tech );
PERFORM buildable_id FROM tech.ships WHERE buildable_id = bdid;
IF FOUND THEN
RAISE EXCEPTION 'Trying to transform a ship into a building';
END IF;
BEGIN
INSERT INTO tech.buildings (buildable_id, workers, output_type, output)
VALUES (bdid , bdwk , bdot , bdo);
EXCEPTION
WHEN unique_violation THEN
UPDATE tech.buildings SET workers = bdwk , output_type = bdot , output = bdo
WHERE buildable_id = bdid;
END;
END;
$$ LANGUAGE plpgsql;
GRANT EXECUTE ON FUNCTION tech.uoc_building( TEXT , TEXT , INT , INT , INT , INT , building_output_type , INT , TEXT ) TO :dbuser;
--
-- Update or create a ship definition (no tech dependency)
--
-- Parameters:
-- sn Buildable name
-- sd Buildable description
-- sc Cost
-- sw Work
-- su Upkeep
-- sp Power
-- sft Orbital flight time
--
CREATE OR REPLACE FUNCTION tech.uoc_ship( sn TEXT , sd TEXT , sc INT , sw INT ,
su INT , sp INT , sft INT )
RETURNS VOID
STRICT
VOLATILE
SECURITY DEFINER
AS $$
DECLARE
bdid INT;
BEGIN
bdid := tech.uoc_buildable( sn , sd , sc , sw , su , '' );
PERFORM buildable_id FROM tech.buildings WHERE buildable_id = bdid;
IF FOUND THEN
RAISE EXCEPTION 'Trying to transform a building into a ship';
END IF;
BEGIN
INSERT INTO tech.ships (buildable_id, flight_time, power)
VALUES (bdid , sft , sp);
EXCEPTION
WHEN unique_violation THEN
UPDATE tech.ships SET flight_time = sft , power = sp
WHERE buildable_id = bdid;
END;
END;
$$ LANGUAGE plpgsql;
GRANT EXECUTE ON FUNCTION tech.uoc_ship( TEXT , TEXT , INT , INT , INT , INT , INT ) TO :dbuser;
--
-- Update or create a ship definition
--
-- Parameters:
-- sn Buildable name
-- sd Buildable description
-- sc Cost
-- sw Work
-- su Upkeep
-- sp Power
-- sft Orbital flight time
-- _tech Technology dependency
--
CREATE OR REPLACE FUNCTION tech.uoc_ship( sn TEXT , sd TEXT , sc INT , sw INT ,
su INT , sp INT , sft INT , _tech TEXT )
RETURNS VOID
STRICT
VOLATILE
SECURITY DEFINER
AS $$
DECLARE
bdid INT;
BEGIN
bdid := tech.uoc_buildable( sn , sd , sc , sw , su , _tech );
PERFORM buildable_id FROM tech.buildings WHERE buildable_id = bdid;
IF FOUND THEN
RAISE EXCEPTION 'Trying to transform a building into a ship';
END IF;
BEGIN
INSERT INTO tech.ships (buildable_id, flight_time, power)
VALUES (bdid , sft , sp);
EXCEPTION
WHEN unique_violation THEN
UPDATE tech.ships SET flight_time = sft , power = sp
WHERE buildable_id = bdid;
END;
END;
$$ LANGUAGE plpgsql;
GRANT EXECUTE ON FUNCTION tech.uoc_ship( TEXT , TEXT , INT , INT , INT , INT , INT , TEXT ) TO :dbuser;