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
//! Imposition of this tool's own row ceiling on a caller's query.
//!
//! # Why the query is not wrapped in a sub-query
//!
//! The Rust reference implementation paginates by wrapping the caller's query
//! as a sub-query and selecting from it. That breaks two legitimate queries:
//!
//! * `SELECT a + 1 FROM t` has an unnamed column, and a derived table demands
//!   that every column be named.
//! * `SELECT t.a, u.a FROM t JOIN u` has a repeated column name, and a derived
//!   table demands that every name be distinct.
//!
//! Labelling those columns would work, but it changes the names the caller
//! asked for, and the caller reads results by name. So this module does not
//! wrap and does not relabel. It reads the syntax tree to prove the query has
//! no row clause of its own, then appends the ceiling to the original text.
//!
//! # Why the text is appended rather than re-rendered
//!
//! Rendering the tree back to text would drop a conditional comment, whose body
//! is executable on this server, and would normalise spacing the caller wrote.
//! Appending leaves every byte the caller sent untouched except the suffix.
//!
//! A query that already carries a clause this module cannot append after is
//! refused with a named reason rather than silently rewritten.

use crate::error::{Failure, Result};
use crate::sql::parse::{parse, ParseLimits};
use crate::sql::strip::strip;
use sqlparser::ast::{Expr, LimitClause, Query, Statement, Value};

/// Imposes a row ceiling and a starting offset on a caller's query.
///
/// When the query already carries a row ceiling of its own that is no larger
/// than `limit`, and no offset is asked for, the text is returned unchanged:
/// the caller's own ceiling is already inside this one.
///
/// # Errors
///
/// Returns a validation failure when the text is not a single query, when
/// `limit` is zero, or when the query carries a clause this module cannot
/// append after.
pub fn with_limit(
    sql: &str,
    limit: usize,
    offset: Option<usize>,
    limits: ParseLimits,
) -> Result<String> {
    if limit == 0 {
        return Err(
            Failure::validation("rewrite.zero_limit", "the row ceiling is zero")
                .with_hint("ask for at least one row"),
        );
    }

    let statements = parse(sql, limits)?;
    let query = single_query(&statements)?;
    refuse_unappendable_clauses(query)?;

    match query.limit_clause.as_ref() {
        None => Ok(append_clause(sql, limit, offset)),
        Some(existing) => reuse_existing_clause(sql, existing, limit, offset),
    }
}

/// Returns the one query the text holds, or says why it is not one.
fn single_query(statements: &[Statement]) -> Result<&Query> {
    let [Statement::Query(query)] = statements else {
        return Err(Failure::validation(
            "rewrite.not_a_single_query",
            "a row ceiling can only be imposed on a single query",
        )
        .with_hint("send one statement that returns rows"));
    };
    Ok(query)
}

/// Refuses a query whose tail this module cannot append after.
///
/// Each of these clauses is written after the row clause on this server, so a
/// suffix would land in the wrong place and change what the statement means.
fn refuse_unappendable_clauses(query: &Query) -> Result<()> {
    // The prose names the clause the way a reader of the message would, and the
    // keyword beside it is what the catalogue substitutes, because a keyword
    // reads the same in every language the catalogue carries.
    let blocker = if query.fetch.is_some() {
        Some(("a fetch clause", "fetch"))
    } else if !query.locks.is_empty() {
        Some(("a row locking clause", "for update"))
    } else if query.for_clause.is_some() {
        Some(("a for clause", "for xml"))
    } else if query.settings.is_some() {
        Some(("a settings clause", "settings"))
    } else if query.format_clause.is_some() {
        Some(("a format clause", "format"))
    } else if !query.pipe_operators.is_empty() {
        Some(("a pipe operator", "|>"))
    } else {
        None
    };

    match blocker {
        None => Ok(()),
        Some((clause, keyword)) => Err(Failure::validation(
            "rewrite.tail_clause_present",
            format!("the query ends in {clause}, so a row ceiling cannot be appended"),
        )
        .with_detail("detail", keyword)
        .with_hint("remove that clause, or ask for the rows without a ceiling")),
    }
}

/// Decides what to do with a row clause the caller already wrote.
fn reuse_existing_clause(
    sql: &str,
    existing: &LimitClause,
    limit: usize,
    offset: Option<usize>,
) -> Result<String> {
    let already = existing_row_count(existing);

    match (already, offset) {
        // The caller's own ceiling is already inside this one and no offset is
        // asked for, so the text stands as written.
        (Some(rows), None) if rows <= limit => Ok(sql.to_owned()),
        _ => Err(Failure::validation(
            "rewrite.limit_already_present",
            "the query already carries a row clause of its own",
        )
        .with_hint(
            "remove the row clause from the query, or ask for a ceiling no smaller than it",
        )),
    }
}

