feat(migrations): Adds a migration to create a users table

As a first step of getting the concept of users into the database,
this creates a (somewhat involved) migration that moves all existing
"users" into a new users table and updates the post table to reference
it.

This migration is not yet finalised and still needs to be updated with
something to handle the concept of anonymous users (which I want to
keep around).

2 files changed, 136 insertions, 0 deletions

diff --git a/migrations/2018-05-01-141548_add-users/down.sql b/migrations/2018-05-01-141548_add-users/down.sql
new file mode 100644
index 0000000000..a29ee47dd6
--- /dev/null
+++ b/migrations/2018-05-01-141548_add-users/down.sql
@@ -0,0 +1,61 @@
+-- First restore the old columns:
+ALTER TABLE threads ADD COLUMN author_name VARCHAR;
+ALTER TABLE threads ADD COLUMN author_email VARCHAR;
+ALTER TABLE posts ADD COLUMN author_name VARCHAR;
+ALTER TABLE posts ADD COLUMN author_email VARCHAR;
+
+-- Then select the data back into them:
+UPDATE threads SET author_name = users.name,
+                   author_email = users.email
+  FROM users
+  WHERE threads.author = users.id;
+
+UPDATE posts SET author_name = users.name,
+                 author_email = users.email
+  FROM users
+  WHERE posts.author = users.id;
+
+-- add the constraints back:
+ALTER TABLE threads ALTER COLUMN author_name SET NOT NULL;
+ALTER TABLE threads ALTER COLUMN author_email SET NOT NULL;
+ALTER TABLE posts ALTER COLUMN author_name SET NOT NULL;
+ALTER TABLE posts ALTER COLUMN author_email SET NOT NULL;
+
+-- reset the index view:
+CREATE OR REPLACE VIEW thread_index AS
+  SELECT t.id AS thread_id,
+         t.title AS title,
+         t.author_name AS thread_author,
+         t.posted AS created,
+         t.sticky AS sticky,
+         p.id AS post_id,
+         p.author_name AS post_author,
+         p.posted AS posted
+    FROM threads t
+    JOIN (SELECT DISTINCT ON (thread_id)
+           id, thread_id, author_name, posted
+          FROM posts
+          ORDER BY thread_id, id DESC) AS p
+    ON t.id = p.thread_id
+    ORDER BY t.sticky DESC, p.id DESC;
+
+-- reset the search view:
+DROP MATERIALIZED VIEW search_index;
+CREATE MATERIALIZED VIEW search_index AS
+  SELECT p.id AS post_id,
+         p.author_name AS author,
+         t.id AS thread_id,
+         t.title AS title,
+         p.body AS body,
+         setweight(to_tsvector('english', t.title), 'B') ||
+         setweight(to_tsvector('english', p.body), 'A') ||
+         setweight(to_tsvector('simple', t.author_name), 'C') ||
+         setweight(to_tsvector('simple', p.author_name), 'C') AS document
+    FROM posts p
+    JOIN threads t
+    ON t.id = p.thread_id;
+
+-- and drop the users table and columns:
+ALTER TABLE posts DROP COLUMN author;
+ALTER TABLE threads DROP COLUMN author;
+DROP TABLE users;
diff --git a/migrations/2018-05-01-141548_add-users/up.sql b/migrations/2018-05-01-141548_add-users/up.sql
new file mode 100644
index 0000000000..bb358f47b5
--- /dev/null
+++ b/migrations/2018-05-01-141548_add-users/up.sql
@@ -0,0 +1,75 @@
+-- This query creates a users table and migrates the existing user
+-- information (from the posts table) into it.
+
+CREATE TABLE users (
+  id SERIAL PRIMARY KEY,
+  email VARCHAR NOT NULL UNIQUE,
+  name VARCHAR NOT NULL,
+  admin BOOLEAN NOT NULL DEFAULT false
+);
+
+INSERT INTO users (email, name)
+SELECT author_email AS email,
+       author_name AS name
+FROM posts
+GROUP BY name, email;
+
+-- Create the 'author' column in the relevant tables (initially
+-- without a not-null constraint) and populate it with the data
+-- selected above:
+ALTER TABLE posts ADD COLUMN author INTEGER REFERENCES users (id);
+UPDATE posts SET author = users.id
+  FROM users
+  WHERE users.email = posts.author_email;
+
+ALTER TABLE threads ADD COLUMN author INTEGER REFERENCES users (id);
+UPDATE threads SET author = users.id
+  FROM users
+  WHERE users.email = threads.author_email;
+
+-- Add the constraints:
+ALTER TABLE posts ALTER COLUMN author SET NOT NULL;
+ALTER TABLE threads ALTER COLUMN author SET NOT NULL;
+
+-- Update the index view:
+CREATE OR REPLACE VIEW thread_index AS
+  SELECT t.id AS thread_id,
+         t.title AS title,
+         ta.name AS thread_author,
+         t.posted AS created,
+         t.sticky AS sticky,
+         p.id AS post_id,
+         pa.name AS post_author,
+         p.posted AS posted
+    FROM threads t
+    JOIN (SELECT DISTINCT ON (thread_id)
+           id, thread_id, author, posted
+          FROM posts
+          ORDER BY thread_id, id DESC) AS p
+    ON t.id = p.thread_id
+    JOIN users ta ON ta.id = t.author
+    JOIN users pa ON pa.id = p.author
+    ORDER BY t.sticky DESC, p.id DESC;
+
+-- Update the search view:
+DROP MATERIALIZED VIEW search_index;
+CREATE MATERIALIZED VIEW search_index AS
+  SELECT p.id AS post_id,
+         pa.name AS author,
+         t.id AS thread_id,
+         t.title AS title,
+         p.body AS body,
+         setweight(to_tsvector('english', t.title), 'B') ||
+         setweight(to_tsvector('english', p.body), 'A') ||
+         setweight(to_tsvector('simple', ta.name), 'C') ||
+         setweight(to_tsvector('simple', pa.name), 'C') AS document
+    FROM posts p
+    JOIN threads t ON t.id = p.thread_id
+    JOIN users ta ON ta.id = t.author
+    JOIN users pa ON pa.id = p.author;
+
+-- And drop the old fields:
+ALTER TABLE posts DROP COLUMN author_name;
+ALTER TABLE posts DROP COLUMN author_email;
+ALTER TABLE threads DROP COLUMN author_name;
+ALTER TABLE threads DROP COLUMN author_email;