--- 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'),
- ('Marseille', 'Move twice'),
- ('Upsidedown', 'Walking on the hands'),
- ('Dark', 'In the shadow'),
- ('Berolina', 'Pawns move diagonally'),
- ('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;