| Index: trunk/extensions/Contest/Contest.sql |
| — | — | @@ -6,8 +6,8 @@ |
| 7 | 7 | CREATE TABLE IF NOT EXISTS /*_*/contests ( |
| 8 | 8 | contest_id SMALLINT unsigned NOT NULL auto_increment PRIMARY KEY, |
| 9 | 9 | contest_name VARCHAR(255) NOT NULL, -- String indentifier for the contest |
| 10 | | - contest_status TINYINT unsigned NOT NULL default '0', -- Status of the contest |
| 11 | | - contest_end varbinary(14) NOT NULL default '', -- End time of the contest |
| | 10 | + contest_status TINYINT unsigned NOT NULL default '0', -- Status of the contest. One of 0 (DRAFT), 1 (ACTIVE), 2 (FINISHED) |
| | 11 | + contest_end varbinary(14) NOT NULL default '', -- End time of the contest (MW timestamp) |
| 12 | 12 | |
| 13 | 13 | contest_rules_page VARCHAR(255) NOT NULL, -- Name of the page with rules |
| 14 | 14 | contest_opportunities VARCHAR(255) NOT NULL, -- Name of the page with opportunities |
| — | — | @@ -16,10 +16,10 @@ |
| 17 | 17 | contest_signup_email VARCHAR(255) NOT NULL, -- Name of the page with the signup email text |
| 18 | 18 | contest_reminder_email VARCHAR(255) NOT NULL, -- Name of the page with the reminder email text |
| 19 | 19 | |
| 20 | | - contest_submission_count SMALLINT unsigned NOT NULL-- Amount of submissions made to the contest |
| | 20 | + contest_submission_count SMALLINT unsigned NOT NULL -- Number of submissions made to the contest |
| 21 | 21 | ) /*$wgDBTableOptions*/; |
| 22 | | -CREATE UNIQUE INDEX /*i*/contest_name ON /*_*/contests (contest_name); |
| 23 | | -CREATE INDEX /*i*/contest_status ON /*_*/contests (contest_status, contest_end); |
| | 22 | +CREATE UNIQUE INDEX /*i*/contests_name ON /*_*/contests (contest_name); |
| | 23 | +CREATE INDEX /*i*/contests_status_end ON /*_*/contests (contest_status, contest_end); |
| 24 | 24 | |
| 25 | 25 | -- Contestants |
| 26 | 26 | CREATE TABLE IF NOT EXISTS /*_*/contest_contestants ( |
| — | — | @@ -35,19 +35,20 @@ |
| 36 | 36 | |
| 37 | 37 | -- Extra contestant info |
| 38 | 38 | contestant_country VARCHAR(255) NOT NULL, -- Country code of the contestant |
| 39 | | - contestant_volunteer TINYINT unsigned NOT NULL, -- If the user is interested in voluneer oportunities |
| | 39 | + contestant_volunteer TINYINT unsigned NOT NULL, -- If the user is interested in volunteer opportunities |
| 40 | 40 | contestant_wmf TINYINT unsigned NOT NULL, -- If the user is interested in a WMF job |
| 41 | 41 | contestant_cv TINYBLOB NOT NULL, -- URL to the users CV |
| 42 | 42 | |
| 43 | 43 | contestant_submission TINYBLOB NOT NULL, -- URL to the users submission |
| 44 | 44 | |
| 45 | | - contestant_rating TINYINT unsigned NOT NULL, -- The avarage rating of the contestant |
| 46 | | - contestant_rating_count SMALLINT unsigned NOT NULL, -- The amount of ratings |
| 47 | | - contestant_comments SMALLINT unsigned NOT NULL -- The amount of comments |
| | 45 | + contestant_rating TINYINT unsigned NOT NULL, -- The average rating of the contestant |
| | 46 | + contestant_rating_count SMALLINT unsigned NOT NULL, -- The number of ratings |
| | 47 | + contestant_comments SMALLINT unsigned NOT NULL -- The number of comments |
| 48 | 48 | ) /*$wgDBTableOptions*/; |
| 49 | | -CREATE INDEX /*i*/contestant_interests ON /*_*/contest_contestants (contestant_challenge_id, contestant_wmf, contestant_volunteer); |
| 50 | | -CREATE INDEX /*i*/contestant_rating ON /*_*/contest_contestants (contestant_challenge_id, contestant_rating, contestant_rating_count); |
| 51 | | -CREATE UNIQUE INDEX /*i*/contestant_user_contest ON /*_*/contest_contestants (contestant_contest_id, contestant_user_id); |
| | 49 | +-- TODO: probably need to split indexes, see queries in ContestantPager |
| | 50 | +CREATE INDEX /*i*/contest_contestants_interests ON /*_*/contest_contestants (contestant_challenge_id, contestant_wmf, contestant_volunteer); |
| | 51 | +CREATE INDEX /*i*/contest_contestants_rating ON /*_*/contest_contestants (contestant_challenge_id, contestant_rating, contestant_rating_count); |
| | 52 | +CREATE UNIQUE INDEX /*i*/contest_contestants_id_user ON /*_*/contest_contestants (contestant_contest_id, contestant_user_id); |
| 52 | 53 | |
| 53 | 54 | -- Challenges |
| 54 | 55 | CREATE TABLE IF NOT EXISTS /*_*/contest_challenges ( |
| — | — | @@ -58,8 +59,8 @@ |
| 59 | 60 | challenge_title VARCHAR(255) NOT NULL, -- Title of the challenge |
| 60 | 61 | challenge_oneline TEXT NOT NULL -- One line description of the challenge |
| 61 | 62 | ) /*$wgDBTableOptions*/; |
| 62 | | -CREATE INDEX /*i*/challenge_contest_id ON /*_*/contest_challenges (challenge_contest_id); |
| 63 | | -CREATE UNIQUE INDEX /*i*/challenge_title ON /*_*/contest_challenges (challenge_title); |
| | 63 | +CREATE INDEX /*i*/contest_challenges_contest_id ON /*_*/contest_challenges (challenge_contest_id); |
| | 64 | +CREATE UNIQUE INDEX /*i*/contest_challenges_title ON /*_*/contest_challenges (challenge_title); |
| 64 | 65 | |
| 65 | 66 | -- Judge votes |
| 66 | 67 | CREATE TABLE IF NOT EXISTS /*_*/contest_votes ( |
| — | — | @@ -69,9 +70,8 @@ |
| 70 | 71 | |
| 71 | 72 | vote_value SMALLINT NOT NULL -- The value of the vote |
| 72 | 73 | ) /*$wgDBTableOptions*/; |
| 73 | | -CREATE INDEX /*i*/vote_contestant_id ON /*_*/contest_votes (vote_contestant_id); |
| 74 | | -CREATE UNIQUE INDEX /*i*/vote_contestant_user ON /*_*/contest_votes (vote_contestant_id, vote_user_id); |
| 75 | | -CREATE INDEX /*i*/vote_user_id ON /*_*/contest_votes (vote_user_id); |
| | 74 | +CREATE UNIQUE INDEX /*i*/contest_votes_contestant_user ON /*_*/contest_votes (vote_contestant_id, vote_user_id); |
| | 75 | +CREATE INDEX /*i*/contest_votes_user ON /*_*/contest_votes (vote_user_id); |
| 76 | 76 | |
| 77 | 77 | -- Judge comments |
| 78 | 78 | CREATE TABLE IF NOT EXISTS /*_*/contest_comments ( |
| — | — | @@ -82,4 +82,4 @@ |
| 83 | 83 | comment_text TEXT NOT NULL, -- The comment text |
| 84 | 84 | comment_time varbinary(14) NOT NULL default '' -- The time at which the comment was made |
| 85 | 85 | ) /*$wgDBTableOptions*/; |
| 86 | | -CREATE INDEX /*i*/comment_time ON /*_*/contest_comments (comment_contestant_id, comment_time); |
| \ No newline at end of file |
| | 86 | +CREATE INDEX /*i*/contest_comments_id_time ON /*_*/contest_comments (comment_contestant_id, comment_time); |