-- Acual Example ---
-- Cleanup arificats from prior R2RML processing (which produces Quad Mappings that instruct Virtuoso about RDF Views handling)


SPARQL
DROP QUAD MAP <urn:oracle:8890:sql:r2rml:mappings> ; 

SPARQL
CLEAR GRAPH <urn:oracle:8890:sql:r2rml:mappings> ;

SPARQL CLEAR GRAPH <http://demo.openlinksw.com:8890/OracleHR#> ;

-- Using R2RML Mappings in the doc at: http://kingsley.idehen.net/DAV/home/kidehen/Public/R2RML%20Demos/views-from-sql-queries/oracle-8890-sql-r2rml.ttl
-- Load Mappings

-- SPARQL
-- *LOAD not working right now*
-- LOAD <http://kingsley.idehen.net/DAV/home/kidehen/Public/R2RML%20Demos/views-from-sql-queries/oracle-8890-sql-r2rml.ttl> INTO <urn:oracle:8890:sql:r2rml:mappings>  ;

SPARQL 

PREFIX rr: <http://www.w3.org/ns/r2rml#> 
PREFIX Oracle: <http://demo.openlinksw.com:8890/schemas/OracleHR#> 
PREFIX oracle-stat: <http://demo.openlinksw.com:8890/OracleHR/stat#> 
PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#> 
PREFIX void: <http://rdfs.org/ns/void#> 
PREFIX scovo: <http://purl.org/NET/scovo#> 
PREFIX aowl: <http://bblfish.net/work/atom-owl/2006-06-06/> 
PREFIX foaf: <http://xmlns.com/foaf/0.1/> 

