diff options
Diffstat (limited to 'migrations')
| -rw-r--r-- | migrations/00000000000000_diesel_initial_setup/down.sql | 11 | ||||
| -rw-r--r-- | migrations/00000000000000_diesel_initial_setup/up.sql | 96 |
2 files changed, 107 insertions, 0 deletions
diff --git a/migrations/00000000000000_diesel_initial_setup/down.sql b/migrations/00000000000000_diesel_initial_setup/down.sql new file mode 100644 index 0000000..74a7b3d --- /dev/null +++ b/migrations/00000000000000_diesel_initial_setup/down.sql @@ -0,0 +1,11 @@ +-- down.sql: Destroy initial database migration for AO +DROP FUNCTION IF EXISTS public.GETDATE(); + +DROP TABLE users; +DROP TABLE activation_keys; +DROP TABLE games; +DROP TABLE allegiances; +DROP TABLE regions; +DROP TABLE nationstates; +DROP TABLE borders; +DROP TABLE games_nationstates;
\ No newline at end of file diff --git a/migrations/00000000000000_diesel_initial_setup/up.sql b/migrations/00000000000000_diesel_initial_setup/up.sql new file mode 100644 index 0000000..83a7869 --- /dev/null +++ b/migrations/00000000000000_diesel_initial_setup/up.sql @@ -0,0 +1,96 @@ + -- up.sql: Initial database migration for AO + +CREATE OR REPLACE FUNCTION public.GETDATE() RETURNS TIMESTAMPTZ + STABLE LANGUAGE SQL AS 'SELECT NOW()'; + + -- base table for users +CREATE TABLE users ( + userid SERIAL, + email VARCHAR(40) UNIQUE NOT NULL, + firstname VARCHAR(20) NOT NULL, + lastname VARCHAR(20) NOT NULL, + password VARCHAR(255) NOT NULL, -- intend to use bcrypt hash + joindate DATE DEFAULT GETDATE(), + activated BIT(1) DEFAULT B'0', -- see activation_keys table + PRIMARY KEY (userid) +); + + -- when activation is completed, key gets deleted and activated bit for user gets flipped to 1. +CREATE TABLE activation_keys ( + code VARCHAR(128), + userid SERIAL, + PRIMARY KEY (code), + CONSTRAINT activation_keys_userid + FOREIGN KEY (userid) REFERENCES users(userid) +); + + -- base table for games +CREATE TABLE games ( + gameid SERIAL, + title VARCHAR(140) NOT NULL, + gametypeid SERIAL NOT NULL, -- future proofing: suppose different game types + players SMALLINT NOT NULL, -- number of players in a game + waitfor INTEGER NOT NULL, -- time in seconds to wait for player to complete a turn + lastturn DATE DEFAULT GETDATE(), + gamestate INTEGER DEFAULT 1, + PRIMARY KEY (gameid) +); + + -- associative table: many users, many games +CREATE TABLE allegiances ( + gameid SERIAL, + userid SERIAL, + allegiance VARCHAR(140), -- important/unimportant role for user (depends on gametype) + ordernum SMALLINT NOT NULL, -- user # spot in game + playing SMALLINT DEFAULT 1, -- default is binary: 0 means player OOP + PRIMARY KEY (gameid, userid), + CONSTRAINT users_games_gameid + FOREIGN KEY (gameid) REFERENCES games(gameid), + CONSTRAINT users_games_userid + FOREIGN KEY (userid) REFERENCES users(userid) +); + + -- Regions are composed of nationstates +CREATE TABLE regions ( + regionid SERIAL, + name VARCHAR(64) NOT NULL, + abbreviation CHAR(2), + bonus INTEGER NOT NULL, + PRIMARY KEY (regionid) +); + + -- base table for nationstates +CREATE TABLE nationstates ( + nationid SERIAL, + regionid SERIAL, + name VARCHAR(64) NOT NULL, + abbreviation CHAR(4), + PRIMARY KEY (nationid), + CONSTRAINT nationstates_regionid + FOREIGN KEY (regionid) REFERENCES regions(regionid) +); + + -- nationstates border nationstates +CREATE TABLE borders ( + nationid SERIAL, + borderid SERIAL, + PRIMARY KEY (nationid, borderid), + CONSTRAINT borders_nationid + FOREIGN KEY (nationid) REFERENCES nationstates(nationid), + CONSTRAINT borders_borderid + FOREIGN KEY (borderid) REFERENCES nationstates(nationid) +); + + -- associative table: many games, many nationstates. Users control them. +CREATE TABLE games_nationstates ( + gameid SERIAL, + nationid SERIAL, + userid SERIAL, -- owner of nationstate for particular game + PRIMARY KEY (gameid, nationid), + CONSTRAINT games_nationstates_gameid + FOREIGN KEY (gameid) REFERENCES games(gameid), + CONSTRAINT games_nationstates_nationid + FOREIGN KEY (nationid) REFERENCES nationstates(nationid), + CONSTRAINT games_nationstates_userid + FOREIGN KEY (userid) REFERENCES users(userid) +); |