Emmanuel BENOîT
597429fadf
* Added session records to carry resource information over to the clients * Added SQL support code for the various views * Added interface and implementation of the resource information access component * Hooked resources information queries into both the empire and planet management component * Added resources display to planet and overview pages
563 lines
16 KiB
PL/PgSQL
563 lines
16 KiB
PL/PgSQL
-- LegacyWorlds Beta 6
|
|
-- PostgreSQL database scripts
|
|
--
|
|
-- Resource definitions management functions
|
|
--
|
|
-- Copyright(C) 2004-2011, DeepClone Development
|
|
-- --------------------------------------------------------
|
|
|
|
/*
|
|
* Return codes for resource creation or update functions.
|
|
*/
|
|
DROP TYPE IF EXISTS defs.resource_update_result CASCADE;
|
|
CREATE TYPE defs.resource_update_result
|
|
AS ENUM(
|
|
/* The resource definition was created */
|
|
'CREATED' ,
|
|
|
|
/* The resource definition was updated */
|
|
'UPDATED' ,
|
|
|
|
/* The resource definition already existed, and was either a basic
|
|
* resource definition while the update required a natural resource,
|
|
* or a natural resource definition when the update required a basic
|
|
* resource.
|
|
*/
|
|
'BAD_TYPE' ,
|
|
|
|
/* The name, description or category string identifiers were not valid
|
|
* string identifiers.
|
|
*/
|
|
'BAD_STRINGS' ,
|
|
|
|
/* One (or more) of the numeric parameters is invalid */
|
|
'BAD_VALUE' ,
|
|
|
|
/* The specified description was in use by another resource */
|
|
'DUP_DESCR'
|
|
);
|
|
|
|
|
|
/*
|
|
* Add a resource to all empire and planet records
|
|
*
|
|
* This function makes sure that all empire and planet records have a row for
|
|
* a newly-created resource.
|
|
*
|
|
* Parameters:
|
|
* _resource The identifier of the resource to add
|
|
*/
|
|
DROP FUNCTION IF EXISTS defs.add_resource_records( INT );
|
|
CREATE FUNCTION defs.add_resource_records( _resource INT )
|
|
RETURNS VOID
|
|
STRICT VOLATILE
|
|
SECURITY INVOKER
|
|
AS $$
|
|
|
|
INSERT INTO emp.resources ( empire_id , resource_name_id )
|
|
SELECT name_id , $1 FROM emp.empires;
|
|
|
|
INSERT INTO verse.planet_resources ( planet_id , resource_name_id )
|
|
SELECT name_id , $1 FROM verse.planets;
|
|
|
|
$$ LANGUAGE SQL;
|
|
|
|
REVOKE EXECUTE
|
|
ON FUNCTION defs.add_resource_records( INT )
|
|
FROM PUBLIC;
|
|
|
|
|
|
|
|
/*
|
|
* Create or update a basic resource
|
|
*
|
|
* /!\ INTERNAL FUNCTION /!\
|
|
*
|
|
* This function is called by the variants of defs.uoc_resource() to actually
|
|
* update or create the resource. It will make sure that all string
|
|
* identifiers exist, then try to insert the resource. If that fails because
|
|
* the resource already exists, make sure it's a basic resource then update
|
|
* it.
|
|
*
|
|
* Parameters:
|
|
* _name the identifier of the resource's name
|
|
* _description the identifier of the resource's description
|
|
* _category the identifier of the resource's category, or NULL if
|
|
* the resource does not belong to a category
|
|
* _weight the resource's ordering weight
|
|
*
|
|
* Returns:
|
|
* ? the result code for the operation
|
|
*/
|
|
DROP FUNCTION IF EXISTS defs.uoc_resource_internal( TEXT , TEXT , TEXT , INT );
|
|
CREATE FUNCTION defs.uoc_resource_internal(
|
|
_name TEXT ,
|
|
_description TEXT ,
|
|
_category TEXT ,
|
|
_weight INT )
|
|
RETURNS defs.resource_update_result
|
|
CALLED ON NULL INPUT
|
|
VOLATILE
|
|
SECURITY INVOKER
|
|
AS $$
|
|
DECLARE
|
|
_ret defs.resource_update_result;
|
|
_name_id INT;
|
|
_desc_id INT;
|
|
_cat_id INT;
|
|
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;
|
|
IF _category IS NULL THEN
|
|
_cat_id := NULL;
|
|
ELSE
|
|
SELECT INTO _cat_id id FROM defs.strings WHERE name = _category;
|
|
IF NOT FOUND THEN
|
|
RETURN 'BAD_STRINGS';
|
|
END IF;
|
|
END IF;
|
|
|
|
-- Try inserting the record
|
|
BEGIN
|
|
INSERT INTO defs.resources (
|
|
resource_name_id , resource_description_id ,
|
|
resource_category_id , resource_weight
|
|
) VALUES (
|
|
_name_id , _desc_id , _cat_id , _weight
|
|
);
|
|
|
|
PERFORM defs.add_resource_records( _name_id );
|
|
RETURN 'CREATED';
|
|
EXCEPTION
|
|
WHEN unique_violation THEN
|
|
IF SQLERRM LIKE '%_description_%' THEN
|
|
RETURN 'DUP_DESCR';
|
|
END IF;
|
|
END;
|
|
|
|
-- Insertion failed, make sure the resource is a basic resource
|
|
PERFORM *
|
|
FROM defs.resources basic_res
|
|
LEFT OUTER JOIN defs.natural_resources nat_res
|
|
USING ( resource_name_id )
|
|
WHERE basic_res.resource_name_id = _name_id
|
|
AND nat_res.resource_name_id IS NULL
|
|
FOR UPDATE OF basic_res;
|
|
IF NOT FOUND THEN
|
|
RETURN 'BAD_TYPE';
|
|
END IF;
|
|
|
|
-- Update the resource
|
|
BEGIN
|
|
UPDATE defs.resources
|
|
SET resource_description_id = _desc_id ,
|
|
resource_category_id = _cat_id ,
|
|
resource_weight = _weight
|
|
WHERE resource_name_id = _name_id;
|
|
RETURN 'UPDATED';
|
|
EXCEPTION
|
|
WHEN unique_violation THEN
|
|
RETURN 'DUP_DESCR';
|
|
END;
|
|
EXCEPTION
|
|
WHEN check_violation THEN
|
|
RETURN 'BAD_VALUE';
|
|
END;
|
|
$$ LANGUAGE PLPGSQL;
|
|
|
|
REVOKE EXECUTE
|
|
ON FUNCTION defs.uoc_resource_internal( TEXT , TEXT , TEXT , INT )
|
|
FROM PUBLIC;
|
|
|
|
|
|
|
|
/*
|
|
* Update or create a basic resource definition with no category
|
|
*
|
|
* Parameters:
|
|
* _name the identifier of the resource's name
|
|
* _description the identifier of the resource's description
|
|
* _weight the resource's ordering weight
|
|
*
|
|
* Returns:
|
|
* ? the result code for the operation
|
|
*/
|
|
DROP FUNCTION IF EXISTS defs.uoc_resource( TEXT , TEXT , INT );
|
|
CREATE FUNCTION defs.uoc_resource(
|
|
_name TEXT ,
|
|
_description TEXT ,
|
|
_weight INT )
|
|
RETURNS defs.resource_update_result
|
|
STRICT
|
|
VOLATILE
|
|
SECURITY DEFINER
|
|
AS $$
|
|
SELECT defs.uoc_resource_internal( $1 , $2 , NULL , $3 );
|
|
$$ LANGUAGE SQL;
|
|
|
|
REVOKE EXECUTE
|
|
ON FUNCTION defs.uoc_resource( TEXT , TEXT , INT )
|
|
FROM PUBLIC;
|
|
GRANT EXECUTE
|
|
ON FUNCTION defs.uoc_resource( TEXT , TEXT , INT )
|
|
TO :dbuser;
|
|
|
|
|
|
|
|
/*
|
|
* Update or create a basic resource definition with a category
|
|
*
|
|
* Parameters:
|
|
* _name the identifier of the resource's name
|
|
* _description the identifier of the resource's description
|
|
* _category the identifier of the resource's category
|
|
* _weight the resource's ordering weight
|
|
*
|
|
* Returns:
|
|
* ? the result code for the operation
|
|
*/
|
|
DROP FUNCTION IF EXISTS defs.uoc_resource( TEXT , TEXT , TEXT , INT );
|
|
CREATE FUNCTION defs.uoc_resource(
|
|
_name TEXT ,
|
|
_description TEXT ,
|
|
_category TEXT ,
|
|
_weight INT )
|
|
RETURNS defs.resource_update_result
|
|
STRICT
|
|
VOLATILE
|
|
SECURITY DEFINER
|
|
AS $$
|
|
SELECT defs.uoc_resource_internal( $1 , $2 , $3 , $4 );
|
|
$$ LANGUAGE SQL;
|
|
|
|
REVOKE EXECUTE
|
|
ON FUNCTION defs.uoc_resource( TEXT , TEXT , TEXT , INT )
|
|
FROM PUBLIC;
|
|
GRANT EXECUTE
|
|
ON FUNCTION defs.uoc_resource( TEXT , TEXT , TEXT , INT )
|
|
TO :dbuser;
|
|
|
|
|
|
|
|
/*
|
|
* Create or update a natural resource
|
|
*
|
|
* /!\ INTERNAL FUNCTION /!\
|
|
*
|
|
* This function is called by the variants of defs.uoc_natural_resource() to
|
|
* actually update or create the resource. It will make sure that all string
|
|
* identifiers exist, then try to insert the resource. If that fails because
|
|
* the resource already exists, make sure it's a natural resource then update
|
|
* it.
|
|
*
|
|
* Parameters:
|
|
* _name the identifier of the resource's name
|
|
* _description the identifier of the resource's description
|
|
* _category the identifier of the resource's category, or NULL if
|
|
* the resource does not belong to a category
|
|
* _weight the resource's ordering weight
|
|
* _presence the presence probability
|
|
* _quantity_avg the average quantity
|
|
* _quantity_dev the deviation from the average quantity
|
|
* _difficulty_avg the average extraction difficulty
|
|
* _difficulty_dev the deviation from the average extraction difficulty
|
|
* _recovery_avg the average recovery rate
|
|
* _recovery_dev the deviation from the average recovery rate
|
|
*
|
|
* Returns:
|
|
* ? the result code for the operation
|
|
*/
|
|
DROP FUNCTION IF EXISTS defs.uoc_natres_internal( TEXT , TEXT , TEXT , INT ,
|
|
DOUBLE PRECISION , DOUBLE PRECISION , DOUBLE PRECISION ,
|
|
DOUBLE PRECISION , DOUBLE PRECISION , DOUBLE PRECISION ,
|
|
DOUBLE PRECISION );
|
|
CREATE FUNCTION defs.uoc_natres_internal(
|
|
_name TEXT ,
|
|
_description TEXT ,
|
|
_category TEXT ,
|
|
_weight INT ,
|
|
_presence DOUBLE PRECISION ,
|
|
_quantity_avg DOUBLE PRECISION ,
|
|
_quantity_dev DOUBLE PRECISION ,
|
|
_difficulty_avg DOUBLE PRECISION ,
|
|
_difficulty_dev DOUBLE PRECISION ,
|
|
_recovery_avg DOUBLE PRECISION ,
|
|
_recovery_dev DOUBLE PRECISION )
|
|
RETURNS defs.resource_update_result
|
|
CALLED ON NULL INPUT
|
|
VOLATILE
|
|
SECURITY INVOKER
|
|
AS $$
|
|
DECLARE
|
|
_ret defs.resource_update_result;
|
|
_name_id INT;
|
|
_desc_id INT;
|
|
_cat_id INT;
|
|
_inserted BOOLEAN;
|
|
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;
|
|
IF _category IS NULL THEN
|
|
_cat_id := NULL;
|
|
ELSE
|
|
SELECT INTO _cat_id id FROM defs.strings WHERE name = _category;
|
|
IF NOT FOUND THEN
|
|
RETURN 'BAD_STRINGS';
|
|
END IF;
|
|
END IF;
|
|
|
|
-- Try inserting the basic record
|
|
BEGIN
|
|
INSERT INTO defs.resources (
|
|
resource_name_id , resource_description_id ,
|
|
resource_category_id , resource_weight
|
|
) VALUES (
|
|
_name_id , _desc_id , _cat_id , _weight
|
|
);
|
|
_inserted := TRUE;
|
|
EXCEPTION
|
|
WHEN unique_violation THEN
|
|
IF SQLERRM LIKE '%_description_%' THEN
|
|
RETURN 'DUP_DESCR';
|
|
END IF;
|
|
_inserted := FALSE;
|
|
END;
|
|
|
|
-- If insertion succeeded, insert the rest of the record
|
|
IF _inserted THEN
|
|
INSERT INTO defs.natural_resources(
|
|
resource_name_id , natres_p_presence ,
|
|
natres_quantity_avg , natres_quantity_dev ,
|
|
natres_difficulty_avg , natres_difficulty_dev ,
|
|
natres_recovery_avg , natres_recovery_dev
|
|
) VALUES (
|
|
_name_id , _presence ,
|
|
_quantity_avg , _quantity_dev ,
|
|
_difficulty_avg , _difficulty_dev ,
|
|
_recovery_avg , _recovery_dev
|
|
);
|
|
|
|
PERFORM defs.add_resource_records( _name_id );
|
|
RETURN 'CREATED';
|
|
END IF;
|
|
|
|
-- Insertion failed, make sure it is a natural resource
|
|
PERFORM *
|
|
FROM defs.resources basic_res
|
|
INNER JOIN defs.natural_resources nat_res
|
|
USING ( resource_name_id )
|
|
WHERE basic_res.resource_name_id = _name_id
|
|
FOR UPDATE;
|
|
IF NOT FOUND THEN
|
|
RETURN 'BAD_TYPE';
|
|
END IF;
|
|
|
|
-- Update the resource
|
|
BEGIN
|
|
UPDATE defs.resources
|
|
SET resource_description_id = _desc_id ,
|
|
resource_category_id = _cat_id ,
|
|
resource_weight = _weight
|
|
WHERE resource_name_id = _name_id;
|
|
UPDATE defs.natural_resources
|
|
SET natres_p_presence = _presence ,
|
|
natres_quantity_avg = _quantity_avg ,
|
|
natres_quantity_dev = _quantity_dev ,
|
|
natres_difficulty_avg = _difficulty_avg ,
|
|
natres_difficulty_dev = _difficulty_dev ,
|
|
natres_recovery_avg = _recovery_avg ,
|
|
natres_recovery_dev = _recovery_dev
|
|
WHERE resource_name_id = _name_id;
|
|
RETURN 'UPDATED';
|
|
EXCEPTION
|
|
WHEN unique_violation THEN
|
|
RETURN 'DUP_DESCR';
|
|
END;
|
|
EXCEPTION
|
|
WHEN check_violation THEN
|
|
RETURN 'BAD_VALUE';
|
|
END;
|
|
$$ LANGUAGE PLPGSQL;
|
|
|
|
REVOKE EXECUTE
|
|
ON FUNCTION defs.uoc_natres_internal( TEXT , TEXT , TEXT , INT ,
|
|
DOUBLE PRECISION , DOUBLE PRECISION , DOUBLE PRECISION ,
|
|
DOUBLE PRECISION , DOUBLE PRECISION , DOUBLE PRECISION ,
|
|
DOUBLE PRECISION )
|
|
FROM PUBLIC;
|
|
|
|
|
|
|
|
/*
|
|
* Create or update a natural resource with no category
|
|
*
|
|
* Parameters:
|
|
* _name the identifier of the resource's name
|
|
* _description the identifier of the resource's description
|
|
* _weight the resource's ordering weight
|
|
* _presence the presence probability
|
|
* _quantity_avg the average quantity
|
|
* _quantity_dev the deviation from the average quantity
|
|
* _difficulty_avg the average extraction difficulty
|
|
* _difficulty_dev the deviation from the average extraction difficulty
|
|
* _recovery_avg the average recovery rate
|
|
* _recovery_dev the deviation from the average recovery rate
|
|
*
|
|
* Returns:
|
|
* ? the result code for the operation
|
|
*/
|
|
CREATE OR REPLACE FUNCTION defs.uoc_natural_resource(
|
|
_name TEXT ,
|
|
_description TEXT ,
|
|
_weight INT ,
|
|
_presence DOUBLE PRECISION ,
|
|
_quantity_avg DOUBLE PRECISION ,
|
|
_quantity_dev DOUBLE PRECISION ,
|
|
_difficulty_avg DOUBLE PRECISION ,
|
|
_difficulty_dev DOUBLE PRECISION ,
|
|
_recovery_avg DOUBLE PRECISION ,
|
|
_recovery_dev DOUBLE PRECISION )
|
|
RETURNS defs.resource_update_result
|
|
STRICT VOLATILE
|
|
SECURITY DEFINER
|
|
AS $$
|
|
SELECT defs.uoc_natres_internal( $1 , $2 , NULL , $3 , $4 , $5 , $6 , $7 ,
|
|
$8 , $9 , $10 );
|
|
$$ LANGUAGE SQL;
|
|
|
|
REVOKE EXECUTE
|
|
ON FUNCTION defs.uoc_natural_resource( TEXT , TEXT , INT ,
|
|
DOUBLE PRECISION , DOUBLE PRECISION , DOUBLE PRECISION ,
|
|
DOUBLE PRECISION , DOUBLE PRECISION , DOUBLE PRECISION ,
|
|
DOUBLE PRECISION )
|
|
FROM PUBLIC;
|
|
GRANT EXECUTE
|
|
ON FUNCTION defs.uoc_natural_resource( TEXT , TEXT , INT ,
|
|
DOUBLE PRECISION , DOUBLE PRECISION , DOUBLE PRECISION ,
|
|
DOUBLE PRECISION , DOUBLE PRECISION , DOUBLE PRECISION ,
|
|
DOUBLE PRECISION )
|
|
TO :dbuser;
|
|
|
|
|
|
|
|
/*
|
|
* Create or update a natural resource with a category
|
|
*
|
|
* Parameters:
|
|
* _name the identifier of the resource's name
|
|
* _description the identifier of the resource's description
|
|
* _category the identifier of the resource's category
|
|
* _weight the resource's ordering weight
|
|
* _presence the presence probability
|
|
* _quantity_avg the average quantity
|
|
* _quantity_dev the deviation from the average quantity
|
|
* _difficulty_avg the average extraction difficulty
|
|
* _difficulty_dev the deviation from the average extraction difficulty
|
|
* _recovery_avg the average recovery rate
|
|
* _recovery_dev the deviation from the average recovery rate
|
|
*
|
|
* Returns:
|
|
* ? the result code for the operation
|
|
*/
|
|
CREATE OR REPLACE FUNCTION defs.uoc_natural_resource(
|
|
_name TEXT ,
|
|
_description TEXT ,
|
|
_category TEXT ,
|
|
_weight INT ,
|
|
_presence DOUBLE PRECISION ,
|
|
_quantity_avg DOUBLE PRECISION ,
|
|
_quantity_dev DOUBLE PRECISION ,
|
|
_difficulty_avg DOUBLE PRECISION ,
|
|
_difficulty_dev DOUBLE PRECISION ,
|
|
_recovery_avg DOUBLE PRECISION ,
|
|
_recovery_dev DOUBLE PRECISION )
|
|
RETURNS defs.resource_update_result
|
|
STRICT VOLATILE
|
|
SECURITY DEFINER
|
|
AS $$
|
|
SELECT defs.uoc_natres_internal( $1 , $2 , $3 , $4 , $5 , $6 , $7 , $8 ,
|
|
$9 , $10 , $11 );
|
|
$$ LANGUAGE SQL;
|
|
|
|
REVOKE EXECUTE
|
|
ON FUNCTION defs.uoc_natural_resource( TEXT , TEXT , TEXT , INT ,
|
|
DOUBLE PRECISION , DOUBLE PRECISION , DOUBLE PRECISION ,
|
|
DOUBLE PRECISION , DOUBLE PRECISION , DOUBLE PRECISION ,
|
|
DOUBLE PRECISION )
|
|
FROM PUBLIC;
|
|
GRANT EXECUTE
|
|
ON FUNCTION defs.uoc_natural_resource( TEXT , TEXT , TEXT , INT ,
|
|
DOUBLE PRECISION , DOUBLE PRECISION , DOUBLE PRECISION ,
|
|
DOUBLE PRECISION , DOUBLE PRECISION , DOUBLE PRECISION ,
|
|
DOUBLE PRECISION )
|
|
TO :dbuser;
|
|
|
|
|
|
|
|
/*
|
|
* View of resource category weights
|
|
*
|
|
* This view computes the average resource weight per category for all
|
|
* resource definitions.
|
|
*
|
|
* Fields:
|
|
* resource_category_id The category's identifier
|
|
* resource_category_weight The average weight of resource defintions
|
|
* in the category.
|
|
*/
|
|
DROP VIEW IF EXISTS defs.resource_category_weight_view CASCADE;
|
|
CREATE VIEW defs.resource_category_weight_view
|
|
AS SELECT resource_category_id ,
|
|
AVG( resource_weight ) AS resource_category_weight
|
|
FROM defs.resources
|
|
WHERE resource_category_id IS NOT NULL
|
|
GROUP BY resource_category_id;
|
|
|
|
|
|
|
|
/*
|
|
* Ordered resource definitions
|
|
*
|
|
* This view contains the name, category and description identifier for all
|
|
* resource definitions, ordered based on the category's average weight and
|
|
* the resource's own weight.
|
|
*
|
|
* Fields:
|
|
* resource_name_id The identifier of the resource's name
|
|
* resource_category_id The identifier of the category's name, or NULL
|
|
* if the resource is not in a category
|
|
* resource_description_id The identifier of the resource's description
|
|
* resource_ordering The index of the resource in a sorted view
|
|
*/
|
|
DROP VIEW IF EXISTS defs.ordered_resources_view CASCADE;
|
|
CREATE VIEW defs.ordered_resources_view
|
|
AS SELECT resource_name_id , resource_category_id , resource_description_id ,
|
|
row_number( ) OVER(
|
|
ORDER BY (
|
|
CASE
|
|
WHEN resource_category_id IS NULL THEN
|
|
resource_weight
|
|
ELSE
|
|
resource_category_weight
|
|
END ) , resource_weight
|
|
) AS resource_ordering
|
|
|
|
FROM defs.resources
|
|
LEFT OUTER JOIN defs.resource_category_weight_view
|
|
USING ( resource_category_id )
|
|
|
|
;
|