Emmanuel BENOîT
e50775ec76
* The main loader script has been updated to generate the list of files it needs to load automatically. As a consequence, files that contained manually-maintained lists of scripts have been removed, and definition directories have been renamed accordingly. * PostgreSQL extension loading and configuration has been moved to a separate script to be loaded automatically in the main transaction. * Data and function definition scripts that had the -data or -functions suffix have been renamed (the suffix is unnecessary). * Unit tests have been reorganised to follow the definition's structure. * Documentation has been improved
121 lines
4.2 KiB
SQL
121 lines
4.2 KiB
SQL
-- LegacyWorlds Beta 6
|
|
-- PostgreSQL database scripts
|
|
--
|
|
-- Resource definitions
|
|
--
|
|
-- Copyright(C) 2004-2011, DeepClone Development
|
|
-- --------------------------------------------------------
|
|
|
|
|
|
/*
|
|
* Common resource definitions
|
|
* ----------------------------
|
|
*
|
|
* The defs.resources table is used to describe the part of a resource's
|
|
* definition that's idependant
|
|
*/
|
|
|
|
CREATE TABLE defs.resources(
|
|
/* The resource identifier is a reference to an I18N string that
|
|
* represents the resource's name. It also serves as the table's
|
|
* primary key.
|
|
*/
|
|
resource_name_id INT NOT NULL PRIMARY KEY ,
|
|
|
|
/* Identifier of an I18N string which serves as the resource's
|
|
* description.
|
|
*/
|
|
resource_description_id INT NOT NULL ,
|
|
|
|
/* Identifier of an I18N string that names the resource's category. This
|
|
* field may be NULL for resources that do not belong to a category.
|
|
*/
|
|
resource_category_id INT ,
|
|
|
|
/* The weight is used when sorting resources. For resources that do not
|
|
* have a category, it is used directly. Otherwise, categories themselves
|
|
* are sorted by average weight of their contents, and resources in
|
|
* categories are sorted by weight.
|
|
*/
|
|
resource_weight INT NOT NULL
|
|
CHECK ( resource_weight > 0 )
|
|
);
|
|
|
|
CREATE UNIQUE INDEX idx_resources_description
|
|
ON defs.resources( resource_description_id );
|
|
CREATE INDEX idx_resources_category
|
|
ON defs.resources( resource_category_id );
|
|
CREATE INDEX idx_resources_weight
|
|
ON defs.resources( resource_weight );
|
|
|
|
ALTER TABLE defs.resources
|
|
ADD CONSTRAINT fk_resources_name
|
|
FOREIGN KEY ( resource_name_id ) REFERENCES defs.strings ,
|
|
ADD CONSTRAINT fk_resources_description
|
|
FOREIGN KEY ( resource_description_id ) REFERENCES defs.strings ,
|
|
ADD CONSTRAINT fk_resources_category
|
|
FOREIGN KEY ( resource_category_id ) REFERENCES defs.strings;
|
|
|
|
|
|
|
|
/*
|
|
* Natural resources
|
|
* ------------------
|
|
*
|
|
* This table contains additional resource information that is used for
|
|
* natural resources only. These fields are used by the universe generator
|
|
* when it creates resource providers.
|
|
*/
|
|
CREATE TABLE defs.natural_resources (
|
|
/* Identifier of the resource definition for this natural resource, which
|
|
* also serves as the primary key for this table.
|
|
*/
|
|
resource_name_id INT NOT NULL PRIMARY KEY ,
|
|
|
|
/* Presence probability, used by the universe generator to determine
|
|
* whether some type of resource should be added to a planet or not. The
|
|
* generator will try to enforce this probability on the universe in
|
|
* general (that is, not assigning the resource to a planet will increase
|
|
* the probability of it being assigned to another).
|
|
*/
|
|
natres_p_presence DOUBLE PRECISION NOT NULL
|
|
CHECK ( natres_p_presence > 0
|
|
AND natres_p_presence < 1 ) ,
|
|
|
|
/* Average quantity in resource providers. */
|
|
natres_quantity_avg DOUBLE PRECISION NOT NULL
|
|
CHECK( natres_quantity_avg > 0 ) ,
|
|
/* Maximal deviation from the average quantity. */
|
|
natres_quantity_dev DOUBLE PRECISION NOT NULL
|
|
CHECK( natres_quantity_dev >= 0 ) ,
|
|
|
|
/* Average extraction difficulty assigned to resource providers */
|
|
natres_difficulty_avg DOUBLE PRECISION NOT NULL
|
|
CHECK( natres_difficulty_avg BETWEEN 0 AND 1 ) ,
|
|
/* Maximal deviation from the average extraction difficulty */
|
|
natres_difficulty_dev DOUBLE PRECISION NOT NULL
|
|
CHECK( natres_difficulty_dev >= 0 ) ,
|
|
|
|
/* Average recovery rate for resource providers */
|
|
natres_recovery_avg DOUBLE PRECISION NOT NULL
|
|
CHECK( natres_recovery_avg > 0
|
|
AND natres_recovery_avg <= 1 ) ,
|
|
/* Maximal deviation from the average recovery rate */
|
|
natres_recovery_dev DOUBLE PRECISION NOT NULL
|
|
CHECK( natres_recovery_dev >= 0 ) ,
|
|
|
|
/*
|
|
* For all values which include both an average and a maximal deviation,
|
|
* make sure the range defined by the deviation is still within valid
|
|
* boundaries for the value in question.
|
|
*/
|
|
CHECK( natres_quantity_avg - natres_quantity_dev > 0 ) ,
|
|
CHECK( natres_difficulty_avg + natres_difficulty_dev <= 1 ) ,
|
|
CHECK( natres_difficulty_avg - natres_difficulty_dev >= 0 ) ,
|
|
CHECK( natres_recovery_avg + natres_recovery_dev <= 1 ) ,
|
|
CHECK( natres_recovery_avg - natres_recovery_dev > 0 )
|
|
);
|
|
|
|
ALTER TABLE defs.natural_resources
|
|
ADD CONSTRAINT fk_natres_resource
|
|
FOREIGN KEY ( resource_name_id ) REFERENCES defs.resources;
|