tetratto_core2/database/
posts.rs

1use std::collections::HashMap;
2use crate::config::StringBan;
3use crate::model::groups::GroupRole;
4use crate::model::{
5    Error, Result,
6    auth::User,
7    posts::{Post, PostContext},
8    permissions::FinePermission,
9    moderation::AuditLogEntry,
10    auth::{AchievementName, Notification},
11    id::Id,
12};
13use tritools::time::unix_epoch_timestamp;
14use crate::{auto_method, DataManager};
15use oiseau::{PostgresRow, execute, get, query_row, query_rows, params, cache::Cache};
16
17impl DataManager {
18    /// Get a [`Post`] from an SQL row.
19    pub(crate) fn get_post_from_row(x: &PostgresRow) -> Post {
20        Post {
21            id: Id::deserialize(&get!(x->0(String))),
22            created: get!(x->1(i64)) as u128,
23            content: get!(x->2(String)),
24            owner: Id::deserialize(&get!(x->3(String))),
25            context: serde_json::from_str(&get!(x->4(String))).unwrap(),
26            replying_to: x
27                .get::<usize, Option<String>>(5)
28                .map(|x| Id::deserialize(&x)),
29            // likes
30            likes: get!(x->6(i32)) as isize,
31            dislikes: get!(x->7(i32)) as isize,
32            // other counts
33            comment_count: get!(x->8(i32)) as usize,
34            // ...
35            uploads: serde_json::from_str(&get!(x->9(String))).unwrap(),
36            is_deleted: get!(x->10(i32)) as i8 == 1,
37            // SKIP tsvector (11)
38            poll_id: x
39                .get::<usize, Option<String>>(12)
40                .map(|x| Id::deserialize(&x)),
41            views: get!(x->13(i32)) as usize,
42            group: x
43                .get::<usize, Option<String>>(14)
44                .map(|x| Id::deserialize(&x)),
45            limited_reach: get!(x->15(i32)) == 1,
46        }
47    }
48
49    auto_method!(get_post_by_id()@get_post_from_row -> "SELECT * FROM posts WHERE id = $1" --name="post" --returns=Post --cache-key-tmpl="atto.post:{}");
50
51    /// Get all posts which are comments on the given post by ID.
52    ///
53    /// # Arguments
54    /// * `id` - the ID of the post the requested posts are commenting on
55    /// * `batch` - the limit of posts in each page
56    /// * `page` - the page number
57    pub async fn get_replies_by_post(
58        &self,
59        id: &Id,
60        batch: usize,
61        page: usize,
62    ) -> Result<Vec<Post>> {
63        let conn = match self.0.connect().await {
64            Ok(c) => c,
65            Err(e) => return Err(Error::DatabaseConnection(e.to_string())),
66        };
67
68        let res = query_rows!(
69            &conn,
70            "SELECT * FROM posts WHERE replying_to = $1 ORDER BY created DESC LIMIT $2 OFFSET $3",
71            &[&id.printable(), &(batch as i64), &((page * batch) as i64)],
72            |x| { Self::get_post_from_row(x) }
73        );
74
75        if res.is_err() {
76            return Err(Error::GeneralNotFound("post".to_string()));
77        }
78
79        Ok(res.unwrap())
80    }
81
82    /// Get all posts from the given user (from most recent).
83    ///
84    /// # Arguments
85    /// * `id` - the ID of the user the requested posts belong to
86    /// * `batch` - the limit of posts in each page
87    /// * `page` - the page number
88    pub async fn get_posts_by_user(&self, id: &Id, batch: usize, page: usize) -> Result<Vec<Post>> {
89        let conn = match self.0.connect().await {
90            Ok(c) => c,
91            Err(e) => return Err(Error::DatabaseConnection(e.to_string())),
92        };
93
94        let res = query_rows!(
95            &conn,
96            "SELECT * FROM posts WHERE owner = $1 AND replying_to IS NULL AND NOT (context::json->>'is_profile_pinned')::boolean AND is_deleted = 0 ORDER BY created DESC LIMIT $2 OFFSET $3",
97            &[&id.printable(), &(batch as i64), &((page * batch) as i64)],
98            |x| { Self::get_post_from_row(x) }
99        );
100
101        if res.is_err() {
102            return Err(Error::GeneralNotFound("post".to_string()));
103        }
104
105        Ok(res.unwrap())
106    }
107
108    /// Get all posts from the given group (from most recent).
109    ///
110    /// # Arguments
111    /// * `id` - the ID of the group the requested posts belong to
112    /// * `batch` - the limit of posts in each page
113    /// * `page` - the page number
114    pub async fn get_posts_by_group(
115        &self,
116        id: &Id,
117        batch: usize,
118        page: usize,
119    ) -> Result<Vec<Post>> {
120        let conn = match self.0.connect().await {
121            Ok(c) => c,
122            Err(e) => return Err(Error::DatabaseConnection(e.to_string())),
123        };
124
125        let res = query_rows!(
126            &conn,
127            "SELECT * FROM posts WHERE group_id = $1 AND replying_to IS NULL AND is_deleted = 0 ORDER BY created DESC LIMIT $2 OFFSET $3",
128            &[&id.printable(), &(batch as i64), &((page * batch) as i64)],
129            |x| { Self::get_post_from_row(x) }
130        );
131
132        if res.is_err() {
133            return Err(Error::GeneralNotFound("post".to_string()));
134        }
135
136        Ok(res.unwrap())
137    }
138
139    /// Get all posts from the given user (sorted by likes - dislikes).
140    ///
141    /// # Arguments
142    /// * `id` - the ID of the user the requested posts belong to
143    /// * `batch` - the limit of posts in each page
144    /// * `page` - the page number
145    pub async fn get_popular_posts_by_user(
146        &self,
147        id: &Id,
148        batch: usize,
149        page: usize,
150    ) -> Result<Vec<Post>> {
151        let conn = match self.0.connect().await {
152            Ok(c) => c,
153            Err(e) => return Err(Error::DatabaseConnection(e.to_string())),
154        };
155
156        let res = query_rows!(
157            &conn,
158            "SELECT * FROM posts WHERE owner = $1 AND replying_to IS NULL AND NOT (context::json->>'is_profile_pinned')::boolean AND is_deleted = 0 ORDER BY (likes - dislikes) DESC, created DESC LIMIT $2 OFFSET $3",
159            &[&id.printable(), &(batch as i64), &((page * batch) as i64)],
160            |x| { Self::get_post_from_row(x) }
161        );
162
163        if res.is_err() {
164            return Err(Error::GeneralNotFound("post".to_string()));
165        }
166
167        Ok(res.unwrap())
168    }
169
170    /// Get all posts (that are answering a question) from the given user (from most recent).
171    ///
172    /// # Arguments
173    /// * `id` - the ID of the user the requested posts belong to
174    /// * `batch` - the limit of posts in each page
175    /// * `page` - the page number
176    pub async fn get_responses_by_user(
177        &self,
178        id: &Id,
179        batch: usize,
180        page: usize,
181    ) -> Result<Vec<Post>> {
182        let conn = match self.0.connect().await {
183            Ok(c) => c,
184            Err(e) => return Err(Error::DatabaseConnection(e.to_string())),
185        };
186
187        let res = query_rows!(
188            &conn,
189            "SELECT * FROM posts WHERE owner = $1 AND replying_to IS NULL AND NOT (context::json->>'is_profile_pinned')::boolean AND is_deleted = 0 AND NOT context LIKE '%\"answering\":null%' ORDER BY created DESC LIMIT $2 OFFSET $3",
190            &[&id.printable(), &(batch as i64), &((page * batch) as i64)],
191            |x| { Self::get_post_from_row(x) }
192        );
193
194        if res.is_err() {
195            return Err(Error::GeneralNotFound("post".to_string()));
196        }
197
198        Ok(res.unwrap())
199    }
200
201    /// Get all replies from the given user (from most recent).
202    ///
203    /// # Arguments
204    /// * `id` - the ID of the user the requested posts belong to
205    /// * `batch` - the limit of posts in each page
206    /// * `page` - the page number
207    pub async fn get_replies_by_user(
208        &self,
209        id: &Id,
210        batch: usize,
211        page: usize,
212        user: &Option<User>,
213    ) -> Result<Vec<Post>> {
214        let conn = match self.0.connect().await {
215            Ok(c) => c,
216            Err(e) => return Err(Error::DatabaseConnection(e.to_string())),
217        };
218
219        // check if we should hide nsfw posts
220        let mut hide_nsfw: bool = true;
221
222        if let Some(ua) = user {
223            hide_nsfw = !ua.settings.show_nsfw;
224        }
225
226        // ...
227        let res = query_rows!(
228            &conn,
229            &format!(
230                "SELECT * FROM posts WHERE owner = $1 AND NOT replying_to IS NULL AND NOT (context::json->>'is_profile_pinned')::boolean {} ORDER BY created DESC LIMIT $2 OFFSET $3",
231                if hide_nsfw {
232                    "AND NOT (context::json->>'is_nsfw')::boolean"
233                } else {
234                    ""
235                }
236            ),
237            &[&id.printable(), &(batch as i64), &((page * batch) as i64)],
238            |x| { Self::get_post_from_row(x) }
239        );
240
241        if res.is_err() {
242            return Err(Error::GeneralNotFound("post".to_string()));
243        }
244
245        Ok(res.unwrap())
246    }
247
248    /// Get all posts containing media from the given user (from most recent).
249    ///
250    /// # Arguments
251    /// * `id` - the ID of the user the requested posts belong to
252    /// * `batch` - the limit of posts in each page
253    /// * `page` - the page number
254    pub async fn get_media_posts_by_user(
255        &self,
256        id: &Id,
257        batch: usize,
258        page: usize,
259        user: &Option<User>,
260    ) -> Result<Vec<Post>> {
261        let conn = match self.0.connect().await {
262            Ok(c) => c,
263            Err(e) => return Err(Error::DatabaseConnection(e.to_string())),
264        };
265
266        // check if we should hide nsfw posts
267        let mut hide_nsfw: bool = true;
268
269        if let Some(ua) = user {
270            hide_nsfw = !ua.settings.show_nsfw;
271        }
272
273        // ...
274        let res = query_rows!(
275            &conn,
276            &format!(
277                "SELECT * FROM posts WHERE owner = $1 AND NOT uploads = '[]' AND NOT (context::json->>'is_profile_pinned')::boolean {} ORDER BY created DESC LIMIT $2 OFFSET $3",
278                if hide_nsfw {
279                    "AND NOT (context::json->>'is_nsfw')::boolean"
280                } else {
281                    ""
282                }
283            ),
284            &[&id.printable(), &(batch as i64), &((page * batch) as i64)],
285            |x| { Self::get_post_from_row(x) }
286        );
287
288        if res.is_err() {
289            return Err(Error::GeneralNotFound("post".to_string()));
290        }
291
292        Ok(res.unwrap())
293    }
294
295    /// Get all posts from the given user (searched).
296    ///
297    /// # Arguments
298    /// * `id` - the ID of the user the requested posts belong to
299    /// * `batch` - the limit of posts in each page
300    /// * `page` - the page number
301    /// * `text_query` - the search query
302    /// * `user` - the user who is viewing the posts
303    pub async fn get_posts_by_user_searched(
304        &self,
305        id: &Id,
306        text_query: &str,
307        batch: usize,
308        page: usize,
309        user: &Option<&User>,
310    ) -> Result<Vec<Post>> {
311        let conn = match self.0.connect().await {
312            Ok(c) => c,
313            Err(e) => return Err(Error::DatabaseConnection(e.to_string())),
314        };
315
316        // check if we should hide nsfw posts
317        let mut hide_nsfw: bool = true;
318
319        if let Some(ua) = user {
320            hide_nsfw = !ua.settings.show_nsfw;
321        }
322
323        // ...
324        let res = query_rows!(
325            &conn,
326            &format!(
327                "SELECT * FROM posts WHERE owner = $1 AND tsvector_content @@ to_tsquery($2) {} AND is_deleted = 0 ORDER BY created DESC LIMIT $3 OFFSET $4",
328                if hide_nsfw {
329                    "AND NOT (context::json->>'is_nsfw')::boolean"
330                } else {
331                    ""
332                }
333            ),
334            params![
335                &id.printable(),
336                &text_query,
337                &(batch as i64),
338                &((page * batch) as i64)
339            ],
340            |x| { Self::get_post_from_row(x) }
341        );
342
343        if res.is_err() {
344            return Err(Error::GeneralNotFound("post".to_string()));
345        }
346
347        Ok(res.unwrap())
348    }
349
350    /// Get all post (searched).
351    ///
352    /// # Arguments
353    /// * `text_query` - the search query
354    /// * `batch` - the limit of posts in each page
355    /// * `page` - the page number
356    pub async fn get_posts_searched(
357        &self,
358        text_query: &str,
359        batch: usize,
360        page: usize,
361    ) -> Result<Vec<Post>> {
362        let conn = match self.0.connect().await {
363            Ok(c) => c,
364            Err(e) => return Err(Error::DatabaseConnection(e.to_string())),
365        };
366
367        // ...
368        let res = query_rows!(
369            &conn,
370            "SELECT * FROM posts WHERE tsvector_content @@ to_tsquery($1) AND is_deleted = 0 ORDER BY created DESC LIMIT $2 OFFSET $3",
371            params![
372                &text_query.replace(" ", " & "),
373                &(batch as i64),
374                &((page * batch) as i64)
375            ],
376            |x| { Self::get_post_from_row(x) }
377        );
378
379        if res.is_err() {
380            return Err(Error::GeneralNotFound("post".to_string()));
381        }
382
383        Ok(res.unwrap())
384    }
385
386    /// Get all posts from the given user with the given tag (from most recent).
387    ///
388    /// # Arguments
389    /// * `id` - the ID of the user the requested posts belong to
390    /// * `tag` - the tag to filter by
391    /// * `batch` - the limit of posts in each page
392    /// * `page` - the page number
393    pub async fn get_posts_by_user_tag(
394        &self,
395        id: &Id,
396        tag: &str,
397        batch: usize,
398        page: usize,
399    ) -> Result<Vec<Post>> {
400        let conn = match self.0.connect().await {
401            Ok(c) => c,
402            Err(e) => return Err(Error::DatabaseConnection(e.to_string())),
403        };
404
405        let res = query_rows!(
406            &conn,
407            "SELECT * FROM posts WHERE owner = $1 AND context::json->>'tags' LIKE $2 AND is_deleted = 0 ORDER BY created DESC LIMIT $3 OFFSET $4",
408            params![
409                &id.printable(),
410                &format!("%\"{tag}\"%"),
411                &(batch as i64),
412                &((page * batch) as i64)
413            ],
414            |x| { Self::get_post_from_row(x) }
415        );
416
417        if res.is_err() {
418            return Err(Error::GeneralNotFound("post".to_string()));
419        }
420
421        Ok(res.unwrap())
422    }
423
424    /// Get all posts (that are answering a question) from the given user
425    /// with the given tag (from most recent).
426    ///
427    /// # Arguments
428    /// * `id` - the ID of the user the requested posts belong to
429    /// * `tag` - the tag to filter by
430    /// * `batch` - the limit of posts in each page
431    /// * `page` - the page number
432    pub async fn get_responses_by_user_tag(
433        &self,
434        id: &Id,
435        tag: &str,
436        batch: usize,
437        page: usize,
438    ) -> Result<Vec<Post>> {
439        let conn = match self.0.connect().await {
440            Ok(c) => c,
441            Err(e) => return Err(Error::DatabaseConnection(e.to_string())),
442        };
443
444        let res = query_rows!(
445            &conn,
446            "SELECT * FROM posts WHERE owner = $1 AND context::json->>'tags' LIKE $2 AND is_deleted = 0 AND NOT context::jsonb->>'answering' = '0' ORDER BY created DESC LIMIT $3 OFFSET $4",
447            params![
448                &id.printable(),
449                &format!("%\"{tag}\"%"),
450                &(batch as i64),
451                &((page * batch) as i64)
452            ],
453            |x| { Self::get_post_from_row(x) }
454        );
455
456        if res.is_err() {
457            return Err(Error::GeneralNotFound("post".to_string()));
458        }
459
460        Ok(res.unwrap())
461    }
462
463    /// Get all pinned posts from the given user (from most recent).
464    ///
465    /// # Arguments
466    /// * `id` - the ID of the user the requested posts belong to
467    pub async fn get_pinned_posts_by_user(&self, id: &Id) -> Result<Vec<Post>> {
468        let conn = match self.0.connect().await {
469            Ok(c) => c,
470            Err(e) => return Err(Error::DatabaseConnection(e.to_string())),
471        };
472
473        let res = query_rows!(
474            &conn,
475            "SELECT * FROM posts WHERE owner = $1 AND context LIKE '%\"is_profile_pinned\":true%' ORDER BY created DESC",
476            &[&id.printable()],
477            |x| { Self::get_post_from_row(x) }
478        );
479
480        if res.is_err() {
481            return Err(Error::GeneralNotFound("post".to_string()));
482        }
483
484        Ok(res.unwrap())
485    }
486
487    /// Get all posts answering the given question (from most recent).
488    ///
489    /// # Arguments
490    /// * `id` - the ID of the question the requested posts belong to
491    /// * `batch` - the limit of posts in each page
492    /// * `page` - the page number
493    pub async fn get_posts_by_question(
494        &self,
495        id: &Id,
496        batch: usize,
497        page: usize,
498    ) -> Result<Vec<Post>> {
499        let conn = match self.0.connect().await {
500            Ok(c) => c,
501            Err(e) => return Err(Error::DatabaseConnection(e.to_string())),
502        };
503
504        let res = query_rows!(
505            &conn,
506            "SELECT * FROM posts WHERE context LIKE $1 AND is_deleted = 0 ORDER BY created DESC LIMIT $2 OFFSET $3",
507            params![
508                &format!("%\"answering\":\"{id}\"%"),
509                &(batch as i64),
510                &((page * batch) as i64)
511            ],
512            |x| { Self::get_post_from_row(x) }
513        );
514
515        if res.is_err() {
516            return Err(Error::GeneralNotFound("post".to_string()));
517        }
518
519        Ok(res.unwrap())
520    }
521
522    /// Get a post given its owner and question ID.
523    ///
524    /// # Arguments
525    /// * `owner` - the ID of the post owner
526    /// * `question` - the ID of the post question
527    pub async fn get_post_by_owner_question(&self, owner: &Id, question: &Id) -> Result<Post> {
528        let conn = match self.0.connect().await {
529            Ok(c) => c,
530            Err(e) => return Err(Error::DatabaseConnection(e.to_string())),
531        };
532
533        let res = query_row!(
534            &conn,
535            "SELECT * FROM posts WHERE context LIKE $1 AND owner = $2 AND is_deleted = 0 LIMIT 1",
536            params![
537                &format!("%\"answering\":\"{question}\"%"),
538                &owner.printable(),
539            ],
540            |x| { Ok(Self::get_post_from_row(x)) }
541        );
542
543        if res.is_err() {
544            return Err(Error::GeneralNotFound("post".to_string()));
545        }
546
547        Ok(res.unwrap())
548    }
549
550    /// Get all quoting posts by the post their quoting.
551    ///
552    /// Requires that the post has content. See [`Self::get_reposts_by_quoting`]
553    /// for the no-content version.
554    ///
555    /// # Arguments
556    /// * `id` - the ID of the post that is being quoted
557    /// * `batch` - the limit of posts in each page
558    /// * `page` - the page number
559    pub async fn get_quoting_posts_by_quoting(
560        &self,
561        id: &Id,
562        batch: usize,
563        page: usize,
564    ) -> Result<Vec<Post>> {
565        let conn = match self.0.connect().await {
566            Ok(c) => c,
567            Err(e) => return Err(Error::DatabaseConnection(e.to_string())),
568        };
569
570        let res = query_rows!(
571            &conn,
572            "SELECT * FROM posts WHERE NOT content = '' AND context LIKE $1 ORDER BY created DESC LIMIT $2 OFFSET $3",
573            params![
574                &format!("%\"reposting\":{id}%"),
575                &(batch as i64),
576                &((page * batch) as i64)
577            ],
578            |x| { Self::get_post_from_row(x) }
579        );
580
581        if res.is_err() {
582            return Err(Error::GeneralNotFound("post".to_string()));
583        }
584
585        Ok(res.unwrap())
586    }
587
588    /// Get all quoting posts by the post their quoting.
589    ///
590    /// Requires that the post has no content. See [`Self::get_quoting_posts_by_quoting`]
591    /// for the content-required version.
592    ///
593    /// # Arguments
594    /// * `id` - the ID of the post that is being quoted
595    /// * `batch` - the limit of posts in each page
596    /// * `page` - the page number
597    pub async fn get_reposts_by_quoting(
598        &self,
599        id: &Id,
600        batch: usize,
601        page: usize,
602    ) -> Result<Vec<Post>> {
603        let conn = match self.0.connect().await {
604            Ok(c) => c,
605            Err(e) => return Err(Error::DatabaseConnection(e.to_string())),
606        };
607
608        let res = query_rows!(
609            &conn,
610            "SELECT * FROM posts WHERE content = '' AND context LIKE $1 ORDER BY created DESC LIMIT $2 OFFSET $3",
611            params![
612                &format!("%\"reposting\":{id}%"),
613                &(batch as i64),
614                &((page * batch) as i64)
615            ],
616            |x| { Self::get_post_from_row(x) }
617        );
618
619        if res.is_err() {
620            return Err(Error::GeneralNotFound("post".to_string()));
621        }
622
623        Ok(res.unwrap())
624    }
625
626    /// Get posts from all communities, sorted by likes.
627    ///
628    /// # Arguments
629    /// * `batch` - the limit of posts in each page
630    /// * `before` - the timestamp to pull posts before
631    /// * `cutoff` - the maximum number of milliseconds ago the post could have been created
632    pub async fn get_popular_posts(
633        &self,
634        batch: usize,
635        before: usize,
636        cutoff: usize,
637    ) -> Result<Vec<Post>> {
638        let conn = match self.0.connect().await {
639            Ok(c) => c,
640            Err(e) => return Err(Error::DatabaseConnection(e.to_string())),
641        };
642
643        let res = query_rows!(
644            &conn,
645            &format!(
646                "SELECT * FROM posts WHERE replying_to IS NULL AND limited_reach = 0 AND NOT context LIKE '%\"is_nsfw\":true%' AND ($1 - created) < $2{} ORDER BY (likes - dislikes) DESC, created ASC LIMIT $3",
647                if before > 0 {
648                    format!(" AND created < {before}")
649                } else {
650                    String::new()
651                }
652            ),
653            &[
654                &(unix_epoch_timestamp() as i64),
655                &(cutoff as i64),
656                &(batch as i64),
657            ],
658            |x| { Self::get_post_from_row(x) }
659        );
660
661        if let Err(e) = res {
662            return Err(Error::DatabaseError(e.to_string()));
663        }
664
665        Ok(res.unwrap())
666    }
667
668    /// Get posts from all communities, sorted by creation.
669    ///
670    /// # Arguments
671    /// * `batch` - the limit of posts in each page
672    /// * `page` - the page number
673    pub async fn get_latest_posts(
674        &self,
675        as_user: &Option<&User>,
676        batch: usize,
677        page: usize,
678    ) -> Result<Vec<Post>> {
679        let hide_answers: bool = if let Some(user) = as_user {
680            user.settings.all_timeline_hide_answers
681        } else {
682            false
683        };
684
685        // check if we should hide nsfw posts
686        let mut hide_nsfw: bool = true;
687        let mut can_view_deleted: bool = false;
688
689        if let Some(ua) = as_user {
690            hide_nsfw = !ua.settings.show_nsfw;
691            can_view_deleted = ua.permissions.check(FinePermission::ManagePosts);
692        }
693
694        // ...
695        let conn = match self.0.connect().await {
696            Ok(c) => c,
697            Err(e) => return Err(Error::DatabaseConnection(e.to_string())),
698        };
699
700        let res = query_rows!(
701            &conn,
702            &format!(
703                "SELECT * FROM posts WHERE replying_to IS NULL AND limited_reach = 0 AND group_id IS NULL{}{}{} ORDER BY created DESC LIMIT $1 OFFSET $2",
704                if hide_nsfw {
705                    " AND NOT context LIKE '%\"is_nsfw\":true%'"
706                } else {
707                    ""
708                },
709                if hide_answers {
710                    " AND context LIKE '%\"answering\":null%'"
711                } else {
712                    ""
713                },
714                if !can_view_deleted {
715                    " AND is_deleted = 0"
716                } else {
717                    ""
718                }
719            ),
720            &[&(batch as i64), &((page * batch) as i64)],
721            |x| { Self::get_post_from_row(x) }
722        );
723
724        if let Err(e) = res {
725            return Err(Error::DatabaseError(e.to_string()));
726        }
727
728        Ok(res.unwrap())
729    }
730
731    /// Get posts from all users the given user is following.
732    ///
733    /// # Arguments
734    /// * `id` - the ID of the user
735    /// * `batch` - the limit of posts in each page
736    /// * `page` - the page number
737    pub async fn get_posts_from_user_following(
738        &self,
739        id: &Id,
740        batch: usize,
741        page: usize,
742    ) -> Result<Vec<Post>> {
743        let following = self.get_user_follows_by_initiator_all(id).await?;
744        let mut following = following.iter();
745        let first = match following.next() {
746            Some(f) => f,
747            None => return Ok(Vec::new()),
748        };
749
750        let mut query_string: String = String::new();
751
752        for user in following {
753            query_string.push_str(&format!(" OR owner = '{}'", user.receiver));
754        }
755
756        // ...
757        let conn = match self.0.connect().await {
758            Ok(c) => c,
759            Err(e) => return Err(Error::DatabaseConnection(e.to_string())),
760        };
761
762        let res = query_rows!(
763            &conn,
764            &format!(
765                "SELECT * FROM posts WHERE (owner = '{id}' OR owner = '{}' {query_string}) AND replying_to IS NULL AND is_deleted = 0 ORDER BY created DESC LIMIT $1 OFFSET $2",
766                first.receiver,
767            ),
768            &[&(batch as i64), &((page * batch) as i64)],
769            |x| { Self::get_post_from_row(x) }
770        );
771
772        if let Err(e) = res {
773            return Err(Error::DatabaseError(e.to_string()));
774        }
775
776        Ok(res.unwrap())
777    }
778
779    /// Get posts from all groups the user is in.
780    ///
781    /// # Arguments
782    /// * `id` - the ID of the user
783    /// * `batch` - the limit of posts in each page
784    /// * `page` - the page number
785    pub async fn get_posts_from_user_groups(
786        &self,
787        id: &Id,
788        batch: usize,
789        page: usize,
790    ) -> Result<Vec<Post>> {
791        let groups = self.get_group_memberships_group_by_owner_all(id).await?;
792        let mut groups = groups.iter();
793        let first = match groups.next() {
794            Some(f) => f,
795            None => return Ok(Vec::new()),
796        };
797
798        let mut query_string: String = String::new();
799
800        for id in groups {
801            query_string.push_str(&format!(" OR group_id = '{}'", id));
802        }
803
804        // ...
805        let conn = match self.0.connect().await {
806            Ok(c) => c,
807            Err(e) => return Err(Error::DatabaseConnection(e.to_string())),
808        };
809
810        let res = query_rows!(
811            &conn,
812            &format!(
813                "SELECT * FROM posts WHERE group_id = '{first}' {query_string} AND replying_to IS NULL AND is_deleted = 0 ORDER BY created DESC LIMIT $1 OFFSET $2",
814            ),
815            &[&(batch as i64), &((page * batch) as i64)],
816            |x| { Self::get_post_from_row(x) }
817        );
818
819        if let Err(e) = res {
820            return Err(Error::DatabaseError(e.to_string()));
821        }
822
823        Ok(res.unwrap())
824    }
825
826    /// Create a new post in the database.
827    ///
828    /// # Arguments
829    /// * `data` - a mock [`Post`] object to insert
830    pub async fn create_post(&self, mut data: Post) -> Result<Id> {
831        // check characters
832        for ban in &self.0.0.banned_data {
833            match ban {
834                StringBan::String(x) => {
835                    if data.content.contains(x) {
836                        return Ok(Id::Legacy(0));
837                    }
838                }
839                StringBan::Unicode(x) => {
840                    if data.content.contains(&match char::from_u32(x.to_owned()) {
841                        Some(c) => c.to_string(),
842                        None => continue,
843                    }) {
844                        return Ok(Id::Legacy(0));
845                    }
846                }
847            }
848        }
849
850        // ...
851        let mut owner = self.get_user_by_id(&data.owner).await?;
852
853        // check group
854        if let Some(ref group) = data.group {
855            // must at least be a member (not pending)
856            let membership = self
857                .get_group_membership_by_owner_group(&owner.id, group)
858                .await?;
859
860            if membership.role == GroupRole::Pending || membership.role == GroupRole::Banned {
861                return Err(Error::NotAllowed);
862            }
863        }
864
865        // check values (if this isn't reposting something else)
866        let is_reposting = if let Some(ref repost) = data.context.repost {
867            repost.reposting.is_some()
868        } else {
869            false
870        };
871
872        if !is_reposting {
873            if data.content.len() < 2 && data.uploads.is_empty() {
874                return Err(Error::DataTooShort("content".to_string()));
875            } else if data.content.len() > 4096 {
876                return Err(Error::DataTooLong("content".to_string()));
877            }
878        }
879
880        // remove request if we were answering a question
881        if let Some(ref answering) = data.context.answering {
882            let question = self.get_question_by_id(answering).await?;
883
884            // check if we've already answered this
885            if self
886                .get_post_by_owner_question(&owner.id, &question.id)
887                .await
888                .is_ok()
889            {
890                return Err(Error::MiscError(
891                    "You've already answered this question".to_string(),
892                ));
893            }
894
895            if !question.is_global {
896                self.delete_request(&question.id, &question.id, &owner, false)
897                    .await?;
898            } else {
899                self.incr_question_answer_count(answering).await?;
900            }
901
902            // create notification for question owner
903            // (if the current user isn't the owner)
904            if (question.owner != data.owner)
905                && (question.owner != Id::Legacy(0))
906                && (!owner.settings.private_profile
907                    | self
908                        .get_user_follow_by_initiator_receiver(&data.owner, &question.owner)
909                        .await
910                        .is_ok())
911            {
912                self.create_notification(Notification::new(
913                    "Your question has received a new answer!".to_string(),
914                    format!(
915                        "[@{}](/api/v2/users/find/{}) has answered your [question](/questions/{}).",
916                        owner.username, owner.id, question.id
917                    ),
918                    question.owner,
919                ))
920                .await?;
921            }
922
923            // inherit nsfw status
924            if question.context.is_nsfw {
925                data.context.is_nsfw = question.context.is_nsfw;
926            }
927        }
928
929        // check if we're reposting a post
930        let reposting = if let Some(ref repost) = data.context.repost {
931            if let Some(ref id) = repost.reposting {
932                Some(self.get_post_by_id(id).await?)
933            } else {
934                None
935            }
936        } else {
937            None
938        };
939
940        if let Some(ref rt) = reposting {
941            if data.content.is_empty() {
942                // reposting but NOT quoting... we shouldn't be able to repost a direct repost
943                data.context.reposts_enabled = false;
944                data.context.reactions_enabled = false;
945                data.context.comments_enabled = false;
946            }
947
948            // mirror nsfw status
949            if rt.context.is_nsfw {
950                data.context.is_nsfw = true;
951            }
952
953            // ...
954            if !rt.context.reposts_enabled {
955                return Err(Error::MiscError("Post has reposts disabled".to_string()));
956            }
957
958            // check blocked status
959            if self
960                .get_user_block_by_initiator_receiver(&rt.owner, &data.owner)
961                .await
962                .is_ok()
963            {
964                return Err(Error::NotAllowed);
965            }
966
967            // send notification
968            if owner.id != rt.owner && !owner.settings.private_profile {
969                self.create_notification(Notification::new(
970                    format!(
971                        "[@{}](/api/v2/users/find/{}) has [quoted](/posts/{}) your [post](/posts/{})",
972                        owner.username, owner.id, data.id, rt.id
973                    ),
974                    if data.content.is_empty() {
975                        String::new()
976                    } else {
977                        format!("\"{}\"", data.content)
978                    },
979                    rt.owner.to_owned(),
980                ))
981                .await?;
982            }
983
984            // award achievement
985            self.add_achievement(&mut owner, AchievementName::CreateRepost.into(), true)
986                .await?;
987        }
988
989        // check if the post we're replying to allows commments
990        let replying_to = if let Some(ref id) = data.replying_to {
991            Some(self.get_post_by_id(id).await?)
992        } else {
993            None
994        };
995
996        if let Some(ref rt) = replying_to {
997            if !rt.context.comments_enabled {
998                return Err(Error::MiscError("Post has comments disabled".to_string()));
999            }
1000
1001            // check blocked status
1002            if self
1003                .get_user_block_by_initiator_receiver(&rt.owner, &data.owner)
1004                .await
1005                .is_ok()
1006            {
1007                return Err(Error::NotAllowed);
1008            }
1009        }
1010
1011        // send mention notifications
1012        let mut already_notified: HashMap<String, User> = HashMap::new();
1013        for username in User::parse_mentions(&data.content) {
1014            let user = {
1015                if let Some(ua) = already_notified.get(&username) {
1016                    ua.to_owned()
1017                } else {
1018                    let user = self.get_user_by_username(&username).await?;
1019
1020                    // check blocked status
1021                    if self
1022                        .get_user_block_by_initiator_receiver(&user.id, &data.owner)
1023                        .await
1024                        .is_ok()
1025                    {
1026                        return Err(Error::NotAllowed);
1027                    }
1028
1029                    // check private status
1030                    if user.settings.private_profile
1031                        && self
1032                            .get_user_follow_by_initiator_receiver(&user.id, &data.owner)
1033                            .await
1034                            .is_err()
1035                    {
1036                        return Err(Error::NotAllowed);
1037                    }
1038
1039                    // send notif
1040                    self.create_notification(Notification::new(
1041                        "You've been mentioned in a post!".to_string(),
1042                        format!(
1043                            "[@{}](/api/v2/users/find/{}) has mentioned you in their [post](/posts/{}).",
1044                            owner.username, owner.id, data.id
1045                        ),
1046                        user.id.to_owned(),
1047                    ))
1048                    .await?;
1049
1050                    // ...
1051                    already_notified.insert(username.to_owned(), user.clone());
1052                    user
1053                }
1054            };
1055
1056            data.content = data.content.replace(
1057                &format!("@{username}"),
1058                &format!("[@{username}](/api/v2/users/find/{})", user.id),
1059            );
1060        }
1061
1062        // auto unlist
1063        if owner.settings.auto_nsfw {
1064            data.context.is_nsfw = true;
1065        }
1066
1067        // ...
1068        let conn = match self.0.connect().await {
1069            Ok(c) => c,
1070            Err(e) => return Err(Error::DatabaseConnection(e.to_string())),
1071        };
1072
1073        let res = execute!(
1074            &conn,
1075            "INSERT INTO posts VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, DEFAULT, $12, $13, $14, $15)",
1076            params![
1077                &data.id.printable(),
1078                &(data.created as i64),
1079                &data.content,
1080                &data.owner.printable(),
1081                &serde_json::to_string(&data.context).unwrap(),
1082                &data.replying_to.map(|x| x.printable()),
1083                &0_i32,
1084                &0_i32,
1085                &0_i32,
1086                &serde_json::to_string(&data.uploads).unwrap(),
1087                &{ if data.is_deleted { 1 } else { 0 } },
1088                &data.poll_id.map(|x| x.printable()),
1089                &0_i32,
1090                &data.group.map(|x| x.printable()),
1091                &{ if data.limited_reach { 1 } else { 0 } },
1092            ]
1093        );
1094
1095        if let Err(e) = res {
1096            return Err(Error::DatabaseError(e.to_string()));
1097        }
1098
1099        // incr comment count and send notification
1100        if let Some(rt) = replying_to {
1101            self.incr_post_comments(&rt.id).await.unwrap();
1102
1103            // send notification
1104            if data.owner != rt.owner {
1105                let owner = self.get_user_by_id(&data.owner).await?;
1106
1107                // make sure we're actually following the person we're commenting to
1108                // we shouldn't send the notif if we aren't, because they can't see it
1109                // (only if our profile is private)
1110                if !owner.settings.private_profile
1111                    | self
1112                        .get_user_follow_by_initiator_receiver(&data.owner, &rt.owner)
1113                        .await
1114                        .is_ok()
1115                {
1116                    self.create_notification(Notification::new(
1117                        "Your post has received a new comment!".to_string(),
1118                        format!(
1119                            "[@{}](/api/v2/users/find/{}) has commented on your [post](/posts/{}).",
1120                            owner.username, owner.id, rt.id
1121                        ),
1122                        rt.owner,
1123                    ))
1124                    .await?;
1125                }
1126
1127                if !rt.context.comments_enabled {
1128                    return Err(Error::NotAllowed);
1129                }
1130            }
1131        }
1132
1133        // increase user post count
1134        self.incr_user_post_count(&data.owner).await?;
1135
1136        // return
1137        Ok(data.id)
1138    }
1139
1140    pub async fn delete_post(&self, id: &Id, user: User) -> Result<()> {
1141        let y = self.get_post_by_id(id).await?;
1142
1143        if user.id != y.owner {
1144            if !user.permissions.check(FinePermission::ManagePosts) {
1145                return Err(Error::NotAllowed);
1146            } else {
1147                self.create_audit_log_entry(AuditLogEntry::new(
1148                    user.id.to_owned(),
1149                    format!("invoked `delete_post` with x value `{id}`"),
1150                ))
1151                .await?
1152            }
1153        }
1154
1155        let conn = match self.0.connect().await {
1156            Ok(c) => c,
1157            Err(e) => return Err(Error::DatabaseConnection(e.to_string())),
1158        };
1159
1160        let res = execute!(&conn, "DELETE FROM posts WHERE id = $1", &[&id.printable()]);
1161
1162        if let Err(e) = res {
1163            return Err(Error::DatabaseError(e.to_string()));
1164        }
1165
1166        self.0.1.remove(format!("atto.post:{}", id)).await;
1167
1168        if !y.is_deleted {
1169            // decr parent comment count
1170            if let Some(replying_to) = y.replying_to {
1171                self.decr_post_comments(&replying_to).await.unwrap();
1172            }
1173
1174            // decr user post count
1175            let owner = self.get_user_by_id(&y.owner).await?;
1176
1177            if owner.post_count > 0 {
1178                self.decr_user_post_count(&y.owner).await?;
1179            }
1180
1181            // decr question answer count
1182            if let Some(answering) = y.context.answering {
1183                let question = self.get_question_by_id(&answering).await?;
1184
1185                if question.is_global {
1186                    self.decr_question_answer_count(&answering).await?;
1187                } else {
1188                    self.delete_question(&question.id, &user).await?;
1189                }
1190            }
1191        }
1192
1193        // delete uploads
1194        for upload in y.uploads {
1195            if let Err(e) = self.1.delete_upload(upload).await {
1196                return Err(Error::MiscError(e.to_string()));
1197            }
1198        }
1199
1200        // remove poll
1201        if let Some(poll_id) = y.poll_id {
1202            self.delete_poll(&poll_id, &user).await?;
1203        }
1204
1205        // return
1206        Ok(())
1207    }
1208
1209    pub async fn fake_delete_post(&self, id: &Id, user: User, is_deleted: bool) -> Result<()> {
1210        let y = self.get_post_by_id(id).await?;
1211
1212        macro_rules! del {
1213            ($user:ident, $self:ident) => {
1214                if !$user.permissions.check(FinePermission::ManagePosts) {
1215                    return Err(Error::NotAllowed);
1216                } else {
1217                    $self
1218                        .create_audit_log_entry(AuditLogEntry::new(
1219                            user.id.to_owned(),
1220                            format!("invoked `fake_delete_post` with x value `{id}`"),
1221                        ))
1222                        .await?
1223                }
1224            };
1225        }
1226
1227        if user.id != y.owner {
1228            if let Some(ref id) = y.group {
1229                if let Ok(x) = self.get_group_membership_by_owner_group(&user.id, id).await {
1230                    if x.role != GroupRole::Owner {
1231                        del!(user, self);
1232                    }
1233                } else {
1234                    del!(user, self);
1235                }
1236            } else {
1237                del!(user, self);
1238            }
1239        }
1240
1241        let conn = match self.0.connect().await {
1242            Ok(c) => c,
1243            Err(e) => return Err(Error::DatabaseConnection(e.to_string())),
1244        };
1245
1246        let res = execute!(
1247            &conn,
1248            "UPDATE posts SET is_deleted = $1 WHERE id = $2",
1249            params![&if is_deleted { 1 } else { 0 }, &id.printable()]
1250        );
1251
1252        if let Err(e) = res {
1253            return Err(Error::DatabaseError(e.to_string()));
1254        }
1255
1256        self.0.1.remove(format!("atto.post:{}", id)).await;
1257
1258        if is_deleted {
1259            // decr parent comment count
1260            if let Some(replying_to) = y.replying_to {
1261                self.decr_post_comments(&replying_to).await.unwrap();
1262            }
1263
1264            // decr user post count
1265            let owner = self.get_user_by_id(&y.owner).await?;
1266
1267            if owner.post_count > 0 {
1268                self.decr_user_post_count(&y.owner).await?;
1269            }
1270
1271            // decr question answer count
1272            if let Some(answering) = y.context.answering {
1273                let question = self.get_question_by_id(&answering).await?;
1274
1275                if question.is_global {
1276                    self.decr_question_answer_count(&answering).await?;
1277                } else {
1278                    self.delete_question(&question.id, &user).await?;
1279                }
1280            }
1281
1282            // delete uploads
1283            for upload in y.uploads {
1284                if let Err(e) = self.1.delete_upload(upload).await {
1285                    return Err(Error::MiscError(e.to_string()));
1286                }
1287            }
1288        } else {
1289            // incr parent comment count
1290            if let Some(replying_to) = y.replying_to {
1291                self.incr_post_comments(&replying_to).await.unwrap();
1292            }
1293
1294            // incr user post count
1295            self.incr_user_post_count(&y.owner).await?;
1296
1297            // incr question answer count
1298            if let Some(answering) = y.context.answering {
1299                let question = self.get_question_by_id(&answering).await?;
1300
1301                if question.is_global {
1302                    self.incr_question_answer_count(&answering).await?;
1303                }
1304            }
1305
1306            // unfortunately, uploads will not be restored
1307        }
1308
1309        // return
1310        Ok(())
1311    }
1312
1313    pub async fn update_post_context(&self, id: &Id, user: User, mut x: PostContext) -> Result<()> {
1314        let y = self.get_post_by_id(id).await?;
1315        x.repost = y.context.repost; // cannot change repost settings at all
1316        x.answering = y.context.answering; // cannot change answering settings at all
1317
1318        if user.id != y.owner {
1319            if !user.permissions.check(FinePermission::ManagePosts) {
1320                return Err(Error::NotAllowed);
1321            } else {
1322                self.create_audit_log_entry(AuditLogEntry::new(
1323                    user.id.clone(),
1324                    format!("invoked `update_post_context` with x value `{id}`"),
1325                ))
1326                .await?
1327            }
1328        }
1329
1330        // check if we can manage profile pins
1331        if (x.is_profile_pinned != y.context.is_profile_pinned) && (user.id != y.owner) {
1332            if !user.permissions.check(FinePermission::ManagePosts) {
1333                return Err(Error::NotAllowed);
1334            } else {
1335                self.create_audit_log_entry(AuditLogEntry::new(
1336                    user.id,
1337                    format!("invoked `update_post_context(profile_pinned)` with x value `{id}`"),
1338                ))
1339                .await?
1340            }
1341        }
1342
1343        // auto unlist
1344        if user.settings.auto_nsfw {
1345            x.is_nsfw = true;
1346        }
1347
1348        // ...
1349        let conn = match self.0.connect().await {
1350            Ok(c) => c,
1351            Err(e) => return Err(Error::DatabaseConnection(e.to_string())),
1352        };
1353
1354        let res = execute!(
1355            &conn,
1356            "UPDATE posts SET context = $1 WHERE id = $2",
1357            params![&serde_json::to_string(&x).unwrap(), &id.printable()]
1358        );
1359
1360        if let Err(e) = res {
1361            return Err(Error::DatabaseError(e.to_string()));
1362        }
1363
1364        self.0.1.remove(format!("atto.post:{}", id)).await;
1365
1366        // return
1367        Ok(())
1368    }
1369
1370    pub async fn update_post_content(&self, id: &Id, user: User, x: String) -> Result<()> {
1371        let mut y = self.get_post_by_id(id).await?;
1372
1373        if user.id != y.owner {
1374            if !user.permissions.check(FinePermission::ManagePosts) {
1375                return Err(Error::NotAllowed);
1376            } else {
1377                self.create_audit_log_entry(AuditLogEntry::new(
1378                    user.id.clone(),
1379                    format!("invoked `update_post_content` with x value `{id}`"),
1380                ))
1381                .await?
1382            }
1383        }
1384
1385        // check length
1386        if x.len() < 2 {
1387            return Err(Error::DataTooShort("content".to_string()));
1388        } else if x.len() > 4096 {
1389            return Err(Error::DataTooLong("content".to_string()));
1390        }
1391
1392        // ...
1393        let conn = match self.0.connect().await {
1394            Ok(c) => c,
1395            Err(e) => return Err(Error::DatabaseConnection(e.to_string())),
1396        };
1397
1398        let res = execute!(
1399            &conn,
1400            "UPDATE posts SET content = $1 WHERE id = $2",
1401            params![&x, &id.printable()]
1402        );
1403
1404        if let Err(e) = res {
1405            return Err(Error::DatabaseError(e.to_string()));
1406        }
1407
1408        // update context
1409        y.context.edited = unix_epoch_timestamp();
1410        self.update_post_context(id, user, y.context).await?;
1411
1412        // return
1413        Ok(())
1414    }
1415
1416    auto_method!(incr_post_likes() -> "UPDATE posts SET likes = likes + 1 WHERE id = $1" --cache-key-tmpl="atto.post:{}" --incr);
1417    auto_method!(incr_post_dislikes() -> "UPDATE posts SET dislikes = dislikes + 1 WHERE id = $1" --cache-key-tmpl="atto.post:{}" --incr);
1418    auto_method!(decr_post_likes() -> "UPDATE posts SET likes = likes - 1 WHERE id = $1" --cache-key-tmpl="atto.post:{}" --decr);
1419    auto_method!(decr_post_dislikes() -> "UPDATE posts SET dislikes = dislikes - 1 WHERE id = $1" --cache-key-tmpl="atto.post:{}" --decr);
1420
1421    auto_method!(incr_post_comments() -> "UPDATE posts SET comment_count = comment_count + 1 WHERE id = $1" --cache-key-tmpl="atto.post:{}" --incr);
1422    auto_method!(decr_post_comments()@get_post_by_id -> "UPDATE posts SET comment_count = comment_count - 1 WHERE id = $1" --cache-key-tmpl="atto.post:{}" --decr=comment_count);
1423
1424    auto_method!(incr_post_views() -> "UPDATE posts SET views = views + 1 WHERE id = $1" --cache-key-tmpl="atto.post:{}" --incr);
1425    auto_method!(decr_post_views()@get_post_by_id -> "UPDATE posts SET views = views - 1 WHERE id = $1" --cache-key-tmpl="atto.post:{}" --decr=views);
1426}
tetratto_core2/database/posts.rs

tetratto_core2/database/
posts.rs
