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
  20     voters      TEXT DEFAULT NULL COMMENT "Filter users who can vote",
  21     viewers     TEXT DEFAULT NULL COMMENT "Filter users who can see the results",
  22
  23     flags       SET('validated', 'anonymous'),
  24
  25     PRIMARY KEY id (id),
  26     UNIQUE KEY shortname (shortname),
  27     FOREIGN KEY (uid) REFERENCES accounts (uid)
  28                       ON UPDATE CASCADE
  29                       ON DELETE CASCADE
  30 ) ENGINE=InnoDB, CHARSET=utf8, COMMENT="Describe a survey";
  31
  32 CREATE TABLE survey_questions (
  33     sid         INT(11) UNSIGNED NOT NULL,
  34     qid         INT(11) UNSIGNED NOT NULL,
  35     parent      INT(11) UNSIGNED DEFAULT NULL COMMENT "Id of the parent question",
  36
  37     type        VARCHAR(32) NOT NULL, -- XXX: Use an enum of possible types?
  38     label       TEXT DEFAULT NULL,
  39     parameters  TEXT DEFAULT NULL COMMENT "Parameters of the question",
  40     flags       SET('multiple', 'mandatory', 'noanswer') NOT NULL DEFAULT '',
  41
  42     PRIMARY KEY id (sid, qid),
  43     FOREIGN KEY (sid) REFERENCES surveys (id)
  44                       ON UPDATE CASCADE
  45                       ON DELETE CASCADE
  46 ) ENGINE=InnoDB, CHARSET=utf8, COMMENT="Describe the questions of the surveys";
  47
  48 CREATE TABLE survey_votes (
  49     vid         INT(11) UNSIGNED NOT NULL auto_increment,
  50     sid         INT(11) UNSIGNED NOT NULL,
  51
  52     PRIMARY KEY vid (vid),
  53     FOREIGN KEY (sid) REFERENCES surveys (id)
  54                       ON UPDATE CASCADE
  55                       ON DELETE CASCADE
  56 ) ENGINE=InnoDB, CHARSET=utf8, COMMENT="Identify unique votes";
  57
  58 CREATE TABLE survey_voters (
  59     sid         INT(11) UNSIGNED NOT NULL,
  60     uid         INT(11) UNSIGNED NOT NULL,
  61     vid         INT(11) UNSIGNED DEFAULT NULL, -- NULL for anonymous votes
  62
  63     PRIMARY KEY id (sid, uid),
  64     FOREIGN KEY (uid) REFERENCES accounts (uid)
  65                       ON UPDATE CASCADE
  66                       ON DELETE CASCADE,
  67     FOREIGN KEY (sid) REFERENCES surveys (id)
  68                       ON UPDATE CASCADE
  69                       ON DELETE CASCADE
  70 ) ENGINE=InnoDB, CHARSET=utf8, COMMENT="List voters";
  71
  72 CREATE TABLE survey_vote_answers (
  73     sid         INT(11) UNSIGNED NOT NULL,
  74     qid         INT(11) UNSIGNED NOT NULL,
  75     vid         INT(11) UNSIGNED NOT NULL,
  76
  77     answer      TEXT DEFAULT NULL,
  78
  79     PRIMARY KEY id (sid, vid, qid),
  80     FOREIGN KEY (vid) REFERENCES survey_votes (vid)
  81                       ON UPDATE CASCADE
  82                       ON DELETE CASCADE,
  83     FOREIGN KEY (sid, qid) REFERENCES survey_questions (sid, qid)
  84                            ON UPDATE CASCADE
  85                            ON DELETE CASCADE
  86 ) ENGINE=InnoDB, CHARSET=utf8, COMMENT="Answers to the surveys";
  87
  88 -- vim:set syntax=mysql: