-- SQL Property Graph Examples - Cinema



-- Drop objects created in this suite

DROP TABLE IF EXISTS qsgs_cinema_graph_table_nodes;
DROP TABLE IF EXISTS qsgs_cinema_graph_table;
DROP GRAPH qsgs_cinema;

DROP TABLE IF EXISTS qsgs_person;
DROP TABLE IF EXISTS qsgs_show;
DROP TABLE IF EXISTS qsgs_role;



-- Create objects for this suite

CREATE TABLE qsgs_person
(
	name VARCHAR(32, TEXT_SEARCH),
	profession VARCHAR(32),
	dob DATE
)
;

INSERT INTO qsgs_person (name, profession, dob)
VALUES
	('Robert De Niro',  'actor:director', '1943-08-07'),
	('Jean Reno',       'actor:director', '1948-07-30'),
	('Al Pacino',       'actor:producer', '1940-04-25'),
	('Michael Douglas', 'actor:producer', '1944-09-25'),
	('James Spader',    'actor',          '1960-02-07'),
	('Katarina Witt',   'actor',          '1965-12-03'),
	('Oliver Stone',    'director',       '1946-09-15'),
	('Joe Carnahan',    'director',       '1969-05-09')
;


CREATE TABLE qsgs_show
(
	title VARCHAR(32),
	show_type VARCHAR(8),
	score REAL,
	genre VARCHAR(32),
	released INT
)
;

INSERT INTO qsgs_show (title, show_type, score, genre, released)
VALUES
	('Wall Street',   'movie',  8.4, 'thriller',                1987),
	('Ronin',         'movie',  7.2, 'action',                  1998),
	('The Godfather', 'movie',  9.2, 'drama:thriller',          1972),
	('Salome',        'movie',  6.3, 'drama',                   2013),
	('The Blacklist', 'series', 8.2, 'drama::mystery:thriller', 1972)
;


CREATE TABLE qsgs_role
(
	person VARCHAR(32),
	film VARCHAR(32),
	participation VARCHAR(32)
)
;

INSERT INTO qsgs_role (person, film, participation)
VALUES
	('Robert De Niro',  'The Godfather', 'acted'),
	('Robert De Niro',  'Ronin',         'acted'),
	('Jean Reno',       'Ronin',         'acted'),
	('Al Pacino',       'The Godfather', 'acted'),
	('Michael Douglas', 'Wall Street',   'acted'),
	('James Spader',    'Wall Street',   'acted'),
	('Katarina Witt',   'Ronin',         'acted'),
	('Al Pacino',       'Salome',        'acted:directed'),
	('Oliver Stone',    'Wall Street',   'directed'),
	('James Spader',    'The Blacklist', 'acted'),
	('Joe Carnahan',    'The Blacklist', 'directed')
;


-- Create graph

CREATE DIRECTED GRAPH qsgs_cinema
(
	NODES => INPUT_TABLES
	(
		(SELECT title AS NAME, show_type || ':' || genre AS LABEL FROM qsgs_show),
		(SELECT DISTINCT CHAR4(released) AS NAME, 'year' AS LABEL FROM qsgs_show),

		(SELECT name AS NAME, 'person:' || profession AS LABEL FROM qsgs_person),
		(SELECT DISTINCT CHAR4(YEAR(DOB)) AS NAME, 'year' AS LABEL FROM qsgs_person)
	),
	EDGES => INPUT_TABLES
	(
		(SELECT person AS NODE1_NAME, film AS NODE2_NAME, participation AS LABEL FROM qsgs_role),
		(SELECT title AS NODE1_NAME, CHAR4(released) AS NODE2_NAME, 'released' AS LABEL FROM qsgs_show),
		(SELECT name AS NODE1_NAME, CHAR4(YEAR(dob)) AS NODE2_NAME, 'born' AS LABEL FROM qsgs_person)
	),
	OPTIONS => KV_PAIRS(label_delimiter = ':', graph_table = 'qsgs_cinema_graph_table')
)
;


-- Alter graph

ALTER GRAPH qsgs_cinema MODIFY
(
	NODES => INPUT_TABLES
	(
		(SELECT 'Stargate' AS NAME, 'movie:scifi' AS LABEL),
		(SELECT '1994' AS NAME, 'year' AS LABEL),

		(SELECT 'Roland Emmerich' AS NAME, 'person:director' AS LABEL)
	),
	EDGES => INPUT_TABLES
	(
		(SELECT 'James Spader' AS NODE1_NAME, 'Stargate' AS NODE2_NAME, 'acted' AS LABEL),
		(SELECT 'Roland Emmerich' AS NODE1_NAME, 'Stargate' AS NODE2_NAME, 'directed' AS LABEL),

		(SELECT 'Stargate' AS NODE1_NAME, '1994' AS NODE2_NAME, 'released' AS LABEL)
	),
	OPTIONS => KV_PAIRS(label_delimiter = ':', graph_table = 'qsgs_cinema_graph_table')
)
;


