cratefield-tables 0.2.0

//! DDL generation: one definition, two dialects.
//!
//! Forward-only. Everything rendered here creates: `CREATE TABLE IF NOT
//! EXISTS` and `CREATE INDEX IF NOT EXISTS`, never `DROP` and never
//! `ALTER`. The diff between two versions of a definition is a separate
//! problem and is not solved here.
//!
//! Deterministic. The same definition always renders byte-identical SQL:
//! tables come out in creation order, columns in declaration order, and
//! every clause on a column has a fixed position. Nothing here reads a
//! clock, a random number or a hash map.
//!
//! Creation order is a topological sort of the foreign-key graph, so a
//! table is always created after the tables it references. Postgres
//! refuses an inline `REFERENCES` to a table that does not exist yet, and
//! plain name order puts `line_item` before `product`. Among the tables
//! whose references are already created, the alphabetically first goes
//! next, which makes the order the lexicographically smallest one that
//! works and keeps the output byte-identical for a given schema.
//!
//! The output stays inside the portable subset the hand-written module
//! migrations use (ADR 0004): text ids, ISO-8601 text timestamps, integer
//! counters, no `AUTOINCREMENT`, no `SERIAL`, no dialect function in DDL.
//! A test runs [`cratefield_core::lint_portable_sql`] over both dialects'
//! output, which is the same predicate `fz doctor` applies to a module.
//!
//! Identifiers are never quoted, because [`Schema::validate`] rejects any
//! name that would need quoting. String literals are quoted, with an
//! embedded quote doubled.
//!
//! # Column types
//!
//! | kind | SQLite | Postgres |
//! |---|---|---|
//! | `text`, `uuid`, `json`, `enum` | `TEXT` | `TEXT` |
//! | `timestamp` | `TEXT` | `TEXT` |
//! | `integer` | `INTEGER` | `BIGINT` |
//! | `real` | `REAL` | `DOUBLE PRECISION` |
//! | `boolean` | `INTEGER` | `BOOLEAN` |
//!
//! A timestamp is text in both, and a UUID is text in both, because the
//! harness stores ISO-8601 timestamps and text ids everywhere else and a
//! declared table should not be the one place that does not. JSON is text
//! for the same reason: `module-cms` already keeps its structured fields
//! as text in the portable subset.
//!
//! Boolean is the one kind whose storage differs, `0` and `1` against
//! `FALSE` and `TRUE`, because SQLite has no boolean type. Row values
//! stay `true` and `false` on the wire in both cases. Converting them for
//! the engine belongs to the writer, which is not built yet.
//!
//! # Where the two engines are not the same
//!
//! The DDL is byte-identical in shape, but three guarantees are weaker on
//! SQLite. They are stated here because someone reading only the table
//! above would assume a declared table behaves the same on both, and each
//! one is a real difference in what the engine enforces:
//!
//! - **An integer primary key is a rowid alias in SQLite.** A
//!   `PRIMARY KEY` on an `INTEGER` column makes that column the table's
//!   rowid: it auto-assigns when the write omits it, and it accepts no
//!   other type. On Postgres a `BIGINT PRIMARY KEY` is an ordinary
//!   column that must be supplied. A declaration that relies on the
//!   database filling the key in works on one engine and not the other,
//!   so do not rely on it — the row validator requires a primary key to
//!   be present or defaulted precisely so the two agree.
//! - **A boolean column is unconstrained on SQLite.** It is stored as
//!   `INTEGER` and nothing stops `7` from being written directly through
//!   SQL. Postgres rejects it. The row validator is what makes the two
//!   agree, which means a write that bypasses it is only checked on one
//!   of the two engines.
//! - **Foreign keys are inert on SQLite unless the connection asks for
//!   them.** `REFERENCES` renders in both dialects, but SQLite enforces
//!   it only with `PRAGMA foreign_keys=ON`, which is per connection and
//!   off by default — and nothing in this repository sets it. So a
//!   foreign key is a comment on SQLite today and a constraint on
//!   Postgres. The topological creation order is still right for both;
//!   it is the enforcement that differs.
//!
//! None of the three is worked around here. A generated `CHECK` for
//! booleans, or a pragma issued behind the caller's back, would make the
//! DDL stop being the portable subset the hand-written migrations use.
//! They belong to the writer and the connection setup, which are #153's
//! CRUD layer, and are written down so that layer inherits a list rather
//! than a surprise.

use cratefield_core::ConfigError;
use serde_json::Value;
use std::fmt::Write as _;

use crate::schema::{FieldDef, FieldKind, Schema, TableDef};

/// The engine a rendering targets.
#[derive(Debug, Clone, Copy, PartialEq, Eq)]
pub enum SqlDialect {
    /// SQLite, which is what D1 is.
    Sqlite,
    /// Postgres, the phase-3 target.
    Postgres,
}

