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

dbmcp_sqlite/tools/
list_tables.rs
