use askama::Template;
use axum::extract::State;
use axum::http::StatusCode;
use axum::response::{Html, IntoResponse, Response};
use serde::Deserialize;
use std::sync::Arc;
use ruprizzle_core::ir::Provider;
use super::AppState;
use crate::studio::db;
#[derive(Debug, Clone)]
pub struct ExplainNode {
pub depth: usize,
pub operation: String,
pub target: String,
pub cost: String,
pub rows: String,
pub is_scan: bool,
}
#[derive(Template)]
#[template(path = "explain/plan.html")]
pub struct ExplainPlanTemplate {
pub nodes: Vec<ExplainNode>,
pub error: Option<String>,
}
#[derive(Debug, Deserialize)]
pub struct ExplainForm {
pub sql: String,
}
pub async fn render_explain_tree(
State(state): State<Arc<AppState>>,
axum::extract::Form(form): axum::extract::Form<ExplainForm>,
) -> Response {
let sql = form.sql.trim().trim_end_matches(';').trim();
let (nodes, error) = if sql.is_empty() {
(Vec::new(), Some("No statement was submitted.".to_string()))
} else if requests_execution(sql) {
(
Vec::new(),
Some(
"Not run: EXPLAIN ANALYZE executes the statement, so Studio accepts neither ANALYZE nor a parenthesised option list. Submit the statement alone to see its plan."
.to_string(),
),
)
} else if let Some(pool) = state.pool.as_ref() {
let provider = pool.provider();
match db::fetch_dynamic_read_only(pool, &explain_sql(provider, sql)).await {
Ok((_, rows)) => (parse_plan(provider, &rows), None),
Err(e) => (Vec::new(), Some(format!("EXPLAIN failed: {e}"))),
}
} else {
(
Vec::new(),
Some(
"Studio has no database connection, so no plan could be obtained. \
Start studio with a --database-url (or DATABASE_URL)."
.to_string(),
),
)
};
let tmpl = ExplainPlanTemplate { nodes, error };
match tmpl.render() {
Ok(html) => Html(html).into_response(),
Err(e) => (
StatusCode::INTERNAL_SERVER_ERROR,
format!("Template error: {e}"),
)
.into_response(),
}
}
#[must_use]
pub fn requests_execution(sql: &str) -> bool {
let sql = sql.trim_start();
if sql.starts_with('(') {
return true;
}
let head = sql
.split(|c: char| c.is_whitespace() || c == '(')
.next()
.unwrap_or_default()
.to_ascii_uppercase();
matches!(head.as_str(), "ANALYZE" | "ANALYSE")
}
#[must_use]
pub fn explain_sql(provider: Provider, sql: &str) -> String {
match provider {
Provider::Postgres | Provider::Mysql => format!("EXPLAIN {sql}"),
Provider::Sqlite => format!("EXPLAIN QUERY PLAN {sql}"),
}
}
#[must_use]
fn parse_plan(provider: Provider, rows: &[db::TextRow]) -> Vec<ExplainNode> {
rows.iter()
.filter_map(|row| {
let line = match provider {
Provider::Sqlite => row.last(),
_ => row.first(),
}?
.as_deref()?;
Some(parse_line(line))
})
.collect()
}
fn parse_line(line: &str) -> ExplainNode {
let depth = line.len().saturating_sub(line.trim_start().len()) / 2;
let text = line.trim_start().trim_start_matches("->").trim();
let cost = extract_between(text, "cost=", ' ').unwrap_or_default();
let rows = extract_between(text, "rows=", ' ').unwrap_or_default();
let head = text.split(" (").next().unwrap_or(text).trim();
let (operation, target) = match head.split_once(" on ") {
Some((op, tgt)) => (op.trim().to_string(), tgt.trim().to_string()),
None => (head.to_string(), String::new()),
};
let upper = operation.to_ascii_uppercase();
let is_scan = upper.contains("SEQ SCAN") || upper.starts_with("SCAN");
ExplainNode {
depth,
operation,
target,
cost,
rows,
is_scan,
}
}
fn extract_between(text: &str, marker: &str, end: char) -> Option<String> {
let start = text.find(marker)? + marker.len();
let rest = text.get(start..)?;
let stop = rest.find(end).unwrap_or(rest.len());
Some(rest.get(..stop)?.trim().to_string())
}
#[cfg(test)]
mod tests {
use super::*;
#[test]
fn analyze_forms_are_recognised() {
for sql in [
"ANALYZE DELETE FROM users",
"analyse delete from users",
" (ANALYZE true) DELETE FROM users",
"(FORMAT JSON) SELECT 1",
] {
assert!(requests_execution(sql), "{sql}");
}
for sql in ["SELECT 1", "SELECT analyze FROM t", "DELETE FROM users"] {
assert!(!requests_execution(sql), "{sql}");
}
}
#[test]
fn plan_statement_never_analyzes() {
assert_eq!(
explain_sql(Provider::Postgres, "SELECT 1"),
"EXPLAIN SELECT 1"
);
assert_eq!(
explain_sql(Provider::Sqlite, "SELECT 1"),
"EXPLAIN QUERY PLAN SELECT 1"
);
assert!(!explain_sql(Provider::Postgres, "DELETE FROM users").contains("ANALYZE"));
}
#[test]
fn postgres_plan_lines_keep_their_real_numbers() {
let node = parse_line(" -> Seq Scan on posts (cost=0.00..4.50 rows=10 width=8)");
assert_eq!(node.operation, "Seq Scan");
assert_eq!(node.target, "posts");
assert_eq!(node.cost, "0.00..4.50");
assert_eq!(node.rows, "10");
assert!(node.is_scan);
}
#[test]
fn a_line_without_costs_reports_no_costs_rather_than_invented_ones() {
let node = parse_line("SEARCH users USING INDEX ix_email (email=?)");
assert_eq!(
node.cost, "",
"no cost in the plan means no cost on the page"
);
assert_eq!(node.rows, "");
assert!(!node.is_scan, "an indexed search is not a full scan");
assert!(parse_line("SCAN users").is_scan);
}
}