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
//! Reading a table as it was, on the servers that keep that.
//!
//! A table either keeps its own history or it does not, and the difference
//! decides whether a question about the past can be answered at all. So the
//! listing here marks the two apart plainly, and the two reading commands
//! refuse a table that keeps no history instead of answering with the present
//! state dressed up as the past, which is the one answer that would be worse
//! than no answer.

pub mod temporal;

use crate::error::{Failure, Result};
use crate::output::{Envelope, Gap, Row};
use crate::serverinfo::{cell, fetch, text_of};
use crate::sql::ident;
use mysql_async::{params, Conn, Params};
use serde_json::{json, Value};

/// The word this server uses for a table that keeps its own history.
const VERSIONED: &str = "SYSTEM VERSIONED";

/// The listing of the tables in one database.
const LISTING: &str = "SELECT TABLE_NAME, TABLE_TYPE, TABLE_ROWS, DATA_LENGTH \
     FROM information_schema.TABLES \
     WHERE TABLE_SCHEMA = :database \
       AND TABLE_TYPE IN ('BASE TABLE', 'SYSTEM VERSIONED') \
     ORDER BY TABLE_NAME";

/// Whether one named table keeps its own history.
const KIND_OF_TABLE: &str = "SELECT TABLE_TYPE \
     FROM information_schema.TABLES \
     WHERE TABLE_SCHEMA = :database AND TABLE_NAME = :table";

/// The marker that opens the clause naming the two validity columns.
const PERIOD_CLAUSE: &str = "PERIOD FOR SYSTEM_TIME";

/// The name this server gives the opening instant when nobody named it.
///
/// A table versioned without declaring its own columns gets these two, and the
/// server keeps them out of sight: they appear in no catalogue listing and in
/// no `SHOW CREATE TABLE`. They are still selectable by name, which is the only
/// reason the reading commands can alias them at all.
const IMPLICIT_START: &str = "row_start";

/// The name this server gives the closing instant when nobody named it.
const IMPLICIT_END: &str = "row_end";

/// Where one versioned table keeps the instants that bound each row.
#[derive(Debug, Clone, PartialEq, Eq)]
pub struct Period {
    /// The database holding the table.
    pub database: String,
    /// The table itself.
    pub table: String,
    /// The column carrying the instant a row became valid.
    pub start_column: String,
    /// The column carrying the instant a row stopped being valid.
    pub end_column: String,
}

/// Lists the tables of a database, marking which of them keep a history.
///
/// # Errors
///
/// Returns an absence failure when no database is named and the destination
/// has none in force, or when the named database does not exist; and the
/// mapped driver failure when the structure cannot be read.
pub async fn tables(
    connection: &mut Conn,
    named: Option<&str>,
    session_database: Option<&str>,
) -> Result<Envelope> {
    let database = resolve_database(named, session_database)?;
    confirm_database_exists(connection, &database).await?;

    let source = fetch(
        connection,
        LISTING,
        params! { "database" => database.clone() },
    )
    .await?;

    let mut rows = Vec::with_capacity(source.len());
    let mut gaps = Vec::new();
    for row in &source {
        rows.push(describe(connection, &database, row, &mut gaps).await);
    }

    let mut envelope = Envelope::new(
        "history tables",
        json!({ "database": database, "rows": rows }),
    );
    for gap in gaps {
        envelope = envelope.with_gap(gap);
    }
    Ok(envelope)
}

/// Describes one listed table, reaching for its earliest instant when it keeps
/// one.
async fn describe(connection: &mut Conn, database: &str, row: &Row, gaps: &mut Vec<Gap>) -> Row {
    let name = text_of(cell(row, "TABLE_NAME"));
    let versioned = text_of(cell(row, "TABLE_TYPE")).eq_ignore_ascii_case(VERSIONED);

    let earliest = if versioned {
        match earliest_instant(connection, database, &name).await {
            Ok(value) => value,
            Err(failure) => {
                gaps.push(Gap::new(
                    name.clone(),
                    "earliest_instant_unreadable",
                    failure.message().to_owned(),
                ));
                Value::Null
            }
        }
    } else {
        Value::Null
    };

    let mut shaped = Row::new();
    // The subject under `name`, scoped by `database`, which is the shape every
    // other listing in this product uses. It used to be a bare name under
    // `table` with no database beside it, so a caller reading this listing got
    // a name in a key that names an owner elsewhere, and no way to tell which
    // database it had come from.
    shaped.insert("database".to_owned(), json!(database));
    shaped.insert("name".to_owned(), json!(name));
    shaped.insert("versioned".to_owned(), json!(versioned));
    shaped.insert("earliest_instant".to_owned(), earliest);
    shaped.insert(
        "rows".to_owned(),
        cell(row, "TABLE_ROWS").cloned().unwrap_or(Value::Null),
    );
    shaped.insert(
        "bytes".to_owned(),
        cell(row, "DATA_LENGTH").cloned().unwrap_or(Value::Null),
    );
    shaped
}

