Save current state (unmerged, broken, not working...)

[vchess.git] / db / create.sql

diff --git a/db/create.sql b/db/create.sql
index 033df4b..e94b84a 100644 (file)
--- 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;