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 an internal-error [`ErrorData`] if `database` is invalid
177    /// or the underlying query fails.
178    pub async fn list_triggers(
179        &self,
180        database: Option<String>,
181        cursor: Option<Cursor>,
182        search: Option<String>,
183        detailed: bool,
184    ) -> Result<ListEntriesResponse, ErrorData> {
185        let database = database.as_deref().map(str::trim).filter(|s| !s.is_empty());
186        let pattern = search.as_deref().map(str::trim).filter(|s| !s.is_empty());
187        let pager = Pager::new(cursor, self.config.page_size);
188
189        if detailed {
190            let rows: Vec<(String, sqlx::types::Json<serde_json::Value>)> = self
191                .connection
192                .fetch(
193                    sqlx::query(DETAILED_SQL)
194                        .bind(pattern)
195                        .bind(pager.limit())
196                        .bind(pager.offset()),
197                    database,
198                )
199                .await?;
200            let (rows, next_cursor) = pager.paginate(rows);
201            return Ok(ListEntriesResponse::detailed(
202                rows.into_iter().map(|(name, json)| (name, json.0)).collect(),
203                next_cursor,
204            ));
205        }
206
207        let rows: Vec<String> = self
208            .connection
209            .fetch_scalar(
210                sqlx::query(BRIEF_SQL)
211                    .bind(pattern)
212                    .bind(pager.limit())
213                    .bind(pager.offset()),
214                database,
215            )
216            .await?;
217        let (triggers, next_cursor) = pager.paginate(rows);
218        Ok(ListEntriesResponse::brief(triggers, next_cursor))
219    }
220}
dbmcp_postgres/tools/list_triggers.rs

dbmcp_postgres/tools/
list_triggers.rs
