361 lines
9.2 KiB
SQL
361 lines
9.2 KiB
SQL
-- LegacyWorlds Beta 6
|
|
-- PostgreSQL database scripts
|
|
--
|
|
-- Empires and alliances
|
|
--
|
|
-- Copyright(C) 2004-2010, DeepClone Development
|
|
-- --------------------------------------------------------
|
|
|
|
|
|
--
|
|
-- Empires
|
|
--
|
|
|
|
CREATE TABLE emp.empires(
|
|
name_id INT NOT NULL PRIMARY KEY ,
|
|
cash REAL NOT NULL
|
|
CHECK( cash >= 0 ),
|
|
debt REAL NOT NULL DEFAULT 0
|
|
CHECK( debt >= 0)
|
|
);
|
|
|
|
ALTER TABLE emp.empires
|
|
ADD CONSTRAINT fk_empires_name
|
|
FOREIGN KEY (name_id) REFERENCES naming.empire_names;
|
|
|
|
|
|
/*
|
|
* Empire resources
|
|
* -----------------
|
|
*
|
|
* This table contains the list of resources possessed and owed by each
|
|
* empire.
|
|
*/
|
|
|
|
CREATE TABLE emp.resources(
|
|
/* Identifier of the empire */
|
|
empire_id INT NOT NULL ,
|
|
|
|
/* Identifier of the type of resource */
|
|
resource_name_id INT NOT NULL ,
|
|
|
|
/* Amount of that specific resource possessed by the empire */
|
|
empres_possessed DOUBLE PRECISION NOT NULL
|
|
DEFAULT 0
|
|
CHECK( empres_possessed >= 0 ) ,
|
|
|
|
/* Amount of that specific resource owed by the empire. This value is
|
|
* used to accumulate debts and that, in turn, is used when computing
|
|
* debt-related fleet and building destruction.
|
|
*/
|
|
empres_owed DOUBLE PRECISION NOT NULL
|
|
DEFAULT 0
|
|
CHECK( empres_owed >= 0 ) ,
|
|
|
|
/* There is only one entry for each (empire,type of resource) pair */
|
|
PRIMARY KEY( empire_id , resource_name_id )
|
|
);
|
|
|
|
CREATE INDEX idx_empres_resource
|
|
ON emp.resources ( resource_name_id );
|
|
|
|
ALTER TABLE emp.resources
|
|
ADD CONSTRAINT fk_empres_empire
|
|
FOREIGN KEY ( empire_id ) REFERENCES emp.empires
|
|
ON DELETE CASCADE ,
|
|
ADD CONSTRAINT fk_empres_resource
|
|
FOREIGN KEY ( resource_name_id ) REFERENCES defs.resources;
|
|
|
|
|
|
|
|
/*
|
|
* States for empire technologies
|
|
* -------------------------------
|
|
*
|
|
* This enumerated type represents the 3 possible state for an empire
|
|
* technology: being researched, pending implementation, or implemented.
|
|
*/
|
|
|
|
DROP TYPE IF EXISTS emp.technology_state CASCADE;
|
|
CREATE TYPE emp.technology_state
|
|
AS ENUM(
|
|
/* Research in progress */
|
|
'RESEARCH' ,
|
|
/* Implementation pending */
|
|
'PENDING' ,
|
|
/* Implemented technology */
|
|
'KNOWN'
|
|
);
|
|
|
|
|
|
/*
|
|
* Empire technologies
|
|
* --------------------
|
|
*
|
|
* This table stores technologies that are being researched, need to be or
|
|
* have been implemented by an empire. Technologies that are being researched
|
|
* include additional information that represent the research progress and
|
|
* priority.
|
|
*
|
|
* FIXME: this table must be renamed to emp.technologies after the old
|
|
* research system has been removed
|
|
*/
|
|
|
|
CREATE TABLE emp.technologies(
|
|
/* Identifier of the empire */
|
|
empire_id INT NOT NULL ,
|
|
|
|
/* Identifier of the technology */
|
|
technology_name_id INT NOT NULL ,
|
|
|
|
/* Current state of the technology */
|
|
emptech_state emp.technology_state NOT NULL
|
|
DEFAULT 'RESEARCH' ,
|
|
|
|
/* Accumulated research points, or NULL if research is over */
|
|
emptech_points DOUBLE PRECISION
|
|
DEFAULT 0.0 ,
|
|
|
|
/* Research priority, or NULL if research is over */
|
|
emptech_priority INT DEFAULT 2
|
|
CHECK( emptech_priority BETWEEN 0 AND 4 ) ,
|
|
|
|
/* Primary key is the empire / technology pair */
|
|
PRIMARY KEY( empire_id , technology_name_id ) ,
|
|
|
|
/* Make sure there are both research points and a priority during research
|
|
* and neither when the technology is pending implementation or
|
|
* implemented.
|
|
*/
|
|
CHECK( CASE emptech_state
|
|
WHEN 'RESEARCH' THEN
|
|
( emptech_points IS NOT NULL AND emptech_priority IS NOT NULL )
|
|
ELSE
|
|
( emptech_points IS NULL AND emptech_priority IS NULL )
|
|
END )
|
|
);
|
|
|
|
CREATE INDEX idx_emptech_technology
|
|
ON emp.technologies ( technology_name_id );
|
|
|
|
ALTER TABLE emp.technologies
|
|
ADD CONSTRAINT fk_emptech_empire
|
|
FOREIGN KEY ( empire_id ) REFERENCES emp.empires ( name_id )
|
|
ON DELETE CASCADE ,
|
|
ADD CONSTRAINT fk_emptech_technology
|
|
FOREIGN KEY ( technology_name_id ) REFERENCES defs.technologies;
|
|
|
|
|
|
--
|
|
-- Empire planets
|
|
--
|
|
|
|
CREATE TABLE emp.planets(
|
|
planet_id INT NOT NULL PRIMARY KEY ,
|
|
empire_id INT NOT NULL
|
|
);
|
|
|
|
CREATE INDEX idx_planets_empire
|
|
ON emp.planets (empire_id);
|
|
|
|
ALTER TABLE emp.planets
|
|
ADD CONSTRAINT fk_eplanets_planet
|
|
FOREIGN KEY (planet_id) REFERENCES verse.planets ,
|
|
ADD CONSTRAINT fk_eplanets_empire
|
|
FOREIGN KEY (empire_id) REFERENCES emp.empires
|
|
ON DELETE CASCADE;
|
|
|
|
|
|
/*
|
|
* Empire mining settings
|
|
* -----------------------
|
|
*
|
|
* This table is used to store general empire mining settings. When the empire
|
|
* gains control over a new planet, or if the planet does not have specific
|
|
* settings, these settings are used to determine the amount of work units
|
|
* that go into extracting each type of resource.
|
|
*
|
|
* Empire-wide settings are ignored if the planet has specific settings, or if
|
|
* none of the resources present on the planet have a positive weight (in
|
|
* which case all present resources are extracted as if they had the same
|
|
* weight).
|
|
*/
|
|
|
|
CREATE TABLE emp.mining_settings(
|
|
/* Identifier of the empire */
|
|
empire_id INT NOT NULL ,
|
|
|
|
/* Identifier of the type of resources */
|
|
resource_name_id INT NOT NULL ,
|
|
|
|
/* Weight to give to this type of resource when there are no planet-
|
|
* specific settings. The weight is a value between 0 (lowest priority)
|
|
* and 4 (highest priority)
|
|
*/
|
|
empmset_weight INT NOT NULL
|
|
DEFAULT 2
|
|
CHECK( empmset_weight BETWEEN 0 AND 4 ) ,
|
|
|
|
/* Primary key on (empire,resource type) pairs */
|
|
PRIMARY KEY( empire_id , resource_name_id )
|
|
);
|
|
|
|
CREATE INDEX idx_empmset_resource
|
|
ON emp.mining_settings ( resource_name_id );
|
|
|
|
ALTER TABLE emp.mining_settings
|
|
ADD CONSTRAINT fk_empmset_empire
|
|
FOREIGN KEY ( empire_id ) REFERENCES emp.empires
|
|
ON DELETE CASCADE ,
|
|
ADD CONSTRAINT fk_empmset_resource
|
|
FOREIGN KEY ( resource_name_id ) REFERENCES defs.natural_resources;
|
|
|
|
|
|
/*
|
|
* Planet-specific mining settings
|
|
* --------------------------------
|
|
*
|
|
* Empire may set planet-specific mining settings when they own a planet. Even
|
|
* once the empire abandons the planet (or when it is taken away), the
|
|
* settings are kept in the database and restored if the empire takes control
|
|
* over the planet again.
|
|
*/
|
|
|
|
CREATE TABLE emp.planet_mining_settings(
|
|
/* Identifier of the empire */
|
|
empire_id INT NOT NULL ,
|
|
|
|
/* The identifier of the planet */
|
|
planet_id INT NOT NULL ,
|
|
|
|
/* Identifier of the type of resources */
|
|
resource_name_id INT NOT NULL ,
|
|
|
|
/* Weight to give to this type of resource. Works in a manner similar to
|
|
* the empmset_weight column of emp.mining_settings.
|
|
*/
|
|
emppmset_weight INT NOT NULL
|
|
DEFAULT 2
|
|
CHECK( emppmset_weight BETWEEN 0 AND 4 ) ,
|
|
|
|
/* Primary key on (empire,resource type) pairs */
|
|
PRIMARY KEY( empire_id , planet_id , resource_name_id )
|
|
);
|
|
|
|
CREATE INDEX idx_emppmset_provider
|
|
ON emp.planet_mining_settings ( planet_id , resource_name_id );
|
|
|
|
ALTER TABLE emp.planet_mining_settings
|
|
ADD CONSTRAINT fk_emppmset_empire
|
|
FOREIGN KEY ( empire_id ) REFERENCES emp.empires
|
|
ON DELETE CASCADE ,
|
|
ADD CONSTRAINT fk_emppmset_resource
|
|
FOREIGN KEY ( planet_id , resource_name_id )
|
|
REFERENCES verse.resource_providers;
|
|
|
|
|
|
--
|
|
-- Planets being abandonned
|
|
--
|
|
|
|
CREATE TABLE emp.abandon(
|
|
planet_id INT NOT NULL PRIMARY KEY ,
|
|
time_left INT NOT NULL CHECK ( time_left > 0 )
|
|
);
|
|
|
|
CREATE INDEX idx_abandon_ready
|
|
ON emp.abandon ( ( time_left = 1 ) );
|
|
|
|
ALTER TABLE emp.abandon
|
|
ADD CONSTRAINT fk_abandon_planet
|
|
FOREIGN KEY (planet_id) REFERENCES emp.planets
|
|
ON DELETE CASCADE;
|
|
|
|
|
|
--
|
|
-- Alliances
|
|
--
|
|
|
|
CREATE TABLE emp.alliances(
|
|
id SERIAL NOT NULL PRIMARY KEY ,
|
|
tag VARCHAR(5) NOT NULL ,
|
|
name VARCHAR(128) NOT NULL ,
|
|
leader_id INT NOT NULL
|
|
);
|
|
|
|
CREATE UNIQUE INDEX idx_alliances_tag
|
|
ON emp.alliances ( lower(tag) );
|
|
CREATE UNIQUE INDEX idx_alliances_leader
|
|
ON emp.alliances (leader_id);
|
|
|
|
ALTER TABLE emp.alliances
|
|
ADD CONSTRAINT fk_alliances_leader
|
|
FOREIGN KEY (leader_id) REFERENCES emp.empires
|
|
ON DELETE CASCADE;
|
|
|
|
|
|
--
|
|
-- Alliance membership
|
|
--
|
|
|
|
CREATE TABLE emp.alliance_members(
|
|
empire_id INT NOT NULL PRIMARY KEY ,
|
|
alliance_id INT NOT NULL ,
|
|
is_pending BOOLEAN NOT NULL
|
|
DEFAULT TRUE
|
|
);
|
|
|
|
CREATE INDEX idx_alliancemembers_alliance
|
|
ON emp.alliance_members( alliance_id );
|
|
|
|
ALTER TABLE emp.alliance_members
|
|
ADD CONSTRAINT fk_alliancemembers_empire
|
|
FOREIGN KEY (empire_id) REFERENCES emp.empires
|
|
ON DELETE CASCADE ,
|
|
ADD CONSTRAINT fk_alliancemembers_alliance
|
|
FOREIGN KEY (alliance_id) REFERENCES emp.alliances
|
|
ON DELETE CASCADE;
|
|
|
|
|
|
--
|
|
-- Enemy lists, alliances
|
|
--
|
|
|
|
CREATE TABLE emp.enemy_alliances(
|
|
empire_id INT NOT NULL ,
|
|
alliance_id INT NOT NULL ,
|
|
PRIMARY KEY (empire_id,alliance_id)
|
|
);
|
|
|
|
CREATE INDEX idx_enemyalliances_alliance
|
|
ON emp.enemy_alliances (alliance_id);
|
|
|
|
ALTER TABLE emp.enemy_alliances
|
|
ADD CONSTRAINT fk_enemyalliances_empire
|
|
FOREIGN KEY (empire_id) REFERENCES emp.empires
|
|
ON DELETE CASCADE ,
|
|
ADD CONSTRAINT fk_enemyalliances_alliance
|
|
FOREIGN KEY (alliance_id) REFERENCES emp.alliances
|
|
ON DELETE CASCADE;
|
|
|
|
|
|
--
|
|
-- Enemy lists, empires
|
|
--
|
|
|
|
CREATE TABLE emp.enemy_empires(
|
|
empire_id INT NOT NULL ,
|
|
enemy_id INT NOT NULL ,
|
|
PRIMARY KEY (empire_id,enemy_id)
|
|
);
|
|
|
|
CREATE INDEX idx_enemyempires_enemy
|
|
ON emp.enemy_empires (enemy_id);
|
|
|
|
ALTER TABLE emp.enemy_empires
|
|
ADD CONSTRAINT fk_enemyempires_empire
|
|
FOREIGN KEY (empire_id) REFERENCES emp.empires
|
|
ON DELETE CASCADE ,
|
|
ADD CONSTRAINT fk_enemyempires_enemy
|
|
FOREIGN KEY (enemy_id) REFERENCES emp.empires
|
|
ON DELETE CASCADE;
|