tetratto_core2/database/
questions.rs

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

tetratto_core2/database/
questions.rs
