Attempt to clarify installation instructions a little
[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,
eaa5ad3e
BA
6 display varchar,
7 groupe integer,
2c5d7b20
BA
8 description text,
9 noProblems boolean
da06a6eb 10);
da06a6eb 11
8d7e2786 12create table Users (
bf20f404
BA
13 id integer primary key,
14 name varchar unique,
15 email varchar unique,
16 loginToken varchar,
17 loginTime datetime,
18 sessionToken varchar,
83494c7f 19 created datetime,
d9a7a1e4 20 notify boolean,
dd10eb93 21 bio text default ''
8d7e2786
BA
22);
23
89021f18
BA
24create table Problems (
25 id integer primary key,
26 added datetime,
604b951e 27 fen varchar,
89021f18
BA
28 uid integer,
29 vid integer,
30 instruction text,
31 solution text,
32 foreign key (uid) references Users(id),
33 foreign key (vid) references Variants(id)
34);
8d7e2786
BA
35
36create table Challenges (
bf20f404
BA
37 id integer primary key,
38 added datetime,
39 uid integer,
40 target integer,
41 vid integer,
eb2d61de 42 options varchar,
bf20f404 43 fen varchar,
71468011 44 cadence varchar,
bf20f404
BA
45 foreign key (uid) references Users(id),
46 foreign key (vid) references Variants(id)
4a209313
BA
47);
48
49create table GameStat (
50 vid integer,
51 total integer default 0,
52 foreign key (vid) references Variants(id)
8d7e2786
BA
53);
54
55create table Games (
bf20f404
BA
56 id integer primary key,
57 vid integer,
58 fenStart varchar, --initial state
59 fen varchar, --current state
0234201f
BA
60 white integer,
61 black integer,
aae89b49 62 score varchar default '*',
dcd68c41 63 scoreMsg varchar,
71468011 64 cadence varchar,
eb2d61de 65 options varchar, --for rematch
71468011 66 created datetime,
aae89b49
BA
67 drawOffer character default '',
68 rematchOffer character default '',
69 deletedByWhite boolean,
70 deletedByBlack boolean,
1ef65040
BA
71 chatReadWhite datetime,
72 chatReadBlack datetime,
0234201f
BA
73 foreign key (vid) references Variants(id),
74 foreign key (white) references Users(id),
75 foreign key (black) references Users(id)
8d7e2786
BA
76);
77
63ca2b89
BA
78create table Chats (
79 gid integer,
c547fbfb
BA
80 name varchar,
81 msg varchar,
3837d4f7 82 added datetime
63ca2b89
BA
83);
84
8d7e2786 85create table Moves (
bf20f404 86 gid integer,
f41ce580 87 squares varchar, --description, appear/vanish/from/to
bf20f404
BA
88 played datetime, --when was this move played?
89 idx integer, --index of the move in the game
bf20f404 90 foreign key (gid) references Games(id)
da06a6eb 91);
c326e657 92
0234201f
BA
93create index scoreIdx on Games(score);
94
8d7e2786 95pragma foreign_keys = on;