| 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 in browser) |
| 33 | |
| 34 | create table Challenges ( |
| 35 | id integer primary key, |
| 36 | added datetime, |
| 37 | uid integer, |
| 38 | target integer, |
| 39 | vid integer, |
| 40 | fen varchar, |
| 41 | timeControl varchar, |
| 42 | foreign key (uid) references Users(id), |
| 43 | foreign key (vid) references Variants(id) |
| 44 | ); |
| 45 | |
| 46 | -- NOTE: no need for a "created" field, it's deduced from first move playing time |
| 47 | create table Games ( |
| 48 | id integer primary key, |
| 49 | vid integer, |
| 50 | fenStart varchar, --initial state |
| 51 | fen varchar, --current state |
| 52 | score varchar, |
| 53 | timeControl varchar, |
| 54 | foreign key (vid) references Variants(id) |
| 55 | ); |
| 56 | |
| 57 | -- Store informations about players in a corr game |
| 58 | create table Players ( |
| 59 | gid integer, |
| 60 | uid integer, |
| 61 | color character, |
| 62 | foreign key (gid) references Games(id), |
| 63 | foreign key (uid) references Users(id) |
| 64 | ); |
| 65 | |
| 66 | create table Moves ( |
| 67 | gid integer, |
| 68 | squares varchar, --description, appear/vanish/from/to |
| 69 | message varchar, |
| 70 | played datetime, --when was this move played? |
| 71 | idx integer, --index of the move in the game |
| 72 | foreign key (gid) references Games(id) |
| 73 | ); |
| 74 | |
| 75 | pragma foreign_keys = on; |