Skip to main content

oxilite_rusqlite/
lib.rs

1//! In-process SQLite backend for oxilite, built on rusqlite (bundled SQLite by default).
2//!
3//! Registers the `oxilite_*` user-defined functions (regular expressions, replace, hashes,
4//! `ENCODE_FOR_URI`) and overrides `upper`/`lower` with Unicode-aware versions, so every
5//! SPARQL function compiles to SQL on this backend.
6//!
7// @lat: [[architecture#Backends#Native rusqlite]]
8
9use oxilite_core::sql::{Capabilities, Mode, Request, Response, ResultSet, SqlValue, Statement};
10use oxilite_core::{Error, Result, SyncBackend};
11use regex::{Regex, RegexBuilder};
12use rusqlite::functions::FunctionFlags;
13use rusqlite::types::ValueRef;
14use rusqlite::{Connection, OpenFlags};
15use std::cell::RefCell;
16use std::collections::HashMap;
17use std::path::Path;
18use std::sync::Mutex;
19
20/// Minimum SQLite version (STRICT tables).
21pub const MIN_SQLITE_VERSION: i32 = 3_037_000;
22
23/// A rusqlite connection used as an oxilite backend.
24pub struct RusqliteBackend {
25    conn: Mutex<Connection>,
26    caps: Capabilities,
27}
28
29fn err(e: rusqlite::Error) -> Error {
30    Error::backend(e)
31}
32
33impl RusqliteBackend {
34    /// An in-memory database.
35    pub fn memory() -> Result<Self> {
36        Self::from_connection(Connection::open_in_memory().map_err(err)?)
37    }
38
39    /// A database file (created if missing).
40    pub fn open(path: impl AsRef<Path>) -> Result<Self> {
41        let conn = Connection::open_with_flags(
42            path,
43            OpenFlags::SQLITE_OPEN_READ_WRITE
44                | OpenFlags::SQLITE_OPEN_CREATE
45                | OpenFlags::SQLITE_OPEN_URI,
46        )
47        .map_err(err)?;
48        conn.pragma_update(None, "journal_mode", "WAL")
49            .map_err(err)?;
50        conn.pragma_update(None, "synchronous", "NORMAL")
51            .map_err(err)?;
52        Self::from_connection(conn)
53    }
54
55    /// A database file opened read-only.
56    pub fn open_read_only(path: impl AsRef<Path>) -> Result<Self> {
57        let conn = Connection::open_with_flags(
58            path,
59            OpenFlags::SQLITE_OPEN_READ_ONLY | OpenFlags::SQLITE_OPEN_URI,
60        )
61        .map_err(err)?;
62        Self::from_connection(conn)
63    }
64
65    /// Wraps an existing connection (registers UDFs).
66    pub fn from_connection(conn: Connection) -> Result<Self> {
67        let version = rusqlite::version_number();
68        if version < MIN_SQLITE_VERSION {
69            return Err(Error::backend(format!(
70                "SQLite {} is too old, oxilite needs 3.37 or newer",
71                rusqlite::version()
72            )));
73        }
74        conn.busy_timeout(std::time::Duration::from_secs(30))
75            .map_err(err)?;
76        register_functions(&conn).map_err(err)?;
77        Ok(Self {
78            conn: Mutex::new(conn),
79            caps: Capabilities {
80                udf: true,
81                name: format!("rusqlite (SQLite {})", rusqlite::version()),
82                ..Capabilities::native()
83            },
84        })
85    }
86
87    /// Runs a closure with the underlying connection.
88    pub fn with_connection<T>(&self, f: impl FnOnce(&Connection) -> T) -> T {
89        f(&self
90            .conn
91            .lock()
92            .unwrap_or_else(std::sync::PoisonError::into_inner))
93    }
94}
95
96fn value(v: ValueRef<'_>) -> SqlValue {
97    match v {
98        ValueRef::Null => SqlValue::Null,
99        ValueRef::Integer(i) => SqlValue::Integer(i),
100        ValueRef::Real(f) => SqlValue::Real(f),
101        ValueRef::Text(t) | ValueRef::Blob(t) => {
102            SqlValue::Text(String::from_utf8_lossy(t).into_owned())
103        }
104    }
105}
106
107fn param(v: &SqlValue) -> rusqlite::types::Value {
108    match v {
109        SqlValue::Null => rusqlite::types::Value::Null,
110        SqlValue::Integer(i) => rusqlite::types::Value::Integer(*i),
111        SqlValue::Real(f) => rusqlite::types::Value::Real(*f),
112        SqlValue::Text(t) => rusqlite::types::Value::Text(t.clone()),
113    }
114}
115
116fn run(conn: &Connection, s: &Statement) -> rusqlite::Result<ResultSet> {
117    let mut stmt = conn.prepare(&s.sql)?;
118    let params = rusqlite::params_from_iter(s.params.iter().map(param));
119    if stmt.column_count() == 0 {
120        let changes = stmt.execute(params)? as u64;
121        return Ok(ResultSet {
122            rows: Vec::new(),
123            changes,
124        });
125    }
126    let n = stmt.column_count();
127    let mut rows = Vec::new();
128    let mut q = stmt.query(params)?;
129    while let Some(row) = q.next()? {
130        let mut out = Vec::with_capacity(n);
131        for i in 0..n {
132            out.push(value(row.get_ref(i)?));
133        }
134        rows.push(out);
135    }
136    Ok(ResultSet { rows, changes: 0 })
137}
138
139impl SyncBackend for RusqliteBackend {
140    fn execute(&self, request: &Request) -> Result<Response> {
141        let conn = self
142            .conn
143            .lock()
144            .unwrap_or_else(std::sync::PoisonError::into_inner);
145        match request.mode {
146            Mode::Read => request
147                .statements
148                .iter()
149                .map(|s| run(&conn, s).map_err(err))
150                .collect(),
151            Mode::Atomic => {
152                conn.execute_batch("SAVEPOINT oxilite_request")
153                    .map_err(err)?;
154                let result: Result<Response> = request
155                    .statements
156                    .iter()
157                    .map(|s| run(&conn, s).map_err(err))
158                    .collect();
159                match result {
160                    Ok(r) => {
161                        conn.execute_batch("RELEASE oxilite_request").map_err(err)?;
162                        Ok(r)
163                    }
164                    Err(e) => {
165                        let _ = conn
166                            .execute_batch("ROLLBACK TO oxilite_request; RELEASE oxilite_request");
167                        Err(e)
168                    }
169                }
170            }
171        }
172    }
173
174    fn capabilities(&self) -> &Capabilities {
175        &self.caps
176    }
177
178    fn begin(&self) -> Result<()> {
179        self.with_connection(|c| c.execute_batch("SAVEPOINT oxilite_tx"))
180            .map_err(err)
181    }
182
183    fn commit(&self) -> Result<()> {
184        self.with_connection(|c| c.execute_batch("RELEASE oxilite_tx"))
185            .map_err(err)
186    }
187
188    fn rollback(&self) -> Result<()> {
189        self.with_connection(|c| c.execute_batch("ROLLBACK TO oxilite_tx; RELEASE oxilite_tx"))
190            .map_err(err)
191    }
192}
193
194thread_local! {
195    static REGEX_CACHE: RefCell<HashMap<(String, String), Option<Regex>>> = RefCell::new(HashMap::new());
196}
197
198/// Compiles a SPARQL (XPath) regex with its flags, caching per thread.
199pub fn sparql_regex(pattern: &str, flags: &str) -> Option<Regex> {
200    REGEX_CACHE.with(|cache| {
201        let mut cache = cache.borrow_mut();
202        if cache.len() > 256 {
203            cache.clear();
204        }
205        cache
206            .entry((pattern.to_string(), flags.to_string()))
207            .or_insert_with(|| {
208                let mut literal = false;
209                let mut b = RegexBuilder::new(pattern);
210                for f in flags.chars() {
211                    match f {
212                        'i' => {
213                            b.case_insensitive(true);
214                        }
215                        's' => {
216                            b.dot_matches_new_line(true);
217                        }
218                        'm' => {
219                            b.multi_line(true);
220                        }
221                        'x' => {
222                            b.ignore_whitespace(true);
223                        }
224                        'q' => literal = true,
225                        _ => return None,
226                    }
227                }
228                if literal {
229                    let escaped = regex::escape(pattern);
230                    b = RegexBuilder::new(&escaped);
231                    if flags.contains('i') {
232                        b.case_insensitive(true);
233                    }
234                }
235                b.size_limit(1 << 20).build().ok()
236            })
237            .clone()
238    })
239}
240
241fn text(ctx: &rusqlite::functions::Context<'_>, i: usize) -> rusqlite::Result<Option<String>> {
242    Ok(match ctx.get_raw(i) {
243        ValueRef::Null => None,
244        ValueRef::Text(t) => Some(String::from_utf8_lossy(t).into_owned()),
245        ValueRef::Integer(v) => Some(v.to_string()),
246        ValueRef::Real(v) => Some(v.to_string()),
247        ValueRef::Blob(b) => Some(String::from_utf8_lossy(b).into_owned()),
248    })
249}
250
251/// Percent-encodes everything except RFC 3986 unreserved characters.
252pub fn encode_for_uri(s: &str) -> String {
253    let mut out = String::with_capacity(s.len());
254    for b in s.bytes() {
255        if b.is_ascii_alphanumeric() || matches!(b, b'-' | b'.' | b'_' | b'~') {
256            out.push(b as char);
257        } else {
258            out.push_str(&format!("%{b:02X}"));
259        }
260    }
261    out
262}
263
264fn hex(bytes: &[u8]) -> String {
265    bytes.iter().map(|b| format!("{b:02x}")).collect()
266}
267
268fn register_functions(conn: &Connection) -> rusqlite::Result<()> {
269    let det = FunctionFlags::SQLITE_UTF8 | FunctionFlags::SQLITE_DETERMINISTIC;
270    conn.create_scalar_function("oxilite_regex", 3, det, |ctx| {
271        let (Some(t), Some(p)) = (text(ctx, 0)?, text(ctx, 1)?) else {
272            return Ok(None);
273        };
274        let flags = text(ctx, 2)?.unwrap_or_default();
275        Ok(sparql_regex(&p, &flags).map(|r| r.is_match(&t)))
276    })?;
277    conn.create_scalar_function("oxilite_replace", 4, det, |ctx| {
278        let (Some(t), Some(p), Some(r)) = (text(ctx, 0)?, text(ctx, 1)?, text(ctx, 2)?) else {
279            return Ok(None);
280        };
281        let flags = text(ctx, 3)?.unwrap_or_default();
282        Ok(sparql_regex(&p, &flags).map(|re| re.replace_all(&t, r.as_str()).into_owned()))
283    })?;
284    conn.create_scalar_function("oxilite_hash", 2, det, |ctx| {
285        let (Some(algo), Some(t)) = (text(ctx, 0)?, text(ctx, 1)?) else {
286            return Ok(None);
287        };
288        use sha2::Digest;
289        Ok(Some(match algo.as_str() {
290            "md5" => hex(&md5::Md5::digest(t.as_bytes())),
291            "sha1" => hex(&sha1::Sha1::digest(t.as_bytes())),
292            "sha256" => hex(&sha2::Sha256::digest(t.as_bytes())),
293            "sha384" => hex(&sha2::Sha384::digest(t.as_bytes())),
294            "sha512" => hex(&sha2::Sha512::digest(t.as_bytes())),
295            _ => return Ok(None),
296        }))
297    })?;
298    conn.create_scalar_function("oxilite_encode_for_uri", 1, det, |ctx| {
299        Ok(text(ctx, 0)?.map(|t| encode_for_uri(&t)))
300    })?;
301    conn.create_scalar_function("upper", 1, det, |ctx| {
302        Ok(text(ctx, 0)?.map(|t| t.to_uppercase()))
303    })?;
304    conn.create_scalar_function("lower", 1, det, |ctx| {
305        Ok(text(ctx, 0)?.map(|t| t.to_lowercase()))
306    })?;
307    Ok(())
308}
309
310#[cfg(test)]
311mod tests {
312    use super::*;
313
314    #[test]
315    fn atomic_requests_roll_back() {
316        let b = RusqliteBackend::memory().unwrap();
317        b.execute(&Request::atomic(vec![
318            "CREATE TABLE t (x INTEGER PRIMARY KEY)".into(),
319        ]))
320        .unwrap();
321        let r = b.execute(&Request::atomic(vec![
322            "INSERT INTO t VALUES (1)".into(),
323            "INSERT INTO t VALUES (1)".into(),
324        ]));
325        assert!(r.is_err());
326        let rows = b
327            .execute(&Request::read(vec!["SELECT COUNT(*) FROM t".into()]))
328            .unwrap();
329        assert_eq!(rows[0].rows[0][0], SqlValue::Integer(0));
330    }
331
332    #[test]
333    fn udfs() {
334        let b = RusqliteBackend::memory().unwrap();
335        let r = b
336            .execute(&Request::read(vec![
337                "SELECT oxilite_regex('Hello', '^h', 'i'), upper('straße'), oxilite_hash('md5', 'abc'), oxilite_encode_for_uri('a b')".into(),
338            ]))
339            .unwrap();
340        assert_eq!(
341            r[0].rows[0],
342            vec![
343                SqlValue::Integer(1),
344                SqlValue::Text("STRASSE".into()),
345                SqlValue::Text("900150983cd24fb0d6963f7d28e17f72".into()),
346                SqlValue::Text("a%20b".into())
347            ]
348        );
349    }
350}