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

diff --git a/db/create.sql b/db/create.sql
index 033df4be..e94b84a3 100644
--- a/db/create.sql
+++ b/db/create.sql
@@ -1,32 +1,78 @@
--- database should be in this folder, and named 'vchess.sqlite'
+-- 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
 );
-insert into Variants values
-	('Checkered', 'Shared pieces'),
-	('Zen', 'Reverse captures'),
-	('Atomic', 'Explosive captures'),
-	('Chess960', 'Standard rules'),
-	('Antiking', 'Keep antiking in check'),
-	('Magnetic', 'Laws of attraction'),
-	('Alice', 'Both sides of the mirror'),
-	('Grand', 'Big board'),
-	('Wildebeest', 'Balanced sliders & leapers'),
-	('Loser', 'Lose all pieces'),
-	('Crazyhouse', 'Captures reborn'),
-	('Switching', 'Exchange pieces positions'),
-	('Extinction', 'Capture all of a kind'),
-	('Ultima', 'Exotic captures');
+
+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)
 );
---PRAGMA foreign_keys = ON;
+
+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;