pg-meta-materialized-views.ts115 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 { MATERIALIZED_VIEWS_SQL } from './sql/materialized-views'
9
10	export const pgMaterializedViewZod = z.object({
11	id: z.number(),
12	schema: z.string(),
13	name: z.string(),
14	is_populated: z.boolean(),
15	comment: z.string().nullable(),
16	columns: pgColumnArrayZod.optional(),
17	})
18
19	export type PGMaterializedView = z.infer<typeof pgMaterializedViewZod>
20
21	export const pgMaterializedViewArrayZod = z.array(pgMaterializedViewZod)
22	export const pgMaterializedViewOptionalZod = z.optional(pgMaterializedViewZod)
23
24	type MaterializedViewWithoutColumns = Omit<PGMaterializedView, 'columns'>
25	type MaterializedViewWithColumns = PGMaterializedView
26
27	type MaterializedViewBasedOnIncludeColumns<T extends boolean | undefined> = T extends true
28	? MaterializedViewWithColumns
29	: MaterializedViewWithoutColumns
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<MaterializedViewBasedOnIncludeColumns<T>[]>
50	} {
51	let sql = generateEnrichedMaterializedViewsSql({ 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: pgMaterializedViewArrayZod,
69	}
70	}
71
72	type MaterializedViewIdentifier =
73	| Pick<PGMaterializedView, 'id'>
74	| Pick<PGMaterializedView, 'name' | 'schema'>
75
76	function getIdentifierWhereClause(identifier: MaterializedViewIdentifier): SafeSqlFragment {
77	if ('id' in identifier && identifier.id) {
78	return safeSql`${ident('id')} = ${literal(identifier.id)}`
79	}
80	if ('name' in identifier && identifier.name && identifier.schema) {
81	return safeSql`${ident('name')} = ${literal(identifier.name)} and ${ident('schema')} = ${literal(identifier.schema)}`
82	}
83	throw new Error('Must provide either id or name and schema')
84	}
85
86	export function retrieve(identifier: MaterializedViewIdentifier): {
87	sql: SafeSqlFragment
88	zod: typeof pgMaterializedViewOptionalZod
89	} {
90	let whereClause = getIdentifierWhereClause(identifier)
91
92	const sql = safeSql`${generateEnrichedMaterializedViewsSql({ includeColumns: true })} where ${whereClause};`
93	return {
94	sql,
95	zod: pgMaterializedViewOptionalZod,
96	}
97	}
98
99	const generateEnrichedMaterializedViewsSql = ({
100	includeColumns,
101	}: {
102	includeColumns?: boolean
103	}) => safeSql`
104	with materialized_views as (${MATERIALIZED_VIEWS_SQL})
105	${includeColumns ? safeSql`, columns as (${COLUMNS_SQL})` : safeSql``}
106	select
107	*
108	${includeColumns ? safeSql`, ${coalesceRowsToArray('columns', safeSql`columns.table_id = materialized_views.id`)}` : safeSql``}
109	from materialized_views`
110
111	export default {
112	list,
113	retrieve,
114	zod: pgMaterializedViewZod,
115	}