ruprizzle-cli 1.6.0

Command-line interface for the ruprizzle ORM
Documentation
//! Query execution plan handler.
//!
//! The plan is whatever the database returns for the submitted statement. It used
//! to be a hardcoded three-node Postgres plan with invented costs that did not even
//! read the query — a fabricated diagnostic, which is worse than none.

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,
}

/// The plan, rendered as a fragment for the sandbox's result panel.
///
/// This used to extend the page shell, so htmx swapped a second sidebar and
/// navbar into the sandbox's result card.
#[derive(Template)]
#[template(path = "explain/plan.html")]
pub struct ExplainPlanTemplate {
    pub nodes: Vec<ExplainNode>,
    /// Set when no plan could be obtained. Suppresses the tree.
    pub error: Option<String>,
}

#[derive(Debug, Deserialize)]
pub struct ExplainForm {
    pub sql: String,
}

/// Runs `EXPLAIN` for the submitted statement and renders the plan.
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();
        // A plan is always a read, even with --allow-writes, so the database is
        // told to refuse writes whatever the text says.
        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(),
    }
}

/// Whether `sql` would turn the `EXPLAIN` prefix into `EXPLAIN ANALYZE`, which
/// executes the statement on Postgres and `MySQL`.
///
/// Covers `ANALYZE`, Postgres's `ANALYSE` spelling, and a parenthesised option
/// list such as `(ANALYZE true)`. A form that slips past this (for example behind
/// a comment) still runs inside a read-only transaction, so it cannot write.
#[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")
}

/// Prefixes `sql` with the provider's plan statement.
///
/// `ANALYZE` is never used: it would execute the statement, which the user did not
/// ask for and which is not safe for a mutation.
#[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}"),
    }
}

/// Turns the driver's plan rows into tree nodes.
///
/// Nothing here invents a value: `cost` and `rows` are only filled in when the plan
/// text actually carries them, and are left empty otherwise.
#[must_use]
fn parse_plan(provider: Provider, rows: &[db::TextRow]) -> Vec<ExplainNode> {
    rows.iter()
        .filter_map(|row| {
            // Postgres returns one text column; SQLite's EXPLAIN QUERY PLAN returns
            // (id, parent, notused, detail); MySQL returns a wide tabular 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();

    // The operation is the text up to the first parenthesised annotation.
    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()),
    };

    // A full table scan: Postgres calls it `Seq Scan`, SQLite's query plan opens
    // the line with `SCAN` (an indexed lookup is `SEARCH ... USING INDEX`).
    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,
    }
}

/// Extracts the text between `marker` and the next `end` character.
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);
    }
}