sos_database/entity/
preference.rs

1use crate::{Error, Result};
2use async_sqlite::rusqlite::{
3    Connection, Error as SqlError, OptionalExtension, Row,
4};
5use sos_core::UtcDateTime;
6use sos_preferences::Preference;
7use sql_query_builder as sql;
8use std::ops::Deref;
9
10/// Preference row from the database.
11#[doc(hidden)]
12#[derive(Debug, Default)]
13pub struct PreferenceRow {
14    pub row_id: i64,
15    created_at: String,
16    modified_at: String,
17    key: String,
18    json_data: String,
19}
20
21impl PreferenceRow {
22    /// Create a preference row to be inserted.
23    pub fn new_insert(key: &str, value: &Preference) -> Result<Self> {
24        Ok(Self {
25            created_at: UtcDateTime::default().to_rfc3339()?,
26            modified_at: UtcDateTime::default().to_rfc3339()?,
27            key: key.to_owned(),
28            json_data: serde_json::to_string(value)?,
29            ..Default::default()
30        })
31    }
32
33    /// Create a preference row to be updated.
34    pub fn new_update(key: &str, value: &Preference) -> Result<Self> {
35        Ok(Self {
36            modified_at: UtcDateTime::default().to_rfc3339()?,
37            key: key.to_owned(),
38            json_data: serde_json::to_string(value)?,
39            ..Default::default()
40        })
41    }
42}
43
44impl<'a> TryFrom<&Row<'a>> for PreferenceRow {
45    type Error = SqlError;
46    fn try_from(row: &Row<'a>) -> std::result::Result<Self, Self::Error> {
47        Ok(PreferenceRow {
48            row_id: row.get(0)?,
49            created_at: row.get(1)?,
50            modified_at: row.get(2)?,
51            key: row.get(3)?,
52            json_data: row.get(4)?,
53        })
54    }
55}
56
57impl TryFrom<PreferenceRow> for (String, Preference) {
58    type Error = Error;
59
60    fn try_from(value: PreferenceRow) -> Result<Self> {
61        let pref: Preference = serde_json::from_str(&value.json_data)?;
62        Ok((value.key, pref))
63    }
64}
65
66/// Preferences entity.
67pub struct PreferenceEntity<'conn, C>
68where
69    C: Deref<Target = Connection>,
70{
71    conn: &'conn C,
72}
73
74impl<'conn, C> PreferenceEntity<'conn, C>
75where
76    C: Deref<Target = Connection>,
77{
78    /// Create a new preference entity.
79    pub fn new(conn: &'conn C) -> Self {
80        Self { conn }
81    }
82
83    fn find_preference_select(
84        &self,
85        account_id: Option<i64>,
86        select_one: bool,
87    ) -> sql::Select {
88        let mut query = sql::Select::new()
89            .select(
90                r#"
91                    preference_id,
92                    created_at,
93                    modified_at,
94                    key,
95                    json_data
96                "#,
97            )
98            .from("preferences");
99
100        if account_id.is_some() {
101            query = query.where_clause("account_id=?1");
102            if select_one {
103                query = query.where_and("key=?2");
104            }
105        } else {
106            query = query.where_clause("account_id IS NULL");
107            if select_one {
108                query = query.where_and("key=?1");
109            }
110        }
111        query
112    }
113
114    /// Find a preference in the database.
115    pub fn find_optional(
116        &self,
117        account_id: Option<i64>,
118        key: &str,
119    ) -> std::result::Result<Option<PreferenceRow>, SqlError> {
120        let query = self.find_preference_select(account_id, true);
121        let mut stmt = self.conn.prepare_cached(&query.as_string())?;
122        if let Some(account_id) = account_id {
123            stmt.query_row((account_id, key), |row| row.try_into())
124                .optional()
125        } else {
126            stmt.query_row((key,), |row| row.try_into()).optional()
127        }
128    }
129
130    /// Load preferences from the database.
131    ///
132    /// When no `account_id` is specified the preferences
133    /// are global.
134    pub fn load_preferences(
135        &self,
136        account_id: Option<i64>,
137    ) -> Result<Vec<PreferenceRow>> {
138        let query = self.find_preference_select(account_id, false);
139        let mut stmt = self.conn.prepare_cached(&query.as_string())?;
140
141        fn convert_row(row: &Row<'_>) -> Result<PreferenceRow> {
142            Ok(row.try_into()?)
143        }
144
145        let rows = if let Some(account_id) = account_id {
146            stmt.query_and_then([account_id], convert_row)?
147        } else {
148            stmt.query_and_then([], convert_row)?
149        };
150        let mut preferences = Vec::new();
151        for row in rows {
152            preferences.push(row?);
153        }
154
155        Ok(preferences)
156    }
157
158    /// Create preferences in the database.
159    ///
160    /// When no `account_id` is specified the preferences
161    /// are global.
162    pub fn insert_preference(
163        &self,
164        account_id: Option<i64>,
165        row: &PreferenceRow,
166    ) -> std::result::Result<(), SqlError> {
167        let query = sql::Insert::new()
168            .insert_into(
169                r#"
170                preferences
171                (
172                    account_id,
173                    created_at,
174                    modified_at,
175                    key,
176                    json_data
177                )
178                "#,
179            )
180            .values("(?1, ?2, ?3, ?4, ?5)");
181        let mut stmt = self.conn.prepare_cached(&query.as_string())?;
182        stmt.execute((
183            account_id,
184            &row.created_at,
185            &row.modified_at,
186            &row.key,
187            &row.json_data,
188        ))?;
189        Ok(())
190    }
191
192    /// Create preferences in the database.
193    ///
194    /// When no `account_id` is specified the preferences
195    /// are global.
196    pub fn insert_preferences(
197        &self,
198        account_id: Option<i64>,
199        rows: &[PreferenceRow],
200    ) -> std::result::Result<(), SqlError> {
201        for row in rows {
202            self.insert_preference(account_id, row)?;
203        }
204        Ok(())
205    }
206
207    /// Update preference in the database.
208    ///
209    /// When no `account_id` is specified the preferences
210    /// are global.
211    pub fn update_preference(
212        &self,
213        account_id: Option<i64>,
214        row: &PreferenceRow,
215    ) -> std::result::Result<(), SqlError> {
216        let mut query = sql::Update::new()
217            .update("preferences")
218            .set("json_data = ?1, modified_at = ?2");
219        if account_id.is_some() {
220            query =
221                query.where_clause("account_id = ?3").where_and("key = ?4");
222        } else {
223            query = query
224                .where_clause("account_id IS NULL")
225                .where_and("key = ?3");
226        }
227        let mut stmt = self.conn.prepare_cached(&query.as_string())?;
228        if let Some(account_id) = account_id {
229            stmt.execute((
230                &row.json_data,
231                &row.modified_at,
232                account_id,
233                &row.key,
234            ))?;
235        } else {
236            stmt.execute((&row.json_data, &row.modified_at, &row.key))?;
237        }
238        Ok(())
239    }
240
241    /// Create or update preferences in the database.
242    ///
243    /// When no `account_id` is specified the preferences
244    /// are global.
245    pub fn upsert_preference(
246        &self,
247        account_id: Option<i64>,
248        row: &PreferenceRow,
249    ) -> std::result::Result<(), SqlError> {
250        let pref_row = self.find_optional(account_id, &row.key)?;
251        match pref_row {
252            Some(_) => {
253                self.update_preference(account_id, row)?;
254            }
255            None => self.insert_preference(account_id, row)?,
256        }
257        Ok(())
258    }
259
260    /// Delete a preference from the database.
261    pub fn delete_preference(
262        &self,
263        account_id: Option<i64>,
264        key: &str,
265    ) -> std::result::Result<(), SqlError> {
266        let mut query = sql::Delete::new().delete_from("preferences");
267        if account_id.is_some() {
268            query =
269                query.where_clause("account_id = ?1").where_and("key = ?2");
270        } else {
271            query = query
272                .where_clause("account_id IS NULL")
273                .where_and("key = ?1");
274        }
275        let mut stmt = self.conn.prepare_cached(&query.as_string())?;
276        if let Some(account_id) = account_id {
277            stmt.execute((account_id, key))?;
278        } else {
279            stmt.execute((key,))?;
280        }
281        Ok(())
282    }
283
284    /// Delete all preferences from the database.
285    pub fn delete_all_preferences(
286        &self,
287        account_id: Option<i64>,
288    ) -> std::result::Result<(), SqlError> {
289        let mut query = sql::Delete::new().delete_from("preferences");
290
291        if account_id.is_some() {
292            query = query.where_clause("account_id = ?1");
293        } else {
294            query = query.where_clause("account_id IS NULL");
295        }
296
297        let mut stmt = self.conn.prepare_cached(&query.as_string())?;
298        if let Some(account_id) = account_id {
299            stmt.execute([account_id])?;
300        } else {
301            stmt.execute([])?;
302        }
303        Ok(())
304    }
305}
sos_database/entity/preference.rs

sos_database/entity/
preference.rs
