Refactor endgame process, start working on game end (abort)
[vchess.git] / server / db / create.sql
CommitLineData
c326e657 1-- Database should be in this folder, and named 'vchess.sqlite'
86c9843c 2
da06a6eb 3create table Variants (
8d7e2786
BA
4 id integer primary key,
5 name varchar unique,
da06a6eb
BA
6 description text
7);
da06a6eb 8
8d7e2786
BA
9create table Users (
10 id integer primary key,
11 name varchar unique,
12 email varchar unique,
13 loginToken varchar,
14 loginTime datetime,
15 sessionToken varchar,
16 notify boolean
17);
18
da06a6eb 19create table Problems (
8d7e2786 20 id integer primary key,
da06a6eb 21 added datetime,
8d7e2786
BA
22 uid integer,
23 vid integer,
da06a6eb
BA
24 fen varchar,
25 instructions text,
26 solution text,
8d7e2786
BA
27 foreign key (uid) references Users(id),
28 foreign key (vid) references Variants(id)
29);
30
31-- All the following tables are for correspondance play only
32-- (Live games are stored only in browsers)
33
34create table Challenges (
35 id integer primary key,
36 added datetime,
37 uid integer,
38 vid integer,
00f2759e 39 nbPlayers integer,
ab4f4bf2 40 fen varchar,
052d17ea 41 timeControl varchar,
8d7e2786
BA
42 foreign key (uid) references Users(id),
43 foreign key (vid) references Variants(id)
44);
45
052d17ea 46-- Store informations about players who (potentially) accept a challenge
8d7e2786 47create table WillPlay (
052d17ea
BA
48 yes boolean,
49 cid integer,
8d7e2786 50 uid integer,
8d7e2786
BA
51 foreign key (cid) references Challenges(id),
52 foreign key (uid) references Users(id)
53);
54
55create table Games (
56 id integer primary key,
57 vid integer,
ab4f4bf2
BA
58 fenStart varchar, --initial state
59 fen varchar, --current state
60 score varchar,
61 mainTime integer,
62 addTime integer,
8d7e2786
BA
63 foreign key (vid) references Variants(id)
64);
65
66-- Store informations about players in a corr game
67create table Players (
fd08ab2c 68 gid integer,
8d7e2786
BA
69 uid integer,
70 color character,
ab4f4bf2 71 rtime integer, --remaining time in milliseconds
fd08ab2c
BA
72 foreign key (gid) references Games(id),
73 foreign key (uid) references Users(id)
8d7e2786
BA
74);
75
76create table Moves (
77 gid integer,
78 move varchar,
ab4f4bf2 79 message varchar,
8d7e2786
BA
80 played datetime, --when was this move played?
81 idx integer, --index of the move in the game
82 color character, --required for e.g. Marseillais Chess
83 foreign key (gid) references Games(id)
da06a6eb 84);
c326e657 85
8d7e2786 86pragma foreign_keys = on;