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