SQL Server 2019中的图形数据库功能–第1部分

最新推荐文章于 2024-06-26 10:57:33 发布

culuo4781

最新推荐文章于 2024-06-26 10:57:33 发布

阅读量1.3k

点赞数

文章标签：数据库 python mysql java sql

原文链接：https://www.sqlshack.com/graph-database-features-in-sql-server-2019-part-1/

版权

SQL Server 2017 introduced Graph database features where we can represent the complex relationship or hierarchical data. We can explore the following articles to get familiar with the concept of the Graph database.

SQL Server 2017引入了Graph数据库功能，我们可以在其中表示复杂的关系或层次结构数据。我们可以浏览以下文章以熟悉Graph数据库的概念。

Graph database contains the following elements

图形数据库包含以下元素

Node: Nodes in graph database represents an entity i.e. customer, employee etc. 节点：图数据库中的节点代表一个实体，即客户，员工等。
Edge: Edge represents the relationship between nodes. 边缘：边缘表示节点之间的关系。

SQL Server 2019 contains below enhancements for the Graph database.

SQL Server 2019包含以下针对Graph数据库的增强功能。

Edge Constraints (CTP 2.0)
边缘约束（CTP 2.0）
Match support in MERGE statement
MERGE语句中的匹配支持
Use a derived table or view aliases in graph match query
在图形匹配查询中使用派生表或查看别名

In this article, we will view the edge constraint with SQL Server 2019 preview.

在本文中，我们将使用SQL Server 2019预览版查看边缘约束。

SQL Server 2019中的边缘约束（CTP 2.0） (Edge Constraints (CTP 2.0) in SQL Server 2019)

Typically, graph databases in SQL Server 2017 look as shown below. Here, you can see that an edge could connect any node to any other node. There were no restrictions on the edge connections. Edge is bidirectional in SQL Server 2017 i.e. edge can connect with any nodes in any direction.

通常，SQL Server 2017中的图形数据库如下所示。在这里，您可以看到边缘可以将任何节点连接到任何其他节点。边缘连接没有限制。 Edge在SQL Server 2017中是双向的，即Edge可以在任何方向上与任何节点连接。

Now let us consider a scenario where we want to place some restrictions on the edge that it should not be able to connect to each node. If someone tries to create an edge between those, it should generate an error.

现在让我们考虑一个场景，在该场景中，我们希望对边缘进行限制，使其不能连接到每个节点。如果有人尝试在两者之间创建边缘，则应生成错误。

Let us consider an example where we have two nodes:

让我们考虑一个示例，其中有两个节点：

Administrators
管理员
Users
用户数

We have an edge connecting these nodes i.e. ‘Authorization’ as shown here.

我们有一条连接这些节点的边缘，即“授权”，如下所示。

In the above example, the administrator can provide authorization to do any work to the user, but a user cannot give authorization for the administrator.

在上面的示例中，管理员可以向用户提供执行任何工作的授权，但是用户无法为管理员提供授权。

In SQL Server 2017, we create the nodes and edge table as shown below:

在SQL Server 2017中，我们创建节点和边表，如下所示：

Create database GraphDB
Go
Use GraphDB
Go
DROP TABLE IF EXISTS administrator
CREATE TABLE administrator (ID INTEGER PRIMARY KEY, administratorName VARCHAR(100)) AS NODE;
INSERT INTO administrator
VALUES (1,'Rajendra')
Go
SELECT * FROM administrator

CREATE TABLE Users (ID INTEGER PRIMARY KEY, UserName VARCHAR(100)) AS NODE;
INSERT INTO Users
VALUES (1,'Sonu')
Go
SELECT * FROM Users

DROP TABLE IF EXISTS [Authorization]
CREATE TABLE [Authorization]  AS EDGE
Go
 
INSERT INTO [Authorization] ($from_id ,$to_id )
VALUES (
(SELECT $node_id from administrator where ID=1)
,(SELECT $node_id from Users where ID=1))
select * from [Authorization]

Now let us insert one more record into users table.

现在让我们在用户表中再插入一条记录。

INSERT INTO Users
VALUES (2,'Mohit')
Go

In this step, let us try to insert an edge records from users to administrator node using the below query. It successfully inserted the record.

在此步骤中，让我们尝试使用以下查询将边缘记录从用户插入到管理员节点。它成功插入了记录。

INSERT INTO [Authorization] ($from_id ,$to_id )
VALUES (
(SELECT $node_id from Users where ID=2),
(SELECT $node_id from administrator where ID=1))
select * from [Authorization]

You can notice here that we have inserted edge record from user to administrator while it is not our desired behavior. We want to restrict this behavior but SQL Server 2017 does not provide any options to do so.

