Some small enhancements and 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
89021f18
BA
20create table Problems (
21 id integer primary key,
22 added datetime,
604b951e 23 fen varchar,
89021f18
BA
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);
8d7e2786 31
604b951e
BA
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
8d7e2786 40create table Challenges (
bf20f404
BA
41 id integer primary key,
42 added datetime,
43 uid integer,
44 target integer,
45 vid integer,
7ba4a5bc 46 randomness integer,
bf20f404 47 fen varchar,
71468011 48 cadence varchar,
bf20f404
BA
49 foreign key (uid) references Users(id),
50 foreign key (vid) references Variants(id)
8d7e2786
BA
51);
52
53create table Games (
bf20f404
BA
54 id integer primary key,
55 vid integer,
56 fenStart varchar, --initial state
57 fen varchar, --current state
aae89b49 58 score varchar default '*',
dcd68c41 59 scoreMsg varchar,
71468011 60 cadence varchar,
afde7666 61 randomness integer, --for rematch
71468011 62 created datetime,
aae89b49
BA
63 drawOffer character default '',
64 rematchOffer character default '',
65 deletedByWhite boolean,
66 deletedByBlack boolean,
bf20f404 67 foreign key (vid) references Variants(id)
8d7e2786
BA
68);
69
63ca2b89
BA
70create table Chats (
71 gid integer,
c547fbfb
BA
72 name varchar,
73 msg varchar,
3837d4f7 74 added datetime
63ca2b89
BA
75);
76
8d7e2786
BA
77-- Store informations about players in a corr game
78create table Players (
bf20f404
BA
79 gid integer,
80 uid integer,
81 color character,
bf20f404
BA
82 foreign key (gid) references Games(id),
83 foreign key (uid) references Users(id)
8d7e2786
BA
84);
85
86create table Moves (
bf20f404 87 gid integer,
f41ce580 88 squares varchar, --description, appear/vanish/from/to
bf20f404
BA
89 played datetime, --when was this move played?
90 idx integer, --index of the move in the game
bf20f404 91 foreign key (gid) references Games(id)
da06a6eb 92);
c326e657 93
8d7e2786 94pragma foreign_keys = on;