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
//! Structural advice, computed only when it was asked for.
//!
//! Working out that a link has no supporting index, or that an index is
//! already contained in another, means comparing every index of a table
//! against every other. That cost buys nothing for a caller who only wanted
//! the listing, so the listings leave clean and the advice appears only when
//! it was asked for: by the mark for one invocation, or by
//! `schema.index_advice` for every one of them on this host.
//!
//! Everything here works on rows already read, so the rules are provable
//! without a server: they are rules about shapes, not about the network.

use super::kinds::Kind;
use crate::output::envelope::Row;
use indexmap::IndexMap;
use serde_json::{json, Value};

/// One index, gathered from the one row the catalog gives per column.
#[derive(Debug, Clone, PartialEq, Eq)]
pub struct IndexShape {
    /// Database holding the indexed table.
    pub database: String,
    /// The indexed table.
    pub table: String,
    /// The index's own name.
    pub name: String,
    /// The indexed columns, in the order the index uses them.
    pub columns: Vec<String>,
    /// Whether the index forbids a repeated value.
    pub unique: bool,
}

impl IndexShape {
    /// Whether this index can serve a lookup on these leading columns.
    ///
    /// An index serves a lookup when the wanted columns are its first columns
    /// in that order; a column in the middle of the index is of no use on its
    /// own, which is exactly why a missing supporting index is worth naming.
    #[must_use]
    pub fn leads_with(&self, columns: &[String]) -> bool {
        !columns.is_empty()
            && self.columns.len() >= columns.len()
            && self.columns[..columns.len()] == *columns
    }

    /// The full name of the table this index belongs to.
    #[must_use]
    pub fn owner(&self) -> String {
        format!("{}.{}", self.database, self.table)
    }
}

/// One link between tables, gathered from the one row per column.
#[derive(Debug, Clone, PartialEq, Eq)]
pub struct KeyShape {
    /// Database holding the referring table.
    pub database: String,
    /// The referring table.
    pub table: String,
    /// The link's own name.
    pub name: String,
    /// The referring columns, in the order the link uses them.
    pub columns: Vec<String>,
    /// The table referred to, as a full name.
    pub references: String,
}

/// Gathers the index rows into one shape per index.
///
/// The catalog answers with one row per indexed column, so column order comes
/// from the order the rows arrive in, which the reading already fixes.
#[must_use]
pub fn index_shapes(rows: &[Row]) -> Vec<IndexShape> {
    let mut gathered: IndexMap<(String, String, String), IndexShape> = IndexMap::new();
    for row in rows {
        let (Some(database), Some(table), Some(name)) =
            (text(row, "database"), text(row, "table"), text(row, "name"))
        else {
            continue;
        };
        let entry = gathered
            .entry((database.clone(), table.clone(), name.clone()))
            .or_insert_with(|| IndexShape {
                database,
                table,
                name,
                columns: Vec::new(),
                unique: flag(row, "unique"),
            });
        if let Some(column) = text(row, "column") {
            entry.columns.push(column);
        }
    }
    gathered.into_values().collect()
}

/// Gathers the link rows into one shape per link.
#[must_use]
pub fn key_shapes(rows: &[Row]) -> Vec<KeyShape> {
    let mut gathered: IndexMap<(String, String, String), KeyShape> = IndexMap::new();
    for row in rows {
        let (Some(database), Some(table), Some(name)) =
            (text(row, "database"), text(row, "table"), text(row, "name"))
        else {
            continue;
        };
        let entry = gathered
            .entry((database.clone(), table.clone(), name.clone()))
            .or_insert_with(|| KeyShape {
                database,
                table,
                name,
                columns: Vec::new(),
                references: referenced(row),
            });
        if let Some(column) = text(row, "column") {
            entry.columns.push(column);
        }
    }
    gathered.into_values().collect()
}

/// The links no index supports, in the order the links were read.
///
/// A link without a supporting index makes the server read the whole referring
/// table whenever the referred row changes, and holds locks while it does.
#[must_use]
pub fn links_without_support(keys: &[KeyShape], indexes: &[IndexShape]) -> Vec<Value> {
    keys.iter()
        .filter(|key| {
            !indexes.iter().any(|index| {
                index.database == key.database
                    && index.table == key.table
                    && index.leads_with(&key.columns)
            })
        })
        .map(|key| {
            json!({
                "database": key.database,
                "table": key.table,
                "key": key.name,
                "columns": key.columns,
                "references": key.references,
                "finding": "no index leads with these columns",
            })
        })
        .collect()
}

/// The indexes another index already covers, in the order they were read.
///
/// An index whose columns are the first columns of another index answers no
/// lookup the wider one cannot, and still costs space and write time.
#[must_use]
pub fn covered_indexes(indexes: &[IndexShape]) -> Vec<Value> {
    let mut found = Vec::new();
    for candidate in indexes {
        if candidate.columns.is_empty() {
            continue;
        }
        let Some(wider) = indexes.iter().find(|other| covers(other, candidate)) else {
            continue;
        };
        found.push(json!({
            "database": candidate.database,
            "table": candidate.table,
            "index": candidate.name,
            "columns": candidate.columns,
            "covered_by": wider.name,
            "covering_columns": wider.columns,
            "finding": "another index already leads with these columns",
        }));
    }
    found
}

/// Whether the wider index makes the narrower one unnecessary.
///
/// Two indexes over the very same columns cover each other, which would report
/// both and leave the caller with nothing to remove. The tie is settled in a
/// fixed way, so exactly one of the pair is named and the same pair always
/// yields the same answer.
fn covers(wider: &IndexShape, narrower: &IndexShape) -> bool {
    if wider.owner() != narrower.owner() || wider.name == narrower.name {
        return false;
    }
    if !wider.leads_with(&narrower.columns) {
        return false;
    }
    if wider.columns.len() > narrower.columns.len() {
        // A unique constraint is not redundant just because a wider index
        // repeats its columns: the wider index does not enforce uniqueness.
        return !narrower.unique;
    }
    rank(wider) < rank(narrower)
}

/// The order two indexes over identical columns are settled in.
///
/// The primary key wins, then a unique index, then the earlier name, so the
/// answer never depends on the order the rows happened to arrive in.
fn rank(index: &IndexShape) -> (u8, &str) {
    let weight = if index.name == "PRIMARY" {
        0
    } else if index.unique {
        1
    } else {
        2
    };
    (weight, index.name.as_str())
}

/// The full name of the table a link points at.
fn referenced(row: &Row) -> String {
    let database = text(row, "referenced_database").unwrap_or_default();
    let table = text(row, "referenced_table").unwrap_or_default();
    format!("{database}.{table}")
}

/// One text field of a row, when it carries one.
fn text(row: &Row, key: &str) -> Option<String> {
    row.get(key).and_then(Value::as_str).map(ToOwned::to_owned)
}

/// One yes-or-no field of a row.
///
/// The catalog answers such a field as a number, so both forms are accepted
/// and neither is treated as absent.
fn flag(row: &Row, key: &str) -> bool {
    match row.get(key) {
        Some(Value::Bool(value)) => *value,
        Some(Value::Number(value)) => value.as_i64().is_some_and(|number| number != 0),
        Some(Value::String(value)) => value == "1" || value.eq_ignore_ascii_case("yes"),
        _ => false,
    }
}

/// The advice one kind of listing can give, from readings already made.
///
/// Each kind answers with its own finding and never with the other's, so a
/// caller that asked about links is not handed a report about indexes.
#[must_use]
pub fn for_kind(kind: Kind, keys: &[Row], indexes: &[Row]) -> Value {
    let shapes = index_shapes(indexes);
    match kind {
        Kind::ForeignKey => json!({
            "links_without_support": links_without_support(&key_shapes(keys), &shapes),
        }),
        _ => json!({ "covered_indexes": covered_indexes(&shapes) }),
    }
}

