zippa-db 0.1.1

//! A table's own definition: columns, indexes, and foreign keys.
//!
//! Mirrors the shape of [`Connection::row_key`](super::connection::Connection::row_key):
//! one entry point dispatching per-engine to three sibling `*_sql` functions
//! in `postgres.rs`/`mysql.rs`/`sqlite.rs`, run through the same
//! `run_query`/`fetch_all` path everything else uses.

use anyhow::Result;

use super::config::Engine;
use super::connection::{Connection, DatabaseObject};
use super::query::{Cell, QueryResult};
use super::{mysql, postgres, sqlite};

/// One column of a table, as the server reports it.
#[derive(Debug, Clone, PartialEq, Eq)]
pub struct ColumnDef {
    pub name: String,
    /// Driver-reported, e.g. `character varying(255)`.
    pub type_name: String,
    pub nullable: bool,
    /// Raw expression text, shown verbatim.
    pub default: Option<String>,
    /// Cross-referenced from the same dedicated primary key read
    /// [`Connection::row_key`] uses, rather than [`TableSchema::indexes`]:
    /// SQLite's common single-column `INTEGER PRIMARY KEY` is the `rowid`
    /// itself and has no backing index to read it from.
    pub is_primary_key: bool,
    /// Empty (the default) on Postgres and SQLite, which change one property
    /// of a column in its own statement. MySQL restates the whole column for
    /// any change (`MODIFY`/`CHANGE COLUMN`), so this is what an edit's
    /// generated statement has to fold back in rather than silently drop.
    pub mysql_extra: MySqlColumnExtra,
}

/// The parts of a MySQL column `type_name`/`nullable`/`default` cannot
/// express, read from `information_schema.columns` alongside them.
#[derive(Debug, Clone, Default, PartialEq, Eq)]
pub struct MySqlColumnExtra {
    pub auto_increment: bool,
    /// A `TIMESTAMP`/`DATETIME` column's own `ON UPDATE CURRENT_TIMESTAMP`.
    pub on_update_current_timestamp: bool,
    /// Also pins the character set: a collation belongs to exactly one.
    pub collation: Option<String>,
    pub comment: Option<String>,
    /// A generated column's expression. Present means the column is not a
    /// stored value at all, so restating it safely is out of scope for the
    /// schema editor; an edit to it is refused instead of risking a wrong
    /// restatement of `GENERATED ALWAYS AS (...) [VIRTUAL | STORED]`.
    pub generation_expression: Option<String>,
}

/// One index, in the order its columns are searched.
#[derive(Debug, Clone, PartialEq, Eq)]
pub struct IndexDef {
    pub name: String,
    pub columns: Vec<String>,
    pub unique: bool,
    pub is_primary_key: bool,
}

/// One foreign key, local columns to referenced columns in matching order.
#[derive(Debug, Clone, PartialEq, Eq)]
pub struct ForeignKeyDef {
    /// SQLite reports no name for a foreign key; one is synthesized.
    pub name: String,
    pub columns: Vec<String>,
    pub referenced_schema: Option<String>,
    pub referenced_table: String,
    pub referenced_columns: Vec<String>,
    pub on_delete: ReferentialAction,
    pub on_update: ReferentialAction,
}

#[derive(Debug, Clone, Copy, PartialEq, Eq)]
pub enum ReferentialAction {
    NoAction,
    Restrict,
    Cascade,
    SetNull,
    SetDefault,
}

impl ReferentialAction {
    /// Parse the text every engine reports this as (`CASCADE`, `SET NULL`, ...).
    fn parse(action: &str) -> Self {
        match action.to_ascii_uppercase().as_str() {
            "CASCADE" => Self::Cascade,
            "SET NULL" => Self::SetNull,
            "SET DEFAULT" => Self::SetDefault,
            "RESTRICT" => Self::Restrict,
            _ => Self::NoAction,
        }
    }

    /// As it reads in a generated `ON DELETE`/`ON UPDATE` clause.
    pub fn label(self) -> &'static str {
        match self {
            Self::NoAction => "NO ACTION",
            Self::Restrict => "RESTRICT",
            Self::Cascade => "CASCADE",
            Self::SetNull => "SET NULL",
            Self::SetDefault => "SET DEFAULT",
        }
    }
}

#[derive(Debug, Clone, Default, PartialEq, Eq)]
pub struct TableSchema {
    pub columns: Vec<ColumnDef>,
    pub indexes: Vec<IndexDef>,
    pub foreign_keys: Vec<ForeignKeyDef>,
}

/// One stored `CREATE` statement on a SQLite table, as `sqlite_master` holds
/// it. Kept so a rebuild can put the object back exactly as it was.
#[derive(Debug, Clone, PartialEq, Eq)]
pub struct StoredDefinition {
    pub name: String,
    pub sql: String,
}

/// What a SQLite table rebuild reads before it starts: the table's own
/// `CREATE TABLE` text, so the rebuild can refuse when it holds something the
/// procedure would not carry, plus the stored text of the explicit indexes,
/// the triggers, and the views that have to go back on top of the new table.
///
/// SQLite's own `ALTER TABLE` cannot express a column's type, nullability, or
/// default, or a primary or foreign key, so those are done by copying the
/// table into a new one with the edited definition and swapping it in —
/// sqlite.org's "Making Other Kinds Of Table Schema Changes". Everything the
/// copy does not restate has to be read back here and replayed.
#[derive(Debug, Clone, PartialEq, Eq, Default)]
pub struct RebuildSource {
    pub table_sql: String,
    /// Explicit `CREATE INDEX` statements (`sqlite_master.sql` is not null);
    /// the auto-indexes behind `UNIQUE`/`PRIMARY KEY` carry no SQL and are
    /// rebuilt from the table itself.
    pub indexes: Vec<StoredDefinition>,
    pub triggers: Vec<StoredDefinition>,
    /// Every view; the generator keeps the ones that mention the table.
    pub views: Vec<StoredDefinition>,
}

impl Connection {
    /// The structure of `object`: its columns, indexes, and foreign keys.
    ///
    /// Three round trips, like `row_key` is one more query on top of the
    /// object list — table metadata is small, so nothing here is worth
    /// running concurrently.
    pub async fn table_schema(&self, object: &DatabaseObject) -> Result<TableSchema> {
        let engine = self.config.engine;
        // `objects` drops the schema when it is the default one, same as
        // `row_key` — an unqualified Postgres table is in `public`.
        let schema = object.schema.as_deref().unwrap_or("public");
        let table = object.name.as_str();

        let (columns_sql, indexes_sql, foreign_keys_sql, primary_key_sql) = match engine {
            Engine::Postgres => (
                postgres::columns_sql(schema, table),
                postgres::indexes_sql(schema, table),
                postgres::foreign_keys_sql(schema, table),
                postgres::primary_key_sql(schema, table),
            ),
            Engine::MySql => (
                mysql::columns_sql(table),
                mysql::indexes_sql(table),
                mysql::foreign_keys_sql(table),
                mysql::primary_key_sql(table),
            ),
            Engine::Sqlite => (
                sqlite::columns_sql(table),
                sqlite::indexes_sql(table),
                sqlite::foreign_keys_sql(table),
                sqlite::primary_key_sql(table),
            ),
        };

        let columns_result = self.run_query(&columns_sql).await?;
        let indexes_result = self.run_query(&indexes_sql).await?;
        let foreign_keys_result = self.run_query(&foreign_keys_sql).await?;
        let primary_key_result = self.run_query(&primary_key_sql).await?;

        let pk_columns: Vec<String> = primary_key_result
            .rows
            .iter()
            .filter_map(|row| row.first().cloned().flatten())
            .collect();

        let mut columns = parse_columns(&columns_result);
        for column in &mut columns {
            column.is_primary_key = pk_columns.contains(&column.name);
        }

        Ok(TableSchema {
            columns,
            indexes: parse_indexes(&indexes_result),
            foreign_keys: parse_foreign_keys(&foreign_keys_result),
        })
    }

    /// The stored SQL a SQLite rebuild has to work from: the table's own
    /// `CREATE TABLE` text, its explicit indexes, its triggers, and every
    /// view. Only SQLite needs this; the other engines express all of these
    /// edits directly.
    pub async fn rebuild_source(&self, object: &DatabaseObject) -> Result<RebuildSource> {
        if self.config.engine != Engine::Sqlite {
            anyhow::bail!("a rebuild source is only read on SQLite");
        }

        let result = self.run_query(&sqlite::master_sql(&object.name)).await?;
        let mut source = RebuildSource::default();
        for row in &result.rows {
            let kind = text(row, 0);
            let name = text(row, 1);
            let sql = text(row, 2);
            let definition = || StoredDefinition {
                name: name.clone(),
                sql: sql.clone(),
            };
            match kind.as_str() {
                "table" => source.table_sql = sql,
                "index" => source.indexes.push(definition()),
                "trigger" => source.triggers.push(definition()),
                "view" => source.views.push(definition()),
                _ => {}
            }
        }
        Ok(source)
    }

    /// Just `object`'s foreign keys — one round trip, for callers that don't
    /// need the rest of [`table_schema`](Self::table_schema).
    pub async fn foreign_keys(&self, object: &DatabaseObject) -> Result<Vec<ForeignKeyDef>> {
        let engine = self.config.engine;
        let schema = object.schema.as_deref().unwrap_or("public");
        let table = object.name.as_str();

        let sql = match engine {
            Engine::Postgres => postgres::foreign_keys_sql(schema, table),
            Engine::MySql => mysql::foreign_keys_sql(table),
            Engine::Sqlite => sqlite::foreign_keys_sql(table),
        };

        let result = self.run_query(&sql).await?;
        Ok(parse_foreign_keys(&result))
    }
}

fn text(row: &[Cell], index: usize) -> String {
    text_opt(row, index).unwrap_or_default()
}

fn text_opt(row: &[Cell], index: usize) -> Option<String> {
    row.get(index).cloned().flatten()
}

/// A boolean expression's cell, which every engine's `cell()` renders as `1`
/// or `0` — a real boolean on Postgres, an integer comparison elsewhere.
fn flag(row: &[Cell], index: usize) -> bool {
    text_opt(row, index).as_deref() == Some("1")
}

/// A comma-joined column list back into its parts.
fn split_columns(joined: &str) -> Vec<String> {
    joined
        .split(',')
        .filter(|part| !part.is_empty())
        .map(str::to_string)
        .collect()
}

fn parse_columns(result: &QueryResult) -> Vec<ColumnDef> {
    result
        .rows
        .iter()
        .map(|row| {
            // Columns 4-7 are MySQL-only (`mysql::columns_sql`); Postgres and
            // SQLite's queries have no such columns, so `row.get` comes back
            // `None` for them and every field below stays at its default.
            let extra = text(row, 4).to_ascii_lowercase();
            ColumnDef {
                name: text(row, 0),
                type_name: text(row, 1),
                nullable: flag(row, 2),
                default: text_opt(row, 3),
                is_primary_key: false,
                mysql_extra: MySqlColumnExtra {
                    auto_increment: extra.contains("auto_increment"),
                    on_update_current_timestamp: extra.contains("on update current_timestamp"),
                    collation: text_opt(row, 5),
                    comment: text_opt(row, 6).filter(|comment| !comment.is_empty()),
                    generation_expression: text_opt(row, 7)
                        .filter(|expression| !expression.is_empty()),
                },
            }
        })
        .collect()
}

fn parse_indexes(result: &QueryResult) -> Vec<IndexDef> {
    result
        .rows
        .iter()
        .map(|row| IndexDef {
            name: text(row, 0),
            columns: split_columns(&text(row, 1)),
            unique: flag(row, 2),
            is_primary_key: flag(row, 3),
        })
        .collect()
}

fn parse_foreign_keys(result: &QueryResult) -> Vec<ForeignKeyDef> {
    result
        .rows
        .iter()
        .enumerate()
        .map(|(position, row)| ForeignKeyDef {
            name: text_opt(row, 0).unwrap_or_else(|| format!("fk_{}", position + 1)),
            columns: split_columns(&text(row, 1)),
            referenced_schema: text_opt(row, 2),
            referenced_table: text(row, 3),
            referenced_columns: split_columns(&text(row, 4)),
            on_delete: ReferentialAction::parse(&text(row, 5)),
            on_update: ReferentialAction::parse(&text(row, 6)),
        })
        .collect()
}