diff options
Diffstat (limited to 'migrations')
4 files changed, 23 insertions, 16 deletions
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; |