quantydb_sqlite/
affinity.rs

1//! Type affinity: what a column's declared type means.
2//!
3//! A declared type in SQLite does not constrain what a column holds. It
4//! sets an affinity, which is a preference applied when a value goes in:
5//! text that looks like a number goes into an integer affinity column as a
6//! number, text that does not stays text. So the declaration is neither a
7//! guarantee nor decoration, and both readings get an import wrong.
8//!
9//! The rules are five, applied in order, against the declared type
10//! uppercased. The order is the whole trick, and it produces results that
11//! look like bugs and are not:
12//!
13//! 1. contains `INT`               -> INTEGER
14//! 2. contains `CHAR`, `CLOB`, `TEXT` -> TEXT
15//! 3. contains `BLOB`, or no type at all -> BLOB
16//! 4. contains `REAL`, `FLOA`, `DOUB` -> REAL
17//! 5. otherwise                    -> NUMERIC
18//!
19//! `FLOATING POINT` has integer affinity, because `POINT` contains `INT`
20//! and rule 1 wins before rule 4 is ever reached. `CHARINT` is an integer
21//! for the same reason. `STRING` is numeric, because it contains none of
22//! the words. These are not our jokes to fix; every reader has to agree
23//! with them or it reads different data than SQLite does.
24//!
25//! One consequence matters more than all the others put together. In a
26//! column with real affinity, SQLite stores a whole numbered value as an
27//! integer to save space, and turns it back into a float on the way out
28//! using the affinity. So `1.0` and `2.5` in one real column are physically
29//! an integer and a float. Judging such a column by its storage classes
30//! alone makes almost every real column in the world look mixed, which is
31//! why the declared type is not optional information here.
32
33use crate::record::SqliteValue;
34
35/// SQLite's five storage classes: what a value physically is.
36#[derive(Debug, Clone, Copy, PartialEq, Eq, PartialOrd, Ord)]
37pub enum StorageClass {
38    Null,
39    Integer,
40    Real,
41    Text,
42    Blob,
43}
44
45impl StorageClass {
46    pub fn of(value: &SqliteValue) -> StorageClass {
47        match value {
48            SqliteValue::Null => StorageClass::Null,
49            SqliteValue::Integer(_) => StorageClass::Integer,
50            SqliteValue::Real(_) => StorageClass::Real,
51            SqliteValue::Text(_) => StorageClass::Text,
52            SqliteValue::Blob(_) => StorageClass::Blob,
53        }
54    }
55
56    pub fn as_str(self) -> &'static str {
57        match self {
58            StorageClass::Null => "null",
59            StorageClass::Integer => "integer",
60            StorageClass::Real => "real",
61            StorageClass::Text => "text",
62            StorageClass::Blob => "blob",
63        }
64    }
65}
66
67#[derive(Debug, Clone, Copy, PartialEq, Eq)]
68pub enum Affinity {
69    Integer,
70    Text,
71    Blob,
72    Real,
73    Numeric,
74}
75
76impl Affinity {
77    /// Apply the five rules to a declared type. A column declared without a
78    /// type has blob affinity, which is the one that converts nothing.
79    pub fn of(declared_type: Option<&str>) -> Affinity {
80        let Some(declared) = declared_type else {
81            return Affinity::Blob;
82        };
83        let upper = declared.to_ascii_uppercase();
84        if upper.contains("INT") {
85            Affinity::Integer
86        } else if upper.contains("CHAR") || upper.contains("CLOB") || upper.contains("TEXT") {
87            Affinity::Text
88        } else if upper.contains("BLOB") || upper.trim().is_empty() {
89            Affinity::Blob
90        } else if upper.contains("REAL") || upper.contains("FLOA") || upper.contains("DOUB") {
91            Affinity::Real
92        } else {
93            Affinity::Numeric
94        }
95    }
96
97    pub fn as_str(self) -> &'static str {
98        match self {
99            Affinity::Integer => "integer",
100            Affinity::Text => "text",
101            Affinity::Blob => "blob",
102            Affinity::Real => "real",
103            Affinity::Numeric => "numeric",
104        }
105    }
106
107    /// What a stored value means once this column's affinity is applied.
108    ///
109    /// The only case where this differs from the physical class is an
110    /// integer in a real affinity column, which is a float that SQLite
111    /// wrote out compactly, and which SQLite itself reports as a real.
112    pub fn logical_class(self, value: &SqliteValue) -> StorageClass {
113        let stored = StorageClass::of(value);
114        match (self, stored) {
115            (Affinity::Real, StorageClass::Integer) => StorageClass::Real,
116            _ => stored,
117        }
118    }
119}
120
121#[cfg(test)]
122mod tests {
123    use super::*;
124
125    #[test]
126    fn the_five_rules_run_in_order() {
127        let cases = [
128            ("INTEGER", Affinity::Integer),
129            ("INT", Affinity::Integer),
130            ("BIGINT", Affinity::Integer),
131            ("UNSIGNED BIG INT", Affinity::Integer),
132            ("INT2", Affinity::Integer),
133            ("VARCHAR(255)", Affinity::Text),
134            ("NVARCHAR(160)", Affinity::Text),
135            ("TEXT", Affinity::Text),
136            ("CLOB", Affinity::Text),
137            ("NCHAR(55)", Affinity::Text),
138            ("BLOB", Affinity::Blob),
139            ("REAL", Affinity::Real),
140            ("DOUBLE PRECISION", Affinity::Real),
141            ("FLOAT", Affinity::Real),
142            ("NUMERIC", Affinity::Numeric),
143            ("DECIMAL(10,5)", Affinity::Numeric),
144            ("BOOLEAN", Affinity::Numeric),
145            ("DATE", Affinity::Numeric),
146            ("DATETIME", Affinity::Numeric),
147            ("STRING", Affinity::Numeric),
148        ];
149        for (declared, expected) in cases {
150            assert_eq!(Affinity::of(Some(declared)), expected, "{declared}");
151            // the rules are case insensitive
152            assert_eq!(
153                Affinity::of(Some(&declared.to_lowercase())),
154                expected,
155                "{declared} in lower case"
156            );
157        }
158    }
159
160    #[test]
161    fn the_rules_that_look_like_bugs() {
162        // POINT contains INT, so rule 1 fires before rule 4 is reached
163        assert_eq!(Affinity::of(Some("FLOATING POINT")), Affinity::Integer);
164        assert_eq!(Affinity::of(Some("POINT")), Affinity::Integer);
165        // CHARINT contains both CHAR and INT, and INT is checked first
166        assert_eq!(Affinity::of(Some("CHARINT")), Affinity::Integer);
167        assert_eq!(Affinity::of(Some("BLOBINT")), Affinity::Integer);
168        // no type at all is blob affinity, which converts nothing
169        assert_eq!(Affinity::of(None), Affinity::Blob);
170        assert_eq!(Affinity::of(Some("")), Affinity::Blob);
171    }
172
173    #[test]
174    fn an_integer_in_a_real_column_is_a_float() {
175        let integer = SqliteValue::Integer(1);
176        assert_eq!(
177            Affinity::Real.logical_class(&integer),
178            StorageClass::Real,
179            "sqlite writes whole floats as integers in real columns"
180        );
181        // every other affinity takes an integer at face value
182        for affinity in [
183            Affinity::Integer,
184            Affinity::Numeric,
185            Affinity::Text,
186            Affinity::Blob,
187        ] {
188            assert_eq!(affinity.logical_class(&integer), StorageClass::Integer);
189        }
190    }
191
192    #[test]
193    fn affinity_never_changes_the_other_classes() {
194        let values = [
195            SqliteValue::Null,
196            SqliteValue::Real(2.5),
197            SqliteValue::Text("x".into()),
198            SqliteValue::Blob(vec![1]),
199        ];
200        for affinity in [
201            Affinity::Integer,
202            Affinity::Text,
203            Affinity::Blob,
204            Affinity::Real,
205            Affinity::Numeric,
206        ] {
207            for value in &values {
208                assert_eq!(
209                    affinity.logical_class(value),
210                    StorageClass::of(value),
211                    "{affinity:?} changed {value:?}"
212                );
213            }
214        }
215    }
216}
quantydb_sqlite/affinity.rs

quantydb_sqlite/
affinity.rs