/// Reads a plain row count out of a row clause, when it holds one.
///
/// A clause whose count is an expression, a placeholder or a grouped count is
/// not comparable, so `None` is returned and the caller refuses.
fn existing_row_count(clause: &LimitClause) -> Option<usize> {
    let LimitClause::LimitOffset {
        limit: Some(limit),
        offset: None,
        limit_by,
    } = clause
    else {
        return None;
    };
    if !limit_by.is_empty() {
        return None;
    }
    let Expr::Value(value) = limit else {
        return None;
    };
    match &value.value {
        Value::Number(digits, _) => digits.parse::<usize>().ok(),
        _ => None,
    }
}

/// Appends the row clause to the caller's own text.
///
/// The clause starts on a new line. A statement may legitimately end in a line
/// comment, and a clause appended on the same line would be swallowed by it.
fn append_clause(sql: &str, limit: usize, offset: Option<usize>) -> String {
    use std::fmt::Write as _;

    // Written straight into the statement rather than through a second string
    // built only to be copied in and dropped.
    let mut text = trim_trailing_separator(sql).to_owned();
    if let Some(start) = offset {
        let _ = write!(text, "\nLIMIT {limit} OFFSET {start}");
    } else {
        let _ = write!(text, "\nLIMIT {limit}");
    }
    text
}

