8000 Merge branch 'develop' of github.com:supabase/postgres-meta into develop · DavraYoung/postgres-meta@13f421f · GitHub
[go: up one dir, main page]
Skip to content

Commit 13f421f

Browse files
i-pipi-pip
committed
Merge branch 'develop' of github.com:supabase/postgres-meta into develop
2 parents 2f6ab96 + e06449e commit 13f421f

File tree

14 files changed

+585
-6
lines changed
  • PostgresMetaTriggers.ts
  • sql
  • types.ts
  • server
  • test

    • 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'
    11 9E12 12
    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+
    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.< F438 /span>
    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

    -1Lines 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(< 6F6E /span>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