Skip to main content

rusqlite/
pragma.rs

1//! Pragma helpers
2
3use std::ops::Deref;
4
5use crate::ffi;
6use crate::types::{ToSql, ToSqlOutput, ValueRef};
7use crate::{Connection, Result, Row};
8
9pub struct Sql {
10    buf: String,
11}
12
13impl Sql {
14    pub fn new() -> Self {
15        Self { buf: String::new() }
16    }
17
18    pub fn push_pragma(
19        &mut self,
20        conn: &Connection,
21        schema_name: Option<&str>,
22        pragma_name: &str,
23    ) -> Result<()> {
24        self.push_keyword("PRAGMA")?;
25        self.push_space();
26        if let Some(schema_name) = schema_name {
27            self.push_schema_name(schema_name);
28            self.push_dot();
29        }
30        const SQL: &str = "SELECT 1 FROM pragma_pragma_list WHERE name = ?";
31        let mut stmt = cfg_select! {
32            feature = "cache" => {
33                // no_fmt
34                conn.prepare_cached(SQL)
35            }
36            _ => {
37                // no_fmt
38                conn.prepare(SQL)
39            }
40        }?;
41        if stmt.exists([pragma_name])? {
42            self.buf.push_str(pragma_name);
43            Ok(())
44        } else {
45            Err(err!(ffi::SQLITE_MISUSE, "Invalid pragma \"{pragma_name}\""))
46        }
47    }
48
49    pub fn push_keyword(&mut self, keyword: &str) -> Result<()> {
50        if !keyword.is_empty() && is_keyword(keyword) {
51            self.buf.push_str(keyword);
52            Ok(())
53        } else {
54            Err(err!(ffi::SQLITE_MISUSE, "Invalid keyword \"{keyword}\""))
55        }
56    }
57
58    pub fn push_schema_name(&mut self, schema_name: &str) {
59        self.push_identifier(schema_name);
60    }
61
62    pub fn push_identifier(&mut self, s: &str) {
63        if is_identifier(s) {
64            self.buf.push_str(s);
65        } else {
66            self.wrap_and_escape(s, '"');
67        }
68    }
69
70    pub fn push_value(&mut self, value: &dyn ToSql) -> Result<()> {
71        let value = value.to_sql()?;
72        let value = match value {
73            ToSqlOutput::Borrowed(v) => v,
74            ToSqlOutput::Owned(ref v) => ValueRef::from(v),
75            #[cfg(any(feature = "blob", feature = "functions", feature = "pointer"))]
76            _ => {
77                return Err(err!(ffi::SQLITE_MISUSE, "Unsupported value \"{value:?}\""));
78            }
79        };
80        match value {
81            ValueRef::Integer(i) => {
82                self.push_int(i);
83            }
84            ValueRef::Real(r) => {
85                self.push_real(r);
86            }
87            ValueRef::Text(s) => {
88                let s = std::str::from_utf8(s)?;
89                self.push_string_literal(s);
90            }
91            _ => {
92                return Err(err!(ffi::SQLITE_MISUSE, "Unsupported value \"{value:?}\""));
93            }
94        }
95        Ok(())
96    }
97
98    pub fn push_string_literal(&mut self, s: &str) {
99        self.wrap_and_escape(s, '\'');
100    }
101
102    pub fn push_int(&mut self, i: i64) {
103        self.buf.push_str(&i.to_string());
104    }
105
106    pub fn push_real(&mut self, f: f64) {
107        self.buf.push_str(&f.to_string());
108    }
109
110    pub fn push_space(&mut self) {
111        self.buf.push(' ');
112    }
113
114    pub fn push_dot(&mut self) {
115        self.buf.push('.');
116    }
117
118    pub fn push_equal_sign(&mut self) {
119        self.buf.push('=');
120    }
121
122    pub fn open_brace(&mut self) {
123        self.buf.push('(');
124    }
125
126    pub fn close_brace(&mut self) {
127        self.buf.push(')');
128    }
129
130    pub fn as_str(&self) -> &str {
131        &self.buf
132    }
133
134    fn wrap_and_escape(&mut self, s: &str, quote: char) {
135        self.buf.push(quote);
136        let chars = s.chars();
137        for ch in chars {
138            // escape `quote` by doubling it
139            if ch == quote {
140                self.buf.push(ch);
141            }
142            self.buf.push(ch);
143        }
144        self.buf.push(quote);
145    }
146}
147
148impl Deref for Sql {
149    type Target = str;
150
151    fn deref(&self) -> &str {
152        self.as_str()
153    }
154}
155
156impl Connection {
157    /// Query the current value of `pragma_name`.
158    ///
159    /// Some pragmas will return multiple rows/values which cannot be retrieved
160    /// with this method.
161    ///
162    /// Prefer [PRAGMA function](https://sqlite.org/pragma.html#pragfunc) introduced in SQLite 3.20:
163    /// `SELECT user_version FROM pragma_user_version;`
164    pub fn pragma_query_value<T, F>(
165        &self,
166        schema_name: Option<&str>,
167        pragma_name: &str,
168        f: F,
169    ) -> Result<T>
170    where
171        F: FnOnce(&Row<'_>) -> Result<T>,
172    {
173        let mut query = Sql::new();
174        query.push_pragma(self, schema_name, pragma_name)?;
175        self.query_row(&query, [], f)
176    }
177
178    /// Query the current rows/values of `pragma_name`.
179    ///
180    /// Prefer [PRAGMA function](https://sqlite.org/pragma.html#pragfunc) introduced in SQLite 3.20:
181    /// `SELECT * FROM pragma_collation_list;`
182    pub fn pragma_query<F>(
183        &self,
184        schema_name: Option<&str>,
185        pragma_name: &str,
186        mut f: F,
187    ) -> Result<()>
188    where
189        F: FnMut(&Row<'_>) -> Result<()>,
190    {
191        let mut query = Sql::new();
192        query.push_pragma(self, schema_name, pragma_name)?;
193        let mut stmt = self.prepare(&query)?;
194        let mut rows = stmt.query([])?;
195        while let Some(result_row) = rows.next()? {
196            let row = result_row;
197            f(row)?;
198        }
199        Ok(())
200    }
201
202    /// Query the current value(s) of `pragma_name` associated to
203    /// `pragma_value`.
204    ///
205    /// This method can be used with query-only pragmas which need an argument
206    /// (e.g. `table_info('one_tbl')`) or pragmas which returns value(s)
207    /// (e.g. `integrity_check`).
208    ///
209    /// Prefer [PRAGMA function](https://sqlite.org/pragma.html#pragfunc) introduced in SQLite 3.20:
210    /// `SELECT * FROM pragma_table_info(?1);`
211    pub fn pragma<F, V>(
212        &self,
213        schema_name: Option<&str>,
214        pragma_name: &str,
215        pragma_value: V,
216        mut f: F,
217    ) -> Result<()>
218    where
219        F: FnMut(&Row<'_>) -> Result<()>,
220        V: ToSql,
221    {
222        let mut sql = Sql::new();
223        sql.push_pragma(self, schema_name, pragma_name)?;
224        // The argument may be either in parentheses
225        // or it may be separated from the pragma name by an equal sign.
226        // The two syntaxes yield identical results.
227        sql.open_brace();
228        sql.push_value(&pragma_value)?;
229        sql.close_brace();
230        let mut stmt = self.prepare(&sql)?;
231        let mut rows = stmt.query([])?;
232        while let Some(result_row) = rows.next()? {
233            let row = result_row;
234            f(row)?;
235        }
236        Ok(())
237    }
238
239    /// Set a new value to `pragma_name`.
240    ///
241    /// Some pragmas will return the updated value which cannot be retrieved
242    /// with this method.
243    pub fn pragma_update<V>(
244        &self,
245        schema_name: Option<&str>,
246        pragma_name: &str,
247        pragma_value: V,
248    ) -> Result<()>
249    where
250        V: ToSql,
251    {
252        let mut sql = Sql::new();
253        sql.push_pragma(self, schema_name, pragma_name)?;
254        // The argument may be either in parentheses
255        // or it may be separated from the pragma name by an equal sign.
256        // The two syntaxes yield identical results.
257        sql.push_equal_sign();
258        sql.push_value(&pragma_value)?;
259        self.execute_batch(&sql)
260    }
261
262    /// Set a new value to `pragma_name` and return the updated value.
263    ///
264    /// Only few pragmas automatically return the updated value.
265    pub fn pragma_update_and_check<F, T, V>(
266        &self,
267        schema_name: Option<&str>,
268        pragma_name: &str,
269        pragma_value: V,
270        f: F,
271    ) -> Result<T>
272    where
273        F: FnOnce(&Row<'_>) -> Result<T>,
274        V: ToSql,
275    {
276        let mut sql = Sql::new();
277        sql.push_pragma(self, schema_name, pragma_name)?;
278        // The argument may be either in parentheses
279        // or it may be separated from the pragma name by an equal sign.
280        // The two syntaxes yield identical results.
281        sql.push_equal_sign();
282        sql.push_value(&pragma_value)?;
283        self.query_row(&sql, [], f)
284    }
285}
286
287fn is_identifier(s: &str) -> bool {
288    let chars = s.char_indices();
289    for (i, ch) in chars {
290        if i == 0 {
291            if !is_identifier_start(ch) {
292                return false;
293            }
294        } else if !is_identifier_continue(ch) {
295            return false;
296        }
297    }
298    true
299}
300
301fn is_identifier_start(c: char) -> bool {
302    c.is_ascii_uppercase() || c == '_' || c.is_ascii_lowercase() || c > '\x7F'
303}
304
305fn is_identifier_continue(c: char) -> bool {
306    c == '$'
307        || c.is_ascii_digit()
308        || c.is_ascii_uppercase()
309        || c == '_'
310        || c.is_ascii_lowercase()
311        || c > '\x7F'
312}
313
314fn is_keyword(s: &str) -> bool {
315    unsafe {
316        ffi::sqlite3_keyword_check(
317            s.as_ptr().cast::<std::ffi::c_char>(),
318            s.len().try_into().unwrap(),
319        ) != 0
320    }
321}
322
323#[cfg(test)]
324mod test {
325    #[cfg(all(target_family = "wasm", target_os = "unknown"))]
326    use wasm_bindgen_test::wasm_bindgen_test as test;
327
328    use super::Sql;
329    use crate::{Connection, Result, error::error_from_sqlite_code, ffi, pragma};
330
331    #[test]
332    #[cfg_attr(miri, ignore)]
333    fn pragma_query_value() -> Result<()> {
334        let db = Connection::open_in_memory()?;
335        let user_version: i32 = db.pragma_query_value(None, "user_version", |row| row.get(0))?;
336        assert_eq!(0, user_version);
337        Ok(())
338    }
339
340    #[test]
341    #[cfg_attr(miri, ignore)]
342    fn pragma_func_query_value() -> Result<()> {
343        let db = Connection::open_in_memory()?;
344        let user_version: i32 =
345            db.one_column("SELECT user_version FROM pragma_user_version", [])?;
346        assert_eq!(0, user_version);
347        Ok(())
348    }
349
350    #[test]
351    #[cfg_attr(miri, ignore)]
352    fn pragma_query_no_schema() -> Result<()> {
353        let db = Connection::open_in_memory()?;
354        let mut user_version = -1;
355        db.pragma_query(None, "user_version", |row| {
356            user_version = row.get(0)?;
357            Ok(())
358        })?;
359        assert_eq!(0, user_version);
360        Ok(())
361    }
362
363    #[test]
364    #[cfg_attr(miri, ignore)]
365    fn pragma_query_with_schema() -> Result<()> {
366        let db = Connection::open_in_memory()?;
367        let mut user_version = -1;
368        db.pragma_query(Some("main"), "user_version", |row| {
369            user_version = row.get(0)?;
370            Ok(())
371        })?;
372        assert_eq!(0, user_version);
373        Ok(())
374    }
375
376    #[test]
377    #[cfg_attr(miri, ignore)]
378    fn pragma() -> Result<()> {
379        let db = Connection::open_in_memory()?;
380        let mut columns = Vec::new();
381        db.pragma(None, "table_info", "sqlite_master", |row| {
382            let column: String = row.get(1)?;
383            columns.push(column);
384            Ok(())
385        })?;
386        assert_eq!(5, columns.len());
387        Ok(())
388    }
389
390    #[test]
391    #[cfg_attr(miri, ignore)]
392    fn pragma_func() -> Result<()> {
393        let db = Connection::open_in_memory()?;
394        let mut table_info = db.prepare("SELECT * FROM pragma_table_info(?1)")?;
395        let mut columns = Vec::new();
396        let mut rows = table_info.query(["sqlite_master"])?;
397
398        while let Some(row) = rows.next()? {
399            let column: String = row.get(1)?;
400            columns.push(column);
401        }
402        assert_eq!(5, columns.len());
403        Ok(())
404    }
405
406    #[test]
407    #[cfg_attr(miri, ignore)]
408    fn pragma_update() -> Result<()> {
409        let db = Connection::open_in_memory()?;
410        db.pragma_update(None, "user_version", 1)
411    }
412
413    #[test]
414    #[cfg_attr(miri, ignore)]
415    fn pragma_update_and_check() -> Result<()> {
416        let db = Connection::open_in_memory()?;
417        let journal_mode: String =
418            db.pragma_update_and_check(None, "locking_mode", "EXCLUSIVE", |row| row.get(0))?;
419        assert!(journal_mode == "exclusive", "mode: {journal_mode:?}");
420        // Sanity checks to ensure the move to a generic `ToSql` wasn't breaking
421        let mode = db.pragma_update_and_check(None, "locking_mode", "EXCLUSIVE", |row| {
422            row.get::<_, String>(0)
423        })?;
424        assert!(mode == "exclusive", "mode: {mode:?}");
425
426        let param: &dyn crate::ToSql = &"EXCLUSIVE";
427        let mode =
428            db.pragma_update_and_check(None, "locking_mode", param, |row| row.get::<_, String>(0))?;
429        assert!(mode == "exclusive", "mode: {mode:?}");
430        Ok(())
431    }
432
433    #[test]
434    fn is_identifier() {
435        assert!(pragma::is_identifier("full"));
436        assert!(pragma::is_identifier("r2d2"));
437        assert!(!pragma::is_identifier("sp ce"));
438        assert!(!pragma::is_identifier("semi;colon"));
439    }
440
441    #[test]
442    fn double_quote() {
443        let mut sql = Sql::new();
444        sql.push_schema_name(r#"schema";--"#);
445        assert_eq!(r#""schema"";--""#, sql.as_str());
446    }
447
448    #[test]
449    fn wrap_and_escape() {
450        let mut sql = Sql::new();
451        sql.push_string_literal("value'; --");
452        assert_eq!("'value''; --'", sql.as_str());
453    }
454
455    #[test]
456    #[cfg_attr(miri, ignore)]
457    fn locking_mode() -> Result<()> {
458        let db = Connection::open_in_memory()?;
459        db.pragma_update(None, "locking_mode", "exclusive")?;
460        Ok(())
461    }
462
463    #[test]
464    #[cfg_attr(miri, ignore)]
465    fn invalid_pragma() -> Result<()> {
466        let db = Connection::open_in_memory()?;
467        let err = db.pragma_update(None, "boom", "1").unwrap_err();
468        assert_eq!(
469            err,
470            error_from_sqlite_code(
471                ffi::SQLITE_MISUSE,
472                Some("Invalid pragma \"boom\"".to_owned())
473            )
474        );
475        Ok(())
476    }
477}