diff options
| -rw-r--r-- | migrations/2018-05-01-141548_add-users/down.sql | 61 | ||||
| -rw-r--r-- | migrations/2018-05-01-141548_add-users/up.sql | 75 |
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; |