x2ora/setup.sql at master · blitzboard/x2ora · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
-- Using CLOB and VARCHAR2

CREATE TABLE x2graph (
  id VARCHAR2(255)
, props CLOB
, CONSTRAINT x2graph_pk PRIMARY KEY (id)
, CONSTRAINT x2graph_check CHECK (props IS JSON)
);

CREATE TABLE x2node (
  graph VARCHAR2(255)
, id VARCHAR2(255)
, label VARCHAR2(255)
, props VARCHAR2(4000)
, CONSTRAINT x2node_pk PRIMARY KEY (graph, id)
, CONSTRAINT x2node_fk_graph FOREIGN KEY (graph) REFERENCES x2graph(id)
, CONSTRAINT x2node_check CHECK (props IS JSON)
);

CREATE TABLE x2edge (
  graph VARCHAR2(255)
, id VARCHAR2(255)
, src VARCHAR2(255)
, dst VARCHAR2(255)
, label VARCHAR2(255)
, props VARCHAR2(4000)
, CONSTRAINT x2edge_pk PRIMARY KEY (graph, id)
, CONSTRAINT x2edge_fk_graph FOREIGN KEY (graph) REFERENCES x2graph(id)
, CONSTRAINT x2edge_fk_src FOREIGN KEY (graph, src) REFERENCES x2node(graph, id)
, CONSTRAINT x2edge_fk_dst FOREIGN KEY (graph, dst) REFERENCES x2node(graph, id)
, CONSTRAINT x2edge_check CHECK (props IS JSON)
);

-- Using BLOB

CREATE TABLE x2graph (
  id VARCHAR2(255)
, props BLOB
, CONSTRAINT x2graph_pk PRIMARY KEY (id)
, CONSTRAINT x2graph_check CHECK (props IS JSON FORMAT OSON)
);

CREATE TABLE x2node (
  graph VARCHAR2(255)
, id VARCHAR2(255)
, label VARCHAR2(255)
, props BLOB
, CONSTRAINT x2node_pk PRIMARY KEY (graph, id)
, CONSTRAINT x2node_fk_graph FOREIGN KEY (graph) REFERENCES x2graph(id)
, CONSTRAINT x2node_check CHECK (props IS JSON FORMAT OSON)
);

CREATE TABLE x2edge (
  graph VARCHAR2(255)
, id VARCHAR2(255)
, src VARCHAR2(255)
, dst VARCHAR2(255)
, label VARCHAR2(255)
, props BLOB
, CONSTRAINT x2edge_pk PRIMARY KEY (graph, id)
, CONSTRAINT x2edge_fk_graph FOREIGN KEY (graph) REFERENCES x2graph(id)
, CONSTRAINT x2edge_fk_src FOREIGN KEY (graph, src) REFERENCES x2node(graph, id)
, CONSTRAINT x2edge_fk_dst FOREIGN KEY (graph, dst) REFERENCES x2node(graph, id)
, CONSTRAINT x2edge_check CHECK (props IS JSON FORMAT OSON)
);

INSERT INTO x2node VALUES ('TEST', '1', 'PERSON', '{"AGE":[37]}');
INSERT INTO x2node VALUES ('TEST', '2', 'PERSON', '{"AGE":[36]}');
INSERT INTO x2edge VALUES ('TEST', '73da3dd7-2518-459a-9c36-c2104a95fc7f', '1', '2', 'KNOWS', '{"SINCE":[2017]}');

DROP PROPERTY GRAPH x2;

CREATE PROPERTY GRAPH x2
  VERTEX TABLES (
    x2node
      KEY (id)
      LABEL node
      PROPERTIES (graph, id, label, props)
  )
  EDGE TABLES (
    x2edge
      KEY (id)
      SOURCE KEY(src) REFERENCES x2node
      DESTINATION KEY(dst) REFERENCES x2node
      LABEL edge
      PROPERTIES (graph, id, label, src, dst, props)
  )
  OPTIONS (PG_VIEW);

DROP VIEW x2metanode;
DROP VIEW x2metaedge;

CREATE VIEW x2metanode AS
SELECT
  id
, MAX(label) AS label
, '{"count":['||COUNT(*)||']}' AS props
FROM x2node
GROUP BY id;

CREATE VIEW x2metaedge AS
SELECT
  ANY_VALUE(id) AS id
, src
, dst
, MAX(label) AS label
, '{"count":['||COUNT(*)||']}' AS props
FROM x2edge
GROUP BY src, dst, label;

CREATE PROPERTY GRAPH x2meta
  VERTEX TABLES (
    x2metanode
      KEY (id)
      LABEL node
      PROPERTIES (id, label, props)
  )
  EDGE TABLES (
    x2metaedge
      KEY (id)
      SOURCE KEY(src) REFERENCES x2metanode
      DESTINATION KEY(dst) REFERENCES x2metanode
      LABEL edge
      PROPERTIES (id, label, src, dst, props)
  )
  OPTIONS (PG_VIEW);