dbmcp_sqlite/tools/
list_tables.rs

1//! MCP tool: `listTables`.
2
3use std::borrow::Cow;
4
5use dbmcp_server::pagination::Pager;
6use dbmcp_sql::Connection as _;
7use rmcp::handler::server::router::tool::{AsyncTool, ToolBase};
8use rmcp::model::{ErrorData, ToolAnnotations};
9
10use crate::SqliteHandler;
11use crate::types::{ListTablesRequest, ListTablesResponse};
12
13const NAME: &str = "listTables";
14const TITLE: &str = "List Tables";
15const DESCRIPTION: &str = include_str!("../../assets/tools/list_tables.md");
16
17/// Marker type for the `listTables` MCP tool.
18pub(crate) struct ListTablesTool;
19
20impl ToolBase for ListTablesTool {
21    type Parameter = ListTablesRequest;
22    type Output = ListTablesResponse;
23    type Error = ErrorData;
24
25    fn name() -> Cow<'static, str> {
26        NAME.into()
27    }
28
29    fn title() -> Option<String> {
30        Some(TITLE.into())
31    }
32
33    fn description() -> Option<Cow<'static, str>> {
34        Some(DESCRIPTION.into())
35    }
36
37    fn annotations() -> Option<ToolAnnotations> {
38        Some(
39            ToolAnnotations::new()
40                .read_only(true)
41                .destructive(false)
42                .idempotent(true)
43                .open_world(false),
44        )
45    }
46}
47
48impl AsyncTool<SqliteHandler> for ListTablesTool {
49    async fn invoke(handler: &SqliteHandler, params: Self::Parameter) -> Result<Self::Output, Self::Error> {
50        handler.list_tables(params).await
51    }
52}
53
54/// Brief-mode SQL: `pragma_table_list` scan with optional `LIKE` filter.
55///
56/// `pragma_table_list` distinguishes ordinary tables (`type = 'table'`),
57/// virtual tables (`type = 'virtual'`), views (`type = 'view'`), and the
58/// automatically-generated shadow tables that back FTS5 / R-Tree / etc.
59/// (`type = 'shadow'`). Restricting to `('table', 'virtual')` hides shadow
60/// tables from users while still surfacing user-declared virtual tables.
61///
62/// `SQLite`'s `LIKE` operator is case-insensitive for ASCII by default and
63/// `dbmcp` does not toggle the `case_sensitive_like` PRAGMA, so a bare
64/// `LIKE` already matches case-insensitively (a `COLLATE NOCASE` on the
65/// RHS pattern would be a no-op — `LIKE` does not honor right-hand-side
66/// collation; see <https://www.sqlite.org/lang_expr.html>). User-facing
67/// wildcards (`%`, `_`) in `?1` flow straight into LIKE semantics — this
68/// matches the `PostgreSQL` contract established in commit `dbe917f`.
69const BRIEF_SQL: &str = r"
70    SELECT tl.name
71    FROM pragma_table_list tl
72    WHERE tl.schema = 'main'
73      AND tl.type IN ('table', 'virtual')
74      AND tl.name NOT LIKE 'sqlite_%'
75      AND (?1 IS NULL OR tl.name LIKE '%' || ?1 || '%')
76    ORDER BY tl.name
77    LIMIT ?2 OFFSET ?3";
78
79/// Detailed-mode SQL: single CTE joining `sqlite_master` with `pragma_*`
80/// table-valued functions and aggregating each table's columns, constraints,
81/// indexes, and triggers into JSON via `json_object` / `json_group_array`.
82///
83/// `LIMIT`/`OFFSET` are pushed into `table_info` so every downstream CTE scans
84/// at most `page_size + 1` tables, never the full schema.
85///
86/// `SQLite`-specific carve-outs (intentionally silent to clients per FR-012):
87/// CHECK constraints are not exposed by `SQLite`'s catalog, so `constraints[]`
88/// only emits PRIMARY KEY / FOREIGN KEY / UNIQUE entries. `owner`, `comment`,
89/// and column-level `comment` are always `NULL`. `method` is always `btree`,
90/// trigger `enabled` is always `true`, and `indexes[].definition` is either
91/// the raw `sqlite_master.sql` or, for auto-generated indexes with no user
92/// SQL, a synthesised `CREATE [UNIQUE] INDEX …` string so the field is never
93/// null. Columns use 1-based `ordinalPosition` (`cid + 1`) for cross-backend
94/// parity with `PostgreSQL`'s `pg_attribute.attnum`.
95const DETAILED_SQL: &str = r#"
96    WITH table_info AS (
97        SELECT
98            tl.name AS table_name,
99            CASE tl.type
100                WHEN 'virtual' THEN 'VIRTUAL_TABLE'
101                ELSE 'TABLE'
102            END AS kind
103        FROM pragma_table_list tl
104        WHERE tl.schema = 'main'
105          AND tl.type IN ('table', 'virtual')
106          AND tl.name NOT LIKE 'sqlite_%'
107          AND (?1 IS NULL OR tl.name LIKE '%' || ?1 || '%')
108        ORDER BY tl.name
109        LIMIT ?2 OFFSET ?3
110    ),
111    columns_info AS (
112        SELECT
113            ti.table_name,
114            c.cid AS cid,
115            json_object(
116                'name',            c.name,
117                'dataType',        c.type,
118                'ordinalPosition', c.cid + 1,
119                'nullable',        json(CASE WHEN c."notnull" = 0 THEN 'true' ELSE 'false' END),
120                'default',         c.dflt_value,
121                'comment',         NULL
122            ) AS column_json
123        FROM table_info ti, pragma_table_info(ti.table_name) c
124    ),
125    pk_constraints AS (
126        SELECT
127            ti.table_name,
128            json_object(
129                'name',       'PRIMARY',
130                'type',       'PRIMARY KEY',
131                'columns',    json_group_array(c.name),
132                'definition', 'PRIMARY KEY (' || group_concat('"' || c.name || '"', ', ') || ')'
133            ) AS constraint_json
134        FROM table_info ti, pragma_table_info(ti.table_name) c
135        WHERE c.pk > 0
136        GROUP BY ti.table_name
137        HAVING COUNT(c.name) > 0
138    ),
139    fk_constraints AS (
140        SELECT
141            ti.table_name,
142            json_object(
143                'name',              'fk_' || ti.table_name || '_' || f.id,
144                'type',              'FOREIGN KEY',
145                'columns',           json_group_array(f."from"),
146                'definition',        'FOREIGN KEY (' || group_concat('"' || f."from" || '"', ', ')
147                                     || ') REFERENCES "' || f."table" || '"('
148                                     || group_concat('"' || f."to" || '"', ', ') || ')',
149                'referencedTable',   f."table",
150                'referencedColumns', json_group_array(f."to")
151            ) AS constraint_json
152        FROM table_info ti, pragma_foreign_key_list(ti.table_name) f
153        GROUP BY ti.table_name, f.id
154    ),
155    unique_constraints AS (
156        SELECT
157            ti.table_name,
158            json_object(
159                'name',       i.name,
160                'type',       'UNIQUE',
161                'columns',    (SELECT json_group_array(ii.name)
162                               FROM pragma_index_info(i.name) ii),
163                'definition', 'UNIQUE ('
164                              || (SELECT group_concat('"' || ii.name || '"', ', ')
165                                  FROM pragma_index_info(i.name) ii)
166                              || ')'
167            ) AS constraint_json
168        FROM table_info ti, pragma_index_list(ti.table_name) i
169        WHERE i."unique" = 1 AND i.origin <> 'pk'
170    ),
171    all_constraints AS (
172        SELECT table_name, constraint_json FROM pk_constraints
173        UNION ALL
174        SELECT table_name, constraint_json FROM fk_constraints
175        UNION ALL
176        SELECT table_name, constraint_json FROM unique_constraints
177    ),
178    indexes_info AS (
179        SELECT
180            ti.table_name,
181            json_object(
182                'name',       il.name,
183                'columns',    (SELECT json_group_array(ii.name) FROM pragma_index_info(il.name) ii),
184                'unique',     json(CASE WHEN il."unique" = 1 THEN 'true' ELSE 'false' END),
185                'primary',    json(CASE WHEN il.origin = 'pk' THEN 'true' ELSE 'false' END),
186                'method',     'btree',
187                'definition', COALESCE(
188                    (SELECT m2.sql FROM sqlite_master m2 WHERE m2.type = 'index' AND m2.name = il.name),
189                    'CREATE ' || CASE il."unique" WHEN 1 THEN 'UNIQUE INDEX ' ELSE 'INDEX ' END
190                        || '"' || il.name || '" ON "' || ti.table_name || '"('
191                        || (SELECT group_concat('"' || ii.name || '"', ', ') FROM pragma_index_info(il.name) ii)
192                        || ')'
193                )
194            ) AS index_json
195        FROM table_info ti, pragma_index_list(ti.table_name) il
196    ),
197    triggers_info AS (
198        SELECT
199            m.tbl_name AS table_name,
200            json_object(
201                'name',       m.name,
202                'definition', m.sql,
203                'enabled',    json('true')
204            ) AS trigger_json
205        FROM sqlite_master m
206        JOIN table_info ti ON ti.table_name = m.tbl_name
207        WHERE m.type = 'trigger'
208    )
209    SELECT
210        ti.table_name AS name,
211        json_object(
212            'schema',      'main',
213            'kind',        ti.kind,
214            'owner',       NULL,
215            'comment',     NULL,
216            'columns',     COALESCE(
217                               (SELECT json_group_array(json(ci.column_json))
218                                FROM (SELECT column_json
219                                      FROM columns_info
220                                      WHERE table_name = ti.table_name
221                                      ORDER BY cid) ci),
222                               json('[]')),
223            'constraints', COALESCE(
224                               (SELECT json_group_array(json(ac.constraint_json))
225                                FROM all_constraints ac
226                                WHERE ac.table_name = ti.table_name),
227                               json('[]')),
228            'indexes',     COALESCE(
229                               (SELECT json_group_array(json(ii.index_json))
230                                FROM indexes_info ii
231                                WHERE ii.table_name = ti.table_name),
232                               json('[]')),
233            'triggers',    COALESCE(
234                               (SELECT json_group_array(json(tg.trigger_json))
235                                FROM triggers_info tg
236                                WHERE tg.table_name = ti.table_name),
237                               json('[]'))
238        ) AS entry
239    FROM table_info ti
240    ORDER BY ti.table_name"#;
241
242impl SqliteHandler {
243    /// Lists one page of tables in the connected database, optionally filtered and/or detailed.
244    ///
245    /// # Errors
246    ///
247    /// Returns [`ErrorData`] with code `-32602` if `cursor` is malformed, or
248    /// an internal-error [`ErrorData`] if the underlying query fails.
249    pub async fn list_tables(
250        &self,
251        ListTablesRequest {
252            cursor,
253            search,
254            detailed,
255        }: ListTablesRequest,
256    ) -> Result<ListTablesResponse, ErrorData> {
257        let pattern = search.as_deref().map(str::trim).filter(|s| !s.is_empty());
258        let pager = Pager::new(cursor, self.config.page_size);
259
260        if detailed {
261            let rows: Vec<(String, sqlx::types::Json<serde_json::Value>)> = self
262                .connection
263                .fetch(
264                    sqlx::query(DETAILED_SQL)
265                        .bind(pattern)
266                        .bind(pager.limit())
267                        .bind(pager.offset()),
268                    None,
269                )
270                .await?;
271            let (rows, next_cursor) = pager.paginate(rows);
272            return Ok(ListTablesResponse::detailed(
273                rows.into_iter().map(|(name, json)| (name, json.0)).collect(),
274                next_cursor,
275            ));
276        }
277
278        let rows: Vec<String> = self
279            .connection
280            .fetch_scalar(
281                sqlx::query(BRIEF_SQL)
282                    .bind(pattern)
283                    .bind(pager.limit())
284                    .bind(pager.offset()),
285                None,
286            )
287            .await?;
288        let (tables, next_cursor) = pager.paginate(rows);
289        Ok(ListTablesResponse::brief(tables, next_cursor))
290    }
291}
dbmcp_sqlite/tools/list_tables.rs

dbmcp_sqlite/tools/
list_tables.rs
