database_mcp_sql/
pagination.rs

1//! SQL string helpers for server-side pagination.
2//!
3//! MCP cursor decoding and offset arithmetic live in the server crate;
4//! this module only owns the SQL rewrite that appends a `LIMIT` /
5//! `OFFSET` to a caller-owned `SELECT` by wrapping it as a subquery.
6
7/// Wraps a caller-owned `SELECT` as a subquery with `LIMIT` / `OFFSET` appended.
8///
9/// Strips a single trailing `;` and surrounding whitespace from `sql`
10/// before wrapping so the emitted statement remains syntactically valid
11/// on every supported backend. The closing parenthesis is placed on a
12/// new line so a trailing `-- line comment` in the caller's SQL cannot
13/// swallow the wrap's own tokens.
14#[must_use]
15pub fn with_limit_offset(sql: &str, limit: usize, offset: u64) -> String {
16    let trimmed = sql.trim_end();
17    let inner = trimmed.strip_suffix(';').unwrap_or(trimmed).trim_end();
18    format!("SELECT * FROM ({inner}\n) AS paginated LIMIT {limit} OFFSET {offset}")
19}
20
21#[cfg(test)]
22mod tests {
23    use super::with_limit_offset;
24
25    #[test]
26    fn injects_limit_offset_and_alias() {
27        assert_eq!(
28            with_limit_offset("SELECT id FROM users ORDER BY id", 3, 0),
29            "SELECT * FROM (SELECT id FROM users ORDER BY id\n) AS paginated LIMIT 3 OFFSET 0",
30        );
31    }
32
33    #[test]
34    fn emits_requested_limit_and_offset() {
35        let wrapped = with_limit_offset("SELECT 1", 11, 50);
36        assert!(wrapped.contains(" LIMIT 11 OFFSET 50"), "got: {wrapped}");
37    }
38
39    #[test]
40    fn strips_trailing_semicolon_and_whitespace() {
41        // Each input variant must produce the same wrapped SQL.
42        let expected = "SELECT * FROM (SELECT 1\n) AS paginated LIMIT 2 OFFSET 0";
43        for input in ["SELECT 1", "SELECT 1;", "SELECT 1 ;", "SELECT 1;   ", "SELECT 1  "] {
44            assert_eq!(with_limit_offset(input, 2, 0), expected, "input = {input:?}");
45        }
46    }
47
48    #[test]
49    fn survives_trailing_line_comment() {
50        // A trailing `--` line comment must not swallow the wrap's alias or
51        // LIMIT/OFFSET tokens. The wrap puts `)` on its own line so the
52        // comment terminates at the newline.
53        assert_eq!(
54            with_limit_offset("SELECT 1 -- count rows", 6, 0),
55            "SELECT * FROM (SELECT 1 -- count rows\n) AS paginated LIMIT 6 OFFSET 0",
56        );
57    }
58}
database_mcp_sql/pagination.rs

database_mcp_sql/
pagination.rs
