1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
|
-- fresh.sql: SQL script that creates the tables used by AO
-- please drop all tables in db before initiating unless told otherwise
-- uncomment the following two lines to automatically drop all tables
--DROP SCHEMA "public" CASCADE;
--CREATE SCHEMA "public";
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 through PHP
games INTEGER DEFAULT 0, -- simple tracking statistics
wins INTEGER DEFAULT 0, -- "
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)
);
|
