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).

1 files changed, 61 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 000000000000..a29ee47dd641
--- /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;