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.

 

 

Rubén Garrigós

Rubén Garrigós is an expert in high-availability enterprise solutions based on SQL Server design, tuning, and troubleshooting. Over the past fifteen years, he has worked with Microsoft data access technologies in leading companies around the world. He currently is a Microsoft SQL Server and .NET applications architect with SolidQ. Ruben is certified by Microsoft as a Solution Expert on the Microsoft Data Platform (MSCE: Data Platform) and as a Solution Expert on the Microsoft Private Cloud (MSCE: Private Cloud). As a Microsoft Certified Trainer (MCT), Ruben has taught multiple official Microsoft courses as well as other courses specializing in SQL Server. He has also presented sessions at official events for various Microsoft technologies user groups.