82 lines
2.3 KiB
MySQL
82 lines
2.3 KiB
MySQL
|
-- LegacyWorlds Beta 5
|
||
|
|
-- PostgreSQL database scripts
|
||
|
|
--
|
||
|
|
-- beta5/structure/02-alliance-tech.sql
|
||
|
|
--
|
||
|
|
-- Beta 5 games:
|
||
|
|
-- Tables for the alliance technology trading tool.
|
||
|
|
--
|
||
|
|
-- Copyright(C) 2004-2007, DeepClone Development
|
||
|
|
-- --------------------------------------------------------
|
||
|
|
|
||
|
|
|
||
|
|
--
|
||
|
|
-- Table tech_trade_list
|
||
|
|
--
|
||
|
|
-- Contains the lists for all players in an alliance
|
||
|
|
--
|
||
|
|
-- status: indicates the player's status w/r to a tech
|
||
|
|
-- N - New technology
|
||
|
|
-- L - Law
|
||
|
|
-- I - Implemented technology
|
||
|
|
-- F - Foreseen breakthrough
|
||
|
|
--
|
||
|
|
|
||
|
|
CREATE TABLE tech_trade_list (
|
||
|
|
alliance INT NOT NULL REFERENCES alliance (id) ON DELETE CASCADE,
|
||
|
|
member BIGINT NOT NULL REFERENCES player (id) ON DELETE CASCADE,
|
||
|
|
tech INT NOT NULL REFERENCES research (id),
|
||
|
|
submitted INT NOT NULL DEFAULT UNIX_TIMESTAMP( NOW() ),
|
||
|
|
status CHAR(1) NOT NULL CHECK(status IN ('N', 'L', 'I', 'F')),
|
||
|
|
PRIMARY KEY(member, tech)
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE INDEX tech_trade_list_alliance ON tech_trade_list (alliance);
|
||
|
|
CREATE INDEX tech_trade_list_tech ON tech_trade_list (tech);
|
||
|
|
|
||
|
|
GRANT INSERT,SELECT,DELETE ON tech_trade_list TO legacyworlds;
|
||
|
|
|
||
|
|
|
||
|
|
--
|
||
|
|
-- Table tech_trade_request
|
||
|
|
--
|
||
|
|
-- Contains the list of requests made by alliance members
|
||
|
|
--
|
||
|
|
|
||
|
|
CREATE TABLE tech_trade_request (
|
||
|
|
alliance INT NOT NULL REFERENCES alliance (id) ON DELETE CASCADE,
|
||
|
|
player BIGINT NOT NULL REFERENCES player (id) ON DELETE CASCADE,
|
||
|
|
priority INT NOT NULL CHECK(priority BETWEEN 0 AND 6),
|
||
|
|
tech INT NOT NULL REFERENCES research (id),
|
||
|
|
|
||
|
|
PRIMARY KEY(player, priority),
|
||
|
|
UNIQUE(player, tech)
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE INDEX tech_trade_req_alliance ON tech_trade_request (alliance);
|
||
|
|
CREATE INDEX tech_trade_req_tech ON tech_trade_request (tech);
|
||
|
|
|
||
|
|
GRANT INSERT,SELECT,DELETE,UPDATE ON tech_trade_request TO legacyworlds;
|
||
|
|
|
||
|
|
|
||
|
|
--
|
||
|
|
-- Table tech_trade_order
|
||
|
|
--
|
||
|
|
-- Contains the orders issued for tech trades
|
||
|
|
--
|
||
|
|
|
||
|
|
CREATE TABLE tech_trade_order (
|
||
|
|
alliance INT NOT NULL REFERENCES alliance (id) ON DELETE CASCADE,
|
||
|
|
player BIGINT NOT NULL REFERENCES player (id) ON DELETE CASCADE,
|
||
|
|
send_to BIGINT NOT NULL UNIQUE REFERENCES player (id) ON DELETE CASCADE,
|
||
|
|
tech INT NOT NULL REFERENCES research (id),
|
||
|
|
submitted INT NOT NULL DEFAULT UNIX_TIMESTAMP( NOW() ),
|
||
|
|
obeyed INT,
|
||
|
|
PRIMARY KEY(player)
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE INDEX tech_trade_order_tech ON tech_trade_order (tech);
|
||
|
|
CREATE INDEX tech_trade_order_alliance ON tech_trade_order (alliance);
|
||
|
|
|
||
|
|
GRANT SELECT,INSERT,UPDATE,DELETE ON tech_trade_order TO legacyworlds;
|