upgrade/1.1.0/10_surveys.sql

   1 #RENAME TABLE surveys TO old_surveys,
   2 #             survey_answers TO old_survey_answers,
   3 #             survey_votes TO old_survey_votes;
   4
   5 DROP TABLE IF EXISTS survey_vote_answers;
   6 DROP TABLE IF EXISTS survey_voters;
   7 DROP TABLE IF EXISTS survey_votes;
   8 DROP TABLE IF EXISTS survey_questions;
   9 DROP TABLE IF EXISTS surveys;
  10
  11 CREATE TABLE surveys (
  12     id          INT(11) UNSIGNED NOT NULL auto_increment,
  13     uid         INT(11) UNSIGNED NOT NULL,
  14     shortname   VARCHAR(32) NOT NULL,
  15     title       VARCHAR(255) NOT NULL,
  16     description TEXT NOT NULL,
  17     begin       DATE NOT NULL,
  18     end         DATE NOT NULL,
  19     anonymous   TINYINT(1) DEFAULT 0,
  20
  21     voters      TEXT DEFAULT NULL COMMENT "Filter users who can vote",
  22     viewers     TEXT DEFAULT NULL COMMENT "Filter users who can see the results",
  23
  24     flags       SET('validated'),
  25
  26     PRIMARY KEY id (id),
  27     UNIQUE KEY shortname (shortname),
  28     FOREIGN KEY (uid) REFERENCES accounts (uid)
  29                       ON UPDATE CASCADE
  30                       ON DELETE CASCADE
  31 ) ENGINE=InnoDB, CHARSET=utf8, COMMENT="Describe a survey";
  32
  33 CREATE TABLE survey_questions (
  34     sid         INT(11) UNSIGNED NOT NULL,
  35     qid         INT(11) UNSIGNED NOT NULL,
  36     parent      INT(11) UNSIGNED DEFAULT NULL COMMENT "Id of the parent question",
  37
  38     type        VARCHAR(32) NOT NULL, -- XXX: Use an enum of possible types?
  39     label       TEXT DEFAULT NULL,
  40     parameters  TEXT DEFAULT NULL COMMENT "Parameters of the question",
  41     flags       SET('multiple', 'mandatory', 'noanswer') NOT NULL DEFAULT '',
  42
  43     PRIMARY KEY id (sid, qid),
  44     FOREIGN KEY (sid) REFERENCES surveys (id)
  45                       ON UPDATE CASCADE
  46                       ON DELETE CASCADE
  47 ) ENGINE=InnoDB, CHARSET=utf8, COMMENT="Describe the questions of the surveys";
  48
  49 CREATE TABLE survey_votes (
  50     sid         INT(11) UNSIGNED NOT NULL,
  51     vid         INT(11) UNSIGNED NOT NULL,
  52
  53     PRIMARY KEY id (sid, vid),
  54     FOREIGN KEY (sid) REFERENCES surveys (id)
  55                       ON UPDATE CASCADE
  56                       ON DELETE CASCADE
  57 ) ENGINE=InnoDB, CHARSET=utf8, COMMENT="Identify unique votes";
  58
  59 CREATE TABLE survey_voters (
  60     sid         INT(11) UNSIGNED NOT NULL,
  61     uid         INT(11) UNSIGNED NOT NULL,
  62     vid         INT(11) UNSIGNED DEFAULT NULL, -- NULL for anonymous votes
  63
  64     PRIMARY KEY id (sid, uid),
  65     FOREIGN KEY (uid) REFERENCES accounts (uid)
  66                       ON UPDATE CASCADE
  67                       ON DELETE CASCADE,
  68     FOREIGN KEY (sid) REFERENCES surveys (id)
  69                       ON UPDATE CASCADE
  70                       ON DELETE CASCADE
  71 ) ENGINE=InnoDB, CHARSET=utf8, COMMENT="List voters";
  72
  73 CREATE TABLE survey_vote_answers (
  74     sid         INT(11) UNSIGNED NOT NULL,
  75     vid         INT(11) UNSIGNED NOT NULL,
  76     qid         INT(11) UNSIGNED NOT NULL,
  77
  78     answer      TEXT DEFAULT NULL,
  79
  80     PRIMARY KEY id (sid, vid, qid),
  81     FOREIGN KEY (sid, qid) REFERENCES survey_questions (sid, qid)
  82                            ON UPDATE CASCADE
  83                            ON DELETE CASCADE
  84 ) ENGINE=InnoDB, CHARSET=utf8, COMMENT="Answers to the surveys";
  85
  86 -- vim:set syntax=mysql: