use super::models::{AlertPref, RetentionPref, SourceRow, StoredClip, StoredDevice};
use super::{Store, StoreError};
use rusqlite::params;
pub fn insert_clip(store: &Store, c: &StoredClip) -> Result<(), StoreError> {
store.with_conn(|conn| {
conn.execute(
r#"INSERT OR REPLACE INTO clips
(id, source, source_key, content_type, content, media_path, byte_size, created_at, pinned, pinned_at)
VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9, ?10)"#,
params![
c.id, c.source, c.source_key, c.content_type, c.content,
c.media_path, c.byte_size, c.created_at,
if c.pinned { 1i64 } else { 0 }, c.pinned_at,
],
)?;
Ok(())
})
}
pub fn list_clips(
store: &Store,
from: Option<&str>,
limit: Option<i64>,
since_ms: Option<i64>,
pinned_only: bool,
default_limit: i64,
) -> Result<Vec<StoredClip>, StoreError> {
let mut sql = String::from(
"SELECT id, source, source_key, content_type, content, media_path, byte_size, created_at, pinned, pinned_at
FROM clips WHERE 1=1"
);
let mut binds: Vec<Box<dyn rusqlite::ToSql>> = Vec::new();
if let Some(s) = from {
sql.push_str(" AND source = ?");
binds.push(Box::new(s.to_string()));
}
if let Some(t) = since_ms {
sql.push_str(" AND created_at >= ?");
binds.push(Box::new(t));
}
if pinned_only {
sql.push_str(" AND pinned = 1");
}
sql.push_str(" ORDER BY created_at DESC LIMIT ?");
binds.push(Box::new(limit.unwrap_or(default_limit)));
store.with_conn(|conn| {
let mut stmt = conn.prepare(&sql)?;
let rows: Vec<StoredClip> = stmt
.query_map(
rusqlite::params_from_iter(binds.iter().map(|b| &**b as &dyn rusqlite::ToSql)),
|r| {
Ok(StoredClip {
id: r.get(0)?,
source: r.get(1)?,
source_key: r.get(2)?,
content_type: r.get(3)?,
content: r.get(4)?,
media_path: r.get(5)?,
byte_size: r.get(6)?,
created_at: r.get(7)?,
pinned: r.get::<_, i64>(8)? != 0,
pinned_at: r.get(9)?,
})
},
)?
.filter_map(|r| r.ok())
.collect();
Ok(rows)
})
}
pub fn get_clip(store: &Store, id: &str) -> Result<Option<StoredClip>, StoreError> {
store.with_conn(|conn| {
let mut stmt = conn.prepare(
"SELECT id, source, source_key, content_type, content, media_path, byte_size, created_at, pinned, pinned_at
FROM clips WHERE id = ?1"
)?;
let mut rows = stmt.query_map(params![id], |r| Ok(StoredClip {
id: r.get(0)?, source: r.get(1)?, source_key: r.get(2)?,
content_type: r.get(3)?, content: r.get(4)?, media_path: r.get(5)?,
byte_size: r.get(6)?, created_at: r.get(7)?,
pinned: r.get::<_, i64>(8)? != 0, pinned_at: r.get(9)?,
}))?;
if let Some(row) = rows.next() { Ok(Some(row?)) } else { Ok(None) }
})
}
pub fn delete_clip(store: &Store, id: &str) -> Result<(), StoreError> {
store.with_conn(|conn| {
conn.execute("DELETE FROM clips WHERE id = ?1", params![id])?;
Ok(())
})
}
pub fn set_pinned(store: &Store, id: &str, pinned: bool, when_ms: i64) -> Result<(), StoreError> {
store.with_conn(|conn| {
conn.execute(
"UPDATE clips SET pinned = ?1, pinned_at = CASE WHEN ?1 = 1 THEN ?2 ELSE NULL END WHERE id = ?3",
params![if pinned { 1i64 } else { 0 }, when_ms, id],
)?;
Ok(())
})
}
pub fn list_sources(store: &Store) -> Result<Vec<SourceRow>, StoreError> {
store.with_conn(|conn| {
let mut stmt = conn.prepare(
"SELECT source, COUNT(*) AS c, MAX(created_at) AS last_seen
FROM clips GROUP BY source ORDER BY last_seen DESC NULLS LAST",
)?;
let rows: Vec<SourceRow> = stmt
.query_map([], |r| {
Ok(SourceRow {
source: r.get(0)?,
clip_count: r.get(1)?,
last_seen: r.get(2)?,
})
})?
.filter_map(|r| r.ok())
.collect();
Ok(rows)
})
}
pub fn upsert_device(store: &Store, d: &StoredDevice) -> Result<(), StoreError> {
store.with_conn(|conn| {
conn.execute(
r#"INSERT OR REPLACE INTO devices
(id, hostname, nickname, source_key, machine_id, public_key,
paired_at, last_push_at, online, refreshed_at)
VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9, ?10)"#,
params![
d.id,
d.hostname,
d.nickname,
d.source_key,
d.machine_id,
d.public_key,
d.paired_at,
d.last_push_at,
if d.online { 1i64 } else { 0 },
d.refreshed_at,
],
)?;
Ok(())
})
}
pub fn list_devices(store: &Store) -> Result<Vec<StoredDevice>, StoreError> {
store.with_conn(|conn| {
let mut stmt = conn.prepare(
"SELECT id, hostname, nickname, source_key, machine_id, public_key,
paired_at, last_push_at, online, refreshed_at
FROM devices ORDER BY last_push_at DESC NULLS LAST",
)?;
let rows: Vec<StoredDevice> = stmt
.query_map([], |r| {
Ok(StoredDevice {
id: r.get(0)?,
hostname: r.get(1)?,
nickname: r.get(2)?,
source_key: r.get(3)?,
machine_id: r.get(4)?,
public_key: r.get(5)?,
paired_at: r.get(6)?,
last_push_at: r.get(7)?,
online: r.get::<_, i64>(8)? != 0,
refreshed_at: r.get(9)?,
})
})?
.filter_map(|r| r.ok())
.collect();
Ok(rows)
})
}
pub fn set_retention(store: &Store, device_id: &str, days: i64) -> Result<(), StoreError> {
store.with_conn(|conn| {
conn.execute(
"INSERT INTO retention_prefs(device_id, days) VALUES(?1, ?2)
ON CONFLICT(device_id) DO UPDATE SET days = excluded.days",
params![device_id, days],
)?;
Ok(())
})
}
pub fn list_retention(store: &Store) -> Result<Vec<RetentionPref>, StoreError> {
store.with_conn(|conn| {
let mut stmt = conn.prepare("SELECT device_id, days FROM retention_prefs")?;
let rows: Vec<RetentionPref> = stmt
.query_map([], |r| {
Ok(RetentionPref {
device_id: r.get(0)?,
days: r.get(1)?,
})
})?
.filter_map(|r| r.ok())
.collect();
Ok(rows)
})
}
pub fn set_alert_pref(store: &Store, source: &str, enabled: bool) -> Result<(), StoreError> {
store.with_conn(|conn| {
conn.execute(
"INSERT INTO alert_prefs(source, enabled) VALUES(?1, ?2)
ON CONFLICT(source) DO UPDATE SET enabled = excluded.enabled",
params![source, if enabled { 1i64 } else { 0 }],
)?;
Ok(())
})
}
pub fn list_alert_prefs(store: &Store) -> Result<Vec<AlertPref>, StoreError> {
store.with_conn(|conn| {
let mut stmt = conn.prepare("SELECT source, enabled FROM alert_prefs")?;
let rows: Vec<AlertPref> = stmt
.query_map([], |r| {
Ok(AlertPref {
source: r.get(0)?,
enabled: r.get::<_, i64>(1)? != 0,
})
})?
.filter_map(|r| r.ok())
.collect();
Ok(rows)
})
}
pub fn watermark(store: &Store) -> Result<Option<String>, StoreError> {
store.with_conn(|conn| {
conn.query_row(
"SELECT value FROM meta WHERE key='last_sync_watermark'",
[],
|r| r.get::<_, String>(0),
)
.map(Some)
.or_else(|e| match e {
rusqlite::Error::QueryReturnedNoRows => Ok(None),
other => Err(other),
})
})
}
pub fn set_watermark(store: &Store, ulid: &str) -> Result<(), StoreError> {
store.with_conn(|conn| {
conn.execute(
"INSERT INTO meta(key, value) VALUES('last_sync_watermark', ?1)
ON CONFLICT(key) DO UPDATE SET value = excluded.value",
params![ulid],
)?;
Ok(())
})
}
pub fn clip_count(store: &Store) -> Result<i64, StoreError> {
store.with_conn(|conn| conn.query_row("SELECT COUNT(*) FROM clips", [], |r| r.get::<_, i64>(0)))
}
pub fn count_clips_before(store: &Store, cutoff_ms: i64) -> Result<i64, StoreError> {
store.with_conn(|conn| {
conn.query_row(
"SELECT COUNT(*) FROM clips WHERE created_at < ?1",
rusqlite::params![cutoff_ms],
|r| r.get::<_, i64>(0),
)
})
}
pub fn clear_all_clips(store: &Store) -> Result<i64, StoreError> {
store.with_conn(|conn| {
let n = conn.execute("DELETE FROM clips", [])?;
Ok(n as i64)
})
}
pub fn search_clips(store: &Store, query: &str, limit: i64) -> Result<Vec<StoredClip>, StoreError> {
store.with_conn(|conn| {
let mut stmt = conn.prepare(
"SELECT c.id, c.source, c.source_key, c.content_type, c.content, c.media_path,
c.byte_size, c.created_at, c.pinned, c.pinned_at
FROM clips c JOIN clips_fts f ON f.rowid = c.rowid
WHERE clips_fts MATCH ?1 ORDER BY rank LIMIT ?2",
)?;
let rows: Vec<StoredClip> = stmt
.query_map(params![query, limit], |r| {
Ok(StoredClip {
id: r.get(0)?,
source: r.get(1)?,
source_key: r.get(2)?,
content_type: r.get(3)?,
content: r.get(4)?,
media_path: r.get(5)?,
byte_size: r.get(6)?,
created_at: r.get(7)?,
pinned: r.get::<_, i64>(8)? != 0,
pinned_at: r.get(9)?,
})
})?
.filter_map(|r| r.ok())
.collect();
Ok(rows)
})
}