architect_sdk/db/
sqlite.rs

1//! SQLite dialect implementation.
2//!
3//! SQLite is dynamically typed — affinity rules apply. RETURNING supported from 3.35 (2021).
4//! RLS and named enum types are not supported.
5
6use super::dialect::Dialect;
7use super::types::{CanonicalType, TypeCategory, TypeSupport};
8
9pub struct SqliteDialect;
10
11/// Target type for a SQLite `CAST(... AS <type>)` over a JSON-extracted value. The CAST gives
12/// the expression numeric affinity, which forces the (text-bound) parameter to numeric so
13/// comparisons and ORDER BY are numeric rather than lexical. Text-like types need no cast.
14fn sqlite_cast(t: &CanonicalType) -> Option<&'static str> {
15    match t {
16        CanonicalType::SmallInt
17        | CanonicalType::Int
18        | CanonicalType::BigInt
19        | CanonicalType::Serial
20        | CanonicalType::BigSerial
21        | CanonicalType::Boolean => Some("INTEGER"),
22        CanonicalType::Real | CanonicalType::Double | CanonicalType::Decimal(_) => Some("REAL"),
23        _ => None,
24    }
25}
26
27impl Dialect for SqliteDialect {
28    fn name(&self) -> &'static str {
29        "sqlite"
30    }
31
32    fn ddl_type(&self, t: &CanonicalType) -> String {
33        match t {
34            CanonicalType::Text
35            | CanonicalType::Varchar(_)
36            | CanonicalType::Char(_)
37            | CanonicalType::Uuid     // stored as TEXT
38            | CanonicalType::Asset => "TEXT".to_string(),
39
40            CanonicalType::SmallInt | CanonicalType::Int | CanonicalType::BigInt => {
41                "INTEGER".to_string()
42            }
43            // INTEGER PRIMARY KEY is auto-incrementing in SQLite.
44            CanonicalType::Serial | CanonicalType::BigSerial => "INTEGER".to_string(),
45
46            CanonicalType::Real | CanonicalType::Double => "REAL".to_string(),
47            CanonicalType::Decimal(_) => "NUMERIC".to_string(),
48            CanonicalType::Boolean => "INTEGER".to_string(), // 0 / 1
49            CanonicalType::Json | CanonicalType::Jsonb => "TEXT".to_string(),
50            CanonicalType::Timestamp | CanonicalType::TimestampNtz => "TEXT".to_string(),
51            CanonicalType::Date => "TEXT".to_string(),
52            CanonicalType::Time | CanonicalType::Timetz => "TEXT".to_string(),
53            CanonicalType::Bytes => "BLOB".to_string(),
54            CanonicalType::AssetArray | CanonicalType::Array(_) => "TEXT".to_string(),
55            CanonicalType::Custom(s) => s.clone(),
56        }
57    }
58
59    fn cast_name(&self, _t: &CanonicalType) -> Option<String> {
60        None
61    }
62
63    fn type_category(&self, t: &CanonicalType) -> TypeCategory {
64        super::types::type_category(t)
65    }
66
67    fn type_support(&self, t: &CanonicalType) -> TypeSupport {
68        match t {
69            CanonicalType::Jsonb => {
70                TypeSupport::Degraded("TEXT", "JSONB not available on SQLite; using TEXT")
71            }
72            CanonicalType::Timetz => TypeSupport::Degraded(
73                "TEXT",
74                "SQLite has no TIME WITH TIME ZONE; storing as ISO-8601 TEXT",
75            ),
76            CanonicalType::Array(_) => TypeSupport::Degraded(
77                "TEXT",
78                "SQLite has no native array type; stored as JSON TEXT",
79            ),
80            CanonicalType::Asset => TypeSupport::Emulated("TEXT"),
81            CanonicalType::AssetArray => TypeSupport::Emulated("TEXT"),
82            _ => TypeSupport::Native(self.ddl_type(t).leak()),
83        }
84    }
85
86    fn quote_ident(&self, s: &str) -> String {
87        format!("\"{}\"", s.replace('"', "\"\""))
88    }
89
90    fn placeholder(&self, _n: usize) -> String {
91        "?".to_string()
92    }
93
94    fn cast_expr(&self, placeholder: &str, _cast: &str) -> String {
95        placeholder.to_string()
96    }
97
98    fn now_fn(&self) -> &'static str {
99        // CURRENT_TIMESTAMP works as both a DDL column DEFAULT and inside DML expressions.
100        // datetime('now') is a function call and is rejected by SQLite as a DEFAULT value.
101        "CURRENT_TIMESTAMP"
102    }
103
104    fn uuid_default_expr(&self) -> &'static str {
105        // Portable UUID v4 via SQLite's randomblob().
106        "lower(hex(randomblob(4)))||'-'||lower(hex(randomblob(2)))||'-4'||\
107         substr(lower(hex(randomblob(2))),2)||'-'||\
108         substr('89ab',abs(random())%4+1,1)||\
109         substr(lower(hex(randomblob(2))),2)||'-'||lower(hex(randomblob(6)))"
110    }
111
112    fn returning_clause(&self, cols: &str) -> String {
113        format!("RETURNING {}", cols)
114    }
115
116    fn upsert_conflict(&self, conflict_cols: &[&str], set_pairs: &str) -> String {
117        let cols = conflict_cols
118            .iter()
119            .map(|c| self.quote_ident(c))
120            .collect::<Vec<_>>()
121            .join(", ");
122        format!("ON CONFLICT ({}) DO UPDATE SET {}", cols, set_pairs)
123    }
124
125    fn to_one_subquery(&self, col_exprs: &[String], from_clause: &str) -> String {
126        let pairs = col_exprs
127            .iter()
128            .map(|c| format!("'{}', {}", c.trim_matches('"'), c))
129            .collect::<Vec<_>>()
130            .join(", ");
131        format!(
132            "(SELECT json_object({}) FROM {} LIMIT 1)",
133            pairs, from_clause
134        )
135    }
136
137    fn to_many_subquery(&self, col_exprs: &[String], from_clause: &str) -> String {
138        let pairs = col_exprs
139            .iter()
140            .map(|c| format!("'{}', {}", c.trim_matches('"'), c))
141            .collect::<Vec<_>>()
142            .join(", ");
143        format!(
144            "(SELECT COALESCE(json_group_array(json_object({})), '[]') FROM {})",
145            pairs, from_clause
146        )
147    }
148
149    fn json_extract_text(&self, col: &str, key: &str) -> String {
150        format!("{}->>'$.{}'", col, key.replace('\'', "''"))
151    }
152
153    fn json_extract_typed(&self, col: &str, key: &str, t: &CanonicalType) -> String {
154        let base = self.json_extract_text(col, key);
155        match sqlite_cast(t) {
156            Some(cast) => format!("CAST({} AS {})", base, cast),
157            None => base,
158        }
159    }
160
161    fn case_insensitive_like(&self, col: &str, placeholder: &str) -> String {
162        // SQLite LIKE is case-insensitive for ASCII by default.
163        format!("{} LIKE {}", col, placeholder)
164    }
165
166    fn sys_json_type(&self) -> &'static str {
167        "TEXT"
168    }
169
170    fn sys_timestamp_type(&self) -> &'static str {
171        "TEXT"
172    }
173
174    fn sys_bigserial_type(&self) -> &'static str {
175        "INTEGER"
176    }
177
178    fn sys_bytes_type(&self) -> &'static str {
179        "BLOB"
180    }
181
182    fn audit_timestamp_type(&self) -> &'static str {
183        "TEXT"
184    }
185
186    fn supports_schemas(&self) -> bool {
187        false
188    }
189
190    fn default_now_plus_hours(&self, _hours: u32) -> Option<String> {
191        // SQLite has no constant-expression interval arithmetic; caller makes the column nullable.
192        None
193    }
194
195    fn supports_rls(&self) -> bool {
196        false
197    }
198
199    fn supports_named_enum_types(&self) -> bool {
200        false
201    }
202
203    fn supports_index_include(&self) -> bool {
204        false
205    }
206
207    fn set_tenant_session_sql(&self, _tenant_id: &str) -> Option<String> {
208        None
209    }
210}
architect_sdk/db/sqlite.rs

architect_sdk/db/
sqlite.rs
