unifier/
sqlite.rs

1//! Managed SQLite databases under `sqlite/<name>.sqlite`.
2//!
3//! The reserved [`TRIPLES_DB`] database holds RDF-style subject/predicate/object
4//! rows and is created automatically on first use.
5
6use std::fs;
7use std::path::PathBuf;
8
9use rusqlite::{params, types::ValueRef, Connection, Statement};
10
11use crate::constants::{SQLITE, TRIPLES_DB};
12use crate::error::{Error, Result};
13use crate::home::UnifierHome;
14use crate::scope::validate_chroot_name;
15
16const SQLITE_SUFFIX: &str = ".sqlite";
17
18#[derive(Debug, Clone, PartialEq, Eq)]
19pub struct ColumnInfo {
20    pub name: String,
21    pub decl_type: String,
22}
23
24#[derive(Debug, Clone, PartialEq, Eq)]
25pub struct TableInfo {
26    pub name: String,
27    pub columns: Vec<ColumnInfo>,
28}
29
30#[derive(Debug, Clone, PartialEq, Eq)]
31pub struct QueryResult {
32    pub columns: Vec<String>,
33    pub rows: Vec<Vec<String>>,
34}
35
36#[derive(Debug, Clone, PartialEq, Eq)]
37pub struct ExecResult {
38    pub rows_affected: usize,
39}
40
41pub fn sqlite_dir(home: &UnifierHome) -> PathBuf {
42    home.path().join(SQLITE)
43}
44
45pub fn validate_db_name(name: &str) -> Result<()> {
46    validate_chroot_name(name).map_err(|_| Error::msg(format!("invalid database name: {name}")))
47}
48
49pub fn db_path(home: &UnifierHome, name: &str) -> Result<PathBuf> {
50    validate_db_name(name)?;
51    Ok(sqlite_dir(home).join(format!("{name}{SQLITE_SUFFIX}")))
52}
53
54/// Create an empty managed database (or ensure `triples` schema).
55pub fn create_db(home: &UnifierHome, name: &str) -> Result<PathBuf> {
56    let path = db_path(home, name)?;
57    let _conn = open_connection(home, name, true)?;
58    Ok(path)
59}
60
61pub fn list_databases(home: &UnifierHome) -> Result<Vec<String>> {
62    let dir = sqlite_dir(home);
63    if !dir.is_dir() {
64        return Ok(Vec::new());
65    }
66    let mut names = Vec::new();
67    for entry in fs::read_dir(&dir)? {
68        let entry = entry?;
69        if !entry.file_type()?.is_file() {
70            continue;
71        }
72        let file_name = entry.file_name();
73        let Some(name) = file_name.to_str() else {
74            continue;
75        };
76        if let Some(stem) = name.strip_suffix(SQLITE_SUFFIX) {
77            if validate_db_name(stem).is_ok() {
78                names.push(stem.to_string());
79            }
80        }
81    }
82    names.sort();
83    Ok(names)
84}
85
86pub fn list_tables(home: &UnifierHome, name: &str) -> Result<Vec<TableInfo>> {
87    let conn = open_connection(home, name, false)?;
88    let mut stmt = conn.prepare(
89        "SELECT name FROM sqlite_master WHERE type = 'table' AND name NOT LIKE 'sqlite_%' ORDER BY name",
90    )?;
91    let names = stmt
92        .query_map([], |row| row.get::<_, String>(0))?
93        .collect::<std::result::Result<Vec<_>, _>>()?;
94
95    let mut tables = Vec::with_capacity(names.len());
96    for table_name in names {
97        let columns = table_columns(&conn, &table_name)?;
98        tables.push(TableInfo {
99            name: table_name,
100            columns,
101        });
102    }
103    Ok(tables)
104}
105
106pub fn exec_sql(home: &UnifierHome, name: &str, sql: &str) -> Result<SqlOutcome> {
107    let sql = sql.trim();
108    if sql.is_empty() {
109        return Err(Error::msg("sql must not be empty"));
110    }
111    let conn = open_connection(home, name, true)?;
112    if looks_like_query(sql) {
113        return Ok(SqlOutcome::Query(run_query(&conn, sql)?));
114    }
115    let trimmed = sql.trim_end_matches(';').trim();
116    if trimmed.contains(';') {
117        conn.execute_batch(sql)?;
118        Ok(SqlOutcome::Exec(ExecResult { rows_affected: 0 }))
119    } else {
120        let rows_affected = conn.execute(sql, [])?;
121        Ok(SqlOutcome::Exec(ExecResult { rows_affected }))
122    }
123}
124
125#[derive(Debug, Clone, PartialEq, Eq)]
126pub enum SqlOutcome {
127    Query(QueryResult),
128    Exec(ExecResult),
129}
130
131pub fn insert_triple(
132    home: &UnifierHome,
133    subject: &str,
134    predicate: &str,
135    object: &str,
136) -> Result<()> {
137    if subject.is_empty() || predicate.is_empty() || object.is_empty() {
138        return Err(Error::msg(
139            "triple subject, predicate, and object must be non-empty",
140        ));
141    }
142    let conn = open_connection(home, TRIPLES_DB, true)?;
143    conn.execute(
144        "INSERT OR IGNORE INTO triples (subject, predicate, object) VALUES (?1, ?2, ?3)",
145        params![subject, predicate, object],
146    )?;
147    Ok(())
148}
149
150pub fn query_triples(
151    home: &UnifierHome,
152    subject: Option<&str>,
153    predicate: Option<&str>,
154    object: Option<&str>,
155) -> Result<Vec<(String, String, String)>> {
156    let conn = open_connection(home, TRIPLES_DB, true)?;
157    let mut sql = String::from("SELECT subject, predicate, object FROM triples WHERE 1 = 1");
158    let mut values: Vec<String> = Vec::new();
159    if let Some(s) = subject {
160        sql.push_str(" AND subject = ?");
161        values.push(s.to_string());
162    }
163    if let Some(p) = predicate {
164        sql.push_str(" AND predicate = ?");
165        values.push(p.to_string());
166    }
167    if let Some(o) = object {
168        sql.push_str(" AND object = ?");
169        values.push(o.to_string());
170    }
171    sql.push_str(" ORDER BY subject, predicate, object");
172
173    let mut stmt = conn.prepare(&sql)?;
174    let params: Vec<&dyn rusqlite::types::ToSql> = values
175        .iter()
176        .map(|v| v as &dyn rusqlite::types::ToSql)
177        .collect();
178    let rows = stmt
179        .query_map(params.as_slice(), |row| {
180            Ok((row.get(0)?, row.get(1)?, row.get(2)?))
181        })?
182        .collect::<std::result::Result<Vec<_>, _>>()?;
183    Ok(rows)
184}
185
186fn open_connection(home: &UnifierHome, name: &str, create: bool) -> Result<Connection> {
187    let path = db_path(home, name)?;
188    if !create && !path.is_file() {
189        return Err(Error::msg(format!("database not found: {name}")));
190    }
191    if let Some(parent) = path.parent() {
192        fs::create_dir_all(parent)?;
193    }
194    let conn = Connection::open(&path).map_err(sql_err)?;
195    if name == TRIPLES_DB {
196        ensure_triples_schema(&conn)?;
197    }
198    Ok(conn)
199}
200
201fn ensure_triples_schema(conn: &Connection) -> Result<()> {
202    conn.execute_batch(
203        "CREATE TABLE IF NOT EXISTS triples (
204            subject TEXT NOT NULL,
205            predicate TEXT NOT NULL,
206            object TEXT NOT NULL,
207            created_at TEXT NOT NULL DEFAULT (datetime('now')),
208            PRIMARY KEY (subject, predicate, object)
209        );
210        CREATE INDEX IF NOT EXISTS idx_triples_predicate ON triples(predicate);
211        CREATE INDEX IF NOT EXISTS idx_triples_object ON triples(object);",
212    )
213    .map_err(sql_err)?;
214    Ok(())
215}
216
217fn table_columns(conn: &Connection, table: &str) -> Result<Vec<ColumnInfo>> {
218    // table name comes from sqlite_master; still quote carefully
219    let pragma = format!("PRAGMA table_info({})", quote_ident(table));
220    let mut stmt = conn.prepare(&pragma)?;
221    let cols = stmt
222        .query_map([], |row| {
223            Ok(ColumnInfo {
224                name: row.get(1)?,
225                decl_type: row.get::<_, Option<String>>(2)?.unwrap_or_default(),
226            })
227        })?
228        .collect::<std::result::Result<Vec<_>, _>>()?;
229    Ok(cols)
230}
231
232fn quote_ident(name: &str) -> String {
233    format!("\"{}\"", name.replace('"', "\"\""))
234}
235
236fn looks_like_query(sql: &str) -> bool {
237    let head = sql
238        .trim_start()
239        .trim_start_matches('(')
240        .to_ascii_lowercase();
241    head.starts_with("select")
242        || head.starts_with("with")
243        || head.starts_with("pragma")
244        || head.starts_with("explain")
245        || head.starts_with("values")
246}
247
248fn run_query(conn: &Connection, sql: &str) -> Result<QueryResult> {
249    let mut stmt = conn.prepare(sql)?;
250    let columns = stmt
251        .column_names()
252        .iter()
253        .map(|s| (*s).to_string())
254        .collect::<Vec<_>>();
255    let rows = collect_rows(&mut stmt)?;
256    Ok(QueryResult { columns, rows })
257}
258
259fn collect_rows(stmt: &mut Statement<'_>) -> Result<Vec<Vec<String>>> {
260    let column_count = stmt.column_count();
261    let mut rows = Vec::new();
262    let mut query = stmt.query([])?;
263    while let Some(row) = query.next()? {
264        let mut values = Vec::with_capacity(column_count);
265        for i in 0..column_count {
266            values.push(value_to_string(row.get_ref(i)?));
267        }
268        rows.push(values);
269    }
270    Ok(rows)
271}
272
273fn value_to_string(value: ValueRef<'_>) -> String {
274    match value {
275        ValueRef::Null => String::new(),
276        ValueRef::Integer(v) => v.to_string(),
277        ValueRef::Real(v) => v.to_string(),
278        ValueRef::Text(v) => String::from_utf8_lossy(v).into_owned(),
279        ValueRef::Blob(v) => format!("\\x{}", hex::encode_fallback(v)),
280    }
281}
282
283fn sql_err(e: rusqlite::Error) -> Error {
284    Error::msg(format!("sqlite: {e}"))
285}
286
287impl From<rusqlite::Error> for Error {
288    fn from(e: rusqlite::Error) -> Self {
289        sql_err(e)
290    }
291}
292
293mod hex {
294    pub fn encode_fallback(bytes: &[u8]) -> String {
295        const HEX: &[u8; 16] = b"0123456789abcdef";
296        let mut out = String::with_capacity(bytes.len() * 2);
297        for b in bytes {
298            out.push(HEX[(b >> 4) as usize] as char);
299            out.push(HEX[(b & 0xf) as usize] as char);
300        }
301        out
302    }
303}
304
305#[cfg(test)]
306mod tests {
307    use super::*;
308    use crate::home::UnifierHome;
309    use tempfile::tempdir;
310
311    fn home() -> (tempfile::TempDir, UnifierHome) {
312        let tmp = tempdir().unwrap();
313        let home = UnifierHome::resolve(Some(tmp.path().to_path_buf()), None).unwrap();
314        home.ensure().unwrap();
315        (tmp, home)
316    }
317
318    #[test]
319    fn create_list_exec_and_tables() {
320        let (_tmp, home) = home();
321        create_db(&home, "app").unwrap();
322        assert_eq!(list_databases(&home).unwrap(), vec!["app".to_string()]);
323
324        match exec_sql(
325            &home,
326            "app",
327            "CREATE TABLE items (id INTEGER PRIMARY KEY, name TEXT); INSERT INTO items(name) VALUES ('a'), ('b');",
328        )
329        .unwrap()
330        {
331            SqlOutcome::Exec(_) => {}
332            other => panic!("expected exec, got {other:?}"),
333        }
334
335        let tables = list_tables(&home, "app").unwrap();
336        assert_eq!(tables.len(), 1);
337        assert_eq!(tables[0].name, "items");
338        assert_eq!(tables[0].columns[0].name, "id");
339        assert_eq!(tables[0].columns[1].name, "name");
340
341        match exec_sql(&home, "app", "SELECT name FROM items ORDER BY id").unwrap() {
342            SqlOutcome::Query(q) => {
343                assert_eq!(q.columns, vec!["name"]);
344                assert_eq!(q.rows, vec![vec!["a".to_string()], vec!["b".to_string()]]);
345            }
346            other => panic!("expected query, got {other:?}"),
347        }
348    }
349
350    #[test]
351    fn triples_insert_and_query() {
352        let (_tmp, home) = home();
353        insert_triple(&home, "alice", "knows", "bob").unwrap();
354        insert_triple(&home, "alice", "knows", "bob").unwrap(); // ignore dup
355        insert_triple(&home, "alice", "likes", "tea").unwrap();
356
357        let all = query_triples(&home, None, None, None).unwrap();
358        assert_eq!(all.len(), 2);
359        let knows = query_triples(&home, Some("alice"), Some("knows"), None).unwrap();
360        assert_eq!(
361            knows,
362            vec![("alice".to_string(), "knows".to_string(), "bob".to_string())]
363        );
364
365        assert!(list_databases(&home)
366            .unwrap()
367            .contains(&"triples".to_string()));
368        let tables = list_tables(&home, "triples").unwrap();
369        assert!(tables.iter().any(|t| t.name == "triples"));
370    }
371
372    #[test]
373    fn rejects_bad_db_name() {
374        let (_tmp, home) = home();
375        assert!(create_db(&home, "../x").is_err());
376        assert!(create_db(&home, "a/b").is_err());
377    }
378}
unifier/sqlite.rs

unifier/
sqlite.rs
