fks.zsh 5.39 KB
Newer Older
Dian Fay's avatar
Dian Fay committed
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
# fks: generate contextual entity-relationship diagrams in your terminal!
#
# syntax:
#   $ fks dbname [schemaname.][tablename]
#
# Passed only dbname, fks will plot the entire foreign-key graph which may not
# render at a usable resolution for databases with many related tables. Passed
# a table reference as well, fks will plot the subgraph of all tables linked
# by lineal dependence to the starting point. Indirect relationships of mixed
# directionality are excluded: from a, a -> b <- c will plot a and b only.
#
# requires:
#   graphviz
#   wezterm

Dian Fay's avatar
Dian Fay committed
16
function fks() {
Dian Fay's avatar
Dian Fay committed
17
18
19
20
21
  if [ -z "$1" ]; then
    echo "Syntax: fks dbname [schemaname.][tablename]"
    return 0
  fi

Dian Fay's avatar
Dian Fay committed
22
23
24
25
26
27
28
29
30
31
  local DIGRAPH_TEMPLATE='
    digraph {
      graph [
        bgcolor="transparent"
      ];

      node [
        color="#9c6b80"
        fontcolor="#f5eff3"
        fontname="Inconsolata LGC"
Dian Fay's avatar
Dian Fay committed
32
33
        shape="rect"
        style="rounded,filled"
Dian Fay's avatar
Dian Fay committed
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
      ];

      edge [
        color="#9ccbdd"
        dir="forward"
        arrowhead="normal"
      ];

      concentrate=true;

      %s
    }
  '

  local SCHEMA=public

  if [ -n "$2" ]; then
    local OBJPATH=(${(@s/./)2}) # split arg2 on .

    if [ "${#OBJPATH[@]}" -gt 1 ]; then
      local SCHEMA="${OBJPATH[1]}"
      local TABLE="${OBJPATH[2]}"
    else
      local TABLE="$2"
    fi

    QUERY="WITH RECURSIVE keys_dn AS (
      SELECT
        1 AS level,
        '$TABLE' COLLATE \"C\" AS origin,
        '$SCHEMA' COLLATE \"C\" AS origin_schema,
        '$TABLE' COLLATE \"C\" AS dependent,
        '$SCHEMA' COLLATE \"C\" AS dependent_schema
67
      UNION
Dian Fay's avatar
Dian Fay committed
68
69
70
71
72
73
74
      SELECT 2, originrel.relname::text, originns.nspname::text, dependentrel.relname::text, dependentns.nspname::text
      FROM pg_catalog.pg_constraint AS foreign_keys
      JOIN pg_catalog.pg_class AS originrel ON originrel.oid = foreign_keys.confrelid
      JOIN pg_catalog.pg_namespace AS originns ON originns.oid = originrel.relnamespace
      JOIN pg_catalog.pg_class AS dependentrel ON dependentrel.oid = foreign_keys.conrelid
      JOIN pg_catalog.pg_namespace AS dependentns ON dependentns.oid = dependentrel.relnamespace
      JOIN keys_dn ON keys_dn.origin_schema = dependentns.nspname AND keys_dn.origin = dependentrel.relname
Dian Fay's avatar
Dian Fay committed
75
      WHERE originrel.relispartition IS FALSE AND foreign_keys.contype = 'f'
Dian Fay's avatar
Dian Fay committed
76
77
78
79
80
81
82
    ), keys_up AS (
      SELECT
        1 AS level,
        '$TABLE' COLLATE \"C\" AS origin,
        '$SCHEMA' COLLATE \"C\" AS origin_schema,
        '$TABLE' COLLATE \"C\" AS dependent,
        '$SCHEMA' COLLATE \"C\" AS dependent_schema
83
      UNION
Dian Fay's avatar
Dian Fay committed
84
85
86
87
88
89
90
      SELECT 2, originrel.relname::text, originns.nspname::text, dependentrel.relname::text, dependentns.nspname::text
      FROM pg_catalog.pg_constraint AS foreign_keys
      JOIN pg_catalog.pg_class AS originrel ON originrel.oid = foreign_keys.confrelid
      JOIN pg_catalog.pg_namespace AS originns ON originns.oid = originrel.relnamespace
      JOIN pg_catalog.pg_class AS dependentrel ON dependentrel.oid = foreign_keys.conrelid
      JOIN pg_catalog.pg_namespace AS dependentns ON dependentns.oid = dependentrel.relnamespace
      JOIN keys_up ON keys_up.dependent_schema = originns.nspname AND keys_up.dependent = originrel.relname
Dian Fay's avatar
Dian Fay committed
91
      WHERE dependentrel.relispartition IS FALSE AND foreign_keys.contype = 'f'
Dian Fay's avatar
Dian Fay committed
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
    ), all_keys AS (
      SELECT origin, nullif(origin_schema, '$SCHEMA') AS origin_schema, dependent, nullif(dependent_schema, '$SCHEMA') AS dependent_schema
      FROM keys_dn WHERE level > 1
      UNION
      SELECT origin, nullif(origin_schema, '$SCHEMA'), dependent, nullif(dependent_schema, '$SCHEMA')
      FROM keys_up WHERE level > 1
    )
    SELECT '\"' || coalesce(origin_schema || '.', '') || origin || '\" -> \"' || coalesce(dependent_schema || '.', '') || dependent || '\"'
    FROM all_keys"
  else
    # TODO care about schemas in a full-db context?
    QUERY="SELECT originrel.relname || ' -> ' || dependentrel.relname
      FROM pg_catalog.pg_constraint AS foreign_keys
      JOIN pg_catalog.pg_class AS originrel ON originrel.oid = foreign_keys.confrelid
      JOIN pg_catalog.pg_class AS dependentrel ON dependentrel.oid = foreign_keys.conrelid
