konnosDashboard
pg-meta-triggers.ts223 lines · main
1import { z } from 'zod'
2
3import { DEFAULT_SYSTEM_SCHEMAS } from './constants'
4import { filterByList } from './helpers'
5import {
6 ident,
7 joinSqlFragments,
8 keyword,
9 literal,
10 safeSql,
11 type SafeSqlFragment,
12} from './pg-format'
13import { TRIGGERS_SQL } from './sql/triggers'
14
15type TriggerIdentifier = Pick<PGTrigger, 'id'> | Pick<PGTrigger, 'name' | 'schema' | 'table'>
16
17function getIdentifierWhereClause(identifier: TriggerIdentifier): SafeSqlFragment {
18 if ('id' in identifier && identifier.id) {
19 return safeSql`${ident('id')} = ${literal(identifier.id)}`
20 }
21 if ('name' in identifier && identifier.name && identifier.table && identifier.schema) {
22 return safeSql`${ident('name')} = ${literal(identifier.name)} and ${ident('schema')} = ${literal(identifier.schema)} and ${ident('table')} = ${literal(identifier.table)}`
23 }
24 throw new Error('Must provide either id or name, schema and table')
25}
26
27export const pgTriggerZod = z.object({
28 id: z.number(),
29 table_id: z.number(),
30 enabled_mode: z.enum(['DISABLED', 'ORIGIN', 'REPLICA', 'ALWAYS']),
31 function_args: z.array(z.string()),
32 name: z.string(),
33 table: z.string(),
34 schema: z.string(),
35 condition: z.string().nullable(),
36 orientation: z.enum(['ROW', 'STATEMENT']),
37 activation: z.enum(['BEFORE', 'AFTER', 'INSTEAD OF']),
38 events: z.array(z.string()),
39 function_name: z.string(),
40 function_schema: z.string(),
41})
42
43export type PGTrigger = z.infer<typeof pgTriggerZod>
44
45export const pgTriggerArrayZod = z.array(pgTriggerZod)
46export const pgTriggerOptionalZod = z.optional(pgTriggerZod)
47
48export function list({
49 includeSystemSchemas = false,
50 includedSchemas,
51 excludedSchemas,
52 limit,
53 offset,
54}: {
55 includeSystemSchemas?: boolean
56 includedSchemas?: string[]
57 excludedSchemas?: string[]
58 limit?: number
59 offset?: number
60} = {}): {
61 sql: SafeSqlFragment
62 zod: typeof pgTriggerArrayZod
63} {
64 let sql = safeSql`with triggers as (${TRIGGERS_SQL}) select * from triggers`
65 const filter = filterByList(
66 includedSchemas,
67 excludedSchemas,
68 !includeSystemSchemas ? DEFAULT_SYSTEM_SCHEMAS : undefined
69 )
70 if (filter) {
71 sql = safeSql`${sql} where schema ${filter}`
72 }
73 if (limit) {
74 sql = safeSql`${sql} limit ${literal(limit)}`
75 }
76 if (offset) {
77 sql = safeSql`${sql} offset ${literal(offset)}`
78 }
79 return {
80 sql,
81 zod: pgTriggerArrayZod,
82 }
83}
84
85type TriggersRetrieveReturn = {
86 sql: SafeSqlFragment
87 zod: typeof pgTriggerOptionalZod
88}
89
90export function retrieve(identifier: TriggerIdentifier): TriggersRetrieveReturn
91export function retrieve(params: TriggerIdentifier): TriggersRetrieveReturn {
92 const whereIdentifierCondition = getIdentifierWhereClause(params)
93 const sql = safeSql`with triggers as (${TRIGGERS_SQL}) select * from triggers where ${whereIdentifierCondition};`
94 return {
95 sql,
96 zod: pgTriggerOptionalZod,
97 }
98}
99
100export const pgTriggerCreateZod = z.object({
101 name: z.string(),
102 schema: z.string().optional().default('public'),
103 table: z.string(),
104 function_schema: z.string().optional().default('public'),
105 function_name: z.string(),
106 function_args: z.array(z.string()).optional(),
107 activation: z.enum(['BEFORE', 'AFTER', 'INSTEAD OF']),
108 events: z.array(z.string()),
109 orientation: z.enum(['ROW', 'STATEMENT']).optional(),
110 condition: z.string().optional(),
111})
112
113// Zod validates `condition` as a runtime string; the SafeSqlFragment brand is a
114// separate compile-time trust check. A parsed string is not automatically safe —
115// callers must promote untrusted input via acceptUntrustedSql/rawSql before it can
116// satisfy this type.
117export type PGTriggerCreate = Omit<z.infer<typeof pgTriggerCreateZod>, 'condition'> & {
118 condition?: SafeSqlFragment
119}
120
121export function create({
122 name,
123 schema = 'public',
124 table,
125 function_schema = 'public',
126 function_name,
127 function_args = [],
128 activation,
129 events,
130 orientation,
131 condition,
132}: PGTriggerCreate): {
133 sql: SafeSqlFragment
134 zod: z.ZodType<void>
135} {
136 const qualifiedTableName = safeSql`${ident(schema)}.${ident(table)}`
137 const qualifiedFunctionName = safeSql`${ident(function_schema)}.${ident(function_name)}`
138 const triggerEvents = joinSqlFragments(events.map(keyword), ' or ')
139 const triggerOrientation = orientation ? safeSql`for each ${keyword(orientation)}` : safeSql``
140 const triggerCondition = condition ? safeSql`when (${condition})` : safeSql``
141 const functionArgsFragment =
142 function_args.length > 0 ? joinSqlFragments(function_args.map(literal), ',') : safeSql``
143
144 const sql = safeSql`create trigger ${ident(name)} ${keyword(activation)} ${triggerEvents} on ${qualifiedTableName} ${triggerOrientation} ${triggerCondition} execute function ${qualifiedFunctionName}(${functionArgsFragment});`
145
146 return {
147 sql,
148 zod: z.void(),
149 }
150}
151
152export const pgTriggerUpdateZod = z.object({
153 name: z.string().optional(),
154 enabled_mode: z.enum(['ORIGIN', 'REPLICA', 'ALWAYS', 'DISABLED']).optional(),
155})
156
157export type PGTriggerUpdate = z.infer<typeof pgTriggerUpdateZod>
158
159export function update(
160 id: { name: string; schema: string; table: string },
161 params: PGTriggerUpdate
162): {
163 sql: SafeSqlFragment
164 zod: z.ZodType<void>
165} {
166 const qualifiedTableName = safeSql`${ident(id.schema)}.${ident(id.table)}`
167
168 let enabledModeSql = safeSql``
169
170 switch (params.enabled_mode) {
171 case 'ORIGIN':
172 enabledModeSql = safeSql`alter table ${qualifiedTableName} enable trigger ${ident(id.name)};`
173 break
174 case 'DISABLED':
175 enabledModeSql = safeSql`alter table ${qualifiedTableName} disable trigger ${ident(id.name)};`
176 break
177 case 'REPLICA':
178 case 'ALWAYS':
179 enabledModeSql = safeSql`alter table ${qualifiedTableName} enable ${keyword(params.enabled_mode)} trigger ${ident(id.name)};`
180 break
181 default:
182 break
183 }
184
185 const updateNameSql =
186 params.name && params.name !== id.name
187 ? safeSql`alter trigger ${ident(id.name)} on ${qualifiedTableName} rename to ${ident(params.name)};`
188 : safeSql``
189
190 // updateNameSql must be last
191 const sql = safeSql`begin; ${enabledModeSql}; ${updateNameSql}; commit;`
192
193 return {
194 sql,
195 zod: z.void(),
196 }
197}
198
199export function remove(
200 id: { name: string; schema: string; table: string },
201 { cascade = false } = {}
202): {
203 sql: SafeSqlFragment
204 zod: z.ZodType<void>
205} {
206 const qualifiedTableName = safeSql`${ident(id.schema)}.${ident(id.table)}`
207
208 const sql = safeSql`drop trigger ${ident(id.name)} on ${qualifiedTableName} ${cascade ? safeSql`cascade` : safeSql``};`
209
210 return {
211 sql,
212 zod: z.void(),
213 }
214}
215
216export default {
217 list,
218 retrieve,
219 create,
220 update,
221 remove,
222 zod: pgTriggerZod,
223}