Add goose database migrations
[cacert-boardvoting.git] / db / migrations / 20170421143114_add_constraints.sql
1 -- +goose Up
2 -- SQL in section 'Up' is executed when this migration is applied
3 CREATE TABLE voters_new (
4 id INTEGER PRIMARY KEY,
5 name VARCHAR(255) NOT NULL,
6 enabled BOOLEAN NOT NULL,
7 reminder VARCHAR(255)
8 );
9 INSERT INTO voters_new (id, name, enabled, reminder)
10 SELECT
11 id,
12 name,
13 enabled,
14 reminder
15 FROM voters;
16 DROP TABLE voters;
17 ALTER TABLE voters_new
18 RENAME TO voters;
19
20 CREATE TABLE emails_new (
21 voter INTEGER NOT NULL REFERENCES voters (id),
22 address VARCHAR(255) UNIQUE NOT NULL
23 );
24 INSERT INTO emails_new (voter, address)
25 SELECT
26 voter,
27 address
28 FROM emails;
29 DROP TABLE emails;
30 ALTER TABLE emails_new
31 RENAME TO emails;
32
33 CREATE TABLE decisions_new (
34 id INTEGER PRIMARY KEY,
35 proposed DATETIME NOT NULL,
36 proponent INTEGER NOT NULL REFERENCES voters (id),
37 title VARCHAR(255) NOT NULL,
38 content TEXT NOT NULL,
39 status INTEGER NOT NULL CHECK (status IN (-2, -1, 0, 1)),
40 due DATETIME NOT NULL,
41 modified DATETIME NOT NULL,
42 tag VARCHAR(255) UNIQUE NOT NULL,
43 votetype INTEGER DEFAULT 0 NOT NULL CHECK (votetype IN (0, 1))
44 );
45 INSERT INTO decisions_new (
46 id, proposed, proponent, title, content, status, due, modified, tag, votetype
47 )
48 SELECT
49 id,
50 proposed,
51 proponent,
52 title,
53 content,
54 status,
55 due,
56 modified,
57 tag,
58 votetype
59 FROM decisions;
60 DROP TABLE decisions;
61 ALTER TABLE decisions_new
62 RENAME TO decisions;
63 CREATE INDEX decisions_proposed_idx
64 ON decisions (proposed);
65
66 CREATE TABLE votes_new (
67 decision INTEGER REFERENCES decisions (id),
68 voter INTEGER REFERENCES voters (id),
69 vote INTEGER NOT NULL CHECK (vote IN (-1, 0, 1)),
70 voted DATETIME NOT NULL,
71 notes TEXT NOT NULL DEFAULT '',
72 PRIMARY KEY (decision, voter)
73 );
74 INSERT INTO votes_new (decision, voter, vote, voted, notes)
75 SELECT
76 decision,
77 voter,
78 vote,
79 voted,
80 notes
81 FROM votes;
82 DROP TABLE votes;
83 ALTER TABLE votes_new
84 RENAME TO votes;
85
86 -- +goose Down
87 -- SQL section 'Down' is executed when this migration is rolled back
88
89 CREATE TABLE votes_orig (
90 decision INT4,
91 voter INT4,
92 vote INT4,
93 voted DATETIME,
94 notes TEXT DEFAULT ''
95 );
96 INSERT INTO votes_orig (decision, voter, vote, voted, notes)
97 SELECT
98 decision,
99 voter,
100 vote,
101 voted,
102 notes
103 FROM votes;
104 DROP TABLE votes;
105 ALTER TABLE votes_orig
106 RENAME TO votes;
107
108 CREATE TABLE decisions_orig (
109 id INTEGER PRIMARY KEY,
110 proposed DATETIME,
111 proponent INTEGER,
112 title VARCHAR(255),
113 content TEXT,
114 quorum INTEGER,
115 majority INTEGER,
116 status INTEGER,
117 due DATETIME,
118 modified DATETIME,
119 tag VARCHAR(255),
120 votetype INT4 DEFAULT 0 NOT NULL
121 );
122 INSERT INTO decisions_orig (id, proposed, proponent, title, content, status, due, modified, tag, votetype)
123 SELECT
124 id,
125 proposed,
126 proponent,
127 title,
128 content,
129 status,
130 due,
131 modified,
132 tag,
133 votetype
134 FROM
135 decisions;
136 DROP INDEX decisions_proposed_idx;
137 DROP TABLE decisions;
138 ALTER TABLE decisions_orig
139 RENAME TO decisions;
140
141 CREATE TABLE emails_orig (
142 voter INT4,
143 address VARCHAR(255)
144 );
145 INSERT INTO emails_orig (voter, address)
146 SELECT
147 voter,
148 address
149 FROM emails;
150 DROP TABLE emails;
151 ALTER TABLE emails_orig
152 RENAME TO emails;
153
154 CREATE TABLE voters_orig (
155 id INTEGER PRIMARY KEY,
156 name VARCHAR(255),
157 enabled INTEGER DEFAULT 0,
158 reminder VARCHAR(255)
159 );
160 INSERT INTO voters_orig (id, name, enabled, reminder)
161 SELECT
162 id,
163 name,
164 enabled,
165 reminder
166 FROM voters;
167 DROP TABLE voters;
168 ALTER TABLE voters_orig
169 RENAME TO voters;