impl SqlDialect {
    /// Both dialects, in a fixed order, for a test that renders each.
    pub const ALL: &'static [SqlDialect] = &[SqlDialect::Sqlite, SqlDialect::Postgres];

    /// The lowercase engine name, the same spelling the testing kit's
    /// dialect uses: `sqlite` or `postgres`.
    #[must_use]
    pub fn name(self) -> &'static str {
        match self {
            SqlDialect::Sqlite => "sqlite",
            SqlDialect::Postgres => "postgres",
        }
    }

    fn column_type(self, kind: &FieldKind) -> &'static str {
        match kind {
            FieldKind::Text { .. }
            | FieldKind::Timestamp
            | FieldKind::Uuid
            | FieldKind::Json
            | FieldKind::Enum { .. } => "TEXT",
            FieldKind::Integer { .. } => match self {
                SqlDialect::Sqlite => "INTEGER",
                SqlDialect::Postgres => "BIGINT",
            },
            FieldKind::Real { .. } => match self {
                SqlDialect::Sqlite => "REAL",
                SqlDialect::Postgres => "DOUBLE PRECISION",
            },
            FieldKind::Boolean => match self {
                SqlDialect::Sqlite => "INTEGER",
                SqlDialect::Postgres => "BOOLEAN",
            },
        }
    }
}

/// The name of the index a declared `indexed` field gets:
/// `<table>_<field>_idx`. Postgres index names are unique across a
/// schema and not per table, so the table name is part of it.
#[must_use]
pub fn index_name(table: &str, field: &str) -> String {
    format!("{table}_{field}_idx")
}

/// Whether an `indexed` field actually gets its own `CREATE INDEX`. A
/// unique or primary-key column is already indexed by its constraint, so
/// a second index on it would only cost writes.
#[must_use]
pub(crate) fn needs_own_index(table: &TableDef, field: &FieldDef) -> bool {
    field.indexed && !field.unique && !table.is_primary_key(&field.name)
}

/// The tables `table` has to be created after: every declared table it
/// points a foreign key at, other than itself. A self-reference is legal
/// inline in both engines, so it is not a dependency and not a cycle. A
/// reference to an undeclared table is reported by
/// [`Schema::validate`] and is not a dependency either.
fn depends_on<'a>(schema: &'a Schema, table: &'a TableDef) -> impl Iterator<Item = &'a str> {
    table
        .foreign_keys
        .iter()
        .map(|key| key.references.as_str())
        .filter(move |name| *name != table.name && schema.table(name).is_some())
}

/// The order `CREATE TABLE` statements must come in, and the tables no
/// order can satisfy.
///
/// The first list is the lexicographically smallest topological sort of
/// the foreign-key graph: at every step the alphabetically first table
/// whose references are already created. The second holds whatever is
/// left, which is exactly the tables on or downstream of a reference
/// cycle. [`Schema::validate`] rejects those and [`Schema::ddl`]
/// validates first, so a rendering never sees a non-empty second list.
pub(crate) fn creation_order(schema: &Schema) -> (Vec<&TableDef>, Vec<&TableDef>) {
    let mut done = vec![false; schema.tables.len()];
    let mut created: Vec<&str> = Vec::with_capacity(schema.tables.len());
    let mut order: Vec<&TableDef> = Vec::with_capacity(schema.tables.len());

    while let Some((index, table)) = schema
        .tables
        .iter()
        .enumerate()
        .filter(|(index, table)| {
            !done[*index] && depends_on(schema, table).all(|name| created.contains(&name))
        })
        // `min_by_key` keeps the first of equal keys, so a schema whose
        // tables were not sorted still renders one fixed order.
        .min_by_key(|(_, table)| table.name.as_str())
    {
        done[index] = true;
        created.push(&table.name);
        order.push(table);
    }

    let cyclic = schema
        .tables
        .iter()
        .enumerate()
        .filter(|(index, _)| !done[*index])
        .map(|(_, table)| table)
        .collect();
    (order, cyclic)
}

impl Schema {
    /// Renders every declared table for `dialect` as one script:
    /// a `CREATE TABLE` per table, in creation order, each followed by
    /// its `CREATE INDEX` statements in field order.
    ///
    /// Creation order is the topological sort of the foreign-key graph
    /// described on this module, so a referenced table is always created
    /// first and the script runs on Postgres as written.
    ///
    /// The schema is validated first, so SQL can never come from a
    /// definition whose identifiers were not checked.
    ///
    /// # Errors
    ///
    /// The [`ConfigError`] from [`Schema::validate`], unchanged.
    pub fn ddl(&self, dialect: SqlDialect) -> Result<String, ConfigError> {
        self.validate()?;
        let mut out = format!(
            "-- Generated by cratefield-tables from the venture manifest.\n\
             -- Dialect: {}. Forward-only: this file only creates.\n",
            dialect.name()
        );
        // `validate` rejected every cycle, so the second list is empty.
        let (ordered, _) = creation_order(self);
        for table in ordered {
            out.push('\n');
            out.push_str(&self.create_table_sql(table, dialect));
            for index in create_index_sql(table) {
                out.push_str(&index);
            }
        }
        Ok(out)
    }

