From d1c45159b9bb23553768b06ce7ad97ec38e8b845 Mon Sep 17 00:00:00 2001 From: Vincent Ambo Date: Sat, 14 Apr 2018 17:47:31 +0200 Subject: feat(db): Add support for stickies in database Adds a 'sticky' column to threads and rewrites the thread index to take sticky markings into account when ordering threads. Stickies are not yet highlighted in any way in the forum overview. --- .../2018-04-14-145711_create_index_view/down.sql | 1 - .../2018-04-14-145711_create_index_view/up.sql | 15 --------------- .../down.sql | 2 ++ .../up.sql | 21 +++++++++++++++++++++ 4 files changed, 23 insertions(+), 16 deletions(-) delete mode 100644 migrations/2018-04-14-145711_create_index_view/down.sql delete mode 100644 migrations/2018-04-14-145711_create_index_view/up.sql create mode 100644 migrations/2018-04-14-153202_add_stickies_improve_index/down.sql create mode 100644 migrations/2018-04-14-153202_add_stickies_improve_index/up.sql (limited to 'migrations') diff --git a/migrations/2018-04-14-145711_create_index_view/down.sql b/migrations/2018-04-14-145711_create_index_view/down.sql deleted file mode 100644 index 87d26820f7e2..000000000000 --- a/migrations/2018-04-14-145711_create_index_view/down.sql +++ /dev/null @@ -1 +0,0 @@ -DROP VIEW thread_index; diff --git a/migrations/2018-04-14-145711_create_index_view/up.sql b/migrations/2018-04-14-145711_create_index_view/up.sql deleted file mode 100644 index 3b7ddc1d647a..000000000000 --- a/migrations/2018-04-14-145711_create_index_view/up.sql +++ /dev/null @@ -1,15 +0,0 @@ --- Create a simple view that returns the list of threads ordered by --- the last post that occured in the thread. - -CREATE VIEW thread_index AS - SELECT t.id AS thread_id, - t.title AS title, - t.author_name AS author_name, - t.posted AS posted, - p.id AS post_id - FROM threads t - JOIN (SELECT DISTINCT ON (thread_id) id, thread_id - FROM posts - ORDER BY thread_id, id DESC) AS p - ON t.id = p.thread_id - ORDER BY p.id DESC; diff --git a/migrations/2018-04-14-153202_add_stickies_improve_index/down.sql b/migrations/2018-04-14-153202_add_stickies_improve_index/down.sql new file mode 100644 index 000000000000..a67ada3d4a57 --- /dev/null +++ b/migrations/2018-04-14-153202_add_stickies_improve_index/down.sql @@ -0,0 +1,2 @@ +DROP VIEW thread_index; +ALTER TABLE threads DROP COLUMN sticky; diff --git a/migrations/2018-04-14-153202_add_stickies_improve_index/up.sql b/migrations/2018-04-14-153202_add_stickies_improve_index/up.sql new file mode 100644 index 000000000000..74a559e35d00 --- /dev/null +++ b/migrations/2018-04-14-153202_add_stickies_improve_index/up.sql @@ -0,0 +1,21 @@ +-- Add support for stickies in threads +ALTER TABLE threads ADD COLUMN sticky BOOLEAN NOT NULL DEFAULT FALSE; + +-- CREATE a simple view that returns the list of threads ordered by +-- the last post that occured in the thread. +CREATE 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; -- cgit 1.4.1