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