konnosDashboard
pg-meta-columns.ts396 lines · main
1import { z } from 'zod'
2
3import { DEFAULT_SYSTEM_SCHEMAS } from './constants'
4import { filterByList } from './helpers'
5import { ident, keyword, literal, rawSql, safeSql, type SafeSqlFragment } from './pg-format'
6import { COLUMNS_SQL } from './sql/columns'
7
8const pgColumnZod = z.object({
9 id: z.string(),
10 table_id: z.number(),
11 schema: z.string(),
12 table: z.string(),
13 name: z.string(),
14 ordinal_position: z.number(),
15 data_type: z.string(),
16 format: z.string(),
17 // Optional at the type level for compatibility with column data sourced from the legacy
18 // `/platform/pg-meta/{ref}/tables` REST endpoint, which doesn't include this field. The pg-meta
19 // SQL itself always returns format_schema.
20 format_schema: z.string().optional(),
21 is_identity: z.boolean(),
22 identity_generation: z.string().nullable(),
23 is_generated: z.boolean(),
24 is_nullable: z.boolean(),
25 is_updatable: z.boolean(),
26 is_unique: z.boolean(),
27 check: z.string().nullable(),
28 default_value: z.any().nullable(),
29 enums: z.array(z.string()),
30 comment: z.string().nullable(),
31})
32
33export const pgColumnArrayZod = z.array(pgColumnZod)
34const pgColumnOptionalZod = z.optional(pgColumnZod)
35
36export type PGColumn = z.infer<typeof pgColumnZod>
37
38function list({
39 tableId,
40 includeSystemSchemas = false,
41 includedSchemas,
42 excludedSchemas,
43 limit,
44 offset,
45}: {
46 tableId?: number
47 includeSystemSchemas?: boolean
48 includedSchemas?: string[]
49 excludedSchemas?: string[]
50 limit?: number
51 offset?: number
52} = {}): {
53 sql: SafeSqlFragment
54 zod: typeof pgColumnArrayZod
55} {
56 let sql = safeSql`
57with
58 columns as (${COLUMNS_SQL})
59select
60 *
61from
62 columns
63where
64 true
65`
66
67 const filter = filterByList(
68 includedSchemas,
69 excludedSchemas,
70 !includeSystemSchemas ? DEFAULT_SYSTEM_SCHEMAS : undefined
71 )
72
73 if (filter) {
74 sql = safeSql`${sql} and schema ${filter}`
75 }
76 if (tableId !== undefined) {
77 sql = safeSql`${sql} and table_id = ${literal(tableId)} `
78 }
79 if (limit) {
80 sql = safeSql`${sql} limit ${literal(limit)}`
81 }
82 if (offset) {
83 sql = safeSql`${sql} offset ${literal(offset)}`
84 }
85 return {
86 sql,
87 zod: pgColumnArrayZod,
88 }
89}
90
91type ColumnIdentifier = Pick<PGColumn, 'id'> | Pick<PGColumn, 'name' | 'schema' | 'table'>
92
93function getIdentifierWhereClause(identifier: ColumnIdentifier): SafeSqlFragment {
94 if ('id' in identifier && identifier.id) {
95 return safeSql`${ident('id')} = ${literal(identifier.id)}`
96 } else if ('name' in identifier && identifier.name && identifier.schema && identifier.table) {
97 return safeSql`schema = ${literal(identifier.schema)} AND ${ident('table')} = ${literal(identifier.table)} AND name = ${literal(identifier.name)}`
98 }
99 throw new Error('Must provide either id or schema, name and table')
100}
101
102function retrieve(identifier: ColumnIdentifier): {
103 sql: SafeSqlFragment
104 zod: typeof pgColumnOptionalZod
105} {
106 const sql = safeSql`WITH columns AS (${COLUMNS_SQL}) SELECT * FROM columns WHERE ${getIdentifierWhereClause(identifier)};`
107 return {
108 sql,
109 zod: pgColumnOptionalZod,
110 }
111}
112
113export type ColumnTypeRef = {
114 schema?: string
115 name: string
116 isArray?: boolean
117}
118
119export interface CreateOptionsBase {
120 schema: string
121 table: string
122 name: string
123 type: ColumnTypeRef
124 is_identity?: boolean
125 identity_generation?: 'BY DEFAULT' | 'ALWAYS'
126 is_nullable?: boolean
127 is_primary_key?: boolean
128 is_unique?: boolean
129 comment?: string
130 check?: string
131 no_transaction?: boolean
132}
133
134type CreateOptionsDefaultExpression = {
135 default_value_format: 'expression'
136 default_value: SafeSqlFragment
137} & CreateOptionsBase
138
139type CreateOptionsNotDefaultExpression = {
140 default_value_format?: 'literal'
141 default_value?: unknown
142} & CreateOptionsBase
143
144type CreateOptions = CreateOptionsDefaultExpression | CreateOptionsNotDefaultExpression
145
146function create({
147 schema,
148 table,
149 name,
150 type,
151 is_identity = false,
152 identity_generation = 'BY DEFAULT',
153 is_nullable,
154 is_primary_key = false,
155 is_unique = false,
156 comment,
157 check,
158 no_transaction = false,
159 ...defaultOptions
160}: CreateOptions): { sql: SafeSqlFragment } {
161 let defaultValueClause: SafeSqlFragment = safeSql``
162 if (is_identity) {
163 if (defaultOptions.default_value !== undefined) {
164 throw new Error('Columns cannot both be identity and have a default value')
165 }
166
167 defaultValueClause = safeSql`GENERATED ${keyword(identity_generation)} AS IDENTITY`
168 } else {
169 if (defaultOptions.default_value === undefined) {
170 // skip
171 } else if (defaultOptions.default_value_format === 'expression') {
172 defaultValueClause = safeSql`DEFAULT ${defaultOptions.default_value}`
173 } else {
174 defaultValueClause = safeSql`DEFAULT ${literal(defaultOptions.default_value)}`
175 }
176 }
177
178 const isNullableClause: SafeSqlFragment =
179 is_nullable === undefined ? safeSql`` : is_nullable ? safeSql`NULL` : safeSql`NOT NULL`
180 const isPrimaryKeyClause: SafeSqlFragment = is_primary_key ? safeSql`PRIMARY KEY` : safeSql``
181 const isUniqueClause: SafeSqlFragment = is_unique ? safeSql`UNIQUE` : safeSql``
182 const checkSql: SafeSqlFragment =
183 check === undefined ? safeSql`` : safeSql`CHECK (${rawSql(check)})`
184 const commentSql: SafeSqlFragment =
185 comment === undefined
186 ? safeSql``
187 : safeSql`COMMENT ON COLUMN ${ident(schema)}.${ident(table)}.${ident(name)} IS ${literal(comment)}`
188
189 const sql = safeSql`
190 ALTER TABLE ${ident(schema)}.${ident(table)} ADD COLUMN ${ident(name)} ${typeIdent(type)}
191 ${defaultValueClause}
192 ${isNullableClause}
193 ${isPrimaryKeyClause}
194 ${isUniqueClause}
195 ${checkSql};
196 ${commentSql};`
197
198 if (no_transaction) {
199 return { sql }
200 }
201
202 return {
203 sql: safeSql`
204 BEGIN;
205 ${sql};
206 COMMIT;`,
207 }
208}
209
210function typeIdent(type: ColumnTypeRef): SafeSqlFragment {
211 const base =
212 type.schema !== undefined
213 ? safeSql`${ident(type.schema)}.${ident(type.name)}`
214 : ident(type.name)
215 return type.isArray ? safeSql`${base}[]` : base
216}
217
218function update(
219 old: Pick<
220 PGColumn,
221 'name' | 'schema' | 'table' | 'table_id' | 'ordinal_position' | 'is_identity' | 'is_unique'
222 >,
223 {
224 name,
225 type,
226 drop_default = false,
227 default_value,
228 default_value_format = 'literal',
229 is_identity,
230 identity_generation = 'BY DEFAULT',
231 is_nullable,
232 is_unique,
233 comment,
234 check,
235 }: {
236 name?: string
237 type?: ColumnTypeRef
238 drop_default?: boolean
239 default_value?: any
240 default_value_format?: 'expression' | 'literal'
241 is_identity?: boolean
242 identity_generation?: 'BY DEFAULT' | 'ALWAYS'
243 is_nullable?: boolean
244 is_unique?: boolean
245 comment?: string | null
246 check?: SafeSqlFragment | null
247 }
248): { sql: SafeSqlFragment } {
249 const nameSql: SafeSqlFragment =
250 name === undefined || name === old.name
251 ? safeSql``
252 : safeSql`ALTER TABLE ${ident(old.schema)}.${ident(old.table)} RENAME COLUMN ${ident(old.name)} TO ${ident(name)};`
253 // We use USING to allow implicit conversion of incompatible types (e.g. int4 -> text).
254 const typeSql: SafeSqlFragment =
255 type === undefined
256 ? safeSql``
257 : safeSql`ALTER TABLE ${ident(old.schema)}.${ident(old.table)} ALTER COLUMN ${ident(old.name)} SET DATA TYPE ${typeIdent(type)} USING ${ident(old.name)}::${typeIdent(type)};`
258
259 let defaultValueSql: SafeSqlFragment
260 if (drop_default) {
261 defaultValueSql = safeSql`ALTER TABLE ${ident(old.schema)}.${ident(old.table)} ALTER COLUMN ${ident(old.name)} DROP DEFAULT;`
262 } else if (default_value === undefined) {
263 defaultValueSql = safeSql``
264 } else {
265 const defaultValue: SafeSqlFragment =
266 default_value_format === 'expression' ? default_value : literal(default_value)
267 defaultValueSql = safeSql`ALTER TABLE ${ident(old.schema)}.${ident(old.table)} ALTER COLUMN ${ident(old.name)} SET DEFAULT ${defaultValue};`
268 }
269 // What identitySql does vary depending on the old and new values of
270 // is_identity and identity_generation.
271 //
272 // | is_identity: old \ new | undefined | true | false |
273 // |------------------------+--------------------+--------------------+----------------|
274 // | true | maybe set identity | maybe set identity | drop if exists |
275 // |------------------------+--------------------+--------------------+----------------|
276 // | false | - | add identity | drop if exists |
277 const alterColumnPrefix = safeSql`ALTER TABLE ${ident(old.schema)}.${ident(old.table)} ALTER COLUMN ${ident(old.name)}`
278 let identitySql: SafeSqlFragment
279 if (is_identity === false) {
280 identitySql = safeSql`${alterColumnPrefix} DROP IDENTITY IF EXISTS;`
281 } else if (old.is_identity === true) {
282 if (identity_generation === undefined) {
283 identitySql = safeSql``
284 } else {
285 identitySql = safeSql`${alterColumnPrefix} SET GENERATED ${keyword(identity_generation)};`
286 }
287 } else if (is_identity === undefined) {
288 identitySql = safeSql``
289 } else {
290 identitySql = safeSql`${alterColumnPrefix} ADD GENERATED ${keyword(identity_generation)} AS IDENTITY;`
291 }
292 let isNullableSql: SafeSqlFragment
293 if (is_nullable === undefined) {
294 isNullableSql = safeSql``
295 } else {
296 isNullableSql = is_nullable
297 ? safeSql`ALTER TABLE ${ident(old.schema)}.${ident(old.table)} ALTER COLUMN ${ident(old.name)} DROP NOT NULL;`
298 : safeSql`ALTER TABLE ${ident(old.schema)}.${ident(old.table)} ALTER COLUMN ${ident(old.name)} SET NOT NULL;`
299 }
300 let isUniqueSql: SafeSqlFragment = safeSql``
301 if (old.is_unique === true && is_unique === false) {
302 isUniqueSql = safeSql`
303DO $$
304DECLARE
305 r record;
306BEGIN
307 FOR r IN
308 SELECT conname FROM pg_constraint WHERE
309 contype = 'u'
310 AND cardinality(conkey) = 1
311 AND conrelid = ${literal(old.table_id)}
312 AND conkey[1] = ${literal(old.ordinal_position)}
313 LOOP
314 EXECUTE ${literal(`ALTER TABLE ${ident(old.schema)}.${ident(old.table)} DROP CONSTRAINT `)} || quote_ident(r.conname);
315 END LOOP;
316END
317$$;`
318 } else if (old.is_unique === false && is_unique === true) {
319 isUniqueSql = safeSql`ALTER TABLE ${ident(old.schema)}.${ident(old.table)} ADD UNIQUE (${ident(old.name)});`
320 }
321 const commentSql: SafeSqlFragment =
322 comment === undefined
323 ? safeSql``
324 : safeSql`COMMENT ON COLUMN ${ident(old.schema)}.${ident(old.table)}.${ident(old.name)} IS ${literal(comment)};`
325
326 let checkSql: SafeSqlFragment = safeSql``
327 if (check !== undefined) {
328 const addCheckSql: SafeSqlFragment =
329 check !== null
330 ? safeSql`
331 ALTER TABLE ${ident(old.schema)}.${ident(old.table)} ADD CONSTRAINT ${ident(`${old.table}_${old.name}_check`)} CHECK (${check});
332
333 SELECT conkey into v_conkey FROM pg_constraint WHERE conname = ${literal(`${old.table}_${old.name}_check`)};
334
335 ASSERT v_conkey IS NOT NULL, 'error creating column constraint: check condition must refer to this column';
336 ASSERT cardinality(v_conkey) = 1, 'error creating column constraint: check condition cannot refer to multiple columns';
337 ASSERT v_conkey[1] = ${literal(old.ordinal_position)}, 'error creating column constraint: check condition cannot refer to other columns';`
338 : safeSql``
339 checkSql = safeSql`
340DO $$
341DECLARE
342 v_conname name;
343 v_conkey int2[];
344BEGIN
345 SELECT conname into v_conname FROM pg_constraint WHERE
346 contype = 'c'
347 AND cardinality(conkey) = 1
348 AND conrelid = ${literal(old.table_id)}
349 AND conkey[1] = ${literal(old.ordinal_position)}
350 ORDER BY oid asc
351 LIMIT 1;
352
353 IF v_conname IS NOT NULL THEN
354 EXECUTE format('ALTER TABLE ${ident(old.schema)}.${ident(old.table)} DROP CONSTRAINT %I', v_conname);
355 END IF;
356 ${addCheckSql}
357END
358$$;`
359 }
360
361 // TODO: Can't set default if column is previously identity even if
362 // is_identity: false. Must do two separate PATCHes (once to drop identity
363 // and another to set default).
364 // NOTE: nameSql must be last. defaultValueSql must be after typeSql.
365 // identitySql must be after isNullableSql.
366 const sql = safeSql`
367BEGIN;
368 ${isNullableSql}
369 ${typeSql}
370 ${defaultValueSql}
371 ${identitySql}
372 ${isUniqueSql}
373 ${commentSql}
374 ${checkSql}
375 ${nameSql}
376COMMIT;`
377
378 return { sql }
379}
380
381function remove(
382 column: Pick<PGColumn, 'name' | 'schema' | 'table'>,
383 { cascade = false } = {}
384): { sql: SafeSqlFragment } {
385 const sql = safeSql`ALTER TABLE ${ident(column.schema)}.${ident(column.table)} DROP COLUMN ${ident(column.name)} ${cascade ? safeSql`CASCADE` : safeSql`RESTRICT`};`
386 return { sql }
387}
388
389export default {
390 list,
391 retrieve,
392 create,
393 update,
394 remove,
395 zod: pgColumnZod,
396}