citadel_sql/
system_tables.rs

1//! Built-in virtual tables: rows materialized from Rust iterators instead of
2//! the B+ tree. Used for PG system catalog views (`pg_timezone_*`,
3//! `information_schema.*`).
4
5use std::sync::Arc;
6
7use citadel::Database;
8use rustc_hash::FxHashSet;
9
10use crate::error::Result;
11use crate::schema::SchemaManager;
12use crate::types::{DataType, QueryResult, Value};
13
14pub trait VirtualTable: Send + Sync {
15    fn name(&self) -> &str;
16    fn scan(&self, db: &Database, schema: &SchemaManager) -> Result<QueryResult>;
17}
18
19pub fn register_builtins(schema: &mut SchemaManager) {
20    let entries: [Arc<dyn VirtualTable>; 6] = [
21        Arc::new(PgTimezoneNames),
22        Arc::new(PgTimezoneAbbrevs),
23        Arc::new(InfoSchemaTables),
24        Arc::new(InfoSchemaColumns),
25        Arc::new(InfoSchemaKeyColumnUsage),
26        Arc::new(InfoSchemaTableConstraints),
27    ];
28    for vt in entries {
29        schema.register_virtual(vt);
30    }
31}
32
33pub struct PgTimezoneNames;
34impl VirtualTable for PgTimezoneNames {
35    fn name(&self) -> &str {
36        "pg_timezone_names"
37    }
38    fn scan(&self, _db: &Database, _schema: &SchemaManager) -> Result<QueryResult> {
39        let columns = vec![
40            "name".to_string(),
41            "utc_offset".to_string(),
42            "is_dst".to_string(),
43        ];
44        let now = jiff::Timestamp::now();
45        let db = jiff::tz::db();
46        let mut rows = Vec::new();
47        for name in db.available() {
48            if let Ok(tz) = db.get(name.as_str()) {
49                let info = tz.to_offset_info(now);
50                let utc_offset = Value::Interval {
51                    months: 0,
52                    days: 0,
53                    micros: i64::from(info.offset().seconds()) * 1_000_000,
54                };
55                rows.push(vec![
56                    Value::Text(name.to_string().into()),
57                    utc_offset,
58                    Value::Boolean(info.dst().is_dst()),
59                ]);
60            }
61        }
62        Ok(QueryResult { columns, rows })
63    }
64}
65
66pub struct PgTimezoneAbbrevs;
67impl VirtualTable for PgTimezoneAbbrevs {
68    fn name(&self) -> &str {
69        "pg_timezone_abbrevs"
70    }
71    fn scan(&self, _db: &Database, _schema: &SchemaManager) -> Result<QueryResult> {
72        let columns = vec![
73            "abbrev".to_string(),
74            "utc_offset".to_string(),
75            "is_dst".to_string(),
76        ];
77        let now = jiff::Timestamp::now();
78        let db = jiff::tz::db();
79        let mut seen: FxHashSet<String> = FxHashSet::default();
80        let mut rows = Vec::new();
81        for name in db.available() {
82            if let Ok(tz) = db.get(name.as_str()) {
83                let info = tz.to_offset_info(now);
84                let abbrev = info.abbreviation().to_string();
85                if !seen.insert(abbrev.clone()) {
86                    continue;
87                }
88                let utc_offset = Value::Interval {
89                    months: 0,
90                    days: 0,
91                    micros: i64::from(info.offset().seconds()) * 1_000_000,
92                };
93                rows.push(vec![
94                    Value::Text(abbrev.into()),
95                    utc_offset,
96                    Value::Boolean(info.dst().is_dst()),
97                ]);
98            }
99        }
100        Ok(QueryResult { columns, rows })
101    }
102}
103
104pub struct InfoSchemaTables;
105impl VirtualTable for InfoSchemaTables {
106    fn name(&self) -> &str {
107        "information_schema.tables"
108    }
109    fn scan(&self, _db: &Database, schema: &SchemaManager) -> Result<QueryResult> {
110        let columns = vec![
111            "table_catalog".to_string(),
112            "table_schema".to_string(),
113            "table_name".to_string(),
114            "table_type".to_string(),
115        ];
116        let mut rows = Vec::new();
117        for ts in schema.all_schemas() {
118            rows.push(vec![
119                Value::Text("citadel".into()),
120                Value::Text("public".into()),
121                Value::Text(ts.name.clone().into()),
122                Value::Text("BASE TABLE".into()),
123            ]);
124        }
125        for vn in schema.view_names() {
126            rows.push(vec![
127                Value::Text("citadel".into()),
128                Value::Text("public".into()),
129                Value::Text(vn.to_string().into()),
130                Value::Text("VIEW".into()),
131            ]);
132        }
133        rows.sort_by(|a, b| match (&a[2], &b[2]) {
134            (Value::Text(x), Value::Text(y)) => x.cmp(y),
135            _ => std::cmp::Ordering::Equal,
136        });
137        Ok(QueryResult { columns, rows })
138    }
139}
140
141pub struct InfoSchemaColumns;
142impl VirtualTable for InfoSchemaColumns {
143    fn name(&self) -> &str {
144        "information_schema.columns"
145    }
146    fn scan(&self, _db: &Database, schema: &SchemaManager) -> Result<QueryResult> {
147        let columns = vec![
148            "table_catalog".to_string(),
149            "table_schema".to_string(),
150            "table_name".to_string(),
151            "column_name".to_string(),
152            "ordinal_position".to_string(),
153            "column_default".to_string(),
154            "is_nullable".to_string(),
155            "data_type".to_string(),
156        ];
157        let mut rows = Vec::new();
158        let mut schemas: Vec<_> = schema.all_schemas().collect();
159        schemas.sort_by(|a, b| a.name.cmp(&b.name));
160        for ts in schemas {
161            for col in &ts.columns {
162                rows.push(vec![
163                    Value::Text("citadel".into()),
164                    Value::Text("public".into()),
165                    Value::Text(ts.name.clone().into()),
166                    Value::Text(col.name.clone().into()),
167                    Value::Integer(i64::from(col.position) + 1),
168                    col.default_sql
169                        .as_deref()
170                        .map(|s| Value::Text(s.to_string().into()))
171                        .unwrap_or(Value::Null),
172                    Value::Text(if col.nullable {
173                        "YES".into()
174                    } else {
175                        "NO".into()
176                    }),
177                    Value::Text(data_type_name(&col.data_type).into()),
178                ]);
179            }
180        }
181        Ok(QueryResult { columns, rows })
182    }
183}
184
185pub struct InfoSchemaKeyColumnUsage;
186impl VirtualTable for InfoSchemaKeyColumnUsage {
187    fn name(&self) -> &str {
188        "information_schema.key_column_usage"
189    }
190    fn scan(&self, _db: &Database, schema: &SchemaManager) -> Result<QueryResult> {
191        let columns = vec![
192            "constraint_catalog".to_string(),
193            "constraint_schema".to_string(),
194            "constraint_name".to_string(),
195            "table_catalog".to_string(),
196            "table_schema".to_string(),
197            "table_name".to_string(),
198            "column_name".to_string(),
199            "ordinal_position".to_string(),
200            "referenced_table_name".to_string(),
201            "referenced_column_name".to_string(),
202        ];
203        let mut rows = Vec::new();
204        let mut schemas: Vec<_> = schema.all_schemas().collect();
205        schemas.sort_by(|a, b| a.name.cmp(&b.name));
206        for ts in schemas {
207            for (i, &col_pos) in ts.primary_key_columns.iter().enumerate() {
208                let col = &ts.columns[col_pos as usize];
209                rows.push(vec![
210                    Value::Text("citadel".into()),
211                    Value::Text("public".into()),
212                    Value::Text(format!("{}_pkey", ts.name).into()),
213                    Value::Text("citadel".into()),
214                    Value::Text("public".into()),
215                    Value::Text(ts.name.clone().into()),
216                    Value::Text(col.name.clone().into()),
217                    Value::Integer((i + 1) as i64),
218                    Value::Null,
219                    Value::Null,
220                ]);
221            }
222            for fk in &ts.foreign_keys {
223                let cname = fk
224                    .name
225                    .clone()
226                    .unwrap_or_else(|| format!("{}_fkey", ts.name));
227                for (i, col_pos) in fk.columns.iter().enumerate() {
228                    let col = &ts.columns[*col_pos as usize];
229                    let ref_col = fk.referred_columns.get(i).cloned().unwrap_or_default();
230                    rows.push(vec![
231                        Value::Text("citadel".into()),
232                        Value::Text("public".into()),
233                        Value::Text(cname.clone().into()),
234                        Value::Text("citadel".into()),
235                        Value::Text("public".into()),
236                        Value::Text(ts.name.clone().into()),
237                        Value::Text(col.name.clone().into()),
238                        Value::Integer((i + 1) as i64),
239                        Value::Text(fk.foreign_table.clone().into()),
240                        Value::Text(ref_col.into()),
241                    ]);
242                }
243            }
244        }
245        Ok(QueryResult { columns, rows })
246    }
247}
248
249pub struct InfoSchemaTableConstraints;
250impl VirtualTable for InfoSchemaTableConstraints {
251    fn name(&self) -> &str {
252        "information_schema.table_constraints"
253    }
254    fn scan(&self, _db: &Database, schema: &SchemaManager) -> Result<QueryResult> {
255        let columns = vec![
256            "constraint_catalog".to_string(),
257            "constraint_schema".to_string(),
258            "constraint_name".to_string(),
259            "table_catalog".to_string(),
260            "table_schema".to_string(),
261            "table_name".to_string(),
262            "constraint_type".to_string(),
263        ];
264        let mut rows = Vec::new();
265        let mut schemas: Vec<_> = schema.all_schemas().collect();
266        schemas.sort_by(|a, b| a.name.cmp(&b.name));
267        for ts in schemas {
268            if !ts.primary_key_columns.is_empty() {
269                rows.push(constraint_row(
270                    &format!("{}_pkey", ts.name),
271                    &ts.name,
272                    "PRIMARY KEY",
273                ));
274            }
275            for fk in &ts.foreign_keys {
276                let cname = fk
277                    .name
278                    .clone()
279                    .unwrap_or_else(|| format!("{}_fkey", ts.name));
280                rows.push(constraint_row(&cname, &ts.name, "FOREIGN KEY"));
281            }
282            for chk in &ts.check_constraints {
283                let cname = chk
284                    .name
285                    .clone()
286                    .unwrap_or_else(|| format!("{}_check", ts.name));
287                rows.push(constraint_row(&cname, &ts.name, "CHECK"));
288            }
289            for col in &ts.columns {
290                if col.check_expr.is_some() {
291                    let cname = col
292                        .check_name
293                        .clone()
294                        .unwrap_or_else(|| format!("{}_{}_check", ts.name, col.name));
295                    rows.push(constraint_row(&cname, &ts.name, "CHECK"));
296                }
297            }
298            for idx in &ts.indices {
299                if idx.unique {
300                    rows.push(constraint_row(&idx.name, &ts.name, "UNIQUE"));
301                }
302            }
303        }
304        Ok(QueryResult { columns, rows })
305    }
306}
307
308fn constraint_row(name: &str, table: &str, kind: &str) -> Vec<Value> {
309    vec![
310        Value::Text("citadel".into()),
311        Value::Text("public".into()),
312        Value::Text(name.to_string().into()),
313        Value::Text("citadel".into()),
314        Value::Text("public".into()),
315        Value::Text(table.to_string().into()),
316        Value::Text(kind.to_string().into()),
317    ]
318}
319
320fn data_type_name(dt: &DataType) -> &'static str {
321    match dt {
322        DataType::Integer => "INTEGER",
323        DataType::Real => "REAL",
324        DataType::Text => "TEXT",
325        DataType::Blob => "BLOB",
326        DataType::Boolean => "BOOLEAN",
327        DataType::Date => "DATE",
328        DataType::Time => "TIME",
329        DataType::Timestamp => "TIMESTAMP",
330        DataType::Interval => "INTERVAL",
331        DataType::Json => "JSON",
332        DataType::Jsonb => "JSONB",
333        DataType::Null => "NULL",
334        DataType::TsVector => "TSVECTOR",
335        DataType::TsQuery => "TSQUERY",
336    }
337}
citadel_sql/system_tables.rs

citadel_sql/
system_tables.rs
