tetratto_core/database/
questions.rs

1use std::collections::HashMap;
2use oiseau::cache::Cache;
3use tetratto_shared::unix_epoch_timestamp;
4use crate::model::addr::RemoteAddr;
5use crate::model::communities::Post;
6use crate::model::communities_permissions::CommunityPermission;
7use buckets_core::model::{MediaType, MediaUpload};
8use crate::model::{
9    Error, Result,
10    communities::Question,
11    requests::{ActionRequest, ActionType},
12    auth::User,
13    permissions::FinePermission,
14};
15use crate::{auto_method, DataManager};
16use oiseau::{execute, get, query_rows, params, PostgresRow};
17
18impl DataManager {
19    /// Get a [`Question`] from an SQL row.
20    pub(crate) fn get_question_from_row(x: &PostgresRow) -> Question {
21        Question {
22            id: get!(x->0(i64)) as usize,
23            created: get!(x->1(i64)) as usize,
24            owner: get!(x->2(i64)) as usize,
25            receiver: get!(x->3(i64)) as usize,
26            content: get!(x->4(String)),
27            is_global: get!(x->5(i32)) as i8 == 1,
28            answer_count: get!(x->6(i32)) as usize,
29            community: get!(x->7(i64)) as usize,
30            // likes
31            likes: get!(x->8(i32)) as isize,
32            dislikes: get!(x->9(i32)) as isize,
33            // ...
34            context: serde_json::from_str(&get!(x->10(String))).unwrap(),
35            ip: get!(x->11(String)),
36            drawings: serde_json::from_str(&get!(x->12(String))).unwrap(),
37        }
38    }
39
40    auto_method!(get_question_by_id()@get_question_from_row -> "SELECT * FROM questions WHERE id = $1" --name="question" --returns=Question --cache-key-tmpl="atto.question:{}");
41
42    /// Get the post a given question is asking about.
43    pub async fn get_question_asking_about(
44        &self,
45        question: &Question,
46    ) -> Result<Option<(User, Post)>> {
47        Ok(if let Some(id) = question.context.asking_about {
48            let post = match self.get_post_by_id(id).await {
49                Ok(x) => x,
50                Err(_) => return Ok(None),
51            };
52
53            Some((self.get_user_by_id(post.owner).await?, post))
54        } else {
55            None
56        })
57    }
58
59    /// Fill the given vector of questions with their owner as well.
60    pub async fn fill_questions(
61        &self,
62        questions: Vec<Question>,
63        ignore_users: &[usize],
64    ) -> Result<Vec<(Question, User, Option<(User, Post)>)>> {
65        let mut out: Vec<(Question, User, Option<(User, Post)>)> = Vec::new();
66
67        let mut seen_users: HashMap<usize, User> = HashMap::new();
68        for question in questions {
69            if ignore_users.contains(&question.owner) {
70                continue;
71            }
72
73            if let Some(ua) = seen_users.get(&question.owner) {
74                let asking_about = self.get_question_asking_about(&question).await?;
75                out.push((question, ua.to_owned(), asking_about));
76            } else {
77                let user = if question.owner == 0 {
78                    User::anonymous()
79                } else {
80                    self.get_user_by_id_with_void(question.owner).await?
81                };
82
83                seen_users.insert(question.owner, user.clone());
84
85                let asking_about = self.get_question_asking_about(&question).await?;
86                out.push((question, user, asking_about));
87            }
88        }
89
90        Ok(out)
91    }
92
93    /// Filter to update questions to clean their owner for public APIs.
94    pub fn questions_owner_filter(
95        &self,
96        questions: &[(Question, User, Option<(User, Post)>)],
97    ) -> Vec<(Question, User, Option<(User, Post)>)> {
98        let mut out: Vec<(Question, User, Option<(User, Post)>)> = Vec::new();
99
100        for mut question in questions.to_owned() {
101            question.1.clean();
102
103            if question.2.is_some() {
104                question.2.as_mut().unwrap().0.clean();
105            }
106
107            out.push(question);
108        }
109
110        out
111    }
112
113    /// Get all questions by `owner`.
114    pub async fn get_questions_by_owner(&self, owner: usize) -> Result<Vec<Question>> {
115        let conn = match self.0.connect().await {
116            Ok(c) => c,
117            Err(e) => return Err(Error::DatabaseConnection(e.to_string())),
118        };
119
120        let res = query_rows!(
121            &conn,
122            "SELECT * FROM questions WHERE owner = $1 AND NOT context LIKE '%\"is_nsfw\":true%' ORDER BY created DESC",
123            &[&(owner as i64)],
124            |x| { Self::get_question_from_row(x) }
125        );
126
127        if res.is_err() {
128            return Err(Error::GeneralNotFound("question".to_string()));
129        }
130
131        Ok(res.unwrap())
132    }
133
134    /// Get all questions by `owner` (paginated).
135    pub async fn get_questions_by_owner_paginated(
136        &self,
137        owner: usize,
138        batch: usize,
139        page: usize,
140    ) -> Result<Vec<Question>> {
141        let conn = match self.0.connect().await {
142            Ok(c) => c,
143            Err(e) => return Err(Error::DatabaseConnection(e.to_string())),
144        };
145
146        let res = query_rows!(
147            &conn,
148            "SELECT * FROM questions WHERE owner = $1 AND NOT context LIKE '%\"is_nsfw\":true%' ORDER BY created DESC LIMIT $2 OFFSET $3",
149            &[&(owner as i64), &(batch as i64), &((page * batch) as i64)],
150            |x| { Self::get_question_from_row(x) }
151        );
152
153        if res.is_err() {
154            return Err(Error::GeneralNotFound("question".to_string()));
155        }
156
157        Ok(res.unwrap())
158    }
159
160    /// Get all questions by `receiver`.
161    pub async fn get_questions_by_receiver(&self, receiver: usize) -> Result<Vec<Question>> {
162        let conn = match self.0.connect().await {
163            Ok(c) => c,
164            Err(e) => return Err(Error::DatabaseConnection(e.to_string())),
165        };
166
167        let res = query_rows!(
168            &conn,
169            "SELECT * FROM questions WHERE receiver = $1 ORDER BY created DESC",
170            &[&(receiver as i64)],
171            |x| { Self::get_question_from_row(x) }
172        );
173
174        if res.is_err() {
175            return Err(Error::GeneralNotFound("question".to_string()));
176        }
177
178        Ok(res.unwrap())
179    }
180
181    /// Get all global questions by `community`.
182    pub async fn get_questions_by_community(
183        &self,
184        community: usize,
185        batch: usize,
186        page: usize,
187    ) -> Result<Vec<Question>> {
188        let conn = match self.0.connect().await {
189            Ok(c) => c,
190            Err(e) => return Err(Error::DatabaseConnection(e.to_string())),
191        };
192
193        let res = query_rows!(
194            &conn,
195            "SELECT * FROM questions WHERE community = $1 AND is_global = 1 ORDER BY created DESC LIMIT $2 OFFSET $3",
196            &[
197                &(community as i64),
198                &(batch as i64),
199                &((page * batch) as i64)
200            ],
201            |x| { Self::get_question_from_row(x) }
202        );
203
204        if res.is_err() {
205            return Err(Error::GeneralNotFound("question".to_string()));
206        }
207
208        Ok(res.unwrap())
209    }
210
211    /// Get all global questions by the given user's following.
212    pub async fn get_questions_from_user_following(
213        &self,
214        id: usize,
215        batch: usize,
216        page: usize,
217    ) -> Result<Vec<Question>> {
218        let following = self.get_userfollows_by_initiator_all(id).await?;
219        let mut following = following.iter();
220        let first = match following.next() {
221            Some(f) => f,
222            None => return Ok(Vec::new()),
223        };
224
225        let mut query_string: String = String::new();
226
227        for user in following {
228            query_string.push_str(&format!(" OR owner = {}", user.receiver));
229        }
230
231        // ...
232        let conn = match self.0.connect().await {
233            Ok(c) => c,
234            Err(e) => return Err(Error::DatabaseConnection(e.to_string())),
235        };
236
237        let res = query_rows!(
238            &conn,
239            &format!(
240                "SELECT * FROM questions WHERE (owner = {} {query_string}) AND is_global = 1 ORDER BY created DESC LIMIT $1 OFFSET $2",
241                first.receiver
242            ),
243            &[&(batch as i64), &((page * batch) as i64)],
244            |x| { Self::get_question_from_row(x) }
245        );
246
247        if res.is_err() {
248            return Err(Error::GeneralNotFound("question".to_string()));
249        }
250
251        Ok(res.unwrap())
252    }
253
254    /// Get all global questions posted in the given user's communities.
255    pub async fn get_questions_from_user_communities(
256        &self,
257        id: usize,
258        batch: usize,
259        page: usize,
260    ) -> Result<Vec<Question>> {
261        let memberships = self.get_memberships_by_owner(id).await?;
262        let mut memberships = memberships.iter();
263        let first = match memberships.next() {
264            Some(f) => f,
265            None => return Ok(Vec::new()),
266        };
267
268        let mut query_string: String = String::new();
269
270        for membership in memberships {
271            query_string.push_str(&format!(" OR community = {}", membership.community));
272        }
273
274        // ...
275        let conn = match self.0.connect().await {
276            Ok(c) => c,
277            Err(e) => return Err(Error::DatabaseConnection(e.to_string())),
278        };
279
280        let res = query_rows!(
281            &conn,
282            &format!(
283                "SELECT * FROM questions WHERE (community = {} {query_string}) AND is_global = 1 AND NOT context LIKE '%\"is_nsfw\":true%' ORDER BY created DESC LIMIT $1 OFFSET $2",
284                first.community
285            ),
286            &[&(batch as i64), &((page * batch) as i64)],
287            |x| { Self::get_question_from_row(x) }
288        );
289
290        if res.is_err() {
291            return Err(Error::GeneralNotFound("question".to_string()));
292        }
293
294        Ok(res.unwrap())
295    }
296
297    /// Get global questions from all communities, sorted by creation.
298    ///
299    /// # Arguments
300    /// * `batch` - the limit of questions in each page
301    /// * `page` - the page number
302    pub async fn get_latest_global_questions(
303        &self,
304        batch: usize,
305        page: usize,
306    ) -> Result<Vec<Question>> {
307        let conn = match self.0.connect().await {
308            Ok(c) => c,
309            Err(e) => return Err(Error::DatabaseConnection(e.to_string())),
310        };
311
312        let res = query_rows!(
313            &conn,
314            "SELECT * FROM questions WHERE is_global = 1 ORDER BY created DESC LIMIT $1 OFFSET $2",
315            &[&(batch as i64), &((page * batch) as i64)],
316            |x| { Self::get_question_from_row(x) }
317        );
318
319        if res.is_err() {
320            return Err(Error::GeneralNotFound("question".to_string()));
321        }
322
323        Ok(res.unwrap())
324    }
325
326    /// Get global questions from all communities, sorted by likes.
327    ///
328    /// # Arguments
329    /// * `batch` - the limit of questions in each page
330    /// * `page` - the page number
331    /// * `cutoff` - the maximum number of milliseconds ago the question could have been created
332    pub async fn get_popular_global_questions(
333        &self,
334        batch: usize,
335        page: usize,
336        cutoff: usize,
337    ) -> Result<Vec<Question>> {
338        let conn = match self.0.connect().await {
339            Ok(c) => c,
340            Err(e) => return Err(Error::DatabaseConnection(e.to_string())),
341        };
342
343        let res = query_rows!(
344            &conn,
345            "SELECT * FROM questions WHERE is_global = 1 AND NOT context LIKE '%\"is_nsfw\":true%' AND ($1 - created) < $2 ORDER BY likes - dislikes DESC, created ASC LIMIT $3 OFFSET $4",
346            &[
347                &(unix_epoch_timestamp() as i64),
348                &(cutoff as i64),
349                &(batch as i64),
350                &((page * batch) as i64)
351            ],
352            |x| { Self::get_question_from_row(x) }
353        );
354
355        if res.is_err() {
356            return Err(Error::GeneralNotFound("question".to_string()));
357        }
358
359        Ok(res.unwrap())
360    }
361
362    const MAXIMUM_DRAWING_SIZE: usize = 32768; // 32 KiB
363
364    /// Create a new question in the database.
365    ///
366    /// # Arguments
367    /// * `data` - a mock [`Question`] object to insert
368    pub async fn create_question(
369        &self,
370        mut data: Question,
371        drawings: Vec<Vec<u8>>,
372    ) -> Result<usize> {
373        // check if we can post this
374        if data.is_global {
375            // global
376            if data.community > 0 {
377                // posting to community
378                data.receiver = 0;
379                let community = self.get_community_by_id(data.community).await?;
380
381                if !community.context.enable_questions
382                    | !self.check_can_post(&community, data.owner).await
383                {
384                    return Err(Error::QuestionsDisabled);
385                }
386
387                // inherit nsfw status
388                data.context.is_nsfw = community.context.is_nsfw;
389            } else {
390                // this should be unreachable
391                return Err(Error::Unknown);
392            }
393        } else {
394            // single
395            let receiver = self.get_user_by_id(data.receiver).await?;
396
397            if !receiver.settings.enable_questions {
398                return Err(Error::QuestionsDisabled);
399            }
400
401            if !receiver.settings.allow_anonymous_questions && data.owner == 0 {
402                return Err(Error::NotAllowed);
403            }
404
405            if !receiver.settings.enable_drawings && !drawings.is_empty() {
406                return Err(Error::DrawingsDisabled);
407            }
408
409            // check muted phrases
410            for phrase in receiver.settings.muted {
411                if phrase.is_empty() {
412                    continue;
413                }
414
415                if data.content.contains(&phrase) {
416                    // act like the question was created so theyre less likely to try and send it again or bypass
417                    return Ok(0);
418                }
419            }
420
421            // check for ip block
422            if self
423                .get_ipblock_by_initiator_receiver(receiver.id, &RemoteAddr::from(data.ip.as_str()))
424                .await
425                .is_ok()
426            {
427                return Err(Error::NotAllowed);
428            }
429        }
430
431        // check asking_about
432        if let Some(id) = data.context.asking_about {
433            let post = self.get_post_by_id(id).await?;
434            let owner = self.get_user_by_id(post.owner).await?;
435
436            if post.stack != 0 {
437                return Err(Error::MiscError(
438                    "Cannot ask about posts in a circle".to_string(),
439                ));
440            } else if owner.settings.private_profile {
441                return Err(Error::MiscError(
442                    "Cannot ask about posts from a private user".to_string(),
443                ));
444            }
445        }
446
447        // create uploads
448        if drawings.len() > 2 {
449            return Err(Error::MiscError(
450                "Too many uploads. Please use a maximum of 2".to_string(),
451            ));
452        }
453
454        for drawing in &drawings {
455            // this is the initial iter to check sizes, we'll do uploads after
456            if drawing.len() > Self::MAXIMUM_DRAWING_SIZE {
457                return Err(Error::FileTooLarge);
458            } else if drawing.len() < 25 {
459                // if we have less than 25 bytes in a drawing, the drawing is likely blank
460                return Err(Error::FileTooSmall);
461            }
462        }
463
464        for _ in 0..drawings.len() {
465            data.drawings.push(
466                match self
467                    .2
468                    .create_upload(MediaUpload::new(
469                        MediaType::Carpgraph,
470                        data.owner,
471                        "drawings".to_string(),
472                    ))
473                    .await
474                {
475                    Ok(x) => x.id,
476                    Err(_) => continue,
477                },
478            );
479        }
480
481        // ...
482        let conn = match self.0.connect().await {
483            Ok(c) => c,
484            Err(e) => return Err(Error::DatabaseConnection(e.to_string())),
485        };
486
487        let res = execute!(
488            &conn,
489            "INSERT INTO questions VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13)",
490            params![
491                &(data.id as i64),
492                &(data.created as i64),
493                &(data.owner as i64),
494                &(data.receiver as i64),
495                &data.content,
496                &{ if data.is_global { 1 } else { 0 } },
497                &0_i32,
498                &(data.community as i64),
499                &0_i32,
500                &0_i32,
501                &serde_json::to_string(&data.context).unwrap(),
502                &data.ip,
503                &serde_json::to_string(&data.drawings).unwrap(),
504            ]
505        );
506
507        if let Err(e) = res {
508            return Err(Error::DatabaseError(e.to_string()));
509        }
510
511        // create request
512        if !data.is_global {
513            self.create_request(ActionRequest::with_id(
514                data.id,
515                data.receiver,
516                ActionType::Answer,
517                data.id,
518                None,
519            ))
520            .await?;
521        }
522
523        // write to uploads
524        for (i, drawing_id) in data.drawings.iter().enumerate() {
525            let drawing = match drawings.get(i) {
526                Some(d) => d,
527                None => {
528                    if let Err(e) = self.2.delete_upload(*drawing_id).await {
529                        return Err(Error::MiscError(e.to_string()));
530                    }
531
532                    continue;
533                }
534            };
535
536            let upload = match self.2.get_upload_by_id(*drawing_id).await {
537                Ok(x) => x,
538                Err(e) => return Err(Error::MiscError(e.to_string())),
539            };
540
541            if let Err(e) = std::fs::write(upload.path(&self.2.0.0.directory).to_string(), drawing)
542            {
543                return Err(Error::MiscError(e.to_string()));
544            }
545        }
546
547        // return
548        Ok(data.id)
549    }
550
551    pub async fn delete_question(&self, id: usize, user: &User) -> Result<()> {
552        let y = self.get_question_by_id(id).await?;
553
554        if user.id != y.owner
555            && user.id != y.receiver
556            && !user.permissions.check(FinePermission::MANAGE_QUESTIONS)
557        {
558            if y.community != 0 {
559                // check for MANAGE_QUESTIONS permission
560                let membership = self
561                    .get_membership_by_owner_community_no_void(user.id, y.community)
562                    .await?;
563
564                if !membership.role.check(CommunityPermission::MANAGE_QUESTIONS) {
565                    return Err(Error::NotAllowed);
566                }
567            } else {
568                return Err(Error::NotAllowed);
569            }
570        }
571
572        let conn = match self.0.connect().await {
573            Ok(c) => c,
574            Err(e) => return Err(Error::DatabaseConnection(e.to_string())),
575        };
576
577        let res = execute!(
578            &conn,
579            "DELETE FROM questions WHERE id = $1",
580            &[&(id as i64)]
581        );
582
583        if let Err(e) = res {
584            return Err(Error::DatabaseError(e.to_string()));
585        }
586
587        self.0.1.remove(format!("atto.question:{}", id)).await;
588
589        // delete request (if it exists and question isn't global)
590        if !y.is_global
591            && self
592                .get_request_by_id_linked_asset(y.id, y.id)
593                .await
594                .is_ok()
595        {
596            // requests are also deleted when a post is created answering the given question
597            // (unless the question is global)
598            self.delete_request(y.id, y.id, user, false).await?;
599        }
600
601        // delete all posts answering question
602        let res = execute!(
603            &conn,
604            "DELETE FROM posts WHERE context LIKE $1",
605            &[&format!("%\"answering\":{id}%")]
606        );
607
608        if let Err(e) = res {
609            return Err(Error::DatabaseError(e.to_string()));
610        }
611
612        // delete uploads
613        for upload in y.drawings {
614            if let Err(e) = self.2.delete_upload(upload).await {
615                return Err(Error::MiscError(e.to_string()));
616            }
617        }
618
619        // return
620        Ok(())
621    }
622
623    pub async fn delete_all_questions(&self, user: &User) -> Result<()> {
624        let y = self.get_questions_by_receiver(user.id).await?;
625
626        for x in y {
627            if user.id != x.receiver && !user.permissions.check(FinePermission::MANAGE_QUESTIONS) {
628                return Err(Error::NotAllowed);
629            }
630
631            self.delete_question(x.id, user).await?
632        }
633
634        Ok(())
635    }
636
637    auto_method!(incr_question_answer_count() -> "UPDATE questions SET answer_count = answer_count + 1 WHERE id = $1" --cache-key-tmpl="atto.question:{}" --incr);
638    auto_method!(decr_question_answer_count() -> "UPDATE questions SET answer_count = answer_count - 1 WHERE id = $1" --cache-key-tmpl="atto.question:{}" --decr);
639
640    auto_method!(incr_question_likes() -> "UPDATE questions SET likes = likes + 1 WHERE id = $1" --cache-key-tmpl="atto.question:{}" --incr);
641    auto_method!(incr_question_dislikes() -> "UPDATE questions SET dislikes = dislikes + 1 WHERE id = $1" --cache-key-tmpl="atto.question:{}" --incr);
642    auto_method!(decr_question_likes() -> "UPDATE questions SET likes = likes - 1 WHERE id = $1" --cache-key-tmpl="atto.question:{}" --decr);
643    auto_method!(decr_question_dislikes() -> "UPDATE questions SET dislikes = dislikes - 1 WHERE id = $1" --cache-key-tmpl="atto.question:{}" --decr);
644}
tetratto_core/database/questions.rs

tetratto_core/database/
questions.rs
