1use 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
20pub const MIN_SQLITE_VERSION: i32 = 3_037_000;
22
23pub 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 pub fn memory() -> Result<Self> {
36 Self::from_connection(Connection::open_in_memory().map_err(err)?)
37 }
38
39 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 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 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 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
198pub 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
251pub 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}