#[cfg(test)]
mod tests {
    use super::*;

    fn index_row(table: &str, name: &str, column: &str, unique: i64) -> Row {
        let mut row = Row::new();
        row.insert("database".to_owned(), json!("shop"));
        row.insert("table".to_owned(), json!(table));
        row.insert("name".to_owned(), json!(name));
        row.insert("column".to_owned(), json!(column));
        row.insert("unique".to_owned(), json!(unique));
        row
    }

    fn key_row(name: &str, column: &str) -> Row {
        let mut row = Row::new();
        row.insert("database".to_owned(), json!("shop"));
        row.insert("table".to_owned(), json!("orders"));
        row.insert("name".to_owned(), json!(name));
        row.insert("column".to_owned(), json!(column));
        row.insert("referenced_database".to_owned(), json!("shop"));
        row.insert("referenced_table".to_owned(), json!("customers"));
        row
    }

    fn shape(name: &str, columns: &[&str], unique: bool) -> IndexShape {
        IndexShape {
            database: "shop".to_owned(),
            table: "orders".to_owned(),
            name: name.to_owned(),
            columns: columns.iter().map(|column| (*column).to_owned()).collect(),
            unique,
        }
    }

    /// One row per indexed column becomes one index with its columns in order.
    #[test]
    fn the_rows_of_one_index_become_one_shape_in_column_order() {
        let rows = vec![
            index_row("orders", "by_customer", "customer_id", 0),
            index_row("orders", "by_customer", "placed_at", 0),
            index_row("orders", "PRIMARY", "id", 1),
        ];
        let shapes = index_shapes(&rows);
        assert_eq!(shapes.len(), 2);
        assert_eq!(shapes[0].columns, vec!["customer_id", "placed_at"]);
        assert!(!shapes[0].unique);
        assert!(shapes[1].unique, "the catalog answers the flag as a number");
    }

    /// An index serves a lookup only on its leading columns.
    #[test]
    fn an_index_serves_only_its_leading_columns() {
        let index = shape("by_pair", &["a", "b"], false);
        assert!(index.leads_with(&["a".to_owned()]));
        assert!(index.leads_with(&["a".to_owned(), "b".to_owned()]));
        assert!(!index.leads_with(&["b".to_owned()]));
        assert!(!index.leads_with(&[]));
    }

    /// A link with no index leading with its columns is named, and a link with
    /// one is not.
    #[test]
    fn a_link_without_a_supporting_index_is_named() {
        let keys = key_shapes(&[key_row("fk_customer", "customer_id")]);
        assert_eq!(links_without_support(&keys, &[]).len(), 1);
        assert!(
            links_without_support(&keys, &[shape("by_customer", &["customer_id"], false)])
                .is_empty()
        );
        assert_eq!(
            links_without_support(
                &keys,
                &[shape("by_other", &["placed_at", "customer_id"], false)]
            )
            .len(),
            1,
            "a column in the middle of an index supports nothing"
        );
    }

    /// A link over several columns is supported only by an index that leads
    /// with those columns in that order.
    #[test]
    fn a_link_over_several_columns_needs_them_in_order() {
        let keys = key_shapes(&[key_row("fk_pair", "a"), key_row("fk_pair", "b")]);
        assert_eq!(keys[0].columns, vec!["a", "b"]);
        assert!(links_without_support(&keys, &[shape("wide", &["a", "b", "c"], false)]).is_empty());
        assert_eq!(
            links_without_support(&keys, &[shape("swapped", &["b", "a"], false)]).len(),
            1
        );
    }

    /// The reported link says which table it points at, so the caller can act
    /// without a second reading.
    #[test]
    fn a_reported_link_says_what_it_points_at() {
        let keys = key_shapes(&[key_row("fk_customer", "customer_id")]);
        let found = links_without_support(&keys, &[]);
        assert_eq!(found[0]["references"], json!("shop.customers"));
        assert_eq!(found[0]["columns"], json!(["customer_id"]));
    }

    /// A narrower index inside a wider one is named, with the index that
    /// covers it.
    #[test]
    fn an_index_contained_in_another_is_named_with_its_cover() {
        let indexes = vec![
            shape("narrow", &["a"], false),
            shape("wide", &["a", "b"], false),
        ];
        let found = covered_indexes(&indexes);
        assert_eq!(found.len(), 1);
        assert_eq!(found[0]["index"], json!("narrow"));
        assert_eq!(found[0]["covered_by"], json!("wide"));
    }

    /// A unique index is not redundant merely because a wider index repeats
    /// its columns: only the unique one refuses a repeated value.
    #[test]
    fn a_unique_index_is_not_covered_by_a_merely_wider_one() {
        let indexes = vec![
            shape("only_one", &["a"], true),
            shape("wide", &["a", "b"], false),
        ];
        assert!(covered_indexes(&indexes).is_empty());
    }

    /// Two indexes over the same columns name exactly one of the pair, and the
    /// same pair always names the same one.
    #[test]
    fn two_identical_indexes_name_exactly_one_of_the_pair() {
        let indexes = vec![
            shape("copy_b", &["a"], false),
            shape("copy_a", &["a"], false),
        ];
        let found = covered_indexes(&indexes);
        assert_eq!(found.len(), 1);
        assert_eq!(found[0]["index"], json!("copy_b"));
        assert_eq!(found[0]["covered_by"], json!("copy_a"));
    }

    /// The primary key is never the one named when another index repeats it.
    #[test]
    fn the_primary_key_is_never_the_one_named() {
        let indexes = vec![
            shape("PRIMARY", &["id"], true),
            shape("also_id", &["id"], true),
        ];
        let found = covered_indexes(&indexes);
        assert_eq!(found.len(), 1);
        assert_eq!(found[0]["index"], json!("also_id"));
    }

    /// Indexes of different tables never cover each other.
    #[test]
    fn indexes_of_different_tables_never_cover_each_other() {
        let mut other = shape("narrow", &["a"], false);
        other.table = "customers".to_owned();
        assert!(covered_indexes(&[other, shape("wide", &["a", "b"], false)]).is_empty());
    }

    /// A clean table produces no advice at all, rather than an empty finding.
    #[test]
    fn a_clean_table_produces_no_advice() {
        let indexes = vec![
            shape("PRIMARY", &["id"], true),
            shape("by_date", &["placed_at"], false),
        ];
        assert!(covered_indexes(&indexes).is_empty());
        assert!(links_without_support(&[], &indexes).is_empty());
    }

    /// Advice on links names the ones no index supports, and advice on indexes
    /// names the ones another index already covers. Neither kind answers with
    /// the other's finding.
    #[test]
    fn each_kind_gives_only_its_own_advice() {
        let keys = vec![key_row("fk_customer", "customer_id")];
        let indexes = vec![index_row("orders", "PRIMARY", "id", 1)];

        let on_links = for_kind(Kind::ForeignKey, &keys, &indexes);
        assert!(on_links.get("links_without_support").is_some());
        assert!(on_links.get("covered_indexes").is_none());

        let on_indexes = for_kind(Kind::Index, &keys, &indexes);
        assert!(on_indexes.get("covered_indexes").is_some());
        assert!(on_indexes.get("links_without_support").is_none());
    }

    /// A clean structure produces an empty finding, not a missing one, so a
    /// caller that asked for advice always learns it was computed.
    #[test]
    fn a_clean_structure_produces_an_empty_finding() {
        let indexes = vec![
            index_row("orders", "PRIMARY", "id", 1),
            index_row("orders", "by_customer", "customer_id", 0),
        ];
        let found = for_kind(
            Kind::ForeignKey,
            &[key_row("fk_customer", "customer_id")],
            &indexes,
        );
        assert_eq!(found["links_without_support"], json!([]));
    }
}