8000 Merge branch 'develop' into feat/add_post_delete_on_functions · supabase/postgres-meta@13799a8 · GitHub
[go: up one dir, main page]
Skip to content

Commit 13799a8

Browse files
i-pipi-pip
committed
Merge branch 'develop' into feat/add_post_delete_on_functions
2 parents 1aca9a9 + 13f421f commit 13799a8

File tree

14 files changed

+585
-6
lines changed

14 files changed

+585
-6
lines changed

‎src/lib/PostgresMeta.ts

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -8,6 +8,7 @@ import PostgresMetaPublications from './PostgresMetaPublications'
88
import PostgresMetaRoles from './PostgresMetaRoles'
99
import PostgresMetaSchemas from './PostgresMetaSchemas'
1010
import PostgresMetaTables from './PostgresMetaTables'
11+
import PostgresMetaTriggers from './PostgresMetaTriggers'
1112
import PostgresMetaTypes from './PostgresMetaTypes'
1213
import PostgresMetaVersion from './PostgresMetaVersion'
1314
import { init } from './db'
@@ -25,6 +26,7 @@ export default class PostgresMeta {
2526
roles: PostgresMetaRoles
2627
schemas: PostgresMetaSchemas
2728
tables: PostgresMetaTables
29+
triggers: PostgresMetaTriggers
2830
types: PostgresMetaTypes
2931
version: PostgresMetaVersion
3032

@@ -41,6 +43,7 @@ export default class PostgresMeta {
4143
this.roles = new PostgresMetaRoles(this.query)
4244
this.schemas = new PostgresMetaSchemas(this.query)
4345
this.tables = new PostgresMetaTables(this.query)
46+
this.triggers = new PostgresMetaTriggers(this.query)
4447
this.types = new PostgresMetaTypes(this.query)
4548
this.version = new PostgresMetaVersion(this.query)
4649
}

‎src/lib/PostgresMetaPublications.ts

Lines changed: 22 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -68,7 +68,17 @@ export default class PostgresMetaPublications {
6868
} else if (tables.length === 0) {
6969
tableClause = ''
7070
} else {
71-
tableClause = `FOR TABLE ${tables.map(ident).join(',')}`
71+
tableClause = `FOR TABLE ${tables
72+
.map((t) => {
73+
if (!t.includes('.')) {
74+
return ident(t)
75+
}
76+
77+
const [schema, ...rest] = t.split('.')
78+
const table = rest.join('.')
79+
return `${ident(schema)}.${ident(table)}`
80+
})
81+
.join(',')}`
7282
}
7383

7484
let publishOps = []
@@ -138,7 +148,17 @@ CREATE PUBLICATION ${ident(name)} ${tableClause}
138148
} else if (old!.tables === null) {
139149
throw new Error('Tables cannot be added to or dropped from FOR ALL TABLES publications')
140150
} else if (tables.length > 0) {
141-
tableSql = `ALTER PUBLICATION ${ident(old!.name)} SET TABLE ${tables.map(ident).join(',')};`
151+
tableSql = `ALTER PUBLICATION ${ident(old!.name)} SET TABLE ${tables
152+
.map((t) => {
153+
if (!t.includes('.')) {
154+
return ident(t)
155+
}
156+
157+
const [schema, ...rest] = t.split('.')
158+
const table = rest.join('.')
159+
return `${ident(schema)}.${ident(table)}`
160+
})
161+
.join(',')};`
142162
} else if (old!.tables.length === 0) {
143163
tableSql = ''
144164
} else {

‎src/lib/PostgresMetaTriggers.ts

Lines changed: 228 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,228 @@
1+
import format, { ident, literal } from 'pg-format'
2+
10000 import { triggersSql } from './sql'
3+
import { PostgresMetaResult, PostgresTrigger } from './types'
4+
5+
export default class PostgresMetaTriggers {
6+
query: (sql: string) => Promise<PostgresMetaResult<any>>
7+
8+
constructor(query: (sql: string) => Promise<PostgresMetaResult<any>>) {
9+
this.query = query
10+
}
11+
12+
async list(): Promise<PostgresMetaResult<PostgresTrigger[]>> {
13+
return await this.query(enrichedTriggersSql)
14+
}
15+
16+
async retrieve({ id }: { id: number }): Promise<PostgresMetaResult<PostgresTrigger>>
17+
async retrieve({
18+
name,
19+
table,
20+
schema,
21+
}: {
22+
name: string
23+
table: string
24+
schema?: string
25+
}): Promise<PostgresMetaResult<PostgresTrigger>>
26+
async retrieve({
27+
id,
28+
name,
29+
schema = 'public',
30+
table,
31+
}: {
32+
id?: number
33+
name?: string
34+
schema?: string
35+
table?: string
36+
}): Promise<PostgresMetaResult<PostgresTrigger>> {
37+
if (id) {
38+
const sql = `${enrichedTriggersSql} WHERE triggers.id = ${literal(id)};`
39+
40+
const { data, error } = await this.query(sql)
41+
42+
if (error) {
43+
return { data: null, error }
44+
}
45+
46+
const triggerRecord = data && data[0]
47+
48+
if (triggerRecord) {
49+
return { data: triggerRecord, error: null }
50+
}
51+
52+
return { data: null, error: { message: `Cannot find a trigger with ID ${id}` } }
53+
}
54+
55+
if (name && schema && table) {
56+
const sql = `${enrichedTriggersSql} WHERE triggers.name = ${literal(
57+
name
58+
)} AND triggers.schema = ${literal(schema)} AND triggers.table = ${literal(table)};`
59+
60+
const { data, error } = await this.query(sql)
61+
62+
if (error) {
63+
return { data: null, error }
64+
}
65+
66+
const triggerRecord = data && data[0]
67+
68+
if (triggerRecord) {
69+
return { data: triggerRecord, error: null }
70+
}
71+
72+
return {
73+
data: null,
74+
error: {
75+
message: `Cannot find a trigger with name ${name} on table "${schema}"."${table}"`,
76+
},
77+
}
78+
}
79+
80+
return { data: null, error: { message: 'Invalid parameters on trigger retrieve' } }
81+
}
82+
83+
/**
84+
* Creates trigger
85+
*
86+
* @param {Object} obj - An object.
87+
* @param {string} obj.name - Trigger name.
88+
* @param {string} obj.schema - Name of schema that trigger is for.
89+
* @param {string} obj.table - Unqualified table, view, or foreign table name that trigger is for.
90+
* @param {string} obj.function_schema - Name of schema that function is for.
91+
* @param {string} obj.function_name - Unqualified name of the function to execute.
92+
* @param {('BEFORE'|'AFTER'|'INSTEAD OF')} obj.activation - Determines when function is called
93+
* during event occurrence.
94+
* @param {Array<string>} obj.events - Event(s) that will fire the trigger. Array of the following options: 'INSERT' | 'UPDATE' | 'UPDATE
95+
* OF column_name1,column_name2' | 'DELETE' | 'TRUNCATE'.
96+
* @param {('ROW'|'STATEMENT')} obj.orientation - Trigger function for every row affected by event or
97+
* once per statement. Defaults to 'STATEMENT'.
98+
* @param {string} obj.condition - Boolean expression that will trigger function.
99+
* For example: 'old.* IS DISTINCT FROM new.*'
100+
* @param {Array<string>} obj.function_args - array of arguments to be passed to function when trigger is fired.
101+
* For example: ['arg1', 'arg2']
102+
*/
103+
async create({
104+
name,
105+
schema = 'public',
106+
table,
107+
function_schema = 'public',
108+
function_name,
109+
function_args,
110+
activation,
111+
events,
112+
orientation,
113+
condition,
114+
}: {
115+
name: string
116+
table: string
117+
function_name: string
118+
activation: string
119+
events: string[]
120+
function_schema?: string
121+
schema?: string
122+
orientation?: string
123+
condition?: string
124+
function_args?: string[]
125+
}): Promise<PostgresMetaResult<PostgresTrigger>> {
126+
const qualifiedTableName = `${ident(schema)}.${ident(table)}`
127+
const qualifiedFunctionName = `${ident(function_schema)}.${ident(function_name)}`
128+
129+
const triggerOrientation = orientation ? `FOR EACH ${format.string(orientation)}` : ''
130+
const triggerCondition = condition ? `WHEN ( ${format.string(condition)} )` : ''
131+
const triggerEvents = Array.isArray(events) ? `${format.string(events.join(' OR '))}` : ''
132+
const functionArgs = Array.isArray(function_args)
133+
? `${function_args.map((arg) => literal(arg)).join(',')}`
134+
: ''
135+
136+
const sql = `CREATE TRIGGER ${ident(name)} ${format.string(
137+
activation
138+
)} ${triggerEvents} ON ${qualifiedTableName} ${triggerOrientation} ${triggerCondition} EXECUTE FUNCTION ${qualifiedFunctionName} ( ${functionArgs} );`
139+
140+
const { error } = await this.query(sql)
141+
142+
if (error) {
143+
return { data: null, error }
144+
}
145+
146+
return await this.retrieve({
147+
name,
148+
table,
149+
schema,
150+
})
151+
}
152+
153+
async update(
154+
id: number,
155+
{
156+
name: newName,
157+
enabled_mode,
158+
}: {
159+
name: string
160+
enabled_mode: 'ORIGIN' | 'REPLICA' | 'ALWAYS' | 'DISABLED'
161+
}
162+
): Promise<PostgresMetaResult<PostgresTrigger>> {
163+
const { data: triggerRecord, error } = await this.retrieve({ id })
164+
165+
if (error) {
166+
return { data: null, error }
167+
}
168+
169+
let enabledModeSql
170+
const enabledMode = enabled_mode.toUpperCase()
171+
const { name: currentName, schema: schema, table: table } = triggerRecord!
172+
const qualifiedTableName = `${ident(schema)}.${ident(table)}`
173+
const updateNameSql = newName
174+
? `ALTER TRIGGER ${ident(currentName)} ON ${qualifiedTableName} RENAME TO ${ident(newName)};`
175+
: ''
176+
177+
if (['ORIGIN', 'REPLICA', 'ALWAYS', 'DISABLED'].includes(enabledMode)) {
178+
if (enabledMode === 'DISABLED') {
179+
enabledModeSql = `ALTER TABLE ${qualifiedTableName} DISABLE TRIGGER ${ident(currentName)};`
180+
} else {
181+
enabledModeSql = `ALTER TABLE ${qualifiedTableName} ENABLE ${
182+
['REPLICA', 'ALWAYS'].includes(enabledMode) ? enabledMode : ''
183+
} TRIGGER ${ident(currentName)};`
184+
}
185+
}
186+
187+
// updateNameSql must be last
188+
const sql = `BEGIN; ${enabledModeSql} ${updateNameSql} COMMIT;`
189+
190+
{
191+
const { error } = await this.query(sql)
192+
193+
if (error) {
194+
return { data: null, error }
195+
}
196+
}
197+
198+
return await this.retrieve({ id })
199+
}
200+
201+
async remove(id: number, { cascade = false }): Promise<PostgresMetaResult<PostgresTrigger>> {
202+
const { data: triggerRecord, error } = await this.retrieve({ id })
203+
204+
if (error) {
205+
return { data: null, error }
206+
}
207+
208+
const { name, schema, table } = triggerRecord!
209+
const qualifiedTableName = `${ident(schema)}.${ident(table)}`
210+
const sql = `DROP TRIGGER ${ident(name)} ON ${qualifiedTableName} ${cascade ? 'CASCADE' : ''};`
211+
212+
{
213+
const { error } = await this.query(sql)
214+
215+
if (error) {
216+
return { data: null, error }
217+
}
218+
}
219+
220+
return { data: triggerRecord!, error: null }
221+
}
222+
}
223+
224+
const enrichedTriggersSql = `
225+
WITH triggers AS (${triggersSql})
226+
SELECT
227+
*
228+
FROM triggers`

‎src/lib/sql/columns.sql

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -59,7 +59,7 @@ SELECT
5959
FROM
6060
pg_catalog.pg_enum enums
6161
WHERE
62-
COALESCE(bt.typname, t.typname) = format_type(enums.enumtypid, NULL)
62+
quote_ident(COALESCE(bt.typname, t.typname)) = format_type(enums.enumtypid, NULL)
6363
ORDER BY
6464
enums.enumsortorder
6565
)

