]>
jfr.im git - erebus.git/blob - modules/contrib/trivia/trivia.sql
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,
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
19 DROP TABLE IF EXISTS trivia_channels
;
20 CREATE TABLE trivia_channels (
21 channel
varchar(100) not null,
22 isteam bool
default 0,
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,
37 DROP TABLE IF EXISTS trivia_scores
;
38 CREATE TABLE trivia_scores (
39 gid
int unsigned
not null,
40 pid
int unsigned
not null,
42 primary key (gid
, pid
)
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,
51 auth
varchar(30) not null,
52 wins
int unsigned
not null,
53 podiums
int unsigned
not null,
54 highstreak
int unsigned
not null,
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,
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
)