]>
Commit | Line | Data |
---|---|---|
3b51ce57 | 1 | DROP TABLE IF EXISTS trivia_questions; |
2 | CREATE TABLE trivia_questions ( | |
3 | qid int unsigned not null auto_increment, | |
4 | question varchar(300) not null, | |
5 | answer varchar(300) not null, | |
6 | deleted bool not null default 0, | |
7 | addtime timestamp not null default CURRENT_TIMESTAMP, | |
8 | modtime timestamp not null, | |
9 | timesasked int unsigned not null, | |
10 | asktime timestamp not null on update CURRENT_TIMESTAMP, | |
11 | author int unsigned not null, | |
12 | primary key (qid) | |
13 | ); | |
14 | -- count = SELECT COUNT(questions) FROM questions | |
15 | -- index = random (0,count] | |
16 | -- while row_is_empty: | |
17 | -- row = SELECT question, answer FROM questions WHERE qid = index AND deleted = 0 | |
18 | ||
19 | DROP TABLE IF EXISTS trivia_channels; | |
20 | CREATE TABLE trivia_channels ( | |
21 | channel varchar(100) not null, | |
22 | isteam bool default 0, | |
23 | primary key (channel) | |
24 | ); | |
25 | ||
26 | DROP TABLE IF EXISTS trivia_games; | |
27 | CREATE TABLE trivia_games ( | |
28 | gid int unsigned not null auto_increment, | |
29 | startdate timestamp not null default CURRENT_TIMESTAMP, | |
30 | enddate timestamp not null, | |
31 | channel int unsigned not null, | |
32 | maxscore int unsigned not null, | |
33 | active bool not null default 1, | |
34 | primary key (gid) | |
35 | ); | |
36 | ||
37 | DROP TABLE IF EXISTS trivia_scores; | |
38 | CREATE TABLE trivia_scores ( | |
39 | gid int unsigned not null, | |
40 | pid int unsigned not null, | |
41 | score int unsigned, | |
42 | primary key (gid, pid) | |
43 | ); | |
44 | ||
45 | DROP TABLE IF EXISTS trivia_players; | |
46 | CREATE TABLE trivia_players ( | |
47 | pid int unsigned not null auto_increment, | |
48 | nick varchar(30) not null, | |
49 | password char(70), | |
50 | salt varchar(10), | |
51 | auth varchar(30) not null, | |
52 | wins int unsigned not null, | |
53 | podiums int unsigned not null, | |
54 | highstreak int unsigned not null, | |
55 | primary key (pid), | |
56 | unique key (nick) | |
57 | ); | |
58 | ||
59 | DROP TABLE IF EXISTS trivia_teams; | |
60 | CREATE TABLE trivia_teams ( | |
61 | tid int unsigned not null auto_increment, | |
62 | teamname varchar(20) not null, | |
63 | channel int unsigned not null, | |
64 | primary key (tid) | |
65 | ); | |
66 | ||
67 | DROP TABLE IF EXISTS trivia_team_players; | |
68 | CREATE TABLE trivia_team_players ( | |
69 | tid int unsigned not null, | |
70 | pid int unsigned not null, | |
71 | captain bool not null, | |
72 | primary key (tid, pid) | |
73 | ); |