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>; 9] = [
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        Arc::new(InfoSchemaTriggers),
28        Arc::new(CitadelTriggersStatus),
29        Arc::new(PgMatviews),
30    ];
31    for vt in entries {
32        schema.register_virtual(vt);
33    }
34}
35
36pub struct PgTimezoneNames;
37impl VirtualTable for PgTimezoneNames {
38    fn name(&self) -> &str {
39        "pg_timezone_names"
40    }
41    fn scan(&self, _db: &Database, _schema: &SchemaManager) -> Result<QueryResult> {
42        let columns = vec![
43            "name".to_string(),
44            "utc_offset".to_string(),
45            "is_dst".to_string(),
46        ];
47        let now = jiff::Timestamp::now();
48        let db = jiff::tz::db();
49        let mut rows = Vec::new();
50        for name in db.available() {
51            if let Ok(tz) = db.get(name.as_str()) {
52                let info = tz.to_offset_info(now);
53                let utc_offset = Value::Interval {
54                    months: 0,
55                    days: 0,
56                    micros: i64::from(info.offset().seconds()) * 1_000_000,
57                };
58                rows.push(vec![
59                    Value::Text(name.to_string().into()),
60                    utc_offset,
61                    Value::Boolean(info.dst().is_dst()),
62                ]);
63            }
64        }
65        Ok(QueryResult { columns, rows })
66    }
67}
68
69pub struct PgTimezoneAbbrevs;
70impl VirtualTable for PgTimezoneAbbrevs {
71    fn name(&self) -> &str {
72        "pg_timezone_abbrevs"
73    }
74    fn scan(&self, _db: &Database, _schema: &SchemaManager) -> Result<QueryResult> {
75        let columns = vec![
76            "abbrev".to_string(),
77            "utc_offset".to_string(),
78            "is_dst".to_string(),
79        ];
80        let now = jiff::Timestamp::now();
81        let db = jiff::tz::db();
82        let mut seen: FxHashSet<String> = FxHashSet::default();
83        let mut rows = Vec::new();
84        for name in db.available() {
85            if let Ok(tz) = db.get(name.as_str()) {
86                let info = tz.to_offset_info(now);
87                let abbrev = info.abbreviation().to_string();
88                if !seen.insert(abbrev.clone()) {
89                    continue;
90                }
91                let utc_offset = Value::Interval {
92                    months: 0,
93                    days: 0,
94                    micros: i64::from(info.offset().seconds()) * 1_000_000,
95                };
96                rows.push(vec![
97                    Value::Text(abbrev.into()),
98                    utc_offset,
99                    Value::Boolean(info.dst().is_dst()),
100                ]);
101            }
102        }
103        Ok(QueryResult { columns, rows })
104    }
105}
106
107pub struct InfoSchemaTables;
108impl VirtualTable for InfoSchemaTables {
109    fn name(&self) -> &str {
110        "information_schema.tables"
111    }
112    fn scan(&self, _db: &Database, schema: &SchemaManager) -> Result<QueryResult> {
113        let columns = vec![
114            "table_catalog".to_string(),
115            "table_schema".to_string(),
116            "table_name".to_string(),
117            "table_type".to_string(),
118        ];
119        let mut rows = Vec::new();
120        for ts in schema.all_schemas() {
121            // Listed separately below as MATERIALIZED VIEW.
122            if schema.get_matview(&ts.name).is_some() {
123                continue;
124            }
125            rows.push(vec![
126                Value::Text("citadel".into()),
127                Value::Text("public".into()),
128                Value::Text(ts.name.clone().into()),
129                Value::Text("BASE TABLE".into()),
130            ]);
131        }
132        for vn in schema.view_names() {
133            rows.push(vec![
134                Value::Text("citadel".into()),
135                Value::Text("public".into()),
136                Value::Text(vn.to_string().into()),
137                Value::Text("VIEW".into()),
138            ]);
139        }
140        for mv in schema.all_matviews() {
141            rows.push(vec![
142                Value::Text("citadel".into()),
143                Value::Text("public".into()),
144                Value::Text(mv.name.clone().into()),
145                Value::Text("MATERIALIZED VIEW".into()),
146            ]);
147        }
148        rows.sort_by(|a, b| match (&a[2], &b[2]) {
149            (Value::Text(x), Value::Text(y)) => x.cmp(y),
150            _ => std::cmp::Ordering::Equal,
151        });
152        Ok(QueryResult { columns, rows })
153    }
154}
155
156pub struct InfoSchemaColumns;
157impl VirtualTable for InfoSchemaColumns {
158    fn name(&self) -> &str {
159        "information_schema.columns"
160    }
161    fn scan(&self, _db: &Database, schema: &SchemaManager) -> Result<QueryResult> {
162        let columns = vec![
163            "table_catalog".to_string(),
164            "table_schema".to_string(),
165            "table_name".to_string(),
166            "column_name".to_string(),
167            "ordinal_position".to_string(),
168            "column_default".to_string(),
169            "is_nullable".to_string(),
170            "data_type".to_string(),
171        ];
172        let mut rows = Vec::new();
173        let mut schemas: Vec<_> = schema.all_schemas().collect();
174        schemas.sort_by(|a, b| a.name.cmp(&b.name));
175        for ts in schemas {
176            for col in &ts.columns {
177                rows.push(vec![
178                    Value::Text("citadel".into()),
179                    Value::Text("public".into()),
180                    Value::Text(ts.name.clone().into()),
181                    Value::Text(col.name.clone().into()),
182                    Value::Integer(i64::from(col.position) + 1),
183                    col.default_sql
184                        .as_deref()
185                        .map(|s| Value::Text(s.to_string().into()))
186                        .unwrap_or(Value::Null),
187                    Value::Text(if col.nullable {
188                        "YES".into()
189                    } else {
190                        "NO".into()
191                    }),
192                    Value::Text(data_type_name(&col.data_type).into()),
193                ]);
194            }
195        }
196        Ok(QueryResult { columns, rows })
197    }
198}
199
200pub struct InfoSchemaKeyColumnUsage;
201impl VirtualTable for InfoSchemaKeyColumnUsage {
202    fn name(&self) -> &str {
203        "information_schema.key_column_usage"
204    }
205    fn scan(&self, _db: &Database, schema: &SchemaManager) -> Result<QueryResult> {
206        let columns = vec![
207            "constraint_catalog".to_string(),
208            "constraint_schema".to_string(),
209            "constraint_name".to_string(),
210            "table_catalog".to_string(),
211            "table_schema".to_string(),
212            "table_name".to_string(),
213            "column_name".to_string(),
214            "ordinal_position".to_string(),
215            "referenced_table_name".to_string(),
216            "referenced_column_name".to_string(),
217        ];
218        let mut rows = Vec::new();
219        let mut schemas: Vec<_> = schema.all_schemas().collect();
220        schemas.sort_by(|a, b| a.name.cmp(&b.name));
221        for ts in schemas {
222            for (i, &col_pos) in ts.primary_key_columns.iter().enumerate() {
223                let col = &ts.columns[col_pos as usize];
224                rows.push(vec![
225                    Value::Text("citadel".into()),
226                    Value::Text("public".into()),
227                    Value::Text(format!("{}_pkey", ts.name).into()),
228                    Value::Text("citadel".into()),
229                    Value::Text("public".into()),
230                    Value::Text(ts.name.clone().into()),
231                    Value::Text(col.name.clone().into()),
232                    Value::Integer((i + 1) as i64),
233                    Value::Null,
234                    Value::Null,
235                ]);
236            }
237            for fk in &ts.foreign_keys {
238                let cname = fk
239                    .name
240                    .clone()
241                    .unwrap_or_else(|| format!("{}_fkey", ts.name));
242                for (i, col_pos) in fk.columns.iter().enumerate() {
243                    let col = &ts.columns[*col_pos as usize];
244                    let ref_col = fk.referred_columns.get(i).cloned().unwrap_or_default();
245                    rows.push(vec![
246                        Value::Text("citadel".into()),
247                        Value::Text("public".into()),
248                        Value::Text(cname.clone().into()),
249                        Value::Text("citadel".into()),
250                        Value::Text("public".into()),
251                        Value::Text(ts.name.clone().into()),
252                        Value::Text(col.name.clone().into()),
253                        Value::Integer((i + 1) as i64),
254                        Value::Text(fk.foreign_table.clone().into()),
255                        Value::Text(ref_col.into()),
256                    ]);
257                }
258            }
259        }
260        Ok(QueryResult { columns, rows })
261    }
262}
263
264pub struct InfoSchemaTableConstraints;
265impl VirtualTable for InfoSchemaTableConstraints {
266    fn name(&self) -> &str {
267        "information_schema.table_constraints"
268    }
269    fn scan(&self, _db: &Database, schema: &SchemaManager) -> Result<QueryResult> {
270        let columns = vec![
271            "constraint_catalog".to_string(),
272            "constraint_schema".to_string(),
273            "constraint_name".to_string(),
274            "table_catalog".to_string(),
275            "table_schema".to_string(),
276            "table_name".to_string(),
277            "constraint_type".to_string(),
278        ];
279        let mut rows = Vec::new();
280        let mut schemas: Vec<_> = schema.all_schemas().collect();
281        schemas.sort_by(|a, b| a.name.cmp(&b.name));
282        for ts in schemas {
283            if !ts.primary_key_columns.is_empty() {
284                rows.push(constraint_row(
285                    &format!("{}_pkey", ts.name),
286                    &ts.name,
287                    "PRIMARY KEY",
288                ));
289            }
290            for fk in &ts.foreign_keys {
291                let cname = fk
292                    .name
293                    .clone()
294                    .unwrap_or_else(|| format!("{}_fkey", ts.name));
295                rows.push(constraint_row(&cname, &ts.name, "FOREIGN KEY"));
296            }
297            for chk in &ts.check_constraints {
298                let cname = chk
299                    .name
300                    .clone()
301                    .unwrap_or_else(|| format!("{}_check", ts.name));
302                rows.push(constraint_row(&cname, &ts.name, "CHECK"));
303            }
304            for col in &ts.columns {
305                if col.check_expr.is_some() {
306                    let cname = col
307                        .check_name
308                        .clone()
309                        .unwrap_or_else(|| format!("{}_{}_check", ts.name, col.name));
310                    rows.push(constraint_row(&cname, &ts.name, "CHECK"));
311                }
312            }
313            for idx in &ts.indices {
314                if idx.unique {
315                    rows.push(constraint_row(&idx.name, &ts.name, "UNIQUE"));
316                }
317            }
318        }
319        Ok(QueryResult { columns, rows })
320    }
321}
322
323fn constraint_row(name: &str, table: &str, kind: &str) -> Vec<Value> {
324    vec![
325        Value::Text("citadel".into()),
326        Value::Text("public".into()),
327        Value::Text(name.to_string().into()),
328        Value::Text("citadel".into()),
329        Value::Text("public".into()),
330        Value::Text(table.to_string().into()),
331        Value::Text(kind.to_string().into()),
332    ]
333}
334
335fn data_type_name(dt: &DataType) -> &'static str {
336    match dt {
337        DataType::Integer => "INTEGER",
338        DataType::Real => "REAL",
339        DataType::Text => "TEXT",
340        DataType::Blob => "BLOB",
341        DataType::Boolean => "BOOLEAN",
342        DataType::Date => "DATE",
343        DataType::Time => "TIME",
344        DataType::Timestamp => "TIMESTAMP",
345        DataType::Interval => "INTERVAL",
346        DataType::Json => "JSON",
347        DataType::Jsonb => "JSONB",
348        DataType::Null => "NULL",
349        DataType::TsVector => "TSVECTOR",
350        DataType::TsQuery => "TSQUERY",
351        DataType::Array => "ARRAY",
352    }
353}
354
355/// One row per event for multi-event triggers (per SQL spec).
356pub struct InfoSchemaTriggers;
357impl VirtualTable for InfoSchemaTriggers {
358    fn name(&self) -> &str {
359        "information_schema.triggers"
360    }
361    fn scan(&self, _db: &Database, schema: &SchemaManager) -> Result<QueryResult> {
362        let columns = vec![
363            "trigger_catalog".to_string(),
364            "trigger_schema".to_string(),
365            "trigger_name".to_string(),
366            "event_manipulation".to_string(),
367            "event_object_catalog".to_string(),
368            "event_object_schema".to_string(),
369            "event_object_table".to_string(),
370            "action_order".to_string(),
371            "action_condition".to_string(),
372            "action_statement".to_string(),
373            "action_orientation".to_string(),
374            "action_timing".to_string(),
375            "action_reference_old_table".to_string(),
376            "action_reference_new_table".to_string(),
377            "action_reference_old_row".to_string(),
378            "action_reference_new_row".to_string(),
379            "created".to_string(),
380        ];
381        let mut all: Vec<&crate::types::TriggerDef> = schema.all_triggers().collect();
382        all.sort_by(|a, b| a.target.cmp(&b.target).then(a.name.cmp(&b.name)));
383        let mut order_in_group: rustc_hash::FxHashMap<(String, String, String, String), i64> =
384            rustc_hash::FxHashMap::default();
385        let mut rows = Vec::new();
386        for td in all {
387            for ev in &td.events {
388                let event_name = match ev {
389                    crate::parser::TriggerEvent::Insert => "INSERT".to_string(),
390                    crate::parser::TriggerEvent::Update(_) => "UPDATE".to_string(),
391                    crate::parser::TriggerEvent::Delete => "DELETE".to_string(),
392                };
393                let timing_name = match td.timing {
394                    crate::parser::TriggerTiming::Before => "BEFORE".to_string(),
395                    crate::parser::TriggerTiming::After => "AFTER".to_string(),
396                    crate::parser::TriggerTiming::InsteadOf => "INSTEAD OF".to_string(),
397                };
398                let orientation = match td.granularity {
399                    crate::parser::TriggerGranularity::ForEachRow => "ROW".to_string(),
400                    crate::parser::TriggerGranularity::ForEachStatement => "STATEMENT".to_string(),
401                };
402                let key = (
403                    td.target.clone(),
404                    event_name.clone(),
405                    timing_name.clone(),
406                    orientation.clone(),
407                );
408                let order = order_in_group.entry(key).or_insert(0);
409                *order += 1;
410                let order_val = *order;
411                let action_condition = match &td.when_sql {
412                    Some(s) => Value::Text(s.clone().into()),
413                    None => Value::Null,
414                };
415                let old_table_alias = td
416                    .referencing
417                    .as_ref()
418                    .and_then(|r| r.old_table_alias.clone());
419                let new_table_alias = td
420                    .referencing
421                    .as_ref()
422                    .and_then(|r| r.new_table_alias.clone());
423                rows.push(vec![
424                    Value::Text("citadel".into()),
425                    Value::Text("public".into()),
426                    Value::Text(td.name.clone().into()),
427                    Value::Text(event_name.into()),
428                    Value::Text("citadel".into()),
429                    Value::Text("public".into()),
430                    Value::Text(td.target.clone().into()),
431                    Value::Integer(order_val),
432                    action_condition,
433                    Value::Text(td.body_sql.clone().into()),
434                    Value::Text(orientation.into()),
435                    Value::Text(timing_name.into()),
436                    old_table_alias
437                        .map(|s| Value::Text(s.into()))
438                        .unwrap_or(Value::Null),
439                    new_table_alias
440                        .map(|s| Value::Text(s.into()))
441                        .unwrap_or(Value::Null),
442                    Value::Null,
443                    Value::Null,
444                    Value::Timestamp(td.created_at_micros),
445                ]);
446            }
447        }
448        Ok(QueryResult { columns, rows })
449    }
450}
451
452/// Surfaces `enabled` status — PG hides this from `information_schema.triggers`.
453pub struct CitadelTriggersStatus;
454impl VirtualTable for CitadelTriggersStatus {
455    fn name(&self) -> &str {
456        "citadel_triggers_status"
457    }
458    fn scan(&self, _db: &Database, schema: &SchemaManager) -> Result<QueryResult> {
459        let columns = vec![
460            "trigger_name".to_string(),
461            "table_name".to_string(),
462            "enabled".to_string(),
463        ];
464        let mut all: Vec<&crate::types::TriggerDef> = schema.all_triggers().collect();
465        all.sort_by(|a, b| a.target.cmp(&b.target).then(a.name.cmp(&b.name)));
466        let rows = all
467            .into_iter()
468            .map(|td| {
469                vec![
470                    Value::Text(td.name.clone().into()),
471                    Value::Text(td.target.clone().into()),
472                    Value::Boolean(td.enabled),
473                ]
474            })
475            .collect();
476        Ok(QueryResult { columns, rows })
477    }
478}
479
480/// `matviewowner` and `tablespace` are constants — citadel has no permission/storage concept.
481pub struct PgMatviews;
482impl VirtualTable for PgMatviews {
483    fn name(&self) -> &str {
484        "pg_matviews"
485    }
486    fn scan(&self, _db: &Database, schema: &SchemaManager) -> Result<QueryResult> {
487        let columns = vec![
488            "schemaname".to_string(),
489            "matviewname".to_string(),
490            "matviewowner".to_string(),
491            "tablespace".to_string(),
492            "hasindexes".to_string(),
493            "ispopulated".to_string(),
494            "definition".to_string(),
495        ];
496        let mut entries: Vec<&crate::types::MatviewDef> = schema.all_matviews().collect();
497        entries.sort_by(|a, b| a.name.cmp(&b.name));
498        let rows = entries
499            .into_iter()
500            .map(|mv| {
501                let hasindexes = schema
502                    .get(&mv.backing_table)
503                    .map(|ts| !ts.indices.is_empty())
504                    .unwrap_or(false);
505                vec![
506                    Value::Text("public".into()),
507                    Value::Text(mv.name.clone().into()),
508                    Value::Text("citadel".into()),
509                    Value::Null,
510                    Value::Boolean(hasindexes),
511                    Value::Boolean(mv.with_data),
512                    Value::Text(mv.select_sql.clone().into()),
513                ]
514            })
515            .collect();
516        Ok(QueryResult { columns, rows })
517    }
518}
citadel_sql/system_tables.rs

citadel_sql/
system_tables.rs