‎src/lib/sql/index.ts

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -13,5 +13,6 @@ export const relationshipsSql = readFileSync(resolve(__dirname, 'relationships.s
1313
export const rolesSql = readFileSync(resolve(__dirname, 'roles.sql'), 'utf-8')
1414
export const schemasSql = readFileSync(resolve(__dirname, 'schemas.sql'), 'utf-8')
1515
export const tablesSql = readFileSync(resolve(__dirname, 'tables.sql'), 'utf-8')
16+
export const triggersSql = readFileSync(resolve(__dirname, 'triggers.sql'), 'utf-8')
1617
export const typesSql = readFileSync(resolve(__dirname, 'types.sql'), 'utf-8')
1718
export const versionSql = readFileSync(resolve(__dirname, 'version.sql'), 'utf-8')

‎src/lib/sql/schemas.sql

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
-- Adapted from infromation_schema.schemata
1+
-- Adapted from information_schema.schemata
22

33
SELECT
44
n.oid :: int8 AS id,

‎src/lib/sql/triggers.sql

Lines changed: 48 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,48 @@
1+
SELECT
2+
pg_t.oid AS id,
3+
CASE
4+
WHEN pg_t.tgenabled = 'D' THEN 'DISABLED'
5+
WHEN pg_t.tgenabled = 'O' THEN 'ORIGIN'
6+
WHEN pg_t.tgenabled = 'R' THEN 'REPLICA'
7+
WHEN pg_t.tgenabled = 'A' THEN 'ALWAYS'
8+
END AS enabled_mode,
9+
(
10+
STRING_TO_ARRAY(
11+
ENCODE(pg_t.tgargs, 'escape'), '\000'
12+
)
13+
)[:pg_t.tgnargs] AS function_args,
14+
is_t.trigger_name AS name,
15+
is_t.event_object_table AS table,
16+
is_t.event_object_schema AS schema,
17+
is_t.action_condition AS condition,
18+
is_t.action_orientation AS orientation,
19+
is_t.action_timing AS activation,
20+
ARRAY_AGG(is_t.event_manipulation)::text[] AS events,
21+
pg_p.proname AS function_name,
22+
pg_n.nspname AS function_schema
23+
FROM
24+
pg_trigger AS pg_t
25+
JOIN
26+
pg_class AS pg_c
27+
ON pg_t.tgrelid = pg_c.oid
28+
JOIN information_schema.triggers AS is_t
29+
ON is_t.trigger_name = pg_t.tgname
30+
AND pg_c.relname = is_t.event_object_table
31+
JOIN pg_proc AS pg_p
32+
ON pg_t.tgfoid = pg_p.oid
33+
JOIN pg_namespace AS pg_n
34+
ON pg_p.pronamespace = pg_n.oid
35+
GROUP BY
36+
pg_t.oid,
37+
pg_t.tgenabled,
38+
pg_t.tgargs,
39+
pg_t.tgnargs,
40+
is_t.trigger_name,
41+
is_t.event_object_table,
42+
is_t.event_object_schema,
43+
is_t.action_condition,
44+
is_t.action_orientation,
45+
is_t.action_statement,
46+
is_t.action_timing,
47+
pg_p.proname,
48+
pg_n.nspname

‎src/lib/types.ts

Lines changed: 25 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -150,6 +150,31 @@ export const postgresPublicationSchema = Type.Object({
150150
})
151151
export type PostgresPublication = Static<typeof postgresPublicationSchema>
152152

153+
export const postgresTriggerSchema = Type.Object({
154+
id: Type.Integer(),
155+
enabled_mode: Type.Union([
156+
Type.Literal('ORIGIN'),
157+
Type.Literal('REPLICA'),
158+
Type.Literal('ALWAYS'),
159+
Type.Literal('DISABLED'),
160+
]),
161+
name: Type.String(),
162+
table: Type.String(),
163+
schema: Type.String(),
164+
condition: Type.Union([Type.String(), Type.Null()]),
165+
orientation: Type.Union([Type.Literal('ROW'), Type.Literal('STATEMENT')]),
166+
activation: Type.Union([
167+
Type.Literal('BEFORE'),
168+
Type.Literal('AFTER'),
169+
Type.Literal('INSTEAD OF'),
170+
]),
171+
events: Type.Array(Type.String()),
172+
function_schema: Type.String(),
173+
function_name: Type.String(),
174+
function_args: Type.Array(Type.String()),
175+
})
176+
export type PostgresTrigger = Static<typeof postgresTriggerSchema>
177+
153178
export const postgresRelationshipSchema = Type.Object({
154179
id: Type.Integer(),
155180
constraint_name: Type.String(),

0 commit comments

Comments
 (0)
0