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};
pub async fn as_of(
connection: &mut Conn,
table: &str,
at: &str,
named: Option<&str>,
session_database: Option<&str>,
) -> Result<Envelope> {
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,
}),
))
}
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)?;
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,
}),
))
}
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])?,
))
}
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])?,
))
}
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])?,
))
}
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)
}
#[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}"))
}
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(),
}
}
#[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"
);
}
#[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`"));
}
#[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`"));
}
#[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`)"));
}
#[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")));
}
#[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());
}
}