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