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 //! * Integers (`i32` and `i64`; SQLite uses `i64` internally, so getting an
8 //! `i32` will truncate   if the value is too large or too small).
9 //! * Reals (`f64`)
10 //! * Strings (`String` and `&str`)
11 //! * Blobs (`Vec<u8>` and `&[u8]`)
12 //!
13 //! Additionally, because it is such a common data type, implementations are
14 //! provided for `time::Timespec` that use the RFC 3339 date/time format,
15 //! `"%Y-%m-%dT%H:%M:%S.%fZ"`, to store time values as strings.  These values
16 //! can be parsed by SQLite's builtin
17 //! [datetime](https://www.sqlite.org/lang_datefunc.html) functions.  If you
18 //! want different storage for timespecs, you can use a newtype. For example, to
19 //! store timespecs as `f64`s:
20 //!
21 //! ```rust
22 //! use rusqlite::types::{FromSql, FromSqlResult, ToSql, ToSqlOutput, ValueRef};
23 //! use rusqlite::Result;
24 //!
25 //! pub struct TimespecSql(pub time::Timespec);
26 //!
27 //! impl FromSql for TimespecSql {
28 //!     fn column_result(value: ValueRef) -> FromSqlResult<Self> {
29 //!         f64::column_result(value).map(|as_f64| {
30 //!             TimespecSql(time::Timespec {
31 //!                 sec: as_f64.trunc() as i64,
32 //!                 nsec: (as_f64.fract() * 1.0e9) as i32,
33 //!             })
34 //!         })
35 //!     }
36 //! }
37 //!
38 //! impl ToSql for TimespecSql {
39 //!     fn to_sql(&self) -> Result<ToSqlOutput> {
40 //!         let TimespecSql(ts) = *self;
41 //!         let as_f64 = ts.sec as f64 + (ts.nsec as f64) / 1.0e9;
42 //!         Ok(as_f64.into())
43 //!     }
44 //! }
45 //!
46 //! # // Prevent this doc test from being wrapped in a `fn main()` so that it
47 //! # // will compile.
48 //! # fn main() {}
49 //! ```
50 //!
51 //! `ToSql` and `FromSql` are also implemented for `Option<T>` where `T`
52 //! implements `ToSql` or `FromSql` for the cases where you want to know if a
53 //! value was NULL (which gets translated to `None`).
54 
55 pub use self::from_sql::{FromSql, FromSqlError, FromSqlResult};
56 pub use self::to_sql::{ToSql, ToSqlOutput};
57 pub use self::value::Value;
58 pub use self::value_ref::ValueRef;
59 
60 use std::fmt;
61 
62 #[cfg(feature = "chrono")]
63 mod chrono;
64 mod from_sql;
65 #[cfg(feature = "serde_json")]
66 mod serde_json;
67 mod time;
68 mod to_sql;
69 #[cfg(feature = "url")]
70 mod url;
71 mod value;
72 mod value_ref;
73 
74 /// Empty struct that can be used to fill in a query parameter as `NULL`.
75 ///
76 /// ## Example
77 ///
78 /// ```rust,no_run
79 /// # use rusqlite::{Connection, Result};
80 /// # use rusqlite::types::{Null};
81 /// fn main() {}
82 /// fn insert_null(conn: &Connection) -> Result<usize> {
83 ///     conn.execute("INSERT INTO people (name) VALUES (?)", &[Null])
84 /// }
85 /// ```
86 #[derive(Copy, Clone)]
87 pub struct Null;
88 
89 #[derive(Clone, Debug, PartialEq)]
90 pub enum Type {
91     Null,
92     Integer,
93     Real,
94     Text,
95     Blob,
96 }
97 
98 impl fmt::Display for Type {
fmt(&self, f: &mut fmt::Formatter<'_>) -> fmt::Result99     fn fmt(&self, f: &mut fmt::Formatter<'_>) -> fmt::Result {
100         match *self {
101             Type::Null => write!(f, "Null"),
102             Type::Integer => write!(f, "Integer"),
103             Type::Real => write!(f, "Real"),
104             Type::Text => write!(f, "Text"),
105             Type::Blob => write!(f, "Blob"),
106         }
107     }
108 }
109 
110 #[cfg(test)]
111 mod test {
112     use time;
113 
114     use super::Value;
115     use crate::{Connection, Error, NO_PARAMS};
116     use std::f64::EPSILON;
117     use std::os::raw::{c_double, c_int};
118 
checked_memory_handle() -> Connection119     fn checked_memory_handle() -> Connection {
120         let db = Connection::open_in_memory().unwrap();
121         db.execute_batch("CREATE TABLE foo (b BLOB, t TEXT, i INTEGER, f FLOAT, n)")
122             .unwrap();
123         db
124     }
125 
126     #[test]
test_blob()127     fn test_blob() {
128         let db = checked_memory_handle();
129 
130         let v1234 = vec![1u8, 2, 3, 4];
131         db.execute("INSERT INTO foo(b) VALUES (?)", &[&v1234])
132             .unwrap();
133 
134         let v: Vec<u8> = db
135             .query_row("SELECT b FROM foo", NO_PARAMS, |r| r.get(0))
136             .unwrap();
137         assert_eq!(v, v1234);
138     }
139 
140     #[test]
test_empty_blob()141     fn test_empty_blob() {
142         let db = checked_memory_handle();
143 
144         let empty = vec![];
145         db.execute("INSERT INTO foo(b) VALUES (?)", &[&empty])
146             .unwrap();
147 
148         let v: Vec<u8> = db
149             .query_row("SELECT b FROM foo", NO_PARAMS, |r| r.get(0))
150             .unwrap();
151         assert_eq!(v, empty);
152     }
153 
154     #[test]
test_str()155     fn test_str() {
156         let db = checked_memory_handle();
157 
158         let s = "hello, world!";
159         db.execute("INSERT INTO foo(t) VALUES (?)", &[&s]).unwrap();
160 
161         let from: String = db
162             .query_row("SELECT t FROM foo", NO_PARAMS, |r| r.get(0))
163             .unwrap();
164         assert_eq!(from, s);
165     }
166 
167     #[test]
test_string()168     fn test_string() {
169         let db = checked_memory_handle();
170 
171         let s = "hello, world!";
172         db.execute("INSERT INTO foo(t) VALUES (?)", &[s.to_owned()])
173             .unwrap();
174 
175         let from: String = db
176             .query_row("SELECT t FROM foo", NO_PARAMS, |r| r.get(0))
177             .unwrap();
178         assert_eq!(from, s);
179     }
180 
181     #[test]
test_value()182     fn test_value() {
183         let db = checked_memory_handle();
184 
185         db.execute("INSERT INTO foo(i) VALUES (?)", &[Value::Integer(10)])
186             .unwrap();
187 
188         assert_eq!(
189             10i64,
190             db.query_row::<i64, _, _>("SELECT i FROM foo", NO_PARAMS, |r| r.get(0))
191                 .unwrap()
192         );
193     }
194 
195     #[test]
test_option()196     fn test_option() {
197         let db = checked_memory_handle();
198 
199         let s = Some("hello, world!");
200         let b = Some(vec![1u8, 2, 3, 4]);
201 
202         db.execute("INSERT INTO foo(t) VALUES (?)", &[&s]).unwrap();
203         db.execute("INSERT INTO foo(b) VALUES (?)", &[&b]).unwrap();
204 
205         let mut stmt = db
206             .prepare("SELECT t, b FROM foo ORDER BY ROWID ASC")
207             .unwrap();
208         let mut rows = stmt.query(NO_PARAMS).unwrap();
209 
210         {
211             let row1 = rows.next().unwrap().unwrap();
212             let s1: Option<String> = row1.get_unwrap(0);
213             let b1: Option<Vec<u8>> = row1.get_unwrap(1);
214             assert_eq!(s.unwrap(), s1.unwrap());
215             assert!(b1.is_none());
216         }
217 
218         {
219             let row2 = rows.next().unwrap().unwrap();
220             let s2: Option<String> = row2.get_unwrap(0);
221             let b2: Option<Vec<u8>> = row2.get_unwrap(1);
222             assert!(s2.is_none());
223             assert_eq!(b, b2);
224         }
225     }
226 
227     #[test]
228     #[allow(clippy::cognitive_complexity)]
test_mismatched_types()229     fn test_mismatched_types() {
230         fn is_invalid_column_type(err: Error) -> bool {
231             match err {
232                 Error::InvalidColumnType(_, _, _) => true,
233                 _ => false,
234             }
235         }
236 
237         let db = checked_memory_handle();
238 
239         db.execute(
240             "INSERT INTO foo(b, t, i, f) VALUES (X'0102', 'text', 1, 1.5)",
241             NO_PARAMS,
242         )
243         .unwrap();
244 
245         let mut stmt = db.prepare("SELECT b, t, i, f, n FROM foo").unwrap();
246         let mut rows = stmt.query(NO_PARAMS).unwrap();
247 
248         let row = rows.next().unwrap().unwrap();
249 
250         // check the correct types come back as expected
251         assert_eq!(vec![1, 2], row.get::<_, Vec<u8>>(0).unwrap());
252         assert_eq!("text", row.get::<_, String>(1).unwrap());
253         assert_eq!(1, row.get::<_, c_int>(2).unwrap());
254         assert!((1.5 - row.get::<_, c_double>(3).unwrap()).abs() < EPSILON);
255         assert!(row.get::<_, Option<c_int>>(4).unwrap().is_none());
256         assert!(row.get::<_, Option<c_double>>(4).unwrap().is_none());
257         assert!(row.get::<_, Option<String>>(4).unwrap().is_none());
258 
259         // check some invalid types
260 
261         // 0 is actually a blob (Vec<u8>)
262         assert!(is_invalid_column_type(
263             row.get::<_, c_int>(0).err().unwrap()
264         ));
265         assert!(is_invalid_column_type(
266             row.get::<_, c_int>(0).err().unwrap()
267         ));
268         assert!(is_invalid_column_type(row.get::<_, i64>(0).err().unwrap()));
269         assert!(is_invalid_column_type(
270             row.get::<_, c_double>(0).err().unwrap()
271         ));
272         assert!(is_invalid_column_type(
273             row.get::<_, String>(0).err().unwrap()
274         ));
275         assert!(is_invalid_column_type(
276             row.get::<_, time::Timespec>(0).err().unwrap()
277         ));
278         assert!(is_invalid_column_type(
279             row.get::<_, Option<c_int>>(0).err().unwrap()
280         ));
281 
282         // 1 is actually a text (String)
283         assert!(is_invalid_column_type(
284             row.get::<_, c_int>(1).err().unwrap()
285         ));
286         assert!(is_invalid_column_type(row.get::<_, i64>(1).err().unwrap()));
287         assert!(is_invalid_column_type(
288             row.get::<_, c_double>(1).err().unwrap()
289         ));
290         assert!(is_invalid_column_type(
291             row.get::<_, Vec<u8>>(1).err().unwrap()
292         ));
293         assert!(is_invalid_column_type(
294             row.get::<_, Option<c_int>>(1).err().unwrap()
295         ));
296 
297         // 2 is actually an integer
298         assert!(is_invalid_column_type(
299             row.get::<_, String>(2).err().unwrap()
300         ));
301         assert!(is_invalid_column_type(
302             row.get::<_, Vec<u8>>(2).err().unwrap()
303         ));
304         assert!(is_invalid_column_type(
305             row.get::<_, Option<String>>(2).err().unwrap()
306         ));
307 
308         // 3 is actually a float (c_double)
309         assert!(is_invalid_column_type(
310             row.get::<_, c_int>(3).err().unwrap()
311         ));
312         assert!(is_invalid_column_type(row.get::<_, i64>(3).err().unwrap()));
313         assert!(is_invalid_column_type(
314             row.get::<_, String>(3).err().unwrap()
315         ));
316         assert!(is_invalid_column_type(
317             row.get::<_, Vec<u8>>(3).err().unwrap()
318         ));
319         assert!(is_invalid_column_type(
320             row.get::<_, Option<c_int>>(3).err().unwrap()
321         ));
322 
323         // 4 is actually NULL
324         assert!(is_invalid_column_type(
325             row.get::<_, c_int>(4).err().unwrap()
326         ));
327         assert!(is_invalid_column_type(row.get::<_, i64>(4).err().unwrap()));
328         assert!(is_invalid_column_type(
329             row.get::<_, c_double>(4).err().unwrap()
330         ));
331         assert!(is_invalid_column_type(
332             row.get::<_, String>(4).err().unwrap()
333         ));
334         assert!(is_invalid_column_type(
335             row.get::<_, Vec<u8>>(4).err().unwrap()
336         ));
337         assert!(is_invalid_column_type(
338             row.get::<_, time::Timespec>(4).err().unwrap()
339         ));
340     }
341 
342     #[test]
test_dynamic_type()343     fn test_dynamic_type() {
344         use super::Value;
345         let db = checked_memory_handle();
346 
347         db.execute(
348             "INSERT INTO foo(b, t, i, f) VALUES (X'0102', 'text', 1, 1.5)",
349             NO_PARAMS,
350         )
351         .unwrap();
352 
353         let mut stmt = db.prepare("SELECT b, t, i, f, n FROM foo").unwrap();
354         let mut rows = stmt.query(NO_PARAMS).unwrap();
355 
356         let row = rows.next().unwrap().unwrap();
357         assert_eq!(Value::Blob(vec![1, 2]), row.get::<_, Value>(0).unwrap());
358         assert_eq!(
359             Value::Text(String::from("text")),
360             row.get::<_, Value>(1).unwrap()
361         );
362         assert_eq!(Value::Integer(1), row.get::<_, Value>(2).unwrap());
363         match row.get::<_, Value>(3).unwrap() {
364             Value::Real(val) => assert!((1.5 - val).abs() < EPSILON),
365             x => panic!("Invalid Value {:?}", x),
366         }
367         assert_eq!(Value::Null, row.get::<_, Value>(4).unwrap());
368     }
369 }
370