Fixes
[vchess.git] / server / db / create.sql
CommitLineData
c326e657 1-- Database should be in this folder, and named 'vchess.sqlite'
86c9843c 2
da06a6eb 3create table Variants (
bf20f404
BA
4 id integer primary key,
5 name varchar unique,
6 description text
da06a6eb 7);
da06a6eb 8
8d7e2786 9create table Users (
bf20f404
BA
10 id integer primary key,
11 name varchar unique,
12 email varchar unique,
13 loginToken varchar,
14 loginTime datetime,
15 sessionToken varchar,
83494c7f 16 created datetime,
bf20f404 17 notify boolean
8d7e2786
BA
18);
19
8d7e2786 20-- All the following tables are for correspondance play only
bebcc8d4 21-- (Live games are stored in browser)
8d7e2786
BA
22
23create table Challenges (
bf20f404
BA
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)
8d7e2786
BA
33);
34
92a523d1 35-- NOTE: no need for a "created" field, it's deduced from first move playing time
8d7e2786 36create table Games (
bf20f404
BA
37 id integer primary key,
38 vid integer,
39 fenStart varchar, --initial state
40 fen varchar, --current state
41 score varchar,
bebcc8d4 42 timeControl varchar,
d431028c 43 created datetime, --used only for DB cleaning
b7cbbda1 44 drawOffer boolean,
bf20f404 45 foreign key (vid) references Variants(id)
8d7e2786
BA
46);
47
63ca2b89
BA
48create table Chats (
49 gid integer,
c547fbfb 50 name varchar,
3837d4f7 51 sid varchar,
c547fbfb 52 msg varchar,
3837d4f7 53 added datetime
63ca2b89
BA
54);
55
8d7e2786
BA
56-- Store informations about players in a corr game
57create table Players (
bf20f404
BA
58 gid integer,
59 uid integer,
60 color character,
bf20f404
BA
61 foreign key (gid) references Games(id),
62 foreign key (uid) references Users(id)
8d7e2786
BA
63);
64
65create table Moves (
bf20f404 66 gid integer,
f41ce580 67 squares varchar, --description, appear/vanish/from/to
bf20f404
BA
68 played datetime, --when was this move played?
69 idx integer, --index of the move in the game
bf20f404 70 foreign key (gid) references Games(id)
da06a6eb 71);
c326e657 72
8d7e2786 73pragma foreign_keys = on;