backend/sqlite/
mod.rs

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

backend/sqlite/
mod.rs
