SQL中带闭包表的有向循环图

I'm trying to determine if it is possible to easily model a directed cyclic graph with a closure table (and/or possibly other helper tables) in SQL. For example, suppose I have this directed graph (all pointing down):

我正在尝试确定是否有可能在SQL中使用闭包表(和/或其他辅助表)轻松地建模一个有指示的循环图。例如，假设我有这个有向图(都指向下方):

I'm having trouble modeling this with a closure table.

我在用闭包表建模时遇到了麻烦。

We would get this table:

我们会得到这个表格:

(ancestor, descendant, path-length)
(祖先、子孙、腔长)
(1, 1, 0)
(1,- 1,0)
(2, 2, 0)
(2 2 0)
(3, 3, 0)
(3 3 0)
(4, 4, 0)
(4 4 0)
(2, 4, 1)
(2、4、1)
(3, 4, 1)
(3、4、1)
(1, 4, 2)
(1、4、2)

A closure table breaks down when removing the edge between 1 and 2.

当删除1和2之间的边缘时，闭包表就会崩溃。

DELETE FROM closure WHERE descendant IN 
(SELECT descendant FROM closure WHERE ancestor=2);

DELETE FROM closure WHERE descendant=2 AND ancestor=1;

The first delete query removes paths between 1 and 4, and 3 and 4, which shouldn't be deleted

第一个delete查询删除1到4、3和4之间的路径，这些路径不应该被删除

I can't find a solution to this with a closure table, and it get's further complicated if 4 were to point to 1. (becoming cyclic).

我找不到一个闭表的解，如果4指向1，它会变得更复杂。(成为循环)。

I haven't been able to find much written on this subject. I'd appreciate any input regarding how to implement this type of graph in SQL, or if SQL is simply not a good choice for this type of graph.

我找不到关于这个题目的很多文章。我想知道如何在SQL中实现这种类型的图，或者SQL对于这种类型的图来说不是一个好的选择。

1 个解决方案

#1

I've done cyclic graphs in a closure table before. It's much more expensive to delete edges but it can be done.

我以前在闭表中做过循环图。删除边缘要昂贵得多，但是可以这么做。

First of all you can forget about path-length. What's the path-length of a cycle? Infinity? Drop that column.

首先，你可以忘记路径长度。一个周期的路径长度是多少?无穷?这一列。

When you remove an edge (parent, child) from the graph you have to consider the possibility that there are alternate paths from parent's ancestors to child's children. So before deleting the edge save all of the parent's ancestor's children - these are the potential alternate paths. Then after you've deleted the edge re-add parent's ancestor's children to the closure table, excluding duplicate rows.

当您从图中删除一条边(父、子)时，您必须考虑从父节点到子节点的可能路径。因此，在删除边缘之前，保存父类的所有子路径——这些是可能的替代路径。然后，在删除边缘之后，将父类的祖先子类重新添加到闭包表中，排除重复的行。

1 个解决方案

#1

更多相关文章

随机推荐