valence_backend_sql/
sqlite_ops.rs

1//! Shared SQL document backend logic for SQLite and Postgres.
2
3use serde_json::{Map, Value};
4use sqlx::Row;
5use valence_core::compiled_query::CompiledQuery;
6use valence_core::error::{Error, Result};
7use valence_core::record_id::RecordId;
8
9use crate::{ensure_table, json_merge, prepare_compiled, row_from_body, upsert_body_fields};
10
11/// Dialect-specific SQL fragments.
12#[allow(dead_code)]
13pub trait SqlDialect: Send + Sync + 'static {
14    fn insert_or_ignore(&self) -> &'static str;
15    fn body_column_type(&self) -> &'static str;
16}
17
18pub fn assert_safe_table(table: &str) -> Result<()> {
19    if table.chars().all(|c| c.is_ascii_alphanumeric() || c == '_') {
20        Ok(())
21    } else {
22        Err(Error::Validation(format!("unsafe table name: {table}")))
23    }
24}
25
26pub fn storage_id(content: &Value) -> Option<String> {
27    content.get("id").and_then(|v| {
28        v.get("id")
29            .and_then(|x| x.as_str())
30            .map(str::to_string)
31            .or_else(|| v.as_str().map(str::to_string))
32    })
33}
34
35pub fn expand_body_rows(table: &str, rows: Vec<(String, String)>) -> Result<Vec<Value>> {
36    rows.into_iter()
37        .map(|(id, body_text)| {
38            let body: Value =
39                serde_json::from_str(&body_text).unwrap_or_else(|_| Value::Object(Map::new()));
40            Ok(row_from_body(table, &id, body))
41        })
42        .collect()
43}
44
45pub fn bind_json_value<'q>(
46    query: sqlx::query::Query<'q, sqlx::Sqlite, sqlx::sqlite::SqliteArguments<'q>>,
47    value: &Value,
48) -> sqlx::query::Query<'q, sqlx::Sqlite, sqlx::sqlite::SqliteArguments<'q>> {
49    match value {
50        Value::Null => query.bind(None::<String>),
51        Value::Bool(b) => query.bind(*b),
52        Value::Number(n) => {
53            if let Some(i) = n.as_i64() {
54                query.bind(i)
55            } else if let Some(f) = n.as_f64() {
56                query.bind(f)
57            } else {
58                query.bind(n.to_string())
59            }
60        }
61        Value::String(s) => query.bind(s.clone()),
62        other => query.bind(other.to_string()),
63    }
64}
65
66pub async fn execute_select_sqlite(
67    pool: &sqlx::SqlitePool,
68    compiled: &CompiledQuery,
69    default_table: &str,
70) -> Result<Vec<Value>> {
71    let (sql, params) = prepare_compiled(compiled)?;
72    let mut q = sqlx::query(&sql);
73    for p in &params {
74        q = bind_json_value(q, p);
75    }
76    let rows = match q.fetch_all(pool).await {
77        Ok(rows) => rows,
78        Err(e) if e.to_string().to_lowercase().contains("no such table") => return Ok(vec![]),
79        Err(e) => return Err(Error::database(e.to_string())),
80    };
81
82    if sql.contains("COUNT(") {
83        let count = rows
84            .first()
85            .and_then(|r| r.try_get::<i64, _>(0).ok())
86            .unwrap_or(0);
87        return Ok(vec![Value::Number(count.into())]);
88    }
89
90    if sql.contains("SELECT id") && !sql.contains("body") {
91        // Return object rows so callers can deserialize as `IdOnlyRecord` / model types.
92        return Ok(rows
93            .iter()
94            .filter_map(|r| r.try_get::<String, _>(0).ok())
95            .map(|id| serde_json::json!({ "id": id }))
96            .collect());
97    }
98
99    let table = compiled
100        .query_string
101        .split("FROM")
102        .nth(1)
103        .and_then(|s| s.split_whitespace().next())
104        .unwrap_or(default_table);
105
106    let pairs: Vec<(String, String)> = rows
107        .iter()
108        .map(|r| {
109            let id: String = r.try_get(0).unwrap_or_default();
110            let body: String = r.try_get(1).unwrap_or_else(|_| "{}".into());
111            (id, body)
112        })
113        .collect();
114    expand_body_rows(table, pairs)
115}
116
117pub async fn ensure_edges_sqlite(pool: &sqlx::SqlitePool) -> Result<()> {
118    sqlx::query(&crate::document::ensure_edges_table_ddl())
119        .execute(pool)
120        .await
121        .map_err(|e| Error::database(e.to_string()))?;
122    Ok(())
123}
124
125pub async fn ensure_table_sqlite(pool: &sqlx::SqlitePool, table: &str) -> Result<()> {
126    assert_safe_table(table)?;
127    sqlx::query(&ensure_table(table))
128        .execute(pool)
129        .await
130        .map_err(|e| Error::database(e.to_string()))?;
131    Ok(())
132}
133
134pub async fn get_record_sqlite(
135    pool: &sqlx::SqlitePool,
136    table: &str,
137    id: &str,
138) -> Result<Option<Value>> {
139    ensure_table_sqlite(pool, table).await?;
140    let q = format!("SELECT id, body FROM {table} WHERE id = ?");
141    let row = sqlx::query(&q)
142        .bind(id)
143        .fetch_optional(pool)
144        .await
145        .map_err(|e| Error::database(e.to_string()))?;
146    Ok(row.map(|r| {
147        let id: String = r.get(0);
148        let body: String = r.get(1);
149        row_from_body(
150            table,
151            &id,
152            serde_json::from_str(&body).unwrap_or_else(|_| Value::Object(Map::new())),
153        )
154    }))
155}
156
157pub async fn create_record_sqlite(
158    pool: &sqlx::SqlitePool,
159    table: &str,
160    content: Value,
161) -> Result<Value> {
162    ensure_table_sqlite(pool, table).await?;
163    let id = storage_id(&content).unwrap_or_else(|| uuid::Uuid::new_v4().to_string());
164    let mut body = upsert_body_fields(content);
165    body.remove("id");
166    let body_text = serde_json::to_string(&Value::Object(body)).map_err(Error::from)?;
167    let q = format!("INSERT INTO {table} (id, body) VALUES (?, ?)");
168    sqlx::query(&q)
169        .bind(&id)
170        .bind(&body_text)
171        .execute(pool)
172        .await
173        .map_err(|e| Error::database(e.to_string()))?;
174    Ok(row_from_body(
175        table,
176        &id,
177        serde_json::from_str(&body_text).unwrap_or_else(|_| Value::Object(Map::new())),
178    ))
179}
180
181pub async fn update_record_sqlite(
182    pool: &sqlx::SqlitePool,
183    table: &str,
184    id: &str,
185    content: Value,
186) -> Result<Value> {
187    if get_record_sqlite(pool, table, id).await?.is_none() {
188        return Err(Error::NotFound(format!("{table}:{id}")));
189    }
190    let mut body = upsert_body_fields(content);
191    body.remove("id");
192    let body_text = serde_json::to_string(&Value::Object(body)).map_err(Error::from)?;
193    let q = format!("UPDATE {table} SET body = ? WHERE id = ?");
194    sqlx::query(&q)
195        .bind(&body_text)
196        .bind(id)
197        .execute(pool)
198        .await
199        .map_err(|e| Error::database(e.to_string()))?;
200    Ok(row_from_body(
201        table,
202        id,
203        serde_json::from_str(&body_text).unwrap_or_else(|_| Value::Object(Map::new())),
204    ))
205}
206
207pub async fn merge_record_sqlite(
208    pool: &sqlx::SqlitePool,
209    table: &str,
210    id: &str,
211    patch: Value,
212) -> Result<Value> {
213    let existing = get_record_sqlite(pool, table, id)
214        .await?
215        .ok_or_else(|| Error::NotFound(format!("{table}:{id}")))?;
216    let mut base = existing.as_object().cloned().unwrap_or_default();
217    base.remove("id");
218    if let Some(patch_obj) = patch.as_object() {
219        json_merge(&mut base, patch_obj);
220    }
221    update_record_sqlite(pool, table, id, Value::Object(base)).await
222}
223
224pub async fn delete_record_sqlite(pool: &sqlx::SqlitePool, table: &str, id: &str) -> Result<()> {
225    let q = format!("DELETE FROM {table} WHERE id = ?");
226    sqlx::query(&q)
227        .bind(id)
228        .execute(pool)
229        .await
230        .map_err(|e| Error::database(e.to_string()))?;
231    Ok(())
232}
233
234pub async fn relate_edge_sqlite(
235    pool: &sqlx::SqlitePool,
236    from: &RecordId,
237    edge_table: &str,
238    to: &RecordId,
239) -> Result<()> {
240    ensure_edges_sqlite(pool).await?;
241    sqlx::query(
242        "INSERT OR IGNORE INTO valence_edges (from_table, from_id, edge_type, to_table, to_id) \
243         VALUES (?, ?, ?, ?, ?)",
244    )
245    .bind(from.table())
246    .bind(from.id())
247    .bind(edge_table)
248    .bind(to.table())
249    .bind(to.id())
250    .execute(pool)
251    .await
252    .map_err(|e| Error::database(e.to_string()))?;
253    Ok(())
254}
255
256pub async fn unrelate_edge_sqlite(
257    pool: &sqlx::SqlitePool,
258    from: &RecordId,
259    edge_table: &str,
260    to: &RecordId,
261) -> Result<()> {
262    sqlx::query(
263        "DELETE FROM valence_edges WHERE from_table = ? AND from_id = ? AND edge_type = ? \
264         AND to_table = ? AND to_id = ?",
265    )
266    .bind(from.table())
267    .bind(from.id())
268    .bind(edge_table)
269    .bind(to.table())
270    .bind(to.id())
271    .execute(pool)
272    .await
273    .map_err(|e| Error::database(e.to_string()))?;
274    Ok(())
275}
276
277pub async fn get_edge_targets_sqlite(
278    pool: &sqlx::SqlitePool,
279    from: &RecordId,
280    edge_table: &str,
281) -> Result<Vec<RecordId>> {
282    let rows = sqlx::query(
283        "SELECT to_table, to_id FROM valence_edges \
284         WHERE from_table = ? AND from_id = ? AND edge_type = ?",
285    )
286    .bind(from.table())
287    .bind(from.id())
288    .bind(edge_table)
289    .fetch_all(pool)
290    .await
291    .map_err(|e| Error::database(e.to_string()))?;
292    Ok(rows
293        .iter()
294        .map(|r| RecordId::new(r.get::<String, _>(0), r.get::<String, _>(1)))
295        .collect())
296}
297
298pub async fn define_unique_index_sqlite(
299    pool: &sqlx::SqlitePool,
300    table: &str,
301    field: &str,
302) -> Result<()> {
303    assert_safe_table(table)?;
304    if !field.chars().all(|c| c.is_ascii_alphanumeric() || c == '_') {
305        return Err(Error::Validation(format!("unsafe field: {field}")));
306    }
307    ensure_table_sqlite(pool, table).await?;
308    let idx = format!("valence_unique_{table}_{field}");
309    let q = format!(
310        "CREATE UNIQUE INDEX IF NOT EXISTS {idx} ON {table} (json_extract(body, '$.{field}'))"
311    );
312    sqlx::query(&q)
313        .execute(pool)
314        .await
315        .map_err(|e| Error::database(e.to_string()))?;
316    Ok(())
317}
318
319pub fn ttl_deferred() -> valence_core::ttl::BackendTtlCapability {
320    valence_core::ttl::BackendTtlCapability::Deferred
321}
322
323#[allow(dead_code, clippy::unused_async)]
324pub async fn apply_ttl_noop(
325    _table: &str,
326    _policy: &valence_core::ttl::SchemaTtlPolicy,
327) -> Result<()> {
328    Ok(())
329}
330
331pub const fn sql_capabilities(label: &'static str) -> valence_core::BackendCapabilities {
332    valence_core::BackendCapabilities {
333        supports_merge: true,
334        supports_graph_edges: true,
335        telemetry_label: label,
336    }
337}
valence_backend_sql/sqlite_ops.rs

valence_backend_sql/
sqlite_ops.rs