您可以在此处注意到，我们已经从用户到管理员插入了边缘记录，但这不是我们所希望的行为。我们想要限制此行为，但SQL Server 2017不提供任何选择来限制此行为。

Let us move to SQL Server 2019 and see what it offers for this problem.

让我们转到SQL Server 2019，看看它为该问题提供了什么。

SQL Server 2019 Edge约束 (SQL Server 2019 Edge constraints)

In SQL Server 2019, we can define edge constraint. We can define the condition in the edge constraint from which edge could not connect.

在SQL Server 2019中，我们可以定义边缘约束。我们可以在边缘约束中定义无法连接边缘的条件。

For example, in our example, above we want a constraint from administrator to the user node. If someone tries to insert records from Users to Administrator, it should fail.

例如，在我们的示例中，上面我们想要从管理员到用户节点的约束。如果有人尝试将记录从用户插入到管理员，则它将失败。

In SQL Server 2019, we can define contains as shown in below query. You can notice the difference in creating edge table in SQL Server 2017 and SQL Server 2019 preview.

在SQL Server 2019中，我们可以定义包含如下查询所示。您可以注意到在SQL Server 2017和SQL Server 2019预览中创建边缘表的区别。

In below query, we defined a constraint that allows connection from Node ‘Administrator’ To Node ‘Users’.

在下面的查询中，我们定义了一个约束，该约束允许从节点“管理员”到节点“用户”的连接。

CREATE TABLE [Authorization] 
(
CONSTRAINT EC_Authorization Connection (Administrator TO Users)
)
As Edge
Go

Now, we can connect the edge between nodes as usual way from the Node ‘Administrator’ To Node ‘Users’:

现在，我们可以像往常一样从节点“管理员”到节点“用户”连接节点之间的边缘：

INSERT INTO [Authorization] ($from_id ,$to_id )
VALUES (
(SELECT $node_id from administrator where ID=1)
,(SELECT $node_id from Users where ID=1)
)
select * from [Authorization]

We defined constraint from Node ‘Administrator’ To Node ‘Users’ in edge table. Therefore, we will test inserting edge record from Node ‘Users’ to Node ‘Administrator’.

我们在边缘表中定义了从节点“管理员”到节点“用户”的约束。因此，我们将测试从“用户”节点到“管理员”节点插入边缘记录。

INSERT INTO Users
VALUES (2,'Mohit')
Go
 
INSERT INTO [Authorization] ($from_id ,$to_id )
VALUES (
(SELECT $node_id from users where ID=2)
,(SELECT $node_id from administrator where ID=1)
)

It fails with below error message. You can see in the error message that since we are trying to insert edge between Users to Administrator. It violates the edge constraint we defined with edge table.

它失败，并显示以下错误消息。您会在错误消息中看到，由于我们正在尝试在用户与管理员之间插入边缘。它违反了我们用边表定义的边约束。

Msg 547, Level 16, State 0, Line 19

消息547，级别16，状态0，第19行

The INSERT statement conflicted with the EDGE constraint “EC_Authorization”. The conflict occurred in database “GraphDB”, table “dbo.Authorization”.

INSERT语句与EDGE约束“ EC_Authorization”冲突。数据库“ GraphDB”的表“ dbo.Authorization”中发生了冲突。

The statement has been terminated.

该语句已终止。

Now let us add one more node Groups in our example. A user who is part of the group can have the required permissions. The graph should look like below.

现在让我们在示例中再添加一个节点组。属于该组的用户可以具有所需的权限。该图应如下图所示。

CREATE TABLE UserGroups
(
	ID INTEGER PRIMARY KEY,
   [UserGroupName] NVARCHAR(100) NOT NULL,
   ) AS NODE
GO
INSERT INTO UserGroups
VALUES (1,'ReadUserGroup')
Go

Create table [Permissions] as edge
Go

Previously we have defined constraints that allow inserting from Node ‘administrators’ to Node ‘users’. Therefore, if we connect edge from Node ‘usergroups’ to Node ‘users’ we get below error message.

以前，我们定义了一些约束，这些约束允许从Node“管理员”插入Node“用户”。因此，如果将边缘从节点“用户组”连接到节点“用户”，则会收到以下错误消息。

INSERT INTO [Authorization] ($from_id ,$to_id )
VALUES (
(SELECT $node_id from usergroups where ID=1)
,(SELECT $node_id from users where ID=1)
)

We can define multiple constraints on an edge table. In below query, we have defined one more constraint to insert from Node ‘Usergroups’ to Node ‘Users’.

我们可以在边表上定义多个约束。在下面的查询中，我们定义了另一个要从节点“用户组”插入到节点“用户”的约束。

ALTER TABLE [Authorization] ADD CONSTRAINT  EC_Connection1_Authorization Connection 
(
   Administrator TO Users,
   UserGroups TO Users
)
GO

