How to modify a SQL Server Graph Edge with T-SQL

As I have been writing a section on SQL Server graph tables in my Database Design book, (and prepping for a hopeful book on the subject next year), I find that there are a few really annoying things about dealing with graph tables. This blog serves to clear up the first, most annoying of them. Inserting, updating, and deleting edges.

Because the key values in the graph database structures are hidden, you can’t just insert a new edge without translating your table’s key values to the graph database internal values. Edges aren’t even available for an update of the from or to references. As I wrote stored procedures to do this, I realized “why not use a view and trigger to make this happen”. So I did. The result is that I can insert, delete, and even update graph tables using normal SQL syntax. What makes this better than the stored procedure is that I can insert multiple rows simultaneously.

I haven’t tried to do this with large number of rows yet, but either way it will work fine for one or two rows in any sized table with proper indexing. To demonstrate this, I started with the following tables. There is no theme to the tables, there are just tables I have used to test out concepts like this:

USE tempdb; GO CREATE SCHEMA Basics; GO CREATE TABLE FROM Basics.Node1 ( Node1Id int NOT NULL CONSTRAINT PKNode1 PRIMARY KEY )  AS NODE; CREATE TABLE Basics.Node2 ( Node2Id int NOT NULL CONSTRAINT PKNode2 PRIMARY KEY )  AS NODE; GO CREATE TABLE Basics.BetweenNodes1 (      ConnectedSinceTime datetime2(0) NOT NULL           CONSTRAINT DFLTBetweenNodes1_ConnectedSinceTime DEFAULT (SYSDATETIME()),      CONSTRAINT ECBetweenNodes1_Node1_Node2              CONNECTION (Basics.Node1 TO Basics.Node2) ON DELETE NO ACTION )AS EDGE;

Then insert a few rows into the two node tables to get things prepared for the edge triggers.

INSERT INTO Basics.Node1(Node1Id)
VALUES (1001),(1002),(1011),(1012),(1021);

INSERT INTO Basics.Node2(Node2Id)
VALUES (2011),(2012),(2021);

Next I create the following view that uses regular joins between the nodes to fetch the internal details of the graph structures. All that will be output by the view is just the primary key values of the two tables.

CREATE OR ALTER VIEW Basics.BetweenNodes1_Manage_Node1_To_Node2
AS
SELECT Node1.Node1Id AS From_Node1Id, 
       Node2.Node2Id AS To_Node2Id
FROM Basics.Node1,
     Basics.BetweenNodes1,
     Basics.Node2
WHERE MATCH(Node1-(BetweenNodes1)->Node2);

There is no data in the edge yet, but before I insert any, I will create the following INSTEAD OF INSERT trigger.

CREATE OR ALTER TRIGGER Basics.BetweenNodes1_Manage_Node1_To_Node2$InsertTrigger
ON Basics.BetweenNodes1_Manage_Node1_To_Node2
INSTEAD OF INSERT
AS
SET NOCOUNT ON;
--note, to keep it simple, only including the insert statement. Could 
--use more error handling for a production version of the trigger
 BEGIN 
  INSERT INTO Basics.BetweenNodes1($From_id, $To_id)
  SELECT Node1.$node_id, Node2.$node_id
  FROM Inserted
       JOIN Basics.Node1
           ON Node1.Node1Id = inserted.From_Node1Id
       JOIN Basics.Node2 
           ON Node2.Node2Id = inserted.To_Node2Id
 END;

Now insert one row into the table, just using the keys of the nodes:

INSERT INTO Basics.BetweenNodes1_Manage_Node1_To_Node2(From_Node1Id, To_Node2Id)
VALUES (1001, 2011);

Then view the output:

SELECT * 
FROM Basics.BetweenNodes1_Manage_Node1_To_Node2;

You can see the rows have been inserted:

Node1Id     Node2Id
----------- -----------
1001        2011

Then add multiple edge rows:

