Skip to main content

max / makenotwork

7.3 KB · 205 lines History Blame Raw
1 use super::{DateTime, PgPool, Utc, Uuid};
2
3 #[derive(sqlx::FromRow)]
4 pub struct PostWithAuthor {
5 pub id: Uuid,
6 pub author_id: Uuid,
7 pub author_name: String,
8 pub author_username: String,
9 pub body_html: String,
10 pub created_at: DateTime<Utc>,
11 pub edited_at: Option<DateTime<Utc>>,
12 pub deleted_at: Option<DateTime<Utc>>,
13 pub removed_at: Option<DateTime<Utc>>,
14 /// Author's current Fan+ status (denormalised on users; refreshed at
15 /// OAuth callback / `POST /auth/refresh`). Used for the + badge and
16 /// for signature visibility, signatures only render for current
17 /// Fan+ subscribers.
18 pub author_is_fan_plus: bool,
19 /// Author's saved signature HTML (rendered at save time). Render only
20 /// when `author_is_fan_plus` is true.
21 pub author_signature_html: Option<String>,
22 }
23
24 #[derive(sqlx::FromRow)]
25 pub struct PostForEdit {
26 pub id: Uuid,
27 pub author_id: Uuid,
28 pub body_markdown: String,
29 pub created_at: DateTime<Utc>,
30 pub deleted_at: Option<DateTime<Utc>>,
31 pub thread_id: Uuid,
32 pub thread_title: String,
33 pub community_name: String,
34 pub community_slug: String,
35 pub community_id: Uuid,
36 pub category_name: String,
37 pub category_slug: String,
38 }
39
40 #[tracing::instrument(skip_all)]
41 pub async fn list_posts_in_thread(
42 pool: &PgPool,
43 thread_id: Uuid,
44 ) -> Result<Vec<PostWithAuthor>, sqlx::Error> {
45 sqlx::query_as!(
46 PostWithAuthor,
47 r#"SELECT p.id, p.author_id,
48 COALESCE(u.display_name, u.username) AS "author_name!",
49 u.username AS author_username,
50 p.body_html,
51 p.created_at AS "created_at: chrono::DateTime<chrono::Utc>",
52 p.edited_at AS "edited_at: chrono::DateTime<chrono::Utc>",
53 p.deleted_at AS "deleted_at: chrono::DateTime<chrono::Utc>",
54 p.removed_at AS "removed_at: chrono::DateTime<chrono::Utc>",
55 u.is_fan_plus AS author_is_fan_plus,
56 u.signature_html AS author_signature_html
57 FROM posts p
58 JOIN users u ON u.mnw_account_id = p.author_id
59 WHERE p.thread_id = $1
60 ORDER BY p.created_at"#,
61 thread_id,
62 )
63 .fetch_all(pool)
64 .await
65 }
66
67 #[tracing::instrument(skip_all)]
68 pub async fn list_posts_in_thread_paginated(
69 pool: &PgPool,
70 thread_id: Uuid,
71 limit: i64,
72 offset: i64,
73 ) -> Result<Vec<PostWithAuthor>, sqlx::Error> {
74 sqlx::query_as!(
75 PostWithAuthor,
76 r#"SELECT p.id, p.author_id,
77 COALESCE(u.display_name, u.username) AS "author_name!",
78 u.username AS author_username,
79 p.body_html,
80 p.created_at AS "created_at: chrono::DateTime<chrono::Utc>",
81 p.edited_at AS "edited_at: chrono::DateTime<chrono::Utc>",
82 p.deleted_at AS "deleted_at: chrono::DateTime<chrono::Utc>",
83 p.removed_at AS "removed_at: chrono::DateTime<chrono::Utc>",
84 u.is_fan_plus AS author_is_fan_plus,
85 u.signature_html AS author_signature_html
86 FROM posts p
87 JOIN users u ON u.mnw_account_id = p.author_id
88 WHERE p.thread_id = $1
89 ORDER BY p.created_at
90 LIMIT $2 OFFSET $3"#,
91 thread_id,
92 limit,
93 offset,
94 )
95 .fetch_all(pool)
96 .await
97 }
98
99 #[tracing::instrument(skip_all)]
100 pub async fn count_posts_in_thread(pool: &PgPool, thread_id: Uuid) -> Result<i64, sqlx::Error> {
101 // Intentionally counts ALL posts including mod-removed/soft-deleted: this
102 // drives the thread-view pagination, and `list_posts_in_thread_paginated`
103 // returns those rows so they render as tombstones. The count must match the
104 // list it paginates, or the last page of tombstones would be cut off. For an
105 // active-only count (e.g. the server-facing stats), use `get_thread_stats`.
106 sqlx::query_scalar!(
107 r#"SELECT COUNT(*) AS "count!" FROM posts WHERE thread_id = $1"#,
108 thread_id,
109 )
110 .fetch_one(pool)
111 .await
112 }
113
114 /// Count posts + footnotes by a user in the last N seconds (for per-user rate limiting).
115 #[tracing::instrument(skip_all)]
116 pub async fn count_recent_posts_by_user(
117 pool: &PgPool,
118 user_id: Uuid,
119 seconds: i64,
120 ) -> Result<i64, sqlx::Error> {
121 sqlx::query_scalar!(
122 r#"SELECT (SELECT COUNT(*) FROM posts WHERE author_id = $1 AND created_at > NOW() - make_interval(secs => $2))
123 + (SELECT COUNT(*) FROM post_footnotes WHERE author_id = $1 AND created_at > NOW() - make_interval(secs => $2)) AS "count!""#,
124 user_id,
125 seconds as f64,
126 )
127 .fetch_one(pool)
128 .await
129 }
130
131 #[tracing::instrument(skip_all)]
132 pub async fn get_post_for_edit(
133 pool: &PgPool,
134 post_id: Uuid,
135 ) -> Result<Option<super::Unscoped<PostForEdit>>, sqlx::Error> {
136 sqlx::query_as!(
137 PostForEdit,
138 r#"SELECT p.id, p.author_id, p.body_markdown,
139 p.created_at AS "created_at: chrono::DateTime<chrono::Utc>",
140 p.deleted_at AS "deleted_at: chrono::DateTime<chrono::Utc>",
141 p.thread_id, t.title AS thread_title,
142 co.name AS community_name, co.slug AS community_slug,
143 co.id AS community_id,
144 c.name AS category_name, c.slug AS category_slug
145 FROM posts p
146 JOIN threads t ON t.id = p.thread_id
147 JOIN categories c ON c.id = t.category_id
148 JOIN communities co ON co.id = c.community_id
149 WHERE p.id = $1"#,
150 post_id,
151 )
152 .fetch_optional(pool)
153 .await
154 .map(|opt| {
155 opt.map(|row| {
156 let community_id = row.community_id;
157 super::Unscoped::new(row, community_id)
158 })
159 })
160 }
161
162 /// Fetch a post's author_id and body_markdown for quote verification, scoped to
163 /// the quoting post's community.
164 ///
165 /// The `community_id` filter is not optional: a `[quote:UUID:HASH]` marker only
166 /// verifies against a post *in the same community*, so a member of community A
167 /// can never use quote verification as an oracle against a post in community B
168 /// (C1 scope class, N3). There is deliberately no unscoped `by-id` variant of
169 /// this loader, the scoping is structural, which is why it needs no
170 /// `clippy.toml` disallowed-methods entry (cf. `get_category_in_community`).
171 #[tracing::instrument(skip_all)]
172 pub async fn get_post_body_markdown_in_community(
173 pool: &PgPool,
174 post_id: Uuid,
175 community_id: Uuid,
176 ) -> Result<Option<(Uuid, String)>, sqlx::Error> {
177 // Only live posts can be quoted: a mod-removed post (or one in a soft-deleted
178 // thread) must fail quote verification so its text cannot be re-surfaced into
179 // a live post by quoting it back.
180 sqlx::query!(
181 "SELECT p.author_id, p.body_markdown
182 FROM posts p
183 JOIN threads t ON t.id = p.thread_id
184 JOIN categories c ON c.id = t.category_id
185 WHERE p.id = $1 AND c.community_id = $2
186 AND p.removed_at IS NULL AND t.deleted_at IS NULL",
187 post_id,
188 community_id,
189 )
190 .fetch_optional(pool)
191 .await
192 .map(|opt| opt.map(|r| (r.author_id, r.body_markdown)))
193 }
194
195 /// Whether a post is mod-removed (`removed_at IS NOT NULL`).
196 #[tracing::instrument(skip_all)]
197 pub async fn is_post_removed(pool: &PgPool, post_id: Uuid) -> Result<bool, sqlx::Error> {
198 sqlx::query_scalar!(
199 r#"SELECT (removed_at IS NOT NULL) AS "removed!" FROM posts WHERE id = $1"#,
200 post_id,
201 )
202 .fetch_one(pool)
203 .await
204 }
205