/// The oldest instant one versioned table still holds.
async fn earliest_instant(connection: &mut Conn, database: &str, table: &str) -> Result<Value> {
    let period = read_period(connection, database, table).await?;
    let sql = format!(
        "SELECT MIN({start}) FROM {qualified} FOR SYSTEM_TIME ALL",
        start = ident::quote(&period.start_column)?,
        qualified = ident::quote_qualified(&[database, table])?,
    );
    let rows = fetch(connection, &sql, Params::Empty).await?;
    Ok(rows
        .first()
        .and_then(|row| row.values().next())
        .cloned()
        .unwrap_or(Value::Null))
}

/// Reads where a versioned table keeps its instants.
///
/// # Errors
///
/// Returns a validation failure when the table keeps no history, so the two
/// reading commands never fall through to the present state.
pub async fn read_period(connection: &mut Conn, database: &str, table: &str) -> Result<Period> {
    let kind = fetch(
        connection,
        KIND_OF_TABLE,
        params! { "database" => database.to_owned(), "table" => table.to_owned() },
    )
    .await?;
    let keeps_history = kind
        .first()
        .is_some_and(|row| text_of(cell(row, "TABLE_TYPE")).eq_ignore_ascii_case(VERSIONED));
    if !keeps_history {
        return Err(not_versioned(database, table));
    }

    let ddl = definition_of(connection, database, table).await?;
    let (start_column, end_column) = period_columns(ddl.as_deref());
    Ok(Period {
        database: database.to_owned(),
        table: table.to_owned(),
        start_column,
        end_column,
    })
}

/// The statement this server would use to recreate one table.
///
/// Read rather than assembled, because the clause naming the validity columns
/// lives here and nowhere else: this server publishes no catalogue view of
/// periods, so a table that named its own columns can be understood only from
/// its own definition.
async fn definition_of(
    connection: &mut Conn,
    database: &str,
    table: &str,
) -> Result<Option<String>> {
    let sql = format!(
        "SHOW CREATE TABLE {qualified}",
        qualified = ident::quote_qualified(&[database, table])?,
    );
    let rows = fetch(connection, &sql, Params::Empty).await?;
    Ok(rows.first().and_then(|row| {
        row.iter()
            .find(|(name, _)| name.eq_ignore_ascii_case("Create Table"))
            .map(|(_, value)| text_of(Some(value)))
    }))
}

/// The two validity columns a definition names, or the pair the server implies.
///
/// Kept apart from the round trip so the rule is provable without a server,
/// since it is the shape of the definition and not the network that decides.
/// A table that declared its own columns carries them in a `PERIOD FOR
/// SYSTEM_TIME` clause; one that did not carries no clause at all, and then the
/// implied pair is the answer.
#[must_use]
fn period_columns(definition: Option<&str>) -> (String, String) {
    let named = definition.and_then(|text| {
        let opened = text.find(PERIOD_CLAUSE)? + PERIOD_CLAUSE.len();
        let mut names = quoted_names(&text[opened..]);
        let start = names.next()?;
        let end = names.next()?;
        Some((start, end))
    });

    named.unwrap_or_else(|| (IMPLICIT_START.to_owned(), IMPLICIT_END.to_owned()))
}

/// Walks the quoted names of one clause, stopping where the clause does.
///
/// A name may itself hold the quoting mark, which this server writes twice over
/// to say it is part of the name. Reading the pair by splitting on the comma
/// would tear such a name in half, so the marks are counted instead.
fn quoted_names(tail: &str) -> impl Iterator<Item = String> + '_ {
    let mut rest = tail;
    std::iter::from_fn(move || {
        let opening = rest.find('`')?;
        // Nothing but the clause's own opening bracket and separators may sit
        // before a name, so anything else means the clause already ended.
        if rest[..opening].contains(')') {
            return None;
        }
        let mut name = String::new();
        let mut walk = rest[opening + 1..].char_indices();
        while let Some((at, letter)) = walk.next() {
            if letter != '`' {
                name.push(letter);
                continue;
            }
            // A doubled mark is one mark inside the name; a lone one closes it.
            if rest[opening + 1 + at + 1..].starts_with('`') {
                name.push('`');
                walk.next();
                continue;
            }
            rest = &rest[opening + 1 + at + 1..];
            return Some(name);
        }
        None
    })
}

/// The refusal a table that keeps no history produces.
#[must_use]
pub fn not_versioned(database: &str, table: &str) -> Failure {
    Failure::validation(
        "validation.table_keeps_no_history",
        format!("the table `{database}`.`{table}` keeps no version history"),
    )
    .with_detail("detail", format!("{database}.{table}"))
    .with_hint(
        "run `history tables` to see which tables keep one, or turn versioning on for this table",
    )
}

