socket rooms correspnding to pages. TODO: Hall+Game (split live and corr? shared...
[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,
16 notify boolean
8d7e2786
BA
17);
18
da06a6eb 19create table Problems (
bf20f404
BA
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)
8d7e2786
BA
29);
30
31-- All the following tables are for correspondance play only
bebcc8d4 32-- (Live games are stored in browser)
8d7e2786
BA
33
34create table Challenges (
bf20f404
BA
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)
8d7e2786
BA
44);
45
92a523d1 46-- NOTE: no need for a "created" field, it's deduced from first move playing time
8d7e2786 47create table Games (
bf20f404
BA
48 id integer primary key,
49 vid integer,
50 fenStart varchar, --initial state
51 fen varchar, --current state
52 score varchar,
bebcc8d4 53 timeControl varchar,
bf20f404 54 foreign key (vid) references Variants(id)
8d7e2786
BA
55);
56
57-- Store informations about players in a corr game
58create table Players (
bf20f404
BA
59 gid integer,
60 uid integer,
61 color character,
bf20f404
BA
62 foreign key (gid) references Games(id),
63 foreign key (uid) references Users(id)
8d7e2786
BA
64);
65
66create table Moves (
bf20f404
BA
67 gid integer,
68 move varchar,
69 message varchar,
70 played datetime, --when was this move played?
71 idx integer, --index of the move in the game
72 color character, --required for e.g. Marseillais Chess
73 foreign key (gid) references Games(id)
da06a6eb 74);
c326e657 75
8d7e2786 76pragma foreign_keys = on;