X-Git-Url: https://git.auder.net/?p=vchess.git;a=blobdiff_plain;f=db%2Fcreate.sql;h=e94b84a3edce38bd821ad1eac2cd3d4e41b8a32c;hp=c28bd561ce369a937220586e352f9ed9aa16c3bc;hb=8d7e2786f5a67a1b9a77c742d7951e0efbe8747d;hpb=7192f4711467ae73a0f813189f8a4d8cca252bf1

diff --git a/db/create.sql b/db/create.sql
index c28bd561..e94b84a3 100644
--- a/db/create.sql
+++ b/db/create.sql
@@ -1,18 +1,78 @@
 -- 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,
+	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,
+	yes boolean,
+	foreign key (cid) references Challenges(id),
+	foreign key (uid) references Users(id)
+);
+
+create table Games (
+	id integer primary key,
+	vid integer,
+	fen varchar, --initial position
+	score varchar,
+	foreign key (vid) references Variants(id)
+);
+
+-- Store informations about players in a corr game
+create table Players (
+	uid integer,
+	color character,
+	gid integer,
+	foreign key (uid) references Users(id),
+	foreign key (gid) references Games(id)
+);
+
+create table Moves (
+	gid integer,
+	move 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;