konnostables.ts99 lines · main
| 1 | import { safeSql } from '../pg-format' |
| 2 | |
| 3 | export const TABLES_SQL = /* SQL */ safeSql` |
| 4 | SELECT |
| 5 | c.oid :: int8 AS id, |
| 6 | nc.nspname AS schema, |
| 7 | c.relname AS name, |
| 8 | c.relrowsecurity AS rls_enabled, |
| 9 | c.relforcerowsecurity AS rls_forced, |
| 10 | CASE |
| 11 | WHEN c.relreplident = 'd' THEN 'DEFAULT' |
| 12 | WHEN c.relreplident = 'i' THEN 'INDEX' |
| 13 | WHEN c.relreplident = 'f' THEN 'FULL' |
| 14 | ELSE 'NOTHING' |
| 15 | END AS replica_identity, |
| 16 | pg_total_relation_size(format('%I.%I', nc.nspname, c.relname)) :: int8 AS bytes, |
| 17 | pg_size_pretty( |
| 18 | pg_total_relation_size(format('%I.%I', nc.nspname, c.relname)) |
| 19 | ) AS size, |
| 20 | pg_stat_get_live_tuples(c.oid) AS live_rows_estimate, |
| 21 | pg_stat_get_dead_tuples(c.oid) AS dead_rows_estimate, |
| 22 | obj_description(c.oid) AS comment, |
| 23 | coalesce(pk.primary_keys, '[]') as primary_keys, |
| 24 | coalesce( |
| 25 | jsonb_agg(relationships) filter (where relationships is not null), |
| 26 | '[]' |
| 27 | ) as relationships |
| 28 | FROM |
| 29 | pg_namespace nc |
| 30 | JOIN pg_class c ON nc.oid = c.relnamespace |
| 31 | left join ( |
| 32 | select |
| 33 | c.oid::int8 as table_id, |
| 34 | jsonb_agg( |
| 35 | jsonb_build_object( |
| 36 | 'table_id', c.oid::int8, |
| 37 | 'schema', n.nspname, |
| 38 | 'table_name', c.relname, |
| 39 | 'name', a.attname |
| 40 | ) |
| 41 | order by array_position(i.indkey, a.attnum) |
| 42 | ) as primary_keys |
| 43 | from |
| 44 | pg_index i |
| 45 | join pg_class c on i.indrelid = c.oid |
| 46 | join pg_namespace n on c.relnamespace = n.oid |
| 47 | join pg_attribute a on a.attrelid = c.oid and a.attnum = any(i.indkey) |
| 48 | where |
| 49 | i.indisprimary |
| 50 | group by c.oid |
| 51 | ) as pk |
| 52 | on pk.table_id = c.oid |
| 53 | left join ( |
| 54 | select |
| 55 | c.oid :: int8 as id, |
| 56 | c.conname as constraint_name, |
| 57 | nsa.nspname as source_schema, |
| 58 | csa.relname as source_table_name, |
| 59 | sa.attname as source_column_name, |
| 60 | nta.nspname as target_table_schema, |
| 61 | cta.relname as target_table_name, |
| 62 | ta.attname as target_column_name |
| 63 | from |
| 64 | pg_constraint c |
| 65 | join ( |
| 66 | pg_attribute sa |
| 67 | join pg_class csa on sa.attrelid = csa.oid |
| 68 | join pg_namespace nsa on csa.relnamespace = nsa.oid |
| 69 | ) on sa.attrelid = c.conrelid and sa.attnum = any (c.conkey) |
| 70 | join ( |
| 71 | pg_attribute ta |
| 72 | join pg_class cta on ta.attrelid = cta.oid |
| 73 | join pg_namespace nta on cta.relnamespace = nta.oid |
| 74 | ) on ta.attrelid = c.confrelid and ta.attnum = any (c.confkey) |
| 75 | where |
| 76 | c.contype = 'f' |
| 77 | ) as relationships |
| 78 | on (relationships.source_schema = nc.nspname and relationships.source_table_name = c.relname) |
| 79 | or (relationships.target_table_schema = nc.nspname and relationships.target_table_name = c.relname) |
| 80 | WHERE |
| 81 | c.relkind IN ('r', 'p') |
| 82 | AND NOT pg_is_other_temp_schema(nc.oid) |
| 83 | AND ( |
| 84 | pg_has_role(c.relowner, 'USAGE') |
| 85 | OR has_table_privilege( |
| 86 | c.oid, |
| 87 | 'SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER' |
| 88 | ) |
| 89 | OR has_any_column_privilege(c.oid, 'SELECT, INSERT, UPDATE, REFERENCES') |
| 90 | ) |
| 91 | group by |
| 92 | c.oid, |
| 93 | c.relname, |
| 94 | c.relrowsecurity, |
| 95 | c.relforcerowsecurity, |
| 96 | c.relreplident, |
| 97 | nc.nspname, |
| 98 | pk.primary_keys |
| 99 | ` |