Commit | Line | Data |
---|---|---|
c326e657 | 1 | -- Database should be in this folder, and named 'vchess.sqlite' |
86c9843c | 2 | |
da06a6eb | 3 | create table Variants ( |
8d7e2786 BA |
4 | id integer primary key, |
5 | name varchar unique, | |
da06a6eb BA |
6 | description text |
7 | ); | |
da06a6eb | 8 | |
8d7e2786 BA |
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 | ||
da06a6eb | 19 | create table Problems ( |
8d7e2786 | 20 | id integer primary key, |
da06a6eb | 21 | added datetime, |
8d7e2786 BA |
22 | uid integer, |
23 | vid integer, | |
da06a6eb BA |
24 | fen varchar, |
25 | instructions text, | |
26 | solution text, | |
8d7e2786 BA |
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 only in browsers) | |
33 | ||
34 | create table Challenges ( | |
35 | id integer primary key, | |
36 | added datetime, | |
37 | uid integer, | |
38 | vid integer, | |
00f2759e | 39 | nbPlayers integer, |
ab4f4bf2 | 40 | fen varchar, |
052d17ea | 41 | timeControl varchar, |
8d7e2786 BA |
42 | foreign key (uid) references Users(id), |
43 | foreign key (vid) references Variants(id) | |
44 | ); | |
45 | ||
052d17ea | 46 | -- Store informations about players who (potentially) accept a challenge |
8d7e2786 | 47 | create table WillPlay ( |
052d17ea BA |
48 | yes boolean, |
49 | cid integer, | |
8d7e2786 | 50 | uid integer, |
8d7e2786 BA |
51 | foreign key (cid) references Challenges(id), |
52 | foreign key (uid) references Users(id) | |
53 | ); | |
54 | ||
55 | create table Games ( | |
56 | id integer primary key, | |
57 | vid integer, | |
ab4f4bf2 BA |
58 | fenStart varchar, --initial state |
59 | fen varchar, --current state | |
60 | score varchar, | |
61 | mainTime integer, | |
62 | addTime integer, | |
8d7e2786 BA |
63 | foreign key (vid) references Variants(id) |
64 | ); | |
65 | ||
66 | -- Store informations about players in a corr game | |
67 | create table Players ( | |
fd08ab2c | 68 | gid integer, |
8d7e2786 BA |
69 | uid integer, |
70 | color character, | |
ab4f4bf2 | 71 | rtime integer, --remaining time in milliseconds |
fd08ab2c BA |
72 | foreign key (gid) references Games(id), |
73 | foreign key (uid) references Users(id) | |
8d7e2786 BA |
74 | ); |
75 | ||
76 | create table Moves ( | |
77 | gid integer, | |
78 | move varchar, | |
ab4f4bf2 | 79 | message varchar, |
8d7e2786 BA |
80 | played datetime, --when was this move played? |
81 | idx integer, --index of the move in the game | |
82 | color character, --required for e.g. Marseillais Chess | |
83 | foreign key (gid) references Games(id) | |
da06a6eb | 84 | ); |
c326e657 | 85 | |
8d7e2786 | 86 | pragma foreign_keys = on; |