-- Acual Example --- -- Cleanup arificats from prior R2RML processing (which produces Quad Mappings that instruct Virtuoso about RDF Views handling) SPARQL DROP QUAD MAP ; SPARQL CLEAR GRAPH ; SPARQL CLEAR GRAPH ; -- 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 INTO ; SPARQL PREFIX rr: PREFIX Oracle: PREFIX oracle-stat: PREFIX rdf: PREFIX void: PREFIX scovo: PREFIX aowl: PREFIX foaf: INSERT { GRAPH { <#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 ]; 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 ]; 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 ]; 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 ]; 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 ]; 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 ]; 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 as the Graph IRI associated with RDF Views to be generated -- And 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 WHERE {?s a ?o} GROUP BY ?o ORDER BY DESC(?count) LIMIT 50 ; -- Test 2 SPARQL SELECT DISTINCT ?o FROM WHERE {?s a ?o} ; -- Test 3 SPARQL SELECT ?s FROM WHERE {?s a foaf:Agent} ;