Attempt to clarify installation instructions a little
[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 display varchar,
7 groupe integer,
8 description text,
9 noProblems boolean
10);
11
12create table Users (
13 id integer primary key,
14 name varchar unique,
15 email varchar unique,
16 loginToken varchar,
17 loginTime datetime,
18 sessionToken varchar,
19 created datetime,
20 notify boolean,
21 bio text default ''
22);
23
24create table Problems (
25 id integer primary key,
26 added datetime,
27 fen varchar,
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);
35
36create table Challenges (
37 id integer primary key,
38 added datetime,
39 uid integer,
40 target integer,
41 vid integer,
42 options varchar,
43 fen varchar,
44 cadence varchar,
45 foreign key (uid) references Users(id),
46 foreign key (vid) references Variants(id)
47);
48
49create table GameStat (
50 vid integer,
51 total integer default 0,
52 foreign key (vid) references Variants(id)
53);
54
55create table Games (
56 id integer primary key,
57 vid integer,
58 fenStart varchar, --initial state
59 fen varchar, --current state
60 white integer,
61 black integer,
62 score varchar default '*',
63 scoreMsg varchar,
64 cadence varchar,
65 options varchar, --for rematch
66 created datetime,
67 drawOffer character default '',
68 rematchOffer character default '',
69 deletedByWhite boolean,
70 deletedByBlack boolean,
71 chatReadWhite datetime,
72 chatReadBlack datetime,
73 foreign key (vid) references Variants(id),
74 foreign key (white) references Users(id),
75 foreign key (black) references Users(id)
76);
77
78create table Chats (
79 gid integer,
80 name varchar,
81 msg varchar,
82 added datetime
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
93create index scoreIdx on Games(score);
94
95pragma foreign_keys = on;