tetratto_core/database/
notes.rs

1use oiseau::cache::Cache;
2use crate::database::common::NAME_REGEX;
3use crate::model::{auth::User, journals::Note, permissions::FinePermission, Error, Result};
4use crate::{auto_method, DataManager};
5use oiseau::{execute, get, params, query_row, query_rows, PostgresRow};
6
7impl DataManager {
8    /// Get a [`Note`] from an SQL row.
9    pub(crate) fn get_note_from_row(x: &PostgresRow) -> Note {
10        Note {
11            id: get!(x->0(i64)) as usize,
12            created: get!(x->1(i64)) as usize,
13            owner: get!(x->2(i64)) as usize,
14            title: get!(x->3(String)),
15            journal: get!(x->4(i64)) as usize,
16            content: get!(x->5(String)),
17            edited: get!(x->6(i64)) as usize,
18            dir: get!(x->7(i64)) as usize,
19            tags: serde_json::from_str(&get!(x->8(String))).unwrap(),
20            is_global: get!(x->9(i32)) as i8 == 1,
21        }
22    }
23
24    auto_method!(get_note_by_id(usize as i64)@get_note_from_row -> "SELECT * FROM notes WHERE id = $1" --name="note" --returns=Note --cache-key-tmpl="atto.note:{}");
25    auto_method!(get_global_note_by_title(&str)@get_note_from_row -> "SELECT * FROM notes WHERE title = $1 AND is_global = 1" --name="note" --returns=Note --cache-key-tmpl="atto.note:{}");
26
27    /// Get the number of global notes a user has.
28    pub async fn get_user_global_notes_count(&self, owner: usize) -> Result<i32> {
29        let conn = match self.0.connect().await {
30            Ok(c) => c,
31            Err(e) => return Err(Error::DatabaseConnection(e.to_string())),
32        };
33
34        let res = query_row!(
35            &conn,
36            "SELECT COUNT(*)::int FROM notes WHERE owner = $1 AND is_global = 1",
37            &[&(owner as i64)],
38            |x| Ok(x.get::<usize, i32>(0))
39        );
40
41        if let Err(e) = res {
42            return Err(Error::DatabaseError(e.to_string()));
43        }
44
45        Ok(res.unwrap())
46    }
47
48    /// Get a note by `journal` and `title`.
49    pub async fn get_note_by_journal_title(&self, journal: usize, title: &str) -> Result<Note> {
50        let conn = match self.0.connect().await {
51            Ok(c) => c,
52            Err(e) => return Err(Error::DatabaseConnection(e.to_string())),
53        };
54
55        let res = query_row!(
56            &conn,
57            "SELECT * FROM notes WHERE journal = $1 AND title = $2",
58            params![&(journal as i64), &title],
59            |x| { Ok(Self::get_note_from_row(x)) }
60        );
61
62        if res.is_err() {
63            return Err(Error::GeneralNotFound("note".to_string()));
64        }
65
66        Ok(res.unwrap())
67    }
68
69    /// Get all notes by journal.
70    ///
71    /// # Arguments
72    /// * `id` - the ID of the journal to fetch notes for
73    pub async fn get_notes_by_journal(&self, id: usize) -> Result<Vec<Note>> {
74        let conn = match self.0.connect().await {
75            Ok(c) => c,
76            Err(e) => return Err(Error::DatabaseConnection(e.to_string())),
77        };
78
79        let res = query_rows!(
80            &conn,
81            "SELECT * FROM notes WHERE journal = $1 ORDER BY edited DESC",
82            &[&(id as i64)],
83            |x| { Self::get_note_from_row(x) }
84        );
85
86        if res.is_err() {
87            return Err(Error::GeneralNotFound("note".to_string()));
88        }
89
90        Ok(res.unwrap())
91    }
92
93    /// Get all notes by journal with the given tag.
94    ///
95    /// # Arguments
96    /// * `id` - the ID of the journal to fetch notes for
97    /// * `tag`
98    pub async fn get_notes_by_journal_tag(&self, id: usize, tag: &str) -> Result<Vec<Note>> {
99        let conn = match self.0.connect().await {
100            Ok(c) => c,
101            Err(e) => return Err(Error::DatabaseConnection(e.to_string())),
102        };
103
104        let res = query_rows!(
105            &conn,
106            "SELECT * FROM notes WHERE journal = $1 AND tags::jsonb ? $2 ORDER BY edited DESC",
107            params![&(id as i64), tag],
108            |x| { Self::get_note_from_row(x) }
109        );
110
111        if res.is_err() {
112            return Err(Error::GeneralNotFound("note".to_string()));
113        }
114
115        Ok(res.unwrap())
116    }
117
118    const MAXIMUM_FREE_NOTES_PER_JOURNAL: usize = 10;
119
120    pub const MAXIMUM_FREE_GLOBAL_NOTES: usize = 10;
121    pub const MAXIMUM_SUPPORTER_GLOBAL_NOTES: usize = 50;
122
123    /// Create a new note in the database.
124    ///
125    /// # Arguments
126    /// * `data` - a mock [`Note`] object to insert
127    pub async fn create_note(&self, mut data: Note) -> Result<Note> {
128        // check values
129        if data.title.len() < 2 {
130            return Err(Error::DataTooShort("title".to_string()));
131        } else if data.title.len() > 64 {
132            return Err(Error::DataTooLong("title".to_string()));
133        }
134
135        if data.content.len() < 2 {
136            return Err(Error::DataTooShort("content".to_string()));
137        } else if data.content.len() > 262144 {
138            return Err(Error::DataTooLong("content".to_string()));
139        }
140
141        data.title = data.title.replace(" ", "_").to_lowercase();
142
143        // check number of notes
144        let owner = self.get_user_by_id(data.owner).await?;
145
146        if !owner.permissions.check(FinePermission::SUPPORTER) {
147            let journals = self.get_notes_by_journal(data.owner).await?;
148
149            if journals.len() >= Self::MAXIMUM_FREE_NOTES_PER_JOURNAL {
150                return Err(Error::MiscError(
151                    "You already have the maximum number of notes you can have in this journal"
152                        .to_string(),
153                ));
154            }
155        }
156
157        // check name
158        let regex = regex::RegexBuilder::new(NAME_REGEX)
159            .multi_line(true)
160            .build()
161            .unwrap();
162
163        if regex.captures(&data.title).is_some() {
164            return Err(Error::MiscError(
165                "This title contains invalid characters".to_string(),
166            ));
167        }
168
169        // make sure this title isn't already in use
170        if self
171            .get_note_by_journal_title(data.journal, &data.title)
172            .await
173            .is_ok()
174        {
175            return Err(Error::TitleInUse);
176        }
177
178        // check permission
179        let journal = self.get_journal_by_id(data.journal).await?;
180
181        if data.owner != journal.owner {
182            return Err(Error::NotAllowed);
183        }
184
185        // ...
186        let conn = match self.0.connect().await {
187            Ok(c) => c,
188            Err(e) => return Err(Error::DatabaseConnection(e.to_string())),
189        };
190
191        let res = execute!(
192            &conn,
193            "INSERT INTO notes VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10)",
194            params![
195                &(data.id as i64),
196                &(data.created as i64),
197                &(data.owner as i64),
198                &data.title,
199                &(data.journal as i64),
200                &data.content,
201                &(data.edited as i64),
202                &(data.dir as i64),
203                &serde_json::to_string(&data.tags).unwrap(),
204                &if data.is_global { 1 } else { 0 }
205            ]
206        );
207
208        if let Err(e) = res {
209            return Err(Error::DatabaseError(e.to_string()));
210        }
211
212        Ok(data)
213    }
214
215    pub async fn delete_note(&self, id: usize, user: &User) -> Result<()> {
216        let note = self.get_note_by_id(id).await?;
217
218        // check user permission
219        if user.id != note.owner && !user.permissions.check(FinePermission::MANAGE_NOTES) {
220            return Err(Error::NotAllowed);
221        }
222
223        // ...
224        let conn = match self.0.connect().await {
225            Ok(c) => c,
226            Err(e) => return Err(Error::DatabaseConnection(e.to_string())),
227        };
228
229        let res = execute!(&conn, "DELETE FROM notes WHERE id = $1", &[&(id as i64)]);
230
231        if let Err(e) = res {
232            return Err(Error::DatabaseError(e.to_string()));
233        }
234
235        // ...
236        self.cache_clear_note(&note).await;
237        Ok(())
238    }
239
240    /// Delete all notes by dir ID.
241    ///
242    /// # Arguments
243    /// * `journal`
244    /// * `dir`
245    pub async fn delete_notes_by_journal_dir(
246        &self,
247        journal: usize,
248        dir: usize,
249        user: &User,
250    ) -> Result<()> {
251        let journal = self.get_journal_by_id(journal).await?;
252
253        if journal.owner != user.id && !user.permissions.check(FinePermission::MANAGE_NOTES) {
254            return Err(Error::NotAllowed);
255        }
256
257        // ...
258        let conn = match self.0.connect().await {
259            Ok(c) => c,
260            Err(e) => return Err(Error::DatabaseConnection(e.to_string())),
261        };
262
263        let res = execute!(
264            &conn,
265            "DELETE FROM notes WHERE dir = $1 AND journal = $2 ORDER BY edited DESC",
266            &[&(dir as i64), &(journal.id as i64)]
267        );
268
269        if let Err(e) = res {
270            return Err(Error::DatabaseError(e.to_string()));
271        }
272
273        Ok(())
274    }
275
276    /// Incremenet note views. Views are only stored in the cache.
277    ///
278    /// This should only be done for global notes.
279    pub async fn incr_note_views(&self, id: usize) {
280        self.0.1.incr(format!("atto.note:{id}/views")).await;
281    }
282
283    pub async fn get_note_views(&self, id: usize) -> Option<String> {
284        self.0.1.get(format!("atto.note:{id}/views")).await
285    }
286
287    pub async fn cache_clear_note(&self, x: &Note) {
288        self.0.1.remove(format!("atto.note:{}", x.id)).await;
289        self.0.1.remove(format!("atto.note:{}", x.title)).await;
290    }
291
292    auto_method!(update_note_title(&str)@get_note_by_id:FinePermission::MANAGE_NOTES; -> "UPDATE notes SET title = $1 WHERE id = $2" --cache-key-tmpl=cache_clear_note);
293    auto_method!(update_note_content(&str)@get_note_by_id:FinePermission::MANAGE_NOTES; -> "UPDATE notes SET content = $1 WHERE id = $2" --cache-key-tmpl=cache_clear_note);
294    auto_method!(update_note_dir(i64)@get_note_by_id:FinePermission::MANAGE_NOTES; -> "UPDATE notes SET dir = $1 WHERE id = $2" --cache-key-tmpl=cache_clear_note);
295    auto_method!(update_note_tags(Vec<String>)@get_note_by_id:FinePermission::MANAGE_NOTES; -> "UPDATE notes SET tags = $1 WHERE id = $2" --serde --cache-key-tmpl=cache_clear_note);
296    auto_method!(update_note_edited(i64)@get_note_by_id -> "UPDATE notes SET edited = $1 WHERE id = $2" --cache-key-tmpl=cache_clear_note);
297    auto_method!(update_note_is_global(i32)@get_note_by_id -> "UPDATE notes SET is_global = $1 WHERE id = $2" --cache-key-tmpl=cache_clear_note);
298}
tetratto_core/database/notes.rs

tetratto_core/database/
notes.rs
