DROP TABLE IF EXISTS GRAPHNODE CASCADE;

Drop table IF EXISTS USERS CASCADE;

Drop table IF EXISTS BLOG CASCADE;

Drop table IF EXISTS COMMENT CASCADE;

Drop table IF EXISTS PROPERTYLINK CASCADE;

DROP TYPE IF EXISTS ADDRESS CASCADE;

DROP TYPE IF EXISTS FYLLO CASCADE;

CREATE TYPE FYLLO AS ENUM ('Άνδρας','Γυναίκα');

CREATE TYPE ADDRESS AS  (

POLH VARCHAR(60),

XORA VARCHAR(60)

);

-- Labelled Property Graph as normalized tables

/* Δημιουργία Πίνακα {GRAPHNODE} */

CREATE TABLE GRAPHNODE (

NODEKEY VARCHAR(30) NOT NULL,

FACET VARCHAR(30),

PRIMARY KEY(NODEKEY));

/* Δημιουργία Πίνακα {USERS} */

CREATE TABLE USERS (

PRIMARY KEY(NODEKEY),

ONOMA VARCHAR(60) NOT NULL,

EPONYMO VARCHAR(120),

GENOS FYLLO NOT NULL,

PERIGRAFH VARCHAR(400)  NOT NULL,

TELEPHONE varchar (20) [],

DIAMONH ADDRESS )

INHERITS (GRAPHNODE);

/* Δημιουργία Πίνακα {BLOG} */

CREATE TABLE BLOG (

PRIMARY KEY(NODEKEY),

BLOGNAME VARCHAR(60) NOT NULL,

ADMINISTRATOR VARCHAR(120),

FOREIGN KEY (ADMINISTRATOR) REFERENCES USERS (NODEKEY))

INHERITS (GRAPHNODE);

/* Δημιουργία Πίνακα {COMMENT} */

CREATE TABLE COMMENT (

PRIMARY KEY(NODEKEY),

BLOGNAME VARCHAR(60) NOT NULL,

USERNAME VARCHAR(120) NOT NULL,

MSG VARCHAR(400)  NOT NULL,

D_MSG DATE,

FOREIGN KEY (BLOGNAME) REFERENCES BLOG (NODEKEY),

FOREIGN KEY (USERNAME) REFERENCES USERS (NODEKEY))

INHERITS (GRAPHNODE);

/* Δημιουργία Πίνακα {PROPERTYLINK} */

CREATE TABLE PROPERTYLINK (

NODEKEY1 VARCHAR(30) NOT NULL,

NODEKEY2 VARCHAR(30) NOT NULL, 

PROPERTYLABEL VARCHAR(30) NOT NULL,

FOREIGN KEY (NODEKEY1) REFERENCES GRAPHNODE (NODEKEY),

FOREIGN KEY (NODEKEY2) REFERENCES GRAPHNODE (NODEKEY),

PRIMARY KEY (NODEKEY1, NODEKEY2, PROPERTYLABEL)

);

INSERT INTO GRAPHNODE VALUES ('u01','Χρήστης');

INSERT INTO GRAPHNODE VALUES ('u02', 'Χρήστης');

INSERT INTO GRAPHNODE VALUES ('b01', 'Blog');

INSERT INTO GRAPHNODE VALUES ('b02', 'Blog');

INSERT INTO GRAPHNODE VALUES ('b03', 'Blog');

INSERT INTO GRAPHNODE VALUES ('c01', 'Σχόλιο');

/* Εισαγωγή εγγραφών στον  Πίνακα {USERS} */

INSERT INTO USERS VALUES ('u01', 'Χρήστης', 'Jacob', 'Date', 'Άνδρας', 'Διδάσκων εργαστηρίου Προχωρημένα Θέματα Βάσεων Δεδομένων', '{2810379190, 6973359242}', ROW('Χανιά',  'Ελλάς'));

INSERT INTO USERS VALUES ('u02', 'Χρήστης', 'George', 'Hunt', 'Άνδρας', 'Βοηθός εργαστηρίου Προχωρημένα Θέματα Βάσεων Δεδομένων', '{2810321040, 6978559242}', ROW('Ηράκλειο',  'Ελλάς'));

/* Εισαγωγή εγγραφών στον  Πίνακα {BLOG} */

INSERT INTO BLOG VALUES ('b01', 'Blog', 'Information Systems', 'u02');

INSERT INTO BLOG VALUES ('b02', 'Blog', 'Databases', 'u01');

INSERT INTO BLOG VALUES ('b03', 'Blog', 'Computer Science', 'u02');

/* Εισαγωγή εγγραφών στον  Πίνακα {COMMENT} */

INSERT INTO COMMENT VALUES ('c01', 'Σχόλιο', 'b01', 'u01', 'Exactly what I was loking for !', TO_DATE('16/03/2020', 'dd/mm/yyyy'));

/* Εισαγωγή εγγραφών στον  Πίνακα {PROPERTYLINK} */

INSERT INTO PROPERTYLINK VALUES ('u02', 'b03', 'ADMINS');

INSERT INTO PROPERTYLINK VALUES ('u02', 'b01', 'ADMINS');

INSERT INTO PROPERTYLINK VALUES ('u01', 'b02', 'ADMINS');

INSERT INTO PROPERTYLINK VALUES ('u01', 'b01', 'FOLLOWS');

