X-Git-Url: https://git.auder.net/?a=blobdiff_plain;f=db%2Fcreate.sql;h=936992ce3289d0130dc627ef2eb081bb2a7f0014;hb=b955c65b942d09d24b5c3bed0d755d4f2f8f71f1;hp=c28bd561ce369a937220586e352f9ed9aa16c3bc;hpb=c326e657d451c90220edbaa6f09fcb991350524c;p=vchess.git

diff --git a/db/create.sql b/db/create.sql
index c28bd561..936992ce 100644
--- a/db/create.sql
+++ b/db/create.sql
@@ -1,18 +1,86 @@
 -- Database should be in this folder, and named 'vchess.sqlite'
 
 create table Variants (
-	name varchar primary key,
+	id integer primary key,
+	name varchar unique,
 	description text
 );
 
+create table Users (
+	id integer primary key,
+	name varchar unique,
+	email varchar unique,
+	loginToken varchar,
+	loginTime datetime,
+	sessionToken varchar,
+	notify boolean
+);
+
 create table Problems (
-	num integer primary key,
+	id integer primary key,
 	added datetime,
-	variant varchar,
+	uid integer,
+	vid integer,
 	fen varchar,
 	instructions text,
 	solution text,
-	foreign key (variant) references Variants(name)
+	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)
 );
 
-PRAGMA foreign_keys = ON;
+pragma foreign_keys = on;