| 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 | -- All the following tables are for correspondance play only |
| 20 | -- (Live games are stored in browser) |
| 21 | |
| 22 | create table Challenges ( |
| 23 | id integer primary key, |
| 24 | added datetime, |
| 25 | uid integer, |
| 26 | target integer, |
| 27 | vid integer, |
| 28 | fen varchar, |
| 29 | timeControl varchar, |
| 30 | foreign key (uid) references Users(id), |
| 31 | foreign key (vid) references Variants(id) |
| 32 | ); |
| 33 | |
| 34 | -- NOTE: no need for a "created" field, it's deduced from first move playing time |
| 35 | create table Games ( |
| 36 | id integer primary key, |
| 37 | vid integer, |
| 38 | fenStart varchar, --initial state |
| 39 | fen varchar, --current state |
| 40 | score varchar, |
| 41 | timeControl varchar, |
| 42 | created datetime, --used only for DB cleaning |
| 43 | foreign key (vid) references Variants(id) |
| 44 | ); |
| 45 | |
| 46 | -- Store informations about players in a corr game |
| 47 | create table Players ( |
| 48 | gid integer, |
| 49 | uid integer, |
| 50 | color character, |
| 51 | foreign key (gid) references Games(id), |
| 52 | foreign key (uid) references Users(id) |
| 53 | ); |
| 54 | |
| 55 | create table Moves ( |
| 56 | gid integer, |
| 57 | squares varchar, --description, appear/vanish/from/to |
| 58 | message varchar, |
| 59 | played datetime, --when was this move played? |
| 60 | idx integer, --index of the move in the game |
| 61 | foreign key (gid) references Games(id) |
| 62 | ); |
| 63 | |
| 64 | pragma foreign_keys = on; |