0fb74564bee402f0407d6e6af1c73ed75047be65
[vchess.git] / server / db / create.sql
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 deduce 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 rtime integer, --remaining time in milliseconds
63 foreign key (gid) references Games(id),
64 foreign key (uid) references Users(id)
65 );
66
67 create table Moves (
68 gid integer,
69 move varchar,
70 message varchar,
71 played datetime, --when was this move played?
72 idx integer, --index of the move in the game
73 color character, --required for e.g. Marseillais Chess
74 foreign key (gid) references Games(id)
75 );
76
77 pragma foreign_keys = on;