konnosDashboard
table.ts335 lines · main
1import { ident, joinSqlFragments, literal, safeSql, type SafeSqlFragment } from '../../../pg-format'
2
3export const getDuplicateTableSQL = ({
4 comment,
5 duplicatedTableName,
6 sourceTableName,
7 sourceTableSchema,
8}: {
9 comment?: string | null
10 duplicatedTableName: string
11 sourceTableName: string
12 sourceTableSchema: string
13}): SafeSqlFragment => {
14 const createSql = safeSql`CREATE TABLE ${ident(sourceTableSchema)}.${ident(duplicatedTableName)} (LIKE ${ident(sourceTableSchema)}.${ident(sourceTableName)} INCLUDING ALL);`
15 const commentSql =
16 comment != undefined
17 ? safeSql`comment on table ${ident(sourceTableSchema)}.${ident(duplicatedTableName)} is ${literal(comment)};`
18 : safeSql``
19 return joinSqlFragments([createSql, commentSql], '\n')
20}
21
22export const getDuplicateRowsSQL = ({
23 duplicatedTableName,
24 sourceTableName,
25 sourceTableSchema,
26}: {
27 duplicatedTableName: string
28 sourceTableName: string
29 sourceTableSchema: string
30}): SafeSqlFragment => {
31 return safeSql`INSERT INTO ${ident(sourceTableSchema)}.${ident(duplicatedTableName)} SELECT * FROM ${ident(sourceTableSchema)}.${ident(sourceTableName)};`
32}
33
34export const getTableEditorSql = ({ id }: { id?: number }): SafeSqlFragment => {
35 if (!id) return safeSql``
36
37 return safeSql`
38 with base_table_info as (
39 select
40 c.oid::int8 as id,
41 nc.nspname as schema,
42 c.relname as name,
43 c.relkind,
44 c.relrowsecurity as rls_enabled,
45 c.relforcerowsecurity as rls_forced,
46 c.relreplident,
47 c.relowner,
48 obj_description(c.oid) as comment,
49 fs.srvname as foreign_server_name,
50 fdw.fdwname as foreign_data_wrapper_name,
51 fdw_handler.proname as foreign_data_wrapper_handler
52 from pg_class c
53 join pg_namespace nc on nc.oid = c.relnamespace
54 left join pg_foreign_table ft on ft.ftrelid = c.oid
55 left join pg_foreign_server fs on fs.oid = ft.ftserver
56 left join pg_foreign_data_wrapper fdw on fdw.oid = fs.srvfdw
57 left join pg_proc fdw_handler on fdw.fdwhandler = fdw_handler.oid
58 where c.oid = ${literal(id)}
59 and not pg_is_other_temp_schema(nc.oid)
60 and (
61 pg_has_role(c.relowner, 'USAGE')
62 or has_table_privilege(
63 c.oid,
64 'SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER'
65 )
66 or has_any_column_privilege(c.oid, 'SELECT, INSERT, UPDATE, REFERENCES')
67 )
68 ),
69 table_stats as (
70 select
71 b.id,
72 case
73 when b.relreplident = 'd' then 'DEFAULT'
74 when b.relreplident = 'i' then 'INDEX'
75 when b.relreplident = 'f' then 'FULL'
76 else 'NOTHING'
77 end as replica_identity,
78 pg_total_relation_size(format('%I.%I', b.schema, b.name))::int8 as bytes,
79 pg_size_pretty(pg_total_relation_size(format('%I.%I', b.schema, b.name))) as size,
80 pg_stat_get_live_tuples(b.id) as live_rows_estimate,
81 pg_stat_get_dead_tuples(b.id) as dead_rows_estimate
82 from base_table_info b
83 where b.relkind in ('r', 'p')
84 ),
85 primary_keys as (
86 select
87 i.indrelid as table_id,
88 jsonb_agg(
89 jsonb_build_object(
90 'schema', n.nspname,
91 'table_name', c.relname,
92 'table_id', i.indrelid::int8,
93 'name', a.attname
94 )
95 order by array_position(i.indkey, a.attnum)
96 ) as primary_keys
97 from pg_index i
98 join pg_class c on i.indrelid = c.oid
99 join pg_namespace n on c.relnamespace = n.oid
100 join pg_attribute a on a.attrelid = c.oid and a.attnum = any(i.indkey)
101 where i.indisprimary
102 group by i.indrelid
103 ),
104 index_cols as (
105 select
106 i.indrelid as table_id,
107 i.indkey,
108 array_agg(
109 a.attname
110 order by array_position(i.indkey, a.attnum)
111 ) as columns
112 from pg_index i
113 join pg_class c on i.indrelid = c.oid
114 join pg_attribute a on a.attrelid = c.oid
115 and a.attnum = any(i.indkey)
116 where i.indisunique
117 and i.indisprimary = false
118 group by i.indrelid, i.indkey
119 ),
120 unique_indexes as (
121 select
122 ic.table_id,
123 jsonb_agg(
124 jsonb_build_object(
125 'schema', n.nspname,
126 'table_name', c.relname,
127 'table_id', ic.table_id::int8,
128 'columns', ic.columns
129 )
130 ) as unique_indexes
131 from index_cols ic
132 join pg_class c on c.oid = ic.table_id
133 join pg_namespace n on n.oid = c.relnamespace
134 group by ic.table_id
135 ),
136 relationships as (
137 select
138 c.conrelid as source_id,
139 c.confrelid as target_id,
140 jsonb_build_object(
141 'id', c.oid::int8,
142 'constraint_name', c.conname,
143 'deletion_action', c.confdeltype,
144 'update_action', c.confupdtype,
145 'source_schema', nsa.nspname,
146 'source_table_name', csa.relname,
147 'source_column_name', sa.attname,
148 'target_table_schema', nta.nspname,
149 'target_table_name', cta.relname,
150 'target_column_name', ta.attname
151 ) as rel_info
152 from pg_constraint c
153 join pg_class csa on c.conrelid = csa.oid
154 join pg_namespace nsa on csa.relnamespace = nsa.oid
155 join pg_attribute sa on (sa.attrelid = c.conrelid and sa.attnum = any(c.conkey))
156 join pg_class cta on c.confrelid = cta.oid
157 join pg_namespace nta on cta.relnamespace = nta.oid
158 join pg_attribute ta on (ta.attrelid = c.confrelid and ta.attnum = any(c.confkey))
159 where c.contype = 'f'
160 ),
161 columns as (
162 select
163 a.attrelid as table_id,
164 jsonb_agg(jsonb_build_object(
165 'id', (a.attrelid || '.' || a.attnum),
166 'table_id', c.oid::int8,
167 'schema', nc.nspname,
168 'table', c.relname,
169 'ordinal_position', a.attnum,
170 'name', a.attname,
171 'default_value', case
172 when a.atthasdef then pg_get_expr(ad.adbin, ad.adrelid)
173 else null
174 end,
175 'data_type', case
176 when t.typtype = 'd' then
177 case
178 when bt.typelem <> 0::oid and bt.typlen = -1 then 'ARRAY'
179 when nbt.nspname = 'pg_catalog' then format_type(t.typbasetype, null)
180 else 'USER-DEFINED'
181 end
182 else
183 case
184 when t.typelem <> 0::oid and t.typlen = -1 then 'ARRAY'
185 when nt.nspname = 'pg_catalog' then format_type(a.atttypid, null)
186 else 'USER-DEFINED'
187 end
188 end,
189 'format', coalesce(bt.typname, t.typname),
190 'format_schema', coalesce(nbt.nspname, nt.nspname),
191 'is_identity', a.attidentity in ('a', 'd'),
192 'identity_generation', case a.attidentity
193 when 'a' then 'ALWAYS'
194 when 'd' then 'BY DEFAULT'
195 else null
196 end,
197 'is_generated', a.attgenerated in ('s'),
198 'is_nullable', not (a.attnotnull or t.typtype = 'd' and t.typnotnull),
199 'is_updatable', (
200 b.relkind in ('r', 'p') or
201 (b.relkind in ('v', 'f') and pg_column_is_updatable(b.id, a.attnum, false))
202 ),
203 'is_unique', uniques.table_id is not null,
204 'check', check_constraints.definition,
205 'comment', col_description(c.oid, a.attnum),
206 'enums', coalesce(
207 (
208 select jsonb_agg(e.enumlabel order by e.enumsortorder)
209 from pg_catalog.pg_enum e
210 where e.enumtypid = coalesce(bt.oid, t.oid)
211 or e.enumtypid = coalesce(bt.typelem, t.typelem)
212 ),
213 '[]'::jsonb
214 )
215 ) order by a.attnum) as columns
216 from pg_attribute a
217 join base_table_info b on a.attrelid = b.id
218 join pg_class c on a.attrelid = c.oid
219 join pg_namespace nc on c.relnamespace = nc.oid
220 left join pg_attrdef ad on (a.attrelid = ad.adrelid and a.attnum = ad.adnum)
221 join pg_type t on a.atttypid = t.oid
222 join pg_namespace nt on t.typnamespace = nt.oid
223 left join pg_type bt on (t.typtype = 'd' and t.typbasetype = bt.oid)
224 left join pg_namespace nbt on bt.typnamespace = nbt.oid
225 left join (
226 select
227 conrelid as table_id,
228 conkey[1] as ordinal_position
229 from pg_catalog.pg_constraint
230 where contype = 'u' and cardinality(conkey) = 1
231 group by conrelid, conkey[1]
232 ) as uniques on uniques.table_id = a.attrelid and uniques.ordinal_position = a.attnum
233 left join (
234 select distinct on (conrelid, conkey[1])
235 conrelid as table_id,
236 conkey[1] as ordinal_position,
237 substring(
238 pg_get_constraintdef(oid, true),
239 8,
240 length(pg_get_constraintdef(oid, true)) - 8
241 ) as definition
242 from pg_constraint
243 where contype = 'c' and cardinality(conkey) = 1
244 order by conrelid, conkey[1], oid asc
245 ) as check_constraints on check_constraints.table_id = a.attrelid
246 and check_constraints.ordinal_position = a.attnum
247 where a.attnum > 0
248 and not a.attisdropped
249 group by a.attrelid
250 )
251 select
252 case b.relkind
253 when 'r' then jsonb_build_object(
254 'entity_type', b.relkind,
255 'id', b.id,
256 'schema', b.schema,
257 'name', b.name,
258 'rls_enabled', b.rls_enabled,
259 'rls_forced', b.rls_forced,
260 'replica_identity', ts.replica_identity,
261 'bytes', ts.bytes,
262 'size', ts.size,
263 'live_rows_estimate', ts.live_rows_estimate,
264 'dead_rows_estimate', ts.dead_rows_estimate,
265 'comment', b.comment,
266 'primary_keys', coalesce(pk.primary_keys, '[]'::jsonb),
267 'unique_indexes', coalesce(ui.unique_indexes, '[]'::jsonb),
268 'relationships', coalesce(
269 (select jsonb_agg(r.rel_info)
270 from relationships r
271 where r.source_id = b.id or r.target_id = b.id),
272 '[]'::jsonb
273 ),
274 'columns', coalesce(c.columns, '[]'::jsonb)
275 )
276 when 'p' then jsonb_build_object(
277 'entity_type', b.relkind,
278 'id', b.id,
279 'schema', b.schema,
280 'name', b.name,
281 'rls_enabled', b.rls_enabled,
282 'rls_forced', b.rls_forced,
283 'replica_identity', ts.replica_identity,
284 'bytes', ts.bytes,
285 'size', ts.size,
286 'live_rows_estimate', ts.live_rows_estimate,
287 'dead_rows_estimate', ts.dead_rows_estimate,
288 'comment', b.comment,
289 'primary_keys', coalesce(pk.primary_keys, '[]'::jsonb),
290 'unique_indexes', coalesce(ui.unique_indexes, '[]'::jsonb),
291 'relationships', coalesce(
292 (select jsonb_agg(r.rel_info)
293 from relationships r
294 where r.source_id = b.id or r.target_id = b.id),
295 '[]'::jsonb
296 ),
297 'columns', coalesce(c.columns, '[]'::jsonb)
298 )
299 when 'v' then jsonb_build_object(
300 'entity_type', b.relkind,
301 'id', b.id,
302 'schema', b.schema,
303 'name', b.name,
304 'is_updatable', (pg_relation_is_updatable(b.id, false) & 20) = 20,
305 'comment', b.comment,
306 'columns', coalesce(c.columns, '[]'::jsonb)
307 )
308 when 'm' then jsonb_build_object(
309 'entity_type', b.relkind,
310 'id', b.id,
311 'schema', b.schema,
312 'name', b.name,
313 'is_populated', true,
314 'comment', b.comment,
315 'columns', coalesce(c.columns, '[]'::jsonb)
316 )
317 when 'f' then jsonb_build_object(
318 'entity_type', b.relkind,
319 'id', b.id,
320 'schema', b.schema,
321 'name', b.name,
322 'comment', b.comment,
323 'foreign_server_name', b.foreign_server_name,
324 'foreign_data_wrapper_name', b.foreign_data_wrapper_name,
325 'foreign_data_wrapper_handler', b.foreign_data_wrapper_handler,
326 'columns', coalesce(c.columns, '[]'::jsonb)
327 )
328 end as entity
329 from base_table_info b
330 left join table_stats ts on b.id = ts.id
331 left join primary_keys pk on b.id = pk.table_id
332 left join unique_indexes ui on b.id = ui.table_id
333 left join columns c on b.id = c.table_id;
334 `
335}