konnosDashboard
pg-meta-foreign-tables.ts113 lines · main
1import { z } from 'zod'
2
3import { DEFAULT_SYSTEM_SCHEMAS } from './constants'
4import { coalesceRowsToArray, filterByList } from './helpers'
5import { ident, literal, safeSql, type SafeSqlFragment } from './pg-format'
6import { pgColumnArrayZod } from './pg-meta-columns'
7import { COLUMNS_SQL } from './sql/columns'
8import { FOREIGN_TABLES_SQL } from './sql/foreign-tables'
9
10export const pgForeignTableZod = z.object({
11 id: z.number(),
12 schema: z.string(),
13 name: z.string(),
14 comment: z.string().nullable(),
15 foreign_server_name: z.string(),
16 foreign_data_wrapper_name: z.string(),
17 foreign_data_wrapper_handler: z.string(),
18 columns: pgColumnArrayZod.optional(),
19})
20
21export type PGForeignTable = z.infer<typeof pgForeignTableZod>
22
23export const pgForeignTableArrayZod = z.array(pgForeignTableZod)
24export const pgForeignTableOptionalZod = z.optional(pgForeignTableZod)
25
26type ForeignTableWithoutColumns = Omit<PGForeignTable, 'columns'>
27type ForeignTableWithColumns = PGForeignTable
28
29type ForeignTableBasedOnIncludeColumns<T extends boolean | undefined> = T extends true
30 ? ForeignTableWithColumns
31 : ForeignTableWithoutColumns
32
33export function list<T extends boolean | undefined = true>(
34 {
35 includeSystemSchemas = false,
36 includedSchemas,
37 excludedSchemas,
38 limit,
39 offset,
40 includeColumns = true as T,
41 }: {
42 includeSystemSchemas?: boolean
43 includedSchemas?: string[]
44 excludedSchemas?: string[]
45 limit?: number
46 offset?: number
47 includeColumns?: T
48 } = {} as any
49): {
50 sql: SafeSqlFragment
51 zod: z.ZodType<ForeignTableBasedOnIncludeColumns<T>[]>
52} {
53 let sql = generateEnrichedForeignTablesSql({ includeColumns })
54 const filter = filterByList(
55 includedSchemas,
56 excludedSchemas,
57 !includeSystemSchemas ? DEFAULT_SYSTEM_SCHEMAS : undefined
58 )
59 if (filter) {
60 sql = safeSql`${sql} where schema ${filter}`
61 }
62 if (limit) {
63 sql = safeSql`${sql} limit ${literal(limit)}`
64 }
65 if (offset) {
66 sql = safeSql`${sql} offset ${literal(offset)}`
67 }
68 return {
69 sql,
70 zod: pgForeignTableArrayZod,
71 }
72}
73
74type ForeignTableIdentifier = Pick<PGForeignTable, 'id'> | Pick<PGForeignTable, 'name' | 'schema'>
75
76function getIdentifierWhereClause(identifier: ForeignTableIdentifier): 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
86export function retrieve(identifier: ForeignTableIdentifier): {
87 sql: SafeSqlFragment
88 zod: typeof pgForeignTableOptionalZod
89} {
90 const sql = safeSql`${generateEnrichedForeignTablesSql({ includeColumns: true })} where ${getIdentifierWhereClause(identifier)};`
91 return {
92 sql,
93 zod: pgForeignTableOptionalZod,
94 }
95}
96
97const generateEnrichedForeignTablesSql = ({
98 includeColumns,
99}: {
100 includeColumns?: boolean
101}) => safeSql`
102with foreign_tables as (${FOREIGN_TABLES_SQL})
103 ${includeColumns ? safeSql`, columns as (${COLUMNS_SQL})` : safeSql``}
104select
105 *
106 ${includeColumns ? safeSql`, ${coalesceRowsToArray('columns', safeSql`columns.table_id = foreign_tables.id`)}` : safeSql``}
107from foreign_tables`
108
109export default {
110 list,
111 retrieve,
112 zod: pgForeignTableZod,
113}