mariadb-cli 0.1.1

One-shot agent-native command line client for MariaDB servers
Documentation
//! Reading a versioned table at one instant, or across an interval.
//!
//! Two refusals stand in front of every read here, and both exist to stop the
//! answer being wrong in a way the caller cannot see:
//!
//! 1. a table that keeps no history is refused, never answered with the state
//!    it happens to be in now;
//! 2. an instant older than anything the table still holds is refused, never
//!    answered with the oldest row that survives, because the nearest row is
//!    not the row that was there.
//!
//! The table name is a name and not a value, so it is quoted by the one
//! sanctioned quoting path. Every instant is a value, so it travels as a bound
//! parameter and is never written into the statement.

use super::{read_period, resolve_database, Period};
use crate::error::{Failure, Result};
use crate::output::Envelope;
use crate::serverinfo::{cell, fetch, number_of, text_of};
use crate::sql::ident;
use mysql_async::{params, Conn};
use serde_json::{json, Value};

/// Reads a versioned table as it stood at one instant.
///
/// # Errors
///
/// Returns a validation failure when the table keeps no history or the instant
/// is older than anything the table still holds, an absence failure when no
/// database is in force, and the mapped driver failure when the read is
/// refused by the server.
pub async fn as_of(
    connection: &mut Conn,
    table: &str,
    at: &str,
    named: Option<&str>,
    session_database: Option<&str>,
) -> Result<Envelope> {
    // The table may name its own database, which is the form `schema map`
    // writes, and that database wins over the one the caller named.
    let split = crate::introspect::names::split(table);
    let table = split.name.as_str();
    let database = resolve_database(split.database.as_deref().or(named), session_database)?;
    let period = read_period(connection, &database, table).await?;
    let earliest = refuse_before_history(connection, &period, at).await?;

    let rows = fetch(
        connection,
        &as_of_statement(&period)?,
        params! { "at" => at.to_owned() },
    )
    .await?;

    Ok(Envelope::new(
        "history as-of",
        json!({
            "database": database,
            "table": table,
            "at": at,
            "earliest_instant": earliest,
            "rows": rows,
        }),
    ))
}

/// Reads what changed in a versioned table across an interval.
///
/// # Errors
///
/// Returns a validation failure when the table keeps no history, when the
/// interval starts before anything the table still holds, or when it ends
/// before it starts; an absence failure when no database is in force; and the
/// mapped driver failure when the read is refused by the server.
pub async fn between(
    connection: &mut Conn,
    table: &str,
    from: &str,
    to: &str,
    named: Option<&str>,
    session_database: Option<&str>,
) -> Result<Envelope> {
    refuse_backwards_interval(from, to)?;

    // The table may name its own database, which is the form `schema map`
    // writes, and that database wins over the one the caller named.
    let split = crate::introspect::names::split(table);
    let table = split.name.as_str();
    let database = resolve_database(split.database.as_deref().or(named), session_database)?;
    let period = read_period(connection, &database, table).await?;
    let earliest = refuse_before_history(connection, &period, from).await?;

    let rows = fetch(
        connection,
        &between_statement(&period)?,
        params! { "from" => from.to_owned(), "to" => to.to_owned() },
    )
    .await?;

    Ok(Envelope::new(
        "history between",
        json!({
            "database": database,
            "table": table,
            "from": from,
            "to": to,
            "earliest_instant": earliest,
            "rows": rows,
        }),
    ))
}

/// The statement that reads the table at one instant.
///
/// The two instants that bound each row are named explicitly, because this
/// server keeps them out of a plain listing and they are exactly what makes
/// the answer auditable.
///
/// # Errors
///
/// Returns a validation failure when a name cannot be quoted safely.
pub fn as_of_statement(period: &Period) -> Result<String> {
    Ok(format!(
        "SELECT *, {start} AS valid_from, {end} AS valid_to \
         FROM {table} FOR SYSTEM_TIME AS OF :at",
        start = ident::quote(&period.start_column)?,
        end = ident::quote(&period.end_column)?,
        table = ident::quote_qualified(&[&period.database, &period.table])?,
    ))
}

/// The statement that reads what changed across an interval.
///
/// # Errors
///
/// Returns a validation failure when a name cannot be quoted safely.
pub fn between_statement(period: &Period) -> Result<String> {
    Ok(format!(
        "SELECT *, {start} AS valid_from, {end} AS valid_to \
         FROM {table} FOR SYSTEM_TIME BETWEEN :from AND :to",
        start = ident::quote(&period.start_column)?,
        end = ident::quote(&period.end_column)?,
        table = ident::quote_qualified(&[&period.database, &period.table])?,
    ))
}

/// The statement that asks whether an instant predates the kept history.
///
/// # Errors
///
/// Returns a validation failure when a name cannot be quoted safely.
pub fn coverage_statement(period: &Period) -> Result<String> {
    Ok(format!(
        "SELECT MIN({start}) AS earliest, (:at < MIN({start})) AS before_history \
         FROM {table} FOR SYSTEM_TIME ALL",
        start = ident::quote(&period.start_column)?,
        table = ident::quote_qualified(&[&period.database, &period.table])?,
    ))
}

/// Refuses an instant older than anything the table still holds.
///
/// Answering with the oldest surviving row would look like an answer and be a
/// different one, which is worse than refusing.
async fn refuse_before_history(
    connection: &mut Conn,
    period: &Period,
    instant: &str,
) -> Result<Value> {
    let rows = fetch(
        connection,
        &coverage_statement(period)?,
        params! { "at" => instant.to_owned() },
    )
    .await?;

    let Some(row) = rows.first() else {
        return Ok(Value::Null);
    };
    let earliest = cell(row, "earliest").cloned().unwrap_or(Value::Null);

    if number_of(cell(row, "before_history")).is_some_and(|before| before != 0) {
        return Err(before_history(period, instant, &text_of(Some(&earliest))));
    }
    Ok(earliest)
}

/// The refusal an instant outside the kept period produces.
#[must_use]
pub fn before_history(period: &Period, instant: &str, earliest: &str) -> Failure {
    Failure::validation(
        "validation.instant_outside_history",
        format!("`{}`.`{}`", period.database, period.table),
    )
    .with_detail(
        "detail",
        format!("{}.{}", period.database, period.table),
    )
    .with_detail("earliest", earliest.to_owned())
    .with_hint(format!(
        "the oldest instant this table still holds is {earliest}; ask for that instant or a later one"
    ))
    .with_cause(format!("the instant asked for was {instant}"))
}

/// Refuses an interval that ends before it starts.
///
/// # Errors
///
/// Returns a validation failure when the two instants are the wrong way round.
pub fn refuse_backwards_interval(from: &str, to: &str) -> Result<()> {
    if from.trim() > to.trim() {
        return Err(Failure::validation(
            "validation.interval_reversed",
            "the interval ends before it starts",
        )
        .with_hint("give --from an instant that is not later than --to"));
    }
    Ok(())
}

#[cfg(test)]
mod tests {
    use super::*;

    fn period() -> Period {
        Period {
            database: "loja".to_owned(),
            table: "vendas".to_owned(),
            start_column: "row_start".to_owned(),
            end_column: "row_end".to_owned(),
        }
    }

    /// Every name in the statement is quoted, and every instant travels as a
    /// bound parameter rather than as text inside the statement.
    #[test]
    fn names_are_quoted_and_instants_are_never_written_in() {
        let statement = as_of_statement(&period()).expect("quotable");
        assert!(statement.contains("`loja`.`vendas`"));
        assert!(statement.contains("`row_start` AS valid_from"));
        assert!(statement.contains("FOR SYSTEM_TIME AS OF :at"));
        assert!(
            !statement.contains("2026"),
            "no instant belongs in the text"
        );
    }

    /// A name carrying the delimiter cannot break out of it.
    #[test]
    fn a_name_carrying_the_delimiter_cannot_break_out() {
        let mut awkward = period();
        awkward.table = "ven`das".to_owned();
        let statement = as_of_statement(&awkward).expect("quotable");
        assert!(statement.contains("`ven``das`"));
    }

    /// The interval statement binds both ends and quotes both names.
    #[test]
    fn the_interval_statement_binds_both_ends() {
        let statement = between_statement(&period()).expect("quotable");
        assert!(statement.contains("FOR SYSTEM_TIME BETWEEN :from AND :to"));
        assert!(statement.contains("`loja`.`vendas`"));
    }

    /// The coverage question reads the whole history, or it would compare the
    /// instant against the present alone.
    #[test]
    fn the_coverage_question_reads_the_whole_history() {
        let statement = coverage_statement(&period()).expect("quotable");
        assert!(statement.contains("FOR SYSTEM_TIME ALL"));
        assert!(statement.contains("MIN(`row_start`)"));
    }

    /// An instant older than the kept history is refused, and the refusal says
    /// how far back the table actually goes.
    #[test]
    fn an_instant_older_than_the_history_is_refused_with_the_oldest_kept() {
        let failure = before_history(&period(), "2020-01-01 00:00", "2026-01-05 09:00");
        assert_eq!(failure.exit_number().code(), 3);
        assert!(failure
            .hint()
            .is_some_and(|hint| hint.contains("2026-01-05 09:00")));
        assert!(failure.causes().iter().any(|cause| cause.contains("2020")));
    }

    /// An interval the wrong way round is refused before anything is read.
    #[test]
    fn a_backwards_interval_is_refused_before_any_read() {
        let failure = refuse_backwards_interval("2026-02-01", "2026-01-01")
            .expect_err("a backwards interval must be refused");
        assert_eq!(failure.code(), "validation.interval_reversed");
        assert!(refuse_backwards_interval("2026-01-01", "2026-02-01").is_ok());
    }
}