+ foreign key (uid) references Users(id),
+ foreign key (vid) references Variants(id)
+);
+
+-- All the following tables are for correspondance play only
+-- (Live games are stored only in browsers)
+
+create table Challenges (
+ id integer primary key,
+ added datetime,
+ uid integer,
+ vid integer,
+ nbPlayers integer,
+ fen varchar,
+ mainTime integer,
+ addTime integer,
+ foreign key (uid) references Users(id),
+ foreign key (vid) references Variants(id)
+);
+
+-- Store informations about players who accept a challenge
+create table WillPlay (
+ cid integer,
+ uid integer,
+ foreign key (cid) references Challenges(id),
+ foreign key (uid) references Users(id)
+);
+
+create table Games (
+ id integer primary key,
+ vid integer,
+ fenStart varchar, --initial state
+ fen varchar, --current state
+ score varchar,
+ mainTime integer,
+ addTime integer,
+ foreign key (vid) references Variants(id)
+);
+
+-- Store informations about players in a corr game
+create table Players (
+ gid integer,
+ uid integer,
+ color character,
+ rtime integer, --remaining time in milliseconds
+ foreign key (gid) references Games(id),
+ foreign key (uid) references Users(id)
+);
+
+create table Moves (
+ gid integer,
+ move varchar,
+ message varchar,
+ played datetime, --when was this move played?
+ idx integer, --index of the move in the game
+ color character, --required for e.g. Marseillais Chess
+ foreign key (gid) references Games(id)