Commit | Line | Data |
---|---|---|
2f4b93be FB |
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, | |
2f4b93be FB |
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 | ||
90343d1e | 23 | flags SET('validated', 'anonymous'), |
2f4b93be FB |
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 ( | |
90343d1e | 49 | vid INT(11) UNSIGNED NOT NULL auto_increment, |
2f4b93be | 50 | sid INT(11) UNSIGNED NOT NULL, |
2f4b93be | 51 | |
90343d1e | 52 | PRIMARY KEY vid (vid), |
2f4b93be FB |
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, | |
2f4b93be | 74 | qid INT(11) UNSIGNED NOT NULL, |
90343d1e | 75 | vid INT(11) UNSIGNED NOT NULL, |
2f4b93be FB |
76 | |
77 | answer TEXT DEFAULT NULL, | |
78 | ||
79 | PRIMARY KEY id (sid, vid, qid), | |
90343d1e FB |
80 | FOREIGN KEY (vid) REFERENCES survey_votes (vid) |
81 | ON UPDATE CASCADE | |
82 | ON DELETE CASCADE, | |
2f4b93be FB |
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: |