use super::prepare::Gated;
use crate::error::{Failure, Result};
use crate::output::envelope::Gap;
use crate::sql::classify::Classification;
use sqlparser::ast::{Expr, FromTable, Statement, TableFactor, TableObject};
#[derive(Debug, Clone, PartialEq, Eq)]
pub struct Target {
pub database: Option<String>,
pub table: String,
}
#[derive(Debug, Clone, PartialEq, Eq)]
pub enum Estimate {
Known(u64),
Derived(String),
Unavailable(&'static str),
}
#[must_use]
pub fn target_of(statement: &Statement) -> Option<Target> {
match statement {
Statement::Delete(delete) => {
let (FromTable::WithFromKeyword(tables) | FromTable::WithoutKeyword(tables)) =
&delete.from;
match tables.as_slice() {
[only] if only.joins.is_empty() => factor(&only.relation),
_ => None,
}
}
Statement::Update(update) if update.table.joins.is_empty() => {
factor(&update.table.relation)
}
Statement::Insert(insert) => match &insert.table {
TableObject::TableName(name) => Some(split_name(&name.to_string())),
_ => None,
},
_ => None,
}
}
fn factor(relation: &TableFactor) -> Option<Target> {
match relation {
TableFactor::Table { name, .. } => Some(split_name(&name.to_string())),
_ => None,
}
}
fn split_name(rendered: &str) -> Target {
let cleaned = rendered.replace('`', "");
cleaned.rsplit_once('.').map_or_else(
|| Target {
database: None,
table: cleaned.clone(),
},
|(database, table)| Target {
database: Some(database.to_owned()),
table: table.to_owned(),
},
)
}
#[must_use]
pub fn restriction_of(statement: &Statement) -> Option<&Expr> {
match statement {
Statement::Delete(delete) => delete.selection.as_ref(),
Statement::Update(update) => update.selection.as_ref(),
_ => None,
}
}
#[must_use]
pub fn estimate(statement: &Statement) -> Estimate {
match statement {
Statement::Delete(_) | Statement::Update(_) => derive_count(statement),
Statement::Insert(insert) => insert_rows(insert),
_ => Estimate::Unavailable("this statement shape carries no derivable row count"),
}
}
fn derive_count(statement: &Statement) -> Estimate {
let Some(target) = target_of(statement) else {
return Estimate::Unavailable("the change names more than one table");
};
let Some(restriction) = restriction_of(statement) else {
return Estimate::Unavailable("the change carries no restriction");
};
let quoted = match &target.database {
Some(database) => crate::sql::ident::quote_qualified(&[database, &target.table]),
None => crate::sql::ident::quote(&target.table),
};
quoted.map_or(
Estimate::Unavailable("the target name cannot be quoted safely"),
|name| Estimate::Derived(format!("SELECT COUNT(*) FROM {name} WHERE {restriction}")),
)
}
fn insert_rows(insert: &sqlparser::ast::Insert) -> Estimate {
if !insert.assignments.is_empty() {
return Estimate::Known(1);
}
let Some(source) = insert.source.as_ref() else {
return Estimate::Unavailable("the insert names no source of rows");
};
match source.body.as_ref() {
sqlparser::ast::SetExpr::Values(values) => Estimate::Known(values.rows.len() as u64),
_ => Estimate::Unavailable("the insert takes its rows from a query"),
}
}
#[must_use]
pub fn count_values<'a>(count_sql: &str, values: &'a [String]) -> &'a [String] {
let markers = count_sql.chars().filter(|c| *c == '?').count();
&values[values.len().saturating_sub(markers)..]
}
#[must_use]
pub fn undecidable_gap(reason: &str) -> Gap {
Gap::new(
"affected_row_ceiling",
"not_derivable",
format!("the ceiling was not applied because {reason}"),
)
}
pub fn explain(gated: &Gated, trace: bool) -> Result<String> {
refuse_nested_plan(gated)?;
let form = if trace { "FORMAT=JSON " } else { "" };
Ok(format!("EXPLAIN {form}{}", gated.sql()))
}
pub fn measure(gated: &Gated) -> Result<String> {
refuse_nested_plan(gated)?;
Ok(format!("ANALYZE {}", gated.sql()))
}
fn refuse_nested_plan(gated: &Gated) -> Result<()> {
if !matches!(gated.kind(), "explain" | "describe" | "analyze") {
return Ok(());
}
Err(Failure::validation(
"sql.already_a_plan",
"the statement already asks for a plan or a measurement",
)
.with_hint("pass the statement itself, without a leading plan or measurement word"))
}
#[must_use]
pub fn running(connection_id: u64) -> (String, Vec<String>) {
(
"SHOW EXPLAIN FOR ?".to_owned(),
vec![connection_id.to_string()],
)
}
#[must_use]
pub fn absent_trace() -> Gap {
Gap::new(
"optimiser_trace",
"unsupported",
"this server does not offer the reasoning behind its choice of plan",
)
}
pub fn refuse_unplannable(gated: &Gated) -> Result<()> {
if matches!(
gated.classification(),
Classification::Read | Classification::Change
) {
return Ok(());
}
Err(Failure::validation(
"sql.not_plannable",
format!(
"the server plans statements that read or change rows, and this is a {}",
gated.classification().as_str()
),
)
.with_detail("detail", gated.classification().as_str())
.with_hint("ask for the plan of a statement that reads or changes rows"))
}
#[cfg(test)]
mod tests {
use super::*;
use crate::cli::globals::RowCountMode;
use crate::exec::{prepare, Policy};
use crate::sql::ParseLimits;
use std::time::Duration;
fn policy() -> Policy {
Policy::new(
ParseLimits::default(),
100,
50,
RowCountMode::Changed,
";".to_owned(),
Duration::from_secs(30),
)
}
fn gated(sql: &str) -> Gated {
prepare::gate(sql, &[], &policy()).expect("admitted")
}
fn first(sql: &str) -> Statement {
crate::sql::parse(sql, ParseLimits::default()).expect("parses")[0].clone()
}
#[test]
fn the_derived_count_carries_the_same_restriction_as_the_change() {
let Estimate::Derived(sql) = estimate(&first("DELETE FROM pedidos WHERE pago = 0")) else {
panic!("a restricted removal must derive a count");
};
assert_eq!(sql, "SELECT COUNT(*) FROM `pedidos` WHERE pago = 0");
let Estimate::Derived(sql) = estimate(&first(
"UPDATE pedidos SET pago = 1 WHERE id > 7 AND total < 100",
)) else {
panic!("a restricted update must derive a count");
};
assert!(sql.starts_with("SELECT COUNT(*) FROM `pedidos` WHERE "));
assert!(
sql.contains("id > 7"),
"the restriction survives whole: {sql}"
);
assert!(sql.contains("total < 100"), "every part survives: {sql}");
}
#[test]
fn a_qualified_target_keeps_its_database_in_the_derived_count() {
let Estimate::Derived(sql) = estimate(&first("DELETE FROM loja.pedidos WHERE id = 1"))
else {
panic!("must derive");
};
assert_eq!(sql, "SELECT COUNT(*) FROM `loja`.`pedidos` WHERE id = 1");
}
#[test]
fn the_derived_count_binds_only_the_values_of_the_restriction() {
let values = vec!["novo".to_owned(), "7".to_owned()];
assert_eq!(
count_values("SELECT COUNT(*) FROM `t` WHERE id = ?", &values),
&["7".to_owned()]
);
assert_eq!(
count_values("SELECT COUNT(*) FROM `t` WHERE a = ? AND b = ?", &values),
values.as_slice()
);
assert!(count_values("SELECT COUNT(*) FROM `t` WHERE a = 1", &values).is_empty());
}
#[test]
fn an_insert_of_written_rows_needs_no_round_trip_to_be_counted() {
assert_eq!(
estimate(&first("INSERT INTO t (a) VALUES (1), (2), (3)")),
Estimate::Known(3)
);
assert_eq!(
estimate(&first("INSERT INTO t SET a = 1")),
Estimate::Known(1)
);
}
#[test]
fn a_count_that_cannot_be_derived_says_so_instead_of_guessing() {
assert!(matches!(
estimate(&first("INSERT INTO t (a) SELECT a FROM u")),
Estimate::Unavailable(_)
));
assert!(matches!(
estimate(&first("DELETE FROM t")),
Estimate::Unavailable(_)
));
assert_eq!(undecidable_gap("no restriction").cause, "not_derivable");
}
#[test]
fn the_target_table_is_read_from_the_tree_for_every_change_shape() {
assert_eq!(
target_of(&first("DELETE FROM loja.pedidos WHERE a = 1")),
Some(Target {
database: Some("loja".to_owned()),
table: "pedidos".to_owned()
})
);
assert_eq!(
target_of(&first("UPDATE pedidos SET a = 1")).map(|t| t.table),
Some("pedidos".to_owned())
);
assert_eq!(
target_of(&first("INSERT INTO pedidos (a) VALUES (1)")).map(|t| t.table),
Some("pedidos".to_owned())
);
assert_eq!(target_of(&first("SELECT 1")), None, "a read has no target");
assert_eq!(
target_of(&first("UPDATE t JOIN u ON u.id = t.id SET t.a = 1")),
None,
"a change over several tables names no single target"
);
}
#[test]
fn the_plan_of_a_destructive_statement_wraps_it_and_changes_nothing() {
let request = explain(&gated("DELETE FROM pedidos WHERE id = 7"), false).expect("plans");
assert_eq!(request, "EXPLAIN DELETE FROM pedidos WHERE id = 7");
assert!(
request.starts_with("EXPLAIN "),
"the statement is only ever wrapped"
);
}
#[test]
fn the_reasoning_behind_the_choice_is_asked_for_only_when_requested() {
assert_eq!(
explain(&gated("SELECT a FROM t"), false).expect("plans"),
"EXPLAIN SELECT a FROM t"
);
assert_eq!(
explain(&gated("SELECT a FROM t"), true).expect("plans"),
"EXPLAIN FORMAT=JSON SELECT a FROM t"
);
}
#[test]
fn a_measurement_is_built_from_the_word_that_runs_the_statement() {
assert_eq!(
measure(&gated("SELECT a FROM t")).expect("measures"),
"ANALYZE SELECT a FROM t"
);
}
#[test]
fn a_text_that_already_asks_for_a_plan_is_refused_rather_than_wrapped_twice() {
for sql in ["EXPLAIN SELECT a FROM t", "DESCRIBE t"] {
assert_eq!(
explain(&gated(sql), false).expect_err("must refuse").code(),
"sql.already_a_plan",
"input {sql:?}"
);
}
assert_eq!(
measure(&gated("EXPLAIN SELECT 1"))
.expect_err("must refuse")
.code(),
"sql.already_a_plan"
);
}
#[test]
fn a_statement_the_server_does_not_plan_is_refused_with_its_own_kind_named() {
let failure = refuse_unplannable(&gated("SET autocommit = 1")).expect_err("must refuse");
assert_eq!(failure.code(), "sql.not_plannable");
assert!(failure.message().contains("administration"));
}
#[test]
fn a_read_and_a_change_are_both_plannable() {
assert!(refuse_unplannable(&gated("SELECT 1")).is_ok());
assert!(refuse_unplannable(&gated("DELETE FROM t WHERE a = 1")).is_ok());
}
#[test]
fn the_identifier_of_a_running_statement_travels_as_a_bound_value() {
let (sql, values) = running(4711);
assert_eq!(sql, "SHOW EXPLAIN FOR ?");
assert_eq!(values, vec!["4711".to_owned()]);
assert!(!sql.contains("4711"), "the value is never in the text");
}
#[test]
fn a_server_without_the_reasoning_declares_the_gap_rather_than_failing() {
let gap = absent_trace();
assert_eq!(gap.object, "optimiser_trace");
assert_eq!(gap.cause, "unsupported");
}
}