konnosDashboard
pg-meta-schemas.ts165 lines · main
1import { z } from 'zod'
2
3import { DEFAULT_SYSTEM_SCHEMAS } from './constants'
4import { ident, joinSqlFragments, literal, safeSql, type SafeSqlFragment } from './pg-format'
5import { SCHEMAS_SQL } from './sql/schemas'
6
7const pgSchemaZod = z.object({
8 id: z.number(),
9 name: z.string(),
10 owner: z.string(),
11 comment: z.string().nullable(),
12})
13const pgSchemaArrayZod = z.array(pgSchemaZod)
14const pgSchemaOptionalZod = z.optional(pgSchemaZod)
15
16export type PGSchema = z.infer<typeof pgSchemaZod>
17
18function list({
19 includeSystemSchemas = false,
20 limit,
21 offset,
22}: {
23 includeSystemSchemas?: boolean
24 limit?: number
25 offset?: number
26} = {}): {
27 sql: SafeSqlFragment
28 zod: typeof pgSchemaArrayZod
29} {
30 let sql = SCHEMAS_SQL
31 if (!includeSystemSchemas) {
32 sql = safeSql`${sql} and not (n.nspname in (${joinSqlFragments(DEFAULT_SYSTEM_SCHEMAS.map(literal), ',')}))`
33 }
34 if (limit) {
35 sql = safeSql`${sql} limit ${literal(limit)}`
36 }
37 if (offset) {
38 sql = safeSql`${sql} offset ${literal(offset)}`
39 }
40 return {
41 sql,
42 zod: pgSchemaArrayZod,
43 }
44}
45
46function retrieve({ id }: { id: number }): { sql: SafeSqlFragment; zod: typeof pgSchemaOptionalZod }
47function retrieve({ name }: { name: string }): {
48 sql: SafeSqlFragment
49 zod: typeof pgSchemaOptionalZod
50}
51function retrieve({ id, name }: { id?: number; name?: string }): {
52 sql: SafeSqlFragment
53 zod: typeof pgSchemaOptionalZod
54} {
55 if (id) {
56 const sql = safeSql`${SCHEMAS_SQL} and n.oid = ${literal(id)};`
57 return {
58 sql,
59 zod: pgSchemaOptionalZod,
60 }
61 } else {
62 const sql = safeSql`${SCHEMAS_SQL} and n.nspname = ${literal(name)};`
63 return {
64 sql,
65 zod: pgSchemaOptionalZod,
66 }
67 }
68}
69
70type SchemaCreateParams = {
71 name: string
72 owner?: string
73}
74function create({ name, owner }: SchemaCreateParams): { sql: SafeSqlFragment } {
75 const sql = safeSql`create schema ${ident(name)}
76 ${owner === undefined ? safeSql`` : safeSql`authorization ${ident(owner)}`};
77`
78 return { sql }
79}
80
81type SchemaUpdateParams = {
82 name?: string
83 owner?: string
84}
85function update({ id }: { id: number }, params: SchemaUpdateParams): { sql: SafeSqlFragment }
86function update({ name }: { name: string }, params: SchemaUpdateParams): { sql: SafeSqlFragment }
87function update(
88 {
89 id,
90 name,
91 }: {
92 id?: number
93 name?: string
94 },
95 { name: newName, owner }: SchemaUpdateParams
96): { sql: SafeSqlFragment } {
97 const sql = safeSql`
98do $$
99declare
100 id oid := ${id === undefined ? safeSql`${literal(name)}::regnamespace` : literal(id)};
101 old record;
102 new_name text := ${newName === undefined ? literal(null) : literal(newName)};
103 new_owner text := ${owner === undefined ? literal(null) : literal(owner)};
104begin
105 select * into old from pg_namespace where oid = id;
106 if old is null then
107 raise exception 'Cannot find schema with id %', id;
108 end if;
109
110 if new_owner is not null then
111 execute(format('alter schema %I owner to %I;', old.nspname, new_owner));
112 end if;
113
114 -- Using the same name in the rename clause gives an error, so only do it if the new name is different.
115 if new_name is not null and new_name != old.nspname then
116 execute(format('alter schema %I rename to %I;', old.nspname, new_name));
117 end if;
118end
119$$;
120`
121 return { sql }
122}
123
124type SchemaRemoveParams = {
125 cascade?: boolean
126}
127function remove({ id }: { id: number }, params?: SchemaRemoveParams): { sql: SafeSqlFragment }
128function remove({ name }: { name: string }, params?: SchemaRemoveParams): { sql: SafeSqlFragment }
129function remove(
130 {
131 id,
132 name,
133 }: {
134 id?: number
135 name?: string
136 },
137 { cascade = false }: SchemaRemoveParams = {}
138): { sql: SafeSqlFragment } {
139 const sql = safeSql`
140do $$
141declare
142 id oid := ${id === undefined ? safeSql`${literal(name)}::regnamespace` : literal(id)};
143 old record;
144 cascade bool := ${literal(cascade)};
145begin
146 select * into old from pg_namespace where oid = id;
147 if old is null then
148 raise exception 'Cannot find schema with id %', id;
149 end if;
150
151 execute(format('drop schema %I %s;', old.nspname, case when cascade then 'cascade' else 'restrict' end));
152end
153$$;
154`
155 return { sql }
156}
157
158export default {
159 list,
160 retrieve,
161 create,
162 update,
163 remove,
164 zod: pgSchemaZod,
165}