我试图将动态网络拓扑表示为无向图,其中边、顶点以及边和顶点之间的连接点都需要与元素类型相关的属性(列)(例如:边具有长度、描述和类型,顶点具有名称和权重等)。
我有三张table:
+==========+
| Vertices |
+==========+----+---------+
| vertex_id | INTEGER | (PRIMARY KEY)
| vertex_name | TEXT |
| vertex_weight | TEXT |
+---------------+---------+
+=======+
| Edges |
+=======+-----+---------+
| edge_id | INTEGER | (PRIMARY KEY)
| edge_name | TEXT |
| edge_media | TEXT |
| edge_length | TEXT |
| edge_tag | TEXT |
+-------------+---------+
+=============+
| Connections |
+=============+--------+---------+
| connection_id | INTEGER | (PRIMARY KEY)
| connection_parent_id | TEXT | (FOREIGN KEY REFERENCES Vertexes.vertex_id)
| connection_name | TEXT |
| connection_edge_id | TEXT | (FOREIGN KEY REFERENCES Edges.edge_id)
+----------------------+---------+
这是这样设计的,我的“连接”表将描述顶点之间的链接,每个连接边id有两行,我可以描述并向连接和边添加更多属性。
我的sql fu最多是入门级的,但我很快意识到,要执行基本查询(例如:连接到vertex.vert\u name=“v1”,connection.connection\u name=“c3”的vertex.vertex\u name和connection.connection\u name的名称是什么),我需要编写一些相当混乱的嵌套查询,如:
SELECT Vertex.vertex_name, Connections.connection_name
FROM Vertices, Connections
WHERE NOT Connections.connection_parent_id IN (
SELECT Vertices.vertex_id
FROM Vertices
WHERE Vertices.vertex_name = "V1"
) AND Connections.edge_id IN (
SELECT Connections.edge_id
FROM Connections
WHERE Connections.connection_parent_id IN (
SELECT Vertices.vertex_id
FROM Vertices
WHERE Vertices.vertex_name = "V1"
) AND Connections.connection_name = "C3"
) AND Vertices.vertex_id = Connections.connection_parent_id
抛开数据库结构不谈,有没有更简单的方法用连接来表达上述内容,或者我是模式的牺牲品?
暂无答案!
目前还没有任何答案,快来回答吧!