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        DataType::Vector { .. } => "VECTOR",
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, 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, 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, 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
