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 +++++++++++++++++++++ src/models.rs | 8 ++++++-- src/render.rs | 2 +- src/schema.rs | 8 ++++++-- 7 files changed, 36 insertions(+), 21 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 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; diff --git a/src/models.rs b/src/models.rs index 159a85469aeb..9d3405e1540f 100644 --- a/src/models.rs +++ b/src/models.rs @@ -24,6 +24,7 @@ pub struct Thread { pub posted: DateTime, pub author_name: String, pub author_email: String, + pub sticky: bool, } /// This struct is used as the query type for the thread index view, @@ -33,9 +34,12 @@ pub struct Thread { pub struct ThreadIndex { pub thread_id: i32, pub title: String, - pub author_name: String, - pub posted: DateTime, + pub thread_author: String, + pub created: DateTime, + pub sticky: bool, pub post_id: i32, + pub post_author: String, + pub posted: DateTime, } #[derive(Identifiable, Queryable, Serialize, Associations)] diff --git a/src/render.rs b/src/render.rs index 66b3fe19c867..598d48bce893 100644 --- a/src/render.rs +++ b/src/render.rs @@ -72,7 +72,7 @@ impl Handler for Renderer { id: thread.thread_id, title: escape_html(&thread.title), posted: thread.posted.into(), - author_name: thread.author_name, + author_name: thread.thread_author, }) .collect(); diff --git a/src/schema.rs b/src/schema.rs index 02392c8fc60c..50a55c786825 100644 --- a/src/schema.rs +++ b/src/schema.rs @@ -16,6 +16,7 @@ table! { posted -> Timestamptz, author_name -> Varchar, author_email -> Varchar, + sticky -> Bool, } } @@ -24,9 +25,12 @@ table! { thread_index (thread_id){ thread_id -> Integer, title -> Text, - author_name -> Text, - posted -> Timestamptz, + thread_author -> Text, + created -> Timestamptz, + sticky -> Bool, post_id -> Integer, + post_author -> Text, + posted -> Timestamptz, } } -- cgit 1.4.1