mariadb-cli 0.1.1

One-shot agent-native command line client for MariaDB servers
Documentation
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
//! What a statement would do, asked two different ways.
//!
//! # The server's plan, which never runs the statement
//!
//! [`explain`] asks the server how it would answer, and the statement itself is
//! never executed. Asking for the plan of a statement that removes rows removes
//! nothing, which is what makes the plan safe to ask for on any statement.
//!
//! # The measured plan, which does run it
//!
//! [`measure`] asks the server for the same plan with real numbers instead of
//! estimated ones, and the server produces those numbers by executing the
//! statement. That makes it a change in every sense that matters: it is
//! confirmed like one, it obeys the ceiling like one, and it is refused without
//! confirmation like one. Treating it as a plan because it is spelled like one
//! would let a removal through under the name of an investigation.
//!
//! # This tool's own prediction
//!
//! [`estimate`] answers the same question without asking the server anything:
//! it reads the statement's own restriction and derives the count that carries
//! it. That prediction is what the ceiling on affected rows is checked against
//! before the change is sent, and what a simulation reports back.

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

/// The table one change acts on.
#[derive(Debug, Clone, PartialEq, Eq)]
pub struct Target {
    /// The database holding it, when the statement named one.
    pub database: Option<String>,
    /// The table itself.
    pub table: String,
}

/// How many rows a change is expected to touch, decided before it is sent.
#[derive(Debug, Clone, PartialEq, Eq)]
pub enum Estimate {
    /// The statement itself says how many rows it carries.
    Known(u64),
    /// A count derived from the statement's own restriction, to be run first.
    Derived(String),
    /// No count can be derived, and the reason is named rather than assumed.
    Unavailable(&'static str),
}

/// The table a change acts on, when the statement names exactly one.
#[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())),
            // A destination that is produced rather than stored names no table
            // whose rows a ceiling could be checked against.
            _ => None,
        },
        _ => None,
    }
}

/// Reads a target out of one table reference, when it is a plain named table.
fn factor(relation: &TableFactor) -> Option<Target> {
    match relation {
        TableFactor::Table { name, .. } => Some(split_name(&name.to_string())),
        _ => None,
    }
}

/// Splits a possibly qualified name into its database and its table.
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(),
        },
    )
}

/// The restriction a change carries, when it carries one.
#[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,
    }
}

/// Predicts how many rows a change would touch, from the statement alone.
#[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"),
    }
}

/// Builds the count that carries the same restriction as the change.
///
/// The restriction travels unchanged, so the count counts exactly the rows the
/// change would reach and never a wider set.
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}")),
    )
}

/// Counts the rows an insert carries in its own text, when it carries them.
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"),
    }
}

/// The values that belong to the derived count, taken from the change's own.
///
/// A change binds the values of its assignments before the values of its
/// restriction, and the derived count carries only the restriction. Taking the
/// trailing values is therefore exactly the restriction's own, in order.
#[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)..]
}

/// The gap left when the ceiling could not be applied at all.
///
/// A ceiling silently skipped is worse than one that was never asked for, so
/// the answer says which rows it could not count and why.
#[must_use]
pub fn undecidable_gap(reason: &str) -> Gap {
    Gap::new(
        "affected_row_ceiling",
        "not_derivable",
        format!("the ceiling was not applied because {reason}"),
    )
}

/// Builds the request for the server's own plan of a statement.
///
/// The statement is wrapped rather than rewritten, so every byte the caller
/// wrote reaches the server unchanged.
///
/// # Errors
///
/// Returns a validation failure when the caller's text already asks for a plan
/// or for a measurement, because wrapping it again would ask a different
/// question than the one the caller wrote.
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()))
}

/// Builds the request for the plan with measured rather than estimated numbers.
///
/// # Errors
///
/// Returns a validation failure when the caller's text already asks for a plan
/// or for a measurement.
pub fn measure(gated: &Gated) -> Result<String> {
    refuse_nested_plan(gated)?;
    Ok(format!("ANALYZE {}", gated.sql()))
}

/// Refuses a text that already asks for a plan or a measurement.
///
/// # Errors
///
/// Returns a validation failure naming what the text already asks for.
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"))
}

/// The request for the plan of a statement running on another connection.
///
/// The identifier travels as a bound value, so a caller who took it from a
/// listing cannot turn it into anything else.
#[must_use]
pub fn running(connection_id: u64) -> (String, Vec<String>) {
    (
        "SHOW EXPLAIN FOR ?".to_owned(),
        vec![connection_id.to_string()],
    )
}

/// The gap declared when the server offers no reasoning for its choice.
#[must_use]
pub fn absent_trace() -> Gap {
    Gap::new(
        "optimiser_trace",
        "unsupported",
        "this server does not offer the reasoning behind its choice of plan",
    )
}

/// Refuses a statement asked for a plan that this tool cannot plan.
///
/// # Errors
///
/// Returns a validation failure when the statement has no shape a plan applies
/// to.
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()
    }

    /// The ceiling is checked from a count carrying the statement's own
    /// restriction, so it counts the rows the change would reach and no others.
    #[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");
    }

    /// A change binds its assignments before its restriction, so the derived
    /// count must take the trailing values and not the leading ones.
    #[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)
        );
    }

    /// A count that cannot be derived is declared, never assumed to be zero.
    #[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"
        );
    }

    /// Asking for a plan must never execute the statement, so the request is
    /// built around the caller's text and the text itself is untouched.
    #[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"
        );
    }

    /// A measurement runs the statement, so it is built from the word that
    /// says so and is treated as a change everywhere else.
    #[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());
    }

    /// A connection identifier is a value the caller took from a listing, so it
    /// travels bound and never spliced into the request.
    #[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");
    }
}