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:
