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

rusqlite/types/
mod.rs
