trailbase_schema/
metadata.rs

1use jsonschema::Validator;
2use lazy_static::lazy_static;
3use log::*;
4use regex::Regex;
5use std::borrow::Borrow;
6use std::collections::HashMap;
7use std::hash::{Hash, Hasher};
8use std::sync::Arc;
9use thiserror::Error;
10
11use crate::sqlite::{Column, ColumnDataType, ColumnOption, QualifiedName, Table, View};
12
13// TODO: Can we merge this with crate::sqlite::SchemaError?
14#[derive(Debug, Clone, Error)]
15pub enum JsonSchemaError {
16  #[error("Schema compile error: {0}")]
17  SchemaCompile(String),
18  #[error("Validation error")]
19  Validation,
20  #[error("Schema not found: {0}")]
21  NotFound(String),
22  #[error("Json serialization error: {0}")]
23  JsonSerialization(Arc<serde_json::Error>),
24}
25
26#[derive(Clone, Debug, PartialEq)]
27pub enum JsonColumnMetadata {
28  SchemaName(String),
29  Pattern(serde_json::Value),
30}
31
32impl JsonColumnMetadata {
33  pub fn validate(&self, value: &serde_json::Value) -> Result<(), JsonSchemaError> {
34    match self {
35      Self::SchemaName(name) => {
36        let Some(schema) = crate::registry::get_compiled_schema(name) else {
37          return Err(JsonSchemaError::NotFound(name.to_string()));
38        };
39        schema
40          .validate(value)
41          .map_err(|_err| JsonSchemaError::Validation)?;
42        return Ok(());
43      }
44      Self::Pattern(pattern) => {
45        let schema =
46          Validator::new(pattern).map_err(|err| JsonSchemaError::SchemaCompile(err.to_string()))?;
47        if !schema.is_valid(value) {
48          Err(JsonSchemaError::Validation)
49        } else {
50          Ok(())
51        }
52      }
53    }
54  }
55}
56
57#[derive(Debug, Clone, PartialEq)]
58pub struct JsonMetadata {
59  pub columns: Vec<Option<JsonColumnMetadata>>,
60
61  // Contains both, 'std.FileUpload' and 'std.FileUpload'.
62  file_column_indexes: Vec<usize>,
63}
64
65impl JsonMetadata {
66  pub fn has_file_columns(&self) -> bool {
67    return !self.file_column_indexes.is_empty();
68  }
69
70  /// Contains both, 'std.FileUpload' and 'std.FileUpload'.
71  pub fn file_column_indexes(&self) -> &[usize] {
72    return &self.file_column_indexes;
73  }
74
75  fn from_table(table: &Table) -> Self {
76    return Self::from_columns(&table.columns);
77  }
78
79  fn from_view(view: &View) -> Option<Self> {
80    return view.columns.as_ref().map(|cols| Self::from_columns(cols));
81  }
82
83  fn from_columns(columns: &[Column]) -> Self {
84    let columns: Vec<_> = columns.iter().map(build_json_metadata).collect();
85
86    let file_column_indexes = find_file_column_indexes(&columns);
87
88    return Self {
89      columns,
90      file_column_indexes,
91    };
92  }
93}
94
95/// A data class describing a sqlite Table and additional meta data useful for TrailBase.
96///
97/// An example of TrailBase idiosyncrasies are UUIDv7 columns, which are a bespoke concept.
98#[derive(Debug, Clone)]
99pub struct TableMetadata {
100  pub schema: Table,
101
102  /// If and which column on this table qualifies as a record PK column, i.e. integer or UUIDv7.
103  pub record_pk_column: Option<usize>,
104  /// If and which columns on this table reference _user(id).
105  pub user_id_columns: Vec<usize>,
106  /// Metadata for CHECK(json_schema()) columns.
107  pub json_metadata: JsonMetadata,
108
109  name_to_index: HashMap<String, usize>,
110  // TODO: Add triggers once sqlparser supports a sqlite "CREATE TRIGGER" statements.
111}
112
113impl TableMetadata {
114  /// Build a new TableMetadata instance containing TrailBase/RecordApi specific information.
115  ///
116  /// NOTE: The list of all tables is needed only to extract interger/UUIDv7 pk columns for foreign
117  /// key relationships.
118  pub fn new(table: Table, tables: &[Table], user_table_name: &str) -> Self {
119    let name_to_index = HashMap::<String, usize>::from_iter(
120      table
121        .columns
122        .iter()
123        .enumerate()
124        .map(|(index, col)| (col.name.clone(), index)),
125    );
126
127    let record_pk_column = find_record_pk_column_index(&table.columns, tables);
128    let user_id_columns = find_user_id_foreign_key_columns(&table.columns, user_table_name);
129    let json_metadata = JsonMetadata::from_table(&table);
130
131    return TableMetadata {
132      schema: table,
133      name_to_index,
134      record_pk_column,
135      user_id_columns,
136      json_metadata,
137    };
138  }
139
140  #[inline]
141  pub fn name(&self) -> &QualifiedName {
142    return &self.schema.name;
143  }
144
145  #[inline]
146  pub fn column_index_by_name(&self, key: &str) -> Option<usize> {
147    return self.name_to_index.get(key).copied();
148  }
149
150  #[inline]
151  pub fn column_by_name(&self, key: &str) -> Option<(usize, &Column)> {
152    let index = self.column_index_by_name(key)?;
153    return Some((index, &self.schema.columns[index]));
154  }
155}
156
157// Implement `PartialEq`, `Hash`, and `Borrow` for TableMetadata based on fully qualified name for
158// use in HashSet.
159impl PartialEq for TableMetadata {
160  fn eq(&self, other: &Self) -> bool {
161    return self.schema.name == other.schema.name;
162  }
163}
164
165impl Eq for TableMetadata {}
166
167// Implement `PartialEq`, `Hash`, and `Borrow` for TableMetadata based on fully qualified name for
168// use in HashSet.
169impl Hash for TableMetadata {
170  fn hash<H: Hasher>(&self, state: &mut H) {
171    self.schema.name.hash(state);
172  }
173}
174
175// Implement `PartialEq`, `Hash`, and `Borrow` for TableMetadata based on fully qualified name for
176// use in HashSet.
177impl Borrow<QualifiedName> for TableMetadata {
178  fn borrow(&self) -> &QualifiedName {
179    return &self.schema.name;
180  }
181}
182
183// Implement `PartialEq`, `Hash`, and `Borrow` for TableMetadata based on fully qualified name for
184// use in HashSet.
185impl Borrow<QualifiedName> for Arc<TableMetadata> {
186  fn borrow(&self) -> &QualifiedName {
187    return &self.schema.name;
188  }
189}
190
191/// A data class describing a sqlite View and future, additional meta data useful for TrailBase.
192#[derive(Debug, Clone)]
193pub struct ViewMetadata {
194  pub schema: View,
195
196  name_to_index: HashMap<String, usize>,
197  record_pk_column: Option<usize>,
198  json_metadata: Option<JsonMetadata>,
199}
200
201impl ViewMetadata {
202  /// Build a new ViewMetadata instance containing TrailBase/RecordApi specific information.
203  ///
204  /// NOTE: The list of all tables is needed only to extract interger/UUIDv7 pk columns for foreign
205  /// key relationships.
206  pub fn new(view: View, tables: &[Table]) -> Self {
207    let name_to_index = if let Some(ref columns) = view.columns {
208      HashMap::<String, usize>::from_iter(
209        columns
210          .iter()
211          .enumerate()
212          .map(|(index, col)| (col.name.clone(), index)),
213      )
214    } else {
215      HashMap::<String, usize>::new()
216    };
217
218    let record_pk_column = view
219      .columns
220      .as_ref()
221      .and_then(|c| find_record_pk_column_index(c, tables));
222    let json_metadata = JsonMetadata::from_view(&view);
223
224    return ViewMetadata {
225      schema: view,
226      name_to_index,
227      record_pk_column,
228      json_metadata,
229    };
230  }
231
232  #[inline]
233  pub fn name(&self) -> &QualifiedName {
234    &self.schema.name
235  }
236
237  #[inline]
238  pub fn column_index_by_name(&self, key: &str) -> Option<usize> {
239    self.name_to_index.get(key).copied()
240  }
241
242  #[inline]
243  pub fn column_by_name(&self, key: &str) -> Option<(usize, &Column)> {
244    let index = self.column_index_by_name(key)?;
245    let cols = self.schema.columns.as_ref()?;
246    return Some((index, &cols[index]));
247  }
248}
249
250// Implement `PartialEq`, `Hash`, and `Borrow` for TableMetadata based on fully qualified name for
251// use in HashSet.
252impl PartialEq for ViewMetadata {
253  fn eq(&self, other: &Self) -> bool {
254    return self.schema.name == other.schema.name;
255  }
256}
257
258impl Eq for ViewMetadata {}
259
260// Implement `PartialEq`, `Hash`, and `Borrow` for TableMetadata based on fully qualified name for
261// use in HashSet.
262impl Hash for ViewMetadata {
263  fn hash<H: Hasher>(&self, state: &mut H) {
264    self.schema.name.hash(state);
265  }
266}
267
268// Implement `PartialEq`, `Hash`, and `Borrow` for TableMetadata based on fully qualified name for
269// use in HashSet.
270impl Borrow<QualifiedName> for ViewMetadata {
271  fn borrow(&self) -> &QualifiedName {
272    return &self.schema.name;
273  }
274}
275
276// Implement `PartialEq`, `Hash`, and `Borrow` for TableMetadata based on fully qualified name for
277// use in HashSet.
278impl Borrow<QualifiedName> for Arc<ViewMetadata> {
279  fn borrow(&self) -> &QualifiedName {
280    return &self.schema.name;
281  }
282}
283
284pub trait TableOrViewMetadata {
285  fn qualified_name(&self) -> &QualifiedName;
286  fn record_pk_column(&self) -> Option<(usize, &Column)>;
287  fn json_metadata(&self) -> Option<&JsonMetadata>;
288  fn columns(&self) -> Option<&[Column]>;
289}
290
291impl TableOrViewMetadata for TableMetadata {
292  fn qualified_name(&self) -> &QualifiedName {
293    return self.name();
294  }
295
296  fn columns(&self) -> Option<&[Column]> {
297    return Some(&self.schema.columns);
298  }
299
300  fn json_metadata(&self) -> Option<&JsonMetadata> {
301    return Some(&self.json_metadata);
302  }
303
304  fn record_pk_column(&self) -> Option<(usize, &Column)> {
305    let index = self.record_pk_column?;
306    return self.schema.columns.get(index).map(|c| (index, c));
307  }
308}
309
310impl TableOrViewMetadata for ViewMetadata {
311  fn qualified_name(&self) -> &QualifiedName {
312    return self.name();
313  }
314
315  fn columns(&self) -> Option<&[Column]> {
316    return self.schema.columns.as_deref();
317  }
318
319  fn json_metadata(&self) -> Option<&JsonMetadata> {
320    return self.json_metadata.as_ref();
321  }
322
323  fn record_pk_column(&self) -> Option<(usize, &Column)> {
324    let Some(columns) = &self.schema.columns else {
325      return None;
326    };
327    let index = self.record_pk_column?;
328    return columns.get(index).map(|c| (index, c));
329  }
330}
331
332fn build_json_metadata(col: &Column) -> Option<JsonColumnMetadata> {
333  for opt in &col.options {
334    match extract_json_metadata(opt) {
335      Ok(maybe) => {
336        if let Some(jm) = maybe {
337          return Some(jm);
338        }
339      }
340      Err(err) => {
341        error!("Failed to get JSON schema: {err}");
342      }
343    }
344  }
345  None
346}
347
348pub fn extract_json_metadata(
349  opt: &ColumnOption,
350) -> Result<Option<JsonColumnMetadata>, JsonSchemaError> {
351  let ColumnOption::Check(check) = opt else {
352    return Ok(None);
353  };
354
355  lazy_static! {
356    static ref SCHEMA_RE: Regex =
357      Regex::new(r#"(?smR)jsonschema\s*\(\s*[\['"](?<name>.*)[\]'"]\s*,.+?\)"#)
358        .expect("infallible");
359    static ref MATCHES_RE: Regex =
360      Regex::new(r"(?smR)jsonschema_matches\s*\(.+?(?<pattern>\{.*\}).+?\)").expect("infallible");
361  }
362
363  if let Some(cap) = SCHEMA_RE.captures(check) {
364    let name = &cap["name"];
365    let Some(_schema) = crate::registry::get_schema(name) else {
366      let schemas: Vec<String> = crate::registry::get_schemas()
367        .iter()
368        .map(|s| s.name.clone())
369        .collect();
370      return Err(JsonSchemaError::NotFound(format!(
371        "Json schema {name} not found in: {schemas:?}"
372      )));
373    };
374
375    return Ok(Some(JsonColumnMetadata::SchemaName(name.to_string())));
376  }
377
378  if let Some(cap) = MATCHES_RE.captures(check) {
379    let pattern = &cap["pattern"];
380    let value = serde_json::from_str::<serde_json::Value>(pattern)
381      .map_err(|err| JsonSchemaError::JsonSerialization(Arc::new(err)))?;
382    return Ok(Some(JsonColumnMetadata::Pattern(value)));
383  }
384
385  return Ok(None);
386}
387
388pub fn find_file_column_indexes(json_column_metadata: &[Option<JsonColumnMetadata>]) -> Vec<usize> {
389  let mut indexes: Vec<usize> = vec![];
390
391  for (index, column) in json_column_metadata.iter().enumerate() {
392    if let Some(metadata) = column {
393      match metadata {
394        JsonColumnMetadata::SchemaName(name) if name == "std.FileUpload" => {
395          indexes.push(index);
396        }
397        JsonColumnMetadata::SchemaName(name) if name == "std.FileUploads" => {
398          indexes.push(index);
399        }
400        _ => {}
401      };
402    }
403  }
404
405  return indexes;
406}
407
408pub fn find_user_id_foreign_key_columns(columns: &[Column], user_table_name: &str) -> Vec<usize> {
409  let mut indexes: Vec<usize> = vec![];
410  for (index, col) in columns.iter().enumerate() {
411    for opt in &col.options {
412      if let ColumnOption::ForeignKey {
413        foreign_table,
414        referred_columns,
415        ..
416      } = opt
417      {
418        if foreign_table == user_table_name
419          && referred_columns.len() == 1
420          && referred_columns[0] == "id"
421        {
422          indexes.push(index);
423        }
424      }
425    }
426  }
427  return indexes;
428}
429
430pub(crate) fn find_pk_column_index(columns: &[Column]) -> Option<usize> {
431  return columns.iter().position(|col| {
432    for opt in &col.options {
433      if let ColumnOption::Unique { is_primary, .. } = opt {
434        return *is_primary;
435      }
436    }
437    return false;
438  });
439}
440
441/// Finds suitable Integer or UUIDv7/UUIDv4 primary key columns, if present.
442///
443/// Cursors require certain properties like a stable, time-sortable primary key.
444fn find_record_pk_column_index(columns: &[Column], tables: &[Table]) -> Option<usize> {
445  let index = find_pk_column_index(columns)?;
446  let column = &columns[index];
447
448  if column.data_type == ColumnDataType::Integer {
449    // TODO: We should detect the "integer pk" desc case and at least warn:
450    // https://www.sqlite.org/lang_createtable.html#rowid.
451    return Some(index);
452  }
453
454  for opts in &column.options {
455    lazy_static! {
456      static ref UUID_CHECK_RE: Regex = Regex::new(r"^is_uuid(|_v7|_v4)\s*\(").expect("infallible");
457    }
458
459    match &opts {
460      // Check if the referenced column is a uuidv7 column.
461      ColumnOption::ForeignKey {
462        foreign_table,
463        referred_columns,
464        ..
465      } => {
466        // NOTE: Foreign keys cannot cross database boundaries, we can therefore compare by
467        // unqualified name.
468        let Some(referred_table) = tables.iter().find(|t| t.name.name == *foreign_table) else {
469          error!("Failed to get foreign key schema for {foreign_table}");
470          continue;
471        };
472
473        if referred_columns.len() != 1 {
474          return None;
475        }
476        let referred_column = &referred_columns[0];
477
478        let col = referred_table
479          .columns
480          .iter()
481          .find(|c| c.name == *referred_column)?;
482
483        let mut is_pk = false;
484        for opt in &col.options {
485          match opt {
486            ColumnOption::Check(expr) if UUID_CHECK_RE.is_match(expr) => {
487              return Some(index);
488            }
489            ColumnOption::Unique { is_primary, .. } if *is_primary => {
490              is_pk = true;
491            }
492            _ => {}
493          }
494        }
495
496        if is_pk && col.data_type == ColumnDataType::Integer {
497          return Some(index);
498        }
499
500        return None;
501      }
502      ColumnOption::Check(expr) if UUID_CHECK_RE.is_match(expr) => {
503        return Some(index);
504      }
505      _ => {}
506    }
507  }
508
509  return None;
510}
511
512#[cfg(test)]
513mod tests {
514  use std::collections::HashSet;
515
516  use super::*;
517  use crate::sqlite::{Table, sqlite3_parse_into_statement};
518
519  #[test]
520  fn test_parse_create_view() {
521    let table_name = QualifiedName {
522      name: "table_name".to_string(),
523      database_schema: Some("main".to_string()),
524    };
525    let table_sql = format!(
526      r#"
527      CREATE TABLE {table_name} (
528          id                           BLOB PRIMARY KEY NOT NULL CHECK(is_uuid_v7(id)) DEFAULT (uuid_v7()),
529          col0                         TEXT NOT NULL DEFAULT '',
530          col1                         BLOB NOT NULL,
531          hidden                       INTEGER DEFAULT 42
532      ) STRICT;"#,
533      table_name = table_name.escaped_string(),
534    );
535
536    let create_table_statement = sqlite3_parse_into_statement(&table_sql).unwrap().unwrap();
537
538    let table: Table = create_table_statement.try_into().unwrap();
539
540    {
541      let metadata = TableMetadata::new(table.clone(), &[table.clone()], "_user");
542
543      assert_eq!(table_name, *metadata.name());
544      assert_eq!("col1", metadata.columns().unwrap()[2].name);
545      assert_eq!(1, *metadata.name_to_index.get("col0").unwrap());
546    }
547
548    let view_name = QualifiedName {
549      name: "view_name".to_string(),
550      database_schema: Some("main".to_string()),
551    };
552
553    {
554      let query = format!("SELECT col0, col1 FROM {}", table_name.escaped_string());
555      let view_sql = format!(
556        "CREATE VIEW {view_name} AS {query}",
557        view_name = view_name.escaped_string()
558      );
559      let create_view_statement = sqlite3_parse_into_statement(&view_sql).unwrap().unwrap();
560
561      let table_view = View::from(create_view_statement, &[table.clone()]).unwrap();
562
563      assert_eq!(table_view.name, view_name);
564      assert_eq!(table_view.query, query);
565      assert_eq!(table_view.temporary, false);
566
567      let view_columns = table_view.columns.as_ref().unwrap();
568
569      assert_eq!(view_columns.len(), 2);
570      assert_eq!(view_columns[0].name, "col0");
571      assert_eq!(view_columns[0].data_type, ColumnDataType::Text);
572
573      assert_eq!(view_columns[1].name, "col1");
574      assert_eq!(view_columns[1].data_type, ColumnDataType::Blob);
575
576      let view_metadata = ViewMetadata::new(table_view, &[table.clone()]);
577
578      assert!(view_metadata.record_pk_column().is_none());
579      assert_eq!(view_metadata.columns().as_ref().unwrap().len(), 2);
580    }
581
582    {
583      let query = format!("SELECT id, col0, col1 FROM {}", table_name.escaped_string());
584      let view_sql = format!(
585        "CREATE VIEW {view_name} AS {query}",
586        view_name = view_name.escaped_string()
587      );
588      let create_view_statement = sqlite3_parse_into_statement(&view_sql).unwrap().unwrap();
589
590      let table_view = View::from(create_view_statement, &[table.clone()]).unwrap();
591
592      assert_eq!(table_view.name, view_name);
593      assert_eq!(table_view.query, query);
594      assert_eq!(table_view.temporary, false);
595
596      let view_metadata = ViewMetadata::new(table_view, &[table.clone()]);
597
598      let uuidv7_col = view_metadata.record_pk_column().unwrap();
599      let columns = view_metadata.columns().unwrap();
600      assert_eq!(columns.len(), 3);
601      assert_eq!(columns[uuidv7_col.0].name, "id");
602    }
603  }
604
605  #[test]
606  fn test_metadata_hash_set_by_name() {
607    let table_name = QualifiedName {
608      name: "table_name".to_string(),
609      database_schema: Some("main".to_string()),
610    };
611    let table_sql = format!(
612      "CREATE TABLE {table_name} (id INTEGER PRIMARY KEY) STRICT",
613      table_name = table_name.escaped_string()
614    );
615    let create_table_statement = sqlite3_parse_into_statement(&table_sql).unwrap().unwrap();
616    let table: Table = create_table_statement.try_into().unwrap();
617    let table_metadata = TableMetadata::new(table.clone(), &[table.clone()], "_user");
618
619    let mut table_set = HashSet::<TableMetadata>::new();
620
621    assert!(table_set.insert(table_metadata.clone()));
622    assert!(table_set.get(&table_name).is_some());
623    assert_eq!(
624      table_set.get(&QualifiedName::parse("table_name").unwrap()),
625      Some(&table_metadata)
626    );
627
628    // Test Arc<views>:
629    let view_name = QualifiedName {
630      name: "view_name".to_string(),
631      database_schema: Some("main".to_string()),
632    };
633    let view_sql = format!(
634      "CREATE VIEW {view_name} AS SELECT id FROM {table_name}",
635      view_name = view_name.escaped_string(),
636      table_name = table_name.escaped_string()
637    );
638    let create_view_statement = sqlite3_parse_into_statement(&view_sql).unwrap().unwrap();
639    let table_view = View::from(create_view_statement, &[table.clone()]).unwrap();
640    let view_metadata = Arc::new(ViewMetadata::new(table_view, &[table.clone()]));
641
642    let mut view_set = HashSet::<Arc<ViewMetadata>>::new();
643
644    assert!(view_set.insert(view_metadata.clone()));
645    assert_eq!(view_set.get(&view_name), Some(&view_metadata));
646    assert_eq!(
647      view_set.get(&QualifiedName::parse("view_name").unwrap()),
648      Some(&view_metadata)
649    );
650  }
651}
trailbase_schema/metadata.rs

trailbase_schema/
metadata.rs
