Emmanuel BENOîT
d246f221f0
Added tables that store event access interfaces and event states per interface, as well as functions which allow events to be retrieved: * events.interfaces lists access interfaces, * events.unprocessed_events lists events which haven't been processed for each type of interface, with a "pre-processed" flag * events.ep_read() is a set of function variants which will read events * events.ep_access() is a set of function variants which read events then update their states.
750 lines
19 KiB
SQL
750 lines
19 KiB
SQL
-- LegacyWorlds Beta 6
|
|
-- PostgreSQL database scripts
|
|
--
|
|
-- Storage of events
|
|
--
|
|
-- Copyright(C) 2004-2012, DeepClone Development
|
|
-- --------------------------------------------------------
|
|
|
|
|
|
/*
|
|
* Event type definitions
|
|
* -----------------------
|
|
*
|
|
* This table stores the "root" of event type definitions. Each definition is
|
|
* uniquely identifier by a short string.
|
|
*/
|
|
DROP TABLE IF EXISTS events.event_definitions CASCADE;
|
|
CREATE TABLE events.event_definitions(
|
|
/* The event definition identifier */
|
|
evdef_id VARCHAR( 48 ) NOT NULL PRIMARY KEY ,
|
|
|
|
/* The default priority of the events of this type */
|
|
evdef_priority INT NOT NULL
|
|
CHECK( evdef_priority BETWEEN 0 AND 4 ) ,
|
|
|
|
/* Whether the priority for this type of event may be adjusted */
|
|
evdef_adjustable BOOLEAN NOT NULL ,
|
|
|
|
/* Internationalised string that contains the name of the event type;
|
|
* used when displaying priority settings.
|
|
*/
|
|
evdef_name_id INT NOT NULL ,
|
|
|
|
/* Internationalised string that contains the template to use when
|
|
* generating the output for a single event.
|
|
*/
|
|
evdef_template_id INT NOT NULL
|
|
);
|
|
|
|
/* Unique index allowing the custom priorities table to reference only
|
|
* adjustable event types.
|
|
*/
|
|
CREATE UNIQUE INDEX idx_evdef_adjustables
|
|
ON events.event_definitions( evdef_id , evdef_adjustable );
|
|
/* Foreign key indexes */
|
|
CREATE UNIQUE INDEX idx_evdef_name
|
|
ON events.event_definitions( evdef_name_id );
|
|
CREATE UNIQUE INDEX idx_evdef_template
|
|
ON events.event_definitions( evdef_template_id );
|
|
|
|
ALTER TABLE events.event_definitions
|
|
ADD CONSTRAINT fk_evdef_name
|
|
FOREIGN KEY ( evdef_name_id ) REFERENCES defs.strings ,
|
|
ADD CONSTRAINT fk_evdef_template
|
|
FOREIGN KEY ( evdef_template_id ) REFERENCES defs.strings;
|
|
|
|
GRANT SELECT ON events.event_definitions TO :dbuser;
|
|
|
|
|
|
/*
|
|
* General event field types
|
|
* --------------------------
|
|
*/
|
|
DROP TYPE IF EXISTS events.field_type CASCADE;
|
|
CREATE TYPE events.field_type AS ENUM(
|
|
/* A numeric event field */
|
|
'NUMERIC' ,
|
|
/* A raw text event field */
|
|
'TEXT' ,
|
|
/* A boolean event field */
|
|
'BOOL' ,
|
|
/* An internationalised text field */
|
|
'I18N' ,
|
|
/* A field which links to another game entity */
|
|
'ENTITY'
|
|
);
|
|
|
|
|
|
/*
|
|
* Entity field sub-types
|
|
* -----------------------
|
|
*
|
|
* FIXME: no message sub-type for now
|
|
*/
|
|
DROP TYPE IF EXISTS events.entity_field_type CASCADE;
|
|
CREATE TYPE events.entity_field_type AS ENUM(
|
|
/* An empire */
|
|
'EMP' ,
|
|
/* A planet */
|
|
'PLN' ,
|
|
/* A fleet */
|
|
'FLT' ,
|
|
/* An alliance */
|
|
'ALL' ,
|
|
/* A battle */
|
|
'BAT' ,
|
|
/* An administrator */
|
|
'ADM' ,
|
|
/* A bug report */
|
|
'BUG'
|
|
);
|
|
|
|
|
|
/*
|
|
* Event field definition
|
|
* -----------------------
|
|
*
|
|
* An event field definition can be associated with an event definition to
|
|
* specify which fields an event will possess.
|
|
*/
|
|
DROP TABLE IF EXISTS events.field_definitions CASCADE;
|
|
CREATE TABLE events.field_definitions (
|
|
/* Identifier of the event type definition */
|
|
evdef_id VARCHAR( 48 ) NOT NULL ,
|
|
|
|
/* Identifier of the field itself */
|
|
efdef_id VARCHAR( 48 ) NOT NULL ,
|
|
|
|
/* Whether the field is optional or mandatory */
|
|
efdef_optional BOOLEAN NOT NULL ,
|
|
|
|
/* General type of the event field */
|
|
efdef_type events.field_type NOT NULL ,
|
|
|
|
/* Entity type if the field is an entity */
|
|
efdef_entity events.entity_field_type ,
|
|
|
|
/* Whether the field should contain an integer or a real number, if the
|
|
* field is numeric.
|
|
*/
|
|
efdef_integer BOOLEAN ,
|
|
|
|
/* Minimal value or length of the field if it is either a numeric field or
|
|
* a text field.
|
|
*/
|
|
efdef_min NUMERIC ,
|
|
|
|
/* Maximal value or length of the field if it is either a numeric field or
|
|
* a text field.
|
|
*/
|
|
efdef_max NUMERIC ,
|
|
|
|
/* Use both the event type identifier and the field identifier as
|
|
* the primary key
|
|
*/
|
|
PRIMARY KEY( evdef_id , efdef_id ) ,
|
|
|
|
/* Entity type is NULL unless the field is an entity field */
|
|
CONSTRAINT ck_efdef_entity CHECK( CASE
|
|
WHEN efdef_type = 'ENTITY' THEN
|
|
efdef_entity IS NOT NULL
|
|
ELSE
|
|
efdef_entity IS NULL
|
|
END ) ,
|
|
|
|
/* Make sure the integer flag is set only when the field is numeric */
|
|
CONSTRAINT ck_efdef_numtype CHECK( CASE
|
|
WHEN efdef_type = 'NUMERIC' THEN
|
|
efdef_integer IS NOT NULL
|
|
ELSE
|
|
efdef_integer IS NULL
|
|
END ) ,
|
|
|
|
/* Minimal / maximal values only allowed for numerics and raw strings */
|
|
CONSTRAINT ck_efdef_hasminmax CHECK( CASE
|
|
WHEN efdef_type IN ( 'NUMERIC' , 'TEXT' ) THEN
|
|
TRUE
|
|
ELSE
|
|
efdef_min IS NULL AND efdef_max IS NULL
|
|
END ) ,
|
|
|
|
/* If both a minimal and maximal values are present, minimal must be lower
|
|
* than maximal.
|
|
*/
|
|
CONSTRAINT ck_efdef_minmaxvalues CHECK( CASE
|
|
WHEN efdef_min IS NULL OR efdef_max IS NULL THEN
|
|
TRUE
|
|
ELSE
|
|
efdef_min < efdef_max
|
|
END )
|
|
);
|
|
|
|
ALTER TABLE events.field_definitions
|
|
ADD CONSTRAINT fk_efdef_evdef
|
|
FOREIGN KEY ( evdef_id ) REFERENCES events.event_definitions
|
|
ON DELETE CASCADE;
|
|
|
|
GRANT SELECT ON events.field_definitions TO :dbuser;
|
|
|
|
|
|
/*
|
|
* Custom event priorities
|
|
* -----------------------
|
|
*
|
|
* This table stores player-defined event priority overrides. Only events
|
|
* with custom priorities may be present in this table.
|
|
*/
|
|
DROP TABLE IF EXISTS events.custom_priorities CASCADE;
|
|
CREATE TABLE events.custom_priorities(
|
|
/* Identifier of the event type */
|
|
evdef_id VARCHAR(48) NOT NULL ,
|
|
|
|
/* Used in reference to the event types - forces this table to
|
|
* reference only events that *can* be customised.
|
|
*/
|
|
evdef_adjustable BOOLEAN NOT NULL DEFAULT TRUE
|
|
CHECK( evdef_adjustable ) ,
|
|
|
|
/* Account identifier */
|
|
address_id INT NOT NULL ,
|
|
|
|
/* Custom priority */
|
|
evcp_priority INT NOT NULL
|
|
CHECK( evcp_priority BETWEEN 0 AND 4 ) ,
|
|
|
|
/* Use the event identifier and "adjustable" set value as the primary
|
|
* key. The advantage is that we get an index on both fields.
|
|
*/
|
|
PRIMARY KEY( evdef_id , evdef_adjustable )
|
|
);
|
|
|
|
ALTER TABLE events.custom_priorities
|
|
ADD CONSTRAINT fk_evcp_evdef
|
|
FOREIGN KEY ( evdef_id , evdef_adjustable )
|
|
REFERENCES events.event_definitions ( evdef_id , evdef_adjustable );
|
|
|
|
GRANT SELECT ON events.custom_priorities TO :dbuser;
|
|
|
|
|
|
/*
|
|
* Event identifier sequence
|
|
* -------------------------
|
|
*
|
|
* This sequence is used by the various type-specific event queues to
|
|
* generate the events' identifiers.
|
|
*/
|
|
DROP SEQUENCE IF EXISTS events.event_id_sequence CASCADE;
|
|
CREATE SEQUENCE events.event_id_sequence;
|
|
GRANT SELECT,UPDATE ON events.event_id_sequence TO :dbuser;
|
|
|
|
|
|
/*
|
|
* Pending events
|
|
* --------------
|
|
*
|
|
* This table is updated when events are inserted into queues and when they
|
|
* are converted. It tracks events that need to be converted.
|
|
*
|
|
* Note: the table does not have a primary key or foreign keys; it is simply
|
|
* a cache which must not get in the way of inserts into queues.
|
|
*
|
|
* Warning: a record in this table may be orphaned, i.e. there could be no
|
|
* corresponding entry in the queue table.
|
|
*/
|
|
DROP TABLE IF EXISTS events.pending_events CASCADE;
|
|
CREATE TABLE events.pending_events(
|
|
/* Event identifier */
|
|
event_id BIGINT NOT NULL ,
|
|
/* Event type */
|
|
evdef_id VARCHAR(48) NOT NULL
|
|
);
|
|
|
|
|
|
/*
|
|
* Event storage table
|
|
* -------------------
|
|
*
|
|
* This table stores the main entries for actual events. It is fed using the
|
|
* various type-specific event queues.
|
|
*
|
|
* FIXME: this table should be renamed once the old internal messages are
|
|
* gone.
|
|
*/
|
|
DROP TABLE IF EXISTS events.events_v2 CASCADE;
|
|
CREATE TABLE events.events_v2(
|
|
/* Numeric identifier of the event */
|
|
event_id BIGINT NOT NULL PRIMARY KEY ,
|
|
|
|
/* Type of the event */
|
|
evdef_id VARCHAR( 48 ) NOT NULL ,
|
|
|
|
/* Real time at which the event occurred */
|
|
event_rtime TIMESTAMP WITHOUT TIME ZONE
|
|
NOT NULL ,
|
|
|
|
/* Game time (tick identifier) at which the event occurred */
|
|
event_gtime BIGINT NOT NULL ,
|
|
|
|
/* Identifier of the empire to which the event applies */
|
|
empire_id INT NOT NULL
|
|
);
|
|
|
|
CREATE UNIQUE INDEX idx_events_iddef
|
|
ON events.events_v2 ( event_id , evdef_id );
|
|
CREATE INDEX idx_events_selector
|
|
ON events.events_v2 ( empire_id , event_rtime );
|
|
|
|
ALTER TABLE events.events_v2
|
|
ADD CONSTRAINT fk_events_evdef
|
|
FOREIGN KEY ( evdef_id ) REFERENCES events.event_definitions ( evdef_id ) ,
|
|
ADD CONSTRAINT fk_events_empire
|
|
FOREIGN KEY ( empire_id ) REFERENCES emp.empires ( name_id )
|
|
ON DELETE CASCADE;
|
|
|
|
GRANT SELECT ON events.events_v2 TO :dbuser;
|
|
|
|
|
|
/*
|
|
* Event field values storage table
|
|
* --------------------------------
|
|
*
|
|
* This table stores the values of the events' fields.
|
|
*/
|
|
DROP TABLE IF EXISTS events.field_values CASCADE;
|
|
CREATE TABLE events.field_values(
|
|
/* The event's identifier */
|
|
event_id BIGINT NOT NULL ,
|
|
|
|
/* The event's type */
|
|
evdef_id VARCHAR(48) NOT NULL ,
|
|
|
|
/* The field's identifier */
|
|
efdef_id VARCHAR(48) NOT NULL ,
|
|
|
|
/* Literal field value */
|
|
efval_litteral TEXT NOT NULL ,
|
|
|
|
/* I18N string reference */
|
|
string_id INT ,
|
|
|
|
/* Empire reference */
|
|
empire_id INT ,
|
|
|
|
/* Planet reference */
|
|
planet_id INT ,
|
|
|
|
/* Fleet reference */
|
|
fleet_id BIGINT ,
|
|
|
|
/* Alliance reference */
|
|
alliance_id INT ,
|
|
|
|
/* Battle reference */
|
|
battle_id BIGINT ,
|
|
|
|
/* Administrator reference */
|
|
admin_id INT ,
|
|
|
|
/* Bug report reference */
|
|
bug_report_id BIGINT ,
|
|
|
|
PRIMARY KEY( event_id , evdef_id , efdef_id )
|
|
);
|
|
|
|
CREATE INDEX idx_fvalues_string
|
|
ON events.field_values ( string_id );
|
|
CREATE INDEX idx_fvalues_empire
|
|
ON events.field_values ( empire_id );
|
|
CREATE INDEX idx_fvalues_planet
|
|
ON events.field_values ( planet_id );
|
|
CREATE INDEX idx_fvalues_fleet
|
|
ON events.field_values ( fleet_id );
|
|
CREATE INDEX idx_fvalues_alliance
|
|
ON events.field_values ( alliance_id );
|
|
CREATE INDEX idx_fvalues_battle
|
|
ON events.field_values ( battle_id );
|
|
CREATE INDEX idx_fvalues_admin
|
|
ON events.field_values ( admin_id );
|
|
CREATE INDEX idx_fvalues_bugreport
|
|
ON events.field_values ( bug_report_id );
|
|
|
|
ALTER TABLE events.field_values
|
|
ADD CONSTRAINT fk_fvalues_event
|
|
FOREIGN KEY ( event_id , evdef_id ) REFERENCES events.events_v2 ( event_id , evdef_id )
|
|
ON DELETE CASCADE ,
|
|
ADD CONSTRAINT fk_fvalues_field
|
|
FOREIGN KEY ( evdef_id , efdef_id ) REFERENCES events.field_definitions ,
|
|
ADD CONSTRAINT fk_fvalues_string
|
|
FOREIGN KEY ( string_id ) REFERENCES defs.strings ,
|
|
ADD CONSTRAINT fk_fvalues_empire
|
|
FOREIGN KEY ( empire_id ) REFERENCES emp.empires
|
|
ON DELETE CASCADE ,
|
|
ADD CONSTRAINT fk_fvalues_planet
|
|
FOREIGN KEY ( planet_id ) REFERENCES verse.planets
|
|
ON DELETE CASCADE ,
|
|
ADD CONSTRAINT fk_fvalues_fleet
|
|
FOREIGN KEY ( fleet_id ) REFERENCES fleets.fleets
|
|
ON DELETE CASCADE ,
|
|
ADD CONSTRAINT fk_fvalues_alliance
|
|
FOREIGN KEY ( alliance_id ) REFERENCES emp.alliances
|
|
ON DELETE CASCADE ,
|
|
ADD CONSTRAINT fk_fvalues_battle
|
|
FOREIGN KEY ( battle_id ) REFERENCES battles.battles
|
|
ON DELETE CASCADE ,
|
|
ADD CONSTRAINT fk_fvalues_admin
|
|
FOREIGN KEY ( admin_id ) REFERENCES admin.administrators
|
|
ON DELETE CASCADE ,
|
|
ADD CONSTRAINT fk_fvalues_bugreport
|
|
FOREIGN KEY ( bug_report_id ) REFERENCES bugs.initial_report_events
|
|
ON DELETE CASCADE;
|
|
|
|
GRANT SELECT ON events.field_values TO :dbuser;
|
|
|
|
|
|
/*
|
|
* Event viewing interfaces
|
|
* ------------------------
|
|
*
|
|
* This table stores the list of interfaces which can be used to access the
|
|
* events. It allows the system to handle multiple types of event viewing or
|
|
* processing: for example the web interface and email notifications.
|
|
*/
|
|
CREATE TABLE events.interfaces(
|
|
|
|
/* A short string that identifies the interface */
|
|
evi_id VARCHAR(8) NOT NULL PRIMARY KEY ,
|
|
|
|
/* A description of the interface */
|
|
evi_descr TEXT NOT NULL
|
|
|
|
);
|
|
|
|
/* Directly insert interface types */
|
|
INSERT INTO events.interfaces( evi_id , evi_descr ) VALUES
|
|
( 'game' , 'The game''s main interface' ) ,
|
|
( 'mail' , 'The e-mail notifications system' );
|
|
|
|
|
|
/*
|
|
* Events to process
|
|
* -----------------
|
|
*
|
|
* This table stores the list of events which need to be processed for each
|
|
* interface.
|
|
*/
|
|
CREATE TABLE events.unprocessed_events(
|
|
/* The event's identifier */
|
|
event_id BIGINT NOT NULL ,
|
|
|
|
/* The interface's identifier */
|
|
evi_id VARCHAR(8) NOT NULL ,
|
|
|
|
/* Whether the interface has pre-processed the event, but not displayed
|
|
* or sent it yet. This is used with e.g. the email notification system,
|
|
* which waits for more events once a first event is ready. When the
|
|
* server restarts, the field is set back to FALSE.
|
|
*/
|
|
upe_preprocessed BOOLEAN NOT NULL
|
|
DEFAULT FALSE ,
|
|
|
|
/* Use both the event and interface as the primary key */
|
|
PRIMARY KEY( event_id , evi_id )
|
|
);
|
|
|
|
CREATE INDEX idx_unprocessed_interface
|
|
ON events.unprocessed_events( evi_id );
|
|
|
|
ALTER TABLE events.unprocessed_events
|
|
ADD CONSTRAINT fk_unprocessed_event
|
|
FOREIGN KEY ( event_id ) REFERENCES events.events_v2 ( event_id )
|
|
ON DELETE CASCADE ,
|
|
ADD CONSTRAINT fk_unprocessed_interface
|
|
FOREIGN KEY ( evi_id ) REFERENCES events.interfaces ( evi_id );
|
|
|
|
|
|
/*
|
|
* OLD B6M1 CODE BELOW!
|
|
*/
|
|
|
|
|
|
CREATE TYPE event_type
|
|
AS ENUM ( 'QUEUE' , 'EMPIRE' , 'FLEETS' , 'PLANET', 'ALLIANCE', 'ADMIN' , 'BUGS' );
|
|
|
|
CREATE TYPE event_status
|
|
AS ENUM( 'TICK' , 'ACTION' , 'READY' , 'SENT' );
|
|
|
|
|
|
|
|
--
|
|
-- Events table
|
|
--
|
|
|
|
CREATE TABLE events.events(
|
|
event_id BIGSERIAL PRIMARY KEY ,
|
|
empire_id INT NOT NULL ,
|
|
tick BIGINT NOT NULL ,
|
|
real_time TIMESTAMP WITHOUT TIME ZONE
|
|
NOT NULL
|
|
DEFAULT now() ,
|
|
evt_type event_type NOT NULL ,
|
|
evt_subtype INT NOT NULL ,
|
|
status event_status NOT NULL
|
|
);
|
|
|
|
CREATE INDEX idx_events_empire
|
|
ON events.events (empire_id);
|
|
|
|
CREATE INDEX idx_events_time
|
|
ON events.events (real_time);
|
|
|
|
CREATE INDEX idx_events_lookup
|
|
ON events.events( empire_id , tick , evt_type , evt_subtype , status )
|
|
WHERE status IN ( 'TICK' , 'ACTION' );
|
|
|
|
ALTER TABLE events.events
|
|
ADD CONSTRAINT fk_events_empire
|
|
FOREIGN KEY ( empire_id ) REFERENCES emp.empires
|
|
ON DELETE CASCADE;
|
|
|
|
GRANT SELECT ON events.events TO :dbuser;
|
|
|
|
|
|
--
|
|
-- Build queue events
|
|
--
|
|
|
|
CREATE TABLE events.queue_events (
|
|
event_id BIGINT NOT NULL PRIMARY KEY
|
|
);
|
|
|
|
ALTER TABLE events.queue_events
|
|
ADD CONSTRAINT fk_bqevents_event
|
|
FOREIGN KEY (event_id) REFERENCES events.events
|
|
ON DELETE CASCADE;
|
|
|
|
|
|
|
|
--
|
|
-- Build queue event locations
|
|
--
|
|
|
|
CREATE TABLE events.bqe_locations(
|
|
event_id BIGINT NOT NULL ,
|
|
location_id INT NOT NULL ,
|
|
location_name VARCHAR(20) NOT NULL ,
|
|
PRIMARY KEY( event_id , location_id )
|
|
);
|
|
|
|
ALTER TABLE events.bqe_locations
|
|
ADD CONSTRAINT fk_bqelocations_event
|
|
FOREIGN KEY (event_id) REFERENCES events.queue_events
|
|
ON DELETE CASCADE ,
|
|
ADD CONSTRAINT fk_bqelocations_location
|
|
FOREIGN KEY (location_id) REFERENCES verse.planets;
|
|
|
|
|
|
--
|
|
-- Fleet events
|
|
--
|
|
|
|
CREATE TABLE events.fleets_events(
|
|
event_id BIGINT NOT NULL PRIMARY KEY ,
|
|
location_id INT NOT NULL ,
|
|
location_name VARCHAR(20) NOT NULL
|
|
);
|
|
|
|
CREATE INDEX idx_flevents_location
|
|
ON events.fleets_events( location_id );
|
|
|
|
ALTER TABLE events.fleets_events
|
|
ADD CONSTRAINT fk_flevents_event
|
|
FOREIGN KEY (event_id) REFERENCES events.events
|
|
ON DELETE CASCADE ,
|
|
ADD CONSTRAINT fk_flevents_location
|
|
FOREIGN KEY (location_id) REFERENCES verse.planets;
|
|
|
|
|
|
|
|
--
|
|
-- Fleets for fleet events
|
|
--
|
|
|
|
CREATE TABLE events.fleet_lists(
|
|
id BIGSERIAL PRIMARY KEY ,
|
|
event_id BIGINT NOT NULL ,
|
|
owner_id INT ,
|
|
owner_name VARCHAR(20) NOT NULL ,
|
|
fleet_name VARCHAR(64) ,
|
|
fleet_power BIGINT NOT NULL CHECK( fleet_power > 0 ) ,
|
|
status BOOLEAN ,
|
|
source_id INT ,
|
|
source_name VARCHAR(20) ,
|
|
CHECK( source_id IS NULL AND source_name IS NULL OR source_id IS NOT NULL AND source_name IS NOT NULL )
|
|
);
|
|
|
|
CREATE INDEX idx_flelists_event
|
|
ON events.fleet_lists( event_id );
|
|
|
|
CREATE INDEX idx_flelists_owner
|
|
ON events.fleet_lists( owner_id )
|
|
WHERE owner_id IS NOT NULL;
|
|
|
|
CREATE INDEX idx_flelists_source
|
|
ON events.fleet_lists( source_id )
|
|
WHERE source_id IS NOT NULL;
|
|
|
|
ALTER TABLE events.fleet_lists
|
|
ADD CONSTRAINT fk_flelist_event
|
|
FOREIGN KEY ( event_id ) REFERENCES events.fleets_events
|
|
ON DELETE CASCADE ,
|
|
ADD CONSTRAINT fk_flelist_owner
|
|
FOREIGN KEY ( owner_id ) REFERENCES emp.empires
|
|
ON DELETE SET NULL ,
|
|
ADD CONSTRAINT fk_flelist_source
|
|
FOREIGN KEY ( source_id ) REFERENCES verse.planets;
|
|
|
|
GRANT SELECT ON events.fleet_lists TO :dbuser;
|
|
|
|
|
|
|
|
--
|
|
-- Planet events
|
|
--
|
|
|
|
CREATE TABLE events.planet_events(
|
|
event_id BIGINT PRIMARY KEY,
|
|
location_id INT NOT NULL ,
|
|
location_name VARCHAR(20) NOT NULL ,
|
|
empire_id INT ,
|
|
empire_name VARCHAR(20) ,
|
|
battle_id BIGINT ,
|
|
CHECK( battle_id IS NULL AND empire_id IS NULL AND empire_name IS NULL
|
|
OR battle_id IS NOT NULL AND empire_id IS NULL AND empire_name IS NULL
|
|
OR battle_id IS NULL AND empire_name IS NOT NULL )
|
|
);
|
|
|
|
CREATE INDEX idx_pevents_event
|
|
ON events.planet_events ( event_id );
|
|
|
|
CREATE INDEX idx_pevents_location
|
|
ON events.planet_events ( location_id );
|
|
|
|
CREATE INDEX idx_pevents_empire
|
|
ON events.planet_events ( empire_id )
|
|
WHERE empire_id IS NOT NULL;
|
|
|
|
CREATE INDEX idx_pevents_battle
|
|
ON events.planet_events ( battle_id )
|
|
WHERE battle_id IS NOT NULL;
|
|
|
|
ALTER TABLE events.planet_events
|
|
ADD CONSTRAINT fk_pevents_event
|
|
FOREIGN KEY ( event_id ) REFERENCES events.events
|
|
ON DELETE CASCADE ,
|
|
ADD CONSTRAINT fk_pevents_location
|
|
FOREIGN KEY ( location_id ) REFERENCES verse.planets ,
|
|
ADD CONSTRAINT fk_pevents_empire
|
|
FOREIGN KEY ( empire_id ) REFERENCES emp.empires
|
|
ON DELETE SET NULL ,
|
|
ADD CONSTRAINT fk_pevents_battle
|
|
FOREIGN KEY ( battle_id ) REFERENCES battles.battles;
|
|
|
|
|
|
|
|
--
|
|
-- Alliance events
|
|
--
|
|
|
|
CREATE TABLE events.alliance_events(
|
|
event_id BIGINT PRIMARY KEY ,
|
|
alliance_id INT ,
|
|
alliance_tag VARCHAR( 5 ) NOT NULL ,
|
|
empire_id INT ,
|
|
empire_name VARCHAR( 20 ) ,
|
|
req_result BOOLEAN ,
|
|
CHECK( req_result IS NULL AND empire_id IS NULL AND empire_name IS NULL
|
|
OR req_result IS NOT NULL AND empire_id IS NULL AND empire_name IS NULL
|
|
OR req_result IS NULL AND empire_name IS NOT NULL )
|
|
);
|
|
|
|
CREATE INDEX idx_aevents_event
|
|
ON events.alliance_events ( event_id );
|
|
|
|
CREATE INDEX idx_aevents_alliance
|
|
ON events.alliance_events ( alliance_id )
|
|
WHERE alliance_id IS NOT NULL;
|
|
|
|
CREATE INDEX idx_aevents_empire
|
|
ON events.alliance_events ( empire_id )
|
|
WHERE empire_id IS NOT NULL;
|
|
|
|
ALTER TABLE events.alliance_events
|
|
ADD CONSTRAINT fk_aevents_event
|
|
FOREIGN KEY ( event_id ) REFERENCES events.events
|
|
ON DELETE CASCADE ,
|
|
ADD CONSTRAINT fk_aevents_allliance
|
|
FOREIGN KEY ( alliance_id ) REFERENCES emp.alliances
|
|
ON DELETE SET NULL ,
|
|
ADD CONSTRAINT fk_aevents_empire
|
|
FOREIGN KEY ( empire_id ) REFERENCES emp.empires
|
|
ON DELETE SET NULL;
|
|
|
|
|
|
|
|
--
|
|
-- Admin events
|
|
--
|
|
|
|
CREATE TABLE events.admin_events(
|
|
event_id BIGINT PRIMARY KEY ,
|
|
n_warnings INT ,
|
|
location_id INT ,
|
|
old_name VARCHAR( 20 ) NOT NULL ,
|
|
new_name VARCHAR( 20 )
|
|
);
|
|
|
|
CREATE INDEX idx_adevents_event
|
|
ON events.admin_events ( event_id );
|
|
|
|
CREATE INDEX idx_adevents_location
|
|
ON events.admin_events ( location_id )
|
|
WHERE location_id IS NOT NULL;
|
|
|
|
ALTER TABLE events.admin_events
|
|
ADD CONSTRAINT fk_adevents_event
|
|
FOREIGN KEY ( event_id ) REFERENCES events.events
|
|
ON DELETE CASCADE ,
|
|
ADD CONSTRAINT fk_adevents_location
|
|
FOREIGN KEY ( location_id ) REFERENCES verse.planets;
|
|
|
|
|
|
|
|
--
|
|
-- Bug tracking events
|
|
--
|
|
|
|
CREATE TABLE events.bug_events(
|
|
event_id BIGINT PRIMARY KEY ,
|
|
bug_id BIGINT NOT NULL ,
|
|
submitter_id BIGINT NOT NULL
|
|
);
|
|
|
|
CREATE INDEX idx_btevents_event
|
|
ON events.bug_events ( event_id );
|
|
|
|
CREATE INDEX idx_btevents_bug
|
|
ON events.bug_events ( bug_id );
|
|
|
|
CREATE INDEX idx_btevents_submitter
|
|
ON events.bug_events ( submitter_id );
|
|
|
|
|
|
ALTER TABLE events.bug_events
|
|
ADD CONSTRAINT fk_btevents_event
|
|
FOREIGN KEY ( event_id ) REFERENCES events.events
|
|
ON DELETE CASCADE ,
|
|
ADD CONSTRAINT fk_btevents_bug
|
|
FOREIGN KEY ( bug_id ) REFERENCES bugs.initial_report_events ,
|
|
ADD CONSTRAINT fk_btevents_submitter
|
|
FOREIGN KEY ( submitter_id ) REFERENCES bugs.submitters;
|