Commit | Line | Data |
---|---|---|
c326e657 | 1 | -- Database should be in this folder, and named 'vchess.sqlite' |
86c9843c | 2 | |
da06a6eb | 3 | create table Variants ( |
bf20f404 BA |
4 | id integer primary key, |
5 | name varchar unique, | |
2c5d7b20 BA |
6 | description text, |
7 | noProblems boolean | |
da06a6eb | 8 | ); |
da06a6eb | 9 | |
8d7e2786 | 10 | create table Users ( |
bf20f404 BA |
11 | id integer primary key, |
12 | name varchar unique, | |
13 | email varchar unique, | |
14 | loginToken varchar, | |
15 | loginTime datetime, | |
16 | sessionToken varchar, | |
83494c7f | 17 | created datetime, |
d9a7a1e4 | 18 | notify boolean, |
dd10eb93 | 19 | bio text default '' |
8d7e2786 BA |
20 | ); |
21 | ||
89021f18 BA |
22 | create table Problems ( |
23 | id integer primary key, | |
24 | added datetime, | |
604b951e | 25 | fen varchar, |
89021f18 BA |
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 | ); | |
8d7e2786 BA |
33 | |
34 | create table Challenges ( | |
bf20f404 BA |
35 | id integer primary key, |
36 | added datetime, | |
37 | uid integer, | |
38 | target integer, | |
39 | vid integer, | |
7ba4a5bc | 40 | randomness integer, |
bf20f404 | 41 | fen varchar, |
71468011 | 42 | cadence varchar, |
bf20f404 BA |
43 | foreign key (uid) references Users(id), |
44 | foreign key (vid) references Variants(id) | |
8d7e2786 BA |
45 | ); |
46 | ||
47 | create table Games ( | |
bf20f404 BA |
48 | id integer primary key, |
49 | vid integer, | |
50 | fenStart varchar, --initial state | |
51 | fen varchar, --current state | |
0234201f BA |
52 | white integer, |
53 | black integer, | |
aae89b49 | 54 | score varchar default '*', |
dcd68c41 | 55 | scoreMsg varchar, |
71468011 | 56 | cadence varchar, |
afde7666 | 57 | randomness integer, --for rematch |
71468011 | 58 | created datetime, |
aae89b49 BA |
59 | drawOffer character default '', |
60 | rematchOffer character default '', | |
61 | deletedByWhite boolean, | |
62 | deletedByBlack boolean, | |
1ef65040 BA |
63 | chatReadWhite datetime, |
64 | chatReadBlack datetime, | |
0234201f BA |
65 | foreign key (vid) references Variants(id), |
66 | foreign key (white) references Users(id), | |
67 | foreign key (black) references Users(id) | |
8d7e2786 BA |
68 | ); |
69 | ||
63ca2b89 BA |
70 | create table Chats ( |
71 | gid integer, | |
c547fbfb BA |
72 | name varchar, |
73 | msg varchar, | |
3837d4f7 | 74 | added datetime |
63ca2b89 BA |
75 | ); |
76 | ||
8d7e2786 | 77 | create table Moves ( |
bf20f404 | 78 | gid integer, |
f41ce580 | 79 | squares varchar, --description, appear/vanish/from/to |
bf20f404 BA |
80 | played datetime, --when was this move played? |
81 | idx integer, --index of the move in the game | |
bf20f404 | 82 | foreign key (gid) references Games(id) |
da06a6eb | 83 | ); |
c326e657 | 84 | |
0234201f BA |
85 | create index scoreIdx on Games(score); |
86 | ||
8d7e2786 | 87 | pragma foreign_keys = on; |