We can delete the old constraint since, in the above query, new constraint contains both the nodes.

我们可以删除旧约束，因为在上面的查询中，新约束包含两个节点。

ALTER TABLE [Authorization] DROP CONSTRAINT EC_Authorization 
GO

INSERT INTO Users
VALUES (3,'Akshita')
Go

Now let us test few scenarios to insert into edge table.

现在让我们测试一些插入边缘表的方案。

INSERT INTO [Authorization] ($from_id ,$to_id )
VALUES (
(SELECT $node_id from users where ID=2)
,(SELECT $node_id from administrator where ID=1)
)

INSERT INTO [Authorization] ($from_id ,$to_id )
VALUES (
(SELECT $node_id from UserGroups where ID=1)
, (SELECT $node_id from users where ID=1)
)

INSERT INTO [Authorization] ($from_id ,$to_id )
VALUES (
(SELECT $node_id from users where ID=1),
(SELECT $node_id from UserGroups where ID=1)
 
)

使用SQL Server 2019删除图形数据库中的记录 (Delete records in a graph database with SQL Server 2019)

In SQL Server 2017, we can delete any records from the graph database whether that contains the connecting edge into the graph or not. This imbalances the graph database since the edge is having no connections for the record. We cannot ensure data integrity in this case.

在SQL Server 2017中，我们可以从图数据库中删除任何记录，无论该记录是否包含图中的连接边。由于边没有该记录的连接，因此这会使图形数据库失衡。在这种情况下，我们无法确保数据的完整性。

Let us delete a record in SQL Server 2017 from the table and records created earlier.

让我们从表和先前创建的记录中删除SQL Server 2017中的记录。

However, we can see that connecting edge already exist which should not be case ideally.

但是，我们可以看到连接边缘已经存在，理想情况下不应该如此。

Now let us observe the same behavior with SQL Server 2019. You can see that delete statement fails with the error

现在让我们观察一下SQL Server 2019的相同行为。您可以看到delete语句因错误而失败

Msg 547, Level 16, State 0, Line 3

消息547，级别16，状态0，第3行

The DELETE statement conflicted with the EDGE REFERENCE constraint “EC_Authorization”. The conflict occurred in database “GraphDB”, table “dbo.Authorization”.

DELETE语句与EDGE REFERENCE约束“ EC_Authorization”冲突。数据库“ GraphDB”的表“ dbo.Authorization”中发生了冲突。

The statement has been terminated.

该语句已终止。

SQL Server 2019中的系统目录视图是否存在边缘限制 (System catalog views in SQL Server 2019 for edge constraints)

SQL Server 2019 contains new system catalog view to get information about the defined edge constraints for SQL Server 2019.

SQL Server 2019包含新的系统目录视图，以获取有关SQL Server 2019的已定义边缘约束的信息。

sys.edge_constraints: This system view show details about the edge constraint
sys.edge_constraints：此系统视图显示有关边约束的详细信息
sys.edge_constraint_clauses: this system view shows details about clause of an edge constraint
sys.edge_constraint_clauses：此系统视图显示有关边约束子句的详细信息

We can join these system views to get complete information from both of these.

我们可以加入这些系统视图，以从这两个视图中获取完整的信息。

SELECT
       EC.name AS edge_constraint_name
     , OBJECT_NAME(EC.parent_object_id) AS [edge table]
     , OBJECT_NAME(ECC.from_object_id) AS [From Node table]
     , OBJECT_NAME(ECC.to_object_id) AS [To Node table],
	 EC.type_desc,
     EC.create_date 
  FROM sys.edge_constraints EC
 INNER JOIN sys.edge_constraint_clauses ECC
    ON EC.object_id = ECC.object_id

We can filter out the records by specifying where condition. For example, below I have filtered out the result for the Node table administrators.

我们可以通过指定条件来过滤记录。例如，下面我过滤了Node表管理员的结果。

SELECT
       EC.name AS edge_constraint_name
     , OBJECT_NAME(EC.parent_object_id) AS [edge table]
     , OBJECT_NAME(ECC.from_object_id) AS [From Node table]
     , OBJECT_NAME(ECC.to_object_id) AS [To Node table],
	 EC.type_desc,
     EC.create_date 
  FROM sys.edge_constraints EC
 INNER JOIN sys.edge_constraint_clauses ECC
    ON EC.object_id = ECC.object_id
 WHERE ECC.from_object_id = object_id('administrator')

结论 (Conclusion)

Edge constraints in SQL Server 2019 are helpful to maintain data integrity for a Graph database. We can now specify the constraints on edge table to control the direction of flow for the edge.

SQL Server 2019中的边缘约束有助于维护Graph数据库的数据完整性。现在，我们可以在边缘表上指定约束以控制边缘的流向。