rusqlite/types/
mod.rs

1//! Traits dealing with SQLite data types.
2//!
3//! SQLite uses a [dynamic type system](https://www.sqlite.org/datatype3.html). Implementations of
4//! the [`ToSql`] and [`FromSql`] traits are provided for the basic types that
5//! SQLite provides methods for:
6//!
7//! * Strings (`String` and `&str`)
8//! * Blobs (`Vec<u8>` and `&[u8]`)
9//! * Numbers
10//!
11//! The number situation is a little complicated due to the fact that all
12//! numbers in SQLite are stored as `INTEGER` (`i64`) or `REAL` (`f64`).
13//!
14//! [`ToSql`] and [`FromSql`] are implemented for all primitive number types.
15//! [`FromSql`] has different behaviour depending on the SQL and Rust types, and
16//! the value.
17//!
18//! * `INTEGER` to integer: returns an
19//!   [`Error::IntegralValueOutOfRange`](crate::Error::IntegralValueOutOfRange)
20//!   error if the value does not fit in the Rust type.
21//! * `REAL` to integer: always returns an
22//!   [`Error::InvalidColumnType`](crate::Error::InvalidColumnType) error.
23//! * `INTEGER` to float: casts using `as` operator. Never fails.
24//! * `REAL` to float: casts using `as` operator. Never fails.
25//!
26//! [`ToSql`] always succeeds except when storing a `u64` or `usize` value that
27//! cannot fit in an `INTEGER` (`i64`). Also note that SQLite ignores column
28//! types, so if you store an `i64` in a column with type `REAL` it will be
29//! stored as an `INTEGER`, not a `REAL` (unless the column is part of a
30//! [STRICT table](https://www.sqlite.org/stricttables.html)).
31//!
32//! If the `time` feature is enabled, implementations are
33//! provided for `time::OffsetDateTime` that use the RFC 3339 date/time format,
34//! `"%Y-%m-%dT%H:%M:%S.%fZ"`, to store time values as strings.  These values
35//! can be parsed by SQLite's builtin
36//! [datetime](https://www.sqlite.org/lang_datefunc.html) functions.  If you
37//! want different storage for datetimes, you can use a newtype.
38#![cfg_attr(
39    feature = "time",
40    doc = r##"
41For example, to store datetimes as `i64`s counting the number of seconds since
42the Unix epoch:
43
44```
45use rusqlite::types::{FromSql, FromSqlError, FromSqlResult, ToSql, ToSqlOutput, ValueRef};
46use rusqlite::Result;
47
48pub struct DateTimeSql(pub time::OffsetDateTime);
49
50impl FromSql for DateTimeSql {
51    fn column_result(value: ValueRef) -> FromSqlResult<Self> {
52        i64::column_result(value).and_then(|as_i64| {
53            time::OffsetDateTime::from_unix_timestamp(as_i64)
54            .map(|odt| DateTimeSql(odt))
55            .map_err(|err| FromSqlError::Other(Box::new(err)))
56        })
57    }
58}
59
60impl ToSql for DateTimeSql {
61    fn to_sql(&self) -> Result<ToSqlOutput> {
62        Ok(self.0.unix_timestamp().into())
63    }
64}
65```
66
67"##
68)]
69//! [`ToSql`] and [`FromSql`] are also implemented for `Option<T>` where `T`
70//! implements [`ToSql`] or [`FromSql`] for the cases where you want to know if
71//! a value was NULL (which gets translated to `None`).
72
73pub use self::from_sql::{FromSql, FromSqlError, FromSqlResult};
74pub use self::to_sql::{ToSql, ToSqlOutput};
75pub use self::value::Value;
76pub use self::value_ref::ValueRef;
77
78use std::fmt;
79
80#[cfg(feature = "chrono")]
81#[cfg_attr(docsrs, doc(cfg(feature = "chrono")))]
82mod chrono;
83mod from_sql;
84#[cfg(feature = "serde_json")]
85#[cfg_attr(docsrs, doc(cfg(feature = "serde_json")))]
86mod serde_json;
87#[cfg(feature = "time")]
88#[cfg_attr(docsrs, doc(cfg(feature = "time")))]
89mod time;
90mod to_sql;
91#[cfg(feature = "url")]
92#[cfg_attr(docsrs, doc(cfg(feature = "url")))]
93mod url;
94mod value;
95mod value_ref;
96
97/// Empty struct that can be used to fill in a query parameter as `NULL`.
98///
99/// ## Example
100///
101/// ```rust,no_run
102/// # use rusqlite::{Connection, Result};
103/// # use rusqlite::types::{Null};
104///
105/// fn insert_null(conn: &Connection) -> Result<usize> {
106///     conn.execute("INSERT INTO people (name) VALUES (?1)", [Null])
107/// }
108/// ```
109#[derive(Copy, Clone)]
110pub struct Null;
111
112/// SQLite data types.
113/// See [Fundamental Datatypes](https://sqlite.org/c3ref/c_blob.html).
114#[derive(Copy, Clone, Debug, PartialEq, Eq)]
115pub enum Type {
116    /// NULL
117    Null,
118    /// 64-bit signed integer
119    Integer,
120    /// 64-bit IEEE floating point number
121    Real,
122    /// String
123    Text,
124    /// BLOB
125    Blob,
126}
127
128impl fmt::Display for Type {
129    fn fmt(&self, f: &mut fmt::Formatter<'_>) -> fmt::Result {
130        match *self {
131            Type::Null => f.pad("Null"),
132            Type::Integer => f.pad("Integer"),
133            Type::Real => f.pad("Real"),
134            Type::Text => f.pad("Text"),
135            Type::Blob => f.pad("Blob"),
136        }
137    }
138}
139
140#[cfg(test)]
141mod test {
142    use super::Value;
143    use crate::{params, Connection, Error, Result, Statement};
144    use std::os::raw::{c_double, c_int};
145
146    fn checked_memory_handle() -> Result<Connection> {
147        let db = Connection::open_in_memory()?;
148        db.execute_batch("CREATE TABLE foo (b BLOB, t TEXT, i INTEGER, f FLOAT, n)")?;
149        Ok(db)
150    }
151
152    #[test]
153    fn test_blob() -> Result<()> {
154        let db = checked_memory_handle()?;
155
156        let v1234 = vec![1u8, 2, 3, 4];
157        db.execute("INSERT INTO foo(b) VALUES (?1)", [&v1234])?;
158
159        let v: Vec<u8> = db.one_column("SELECT b FROM foo")?;
160        assert_eq!(v, v1234);
161        Ok(())
162    }
163
164    #[test]
165    fn test_empty_blob() -> Result<()> {
166        let db = checked_memory_handle()?;
167
168        let empty = vec![];
169        db.execute("INSERT INTO foo(b) VALUES (?1)", [&empty])?;
170
171        let v: Vec<u8> = db.one_column("SELECT b FROM foo")?;
172        assert_eq!(v, empty);
173        Ok(())
174    }
175
176    #[test]
177    fn test_str() -> Result<()> {
178        let db = checked_memory_handle()?;
179
180        let s = "hello, world!";
181        db.execute("INSERT INTO foo(t) VALUES (?1)", [&s])?;
182
183        let from: String = db.one_column("SELECT t FROM foo")?;
184        assert_eq!(from, s);
185        Ok(())
186    }
187
188    #[test]
189    fn test_string() -> Result<()> {
190        let db = checked_memory_handle()?;
191
192        let s = "hello, world!";
193        db.execute("INSERT INTO foo(t) VALUES (?1)", [s.to_owned()])?;
194
195        let from: String = db.one_column("SELECT t FROM foo")?;
196        assert_eq!(from, s);
197        Ok(())
198    }
199
200    #[test]
201    fn test_value() -> Result<()> {
202        let db = checked_memory_handle()?;
203
204        db.execute("INSERT INTO foo(i) VALUES (?1)", [Value::Integer(10)])?;
205
206        assert_eq!(10i64, db.one_column::<i64>("SELECT i FROM foo")?);
207        Ok(())
208    }
209
210    #[test]
211    fn test_option() -> Result<()> {
212        let db = checked_memory_handle()?;
213
214        let s = "hello, world!";
215        let b = Some(vec![1u8, 2, 3, 4]);
216
217        db.execute("INSERT INTO foo(t) VALUES (?1)", [Some(s)])?;
218        db.execute("INSERT INTO foo(b) VALUES (?1)", [&b])?;
219
220        let mut stmt = db.prepare("SELECT t, b FROM foo ORDER BY ROWID ASC")?;
221        let mut rows = stmt.query([])?;
222
223        {
224            let row1 = rows.next()?.unwrap();
225            let s1: Option<String> = row1.get_unwrap(0);
226            let b1: Option<Vec<u8>> = row1.get_unwrap(1);
227            assert_eq!(s, s1.unwrap());
228            assert!(b1.is_none());
229        }
230
231        {
232            let row2 = rows.next()?.unwrap();
233            let s2: Option<String> = row2.get_unwrap(0);
234            let b2: Option<Vec<u8>> = row2.get_unwrap(1);
235            assert!(s2.is_none());
236            assert_eq!(b, b2);
237        }
238        Ok(())
239    }
240
241    #[test]
242    #[allow(clippy::cognitive_complexity)]
243    fn test_mismatched_types() -> Result<()> {
244        fn is_invalid_column_type(err: Error) -> bool {
245            matches!(err, Error::InvalidColumnType(..))
246        }
247
248        let db = checked_memory_handle()?;
249
250        db.execute(
251            "INSERT INTO foo(b, t, i, f) VALUES (X'0102', 'text', 1, 1.5)",
252            [],
253        )?;
254
255        let mut stmt = db.prepare("SELECT b, t, i, f, n FROM foo")?;
256        let mut rows = stmt.query([])?;
257
258        let row = rows.next()?.unwrap();
259
260        // check the correct types come back as expected
261        assert_eq!(vec![1, 2], row.get::<_, Vec<u8>>(0)?);
262        assert_eq!("text", row.get::<_, String>(1)?);
263        assert_eq!(1, row.get::<_, c_int>(2)?);
264        assert!((1.5 - row.get::<_, c_double>(3)?).abs() < f64::EPSILON);
265        assert_eq!(row.get::<_, Option<c_int>>(4)?, None);
266        assert_eq!(row.get::<_, Option<c_double>>(4)?, None);
267        assert_eq!(row.get::<_, Option<String>>(4)?, None);
268
269        // check some invalid types
270
271        // 0 is actually a blob (Vec<u8>)
272        assert!(is_invalid_column_type(row.get::<_, c_int>(0).unwrap_err()));
273        assert!(is_invalid_column_type(row.get::<_, c_int>(0).unwrap_err()));
274        assert!(is_invalid_column_type(row.get::<_, i64>(0).err().unwrap()));
275        assert!(is_invalid_column_type(
276            row.get::<_, c_double>(0).unwrap_err()
277        ));
278        assert!(is_invalid_column_type(row.get::<_, String>(0).unwrap_err()));
279        #[cfg(feature = "time")]
280        assert!(is_invalid_column_type(
281            row.get::<_, time::OffsetDateTime>(0).unwrap_err()
282        ));
283        assert!(is_invalid_column_type(
284            row.get::<_, Option<c_int>>(0).unwrap_err()
285        ));
286
287        // 1 is actually a text (String)
288        assert!(is_invalid_column_type(row.get::<_, c_int>(1).unwrap_err()));
289        assert!(is_invalid_column_type(row.get::<_, i64>(1).err().unwrap()));
290        assert!(is_invalid_column_type(
291            row.get::<_, c_double>(1).unwrap_err()
292        ));
293        assert!(is_invalid_column_type(
294            row.get::<_, Vec<u8>>(1).unwrap_err()
295        ));
296        assert!(is_invalid_column_type(
297            row.get::<_, Option<c_int>>(1).unwrap_err()
298        ));
299
300        // 2 is actually an integer
301        assert!(is_invalid_column_type(row.get::<_, String>(2).unwrap_err()));
302        assert!(is_invalid_column_type(
303            row.get::<_, Vec<u8>>(2).unwrap_err()
304        ));
305        assert!(is_invalid_column_type(
306            row.get::<_, Option<String>>(2).unwrap_err()
307        ));
308
309        // 3 is actually a float (c_double)
310        assert!(is_invalid_column_type(row.get::<_, c_int>(3).unwrap_err()));
311        assert!(is_invalid_column_type(row.get::<_, i64>(3).err().unwrap()));
312        assert!(is_invalid_column_type(row.get::<_, String>(3).unwrap_err()));
313        assert!(is_invalid_column_type(
314            row.get::<_, Vec<u8>>(3).unwrap_err()
315        ));
316        assert!(is_invalid_column_type(
317            row.get::<_, Option<c_int>>(3).unwrap_err()
318        ));
319
320        // 4 is actually NULL
321        assert!(is_invalid_column_type(row.get::<_, c_int>(4).unwrap_err()));
322        assert!(is_invalid_column_type(row.get::<_, i64>(4).err().unwrap()));
323        assert!(is_invalid_column_type(
324            row.get::<_, c_double>(4).unwrap_err()
325        ));
326        assert!(is_invalid_column_type(row.get::<_, String>(4).unwrap_err()));
327        assert!(is_invalid_column_type(
328            row.get::<_, Vec<u8>>(4).unwrap_err()
329        ));
330        #[cfg(feature = "time")]
331        assert!(is_invalid_column_type(
332            row.get::<_, time::OffsetDateTime>(4).unwrap_err()
333        ));
334        Ok(())
335    }
336
337    #[test]
338    fn test_dynamic_type() -> Result<()> {
339        use super::Value;
340        let db = checked_memory_handle()?;
341
342        db.execute(
343            "INSERT INTO foo(b, t, i, f) VALUES (X'0102', 'text', 1, 1.5)",
344            [],
345        )?;
346
347        let mut stmt = db.prepare("SELECT b, t, i, f, n FROM foo")?;
348        let mut rows = stmt.query([])?;
349
350        let row = rows.next()?.unwrap();
351        assert_eq!(Value::Blob(vec![1, 2]), row.get::<_, Value>(0)?);
352        assert_eq!(Value::Text(String::from("text")), row.get::<_, Value>(1)?);
353        assert_eq!(Value::Integer(1), row.get::<_, Value>(2)?);
354        match row.get::<_, Value>(3)? {
355            Value::Real(val) => assert!((1.5 - val).abs() < f64::EPSILON),
356            x => panic!("Invalid Value {x:?}"),
357        }
358        assert_eq!(Value::Null, row.get::<_, Value>(4)?);
359        Ok(())
360    }
361
362    macro_rules! test_conversion {
363        ($db_etc:ident, $insert_value:expr, $get_type:ty,expect $expected_value:expr) => {
364            $db_etc.insert_statement.execute(params![$insert_value])?;
365            let res = $db_etc
366                .query_statement
367                .query_row([], |row| row.get::<_, $get_type>(0));
368            assert_eq!(res?, $expected_value);
369            $db_etc.delete_statement.execute([])?;
370        };
371        ($db_etc:ident, $insert_value:expr, $get_type:ty,expect_from_sql_error) => {
372            $db_etc.insert_statement.execute(params![$insert_value])?;
373            let res = $db_etc
374                .query_statement
375                .query_row([], |row| row.get::<_, $get_type>(0));
376            res.unwrap_err();
377            $db_etc.delete_statement.execute([])?;
378        };
379        ($db_etc:ident, $insert_value:expr, $get_type:ty,expect_to_sql_error) => {
380            $db_etc
381                .insert_statement
382                .execute(params![$insert_value])
383                .unwrap_err();
384        };
385    }
386
387    #[test]
388    fn test_numeric_conversions() -> Result<()> {
389        #![allow(clippy::float_cmp)]
390
391        // Test what happens when we store an f32 and retrieve an i32 etc.
392        let db = Connection::open_in_memory()?;
393        db.execute_batch("CREATE TABLE foo (x)")?;
394
395        // SQLite actually ignores the column types, so we just need to test
396        // different numeric values.
397
398        struct DbEtc<'conn> {
399            insert_statement: Statement<'conn>,
400            query_statement: Statement<'conn>,
401            delete_statement: Statement<'conn>,
402        }
403
404        let mut db_etc = DbEtc {
405            insert_statement: db.prepare("INSERT INTO foo VALUES (?1)")?,
406            query_statement: db.prepare("SELECT x FROM foo")?,
407            delete_statement: db.prepare("DELETE FROM foo")?,
408        };
409
410        // Basic non-converting test.
411        test_conversion!(db_etc, 0u8, u8, expect 0u8);
412
413        // In-range integral conversions.
414        test_conversion!(db_etc, 100u8, i8, expect 100i8);
415        test_conversion!(db_etc, 200u8, u8, expect 200u8);
416        test_conversion!(db_etc, 100u16, i8, expect 100i8);
417        test_conversion!(db_etc, 200u16, u8, expect 200u8);
418        test_conversion!(db_etc, u32::MAX, u64, expect u32::MAX as u64);
419        test_conversion!(db_etc, i64::MIN, i64, expect i64::MIN);
420        test_conversion!(db_etc, i64::MAX, i64, expect i64::MAX);
421        test_conversion!(db_etc, i64::MAX, u64, expect i64::MAX as u64);
422        test_conversion!(db_etc, 100usize, usize, expect 100usize);
423        test_conversion!(db_etc, 100u64, u64, expect 100u64);
424        test_conversion!(db_etc, i64::MAX as u64, u64, expect i64::MAX as u64);
425
426        // Out-of-range integral conversions.
427        test_conversion!(db_etc, 200u8, i8, expect_from_sql_error);
428        test_conversion!(db_etc, 400u16, i8, expect_from_sql_error);
429        test_conversion!(db_etc, 400u16, u8, expect_from_sql_error);
430        test_conversion!(db_etc, -1i8, u8, expect_from_sql_error);
431        test_conversion!(db_etc, i64::MIN, u64, expect_from_sql_error);
432        test_conversion!(db_etc, u64::MAX, i64, expect_to_sql_error);
433        test_conversion!(db_etc, u64::MAX, u64, expect_to_sql_error);
434        test_conversion!(db_etc, i64::MAX as u64 + 1, u64, expect_to_sql_error);
435
436        // FromSql integer to float, always works.
437        test_conversion!(db_etc, i64::MIN, f32, expect i64::MIN as f32);
438        test_conversion!(db_etc, i64::MAX, f32, expect i64::MAX as f32);
439        test_conversion!(db_etc, i64::MIN, f64, expect i64::MIN as f64);
440        test_conversion!(db_etc, i64::MAX, f64, expect i64::MAX as f64);
441
442        // FromSql float to int conversion, never works even if the actual value
443        // is an integer.
444        test_conversion!(db_etc, 0f64, i64, expect_from_sql_error);
445        Ok(())
446    }
447}
rusqlite/types/mod.rs

rusqlite/types/
mod.rs
