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

citadel_sql/
system_tables.rs