/// Decides which database the command works on.
///
/// # Errors
///
/// Returns an absence failure when neither the caller nor the destination
/// names one, because guessing a database would answer a question nobody
/// asked.
pub fn resolve_database(named: Option<&str>, in_force: Option<&str>) -> Result<String> {
    named.or(in_force).map(ToOwned::to_owned).ok_or_else(|| {
        Failure::absence(
            "absence.no_database_in_force",
            "no database was named and the destination has none in force",
        )
        .with_hint("name one with --database")
    })
}

/// Refuses a database that is not on this destination.
async fn confirm_database_exists(connection: &mut Conn, database: &str) -> Result<()> {
    let rows = fetch(
        connection,
        "SELECT SCHEMA_NAME FROM information_schema.SCHEMATA WHERE SCHEMA_NAME = :database",
        params! { "database" => database.to_owned() },
    )
    .await?;

    if rows.is_empty() {
        return Err(Failure::absence(
            "absence.unknown_database",
            format!("the database `{database}` does not exist on this destination"),
        )
        .with_detail("detail", database)
        .with_hint("run `schema databases` to see the databases this credential can reach"));
    }
    Ok(())
}

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

    /// A table that keeps no history is refused before anything is read from
    /// it, so the present state can never pass for the past.
    #[test]
    fn a_table_keeping_no_history_is_refused_with_a_way_forward() {
        let failure = not_versioned("loja", "vendas");
        assert_eq!(failure.exit_number().code(), 3);
        assert!(failure.message().contains("vendas"));
        assert!(failure
            .hint()
            .is_some_and(|hint| hint.contains("history tables")));
    }

    /// The database the caller names outranks the one in force, and neither
    /// being present is an absence rather than a guess.
    #[test]
    fn the_named_database_outranks_the_one_in_force() {
        assert_eq!(
            resolve_database(Some("loja"), Some("sales")).expect("named wins"),
            "loja"
        );
        assert_eq!(
            resolve_database(None, Some("sales")).expect("in force serves"),
            "sales"
        );
        let failure = resolve_database(None, None).expect_err("nothing to work on");
        assert_eq!(failure.exit_number().code(), 8);
    }

    /// The word the server uses for a versioned table is matched whatever case
    /// the release chose for it.
    #[test]
    fn a_versioned_table_is_recognised_whatever_case_the_server_used() {
        assert!("System Versioned".eq_ignore_ascii_case(VERSIONED));
        assert!(!"BASE TABLE".eq_ignore_ascii_case(VERSIONED));
    }

    /// A table that declared its own validity columns is read from its own
    /// definition, because that clause is the only place the pair is written.
    #[test]
    fn a_declared_pair_comes_from_the_definition() {
        let ddl = "CREATE TABLE `prices` (\n  `id` int(11) NOT NULL,\n  \
             `st` timestamp(6) GENERATED ALWAYS AS ROW START,\n  \
             `en` timestamp(6) GENERATED ALWAYS AS ROW END,\n  \
             PRIMARY KEY (`id`,`en`),\n  \
             PERIOD FOR SYSTEM_TIME (`st`, `en`)\n) WITH SYSTEM VERSIONING";
        assert_eq!(
            period_columns(Some(ddl)),
            ("st".to_owned(), "en".to_owned())
        );
    }

    /// A table versioned without naming columns carries no clause at all, and
    /// then the pair the server implies is the answer rather than a failure.
    #[test]
    fn an_undeclared_pair_falls_back_to_the_implied_names() {
        let ddl = "CREATE TABLE `prices` (\n  `sku` varchar(32) NOT NULL,\n  \
             PRIMARY KEY (`sku`)\n) ENGINE=InnoDB WITH SYSTEM VERSIONING";
        assert_eq!(
            period_columns(Some(ddl)),
            (IMPLICIT_START.to_owned(), IMPLICIT_END.to_owned())
        );
    }

    /// A definition that could not be read at all still answers, because the
    /// implied pair is what an unnamed period is called on every release.
    #[test]
    fn a_missing_definition_still_answers_with_the_implied_names() {
        assert_eq!(
            period_columns(None),
            (IMPLICIT_START.to_owned(), IMPLICIT_END.to_owned())
        );
    }

    /// A name carrying the quoting mark survives, because the mark written
    /// twice over is one mark inside the name and never the end of it.
    #[test]
    fn a_name_carrying_the_quoting_mark_is_not_torn_in_half() {
        let ddl = "PERIOD FOR SYSTEM_TIME (`od``d`, `en,d`)";
        assert_eq!(
            period_columns(Some(ddl)),
            ("od`d".to_owned(), "en,d".to_owned())
        );
    }

    /// A clause that names only one column is no pair, so the implied names
    /// answer rather than a half-read pair being trusted.
    #[test]
    fn a_half_written_clause_is_not_trusted() {
        assert_eq!(
            period_columns(Some("PERIOD FOR SYSTEM_TIME (`st`)")),
            (IMPLICIT_START.to_owned(), IMPLICIT_END.to_owned())
        );
    }
}