1 //! [`ToSql`] and [`FromSql`] implementation for JSON `Value`. 2 3 use serde_json::{Number, Value}; 4 5 use crate::types::{FromSql, FromSqlError, FromSqlResult, ToSql, ToSqlOutput, ValueRef}; 6 use crate::{Error, Result}; 7 8 /// Serialize JSON `Value` to text: 9 /// 10 /// 11 /// | JSON | SQLite | 12 /// |----------|---------| 13 /// | Null | NULL | 14 /// | Bool | 'true' / 'false' | 15 /// | Number | INT or REAL except u64 | 16 /// | _ | TEXT | 17 impl ToSql for Value { 18 #[inline] to_sql(&self) -> Result<ToSqlOutput<'_>>19 fn to_sql(&self) -> Result<ToSqlOutput<'_>> { 20 match self { 21 Value::Null => Ok(ToSqlOutput::Borrowed(ValueRef::Null)), 22 Value::Number(n) if n.is_i64() => Ok(ToSqlOutput::from(n.as_i64().unwrap())), 23 Value::Number(n) if n.is_f64() => Ok(ToSqlOutput::from(n.as_f64().unwrap())), 24 _ => serde_json::to_string(self) 25 .map(ToSqlOutput::from) 26 .map_err(|err| Error::ToSqlConversionFailure(err.into())), 27 } 28 } 29 } 30 31 /// Deserialize SQLite value to JSON `Value`: 32 /// 33 /// | SQLite | JSON | 34 /// |----------|---------| 35 /// | NULL | Null | 36 /// | 'null' | Null | 37 /// | 'true' | Bool | 38 /// | 1 | Number | 39 /// | 0.1 | Number | 40 /// | '"text"' | String | 41 /// | 'text' | _Error_ | 42 /// | '[0, 1]' | Array | 43 /// | '{"x": 1}' | Object | 44 impl FromSql for Value { 45 #[inline] column_result(value: ValueRef<'_>) -> FromSqlResult<Self>46 fn column_result(value: ValueRef<'_>) -> FromSqlResult<Self> { 47 match value { 48 ValueRef::Text(s) => serde_json::from_slice(s), // KO for b"text" 49 ValueRef::Blob(b) => serde_json::from_slice(b), 50 ValueRef::Integer(i) => Ok(Value::Number(Number::from(i))), 51 ValueRef::Real(f) => { 52 match Number::from_f64(f) { 53 Some(n) => Ok(Value::Number(n)), 54 _ => return Err(FromSqlError::InvalidType), // FIXME 55 } 56 } 57 ValueRef::Null => Ok(Value::Null), 58 } 59 .map_err(|err| FromSqlError::Other(Box::new(err))) 60 } 61 } 62 63 #[cfg(test)] 64 mod test { 65 use crate::types::ToSql; 66 use crate::{Connection, Result}; 67 use serde_json::{Number, Value}; 68 checked_memory_handle() -> Result<Connection>69 fn checked_memory_handle() -> Result<Connection> { 70 let db = Connection::open_in_memory()?; 71 db.execute_batch("CREATE TABLE foo (t TEXT, b BLOB)")?; 72 Ok(db) 73 } 74 75 #[test] test_json_value() -> Result<()>76 fn test_json_value() -> Result<()> { 77 let db = checked_memory_handle()?; 78 79 let json = r#"{"foo": 13, "bar": "baz"}"#; 80 let data: Value = serde_json::from_str(json).unwrap(); 81 db.execute( 82 "INSERT INTO foo (t, b) VALUES (?1, ?2)", 83 [&data as &dyn ToSql, &json.as_bytes()], 84 )?; 85 86 let t: Value = db.one_column("SELECT t FROM foo")?; 87 assert_eq!(data, t); 88 let b: Value = db.one_column("SELECT b FROM foo")?; 89 assert_eq!(data, b); 90 Ok(()) 91 } 92 93 #[test] test_to_sql() -> Result<()>94 fn test_to_sql() -> Result<()> { 95 let db = Connection::open_in_memory()?; 96 97 let v: Option<String> = db.query_row("SELECT ?", [Value::Null], |r| r.get(0))?; 98 assert_eq!(None, v); 99 let v: String = db.query_row("SELECT ?", [Value::Bool(true)], |r| r.get(0))?; 100 assert_eq!("true", v); 101 let v: i64 = db.query_row("SELECT ?", [Value::Number(Number::from(1))], |r| r.get(0))?; 102 assert_eq!(1, v); 103 let v: f64 = db.query_row( 104 "SELECT ?", 105 [Value::Number(Number::from_f64(0.1).unwrap())], 106 |r| r.get(0), 107 )?; 108 assert_eq!(0.1, v); 109 let v: String = 110 db.query_row("SELECT ?", [Value::String("text".to_owned())], |r| r.get(0))?; 111 assert_eq!("\"text\"", v); 112 Ok(()) 113 } 114 115 #[test] test_from_sql() -> Result<()>116 fn test_from_sql() -> Result<()> { 117 let db = Connection::open_in_memory()?; 118 119 let v: Value = db.one_column("SELECT NULL")?; 120 assert_eq!(Value::Null, v); 121 let v: Value = db.one_column("SELECT 'null'")?; 122 assert_eq!(Value::Null, v); 123 let v: Value = db.one_column("SELECT 'true'")?; 124 assert_eq!(Value::Bool(true), v); 125 let v: Value = db.one_column("SELECT 1")?; 126 assert_eq!(Value::Number(Number::from(1)), v); 127 let v: Value = db.one_column("SELECT 0.1")?; 128 assert_eq!(Value::Number(Number::from_f64(0.1).unwrap()), v); 129 let v: Value = db.one_column("SELECT '\"text\"'")?; 130 assert_eq!(Value::String("text".to_owned()), v); 131 let v: Result<Value> = db.one_column("SELECT 'text'"); 132 assert!(v.is_err()); 133 Ok(()) 134 } 135 } 136