Dian Fay's avatar
Dian Fay committed
107
108
109
      WHERE originrel.relispartition IS FALSE
        AND dependentrel.relispartition IS FALSE
        AND foreign_keys.contype = 'f'"
Dian Fay's avatar
Dian Fay committed
110
111
112
113
114
115
116
117
  fi

  local DOT_DEFN=$(psql $1 --no-psqlrc --tuples-only -c "$QUERY")

  if [ -z "$DOT_DEFN" ]; then
    if [ -z "$TABLE" ]; then
      echo "no foreign keys found"
    else
Dian Fay's avatar
Dian Fay committed
118
      echo "$SCHEMA.$TABLE does not exist or has no foreign keys"
Dian Fay's avatar
Dian Fay committed
119
120
121
122
123
124
    fi

    return 1
  fi

  # echo "$DOT_DEFN"
125
126
127
128
129
130
131
132

  if [ -t 1 ]; then
    # we're sending output to the terminal
    printf "$DIGRAPH_TEMPLATE" "$DOT_DEFN" | dot -Tpng | wezterm imgcat
  else
    # we're in a pipe
    printf "$DIGRAPH_TEMPLATE" "$DOT_DEFN" | dot -Tpng
  fi
Dian Fay's avatar
Dian Fay committed
133
}
Dian Fay's avatar
Dian Fay committed
134
135
136
137

function _fks() {
  local curcontext="$curcontext" state line
  local dbquery="select datname from pg_database"
Dian Fay's avatar
Dian Fay committed
138
  local tblquery="select case when table_schema <> 'public' then table_schema || '.' || table_name else table_name end from information_schema.tables where table_schema not in ('pg_catalog', 'information_schema') and table_type = 'BASE TABLE'"
Dian Fay's avatar
Dian Fay committed
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
  typeset -A opt_args

  _arguments -C \
    "1:database:->db" \
    "2:table:->table"

  case "$state" in
    db)
      _alternative "databases:database:($(psql --no-psqlrc --tuples-only -c $dbquery))"
      ;;
    table)
      _alternative "tables:table:($(psql $words[2] --no-psqlrc --tuples-only -c $tblquery 2>/dev/null))"
      ;;
  esac
}

compdef _fks fks