use super::kinds::Kind;
use crate::output::envelope::Row;
use indexmap::IndexMap;
use serde_json::{json, Value};
#[derive(Debug, Clone, PartialEq, Eq)]
pub struct IndexShape {
pub database: String,
pub table: String,
pub name: String,
pub columns: Vec<String>,
pub unique: bool,
}
impl IndexShape {
#[must_use]
pub fn leads_with(&self, columns: &[String]) -> bool {
!columns.is_empty()
&& self.columns.len() >= columns.len()
&& self.columns[..columns.len()] == *columns
}
#[must_use]
pub fn owner(&self) -> String {
format!("{}.{}", self.database, self.table)
}
}
#[derive(Debug, Clone, PartialEq, Eq)]
pub struct KeyShape {
pub database: String,
pub table: String,
pub name: String,
pub columns: Vec<String>,
pub references: String,
}
#[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()
}
#[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()
}
#[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()
}
#[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
}
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() {
return !narrower.unique;
}
rank(wider) < rank(narrower)
}
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())
}
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}")
}
fn text(row: &Row, key: &str) -> Option<String> {
row.get(key).and_then(Value::as_str).map(ToOwned::to_owned)
}
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,
}
}
#[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,
}
}
#[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");
}
#[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(&[]));
}
#[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"
);
}
#[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
);
}
#[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"]));
}
#[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"));
}
#[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());
}
#[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"));
}
#[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"));
}
#[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());
}
#[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());
}
#[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());
}
#[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!([]));
}
}