    /// One table's `CREATE TABLE IF NOT EXISTS`, ending in a newline.
    /// Rendering lives on the schema because a foreign key names the
    /// target table's primary-key column, which only the schema knows.
    fn create_table_sql(&self, table: &TableDef, dialect: SqlDialect) -> String {
        let mut parts: Vec<String> = table
            .fields
            .iter()
            .map(|field| column_sql(table, field, dialect))
            .collect();

        parts.push(format!("PRIMARY KEY ({})", table.primary_key.join(", ")));
        for key in &table.foreign_keys {
            let column = self
                .table(&key.references)
                .and_then(|target| target.primary_key.first())
                .map_or("id", String::as_str);
            parts.push(format!(
                "FOREIGN KEY ({}) REFERENCES {} ({column})",
                key.field, key.references
            ));
        }

        format!(
            "CREATE TABLE IF NOT EXISTS {} (\n    {}\n);\n",
            table.name,
            parts.join(",\n    ")
        )
    }
}

/// `<name> <type>[ NOT NULL][ UNIQUE][ DEFAULT <literal>][ CHECK (...)]`,
/// always in that order.
fn column_sql(table: &TableDef, field: &FieldDef, dialect: SqlDialect) -> String {
    let mut sql = format!("{} {}", field.name, dialect.column_type(&field.kind));
    if field.required || table.is_primary_key(&field.name) {
        sql.push_str(" NOT NULL");
    }
    if field.unique {
        sql.push_str(" UNIQUE");
    }
    if let Some(default) = &field.default {
        sql.push_str(" DEFAULT ");
        sql.push_str(&literal(&field.kind, default, dialect));
    }
    if let FieldKind::Enum { values } = &field.kind {
        let allowed: Vec<String> = values.iter().map(|value| quoted(value)).collect();
        let _ = write!(sql, " CHECK ({} IN ({}))", field.name, allowed.join(", "));
    }
    sql
}

/// One `CREATE INDEX IF NOT EXISTS` per indexed field, in declaration
/// order. Each ends in a newline. The syntax is the same in both
/// dialects, so there is nothing to switch on.
fn create_index_sql(table: &TableDef) -> Vec<String> {
    table
        .fields
        .iter()
        .filter(|field| needs_own_index(table, field))
        .map(|field| {
            format!(
                "CREATE INDEX IF NOT EXISTS {} ON {} ({});\n",
                index_name(&table.name, &field.name),
                table.name,
                field.name
            )
        })
        .collect()
}

/// A SQL string literal: single quotes, with an embedded quote doubled.
fn quoted(text: &str) -> String {
    format!("'{}'", text.replace('\'', "''"))
}

/// A default rendered for `dialect`. The value was already checked
/// against `kind` by [`Schema::validate`], so an unexpected shape here
/// falls back to the quoted JSON form rather than inventing a literal.
fn literal(kind: &FieldKind, value: &Value, dialect: SqlDialect) -> String {
    match kind {
        FieldKind::Boolean => match (value.as_bool(), dialect) {
            (Some(true), SqlDialect::Sqlite) => "1".to_owned(),
            (Some(false), SqlDialect::Sqlite) => "0".to_owned(),
            (Some(true), SqlDialect::Postgres) => "TRUE".to_owned(),
            (Some(false), SqlDialect::Postgres) => "FALSE".to_owned(),
            (None, _) => quoted(&value.to_string()),
        },
        FieldKind::Integer { .. } => crate::value::integral(value)
            .map_or_else(|| quoted(&value.to_string()), |integer| integer.to_string()),
        FieldKind::Real { .. } => value.as_f64().map_or_else(
            || quoted(&value.to_string()),
            |number| {
                if number.is_finite() && number.fract() == 0.0 {
                    format!("{number:.1}")
                } else {
                    number.to_string()
                }
            },
        ),
        FieldKind::Json => quoted(&value.to_string()),
        FieldKind::Text { .. }
        | FieldKind::Timestamp
        | FieldKind::Uuid
        | FieldKind::Enum { .. } => value
            .as_str()
            .map_or_else(|| quoted(&value.to_string()), quoted),
    }
}