'update'
[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 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 timeControl varchar,
31 foreign key (uid) references Users(id),
32 foreign key (vid) references Variants(id)
33 );
34
35 -- NOTE: no need for a "created" field, it's deduced from first move playing time
36 create table Games (
37 id integer primary key,
38 vid integer,
39 fenStart varchar, --initial state
40 fen varchar, --current state
41 score varchar,
42 timeControl varchar,
43 created datetime, --used only for DB cleaning
44 drawOffer boolean,
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;