summaryrefslogtreecommitdiff
path: root/db/migrations/20170421143114_add_constraints.sql
diff options
context:
space:
mode:
Diffstat (limited to 'db/migrations/20170421143114_add_constraints.sql')
-rw-r--r--db/migrations/20170421143114_add_constraints.sql169
1 files changed, 169 insertions, 0 deletions
diff --git a/db/migrations/20170421143114_add_constraints.sql b/db/migrations/20170421143114_add_constraints.sql
new file mode 100644
index 0000000..c1ddfbe
--- /dev/null
+++ b/db/migrations/20170421143114_add_constraints.sql
@@ -0,0 +1,169 @@
+-- +goose 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; \ No newline at end of file