Experimental update: preview corr move + allow deletion of any game
[vchess.git] / server / db / create.sql
... / ...
CommitLineData
1-- Database should be in this folder, and named 'vchess.sqlite'
2
3create table Variants (
4 id integer primary key,
5 name varchar unique,
6 description text
7);
8
9create 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
20create table Problems (
21 id integer primary key,
22 added datetime,
23 fen varchar,
24 uid integer,
25 vid integer,
26 instruction text,
27 solution text,
28 foreign key (uid) references Users(id),
29 foreign key (vid) references Variants(id)
30);
31
32create table News (
33 id integer primary key,
34 uid integer,
35 added datetime,
36 content text,
37 foreign key (uid) references Users(id)
38);
39
40create table Challenges (
41 id integer primary key,
42 added datetime,
43 uid integer,
44 target integer,
45 vid integer,
46 randomness integer,
47 fen varchar,
48 cadence varchar,
49 foreign key (uid) references Users(id),
50 foreign key (vid) references Variants(id)
51);
52
53create table Games (
54 id integer primary key,
55 vid integer,
56 fenStart varchar, --initial state
57 fen varchar, --current state
58 score varchar default '*',
59 scoreMsg varchar,
60 cadence varchar,
61 created datetime,
62 drawOffer character default '',
63 rematchOffer character default '',
64 deletedByWhite boolean,
65 deletedByBlack boolean,
66 foreign key (vid) references Variants(id)
67);
68
69create table Chats (
70 gid integer,
71 name varchar,
72 msg varchar,
73 added datetime
74);
75
76-- Store informations about players in a corr game
77create table Players (
78 gid integer,
79 uid integer,
80 color character,
81 foreign key (gid) references Games(id),
82 foreign key (uid) references Users(id)
83);
84
85create table Moves (
86 gid integer,
87 squares varchar, --description, appear/vanish/from/to
88 played datetime, --when was this move played?
89 idx integer, --index of the move in the game
90 foreign key (gid) references Games(id)
91);
92
93pragma foreign_keys = on;