client_core/store/
queries.rs

1use super::models::{AlertPref, RetentionPref, SourceRow, StoredClip, StoredDevice};
2use super::{Store, StoreError};
3use rusqlite::params;
4
5pub fn insert_clip(store: &Store, c: &StoredClip) -> Result<(), StoreError> {
6    store.with_conn(|conn| {
7        conn.execute(
8            r#"INSERT OR REPLACE INTO clips
9               (id, source, source_key, content_type, content, media_path, byte_size, created_at, pinned, pinned_at)
10               VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9, ?10)"#,
11            params![
12                c.id, c.source, c.source_key, c.content_type, c.content,
13                c.media_path, c.byte_size, c.created_at,
14                if c.pinned { 1i64 } else { 0 }, c.pinned_at,
15            ],
16        )?;
17        Ok(())
18    })
19}
20
21pub fn list_clips(
22    store: &Store,
23    from: Option<&str>,
24    limit: Option<i64>,
25    since_ms: Option<i64>,
26    pinned_only: bool,
27    default_limit: i64,
28) -> Result<Vec<StoredClip>, StoreError> {
29    let mut sql = String::from(
30        "SELECT id, source, source_key, content_type, content, media_path, byte_size, created_at, pinned, pinned_at
31         FROM clips WHERE 1=1"
32    );
33    let mut binds: Vec<Box<dyn rusqlite::ToSql>> = Vec::new();
34    if let Some(s) = from {
35        sql.push_str(" AND source = ?");
36        binds.push(Box::new(s.to_string()));
37    }
38    if let Some(t) = since_ms {
39        sql.push_str(" AND created_at >= ?");
40        binds.push(Box::new(t));
41    }
42    if pinned_only {
43        sql.push_str(" AND pinned = 1");
44    }
45    sql.push_str(" ORDER BY created_at DESC LIMIT ?");
46    binds.push(Box::new(limit.unwrap_or(default_limit)));
47
48    store.with_conn(|conn| {
49        let mut stmt = conn.prepare(&sql)?;
50        let rows: Vec<StoredClip> = stmt
51            .query_map(
52                rusqlite::params_from_iter(binds.iter().map(|b| &**b as &dyn rusqlite::ToSql)),
53                |r| {
54                    Ok(StoredClip {
55                        id: r.get(0)?,
56                        source: r.get(1)?,
57                        source_key: r.get(2)?,
58                        content_type: r.get(3)?,
59                        content: r.get(4)?,
60                        media_path: r.get(5)?,
61                        byte_size: r.get(6)?,
62                        created_at: r.get(7)?,
63                        pinned: r.get::<_, i64>(8)? != 0,
64                        pinned_at: r.get(9)?,
65                    })
66                },
67            )?
68            .filter_map(|r| r.ok())
69            .collect();
70        Ok(rows)
71    })
72}
73
74pub fn get_clip(store: &Store, id: &str) -> Result<Option<StoredClip>, StoreError> {
75    store.with_conn(|conn| {
76        let mut stmt = conn.prepare(
77            "SELECT id, source, source_key, content_type, content, media_path, byte_size, created_at, pinned, pinned_at
78             FROM clips WHERE id = ?1"
79        )?;
80        let mut rows = stmt.query_map(params![id], |r| Ok(StoredClip {
81            id: r.get(0)?, source: r.get(1)?, source_key: r.get(2)?,
82            content_type: r.get(3)?, content: r.get(4)?, media_path: r.get(5)?,
83            byte_size: r.get(6)?, created_at: r.get(7)?,
84            pinned: r.get::<_, i64>(8)? != 0, pinned_at: r.get(9)?,
85        }))?;
86        if let Some(row) = rows.next() { Ok(Some(row?)) } else { Ok(None) }
87    })
88}
89
90pub fn delete_clip(store: &Store, id: &str) -> Result<(), StoreError> {
91    store.with_conn(|conn| {
92        conn.execute("DELETE FROM clips WHERE id = ?1", params![id])?;
93        Ok(())
94    })
95}
96
97pub fn set_pinned(store: &Store, id: &str, pinned: bool, when_ms: i64) -> Result<(), StoreError> {
98    store.with_conn(|conn| {
99        conn.execute(
100            "UPDATE clips SET pinned = ?1, pinned_at = CASE WHEN ?1 = 1 THEN ?2 ELSE NULL END WHERE id = ?3",
101            params![if pinned { 1i64 } else { 0 }, when_ms, id],
102        )?;
103        Ok(())
104    })
105}
106
107pub fn list_sources(store: &Store) -> Result<Vec<SourceRow>, StoreError> {
108    store.with_conn(|conn| {
109        let mut stmt = conn.prepare(
110            "SELECT source, COUNT(*) AS c, MAX(created_at) AS last_seen
111             FROM clips GROUP BY source ORDER BY last_seen DESC NULLS LAST",
112        )?;
113        let rows: Vec<SourceRow> = stmt
114            .query_map([], |r| {
115                Ok(SourceRow {
116                    source: r.get(0)?,
117                    clip_count: r.get(1)?,
118                    last_seen: r.get(2)?,
119                })
120            })?
121            .filter_map(|r| r.ok())
122            .collect();
123        Ok(rows)
124    })
125}
126
127pub fn upsert_device(store: &Store, d: &StoredDevice) -> Result<(), StoreError> {
128    store.with_conn(|conn| {
129        conn.execute(
130            r#"INSERT OR REPLACE INTO devices
131               (id, hostname, nickname, source_key, machine_id, public_key,
132                paired_at, last_push_at, online, refreshed_at)
133               VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9, ?10)"#,
134            params![
135                d.id,
136                d.hostname,
137                d.nickname,
138                d.source_key,
139                d.machine_id,
140                d.public_key,
141                d.paired_at,
142                d.last_push_at,
143                if d.online { 1i64 } else { 0 },
144                d.refreshed_at,
145            ],
146        )?;
147        Ok(())
148    })
149}
150
151pub fn list_devices(store: &Store) -> Result<Vec<StoredDevice>, StoreError> {
152    store.with_conn(|conn| {
153        let mut stmt = conn.prepare(
154            "SELECT id, hostname, nickname, source_key, machine_id, public_key,
155                    paired_at, last_push_at, online, refreshed_at
156             FROM devices ORDER BY last_push_at DESC NULLS LAST",
157        )?;
158        let rows: Vec<StoredDevice> = stmt
159            .query_map([], |r| {
160                Ok(StoredDevice {
161                    id: r.get(0)?,
162                    hostname: r.get(1)?,
163                    nickname: r.get(2)?,
164                    source_key: r.get(3)?,
165                    machine_id: r.get(4)?,
166                    public_key: r.get(5)?,
167                    paired_at: r.get(6)?,
168                    last_push_at: r.get(7)?,
169                    online: r.get::<_, i64>(8)? != 0,
170                    refreshed_at: r.get(9)?,
171                })
172            })?
173            .filter_map(|r| r.ok())
174            .collect();
175        Ok(rows)
176    })
177}
178
179pub fn set_retention(store: &Store, device_id: &str, days: i64) -> Result<(), StoreError> {
180    store.with_conn(|conn| {
181        conn.execute(
182            "INSERT INTO retention_prefs(device_id, days) VALUES(?1, ?2)
183             ON CONFLICT(device_id) DO UPDATE SET days = excluded.days",
184            params![device_id, days],
185        )?;
186        Ok(())
187    })
188}
189
190pub fn list_retention(store: &Store) -> Result<Vec<RetentionPref>, StoreError> {
191    store.with_conn(|conn| {
192        let mut stmt = conn.prepare("SELECT device_id, days FROM retention_prefs")?;
193        let rows: Vec<RetentionPref> = stmt
194            .query_map([], |r| {
195                Ok(RetentionPref {
196                    device_id: r.get(0)?,
197                    days: r.get(1)?,
198                })
199            })?
200            .filter_map(|r| r.ok())
201            .collect();
202        Ok(rows)
203    })
204}
205
206pub fn set_alert_pref(store: &Store, source: &str, enabled: bool) -> Result<(), StoreError> {
207    store.with_conn(|conn| {
208        conn.execute(
209            "INSERT INTO alert_prefs(source, enabled) VALUES(?1, ?2)
210             ON CONFLICT(source) DO UPDATE SET enabled = excluded.enabled",
211            params![source, if enabled { 1i64 } else { 0 }],
212        )?;
213        Ok(())
214    })
215}
216
217pub fn list_alert_prefs(store: &Store) -> Result<Vec<AlertPref>, StoreError> {
218    store.with_conn(|conn| {
219        let mut stmt = conn.prepare("SELECT source, enabled FROM alert_prefs")?;
220        let rows: Vec<AlertPref> = stmt
221            .query_map([], |r| {
222                Ok(AlertPref {
223                    source: r.get(0)?,
224                    enabled: r.get::<_, i64>(1)? != 0,
225                })
226            })?
227            .filter_map(|r| r.ok())
228            .collect();
229        Ok(rows)
230    })
231}
232
233pub fn watermark(store: &Store) -> Result<Option<String>, StoreError> {
234    store.with_conn(|conn| {
235        conn.query_row(
236            "SELECT value FROM meta WHERE key='last_sync_watermark'",
237            [],
238            |r| r.get::<_, String>(0),
239        )
240        .map(Some)
241        .or_else(|e| match e {
242            rusqlite::Error::QueryReturnedNoRows => Ok(None),
243            other => Err(other),
244        })
245    })
246}
247
248pub fn set_watermark(store: &Store, ulid: &str) -> Result<(), StoreError> {
249    store.with_conn(|conn| {
250        conn.execute(
251            "INSERT INTO meta(key, value) VALUES('last_sync_watermark', ?1)
252             ON CONFLICT(key) DO UPDATE SET value = excluded.value",
253            params![ulid],
254        )?;
255        Ok(())
256    })
257}
258
259/// Return the total number of clips in the store.
260pub fn clip_count(store: &Store) -> Result<i64, StoreError> {
261    store.with_conn(|conn| conn.query_row("SELECT COUNT(*) FROM clips", [], |r| r.get::<_, i64>(0)))
262}
263
264/// Return the number of clips whose `created_at` (ms) is before `cutoff_ms`.
265/// Used by the Settings pane "preview retention change" dialog.
266pub fn count_clips_before(store: &Store, cutoff_ms: i64) -> Result<i64, StoreError> {
267    store.with_conn(|conn| {
268        conn.query_row(
269            "SELECT COUNT(*) FROM clips WHERE created_at < ?1",
270            rusqlite::params![cutoff_ms],
271            |r| r.get::<_, i64>(0),
272        )
273    })
274}
275
276/// Delete all clips from the store. Returns the number of rows deleted.
277pub fn clear_all_clips(store: &Store) -> Result<i64, StoreError> {
278    store.with_conn(|conn| {
279        let n = conn.execute("DELETE FROM clips", [])?;
280        Ok(n as i64)
281    })
282}
283
284pub fn search_clips(store: &Store, query: &str, limit: i64) -> Result<Vec<StoredClip>, StoreError> {
285    store.with_conn(|conn| {
286        let mut stmt = conn.prepare(
287            "SELECT c.id, c.source, c.source_key, c.content_type, c.content, c.media_path,
288                    c.byte_size, c.created_at, c.pinned, c.pinned_at
289             FROM clips c JOIN clips_fts f ON f.rowid = c.rowid
290             WHERE clips_fts MATCH ?1 ORDER BY rank LIMIT ?2",
291        )?;
292        let rows: Vec<StoredClip> = stmt
293            .query_map(params![query, limit], |r| {
294                Ok(StoredClip {
295                    id: r.get(0)?,
296                    source: r.get(1)?,
297                    source_key: r.get(2)?,
298                    content_type: r.get(3)?,
299                    content: r.get(4)?,
300                    media_path: r.get(5)?,
301                    byte_size: r.get(6)?,
302                    created_at: r.get(7)?,
303                    pinned: r.get::<_, i64>(8)? != 0,
304                    pinned_at: r.get(9)?,
305                })
306            })?
307            .filter_map(|r| r.ok())
308            .collect();
309        Ok(rows)
310    })
311}
client_core/store/queries.rs

client_core/store/
queries.rs
