关系表中的图表
#postgres #database #graphdatabase #apacheage

关于在关系表表示中制作图形的快速概述

我们将有2个表顶点和边缘表。

  • 顶点表： 
CREATE TABLE vertices (
vertex_id integer PRIMARY KEY,
properties json
);
  • 边缘表 
CREATE TABLE edges (
edge_id integer PRIMARY KEY,
from_vertex integer REFERENCES vertices (vertex_id),
to_vertex integer REFERENCES vertices (vertex_id),
label text,
properties json
);
  • 索引边缘表中的对顶点ID（以更快地访问） 
CREATE INDEX edges_from ON edges (from_vertex);
CREATE INDEX edges_to ON edges (to_vertex);
  • PostgreSQL中的JSON运营商

Operations

该模型的一些重要方面是：

  1. 任何顶点都可以具有将其与任何其他顶点连接的边缘。没有
    限制哪些事物可以或不能关联的模式。

  2. 给定任何顶点，您可以有效地找到其传入和外向
    边缘，从而遍历图形，即沿着一条穿过顶点链的路径
    向前和向后。 （这就是为什么示例2-2在
    上都有索引 tail_vertex和head_vertex列。）

  3. 通过使用不同类型的关系的不同标签，您可以存储几个
    单个图中的各种信息，同时仍保持干净
    数据模型。

  • 查询顶点表： 
SELECT * FROM vertices WHERE properties->>'json_filed' = 'Peru';
  • 查询边缘表 
SELECT * FROM edges WHERE from_vertex = (**SELECT QUERY**) and to_vertex = (**SELECT QUERY**)

那么，最难的部分是什么？当试图进行复杂的查询时，最难的部分是有多个边缘的特定过滤
之间的多个边缘的。 因此，请记住在OpencyPher中查询

MATCH
(person) -[:BORN_IN]-> () -[:WITHIN*0..]-> (us:Location {name:'United States'}),
(person) -[:LIVES_IN]-> () -[:WITHIN*0..]-> (eu:Location {name:'Europe'})
RETURN person.name
等效于SQL 
WITH RECURSIVE
-- in_usa is the set of vertex IDs of all locations within the United States
in_usa(vertex_id) AS (
SELECT vertex_id FROM vertices WHERE properties->>'name' = 'United States'
UNION
SELECT edges.tail_vertex FROM edges
JOIN in_usa ON edges.head_vertex = in_usa.vertex_id
WHERE edges.label = 'within'
),
-- in_europe is the set of vertex IDs of all locations within Europe
in_europe(vertex_id) AS (
SELECT vertex_id FROM vertices WHERE properties->>'name' = 'Europe'
UNION
SELECT edges.tail_vertex FROM edges
JOIN in_europe ON edges.head_vertex = in_europe.vertex_id
WHERE edges.label = 'within'
),
-- born_in_usa is the set of vertex IDs of all people born in the US
born_in_usa(vertex_id) AS (
SELECT edges.tail_vertex FROM edges
JOIN in_usa ON edges.head_vertex = in_usa.vertex_id
WHERE edges.label = 'born_in'
),
-- lives_in_europe is the set of vertex IDs of all people living in Europe
lives_in_europe(vertex_id) AS (
SELECT edges.tail_vertex FROM edges
JOIN in_europe ON edges.head_vertex = in_europe.vertex_id
WHERE edges.label = 'lives_in'
)
SELECT vertices.properties->>'name'
FROM vertices
-- join to find those people who were both born in the US *and* live in Europe
JOIN born_in_usa ON vertices.vertex_id = born_in_usa.vertex_id
JOIN lives_in_europe ON vertices.vertex_id = lives_in_europe.vertex_id;

参考和资源