dbmcp_postgres/tools/
list_triggers.rs

1//! MCP tool: `listTriggers`.
2
3use dbmcp_server::pagination::{Cursor, Pager};
4
5use super::prelude::*;
6use crate::types::{ListEntriesResponse, ListTriggersRequest};
7
8const NAME: &str = "listTriggers";
9const TITLE: &str = "List Triggers";
10const DESCRIPTION_PINNED: &str = include_str!("../../assets/tools/list_triggers/pinned.md");
11const DESCRIPTION_UNPINNED: &str = include_str!("../../assets/tools/list_triggers/unpinned.md");
12
13fn annotations() -> ToolAnnotations {
14    ToolAnnotations::new()
15        .read_only(true)
16        .destructive(false)
17        .idempotent(true)
18        .open_world(false)
19}
20
21/// Marker type for the `listTriggers` MCP tool (pinned variant — no `database` field).
22pub(crate) struct PinnedListTriggersTool;
23
24impl ToolBase for PinnedListTriggersTool {
25    type Parameter = ListTriggersRequest;
26    type Output = ListEntriesResponse;
27    type Error = ErrorData;
28
29    fn name() -> Cow<'static, str> {
30        NAME.into()
31    }
32
33    fn title() -> Option<String> {
34        Some(TITLE.into())
35    }
36
37    fn description() -> Option<Cow<'static, str>> {
38        Some(DESCRIPTION_PINNED.into())
39    }
40
41    fn annotations() -> Option<ToolAnnotations> {
42        Some(annotations())
43    }
44
45    fn input_schema() -> Option<Arc<JsonObject>> {
46        Some(input_schema::<Self::Parameter>(true))
47    }
48
49    fn output_schema() -> Option<Arc<JsonObject>> {
50        Some(output_schema::<Self::Output>())
51    }
52}
53
54impl AsyncTool<PostgresHandler> for PinnedListTriggersTool {
55    async fn invoke(handler: &PostgresHandler, params: Self::Parameter) -> Result<Self::Output, Self::Error> {
56        handler
57            .list_triggers(None, params.cursor, params.search, params.detailed)
58            .await
59    }
60}
61
62/// Marker type for the `listTriggers` MCP tool (unpinned variant — carries `database`).
63pub(crate) struct UnpinnedListTriggersTool;
64
65impl ToolBase for UnpinnedListTriggersTool {
66    type Parameter = ListTriggersRequest;
67    type Output = ListEntriesResponse;
68    type Error = ErrorData;
69
70    fn name() -> Cow<'static, str> {
71        NAME.into()
72    }
73
74    fn title() -> Option<String> {
75        Some(TITLE.into())
76    }
77
78    fn description() -> Option<Cow<'static, str>> {
79        Some(DESCRIPTION_UNPINNED.into())
80    }
81
82    fn annotations() -> Option<ToolAnnotations> {
83        Some(annotations())
84    }
85
86    fn input_schema() -> Option<Arc<JsonObject>> {
87        Some(input_schema::<Self::Parameter>(false))
88    }
89
90    fn output_schema() -> Option<Arc<JsonObject>> {
91        Some(output_schema::<Self::Output>())
92    }
93}
94
95impl AsyncTool<PostgresHandler> for UnpinnedListTriggersTool {
96    async fn invoke(handler: &PostgresHandler, params: Self::Parameter) -> Result<Self::Output, Self::Error> {
97        handler
98            .list_triggers(params.database, params.cursor, params.search, params.detailed)
99            .await
100    }
101}
102
103/// Brief-mode SQL: `pg_trigger` join with optional `ILIKE` filter on trigger name.
104///
105/// `'public'::regnamespace` casts the schema name to a `pg_namespace.oid`
106/// at plan time, replacing what would otherwise be a `pg_namespace` join.
107/// Trigger names are not globally unique on Postgres — the same trigger
108/// name can appear on multiple relations — so `c.relname` is the secondary
109/// sort key, matching the detailed-mode `ORDER BY` and keeping `OFFSET`
110/// pagination stable across duplicate names.
111const BRIEF_SQL: &str = r"
112    SELECT t.tgname
113    FROM pg_trigger t
114    JOIN pg_class c ON t.tgrelid = c.oid
115    WHERE c.relnamespace = 'public'::regnamespace
116      AND NOT t.tgisinternal
117      AND ($1::text IS NULL OR t.tgname ILIKE '%' || $1 || '%')
118    ORDER BY t.tgname, c.relname
119    LIMIT $2 OFFSET $3";
120
121/// Detailed-mode SQL: per-trigger `json_build_object` projection.
122///
123/// `'public'::regnamespace` casts the schema name to a `pg_namespace.oid`
124/// at plan time, eliminating the otherwise-needed `pg_namespace` join.
125/// `schema` is hard-coded `'public'` in the output since the WHERE filter
126/// already pins it to that one namespace. Postgres defers SELECT-list
127/// evaluation past `LIMIT`, so `pg_get_triggerdef` and the events array
128/// only run for the page's rows.
129///
130/// `tgtype` bitmask values are stable since 8.4 and defined in
131/// `src/include/catalog/pg_trigger.h`:
132/// `TRIGGER_TYPE_ROW`=1, `TRIGGER_TYPE_BEFORE`=2, `TRIGGER_TYPE_INSERT`=4,
133/// `TRIGGER_TYPE_DELETE`=8, `TRIGGER_TYPE_UPDATE`=16,
134/// `TRIGGER_TYPE_TRUNCATE`=32, `TRIGGER_TYPE_INSTEAD`=64.
135const DETAILED_SQL: &str = r"
136    SELECT
137        t.tgname AS name,
138        json_build_object(
139            'schema',          'public',
140            'table',           c.relname,
141            'status',          CASE t.tgenabled
142                                   WHEN 'O' THEN 'ENABLED'
143                                   WHEN 'D' THEN 'DISABLED'
144                                   WHEN 'R' THEN 'REPLICA'
145                                   WHEN 'A' THEN 'ALWAYS'
146                               END,
147            'timing',          CASE
148                                   WHEN (t.tgtype & 2)  = 2  THEN 'BEFORE'
149                                   WHEN (t.tgtype & 64) = 64 THEN 'INSTEAD OF'
150                                   ELSE 'AFTER'
151                               END,
152            'events',          to_json(array_remove(ARRAY[
153                                   CASE WHEN (t.tgtype & 4)  = 4  THEN 'INSERT'   END,
154                                   CASE WHEN (t.tgtype & 16) = 16 THEN 'UPDATE'   END,
155                                   CASE WHEN (t.tgtype & 8)  = 8  THEN 'DELETE'   END,
156                                   CASE WHEN (t.tgtype & 32) = 32 THEN 'TRUNCATE' END
157                               ], NULL)),
158            'activationLevel', CASE WHEN (t.tgtype & 1) = 1 THEN 'ROW' ELSE 'STATEMENT' END,
159            'functionName',    p.proname,
160            'definition',      pg_get_triggerdef(t.oid)
161        ) AS entry
162    FROM pg_trigger t
163    JOIN pg_class c ON t.tgrelid = c.oid
164    LEFT JOIN pg_proc p ON p.oid = t.tgfoid
165    WHERE c.relnamespace = 'public'::regnamespace
166      AND NOT t.tgisinternal
167      AND ($1::text IS NULL OR t.tgname ILIKE '%' || $1 || '%')
168    ORDER BY t.tgname, c.relname
169    LIMIT $2 OFFSET $3";
170
171impl PostgresHandler {
172    /// Lists one page of user-defined triggers, optionally filtered and/or detailed.
173    ///
174    /// # Errors
175    ///
176    /// Returns [`ErrorData`] with code `-32602` if `cursor` is malformed,
177    /// or an internal-error [`ErrorData`] if `database` is invalid
178    /// or the underlying query fails.
179    pub async fn list_triggers(
180        &self,
181        database: Option<String>,
182        cursor: Option<Cursor>,
183        search: Option<String>,
184        detailed: bool,
185    ) -> Result<ListEntriesResponse, ErrorData> {
186        let database = database.as_deref().map(str::trim).filter(|s| !s.is_empty());
187        let pattern = search.as_deref().map(str::trim).filter(|s| !s.is_empty());
188        let pager = Pager::new(cursor, self.config.page_size);
189
190        if detailed {
191            let rows: Vec<(String, sqlx::types::Json<serde_json::Value>)> = self
192                .connection
193                .fetch(
194                    sqlx::query(DETAILED_SQL)
195                        .bind(pattern)
196                        .bind(pager.limit())
197                        .bind(pager.offset()),
198                    database,
199                )
200                .await?;
201            let (rows, next_cursor) = pager.paginate(rows);
202            return Ok(ListEntriesResponse::detailed(
203                rows.into_iter().map(|(name, json)| (name, json.0)).collect(),
204                next_cursor,
205            ));
206        }
207
208        let rows: Vec<String> = self
209            .connection
210            .fetch_scalar(
211                sqlx::query(BRIEF_SQL)
212                    .bind(pattern)
213                    .bind(pager.limit())
214                    .bind(pager.offset()),
215                database,
216            )
217            .await?;
218        let (triggers, next_cursor) = pager.paginate(rows);
219        Ok(ListEntriesResponse::brief(triggers, next_cursor))
220    }
221}
dbmcp_postgres/tools/list_triggers.rs

dbmcp_postgres/tools/
list_triggers.rs
