backend/sqlite/
mod.rs

1use std::{path::PathBuf, str::FromStr};
2
3use self::sqlite_helper::EntryIntermediate;
4
5use super::*;
6use anyhow::anyhow;
7use path_absolutize::Absolutize;
8use sqlx::{
9    Row, Sqlite, SqlitePool,
10    migrate::MigrateDatabase,
11    sqlite::{SqliteConnectOptions, SqliteJournalMode, SqlitePoolOptions, SqliteSynchronous},
12};
13
14mod sqlite_helper;
15
16pub struct SqliteDataProvide {
17    pool: SqlitePool,
18}
19
20impl SqliteDataProvide {
21    pub async fn from_file(file_path: PathBuf) -> anyhow::Result<Self> {
22        let file_full_path = file_path.absolutize()?;
23        if !file_path.exists() {
24            if let Some(parent) = file_path.parent() {
25                tokio::fs::create_dir_all(parent).await?;
26            }
27        }
28
29        let db_url = format!("sqlite://{}", file_full_path.to_string_lossy());
30
31        SqliteDataProvide::create(&db_url).await
32    }
33
34    pub async fn create(db_url: &str) -> anyhow::Result<Self> {
35        if !Sqlite::database_exists(db_url).await? {
36            log::trace!("Creating Database with the URL '{db_url}'");
37            Sqlite::create_database(db_url)
38                .await
39                .map_err(|err| anyhow!("Creating database failed. Error info: {err}"))?;
40        }
41
42        // We are using the database as a normal file for one user.
43        // Journal mode will causes problems with the synchronisation in our case and it must be
44        // turned off
45        let options = SqliteConnectOptions::from_str(db_url)?
46            .journal_mode(SqliteJournalMode::Off)
47            .synchronous(SqliteSynchronous::Off);
48
49        let pool = SqlitePoolOptions::new().connect_with(options).await?;
50
51        sqlx::migrate!("backend/src/sqlite/migrations")
52            .run(&pool)
53            .await
54            .map_err(|err| match err {
55                sqlx::migrate::MigrateError::VersionMissing(id) => anyhow!("Database version mismatches. Error Info: migration {id} was previously applied but is missing in the resolved migrations"),
56                err => anyhow!("Error while applying migrations on database: Error info {err}"),
57            })?;
58
59        Ok(Self { pool })
60    }
61}
62
63impl DataProvider for SqliteDataProvide {
64    async fn load_all_entries(&self) -> anyhow::Result<Vec<Entry>> {
65        let entries: Vec<EntryIntermediate> = sqlx::query_as(
66            r"SELECT entries.id, entries.title, entries.date, entries.content, entries.priority, GROUP_CONCAT(tags.tag) AS tags
67            FROM entries
68            LEFT JOIN tags ON entries.id = tags.entry_id
69            GROUP BY entries.id
70            ORDER BY date DESC",
71        )
72        .fetch_all(&self.pool)
73        .await
74        .map_err(|err| {
75            log::error!("Loading entries failed. Error Info {err}");
76            anyhow!(err)
77        })?;
78
79        let entries: Vec<Entry> = entries.into_iter().map(Entry::from).collect();
80
81        Ok(entries)
82    }
83
84    async fn add_entry(&self, entry: EntryDraft) -> Result<Entry, ModifyEntryError> {
85        let row = sqlx::query(
86            r"INSERT INTO entries (title, date, content, priority)
87            VALUES($1, $2, $3, $4)
88            RETURNING id",
89        )
90        .bind(&entry.title)
91        .bind(entry.date)
92        .bind(&entry.content)
93        .bind(entry.priority)
94        .fetch_one(&self.pool)
95        .await
96        .map_err(|err| {
97            log::error!("Add entry failed. Error info: {err}");
98            anyhow!(err)
99        })?;
100
101        let id = row.get::<u32, _>(0);
102
103        for tag in entry.tags.iter() {
104            sqlx::query(
105                r"INSERT INTO tags (entry_id, tag)
106                VALUES($1, $2)",
107            )
108            .bind(id)
109            .bind(tag)
110            .execute(&self.pool)
111            .await
112            .map_err(|err| {
113                log::error!("Add entry tags failed. Error info:{err}");
114                anyhow!(err)
115            })?;
116        }
117
118        Ok(Entry::from_draft(id, entry))
119    }
120
121    async fn remove_entry(&self, entry_id: u32) -> anyhow::Result<()> {
122        sqlx::query(r"DELETE FROM entries WHERE id=$1")
123            .bind(entry_id)
124            .execute(&self.pool)
125            .await
126            .map_err(|err| {
127                log::error!("Delete entry failed. Error info: {err}");
128                anyhow!(err)
129            })?;
130
131        Ok(())
132    }
133
134    async fn update_entry(&self, entry: Entry) -> Result<Entry, ModifyEntryError> {
135        sqlx::query(
136            r"UPDATE entries
137            Set title = $1,
138                date = $2,
139                content = $3,
140                priority = $4
141            WHERE id = $5",
142        )
143        .bind(&entry.title)
144        .bind(entry.date)
145        .bind(&entry.content)
146        .bind(entry.priority)
147        .bind(entry.id)
148        .execute(&self.pool)
149        .await
150        .map_err(|err| {
151            log::error!("Update entry failed. Error info {err}");
152            anyhow!(err)
153        })?;
154
155        let existing_tags: Vec<String> = sqlx::query_scalar(
156            r"SELECT tag FROM tags 
157            WHERE entry_id = $1",
158        )
159        .bind(entry.id)
160        .fetch_all(&self.pool)
161        .await
162        .map_err(|err| {
163            log::error!("Update entry tags failed. Error info {err}");
164            anyhow!(err)
165        })?;
166
167        // Tags to remove
168        for tag_to_remove in existing_tags.iter().filter(|tag| !entry.tags.contains(tag)) {
169            sqlx::query(r"DELETE FROM tags Where entry_id = $1 AND tag = $2")
170                .bind(entry.id)
171                .bind(tag_to_remove)
172                .execute(&self.pool)
173                .await
174                .map_err(|err| {
175                    log::error!("Update entry tags failed. Error info {err}");
176                    anyhow!(err)
177                })?;
178        }
179
180        // Tags to insert
181        for tag_to_insert in entry.tags.iter().filter(|tag| !existing_tags.contains(tag)) {
182            sqlx::query(
183                r"INSERT INTO tags (entry_id, tag)
184                VALUES ($1, $2)",
185            )
186            .bind(entry.id)
187            .bind(tag_to_insert)
188            .execute(&self.pool)
189            .await
190            .map_err(|err| {
191                log::error!("Update entry tags failed. Error info {err}");
192                anyhow!(err)
193            })?;
194        }
195
196        Ok(entry)
197    }
198
199    async fn get_export_object(&self, entries_ids: &[u32]) -> anyhow::Result<EntriesDTO> {
200        let ids_text = entries_ids
201            .iter()
202            .map(|id| id.to_string())
203            .collect::<Vec<String>>()
204            .join(", ");
205
206        let sql = format!(
207            r"SELECT entries.id, entries.title, entries.date, entries.content, entries.priority, GROUP_CONCAT(tags.tag) AS tags
208            FROM entries
209            LEFT JOIN tags ON entries.id = tags.entry_id
210            WHERE entries.id IN ({ids_text})
211            GROUP BY entries.id
212            ORDER BY date DESC"
213        );
214
215        let entries: Vec<EntryIntermediate> = sqlx::query_as(sql.as_str())
216            .fetch_all(&self.pool)
217            .await
218            .map_err(|err| {
219                log::error!("Loading entries failed. Error Info {err}");
220                anyhow!(err)
221            })?;
222
223        let entry_drafts = entries
224            .into_iter()
225            .map(Entry::from)
226            .map(EntryDraft::from_entry)
227            .collect();
228
229        Ok(EntriesDTO::new(entry_drafts))
230    }
231
232    async fn assign_priority_to_entries(&self, priority: u32) -> anyhow::Result<()> {
233        let sql = format!(
234            r"UPDATE entries
235            SET priority = '{priority}'
236            WHERE priority IS NULL;"
237        );
238
239        sqlx::query(sql.as_str())
240            .execute(&self.pool)
241            .await
242            .map_err(|err| {
243                log::error!("Assign priority to entries failed. Error info {err}");
244
245                anyhow!(err)
246            })?;
247
248        Ok(())
249    }
250}
backend/sqlite/mod.rs

backend/sqlite/
mod.rs
