En SQL Server 2017 se incorpora por primera vez en el motor de SQL Server el soporte nativo de grafos. En el SolidQ Summit 2018 impartí una sesión dedicada precisamente a mostrar en detalle esta nueva funcionalidad. En este post vamos a tratar cómo podemos añadir un workaround a una limitación de integridad que no se incluye por defecto y nos parece de bastante importancia.
Como la funcionalidad de grafos es bastante reciente haremos una pequeña introducción de forma que podamos todos llegar al punto problemático con los conceptos básicos claros. La funcionalidad de grafos en SQL Server 2017 añade dos elementos nuevos, los nodos y las aristas. Desde el punto de vista de implementación se apoyan en una estructura tabular (una tabla de toda la vida) con ciertas columnas extra (algunas visibles y otras no) que se añaden de forma automática. La creación de este tipo de elementos es sencilla ya que solo tenemos que crear nuestra tabla y añadir al final “AS NODE” o “AS EDGE” según corresponda.
Comenzaremos nuestro ejemplo creando una base de datos y añadiendo una entidad Personas que representará a nodos de nuestro grafo.
USE master GO DROP DATABASE IF EXISTS graph GO CREATE DATABASE graph GO USE graph GO CREATE TABLE [dbo].[Personas]( [PersonaId] [int] PRIMARY KEY, [Nombre] varchar(100), [Apellidos] varchar(200) ) AS NODE GO
La inserción de nuevos nodos es tan sencilla como la inserción de filas en una tabla. Podemos acceder a la información con una SELECT tradicional:
INSERT INTO dbo.Personas (PersonaId,Nombre,Apellidos) VALUES (1,'Francisco', 'García Rodríguez'), (2,'Antonio','Ruiz Martínez') GO SELECT * FROM dbo.Personas
A continuación, vamos a crear una relación llamada “Conoce” que estará representada como una arista en nuestro grafo. Como solamente tenemos “Personas”, la arista relacionará “Personas“ con “Personas” que son conocidas entre sí. Para crear la arista utilizaremos “AS EDGE” al final del comando CREATE TABLE:
CREATE TABLE dbo.Conoce AS EDGE; GO
Mediante un cross join entre las tabla “Personas” y ella misma insertaremos una relación entre cada uno de los nodos de forma que todos se conozcan entre sí. Los valores que tendremos que insertar son los que nos devuelve la pseudocolumna $nodeid:
-- Todos se conocen a todos INSERT INTO dbo.Conoce SELECT p1.$node_id origen,p2.$node_id destino FROM dbo.Personas p1, dbo.Personas p2 GO SELECT * FROM Conoce GO
Vamos a añadir el nodo “Lugares” y una nueva relación “Nacido” que nos permita representar en qué lugar han nacido las personas:
CREATE TABLE [dbo].[Lugares]( [LugarId] [int] PRIMARY KEY, [Nombre] varchar(100) ) AS NODE INSERT INTO dbo.Lugares (LugarId,Nombre) VALUES (1,'España'), (2,'Francia'), (3,'Alemania') CREATE TABLE dbo.Nacido AS EDGE;
Insertaremos el lugar de nacimiento de Francisco y el lugar de nacimiento de Antonio. Para ello utilizaremos subconsultas para obtener los $node_id (aunque podríamos haberlos obtenido de otras formas):
-- Lugar de nacimiento de Francisco INSERT INTO dbo.Nacido SELECT (select p1.$node_id from dbo.Personas p1 where p1.PersonaId=1),(select l1.$node_id from dbo.Lugares l1 where LugarId=1) -- Lugar de nacimiento de Antonio INSERT INTO dbo.Nacido SELECT (select p1.$node_id from dbo.Personas p1 where p1.PersonaId=2),(select l1.$node_id from dbo.Lugares l1 where LugarId=2) select * from dbo.Nacido
Uno de los puntos fuertes de los grafos es que podemos reutilizar las relaciones entre nodos de distinto tipo. Para mostrar esto vamos a utilizar la relación “Conoce” para representar que ciertas “Personas” conocen ciertos “Lugares” (y no solo a otras personas):
-- Reutilizamos la relacion conoce para indicar que ciertas personas conocen lugares además de a otras personas -- Francisco conoce Alemania INSERT INTO dbo.Conoce SELECT (select p1.$node_id from dbo.Personas p1 where p1.PersonaId=1),(select l1.$node_id from dbo.Lugares l1 where LugarId=3) -- Antonio conoce España INSERT INTO dbo.Conoce SELECT (select p1.$node_id from dbo.Personas p1 where p1.PersonaId=2),(select l1.$node_id from dbo.Lugares l1 where LugarId=1) select * from dbo.Conoce
Podemos ver como en el “nodo destino” de la arista tenemos nodos de distinto tipo, “Personas” y “Lugares”. Cuando insertamos datos en las aristas, necesitamos insertar un $node_id que sea válido, el cual se valida en el momento de inserción y obtendríamos un error si fuese incorrecto (se mantiene la integridad).
Como suele pasar con las primeras versiones de funcionalidades cuando se introducen en SQL Server tenemos ciertas limitaciones:
Entre ellas vemos que las aristas tienen una limitación que implica no poder realizar operaciones de modificación sobre ellas, debiendo ser simuladas mediante un delete+insert dentro de una transacción. Sin embargo, lo que nos sorprendió negativamente fue que mientras que a la hora de insertar aristas se valida que los $node_id existan, cuando borramos nodos no se valida que dejen aristas huérfanas (se rompe la integridad). Podemos ver como si borramos todas las “Personas” y “Lugares” no obtenemos ningún error y quedan las ariastas apuntando a nodos ya no existentes:
-- Podemos borrar datos de los nodos aunque rompan la integridad de las aristas y queden huérfanas set xact_abort off begin tran delete from dbo.Personas delete from dbo.Lugares select * from dbo.Personas select * from dbo.Lugares select * from dbo.Conoce select * from dbo.Nacido rollback tran
Para poder mantener la integridad en este tipo de casos tendremos que hacer uso de una relación de integridad programática utilizando triggers que nos controlen este tipo de situaciones. Por ejemplo, para evitar el problema de los borrados de Personas podríamos añadir un trigger como el siguiente:
-- Para evitar esto, podemos utilizar triggers en aquellas tablas que sean de tipo nodo que detecten las posibles relaciones e impidan el borrado si existen relaciones
CREATE OR ALTER TRIGGER tr_Persona ON dbo.Personas
AFTER DELETE AS
BEGIN
DECLARE @Count int;
SET @Count = @@ROWCOUNT;
IF @Count = 0
RETURN;
SET NOCOUNT ON;
BEGIN TRY
DECLARE @objectid int = object_id('dbo.Personas')
-- Comprobar aristas y lanzar errores si tenemos dependencias
-- dbo.Conoce
IF EXISTS (
select 1 from
(
select $to_id toid, $from_id fromid,* from dbo.Conoce
where OBJECT_ID_FROM_NODE_ID($to_id)=@objectid or OBJECT_ID_FROM_NODE_ID($from_id)=@objectid
) a
inner join deleted P on P.$node_id=toid or P.$node_id=fromid
)
RAISERROR ('Error al borrar en dbo.Persona por FK arista dbo.Conoce',16,1)
-- dbo.Nacido
IF EXISTS (
select 1 from
(
select $to_id toid, $from_id fromid,* from dbo.Nacido
where OBJECT_ID_FROM_NODE_ID($to_id)=@objectid or OBJECT_ID_FROM_NODE_ID($from_id)=@objectid
) a
inner join deleted P on P.$node_id=toid or P.$node_id=fromid
)
RAISERROR ('Error al borrar en dbo.Persona por FK arista dbo.Nacido',16,1)
END TRY
BEGIN CATCH
-- Rollback
IF @@TRANCOUNT > 0
BEGIN
ROLLBACK TRANSACTION;
THROW;
END
END CATCH;
END;
GO
En el trigger tenemos que realizar ciertas acciones que no parecen muy intuitivas pero que están causadas por la propia estructura interna de las aristas. En las aristas, además del identificador del nodo en cuestión (la fila a la que refiere) se incluye un object_id que representa la tabla de nodos donde se encuentra.
Por tanto, una vez esté el trigger creado, para poder borrar una “Persona” tendremos que comprobar si en aquellas aristas donde intervengan (“Conoce” y “Nacido”) tenemos alguna arista que incluya al nodo que queremos borrar. Si existe cualquiera de ellas, generaremos un error indicando la razón por la que no se puede realizar el borrado.
Sin intentamos lanzar la operación anterior con el trigger creado nos daría el siguiente error:
Para poder borrar a Francisco por ejemplo tendríamos que borrar las aristas “Conoce”, las aristas “Nacido” y luego el nodo Francisco:
-- Para borrar a Francisco tendríamos que borrar primero sus aristas set xact_abort off begin tran declare @francisco_node_id nvarchar(2000) = (select $node_id from dbo.personas where nombre='Francisco') -- Borramos aristas Conoce delete from Conoce where $from_id=@francisco_node_id or $to_id=@francisco_node_id -- Borramos aristas Nacido delete from Nacido where $from_id=@francisco_node_id or $to_id=@francisco_node_id -- Borramos el nodo delete from dbo.Personas where nombre='Francisco' rollback tran
Como mantener el código de los triggers puede ser bastante complicado si cambian las aristas con las que se relacionan los nodos o simplemente si tenemos muchos nodos hemos desarrollado un código más genérico que nos permitirá reutilizarlo entre nodos distintos.
-- Para evitar esto, podemos utilizar triggers en aquellas tablas que sean de tipo nodo que detecten las posibles relaciones e impidan el borrado si existen relaciones
CREATE OR ALTER TRIGGER tr_Persona ON dbo.Personas
AFTER DELETE AS
BEGIN
DECLARE @Count int;
SET @Count = @@ROWCOUNT;
IF @Count = 0
RETURN;
SET NOCOUNT ON;
BEGIN TRY
-- Volcamos a temporal para tener acceso desde código dinámico
SELECT * into #deleted from deleted
-- Obtener ids dinámicamente
DECLARE @object_id INT = (SELECT T.object_id FROM sys.objects P JOIN sys.objects T ON P.parent_object_id = T.object_id WHERE P.object_id = @@procid)
DECLARE @schema_id INT = (SELECT T.schema_id FROM sys.objects P JOIN sys.objects T ON P.parent_object_id = T.object_id WHERE P.object_id = @@procid)
DECLARE @node_id nvarchar(2000) = (SELECT name FROM syscolumns WHERE id = @object_id and name like '%node_id%')
-- Comprobar aristas y lanzar errores si tenemos dependencias
declare @sql nvarchar(max)=
(
select
string_agg('IF EXISTS (
select 1 from
(
select $to_id toid, $from_id fromid,* from ' + quotename(schema_name(t.schema_id)) + '.' + quotename(object_name(t.object_id)) + '
where OBJECT_ID_FROM_NODE_ID($to_id)=' + convert(varchar(max),@object_id) + ' or OBJECT_ID_FROM_NODE_ID($from_id)=' + convert(varchar(max),@object_id) + '
) a
inner join #deleted P on P.' + quotename(@node_id) + '=toid or P.' + quotename(@node_id) + '=fromid
)
RAISERROR (''Error al borrar en ' + quotename(schema_name(@schema_id))+ '.' + quotename(object_name(@object_id)) + ' por FK arista '+ quotename(schema_name(t.schema_id)) + '.' + quotename(object_name(t.object_id)) + ' '',16,1)',' ')
from sys.tables t where is_edge=1
)
exec(@sql)
drop table #deleted
END TRY
BEGIN CATCH
-- Rollback
IF @@TRANCOUNT > 0
BEGIN
ROLLBACK TRANSACTION;
THROW;
END
END CATCH;
END;
GO
Si sustituimos el anterior trigger por esta versión dinámica podemos ver que nos protege igualmente del borrado si no realizamos el borrado previo de todas las aristas:
-- Para borrar a Francisco tendríamos que borrar primero sus aristas set xact_abort off begin tran declare @francisco_node_id nvarchar(2000) = (select $node_id from dbo.personas where nombre='Francisco') -- Borramos solo aristas Conoce delete from Conoce where $from_id=@francisco_node_id or $to_id=@francisco_node_id -- No borramos aristas Nacido --delete from Nacido where $from_id=@francisco_node_id or $to_id=@francisco_node_id -- Borramos el nodo delete from dbo.Personas where nombre='Francisco' rollback tran
-- Para borrar a Francisco tendríamos que borrar primero sus aristas set xact_abort off begin tran declare @francisco_node_id nvarchar(2000) = (select $node_id from dbo.personas where nombre='Francisco') -- No borramos aristas Conoce --delete from Conoce where $from_id=@francisco_node_id or $to_id=@francisco_node_id -- Borramos solo aristas Nacido delete from Nacido where $from_id=@francisco_node_id or $to_id=@francisco_node_id -- Borramos el nodo delete from dbo.Personas where nombre='Francisco' rollback tran
Si creamos el mismo trigger en “Lugares” e intentamos borrarlos mientras tenemos una arista “Conoce” o “Nacido” apuntando a los lugares que pretendemos borrar, obtendremos el correspondiente error:
-- Borrar todos los lugares delete from dbo.Lugares
Es cierto que este código genérico y dinámico tiene peor rendimiento, accede a metadatos, es poco legible, etc. por lo que si el rendimiento es crítico sería mejor optar por algún tipo de generador de código (por ejemplo, plantillas T4) para que podamos generar código estático específico para cada trigger de forma automática, pero a partir de las definiciones de nodos y aristas que tengamos en nuestro proyecto de base de datos.
En conclusión, la utilización de grafos abre nuevas posibilidades para el modelado. Junto a éstas, también nos obliga a tener precauciones adicionales de integridad que en un modelado tradicional con FKs ya se encuentran disponibles de forma nativa. Esperamos que dado el empuje que el modelado de grafos está teniendo en la industria en futuras versiones o revisiones de SQL Server esta integridad se implemente de forma nativa y no sea necesario el uso de triggers para este fin.