INSERT INTO Basics.BetweenNodes1_Manage_Node1_To_Node2(From_Node1Id, To_Node2Id)
VALUES (1002, 2012),(1002,2021);

Those rows were also added:

Node1Id     Node2Id
----------- -----------
1001        2011
1002        2012
1002        2021

Next I add an update trigger. You can’t update an edge, so this turns into a delete, and then an update.

CREATE OR ALTER TRIGGER Basics.BetweenNodes1_Manage_Node1_To_Node2$UpdateTrigger
ON Basics.BetweenNodes1_Manage_Node1_To_Node2
INSTEAD OF UPDATE
AS
  BEGIN
     SET NOCOUNT ON;
     --delete rows in deleted
     DELETE FROM Basics.BetweenNodes1
     FROM Deleted
         JOIN Basics.Node1
             ON Node1.Node1Id = deleted.From_Node1Id
         JOIN Basics.Node2 
             ON Node2.Node2Id = deleted.To_Node2Id
     --get the rows to delete by joining to the table
     --through the graph keys
          JOIN Basics.BetweenNodes1
             ON Node1.$node_id = BetweenNodes1.$from_id
                AND Node2.$node_id = BetweenNodes1.$to_id;

      --add the rows from inserted
      INSERT INTO Basics.BetweenNodes1($From_id, $To_id)
      SELECT Node1.$node_id, Node2.$node_id
      FROM Inserted
           JOIN Basics.Node1
              ON Node1.Node1Id = inserted.From_Node1Id
           JOIN Basics.Node2 
              ON Node2.Node2Id = inserted.To_Node2Id;
 END;

This is how the data looks now:

Node1Id     Node2Id
----------- -----------
1001        2011
1002        2012
1002        2021

Update the rows using the view:

UPDATE Basics.BetweenNodes1_Manage_Node1_To_Node2
SET    From_Node1Id = 1001,
       To_Node2Id = 2012
WHERE  From_Node1Id = 1001
  AND  To_Node2Id = 2011;

You can see that the 1001 row now is connected to 2012:

Node1Id     Node2Id
----------- -----------
1001        2012
1002        2012
1002        2021

Now, update all of the rows to be connected to Node2Id = 2011.

UPDATE Basics.BetweenNodes1_Manage_Node1_To_Node2
SET To_Node2Id = 2011;

Check out the data and you will see the following:

Node1Id     Node2Id
----------- -----------
1001        2011
1002        2011
1002        2011

Finally, I will implement an INSTEAD OF DELETE trigger:

CREATE OR ALTER TRIGGER Basics.BetweenNodes1_Manage_Node1_To_Node2$UpdateTrigger
ON Basics.BetweenNodes1_Manage_Node1_To_Node2
INSTEAD OF DELETE
AS
  BEGIN
    SET NOCOUNT ON;
    --delete rows in deleted
    DELETE FROM Basics.BetweenNodes1
    FROM Deleted
          JOIN Basics.Node1
             ON Node1.Node1Id = deleted.From_Node1Id
          JOIN Basics.Node2 
             ON Node2.Node2Id = deleted.To_Node2Id
    
     --get the rows to delete by joining to the table
     --through the graph keys
          JOIN Basics.BetweenNodes1
             ON Node1.$node_id = BetweenNodes1.$from_id
               AND Node2.$node_id = BetweenNodes1.$to_id;
  END;

Delete the row where node1Id = 1001:

DELETE FROM Basics.BetweenNodes1_Manage_Node1_To_Node2
WHERE From_Node1Id = 1001;

Check out the data and you will see the following:

Node1Id     Node2Id
----------- -----------
1002        2011
1002        2011

Finally, delete the last two rows:

DELETE FROM Basics.BetweenNodes1_Manage_Node1_To_Node2;

The table is empty now. Next trick is to build a code generator to build a view for every edge without having to write this code over and over.

The post How to modify a SQL Server Graph Edge with T-SQL appeared first on Simple Talk.

from Simple Talk https://ift.tt/30HsdlT
via