trailbase_schema/
json_schema.rs

1use jsonschema::Validator;
2use log::*;
3use serde::{Deserialize, Serialize};
4use serde_json::Value;
5
6use crate::metadata::{JsonColumnMetadata, JsonSchemaError, TableMetadata, extract_json_metadata};
7use crate::sqlite::{Column, ColumnDataType, ColumnOption};
8
9/// Influeces the generated JSON schema. In `Insert` mode columns with default values will be
10/// optional.
11#[derive(Copy, Clone, Debug, Deserialize, Serialize)]
12pub enum JsonSchemaMode {
13  /// Insert mode.
14  Insert,
15  /// Read/Select mode.
16  Select,
17  /// Update mode.
18  Update,
19}
20
21/// Builds a JSON Schema definition for the given table.
22///
23/// NOTE: insert and select require different types to model default values, i.e. a column with a
24/// default value is optional during insert but guaranteed during reads.
25///
26/// NOTE: We're not currently respecting the RecordApi `autofill_missing_user_id_columns`
27/// setting. Not sure we should since this is more a feature for no-JS, HTTP-only apps, which
28/// don't benefit from type-safety anyway.
29pub fn build_json_schema(
30  title: &str,
31  columns: &[Column],
32  mode: JsonSchemaMode,
33) -> Result<(Validator, serde_json::Value), JsonSchemaError> {
34  return build_json_schema_expanded(title, columns, mode, None);
35}
36
37#[derive(Debug)]
38pub struct Expand<'a> {
39  pub tables: &'a [TableMetadata],
40  pub foreign_key_columns: Vec<&'a str>,
41}
42
43/// NOTE: Foreign keys can only reference tables not view, so the inline schemas don't need to be
44/// able to reference views.
45pub fn build_json_schema_expanded(
46  title: &str,
47  columns: &[Column],
48  mode: JsonSchemaMode,
49  expand: Option<Expand<'_>>,
50) -> Result<(Validator, serde_json::Value), JsonSchemaError> {
51  let mut properties = serde_json::Map::new();
52  let mut defs = serde_json::Map::new();
53  let mut required_cols: Vec<String> = vec![];
54
55  for col in columns {
56    let mut def_name: Option<String> = None;
57    let mut not_null = false;
58    let mut default = false;
59
60    for opt in &col.options {
61      match opt {
62        ColumnOption::NotNull => not_null = true,
63        ColumnOption::Default(_) => default = true,
64        ColumnOption::Check(check) => {
65          if let Some(json_metadata) = extract_json_metadata(&ColumnOption::Check(check.clone()))? {
66            let new_def_name = &col.name;
67            match json_metadata {
68              JsonColumnMetadata::SchemaName(name) => {
69                let Some(schema) = crate::registry::get_schema(&name) else {
70                  return Err(JsonSchemaError::NotFound(name.to_string()));
71                };
72                defs.insert(new_def_name.clone(), schema.schema);
73                def_name = Some(new_def_name.clone());
74              }
75              JsonColumnMetadata::Pattern(pattern) => {
76                defs.insert(new_def_name.clone(), pattern.clone());
77                def_name = Some(new_def_name.clone());
78              }
79            }
80          }
81        }
82        ColumnOption::Unique { is_primary, .. } => {
83          // According to the SQL standard, PRIMARY KEY should always imply NOT NULL.
84          // Unfortunately, due to a bug in some early versions, this is not the case in SQLite.
85          // Unless the column is an INTEGER PRIMARY KEY or the table is a WITHOUT ROWID table or a
86          // STRICT table or the column is declared NOT NULL, SQLite allows NULL values in a
87          // PRIMARY KEY column
88          // source: https://www.sqlite.org/lang_createtable.html
89          if *is_primary {
90            if col.data_type == ColumnDataType::Integer {
91              not_null = true;
92            }
93
94            default = true;
95          }
96        }
97        ColumnOption::ForeignKey {
98          foreign_table,
99          referred_columns,
100          ..
101        } => {
102          if let (Some(expand), JsonSchemaMode::Select) = (&expand, mode) {
103            let column_is_expanded = expand
104              .foreign_key_columns
105              .iter()
106              .any(|column_name| *column_name != col.name);
107            if !column_is_expanded {
108              continue;
109            }
110
111            // NOTE: Foreign keys cannot cross database boundaries, we can therefore compare by
112            // unqualified name.
113            let Some(table) = expand
114              .tables
115              .iter()
116              .find(|t| t.name().name == *foreign_table)
117            else {
118              warn!("Failed to find table: {foreign_table}");
119              continue;
120            };
121
122            let Some(pk_column) = (match referred_columns.len() {
123              0 => crate::metadata::find_pk_column_index(&table.schema.columns)
124                .map(|idx| &table.schema.columns[idx]),
125              1 => table
126                .schema
127                .columns
128                .iter()
129                .find(|c| c.name == referred_columns[0]),
130              _ => {
131                warn!("Skipping. Expected single referred column : {referred_columns:?}");
132                continue;
133              }
134            }) else {
135              warn!("Failed to find pk column for {:?}", table.name());
136              continue;
137            };
138
139            let (_validator, schema) =
140              build_json_schema(foreign_table, &table.schema.columns, mode)?;
141
142            let new_def_name = foreign_table.clone();
143            defs.insert(
144              new_def_name.clone(),
145              serde_json::json!({
146                "type": "object",
147                "properties": {
148                  "id": {
149                    "type": column_data_type_to_json_type(pk_column.data_type),
150                  },
151                  "data": schema,
152                },
153                "required": ["id"],
154              }),
155            );
156            def_name = Some(new_def_name);
157          }
158        }
159        _ => {}
160      }
161    }
162
163    match mode {
164      JsonSchemaMode::Insert => {
165        if not_null && !default {
166          required_cols.push(col.name.clone());
167        }
168      }
169      JsonSchemaMode::Select => {
170        if not_null {
171          required_cols.push(col.name.clone());
172        }
173      }
174      JsonSchemaMode::Update => {}
175    }
176
177    properties.insert(
178      col.name.clone(),
179      if let Some(def_name) = def_name {
180        serde_json::json!({
181          "$ref": format!("#/$defs/{def_name}")
182        })
183      } else {
184        serde_json::json!({
185          "type": column_data_type_to_json_type(col.data_type),
186        })
187      },
188    );
189  }
190
191  let schema = if defs.is_empty() {
192    serde_json::json!({
193      "title": title,
194      "type": "object",
195      "properties": serde_json::Value::Object(properties),
196      "required": serde_json::json!(required_cols),
197    })
198  } else {
199    serde_json::json!({
200      "title": title,
201      "type": "object",
202      "properties": serde_json::Value::Object(properties),
203      "required": serde_json::json!(required_cols),
204      "$defs": serde_json::Value::Object(defs),
205    })
206  };
207
208  return Ok((
209    Validator::new(&schema).map_err(|err| JsonSchemaError::SchemaCompile(err.to_string()))?,
210    schema,
211  ));
212}
213
214fn column_data_type_to_json_type(data_type: ColumnDataType) -> Value {
215  return match data_type {
216    ColumnDataType::Null => Value::String("null".into()),
217    ColumnDataType::Any => Value::Array(vec![
218      "number".into(),
219      "string".into(),
220      "boolean".into(),
221      "object".into(),
222      "array".into(),
223      "null".into(),
224    ]),
225    ColumnDataType::Text => Value::String("string".into()),
226    // We encode all blobs as url-safe Base64.
227    ColumnDataType::Blob => Value::String("string".into()),
228    ColumnDataType::Integer => Value::String("integer".into()),
229    ColumnDataType::Real => Value::String("number".into()),
230    ColumnDataType::Numeric => Value::String("number".into()),
231    // JSON types
232    ColumnDataType::JSON => Value::String("object".into()),
233    ColumnDataType::JSONB => Value::String("object".into()),
234    // Affine types
235    //
236    // Integers:
237    ColumnDataType::Int => Value::String("number".into()),
238    ColumnDataType::TinyInt => Value::String("number".into()),
239    ColumnDataType::SmallInt => Value::String("number".into()),
240    ColumnDataType::MediumInt => Value::String("number".into()),
241    ColumnDataType::BigInt => Value::String("number".into()),
242    ColumnDataType::UnignedBigInt => Value::String("number".into()),
243    ColumnDataType::Int2 => Value::String("number".into()),
244    ColumnDataType::Int4 => Value::String("number".into()),
245    ColumnDataType::Int8 => Value::String("number".into()),
246    // Text:
247    ColumnDataType::Character => Value::String("string".into()),
248    ColumnDataType::Varchar => Value::String("string".into()),
249    ColumnDataType::VaryingCharacter => Value::String("string".into()),
250    ColumnDataType::NChar => Value::String("string".into()),
251    ColumnDataType::NativeCharacter => Value::String("string".into()),
252    ColumnDataType::NVarChar => Value::String("string".into()),
253    ColumnDataType::Clob => Value::String("string".into()),
254    // Real:
255    ColumnDataType::Double => Value::String("number".into()),
256    ColumnDataType::DoublePrecision => Value::String("number".into()),
257    ColumnDataType::Float => Value::String("number".into()),
258    // Numeric:
259    ColumnDataType::Boolean => Value::String("boolean".into()),
260    ColumnDataType::Decimal => Value::String("number".into()),
261    ColumnDataType::Date => Value::String("number".into()),
262    ColumnDataType::DateTime => Value::String("number".into()),
263  };
264}
265
266#[cfg(test)]
267mod tests {
268  use serde_json::json;
269
270  use crate::FileUpload;
271  use crate::sqlite::{ColumnOption, lookup_and_parse_table_schema};
272
273  use super::*;
274
275  #[tokio::test]
276  async fn test_parse_table_schema() {
277    crate::registry::try_init_schemas();
278
279    let conn = trailbase_extension::connect_sqlite(None, None).unwrap();
280
281    let check = indoc::indoc! {r#"
282        jsonschema_matches ('{
283          "type": "object",
284          "additionalProperties": false,
285          "properties": {
286            "name": {
287              "type": "string"
288            },
289            "age": {
290              "type": "integer",
291              "minimum": 0
292            }
293          },
294          "required": ["name", "age"]
295        }', col0)"#
296    };
297
298    conn
299      .execute(
300        &format!(
301          r#"CREATE TABLE test_table (
302            col0 TEXT CHECK({check}),
303            col1 TEXT CHECK(jsonschema('std.FileUpload', col1)),
304            col2 TEXT,
305            col3 TEXT CHECK(jsonschema('std.FileUpload', col3, 'image/jpeg, image/png'))
306          ) STRICT"#
307        ),
308        (),
309      )
310      .unwrap();
311
312    let insert = |col: &'static str, json: serde_json::Value| {
313      conn.execute(
314        &format!(
315          "INSERT INTO test_table ({col}) VALUES ('{}')",
316          json.to_string()
317        ),
318        (),
319      )
320    };
321
322    assert!(insert("col2", json!({"name": 42})).unwrap() > 0);
323    assert!(
324      insert(
325        "col1",
326        serde_json::to_value(FileUpload::new(
327          uuid::Uuid::now_v7(),
328          Some("filename".to_string()),
329          None,
330          None
331        ))
332        .unwrap()
333      )
334      .unwrap()
335        > 0
336    );
337    assert!(insert("col1", json!({"foo": "/foo"})).is_err());
338    assert!(insert("col0", json!({"name": 42})).is_err());
339    assert!(insert("col0", json!({"name": "Alice"})).is_err());
340    assert!(insert("col0", json!({"name": "Alice", "age": 23})).unwrap() > 0);
341    assert!(
342      insert(
343        "col0",
344        json!({"name": "Alice", "age": 23, "additional": 42})
345      )
346      .is_err()
347    );
348
349    assert!(insert("col3", json!({"foo": "/foo"})).is_err());
350    assert!(
351      insert(
352        "col3",
353        json!({
354            "id": uuid::Uuid::now_v7().to_string(),
355            // Missing mime-type.
356        })
357      )
358      .is_err()
359    );
360    assert!(insert("col3", json!({"mime_type": "invalid"})).is_err());
361    assert!(
362      insert(
363        "col3",
364        json!({
365          "id": uuid::Uuid::now_v7().to_string(),
366          "mime_type": "image/png"
367        })
368      )
369      .is_ok()
370    );
371
372    let cnt: i64 = conn
373      .query_row("SELECT COUNT(*) FROM test_table", (), |row| row.get(0))
374      .unwrap();
375
376    assert_eq!(cnt, 4);
377
378    let table = lookup_and_parse_table_schema(&conn, "test_table").unwrap();
379
380    let col = table.columns.first().unwrap();
381    let check_expr = col
382      .options
383      .iter()
384      .filter_map(|c| match c {
385        ColumnOption::Check(check) => Some(check),
386        _ => None,
387      })
388      .collect::<Vec<_>>()[0];
389
390    assert_eq!(check_expr, check);
391    let table_metadata = TableMetadata::new(table.clone(), &[table], "_user");
392
393    let (schema, _) = build_json_schema(
394      &table_metadata.name().name,
395      &table_metadata.schema.columns,
396      JsonSchemaMode::Insert,
397    )
398    .unwrap();
399    assert!(schema.is_valid(&json!({
400      "col2": "test",
401    })));
402
403    assert!(schema.is_valid(&json!({
404      "col0": json!({
405        "name": "Alice", "age": 23,
406      }),
407    })));
408
409    assert!(!schema.is_valid(&json!({
410      "col0": json!({
411        "name": 42, "age": "23",
412      }),
413    })));
414  }
415}
trailbase_schema/json_schema.rs

trailbase_schema/
json_schema.rs