-- Insert backing values for the movie Stargate to allow future supplemental
-- queries to be run

INSERT INTO qsgs_show (title, show_type, score, genre, released)
VALUES ('Stargate', 'movie', 7.6, 'scifi', 1994)
;


-- Query graph by hops
--
-- Find the directors and titles of movies (no TV series) in which James Spader
-- has acted; TARGET_NODE_LABEL aligns the edge in movie<-director order

SELECT
	QUERY_NODE2_NAME AS Director,
	QUERY_NODE1_NAME AS Movie
FROM TABLE
(
	QUERY_GRAPH
	(
		GRAPH => 'qsgs_cinema',
		QUERIES => INPUT_TABLES
		(
			(SELECT 'James Spader'           AS NODE_NAME),
			(SELECT  1 AS HOP_ID, 'acted'    AS EDGE_LABEL),
			(SELECT  1 AS HOP_ID, 'movie'    AS NODE_LABEL),
			(SELECT -2 AS HOP_ID, 'directed' AS EDGE_LABEL),
			(SELECT 'director'               AS TARGET_NODE_LABEL)
		),
		RINGS => 2
	)
)
WHERE RING_ID = 2
ORDER BY 1, 2
;


-- Query graph with fuzzy search
--
-- Same hop query as above, but use Kinetica to do a fuzzy search on James
-- Spader's name

SELECT
	QUERY_NODE2_NAME AS Director,
	QUERY_NODE1_NAME AS Movie
FROM TABLE
(
	QUERY_GRAPH
	(
		GRAPH => 'qsgs_cinema',
		QUERIES => INPUT_TABLES
		(
			(
				SELECT name AS NODE_NAME
				FROM TABLE
				(
					FILTER_BY_STRING
					(
						TABLE_NAME => INPUT_TABLE(qsgs_person),
						MODE => 'search',
						EXPRESSION => 'James Spdar'
					)
				)
			),
			(SELECT  1 AS HOP_ID, 'acted'    AS EDGE_LABEL),
			(SELECT  1 AS HOP_ID, 'movie'    AS NODE_LABEL),
			(SELECT -2 AS HOP_ID, 'directed' AS EDGE_LABEL),
			(SELECT 'director'               AS TARGET_NODE_LABEL)
		),
		RINGS => 2
	)
)
WHERE RING_ID = 2
ORDER BY 1, 2
;


-- Query graph with supplemental data
--
-- Same hop query as above, but use a Kinetica table to supplement results with
-- movie scores

SELECT
	QUERY_NODE2_NAME AS Director,
	QUERY_NODE1_NAME AS Movie,
	s.score AS Score
FROM TABLE
(
	QUERY_GRAPH
	(
		GRAPH => 'qsgs_cinema',
		QUERIES => INPUT_TABLES
		(
			(SELECT 'James Spader'           AS NODE_NAME),
			(SELECT  1 AS HOP_ID, 'acted'    AS EDGE_LABEL),
			(SELECT  1 AS HOP_ID, 'movie'    AS NODE_LABEL),
			(SELECT -2 AS HOP_ID, 'directed' AS EDGE_LABEL),
			(SELECT 'director'               AS TARGET_NODE_LABEL)
		),
		RINGS => 2
	)
)
JOIN qsgs_show s ON s.title = QUERY_NODE1_NAME
WHERE RING_ID = 2
ORDER BY 1, 2
;


-- Query graph with a nested graph query
--
-- Find the titles and release years of shows directed by directors of James
-- Spader movies

SELECT
	QUERY_NODE1_NAME AS "Show",
	QUERY_NODE2_NAME AS Release_Year
FROM TABLE
(
	QUERY_GRAPH
	(
		GRAPH => 'qsgs_cinema',
		QUERIES => INPUT_TABLES
		(
			(
				SELECT DISTINCT QUERY_NODE2_NAME as NODE_NAME
				FROM TABLE
				(
					QUERY_GRAPH
					(
						GRAPH => 'qsgs_cinema',
						QUERIES => INPUT_TABLES
						(
							(SELECT 'James Spader'           AS NODE_NAME),
							(SELECT  1 AS HOP_ID, 'acted'    AS EDGE_LABEL),
							(SELECT  1 AS HOP_ID, 'movie'    AS NODE_LABEL),
							(SELECT -2 AS HOP_ID, 'directed' AS EDGE_LABEL),
							(SELECT  3 AS HOP_ID, 'directed' AS EDGE_LABEL),
							(SELECT 'movie'                  AS TARGET_NODE_LABEL)
						),
						RINGS => 3
					)
				)
			),
			(SELECT 'year' AS TARGET_NODE_LABEL)
		),
		RINGS => 1
	)
)
ORDER BY 1
;