Gráfico del esquema de una base de datos
Hace unos dias escribi una entrada sobre como generar un gráfico de las llaves foráneas de una base de datos. El método utilizado fue escribir una consulta SQL que utilizando datos contenidos en el esquema information_schema generase una salida que se pudiese utilizar con Graphviz para generar un gráfico.
Hoy tenia ganas de seguir “jugando” con este tema y probar nuevas posibilidades. Partiendo de la consulta SQL utilizada en la entrada anterior, vamos a modificarla para generar un gráfico con todas las tablas contenidas en una base de datos y las relaciones entra ellas.
Esta consulta SQL solo funciona con PostgreSQL 9.0 o posterior y tiene en cuenta si existen multiples esquemas (schemas) en la base de datos en donde la ejecutemos. Al contrario que la consulta en la entrada anterior, esta genera todo lo necesario por Graphviz, asi que no tendremos que copiar/pegar y editar ficheros para obtener nuestro gráfico.
La consulta SQL de hoy es esta:
SELECT
E'digraph database_schema {
concentrate=true;
nodesep=1;
graph [
overlap=false
];
\n' ||
(SELECT string_agg('"' || a.table_schema || '.' || a.table_name || E'" [\n' ||
'label = <<TABLE BORDER="0" CELLBORDER="1" CELLSPACING="0" CELLPADDING="4">' ||
(SELECT '<TR><TD BGCOLOR="grey" PORT="0"><B>' || a.table_schema || '.' || a.table_name || '</B></TD></TR>'
|| string_agg('<TR><TD BGCOLOR="white" PORT="' || ordinal_position || '" ALIGN="LEFT">' || column_name || ' (' || upper(data_type) || ')</TD></TR>','')
FROM information_schema.columns
WHERE table_schema NOT IN ('pg_catalog','information_schema')
AND table_schema = a.table_schema
AND table_name = a.table_name) ||
E' </TABLE>>, shape = none];\n\n','')
FROM information_schema.tables a
WHERE a.table_schema NOT IN ('pg_catalog','information_schema')
AND a.table_type = 'BASE TABLE') ||
(SELECT string_agg(output,'')
FROM
(
SELECT DISTINCT ON ('"' || fkey_table_schema || '.' || fkey_table || '":' || fkey_position || ' -> "' || pkey_table_schema || '.' || pkey_table || '":' || pkey_position)
'"' || fkey_table_schema || '.' || fkey_table || '":' || fkey_position || ' -> "' || pkey_table_schema || '.' || pkey_table || '":' || pkey_position || E';\n' AS output
FROM
(
SELECT
a.constraint_name,
b.table_schema AS fkey_table_schema,
b.table_name AS fkey_table,
b.column_name AS fkey_column,
c.ordinal_position AS fkey_position
FROM information_schema.referential_constraints a
INNER JOIN information_schema.key_column_usage b ON a.constraint_name = b.constraint_name
INNER JOIN information_schema.columns c ON (b.column_name = c.column_name AND b.table_name = c.table_name)
) AS fkey_constraints
INNER JOIN
(
SELECT
a.unique_constraint_name,
b.table_schema AS pkey_table_schema,
b.table_name AS pkey_table,
b.column_name AS pkey_column,
c.ordinal_position AS pkey_position,
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
INNER JOIN information_schema.columns c ON (b.column_name = c.column_name AND b.table_name = c.table_name)
) AS pkey_constraints
ON fkey_constraints.constraint_name = pkey_constraints.constraint_name) AS node_relations
) ||
E'\n}\n';
La podeis grabar en un fichero llamado, por ejemplo, relaciones_tablas.sql y ejecutar este comando para obtener vuestro gráfico:
psql -At "tu_base_de_datos" < relaciones_tablas.sql | dot -Tpng -o relaciones_tablas.png
El resultado que yo he conseguido ejecutando este comando en la base de datos de NAV - Network Administration Visualized es el siguiente:
