8000 chore: refactor current `relationships.sql` to `relationships_old.sql` · shenmirs/postgres-meta@2f92d12 · GitHub
[go: up one dir, main page]
Skip to content

Commit 2f92d12

Browse files
soedirgosoedirgo
committed
chore: refactor current relationships.sql to relationships_old.sql 
1 parent 68b7150 commit 2f92d12

File tree

5 files changed

+84
-29
lines changed

5 files changed

+84
-29
lines changed

‎src/lib/PostgresMetaTables.ts

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1,7 +1,7 @@
11
import { ident, literal } from 'pg-format'
22
import { DEFAULT_SYSTEM_SCHEMAS } from './constants.js'
33
import { coalesceRowsToArray, filterByList } from './helpers.js'
4-
import { columnsSql, primaryKeysSql, relationshipsSql, tablesSql } from './sql/index.js'
4+
import { columnsSql, primaryKeysSql, relationshipsOldSql, tablesSql } from './sql/index.js'
55
import {
66
PostgresMetaResult,
77
PostgresTable,
@@ -251,7 +251,7 @@ const generateEnrichedTablesSql = ({ includeColumns }: { includeColumns: boolean
251251
with tables as (${tablesSql})
252252
${includeColumns ? `, columns as (${columnsSql})` : ''}
253253
, primary_keys as (${primaryKeysSql})
254-
, relationships as (${relationshipsSql})
254+
, relationships as (${relationshipsOldSql})
255255
select
256256
*
257257
${includeColumns ? `, ${coalesceRowsToArray('columns', 'columns.table_id = tables.id')}` : ''}

‎src/lib/sql/index.ts

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -16,6 +16,7 @@ export const policiesSql = await readFile(join(__dirname, 'policies.sql'), 'utf-
1616
export const primaryKeysSql = await readFile(join(__dirname, 'primary_keys.sql'), 'utf-8')
1717
export const publicationsSql = await readFile(join(__dirname, 'publications.sql'), 'utf-8')
1818
export const relationshipsSql = await readFile(join(__dirname, 'relationships.sql'), 'utf-8')
19+
export const relationshipsOldSql = await readFile(join(__dirname, 'relationships_old.sql'), 'utf-8')
1920
export const rolesSql = await readFile(join(__dirname, 'roles.sql'), 'utf-8')
2021
export const schemasSql = await readFile(join(__dirname, 'schemas.sql'), 'utf-8')
2122
export const tablesSql = await readFile(join(__dirname, 'tables.sql'), 'utf-8')

‎src/lib/sql/relationships.sql

Lines changed: 44 additions & 25 deletions
Original file line numberDiff line numberDiff line change
@@ -1,25 +1,44 @@
1-
SELECT
2-
c.oid :: int8 AS id,
3-
c.conname AS constraint_name,
4-
nsa.nspname AS source_schema,
5-
csa.relname AS source_table_name,
6-
sa.attname AS source_column_name,
7-
nta.nspname AS target_table_schema,
8-
cta.relname AS target_table_name,
9-
ta.attname AS target_column_name
10-
FROM
11-
pg_constraint c
12-
JOIN (
13-
pg_attribute sa
14-
JOIN pg_class csa ON sa.attrelid = csa.oid
15-
JOIN pg_namespace nsa ON csa.relnamespace = nsa.oid
16-
) ON sa.attrelid = c.conrelid
17-
AND sa.attnum = ANY (c.conkey)
18-
JOIN (
19-
pg_attribute ta
20-
JOIN pg_class cta ON ta.attrelid = cta.oid
21-
JOIN pg_namespace nta ON cta.relnamespace = nta.oid
22-
) ON ta.attrelid = c.confrelid
23-
AND ta.attnum = ANY (c.confkey)
24-
WHERE
25-
c.contype = 'f'
1+
-- Adapted from
2+
-- https://github.com/PostgREST/postgrest/blob/f9f0f79fa914ac00c11fbf7f4c558e14821e67e2/src/PostgREST/SchemaCache.hs#L722
3+
WITH
4+
pks_uniques_cols AS (
5+
SELECT
6+
connamespace,
7+
conrelid,
8+
jsonb_agg(column_info.cols) as cols
9+
FROM pg_constraint
10+
JOIN lateral (
11+
SELECT array_agg(cols.attname order by cols.attnum) as cols
12+
FROM ( select unnest(conkey) as col) _
13+
JOIN pg_attribute cols on cols.attrelid = conrelid and cols.attnum = col
14+
) column_info ON TRUE
15+
WHERE
16+
contype IN ('p', 'u') and
17+
connamespace::regnamespace::text <> 'pg_catalog'
18+
GROUP BY connamespace, conrelid
19+
)
20+
SELECT
21+
traint.oid AS id,
22+
traint.conname AS f 67E6 oreign_key_name,
23+
ns1.nspname AS schema,
24+
tab.relname AS relation,
25+
column_info.cols AS columns,
26+
ns2.nspname AS referenced_schema,
27+
other.relname AS referenced_relation,
28+
column_info.refs AS referenced_columns
29+
FROM pg_constraint traint
30+
JOIN LATERAL (
31+
SELECT
32+
array_agg(row(cols.attname, refs.attname) order by ord) AS cols_and_fcols,
33+
jsonb_agg(cols.attname order by ord) AS cols,
34+
jsonb_agg(refs.attname order by ord) AS refs
35+
FROM unnest(traint.conkey, traint.confkey) WITH ORDINALITY AS _(col, ref, ord)
36+
JOIN pg_attribute cols ON cols.attrelid = traint.conrelid AND cols.attnum = col
37+
JOIN pg_attribute refs ON refs.attrelid = traint.confrelid AND refs.attnum = ref
38+
) AS column_info ON TRUE
39+
JOIN pg_namespace ns1 ON ns1.oid = traint.connamespace
40+
JOIN pg_class tab ON tab.oid = traint.conrelid
41+
JOIN pg_class other ON other.oid = traint.confrelid
42+
JOIN pg_namespace ns2 ON ns2.oid = other.relnamespace
43+
LEFT JOIN pks_uniques_cols pks_uqs ON pks_uqs.connamespace = traint.connamespace AND pks_uqs.conrelid = traint.conrelid
44+
WHERE traint.contype = 'f'

‎src/lib/sql/relationships_old.sql

Lines changed: 25 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,25 @@
1+
SELECT
2+
c.oid :: int8 AS id,
3+
c.conname AS constraint_name,
4+
nsa.nspname AS source_schema,
5+
csa.relname AS source_table_name,
6+
sa.attname AS source_column_name,
7+
nta.nspname AS target_table_schema,
8+
cta.relname AS target_table_name,
9+
ta.attname AS target_column_name
10+
FROM
11+
pg_constraint c
12+
JOIN (
13+
pg_attribute sa
14+
JOIN pg_class csa ON sa.attrelid = csa.oid
15+
JOIN pg_namespace nsa ON csa.relnamespace = nsa.oid
16+
) ON sa.attrelid = c.conrelid
17+
AND sa.attnum = ANY (c.conkey)
18+
JOIN (
19+
pg_attribute ta
20+
JOIN pg_class cta ON ta.attrelid = cta.oid
21+
JOIN pg_namespace nta ON cta.relnamespace = nta.oid
22+
) ON ta.attrelid = c.confrelid
23+
AND ta.attnum = ANY (c.confkey)
24+
WHERE
25+
c.contype = 'f'

‎src/lib/types.ts

Lines changed: 12 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -186,7 +186,7 @@ export const postgresPublicationSchema = Type.Object({
186186
})
187187
export type PostgresPublication = Static<typeof postgresPublicationSchema>
188188

189-
export const postgresRelationshipSchema = Type.Object({
189+
export const postgresRelationshipOldSchema = Type.Object({
190190
id: Type.Integer(),
191191
constraint_name: Type.String(),
192192
source_schema: Type.String(),
@@ -196,6 +196,16 @@ export const postgresRelationshipSchema = Type.Object({
196196
target_table_name: Type.String(),
197197
target_column_name: Type.String(),
198198
})
199+
export const postgresRelationshipSchema = Type.Object({
200+
id: Type.Integer(),
201+
foreign_key_name: Type.String(),
202+
schema: Type.String(),
203+
relation: Type.String(),
204+
columns: Type.Array(Type.String()),
205+
referenced_schema: Type.String(),
206+
referenced_relation: Type.String(),
207+
referenced_columns: Type.Array(Type.String()),
208+
})
199209
export type PostgresRelationship = Static<typeof postgresRelationshipSchema>
200210

201211
export const PostgresMetaRoleConfigSchema = Type.Object({
@@ -296,7 +306,7 @@ export const postgresTableSchema = Type.Object({
296306
comment: Type.Union([Type.String(), Type.Null()]),
297307
columns: Type.Optional(Type.Array(postgresColumnSchema)),
298308
primary_keys: Type.Array(postgresPrimaryKeySchema),
299-
relationships: Type.Array(postgresRelationshipSchema),
309+
relationships: Type.Array(postgresRelationshipOldSchema),
300310
})
301311
export type PostgresTable = Static<typeof postgresTableSchema>
302312

0 commit comments

Comments
 (0)
0