INSERT INTO PROPERTYLINK VALUES ('u01', 'b02', 'FOLLOWS');

INSERT INTO PROPERTYLINK VALUES ('u01', 'b03', 'FOLLOWS');

INSERT INTO PROPERTYLINK VALUES ('u02', 'b01', 'FOLLOWS');

INSERT INTO PROPERTYLINK VALUES ('u01', 'c01', 'UPLOADS');

INSERT INTO PROPERTYLINK VALUES ('u02', 'c01', 'TAGS');

INSERT INTO PROPERTYLINK VALUES ('c01', 'b01', 'REGISTERS');

--Find the descendants of 'u02' in the path labelled 'FOLLOWS'

SELECT NODEKEY1 as Αρχή, NODEKEY2 as Στόχος 

FROM PROPERTYLINK 

WHERE NODEKEY1 = 'u02' AND PROPERTYLABEL = 'FOLLOWS'

WITH RECURSIVE pair(s,t) AS (

SELECT NODEKEY1 as s, NODEKEY2 as t 

FROM PROPERTYLINK 

WHERE NODEKEY1 = 'u02' AND PROPERTYLABEL = 'FOLLOWS'

UNION

SELECT NODEKEY1, NODEKEY2 

FROM pair, PROPERTYLINK 

WHERE NODEKEY1 = t AND PROPERTYLABEL = 'FOLLOWS')

SELECT DISTINCT s, t 

FROM pair

WHERE t != 'u02' ORDER BY t ;

--Find all descendants of 'u01' in the path labelled 'ADMINS'

SELECT NODEKEY1 as Αρχή, NODEKEY2 as Στόχος 

FROM PROPERTYLINK 

WHERE NODEKEY1 = 'u01' AND PROPERTYLABEL = 'ADMINS'

WITH RECURSIVE pair(s,t) AS (

SELECT NODEKEY1 as s, NODEKEY2 as t 

FROM PROPERTYLINK 

WHERE NODEKEY1 = 'u01' AND PROPERTYLABEL = 'ADMINS'

UNION

SELECT NODEKEY1, NODEKEY2 

FROM pair, PROPERTYLINK 

WHERE NODEKEY1 = t AND PROPERTYLABEL = 'ADMINS')

SELECT DISTINCT s, t 

FROM pair

WHERE t != 'u01' ORDER BY t ;

-- How are two nodes connected (irrespective of path)

-- παράδειγμα όπου δεν υπάρχει μια διαδρομή 

SELECT NODEKEY1 as Αρχή, NODEKEY2 as Στόχος, PROPERTYLABEL 

FROM PROPERTYLINK 

WHERE NODEKEY1 = 'u01' AND NODEKEY2 = 'b01'

WITH RECURSIVE transitive_closure(NODEKEY1, NODEKEY2, PROPERTYLABEL, distance, path_string) AS

( SELECT NODEKEY1, NODEKEY2, PROPERTYLABEL AS Διαδρομή, 1 AS distance,

NODEKEY1 || '.' || NODEKEY2 || '.' AS path_string,

NODEKEY2 AS direct_connection

FROM PROPERTYLINK

WHERE NODEKEY1 = 'u01' 

UNION ALL

SELECT tc.NODEKEY1, e.NODEKEY2, tc.PROPERTYLABEL, tc.distance + 1,

tc.path_string || e.NODEKEY2 || '.' AS path_string, tc.direct_connection

FROM PROPERTYLINK AS e

JOIN transitive_closure AS tc ON e.NODEKEY1 = tc.NODEKEY2

WHERE e.PROPERTYLABEL = 'REGISTERS' AND tc.path_string NOT LIKE '%' || e.NODEKEY2 || '.%'

AND tc.distance < 6

)

SELECT * 

FROM transitive_closure

WHERE NODEKEY2 = 'b01' 

ORDER BY NODEKEY1, NODEKEY2, PROPERTYLABEL, distance;

-- How are two nodes connected in a specific path

WITH RECURSIVE transitive_closure(NODEKEY1, NODEKEY2, PROPERTYLABEL, distance, path_string) AS

( SELECT NODEKEY1, NODEKEY2, PROPERTYLABEL AS Διαδρομή, 1 AS distance,

NODEKEY1 || '.' || NODEKEY2 || '.' AS path_string,

NODEKEY2 AS direct_connection

FROM PROPERTYLINK

WHERE NODEKEY1 = 'u02' AND PROPERTYLABEL = 'TAGS' -- source

UNION ALL

SELECT tc.NODEKEY1, e.NODEKEY2, tc.PROPERTYLABEL, tc.distance + 1,

tc.path_string || e.NODEKEY2 || '.' AS path_string, tc.direct_connection

FROM PROPERTYLINK AS e

JOIN transitive_closure AS tc ON e.NODEKEY1 = tc.NODEKEY2

WHERE e.PROPERTYLABEL = 'REGISTERS' AND tc.path_string NOT LIKE '%' || e.NODEKEY2 || '.%'

AND tc.distance <= 2

)

SELECT NODEKEY1, NODEKEY2, distance, path_string

FROM transitive_closure

WHERE NODEKEY2 = 'b01' 

ORDER BY NODEKEY1, NODEKEY2, PROPERTYLABEL, distance;