| 1 | -- Database should be in this folder, and named 'vchess.sqlite' |
| 2 | |
| 3 | create table Variants ( |
| 4 | id integer primary key, |
| 5 | name varchar unique, |
| 6 | description text |
| 7 | ); |
| 8 | |
| 9 | create table Users ( |
| 10 | id integer primary key, |
| 11 | name varchar unique, |
| 12 | email varchar unique, |
| 13 | loginToken varchar, |
| 14 | loginTime datetime, |
| 15 | sessionToken varchar, |
| 16 | notify boolean |
| 17 | ); |
| 18 | |
| 19 | create table Problems ( |
| 20 | id integer primary key, |
| 21 | added datetime, |
| 22 | uid integer, |
| 23 | vid integer, |
| 24 | fen varchar, |
| 25 | instructions text, |
| 26 | solution text, |
| 27 | foreign key (uid) references Users(id), |
| 28 | foreign key (vid) references Variants(id) |
| 29 | ); |
| 30 | |
| 31 | -- All the following tables are for correspondance play only |
| 32 | -- (Live games are stored only in browsers) |
| 33 | |
| 34 | create table Challenges ( |
| 35 | id integer primary key, |
| 36 | added datetime, |
| 37 | uid integer, |
| 38 | vid integer, |
| 39 | nbPlayers integer, |
| 40 | fen varchar, |
| 41 | timeControl varchar, |
| 42 | foreign key (uid) references Users(id), |
| 43 | foreign key (vid) references Variants(id) |
| 44 | ); |
| 45 | |
| 46 | -- Store informations about players who (potentially) accept a challenge |
| 47 | create table WillPlay ( |
| 48 | yes boolean, |
| 49 | cid integer, |
| 50 | uid integer, |
| 51 | foreign key (cid) references Challenges(id), |
| 52 | foreign key (uid) references Users(id) |
| 53 | ); |
| 54 | |
| 55 | create table Games ( |
| 56 | id integer primary key, |
| 57 | vid integer, |
| 58 | fenStart varchar, --initial state |
| 59 | fen varchar, --current state |
| 60 | score varchar, |
| 61 | mainTime integer, |
| 62 | addTime integer, |
| 63 | foreign key (vid) references Variants(id) |
| 64 | ); |
| 65 | |
| 66 | -- Store informations about players in a corr game |
| 67 | create table Players ( |
| 68 | gid integer, |
| 69 | uid integer, |
| 70 | color character, |
| 71 | rtime integer, --remaining time in milliseconds |
| 72 | foreign key (gid) references Games(id), |
| 73 | foreign key (uid) references Users(id) |
| 74 | ); |
| 75 | |
| 76 | create table Moves ( |
| 77 | gid integer, |
| 78 | move varchar, |
| 79 | message varchar, |
| 80 | played datetime, --when was this move played? |
| 81 | idx integer, --index of the move in the game |
| 82 | color character, --required for e.g. Marseillais Chess |
| 83 | foreign key (gid) references Games(id) |
| 84 | ); |
| 85 | |
| 86 | pragma foreign_keys = on; |