INSERT { 
			GRAPH <urn:oracle:8890:sql:r2rml:mappings> {


			<#TriplesMapCOUNTRIES> a rr:TriplesMap; rr:logicalTable [ rr:sqlQuery """
																			       SELECT "COUNTRY_ID", "COUNTRY_NAME", "REGION_ID"
																				   FROM "Oracle"."HR"."COUNTRIES"
																				   """
																	]; 
			rr:subjectMap [ rr:termType rr:IRI  ; rr:template "http://demo.openlinksw.com:8890/OracleHR/countries/COUNTRY_ID={COUNTRY_ID}#this"; rr:class Oracle:COUNTRIES; rr:graph <http://demo.openlinksw.com:8890/OracleHR#> ];
			rr:predicateObjectMap [ rr:predicateMap [ rr:constant Oracle:country_id ] ; rr:objectMap [ rr:column "COUNTRY_ID" ]; ] ;
			rr:predicateObjectMap [ rr:predicateMap [ rr:constant Oracle:country_name ] ; rr:objectMap [ rr:column "COUNTRY_NAME" ]; ] ;
			rr:predicateObjectMap [ rr:predicateMap [ rr:constant Oracle:countries_has_regions ] ; rr:objectMap [ rr:termType rr:IRI ; rr:template "http://demo.openlinksw.com:8890/OracleHR/regions/REGION_ID={REGION_ID}#this" ]; ] ;
			rr:predicateObjectMap [ rr:predicateMap [ rr:constant Oracle:countries_of_locations ] ; rr:objectMap [ rr:parentTriplesMap <#TriplesMapLOCATIONS>;  rr:joinCondition [ rr:child "COUNTRY_ID" ; rr:parent "COUNTRY_ID" ] ; ]; ] .

			<#TriplesMapDEPARTMENTS> a rr:TriplesMap; rr:logicalTable [ rr:sqlQuery """
																			       SELECT "DEPARTMENT_ID", "DEPARTMENT_NAME", "MANAGER_ID", "LOCATION_ID"
																				   FROM "Oracle"."HR"."DEPARTMENTS"
																				   """
																	]; 
			rr:subjectMap [ rr:termType rr:IRI  ; rr:template "http://demo.openlinksw.com:8890/OracleHR/departments/DEPARTMENT_ID={DEPARTMENT_ID}#this"; rr:class Oracle:DEPARTMENTS; rr:graph <http://demo.openlinksw.com:8890/OracleHR#> ];
			rr:predicateObjectMap [ rr:predicateMap [ rr:constant Oracle:department_id ] ; rr:objectMap [ rr:column "DEPARTMENT_ID" ]; ] ;
			rr:predicateObjectMap [ rr:predicateMap [ rr:constant Oracle:department_name ] ; rr:objectMap [ rr:column "DEPARTMENT_NAME" ]; ] ;
			rr:predicateObjectMap [ rr:predicateMap [ rr:constant Oracle:departments_has_employees ] ; rr:objectMap [ rr:termType rr:IRI ; rr:template "http://demo.openlinksw.com:8890/OracleHR/employees/MANAGER_ID={MANAGER_ID}#this" ]; ] ;
			rr:predicateObjectMap [ rr:predicateMap [ rr:constant Oracle:departments_has_locations ] ; rr:objectMap [ rr:termType rr:IRI ; rr:template "http://demo.openlinksw.com:8890/OracleHR/locations/LOCATION_ID={LOCATION_ID}#this" ]; ] ;
			rr:predicateObjectMap [ rr:predicateMap [ rr:constant Oracle:departments_of_employees ] ; rr:objectMap [ rr:parentTriplesMap <#TriplesMapEMPLOYEES>;  rr:joinCondition [ rr:child "DEPARTMENT_ID" ; rr:parent "DEPARTMENT_ID" ] ; ]; ] ;
			rr:predicateObjectMap [ rr:predicateMap [ rr:constant Oracle:departments_of_job_history ] ; rr:objectMap [ rr:parentTriplesMap <#TriplesMapJOB_HISTORY>;  rr:joinCondition [ rr:child "DEPARTMENT_ID" ; rr:parent "DEPARTMENT_ID" ] ; ]; ] .

			<#TriplesMapEMPLOYEES> a rr:TriplesMap; rr:logicalTable [ rr:sqlQuery """
		    																			SELECT "EMPLOYEE_ID", "FIRST_NAME", 
																								"LAST_NAME", 
																								CONCAT(CONCAT("FIRST_NAME",' ') , "LAST_NAME") AS "NAME",
																								"EMAIL", "PHONE_NUMBER", "HIRE_DATE",
		   																				   		"SALARY", "COMMISSION_PCT", "DEPARTMENT_ID", 
																								"JOB_ID", "MANAGER_ID"
		   																				   FROM "Oracle"."HR"."EMPLOYEES"
																				   """
																	];  
			rr:subjectMap [ rr:termType rr:IRI  ; rr:template "http://demo.openlinksw.com:8890/OracleHR/employees/EMPLOYEE_ID={EMPLOYEE_ID}#this"; rr:class Oracle:EMPLOYEES, foaf:Agent, foaf:Person; rr:graph <http://demo.openlinksw.com:8890/OracleHR#> ];
			rr:predicateObjectMap [ rr:predicateMap [ rr:constant Oracle:employee_id ] ; rr:objectMap [ rr:column "EMPLOYEE_ID" ]; ] ;
			rr:predicateObjectMap [ rr:predicateMap [ rr:constant Oracle:first_name ] ; rr:objectMap [ rr:column "FIRST_NAME" ]; ] ;
			rr:predicateObjectMap [ rr:predicateMap [ rr:constant Oracle:last_name ] ; rr:objectMap [ rr:column "LAST_NAME" ]; ] ;
			rr:predicateObjectMap [ rr:predicateMap [ rr:constant Oracle:name ] ; rr:objectMap [ rr:column "NAME" ]; ] ;
			rr:predicateObjectMap [ rr:predicateMap [ rr:constant Oracle:email ] ; rr:objectMap [ rr:column "EMAIL" ]; ] ;
			rr:predicateObjectMap [ rr:predicateMap [ rr:constant Oracle:phone_number ] ; rr:objectMap [ rr:column "PHONE_NUMBER" ]; ] ;
			rr:predicateObjectMap [ rr:predicateMap [ rr:constant Oracle:hire_date ] ; rr:objectMap [ rr:column "HIRE_DATE" ]; ] ;
			rr:predicateObjectMap [ rr:predicateMap [ rr:constant Oracle:salary ] ; rr:objectMap [ rr:column "SALARY" ]; ] ;
			rr:predicateObjectMap [ rr:predicateMap [ rr:constant Oracle:commission_pct ] ; rr:objectMap [ rr:column "COMMISSION_PCT" ]; ] ;
			rr:predicateObjectMap [ rr:predicateMap [ rr:constant Oracle:employees_has_departments ] ; rr:objectMap [ rr:termType rr:IRI ; rr:template "http://demo.openlinksw.com:8890/OracleHR/departments/DEPARTMENT_ID={DEPARTMENT_ID}#this" ]; ] ;
			rr:predicateObjectMap [ rr:predicateMap [ rr:constant Oracle:employees_has_jobs ] ; rr:objectMap [ rr:termType rr:IRI ; rr:template "http://demo.openlinksw.com:8890/OracleHR/jobs/JOB_ID={JOB_ID}#this" ]; ] ;
			rr:predicateObjectMap [ rr:predicateMap [ rr:constant Oracle:employees_of_departments ] ; rr:objectMap [ rr:parentTriplesMap <#TriplesMapDEPARTMENTS>;  rr:joinCondition [ rr:child "EMPLOYEE_ID" ; rr:parent "MANAGER_ID" ] ; ]; ] ;
			rr:predicateObjectMap [ rr:predicateMap [ rr:constant Oracle:employees_has_employees ] ; rr:objectMap [ rr:termType rr:IRI ; rr:template "http://demo.openlinksw.com:8890/OracleHR/employees/MANAGER_ID={MANAGER_ID}#this" ]; ] ;
			rr:predicateObjectMap [ rr:predicateMap [ rr:constant Oracle:employees_of_job_history ] ; rr:objectMap [ rr:parentTriplesMap <#TriplesMapJOB_HISTORY>;  rr:joinCondition [ rr:child "EMPLOYEE_ID" ; rr:parent "EMPLOYEE_ID" ] ; ]; ] .

			<#TriplesMapJOBS> a rr:TriplesMap; rr:logicalTable [ rr:sqlQuery """
																			       SELECT "JOB_ID", "JOB_TITLE", "MIN_SALARY", "MAX_SALARY"
																				   FROM "Oracle"."HR"."JOBS"
																				   """
																	]; 
			rr:subjectMap [ rr:termType rr:IRI  ; rr:template "http://demo.openlinksw.com:8890/OracleHR/jobs/JOB_ID={JOB_ID}#this"; rr:class Oracle:JOBS; rr:graph <http://demo.openlinksw.com:8890/OracleHR#> ];
			rr:predicateObjectMap [ rr:predicateMap [ rr:constant Oracle:job_id ] ; rr:objectMap [ rr:column "JOB_ID" ]; ] ;
			rr:predicateObjectMap [ rr:predicateMap [ rr:constant Oracle:job_title ] ; rr:objectMap [ rr:column "JOB_TITLE" ]; ] ;
			rr:predicateObjectMap [ rr:predicateMap [ rr:constant Oracle:min_salary ] ; rr:objectMap [ rr:column "MIN_SALARY" ]; ] ;
			rr:predicateObjectMap [ rr:predicateMap [ rr:constant Oracle:max_salary ] ; rr:objectMap [ rr:column "MAX_SALARY" ]; ] ;
			rr:predicateObjectMap [ rr:predicateMap [ rr:constant Oracle:jobs_of_employees ] ; rr:objectMap [ rr:parentTriplesMap <#TriplesMapEMPLOYEES>;  rr:joinCondition [ rr:child "JOB_ID" ; rr:parent "JOB_ID" ] ; ]; ] ;
			rr:predicateObjectMap [ rr:predicateMap [ rr:constant Oracle:jobs_of_job_history ] ; rr:objectMap [ rr:parentTriplesMap <#TriplesMapJOB_HISTORY>;  rr:joinCondition [ rr:child "JOB_ID" ; rr:parent "JOB_ID" ] ; ]; ] .

			<#TriplesMapJOB_HISTORY> a rr:TriplesMap; rr:logicalTable [ rr:sqlQuery """
																			       SELECT "START_DATE", "END_DATE", "DEPARTMENT_ID", "EMPLOYEE_ID", "JOB_ID"
																				   FROM "Oracle"."HR"."JOB_HISTORY"
																				   """
																	]; 
			rr:subjectMap [ rr:termType rr:IRI  ; rr:template "http://demo.openlinksw.com:8890/OracleHR/job_history/EMPLOYEE_ID={EMPLOYEE_ID}/START_DATE={START_DATE}#this"; rr:class Oracle:JOB_HISTORY; rr:graph <http://demo.openlinksw.com:8890/OracleHR#> ];
			rr:predicateObjectMap [ rr:predicateMap [ rr:constant Oracle:start_date ] ; rr:objectMap [ rr:column "START_DATE" ]; ] ;
			rr:predicateObjectMap [ rr:predicateMap [ rr:constant Oracle:end_date ] ; rr:objectMap [ rr:column "END_DATE" ]; ] ;
			rr:predicateObjectMap [ rr:predicateMap [ rr:constant Oracle:job_history_has_departments ] ; rr:objectMap [ rr:termType rr:IRI ; rr:template "http://demo.openlinksw.com:8890/OracleHR/departments/DEPARTMENT_ID={DEPARTMENT_ID}#this" ]; ] ;
			rr:predicateObjectMap [ rr:predicateMap [ rr:constant Oracle:job_history_has_employees ] ; rr:objectMap [ rr:termType rr:IRI ; rr:template "http://demo.openlinksw.com:8890/OracleHR/employees/EMPLOYEE_ID={EMPLOYEE_ID}#this" ]; ] ;
			rr:predicateObjectMap [ rr:predicateMap [ rr:constant Oracle:job_history_has_jobs ] ; rr:objectMap [ rr:termType rr:IRI ; rr:template "http://demo.openlinksw.com:8890/OracleHR/jobs/JOB_ID={JOB_ID}#this" ]; ] .

			<#TriplesMapLOCATIONS> a rr:TriplesMap; rr:logicalTable [ rr:sqlQuery """
																			       SELECT "LOCATION_ID", "STREET_ADDRESS", "POSTAL_CODE", "CITY", "STATE_PROVINCE", "COUNTRY_ID"
																				   FROM "Oracle"."HR"."LOCATIONS"
																				   """
																	]; 
			rr:subjectMap [ rr:termType rr:IRI  ; rr:template "http://demo.openlinksw.com:8890/OracleHR/locations/LOCATION_ID={LOCATION_ID}#this"; rr:class Oracle:LOCATIONS; rr:graph <http://demo.openlinksw.com:8890/OracleHR#> ];
			rr:predicateObjectMap [ rr:predicateMap [ rr:constant Oracle:location_id ] ; rr:objectMap [ rr:column "LOCATION_ID" ]; ] ;
			rr:predicateObjectMap [ rr:predicateMap [ rr:constant Oracle:street_address ] ; rr:objectMap [ rr:column "STREET_ADDRESS" ]; ] ;
			rr:predicateObjectMap [ rr:predicateMap [ rr:constant Oracle:postal_code ] ; rr:objectMap [ rr:column "POSTAL_CODE" ]; ] ;
			rr:predicateObjectMap [ rr:predicateMap [ rr:constant Oracle:city ] ; rr:objectMap [ rr:column "CITY" ]; ] ;
			rr:predicateObjectMap [ rr:predicateMap [ rr:constant Oracle:state_province ] ; rr:objectMap [ rr:column "STATE_PROVINCE" ]; ] ;
			rr:predicateObjectMap [ rr:predicateMap [ rr:constant Oracle:locations_has_countries ] ; rr:objectMap [ rr:termType rr:IRI ; rr:template "http://demo.openlinksw.com:8890/OracleHR/countries/COUNTRY_ID={COUNTRY_ID}#this" ]; ] ;
			rr:predicateObjectMap [ rr:predicateMap [ rr:constant Oracle:locations_of_departments ] ; rr:objectMap [ rr:parentTriplesMap <#TriplesMapDEPARTMENTS>;  rr:joinCondition [ rr:child "LOCATION_ID" ; rr:parent "LOCATION_ID" ] ; ]; ] .

			}
	   } ;
	   

-- Generate & Execute Virtual RDF Views Script, hence the use of EXEC()
-- Where R2RML mappings identify <http://demo.openlinksw.com:8890/OracleHR#> as the Graph IRI associated with RDF Views to be generated
-- And <urn:oracle:8890:sql:r2rml:mappings> is the local Named Graph that holds the actual R2RML mappings to be processed


EXEC ('SPARQL ' || DB.DBA.R2RML_MAKE_QM_FROM_G ('urn:oracle:8890:sql:r2rml:mappings'));

-- Test RDF generated Virtual RDF Views --
-- Test 1

SPARQL
SELECT SAMPLE(?s) AS ?sample COUNT(1) AS ?count ?o
FROM <http://demo.openlinksw.com:8890/OracleHR#>
WHERE {?s a ?o}
GROUP BY ?o
ORDER BY DESC(?count)
LIMIT 50 ;

-- Test 2

SPARQL
SELECT DISTINCT ?o
FROM <http://demo.openlinksw.com:8890/OracleHR#>
WHERE {?s a ?o} ;

-- Test 3 

SPARQL
SELECT ?s
FROM <http://demo.openlinksw.com:8890/OracleHR#>
WHERE {?s a foaf:Agent} ;


