summaryrefslogtreecommitdiff
path: root/boardvoting/migrations/20170421143114_add_constraints.sql
blob: 2fc969e13c83c8044e1f637c39bc354ef331e4a6 (plain)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
-- +migrate Up
-- SQL in section 'Up' is executed when this migration is applied
CREATE TABLE voters_new (
  id       INTEGER PRIMARY KEY,
  name     VARCHAR(255) NOT NULL,
  enabled  BOOLEAN      NOT NULL,
  reminder VARCHAR(255)
);
INSERT INTO voters_new (id, name, enabled, reminder)
  SELECT
    id,
    name,
    enabled,
    reminder
  FROM voters;
DROP TABLE voters;
ALTER TABLE voters_new
  RENAME TO voters;

CREATE TABLE emails_new (
  voter   INTEGER             NOT NULL REFERENCES voters (id),
  address VARCHAR(255) UNIQUE NOT NULL
);
INSERT INTO emails_new (voter, address)
  SELECT
    voter,
    address
  FROM emails;
DROP TABLE emails;
ALTER TABLE emails_new
  RENAME TO emails;

CREATE TABLE decisions_new (
  id        INTEGER PRIMARY KEY,
  proposed  DATETIME            NOT NULL,
  proponent INTEGER             NOT NULL REFERENCES voters (id),
  title     VARCHAR(255)        NOT NULL,
  content   TEXT                NOT NULL,
  status    INTEGER             NOT NULL CHECK (status IN (-2, -1, 0, 1)),
  due       DATETIME            NOT NULL,
  modified  DATETIME            NOT NULL,
  tag       VARCHAR(255) UNIQUE NOT NULL,
  votetype  INTEGER DEFAULT 0 NOT NULL CHECK (votetype IN (0, 1))
);
INSERT INTO decisions_new (
  id, proposed, proponent, title, content, status, due, modified, tag, votetype
)
  SELECT
    id,
    proposed,
    proponent,
    title,
    content,
    status,
    due,
    modified,
    tag,
    votetype
  FROM decisions;
DROP TABLE decisions;
ALTER TABLE decisions_new
  RENAME TO decisions;
CREATE INDEX decisions_proposed_idx
  ON decisions (proposed);

CREATE TABLE votes_new (
  decision INTEGER REFERENCES decisions (id),
  voter    INTEGER REFERENCES voters (id),
  vote     INTEGER  NOT NULL CHECK (vote IN (-1, 0, 1)),
  voted    DATETIME NOT NULL,
  notes    TEXT     NOT NULL DEFAULT '',
  PRIMARY KEY (decision, voter)
);
INSERT INTO votes_new (decision, voter, vote, voted, notes)
  SELECT
    decision,
    voter,
    vote,
    voted,
    notes
  FROM votes;
DROP TABLE votes;
ALTER TABLE votes_new
  RENAME TO votes;

-- +goose Down
-- SQL section 'Down' is executed when this migration is rolled back

CREATE TABLE votes_orig (
  decision INT4,
  voter    INT4,
  vote     INT4,
  voted    DATETIME,
  notes    TEXT DEFAULT ''
);
INSERT INTO votes_orig (decision, voter, vote, voted, notes)
  SELECT
    decision,
    voter,
    vote,
    voted,
    notes
  FROM votes;
DROP TABLE votes;
ALTER TABLE votes_orig
  RENAME TO votes;

CREATE TABLE decisions_orig (
  id        INTEGER PRIMARY KEY,
  proposed  DATETIME,
  proponent INTEGER,
  title     VARCHAR(255),
  content   TEXT,
  quorum    INTEGER,
  majority  INTEGER,
  status    INTEGER,
  due       DATETIME,
  modified  DATETIME,
  tag       VARCHAR(255),
  votetype  INT4 DEFAULT 0 NOT NULL
);
INSERT INTO decisions_orig (id, proposed, proponent, title, content, status, due, modified, tag, votetype)
  SELECT
    id,
    proposed,
    proponent,
    title,
    content,
    status,
    due,
    modified,
    tag,
    votetype
  FROM
    decisions;
DROP INDEX decisions_proposed_idx;
DROP TABLE decisions;
ALTER TABLE decisions_orig
  RENAME TO decisions;

CREATE TABLE emails_orig (
  voter   INT4,
  address VARCHAR(255)
);
INSERT INTO emails_orig (voter, address)
  SELECT
    voter,
    address
  FROM emails;
DROP TABLE emails;
ALTER TABLE emails_orig
  RENAME TO emails;

CREATE TABLE voters_orig (
  id       INTEGER PRIMARY KEY,
  name     VARCHAR(255),
  enabled  INTEGER DEFAULT 0,
  reminder VARCHAR(255)
);
INSERT INTO voters_orig (id, name, enabled, reminder)
  SELECT
    id,
    name,
    enabled,
    reminder
  FROM voters;
DROP TABLE voters;
ALTER TABLE voters_orig
  RENAME TO voters;