From a45da31abc09b5199d2aaefc038b8c59e0c50975 Mon Sep 17 00:00:00 2001 From: Vincent Ambo Date: Tue, 1 May 2018 20:23:51 +0200 Subject: refactor(migrations): Rename 'author' column to 'user_id' This makes it easier to integrate with Diesel, for which this is the expected column name. --- migrations/2018-05-01-141548_add-users/down.sql | 8 ++++---- migrations/2018-05-01-141548_add-users/up.sql | 24 ++++++++++++------------ 2 files changed, 16 insertions(+), 16 deletions(-) (limited to 'migrations') diff --git a/migrations/2018-05-01-141548_add-users/down.sql b/migrations/2018-05-01-141548_add-users/down.sql index 2f903171d4ba..61fd222e18cf 100644 --- a/migrations/2018-05-01-141548_add-users/down.sql +++ b/migrations/2018-05-01-141548_add-users/down.sql @@ -8,12 +8,12 @@ ALTER TABLE posts ADD COLUMN author_email VARCHAR; UPDATE threads SET author_name = users.name, author_email = users.email FROM users - WHERE threads.author = users.id; + WHERE threads.user_id = users.id; UPDATE posts SET author_name = users.name, author_email = users.email FROM users - WHERE posts.author = users.id; + WHERE posts.user_id = users.id; -- add the constraints back: ALTER TABLE threads ALTER COLUMN author_name SET NOT NULL; @@ -58,6 +58,6 @@ CREATE MATERIALIZED VIEW search_index AS CREATE INDEX idx_fts_search ON search_index USING gin(document); -- and drop the users table and columns: -ALTER TABLE posts DROP COLUMN author; -ALTER TABLE threads DROP COLUMN author; +ALTER TABLE posts DROP COLUMN user_id; +ALTER TABLE threads DROP COLUMN user_id; 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 index 5fadeeb23a00..fcb7133e8e41 100644 --- a/migrations/2018-05-01-141548_add-users/up.sql +++ b/migrations/2018-05-01-141548_add-users/up.sql @@ -20,22 +20,22 @@ INSERT INTO users (id, email, name) WHERE author_email != 'anonymous@nothing.org' GROUP BY name, email; --- Create the 'author' column in the relevant tables (initially +-- Create the 'user_id' 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 +ALTER TABLE posts ADD COLUMN user_id INTEGER REFERENCES users (id); +UPDATE posts SET user_id = 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 +ALTER TABLE threads ADD COLUMN user_id INTEGER REFERENCES users (id); +UPDATE threads SET user_id = 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; +ALTER TABLE posts ALTER COLUMN user_id SET NOT NULL; +ALTER TABLE threads ALTER COLUMN user_id SET NOT NULL; -- Update the index view: CREATE OR REPLACE VIEW thread_index AS @@ -49,12 +49,12 @@ CREATE OR REPLACE VIEW thread_index AS p.posted AS posted FROM threads t JOIN (SELECT DISTINCT ON (thread_id) - id, thread_id, author, posted + id, thread_id, user_id, 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 + JOIN users ta ON ta.id = t.user_id + JOIN users pa ON pa.id = p.user_id ORDER BY t.sticky DESC, p.id DESC; -- Update the search view: @@ -71,8 +71,8 @@ CREATE MATERIALIZED VIEW search_index AS 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; + JOIN users ta ON ta.id = t.user_id + JOIN users pa ON pa.id = p.user_id; CREATE INDEX idx_fts_search ON search_index USING gin(document); -- cgit 1.4.1