blob: b139b9b2b16fb79716f7d3b3a05cb460c5126ad4 [file] [log] [blame]
-- Generic entity ID allocation
CREATE TABLE /*_*/securepoll_entity (
-- ID
en_id int not null primary key auto_increment,
-- "election", "question" or "option"
en_type varbinary(32) not null
) /*$wgDBTableOptions*/;
-- i18n text associated with an entity
CREATE TABLE /*_*/securepoll_msgs (
-- securepoll_entity.en_id
msg_entity int not null,
-- Language code
msg_lang varbinary(32) not null,
-- Message key
msg_key varbinary(32) not null,
-- Message text, UTF-8 encoded
msg_text mediumtext not null
) /*$wgDBTableOptions*/;
CREATE UNIQUE INDEX /*i*/spmsg_entity ON /*_*/securepoll_msgs (msg_entity, msg_lang, msg_key);
-- key/value pairs (properties) associated with an entity
CREATE TABLE /*_*/securepoll_properties (
-- securepoll_entity.en_id
pr_entity int not null,
-- Property key
pr_key varbinary(32) not null,
-- Property value
pr_value mediumblob not null
) /*$wgDBTableOptions*/;
CREATE UNIQUE INDEX /*i*/sppr_entity ON /*_*/securepoll_properties (pr_entity, pr_key);
-- List of elections (or polls, surveys, etc)
CREATE TABLE /*_*/securepoll_elections (
-- securepoll_entity.en_id
el_entity int not null primary key,
-- Election title
-- Only used for the election list on the entry page
el_title varchar(255) not null,
-- Owner user.user_id
el_owner int not null,
-- Ballot type, see Ballot.php
el_ballot varchar(32) not null,
-- Tally type, see Tally.php
el_tally varchar(32) not null,
-- Primary (administrative) language
-- This is the primary source for translations
el_primary_lang varbinary(32) not null,
-- Start date, in 14-char MW format
el_start_date varbinary(14),
-- End date, in 14-char MW format
el_end_date varbinary(14),
-- User authorization type, see Auth.php
el_auth_type varbinary(32) not null
) /*$wgDBTableOptions*/;
CREATE UNIQUE INDEX /*i*/spel_title ON /*_*/securepoll_elections (el_title);
-- Questions, see Question.php
CREATE TABLE /*_*/securepoll_questions (
-- securepoll_entity.en_id
qu_entity int not null primary key,
-- securepoll_elections.el_entity
qu_election int not null,
-- Index determining the order the questions are shown, if shuffle is off
qu_index int not null
) /*$wgDBTableOptions*/;
CREATE INDEX /*i*/spqu_election_index ON /*_*/securepoll_questions (qu_election, qu_index, qu_entity);
-- Options for answering a given question, see Option.php
-- FIXME: needs op_index column for determining the order if shuffle is off
CREATE TABLE /*_*/securepoll_options (
-- securepoll_entity.en_id
op_entity int not null primary key,
-- securepoll_elections.el_entity
op_election int not null,
-- securepoll_questions.qu_entity
op_question int not null
) /*$wgDBTableOptions*/;
CREATE INDEX /*i*/spop_question ON /*_*/securepoll_options (op_question, op_entity);
CREATE INDEX /*i*/spop_election ON /*_*/securepoll_options (op_election);
-- Voter list, independent for each election
-- See Voter.php
CREATE TABLE /*_*/securepoll_voters (
-- Primary key
voter_id int not null primary key auto_increment,
-- securepoll_elections.el_id
voter_election int not null,
-- The voter's name, as it appears on the remote site
voter_name varchar(255) binary not null,
-- The auth type that created this voter
voter_type varbinary(32) not null,
-- The voter's domain, should be fully-qualified
voter_domain varbinary(255) not null,
-- A URL uniquely identifying the voter
voter_url blob,
-- serialized properties blob
voter_properties blob
) /*$wgDBTableOptions*/;
CREATE INDEX /*i*/spvoter_elec_name_domain ON /*_*/securepoll_voters
(voter_election, voter_name, voter_domain);
-- Votes that have been cast
-- Contains a blob with answers to all questions
CREATE TABLE /*_*/securepoll_votes (
vote_id int not null primary key auto_increment,
-- securepoll_elections.el_id
vote_election int not null,
-- securepoll_voters.voter_id
vote_voter int not null,
-- Denormalized fields from the user table for efficient sorting
-- securepoll_voters.voter_name
vote_voter_name varchar(255) binary not null,
-- securepoll_voters.voter_domain
vote_voter_domain varbinary(32) not null,
-- Denormalized field from the strike table
-- 1 if struck, 0 if not struck
vote_struck tinyint not null,
-- The voting record, produced and interpreted by the ballot type
-- May be encrypted
vote_record blob not null,
-- The IP address, in hexadecimal form (IP::toHex())
vote_ip varbinary(35) not null,
-- The X-Forwarded-For header
vote_xff varbinary(255) not null,
-- The User-Agent header
vote_ua varbinary(255) not null,
-- MW-format timestamp when the vote was cast
vote_timestamp varbinary(14) not null,
-- 1 if the vote is current, 0 if old
-- Only one vote with a given voter will have vote_current=1
vote_current tinyint not null,
-- 1 if the CSRF token matched (good), 0 for a potential hack
vote_token_match tinyint not null,
-- 1 if the vote is flagged as being made by a potential sockpuppet
-- Details in securepoll_cookie_match
vote_cookie_dup tinyint not null
) /*$wgDBTableOptions*/;
-- For list subpage, sorted by timestamp
CREATE INDEX /*i*/spvote_timestamp ON /*_*/securepoll_votes
(vote_election, vote_timestamp);
-- For list subpage, sorted by name
CREATE INDEX /*i*/spvote_voter_name ON /*_*/securepoll_votes
(vote_election, vote_voter_name, vote_timestamp);
-- For list subpage, sorted by domain
CREATE INDEX /*i*/spvote_voter_domain ON /*_*/securepoll_votes
(vote_election, vote_voter_domain, vote_timestamp);
-- For list subpage, sorted by IP
CREATE INDEX /*i*/spvote_ip ON /*_*/securepoll_votes
(vote_election, vote_ip, vote_timestamp);
-- Log of admin strike actions
CREATE TABLE /*_*/securepoll_strike (
-- Primary key
st_id int not null primary key auto_increment,
-- securepoll_votes.vote_id
st_vote int not null,
-- Time at which the action occurred
st_timestamp varbinary(14) not null,
-- "strike" or "unstrike"
st_action varbinary(32) not null,
-- Explanatory reason
st_reason varchar(255) not null,
-- user.user_id who did the action
st_user int not null
) /*$wgDBTableOptions*/;
-- For details subpage (strike log)
CREATE INDEX /*i*/spstrike_vote ON /*_*/securepoll_strike
(st_vote, st_timestamp);
-- Local voter qualification lists
-- Currently manually populated, referenced by Auth.php
CREATE TABLE /*_*/securepoll_lists (
-- List name
li_name varbinary(255),
-- user.user_id
li_member int not null
) /*$wgDBTableOptions*/;
CREATE INDEX /*i*/splists_name ON /*_*/securepoll_lists
(li_name, li_member);
CREATE INDEX /*i*/splists_member ON /*_*/securepoll_lists
(li_member, li_name);
-- Suspicious cookie match logs
CREATE TABLE /*_*/securepoll_cookie_match (
-- Primary key
cm_id int not null primary key auto_increment,
-- securepoll_elections.el_id
cm_election int not null,
-- securepoll_voters.voter_id
cm_voter_1 int not null,
-- securepoll_voters.voter_id
cm_voter_2 int not null,
-- Timestamp at which the match was logged
cm_timestamp varbinary(14) not null
) /*$wgDBTableOptions*/;
CREATE INDEX /*i*/spcookie_match_voter_1 ON /*_*/securepoll_cookie_match
(cm_voter_1, cm_timestamp);
CREATE INDEX /*i*/spcookie_match_voter_2 ON /*_*/securepoll_cookie_match
(cm_voter_2, cm_timestamp);