pg-meta-views.ts109 lines · main

1	import { z } from 'zod'
2
3	import { DEFAULT_SYSTEM_SCHEMAS } from './constants'
4	import { coalesceRowsToArray, filterByList } from './helpers'
5	import { ident, literal, safeSql, type SafeSqlFragment } from './pg-format'
6	import { pgColumnArrayZod } from './pg-meta-columns'
7	import { COLUMNS_SQL } from './sql/columns'
8	import { VIEWS_SQL } from './sql/views'
9
10	export const pgViewZod = z.object({
11	id: z.number(),
12	schema: z.string(),
13	name: z.string(),
14	is_updatable: z.boolean(),
15	comment: z.string().nullable(),
16	columns: pgColumnArrayZod.optional(),
17	})
18
19	export type PGView = z.infer<typeof pgViewZod>
20
21	export const pgViewArrayZod = z.array(pgViewZod)
22	export const pgViewOptionalZod = z.optional(pgViewZod)
23
24	type ViewWithoutColumns = Omit<PGView, 'columns'>
25	type ViewWithColumns = PGView
26
27	type ViewBasedOnIncludeColumns<T extends boolean | undefined> = T extends true
28	? ViewWithColumns
29	: ViewWithoutColumns
30
31	export function list<T extends boolean | undefined = true>(
32	{
33	includeSystemSchemas = false,
34	includedSchemas,
35	excludedSchemas,
36	limit,
37	offset,
38	includeColumns = true as T,
39	}: {
40	includeSystemSchemas?: boolean
41	includedSchemas?: string[]
42	excludedSchemas?: string[]
43	limit?: number
44	offset?: number
45	includeColumns?: T
46	} = {} as any
47	): {
48	sql: SafeSqlFragment
49	zod: z.ZodType<ViewBasedOnIncludeColumns<T>[]>
50	} {
51	let sql = generateEnrichedViewsSql({ includeColumns })
52	const filter = filterByList(
53	includedSchemas,
54	excludedSchemas,
55	!includeSystemSchemas ? DEFAULT_SYSTEM_SCHEMAS : undefined
56	)
57	if (filter) {
58	sql = safeSql`${sql} where schema ${filter}`
59	}
60	if (limit) {
61	sql = safeSql`${sql} limit ${literal(limit)}`
62	}
63	if (offset) {
64	sql = safeSql`${sql} offset ${literal(offset)}`
65	}
66	return {
67	sql,
68	zod: pgViewArrayZod,
69	}
70	}
71
72	type ViewIdentifier = Pick<PGView, 'id'> | Pick<PGView, 'name' | 'schema'>
73
74	function getIdentifierWhereClause(identifier: ViewIdentifier): SafeSqlFragment {
75	if ('id' in identifier && identifier.id) {
76	return safeSql`${ident('id')} = ${literal(identifier.id)}`
77	}
78	if ('name' in identifier && identifier.name && identifier.schema) {
79	return safeSql`${ident('name')} = ${literal(identifier.name)} and ${ident('schema')} = ${literal(identifier.schema)}`
80	}
81	throw new Error('Must provide either id or name and schema')
82	}
83
84	export function retrieve(identifier: ViewIdentifier): {
85	sql: SafeSqlFragment
86	zod: typeof pgViewOptionalZod
87	} {
88	let whereClause = getIdentifierWhereClause(identifier)
89
90	const sql = safeSql`${generateEnrichedViewsSql({ includeColumns: true })} where ${whereClause};`
91	return {
92	sql,
93	zod: pgViewOptionalZod,
94	}
95	}
96
97	const generateEnrichedViewsSql = ({ includeColumns }: { includeColumns?: boolean }) => safeSql`
98	with views as (${VIEWS_SQL})
99	${includeColumns ? safeSql`, columns as (${COLUMNS_SQL})` : safeSql``}
100	select
101	*
102	${includeColumns ? safeSql`, ${coalesceRowsToArray('columns', safeSql`columns.table_id = views.id`)}` : safeSql``}
103	from views`
104
105	export default {
106	list,
107	retrieve,
108	zod: pgViewZod,
109	}