Gráfico de llaves foráneas

Ayer, uno de los sistemas de monitorización de red que utilizamos en la universidad NAV - Network Administration Visualized tuvo problemas con una de las consultas DELETE que mandaba a la base de datos PostgreSQL que utiliza.

La base de datos que utiliza NAV es un poco complicada, con muchas claves foráneas, triggers y rules para garantizar la integridad de los datos. En el proceso de busqueda del fallo, una de las cosas que queria tener era una relación de todas las tablas y sus claves foráneas para hacerme una idea de como las diferentes tablas estaban relacionadas entre si. En una base de datos pequeña y sin muchas relaciones esto no seria un problema pero a medida que la cosa crece, se va complicando rápidamente.

Necesitaba algo rápido y fácil que me diera esta información sin tener que instalar ningún programa adicional, además solamente me interesaba la relación de claves foráneas y no una representación completa de la base de datos.

Y como no hay nada más rápido que una consola con psql y una consulta SQL apropiada, me puse manos a la obra para generar una consulta SQL que me generase una salida que pudiese utilizar con Graphviz, para crear un gráfico con las relaciones. En debian/ubuntu, Graphviz se puede instalar con: sudo apt-get install graphviz

La consulta que se me ocurrio es la siguiente:

SELECT DISTINCT ON ('"' || fkey_table || '" -> "' || pkey_table || '(' || pkey_column || ')";') 
'"' || fkey_table || '" -> "' || pkey_table || '(' || pkey_column || ')";'
FROM 
(
  SELECT a.constraint_name,
       b.column_name AS fkey_column,
       b.table_name AS fkey_table
  FROM information_schema.referential_constraints a
  INNER JOIN  information_schema.key_column_usage b ON a.constraint_name = b.constraint_name
) AS fkey_constraints

INNER JOIN

(
  SELECT a.unique_constraint_name,
       b.column_name AS pkey_column,
       b.table_name AS pkey_table,
       a.constraint_name
  FROM information_schema.referential_constraints a
  INNER JOIN  information_schema.constraint_column_usage b ON a.unique_constraint_name = b.constraint_name
) AS pkey_constraints

ON fkey_constraints.constraint_name = pkey_constraints.constraint_name;

Esta consulta te devuelve una lista con lineas del tipo:

"accountalertqueue" -> "account(id)";
"accountalertqueue" -> "account(id)";
"accountalertqueue" -> "alertq(alertqid)";
"accountalertqueue" -> "alertsubscription(id)";
"accountgroup_accounts" -> "account(id)";
"accountgroup_accounts" -> "accountgroup(id)";
...........

"type" -> "vendor(vendorid)";
"vlan" -> "nettype(nettypeid)";
"vlan" -> "org(orgid)";
"vlan" -> "usage(usageid)";

En donde el primer termino antes del simbolo “->” es el nombre de una tabla en vuestra base de datos y el segundo una clave foránea (tabla(columna)) definida en esta tabla.

Ahora lo único que nos queda es copiar y pegar el resultado en un editor de texto, añadirle un par de lineas extras, grabarlo en un fichero y ejecutar Graphviz sobre el mismo. Podemos grabar un fichero llamado claves_foraneas.dot con estos datos:

digraph fkey_pkey_relacion{ 

 K=1;

 Aqui vendria la salida de ejecutar 
 el comando SQL en vustro sistema

}

y ejecutar este comando para generar un fichero PNG:

 fdp -Tpng -o claves_foraneas.png claves_foraneas.dot 

El resultado que conseguí lo podeis ver a continuación: