在SQL Server中存储依赖图,并在NHibernate / C#中创建相应的图对象

Store Dependency Graph in SQL Server and create corresponding Graph Object in NHibernate/C#
2021-09-14
  •  译文(汉语)
  •  原文(英语)

我基本上有4天的时间来获得基本工作,这意味着要在NHibernate中创建一个图形网络对象.没有什么是完美的,只是可以通过HNibernate Graph对象(模型)进行搜索的东西.

也就是说,我们在网络表单上具有数据字段(实际上将是我的对象图中的节点),例如,当更新一个fieldK时,其他字段(FieldB,FieldT等)会受到影响(取决于该fieldK),还需要作为更新fieldK的结果来更新.这只是fieldK的一个示例.

我们更新的fieldK也可能在其他"树"中(在这种情况下,我认为是Graph).因此,如果我们更新FieldA,则需要进行搜索以查看FieldA在其他哪些图形中并找到它,然后遍历其子节点并更新这些子节点字段.

因此,您实质上具有图形网络类型的结构.

我被要求a)将其存储在数据库中..在这种情况下为SQL Server 2008,并且b)创建一个NHibernate对象来表示该图,以便我们可以使用它并双向搜索和遍历节点(在x棵树上上下移动)并找到FieldA,并执行我们需要做的事情,例如更新其相关字段.

所以我首先想到的是:

1)创建一个包含树结构的表,例如:

表:DataFieldTrees

DataFieldTreeID     DataFieldID      ParentDataFieldID    Level
---------------     -----------      -----------------    -----
       0                 12                 NULL            0    (Tree1) 
       1                 232                 12             1
       2                 32                  12             1
       3                 100                 12             1
       4                 210                 100            3
       5                 500                 100            3
       6                 600                 32             4
       7                 800                 600            5
       8                 44                 NULL            0     (Tree2)
       9                 400                 44             1
       10                55                  44             1
       11                111                 55             2
       12                211                 55             2
       13                600                 111            3
       14                43                  600            4
       15                15                  600            5

   ....more trees and so on

请注意,例如,在两个树中都找到了600和100个节点.

现在我确定,我需要某种方式将在多个树中找到的那些节点绑定到其RootTreeID,因此我想出了这个桥接表:

表格:DataFieldRootDataField

RootFieldID是第一个表中的树根(带有NULL的行)的字段

DataFieldID   RootDataFieldID
-----------   --------------- 
    100             0
    100             8
    600             0
    600             8

认为这将使我能够快速找出某个节点位于哪些树中以进行查找

最后,修复图并使其成为图,我创建了一个表以将图(树)链接在一起(关联它们):

表:DataFieldTreeRelation

RootDataFieldID    DataFieldTreeID    RelatedDataFieldTreeID
---------------    ---------------    ----------------------
      8                   800                 15          
...

因此这会将第一棵树链接到第二棵树(因为第一棵树中的节点800连接到节点15).

我知道我称这些树为"图形"集时是在称呼这些树,但我对此并不陌生,在开始思考如何做时会尝试阐明需求和我的想法.

然后是创建和解析为C#中的NHibernate网络图对象的整个其他问题...然后我必须确定检索此内容的最佳方法(创建节点的链表,基于我的理解,他们在C#中知道从这些表格中...目前还不知道我将如何做并形成一个我们可以搜索的对象)

我要么走了,要么对如何去做有一些不错的想法,但是不确定我是否正在就人们如何处理这个问题进行思考……

需要一些帮助和/或指导或保证我的想法有意义.

速聊1:
因此,总而言之,您要坚持将树集合保存到NHibernate吗?这样对吗?
解决过程1

SQL Server 2008具有称为"hierarchy-id"的数据类型.基本上,这使您可以表示一个字段并执行对分层对象的查询

数据类型: http : //msdn.microsoft.com/en-in/library/bb677173(v=sql.105).aspx

引用msdn页上的有关如何使用此数据类型的信息:

  • 组织结构

  • 文件系统

  • 项目中的一组任务

  • 语言术语分类

  • 网页之间的链接图

另外,此链接可能很有用: 如何使用SQL Server 2008为数据层次结构建模 http://msdn.microsoft.com/zh-cn/magazine/cc794278.aspx

速聊1:
我为什么要使用它而不是像使用类别那样简单地使用SomeID和SomeParentID创建表?(自我加入表)
速聊2:
实际上,在发布此内容之前,我已经查看了您的建议.我在寻找一个图形,而不仅仅是一个层次结构……在这里,您有连接到其他集合的节点集,包括循环引用.
速聊3:
为了回答您的第一个问题,使用现有语法对模式进行建模可能会使图形遍历(递归CTE或游标afaik)变得难以维护且难以调试,同时使用对层次结构的内置支持在查询方面具有更好的性能,因为支持位于数据库引擎内部.有关如何将其用作图形的更多信息,请参阅: blogs.msdn.com/b/manisblog/archive/2007/08/17/…您可能已经看过了,但是这就是我的方式会解决的.

I basically have 4 days to get something at its basic level working meaning get a graph network object created in NHibernate. Nothing perfect but just something that can be searched via an HNibernate Graph object (model).

That is, we have data fields (which are gonna essentially be nodes in my object graph) on a web form that when one fieldK for example is updated other fields (FieldB, FieldT, etc.) are affected (depend on that fieldK ) and also need to be updated in a return as a result of updating fieldK. This is just one example here, fieldK.

This fieldK we updated could also be in other "trees" of fields (what I think are Graphs in this case). So if we update FieldA, we need to do a search to see in what other graphs FieldA is in and find it, then traverse its subnodes and update those subnode fields.

So you essentially have a Network of Graphs type of structure.

I've been asked to a) store it in the DB ..in this case being SQL Server 2008 and b) Create an NHibernate object(s) to represent the graph so we can work with it and search and traverse the nodes bidirectional (up and down x trees) and find FieldA and do what we neeed to in terms of updating its dependent fields for example.

So my first thoughts are:

1) Create a table that holds tree structures, for example:

Table: DataFieldTrees

DataFieldTreeID     DataFieldID      ParentDataFieldID    Level
---------------     -----------      -----------------    -----
       0                 12                 NULL            0    (Tree1) 
       1                 232                 12             1
       2                 32                  12             1
       3                 100                 12             1
       4                 210                 100            3
       5                 500                 100            3
       6                 600                 32             4
       7                 800                 600            5
       8                 44                 NULL            0     (Tree2)
       9                 400                 44             1
       10                55                  44             1
       11                111                 55             2
       12                211                 55             2
       13                600                 111            3
       14                43                  600            4
       15                15                  600            5

   ....more trees and so on

Notice that 600 and 100 nodes are found in both trees for example.

Now I figured ok, I need some way of tying those nodes that are found in multiple trees to their RootTreeIDs so I came up with this bridge table:

Table: DataFieldRootDataField

RootFieldID are those fields in the first table that are the tree roots (rows with NULL)

DataFieldID   RootDataFieldID
-----------   --------------- 
    100             0
    100             8
    600             0
    600             8

figured this would allow me to quickly figure out which trees a certain node are in for lookup purposes

Finally to mend the graph and make it a graph I created a table to link the graphs (trees) together (relate them):

Table: DataFieldTreeRelation

RootDataFieldID    DataFieldTreeID    RelatedDataFieldTreeID
---------------    ---------------    ----------------------
      8                   800                 15          
...

so this would link the first tree to the second (since node 800 in the first tree is connected to Node 15).

I know I'm calling these trees when they are I think sets of "graphs" but I am new to all this and trying to articulate the requirement and my thinking when starting to think how I might do this.

Then there's the whole other issue of creating and parsing this into an NHibernate Network Graph object in C#...and then I have to determine the best way to retrieve this stuff (create linkedLists of nodes, who knows in C# based on what I pull from these tables...no idea yet how I'll do this and form an object that we can search through)

I'm either way off, or have some decent thoughts about maybe how to go about it but not sure if I'm even close to thinking right in terms of how people are dealing with this...

need some help here and/or guidance or reassurance that my ideas make sense.

Talk1:
So to summarise you want to persist a collection of trees to NHibernate? Is this correct?
Solutions1

Sql server 2008 has a data type called "hierarchy-id". This basically allows you to represent a field and perform queries for hierarchical objects

The data type: http://msdn.microsoft.com/en-in/library/bb677173(v=sql.105).aspx

Quote from the msdn page on how this data type can be used:

  • An organizational structure

  • A file system

  • A set of tasks in a project

  • A taxonomy of language terms

  • A graph of links between Web pages

Also, this link might be useful: How to Model Your Data Hierarchies With SQL Server 2008 http://msdn.microsoft.com/en-us/magazine/cc794278.aspx

Talk1:
why would I want to use that vs. simply creating a table with SomeID and SomeParentID like you would do with categories for example? (self join table)
Talk2:
actually I've already looked at what you suggested before posting this stuff. I'm looking for a graph not just a hierarchy...where you have sets of nodes connected to other sets and including circular references.
Talk3:
To answer your first question, modelling a schema with existing syntax might make graph traversals a bit (recursive CTEs or cursors afaik) unmaintainable and difficult to debug, while using inbuilt support for hierarchies gives the advantage of much better performance in terms of queries because the support is inside the database engines. For more information on how to use this as a graph, please refer to: blogs.msdn.com/b/manisblog/archive/2007/08/17/… guess you would have already looked at this too, but this is how I would solve it.
转载于:https://stackoverflow.com/questions/15594383/store-dependency-graph-in-sql-server-and-create-corresponding-graph-object-in-nh

本人是.net程序员,因为英语不行,使用工具翻译,希望对有需要的人有所帮助
如果本文质量不好,还请谅解,毕竟这些操作还是比较费时的,英语较好的可以看原文

留言回复
我们只提供高质量资源,素材,源码,坚持 下了就能用 原则,让客户花了钱觉得值
上班时间 : 周一至周五9:00-17:30 期待您的加入