| 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 | noProblems boolean |
| 8 | ); |
| 9 | |
| 10 | create table Users ( |
| 11 | id integer primary key, |
| 12 | name varchar unique, |
| 13 | email varchar unique, |
| 14 | loginToken varchar, |
| 15 | loginTime datetime, |
| 16 | sessionToken varchar, |
| 17 | created datetime, |
| 18 | notify boolean, |
| 19 | newsRead datetime |
| 20 | ); |
| 21 | |
| 22 | create table Problems ( |
| 23 | id integer primary key, |
| 24 | added datetime, |
| 25 | fen varchar, |
| 26 | uid integer, |
| 27 | vid integer, |
| 28 | instruction text, |
| 29 | solution text, |
| 30 | foreign key (uid) references Users(id), |
| 31 | foreign key (vid) references Variants(id) |
| 32 | ); |
| 33 | |
| 34 | create table News ( |
| 35 | id integer primary key, |
| 36 | uid integer, |
| 37 | added datetime, |
| 38 | content text, |
| 39 | foreign key (uid) references Users(id) |
| 40 | ); |
| 41 | |
| 42 | create table Challenges ( |
| 43 | id integer primary key, |
| 44 | added datetime, |
| 45 | uid integer, |
| 46 | target integer, |
| 47 | vid integer, |
| 48 | randomness integer, |
| 49 | fen varchar, |
| 50 | cadence varchar, |
| 51 | foreign key (uid) references Users(id), |
| 52 | foreign key (vid) references Variants(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 | white integer, |
| 61 | black integer, |
| 62 | score varchar default '*', |
| 63 | scoreMsg varchar, |
| 64 | cadence varchar, |
| 65 | randomness integer, --for rematch |
| 66 | created datetime, |
| 67 | drawOffer character default '', |
| 68 | rematchOffer character default '', |
| 69 | deletedByWhite boolean, |
| 70 | deletedByBlack boolean, |
| 71 | foreign key (vid) references Variants(id), |
| 72 | foreign key (white) references Users(id), |
| 73 | foreign key (black) references Users(id) |
| 74 | ); |
| 75 | |
| 76 | create table Chats ( |
| 77 | gid integer, |
| 78 | name varchar, |
| 79 | msg varchar, |
| 80 | added datetime |
| 81 | ); |
| 82 | |
| 83 | create table Moves ( |
| 84 | gid integer, |
| 85 | squares varchar, --description, appear/vanish/from/to |
| 86 | played datetime, --when was this move played? |
| 87 | idx integer, --index of the move in the game |
| 88 | foreign key (gid) references Games(id) |
| 89 | ); |
| 90 | |
| 91 | create index scoreIdx on Games(score); |
| 92 | |
| 93 | pragma foreign_keys = on; |