Ticket-To-Ride-Server/creations.sql at master · jmango22/Ticket-To-Ride-Server · GitHub

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
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
CREATE TABLE IF NOT EXISTS player (
    user_id SERIAL not null,
    username VARCHAR(20) UNIQUE NOT NULL,
    password VARCHAR(20) NOT NULL,
    access_token VARCHAR(20) UNIQUE,
    PRIMARY KEY(user_id)
);

CREATE TABLE IF NOT EXISTS participants (
    user_id INTEGER NOT NULL,
    game_id INTEGER NOT NULL,
    player_number INTEGER,
    points INTEGER,
    trains INTEGER,
    PRIMARY Key(user_id, game_id),
    FOREIGN KEY(user_id)
      REFERENCES player
      ON DELETE CASCADE,
    FOREIGN KEY(game_id)
      REFERENCES game
      ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS game (
    game_id SERIAL NOT NULL,
    name VARCHAR(20) NOT NULL,
    started BOOLEAN NOT NULL,
    PRIMARY KEY(game_id)
);

CREATE TABLE IF NOT EXISTS route (
    route_id SERIAL NOT NULL,
    city_1 UNIQUE INTEGER NOT NULL,
    city_2 UNIQUE INTEGER NOT NULL,
    route_color VARCHAR(10) NOT NULL,
    route_length INTEGER NOT NULL,
    PRIMARY KEY(route_id),
    FOREIGN KEY(city_1)
      REFERENCES city
      ON DELETE CASCADE,
    FOREIGN KEY(city_2)
      REFERENCES city
      ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS city (
    city_id SERIAL NOT NULL,
    city_name VARCHAR(20) UNIQUE,
    point_x DECIMAL NOT NULL,
    point_y DECIMAL NOT NULL
    PRIMARY KEY(city_id)
);

CREATE TABLE IF NOT EXISTS claimed_route (
    route_id INTEGER NOT NULL,
    game_id INTEGER NOT NULL,
    player_id INTEGER NOT NULL,
    PRIMARY KEY(route_id, game_id),
    FOREIGN KEY(route_id)
      REFERENCES route
      ON DELETE CASCADE,
    FOREIGN KEY(user_id)
      REFERENCES player
      ON DELETE CASCADE,
    FOREIGN KEY(game_id)
      REFERENCES game
      ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS train_card (
    train_card_id SERIAL NOT NULL,
    game_id INTEGER NOT NULL,
    player_id INTEGER,
    train_type VARCHAR(10),
    discarded BOOLEAN,
    PRIMARY KEY(train_card_id),
    FOREIGN KEY(player_id)
      REFERENCES player
      ON DELETE CASCADE,
    FOREIGN KEY(game_id)
      REFERENCES game
      ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS destination_card (
    destination_card_id SERIAL NOT NULL,
    game_id INTEGER NOT NULL,
    player_id INTEGER,
    city_1 INTEGER NOT NULL,
    city_2 INTEGER NOT NULL,
    discarded BOOLEAN,
    PRIMARY KEY(destination_card_id),
    FOREIGN KEY(player_id)
      REFERENCES player
      on delete CASCADE,
    FOREIGN KEY(game_id)
      REFERENCES game
      on delete CASCADE,
    FOREIGN KEY(city_1)
      REFERENCES city
      ON DELETE CASCADE,
    FOREIGN KEY(city_2)
      REFERENCES city
      ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS chat_message (
    message_id SERIAL NOT NULL,
    game_id INTEGER NOT NULL,
    player_id INTEGER NOT NULL,
    message VARCHAR(120) NOT NULL,
    PRIMARY KEY(message_id),
    FOREIGN KEY(game_id)
      REFERENCES game
      ON DELETE CASCADE,
    FOREIGN KEY(player_id)
      REFERENCES player
      ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS command (
    command_id SERIAL NOT NULL,
    game_id INTEGER NOT NULL,
    player_id INTEGER NOT NULL,
    metadata VARCHAR(200) NOT NULL,
    visible_to_self BOOLEAN NOT NULL,
    visible_to_all BOOLEAN NOT NULL,
    PRIMARY KEY(command_id),
    FOREIGN KEY(game_id)
      REFERENCES game
      ON DELETE CASCADE,
    FOREIGN KEY(player_id)
      REFERENCES player
      ON DELETE CASCADE
);