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

rusqlite/types/
mod.rs
