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##" 40 For example, to store datetimes as `i64`s counting the number of seconds since 41 the Unix epoch: 42 43 ``` 44 use rusqlite::types::{FromSql, FromSqlError, FromSqlResult, ToSql, ToSqlOutput, ValueRef}; 45 use rusqlite::Result; 46 47 pub struct DateTimeSql(pub time::OffsetDateTime); 48 49 impl 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 59 impl 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 72 pub use self::from_sql::{FromSql, FromSqlError, FromSqlResult}; 73 pub use self::to_sql::{ToSql, ToSqlOutput}; 74 pub use self::value::Value; 75 pub use self::value_ref::ValueRef; 76 77 use std::fmt; 78 79 #[cfg(feature = "chrono")] 80 #[cfg_attr(docsrs, doc(cfg(feature = "chrono")))] 81 mod chrono; 82 mod from_sql; 83 #[cfg(feature = "serde_json")] 84 #[cfg_attr(docsrs, doc(cfg(feature = "serde_json")))] 85 mod serde_json; 86 #[cfg(feature = "time")] 87 #[cfg_attr(docsrs, doc(cfg(feature = "time")))] 88 mod time; 89 mod to_sql; 90 #[cfg(feature = "url")] 91 #[cfg_attr(docsrs, doc(cfg(feature = "url")))] 92 mod url; 93 mod value; 94 mod 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 (?1)", [Null]) 106 /// } 107 /// ``` 108 #[derive(Copy, Clone)] 109 pub struct Null; 110 111 /// SQLite data types. 112 /// See [Fundamental Datatypes](https://sqlite.org/c3ref/c_blob.html). 113 #[derive(Clone, Debug, PartialEq, Eq)] 114 pub 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 127 impl fmt::Display for Type { fmt(&self, f: &mut fmt::Formatter<'_>) -> fmt::Result128 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)] 140 mod test { 141 use super::Value; 142 use crate::{params, Connection, Error, Result, Statement}; 143 use std::os::raw::{c_double, c_int}; 144 checked_memory_handle() -> Result<Connection>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] test_blob() -> Result<()>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 (?1)", [&v1234])?; 157 158 let v: Vec<u8> = db.one_column("SELECT b FROM foo")?; 159 assert_eq!(v, v1234); 160 Ok(()) 161 } 162 163 #[test] test_empty_blob() -> Result<()>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 (?1)", [&empty])?; 169 170 let v: Vec<u8> = db.one_column("SELECT b FROM foo")?; 171 assert_eq!(v, empty); 172 Ok(()) 173 } 174 175 #[test] test_str() -> Result<()>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 (?1)", [&s])?; 181 182 let from: String = db.one_column("SELECT t FROM foo")?; 183 assert_eq!(from, s); 184 Ok(()) 185 } 186 187 #[test] test_string() -> Result<()>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 (?1)", [s.to_owned()])?; 193 194 let from: String = db.one_column("SELECT t FROM foo")?; 195 assert_eq!(from, s); 196 Ok(()) 197 } 198 199 #[test] test_value() -> Result<()>200 fn test_value() -> Result<()> { 201 let db = checked_memory_handle()?; 202 203 db.execute("INSERT INTO foo(i) VALUES (?1)", [Value::Integer(10)])?; 204 205 assert_eq!(10i64, db.one_column::<i64>("SELECT i FROM foo")?); 206 Ok(()) 207 } 208 209 #[test] test_option() -> Result<()>210 fn test_option() -> Result<()> { 211 let db = checked_memory_handle()?; 212 213 let s = Some("hello, world!"); 214 let b = Some(vec![1u8, 2, 3, 4]); 215 216 db.execute("INSERT INTO foo(t) VALUES (?1)", [&s])?; 217 db.execute("INSERT INTO foo(b) VALUES (?1)", [&b])?; 218 219 let mut stmt = db.prepare("SELECT t, b FROM foo ORDER BY ROWID ASC")?; 220 let mut rows = stmt.query([])?; 221 222 { 223 let row1 = rows.next()?.unwrap(); 224 let s1: Option<String> = row1.get_unwrap(0); 225 let b1: Option<Vec<u8>> = row1.get_unwrap(1); 226 assert_eq!(s.unwrap(), s1.unwrap()); 227 assert!(b1.is_none()); 228 } 229 230 { 231 let row2 = rows.next()?.unwrap(); 232 let s2: Option<String> = row2.get_unwrap(0); 233 let b2: Option<Vec<u8>> = row2.get_unwrap(1); 234 assert!(s2.is_none()); 235 assert_eq!(b, b2); 236 } 237 Ok(()) 238 } 239 240 #[test] 241 #[allow(clippy::cognitive_complexity)] test_mismatched_types() -> Result<()>242 fn test_mismatched_types() -> Result<()> { 243 fn is_invalid_column_type(err: Error) -> bool { 244 matches!(err, Error::InvalidColumnType(..)) 245 } 246 247 let db = checked_memory_handle()?; 248 249 db.execute( 250 "INSERT INTO foo(b, t, i, f) VALUES (X'0102', 'text', 1, 1.5)", 251 [], 252 )?; 253 254 let mut stmt = db.prepare("SELECT b, t, i, f, n FROM foo")?; 255 let mut rows = stmt.query([])?; 256 257 let row = rows.next()?.unwrap(); 258 259 // check the correct types come back as expected 260 assert_eq!(vec![1, 2], row.get::<_, Vec<u8>>(0)?); 261 assert_eq!("text", row.get::<_, String>(1)?); 262 assert_eq!(1, row.get::<_, c_int>(2)?); 263 assert!((1.5 - row.get::<_, c_double>(3)?).abs() < f64::EPSILON); 264 assert_eq!(row.get::<_, Option<c_int>>(4)?, None); 265 assert_eq!(row.get::<_, Option<c_double>>(4)?, None); 266 assert_eq!(row.get::<_, Option<String>>(4)?, None); 267 268 // check some invalid types 269 270 // 0 is actually a blob (Vec<u8>) 271 assert!(is_invalid_column_type(row.get::<_, c_int>(0).unwrap_err())); 272 assert!(is_invalid_column_type(row.get::<_, c_int>(0).unwrap_err())); 273 assert!(is_invalid_column_type(row.get::<_, i64>(0).err().unwrap())); 274 assert!(is_invalid_column_type( 275 row.get::<_, c_double>(0).unwrap_err() 276 )); 277 assert!(is_invalid_column_type(row.get::<_, String>(0).unwrap_err())); 278 #[cfg(feature = "time")] 279 assert!(is_invalid_column_type( 280 row.get::<_, time::OffsetDateTime>(0).unwrap_err() 281 )); 282 assert!(is_invalid_column_type( 283 row.get::<_, Option<c_int>>(0).unwrap_err() 284 )); 285 286 // 1 is actually a text (String) 287 assert!(is_invalid_column_type(row.get::<_, c_int>(1).unwrap_err())); 288 assert!(is_invalid_column_type(row.get::<_, i64>(1).err().unwrap())); 289 assert!(is_invalid_column_type( 290 row.get::<_, c_double>(1).unwrap_err() 291 )); 292 assert!(is_invalid_column_type( 293 row.get::<_, Vec<u8>>(1).unwrap_err() 294 )); 295 assert!(is_invalid_column_type( 296 row.get::<_, Option<c_int>>(1).unwrap_err() 297 )); 298 299 // 2 is actually an integer 300 assert!(is_invalid_column_type(row.get::<_, String>(2).unwrap_err())); 301 assert!(is_invalid_column_type( 302 row.get::<_, Vec<u8>>(2).unwrap_err() 303 )); 304 assert!(is_invalid_column_type( 305 row.get::<_, Option<String>>(2).unwrap_err() 306 )); 307 308 // 3 is actually a float (c_double) 309 assert!(is_invalid_column_type(row.get::<_, c_int>(3).unwrap_err())); 310 assert!(is_invalid_column_type(row.get::<_, i64>(3).err().unwrap())); 311 assert!(is_invalid_column_type(row.get::<_, String>(3).unwrap_err())); 312 assert!(is_invalid_column_type( 313 row.get::<_, Vec<u8>>(3).unwrap_err() 314 )); 315 assert!(is_invalid_column_type( 316 row.get::<_, Option<c_int>>(3).unwrap_err() 317 )); 318 319 // 4 is actually NULL 320 assert!(is_invalid_column_type(row.get::<_, c_int>(4).unwrap_err())); 321 assert!(is_invalid_column_type(row.get::<_, i64>(4).err().unwrap())); 322 assert!(is_invalid_column_type( 323 row.get::<_, c_double>(4).unwrap_err() 324 )); 325 assert!(is_invalid_column_type(row.get::<_, String>(4).unwrap_err())); 326 assert!(is_invalid_column_type( 327 row.get::<_, Vec<u8>>(4).unwrap_err() 328 )); 329 #[cfg(feature = "time")] 330 assert!(is_invalid_column_type( 331 row.get::<_, time::OffsetDateTime>(4).unwrap_err() 332 )); 333 Ok(()) 334 } 335 336 #[test] test_dynamic_type() -> Result<()>337 fn test_dynamic_type() -> Result<()> { 338 use super::Value; 339 let db = checked_memory_handle()?; 340 341 db.execute( 342 "INSERT INTO foo(b, t, i, f) VALUES (X'0102', 'text', 1, 1.5)", 343 [], 344 )?; 345 346 let mut stmt = db.prepare("SELECT b, t, i, f, n FROM foo")?; 347 let mut rows = stmt.query([])?; 348 349 let row = rows.next()?.unwrap(); 350 assert_eq!(Value::Blob(vec![1, 2]), row.get::<_, Value>(0)?); 351 assert_eq!(Value::Text(String::from("text")), row.get::<_, Value>(1)?); 352 assert_eq!(Value::Integer(1), row.get::<_, Value>(2)?); 353 match row.get::<_, Value>(3)? { 354 Value::Real(val) => assert!((1.5 - val).abs() < f64::EPSILON), 355 x => panic!("Invalid Value {:?}", x), 356 } 357 assert_eq!(Value::Null, row.get::<_, Value>(4)?); 358 Ok(()) 359 } 360 361 macro_rules! test_conversion { 362 ($db_etc:ident, $insert_value:expr, $get_type:ty,expect $expected_value:expr) => { 363 $db_etc.insert_statement.execute(params![$insert_value])?; 364 let res = $db_etc 365 .query_statement 366 .query_row([], |row| row.get::<_, $get_type>(0)); 367 assert_eq!(res?, $expected_value); 368 $db_etc.delete_statement.execute([])?; 369 }; 370 ($db_etc:ident, $insert_value:expr, $get_type:ty,expect_from_sql_error) => { 371 $db_etc.insert_statement.execute(params![$insert_value])?; 372 let res = $db_etc 373 .query_statement 374 .query_row([], |row| row.get::<_, $get_type>(0)); 375 res.unwrap_err(); 376 $db_etc.delete_statement.execute([])?; 377 }; 378 ($db_etc:ident, $insert_value:expr, $get_type:ty,expect_to_sql_error) => { 379 $db_etc 380 .insert_statement 381 .execute(params![$insert_value]) 382 .unwrap_err(); 383 }; 384 } 385 386 #[test] test_numeric_conversions() -> Result<()>387 fn test_numeric_conversions() -> Result<()> { 388 #![allow(clippy::float_cmp)] 389 390 // Test what happens when we store an f32 and retrieve an i32 etc. 391 let db = Connection::open_in_memory()?; 392 db.execute_batch("CREATE TABLE foo (x)")?; 393 394 // SQLite actually ignores the column types, so we just need to test 395 // different numeric values. 396 397 struct DbEtc<'conn> { 398 insert_statement: Statement<'conn>, 399 query_statement: Statement<'conn>, 400 delete_statement: Statement<'conn>, 401 } 402 403 let mut db_etc = DbEtc { 404 insert_statement: db.prepare("INSERT INTO foo VALUES (?1)")?, 405 query_statement: db.prepare("SELECT x FROM foo")?, 406 delete_statement: db.prepare("DELETE FROM foo")?, 407 }; 408 409 // Basic non-converting test. 410 test_conversion!(db_etc, 0u8, u8, expect 0u8); 411 412 // In-range integral conversions. 413 test_conversion!(db_etc, 100u8, i8, expect 100i8); 414 test_conversion!(db_etc, 200u8, u8, expect 200u8); 415 test_conversion!(db_etc, 100u16, i8, expect 100i8); 416 test_conversion!(db_etc, 200u16, u8, expect 200u8); 417 test_conversion!(db_etc, u32::MAX, u64, expect u32::MAX as u64); 418 test_conversion!(db_etc, i64::MIN, i64, expect i64::MIN); 419 test_conversion!(db_etc, i64::MAX, i64, expect i64::MAX); 420 test_conversion!(db_etc, i64::MAX, u64, expect i64::MAX as u64); 421 test_conversion!(db_etc, 100usize, usize, expect 100usize); 422 test_conversion!(db_etc, 100u64, u64, expect 100u64); 423 test_conversion!(db_etc, i64::MAX as u64, u64, expect i64::MAX as u64); 424 425 // Out-of-range integral conversions. 426 test_conversion!(db_etc, 200u8, i8, expect_from_sql_error); 427 test_conversion!(db_etc, 400u16, i8, expect_from_sql_error); 428 test_conversion!(db_etc, 400u16, u8, expect_from_sql_error); 429 test_conversion!(db_etc, -1i8, u8, expect_from_sql_error); 430 test_conversion!(db_etc, i64::MIN, u64, expect_from_sql_error); 431 test_conversion!(db_etc, u64::MAX, i64, expect_to_sql_error); 432 test_conversion!(db_etc, u64::MAX, u64, expect_to_sql_error); 433 test_conversion!(db_etc, i64::MAX as u64 + 1, u64, expect_to_sql_error); 434 435 // FromSql integer to float, always works. 436 test_conversion!(db_etc, i64::MIN, f32, expect i64::MIN as f32); 437 test_conversion!(db_etc, i64::MAX, f32, expect i64::MAX as f32); 438 test_conversion!(db_etc, i64::MIN, f64, expect i64::MIN as f64); 439 test_conversion!(db_etc, i64::MAX, f64, expect i64::MAX as f64); 440 441 // FromSql float to int conversion, never works even if the actual value 442 // is an integer. 443 test_conversion!(db_etc, 0f64, i64, expect_from_sql_error); 444 Ok(()) 445 } 446 } 447