| 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 | created datetime, |
| 17 | notify boolean |
| 18 | ); |
| 19 | |
| 20 | -- All the following tables are for correspondance play only |
| 21 | -- (Live games are stored in browser) |
| 22 | |
| 23 | create table Challenges ( |
| 24 | id integer primary key, |
| 25 | added datetime, |
| 26 | uid integer, |
| 27 | target integer, |
| 28 | vid integer, |
| 29 | fen varchar, |
| 30 | cadence varchar, |
| 31 | foreign key (uid) references Users(id), |
| 32 | foreign key (vid) references Variants(id) |
| 33 | ); |
| 34 | |
| 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 | scoreMsg varchar, |
| 42 | cadence varchar, |
| 43 | created datetime, |
| 44 | drawOffer character, |
| 45 | foreign key (vid) references Variants(id) |
| 46 | ); |
| 47 | |
| 48 | create table Chats ( |
| 49 | gid integer, |
| 50 | name varchar, |
| 51 | msg varchar, |
| 52 | added datetime |
| 53 | ); |
| 54 | |
| 55 | -- Store informations about players in a corr game |
| 56 | create table Players ( |
| 57 | gid integer, |
| 58 | uid integer, |
| 59 | color character, |
| 60 | foreign key (gid) references Games(id), |
| 61 | foreign key (uid) references Users(id) |
| 62 | ); |
| 63 | |
| 64 | create table Moves ( |
| 65 | gid integer, |
| 66 | squares varchar, --description, appear/vanish/from/to |
| 67 | played datetime, --when was this move played? |
| 68 | idx integer, --index of the move in the game |
| 69 | foreign key (gid) references Games(id) |
| 70 | ); |
| 71 | |
| 72 | pragma foreign_keys = on; |