/// Cuts a trailing statement separator, and nothing else.
///
/// The separator is located over the blanked text, so a separator that is
/// really the last character of a literal, of a comment or of a quoted name is
/// never mistaken for one. Everything before it is returned exactly as the
/// caller wrote it, including any blanked region, which is why the search runs
/// over the blanked text but the cut is applied to the original.
fn trim_trailing_separator(sql: &str) -> &str {
    let blanked = strip(sql);
    let text = blanked.text();
    match text.rfind(|character: char| !character.is_whitespace()) {
        Some(index) if text.as_bytes().get(index) == Some(&b';') => &sql[..index],
        _ => sql,
    }
}

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

    fn rewritten(sql: &str, limit: usize, offset: Option<usize>) -> String {
        with_limit(sql, limit, offset, ParseLimits::default()).expect("the query accepts a ceiling")
    }

    fn refusal(sql: &str, limit: usize, offset: Option<usize>) -> Failure {
        with_limit(sql, limit, offset, ParseLimits::default())
            .expect_err("the query must be refused")
    }

    #[test]
    fn a_query_without_a_row_clause_receives_one() {
        assert_eq!(
            rewritten("SELECT a FROM t", 10, None),
            "SELECT a FROM t\nLIMIT 10"
        );
    }

    #[test]
    fn an_offset_is_appended_with_the_ceiling() {
        assert_eq!(
            rewritten("SELECT a FROM t", 10, Some(20)),
            "SELECT a FROM t\nLIMIT 10 OFFSET 20"
        );
    }

    /// A clause appended on the same line would land inside the comment.
    #[test]
    fn a_query_ending_in_a_line_comment_still_receives_a_usable_ceiling() {
        for sql in ["SELECT a FROM t -- note", "SELECT a FROM t # note"] {
            let text = rewritten(sql, 4, None);
            assert_eq!(text, format!("{sql}\nLIMIT 4"), "input {sql:?}");
            let parsed = parse(&text, ParseLimits::default()).expect("the result parses");
            assert_eq!(parsed.len(), 1, "input {sql:?}");
        }
    }

    /// The Rust reference implementation wraps the query in a derived table,
    /// which a server refuses when a column has no name.
    #[test]
    fn a_query_with_an_unnamed_column_is_not_broken() {
        let text = rewritten("SELECT a + 1 FROM t", 5, None);
        assert_eq!(text, "SELECT a + 1 FROM t\nLIMIT 5");
        assert!(
            !text.to_uppercase().contains("FROM ("),
            "the caller's query is never wrapped"
        );
    }

    /// The same wrapping breaks when two columns share a name.
    #[test]
    fn a_query_with_a_repeated_column_name_is_not_broken() {
        assert_eq!(
            rewritten("SELECT t.a, u.a FROM t JOIN u ON u.id = t.id", 5, None),
            "SELECT t.a, u.a FROM t JOIN u ON u.id = t.id\nLIMIT 5"
        );
    }

    #[test]
    fn the_callers_own_bytes_are_left_untouched() {
        let sql = "SELECT   a,\n  b\nFROM t";
        assert_eq!(rewritten(sql, 3, None), format!("{sql}\nLIMIT 3"));
    }

    /// A conditional comment is executable here, so re-rendering the tree would
    /// have dropped it.
    #[test]
    fn a_conditional_comment_survives_the_rewrite() {
        let sql = "SELECT /*!40001 SQL_NO_CACHE */ a FROM t";
        assert_eq!(rewritten(sql, 7, None), format!("{sql}\nLIMIT 7"));
    }

    #[test]
    fn a_trailing_separator_is_removed_before_the_clause_is_appended() {
        assert_eq!(
            rewritten("SELECT a FROM t;", 4, None),
            "SELECT a FROM t\nLIMIT 4"
        );
        assert_eq!(
            rewritten("SELECT a FROM t ;  ", 4, None),
            "SELECT a FROM t \nLIMIT 4"
        );
    }

    #[test]
    fn a_separator_that_ends_a_literal_is_not_mistaken_for_a_trailing_one() {
        assert_eq!(
            rewritten("SELECT 'a;'", 4, None),
            "SELECT 'a;'\nLIMIT 4",
            "the last character is inside a literal"
        );
    }

    #[test]
    fn a_separator_that_ends_a_comment_is_not_mistaken_for_a_trailing_one() {
        assert_eq!(
            rewritten("SELECT a FROM t /* ; */", 4, None),
            "SELECT a FROM t /* ; */\nLIMIT 4"
        );
    }

    #[test]
    fn a_query_whose_own_ceiling_is_inside_this_one_is_left_as_written() {
        assert_eq!(
            rewritten("SELECT a FROM t LIMIT 5", 10, None),
            "SELECT a FROM t LIMIT 5"
        );
        assert_eq!(
            rewritten("SELECT a FROM t LIMIT 10", 10, None),
            "SELECT a FROM t LIMIT 10"
        );
    }

    #[test]
    fn a_query_whose_own_ceiling_is_wider_than_this_one_is_refused() {
        assert_eq!(
            refusal("SELECT a FROM t LIMIT 1000", 10, None).code(),
            "rewrite.limit_already_present"
        );
    }

    #[test]
    fn a_query_with_its_own_row_clause_is_refused_when_an_offset_is_asked_for() {
        assert_eq!(
            refusal("SELECT a FROM t LIMIT 5", 10, Some(2)).code(),
            "rewrite.limit_already_present"
        );
    }

    #[test]
    fn a_query_with_its_own_offset_is_refused() {
        for sql in [
            "SELECT a FROM t LIMIT 5 OFFSET 3",
            "SELECT a FROM t LIMIT 3, 5",
        ] {
            assert_eq!(
                refusal(sql, 10, None).code(),
                "rewrite.limit_already_present",
                "input {sql:?}"
            );
        }
    }

    #[test]
    fn a_query_ending_in_a_row_lock_is_refused_rather_than_rewritten() {
        assert_eq!(
            refusal("SELECT a FROM t FOR UPDATE", 10, None).code(),
            "rewrite.tail_clause_present"
        );
    }

    #[test]
    fn a_statement_that_does_not_return_rows_is_refused() {
        for sql in [
            "DELETE FROM t",
            "CREATE TABLE t (a INT)",
            "SET autocommit = 1",
        ] {
            assert_eq!(
                refusal(sql, 10, None).code(),
                "rewrite.not_a_single_query",
                "input {sql:?}"
            );
        }
    }

    #[test]
    fn more_than_one_statement_is_refused() {
        assert_eq!(
            refusal("SELECT 1; SELECT 2", 10, None).code(),
            "rewrite.not_a_single_query"
        );
    }

    #[test]
    fn a_zero_ceiling_is_refused() {
        assert_eq!(
            refusal("SELECT a FROM t", 0, None).code(),
            "rewrite.zero_limit"
        );
    }

    #[test]
    fn the_result_is_still_a_single_readable_query() {
        let text = rewritten("SELECT a FROM t WHERE b = 'x;y'", 2, Some(1));
        let parsed = parse(&text, ParseLimits::default()).expect("the result parses");
        assert_eq!(parsed.len(), 1);
        assert!(text.contains("'x;y'"), "the literal survives, got {text:?}");
    }

    #[test]
    fn a_union_receives_the_ceiling_at_the_end() {
        assert_eq!(
            rewritten("SELECT a FROM t UNION SELECT b FROM u", 6, None),
            "SELECT a FROM t UNION SELECT b FROM u\nLIMIT 6"
        );
    }

    #[test]
    fn a_query_with_a_common_table_expression_receives_the_ceiling() {
        assert_eq!(
            rewritten("WITH c AS (SELECT a FROM t) SELECT a FROM c", 6, None),
            "WITH c AS (SELECT a FROM t) SELECT a FROM c\nLIMIT 6"
        );
    }

    /// An inner ceiling belongs to a sub-query and is not the query's own.
    #[test]
    fn a_ceiling_inside_a_sub_query_does_not_block_the_outer_one() {
        assert_eq!(
            rewritten("SELECT a FROM (SELECT a FROM t LIMIT 3) AS s", 6, None),
            "SELECT a FROM (SELECT a FROM t LIMIT 3) AS s\nLIMIT 6"
        );
    }
}