-- Setup script for Linked Data Views of Oracle 10 Human Resources Sample Database -- GRANT SELECT ON HR.orama.COUNTRIES TO "SPARQL", "SPARQL_UPDATE" ; GRANT SELECT ON HR.orama.REGIONS TO "SPARQL", "SPARQL_UPDATE" ; GRANT SELECT ON HR.orama.DEPARTMENTS TO "SPARQL", "SPARQL_UPDATE" ; GRANT SELECT ON HR.orama.LOCATIONS TO "SPARQL", "SPARQL_UPDATE" ; GRANT SELECT ON HR.orama.EMPLOYEES TO "SPARQL", "SPARQL_UPDATE" ; GRANT SELECT ON HR.orama.JOBS TO "SPARQL", "SPARQL_UPDATE" ; GRANT SELECT ON HR.orama.JOB_HISTORY TO "SPARQL", "SPARQL_UPDATE" ; ------------------------------------------------------------------- -------- Create rdfs:Class definitions ---------------------------- ttlp ( ' @prefix rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#> . @prefix rdfs: <http://www.w3.org/2000/01/rdf-schema#> . @prefix xsd: <http://www.w3.org/2001/XMLSchema#> . @prefix hr: <http://localhost:8890/schemas/oraclehr/> . hr:countries a rdfs:Class ; rdfs:isDefinedBy <http://localhost:8890/schemas/oraclehr> ; rdfs:label "COUNTRIES" ; rdfs:comment "Oracle HR COUNTRIES table" . hr:country_id a rdf:Property ; rdfs:domain hr:countries ; rdfs:range xsd:string ; rdfs:label "COUNTRY ID" . hr:country_name a rdf:Property ; rdfs:domain hr:countries ; rdfs:range xsd:string ; rdfs:label "COUNTRY NAME" . hr:region_id a rdf:Property ; rdfs:domain hr:countries ; rdfs:range hr:regions ; rdfs:label "REGION ID" . hr:regions a rdfs:Class ; rdfs:isDefinedBy <http://localhost:8890/schemas/oraclehr> ; rdfs:label "REGIONS" ; rdfs:comment "Oracle HR REGIONS table" . hr:region_id a rdf:Property ; rdfs:domain hr:regions ; rdfs:range xsd:integer ; rdfs:label "REGION ID" . hr:region_name a rdf:Property ; rdfs:domain hr:regions ; rdfs:range xsd:string ; rdfs:label "REGION NAME" . hr:departments a rdfs:Class ; rdfs:isDefinedBy <http://localhost:8890/schemas/oraclehr> ; rdfs:label "DEPARTMENTS" ; rdfs:comment "Oracle HR DEPARTMENT table" . hr:department_id a rdf:Property ; rdfs:domain hr:departments ; rdfs:range xsd:integer ; rdfs:label "DEPARTMENT ID" . hr:department_name a rdf:Property ; rdfs:domain hr:departments ; rdfs:range xsd:string ; rdfs:comment "DEPARTMENT NAME" . hr:manager_id a rdf:Property ; rdfs:domain hr:departments ; rdfs:range hr:employees ; rdfs:comment "MANAGER ID" . hr:location_id a rdf:Property ; rdfs:domain hr:departments ; rdfs:range hr:locations ; rdfs:comment "LOCATION ID" . hr:employees a rdfs:Class ; rdfs:isDefinedBy <http://localhost:8890/schemas/oraclehr> ; rdfs:label "employees" ; rdfs:comment "Oracle HR EMPLOYEES table" . hr:employee_id a rdf:Property ; rdfs:domain hr:employees; rdfs:range xsd:integer ; rdfs:label "EMPLOYEE ID" . hr:first_name a rdf:Property ; rdfs:domain hr:employees; rdfs:range xsd:string ; rdfs:label "FIRST NAME" . hr:last_name a rdf:Property ; rdfs:domain hr:employees ; rdfs:range xsd:string ; rdfs:label "LAST NAME" . hr:email a rdf:Property ; rdfs:domain hr:employees; rdfs:range xsd:string ; rdfs:label "EMAIL" . hr:phone_number a rdf:Property ; rdfs:domain hr:employees ; rdfs:range xsd:string ; rdfs:label "PHONE NUMBER" . hr:hire_date a rdf:Property ; rdfs:domain hr:employees ; rdfs:range xsd:date ; rdfs:label "HIRE DATE" . hr:job_id a rdf:Property ; rdfs:domain hr:employees; rdfs:range hr:jobs ; rdfs:label "JOB ID" . hr:salary a rdf:Property ; rdfs:domain hr:employees ; rdfs:range xsd:integer ; rdfs:label "SALARY" . hr:commission_pct a rdf:Property ; rdfs:domain hr:employees ; rdfs:range xsd:integer ; rdfs:label "COMMISSION PCT" . hr:manager_id a rdf:Property ; rdfs:domain hr:employees ; rdfs:range xsd:string ; rdfs:label "MANAGER ID" . hr:department_id a rdf:Property ; rdfs:domain hr:employees ; rdfs:range hr:departments ; rdfs:label "DEPARTMENT ID" . hr:jobs a rdfs:Class ; rdfs:isDefinedBy <http://localhost:8890/schemas/oraclehr> ; rdfs:label "JOBS" ; rdfs:comment "Oracle HR JOBS table" . hr:job_id a rdf:Property ; rdfs:domain hr:jobs ; rdfs:range xsd:string ; rdfs:label "JOB ID" . hr:job_title a rdf:Property ; rdfs:domain hr:jobs ; rdfs:range xsd:string ; rdfs:label "JOB TITLE" . hr:min_salary a rdf:Property ; rdfs:domain hr:jobs ; rdfs:range xsd:number; rdfs:label "MIN SALARY" . hr:max_salary a rdf:Property ; rdfs:domain hr:jobs ; rdfs:range xsd:number; rdfs:label "MAXSALARY" . hr:job_history a rdfs:Class ; rdfs:isDefinedBy <http://localhost:8890/schemas/oraclehr> ; rdfs:label "JOB HISTORY" ; rdfs:comment "Oracle HR JOB HISTORY table" . hr:employee_id a rdf:Property ; rdfs:domain hr:job_history ; rdfs:range hr:employees ; rdfs:label "EMPLOYEE ID" . hr:start_date a rdf:Property ; rdfs:domain hr:job_history ; rdfs:range xsd:date ; rdfs:label "START DATE" . hr:end_date a rdf:Property ; rdfs:domain hr:job_history ; rdfs:range xsd:date ; rdfs:label "END DATE" . hr:job_id a rdf:Property ; rdfs:domain hr:job_history ; rdfs:range hr:jobs ; rdfs:label "JOB ID" . hr:department_id a rdf:Property ; rdfs:domain hr:job_history ; rdfs:range hr:departments ; rdfs:label "DEPARTMENT ID" . hr:locations a rdfs:Class ; rdfs:isDefinedBy <http://localhost:8890/schemas/oraclehr> ; rdfs:label "LOCATIONS" ; rdfs:comment "Oracle HR JOB LOCATIONS table" . hr:location_id a rdf:Property ; rdfs:domain hr:locations ; rdfs:range xsd:number ; rdfs:label "LOCATION ID" . hr:street_address a rdf:Property ; rdfs:domain hr:locations ; rdfs:range xsd:string ; rdfs:label "STREET ADDRESS" . hr:postal_code a rdf:Property ; rdfs:domain hr:locations ; rdfs:range xsd:string ; rdfs:label "POSTAL CODE" . hr:city a rdf:Property ; rdfs:domain hr:locations ; rdfs:range xsd:string ; rdfs:label "CITY" . hr:state_province a rdf:Property ; rdfs:domain hr:locations ; rdfs:range xsd:string ; rdfs:label "STATE PROVINCE" . hr:country_id a rdf:Property ; rdfs:domain hr:locations ; rdfs:range hr:countries ; rdfs:label "COUNTRY" . ', '', 'http://localhost:8890/schemas/oraclehr', 0); --------------------------------------------------------------- ----------- Create IRI Classes ------------- CREATE FUNCTION DB.DBA.JOB_HISTORY ( IN EMPLOYEE_ID INTEGER, IN START_DATE DATE ) RETURNS VARCHAR { RETURN sprintf_or_null ( 'http://localhost:8890/oraclehr/job_history/%d_%s#this', EMPLOYEE_ID, CAST (START_DATE AS VARCHAR) ) ; } ; CREATE FUNCTION DB.DBA.JOB_HISTORY_INV_1 ( IN id VARCHAR ) RETURNS INTEGER { RETURN sprintf_inverse ( id, 'http://localhost:8890/oraclehr/job_history/%d_%s#this', 2 ) [0]; } ; CREATE FUNCTION DB.DBA.JOB_HISTORY_INV_2 ( IN id VARCHAR ) RETURNS DATE { DECLARE EXIT HANDLER FOR SQLSTATE '*' { RETURN NULL; } ; RETURN CAST ( sprintf_inverse ( id, 'http://localhost:8890/oraclehr/job_history/%d_%s#this', 2 ) [1] AS DATE ) ; } ; GRANT EXECUTE ON DB.DBA.JOB_HISTORY TO "SPARQL", "SPARQL_UPDATE" ; GRANT EXECUTE ON DB.DBA.JOB_HISTORY_URI_INV_1 TO "SPARQL", "SPARQL_UPDATE" ; GRANT EXECUTE ON DB.DBA.JOB_HISTORY_URI_INV_2 TO "SPARQL", "SPARQL_UPDATE" ; sparql CREATE IRI CLASS <http://localhost:8890/schemas/oraclehr/countries_iri> "http://^{URIQADefaultHost}^/oraclehr/countries/%s#this" ( IN COUNTRY_ID VARCHAR NOT NULL ) . CREATE IRI CLASS <http://localhost:8890/schemas/oraclehr/regions_iri> "http://^{URIQADefaultHost}^/oraclehr/regions/%d#this" ( IN REGION_ID INTEGER NOT NULL ) . CREATE IRI CLASS <http://localhost:8890/schemas/oraclehr/departments_iri> "http://^{URIQADefaultHost}^/oraclehr/departments/%d#this" ( IN DEPARTMENT_ID INTEGER NOT NULL ) . CREATE IRI CLASS <http://localhost:8890/schemas/oraclehr/employees_iri> "http://^{URIQADefaultHost}^/oraclehr/employees/%d#this" ( IN EMPLOYEE_ID INTEGER NOT NULL ) . CREATE IRI CLASS <http://localhost:8890/schemas/oraclehr/jobs_iri> "http://^{URIQADefaultHost}^/oraclehr/jobs/%s#this" ( IN JOB_ID VARCHAR NOT NULL ) . CREATE IRI CLASS <http://localhost:8890/schemas/oraclehr/job_history_iri> USING FUNCTION DB.DBA.JOB_HISTORY ( IN EMPLOYEE_ID INTEGER NOT NULL, IN START_DATE DATE NOT NULL ) RETURNS VARCHAR NOT NULL, FUNCTION DB.DBA.JOB_HISTORY_INV_1 ( IN id VARCHAR ) RETURNS INTEGER, FUNCTION DB.DBA.JOB_HISTORY_INV_2 ( IN id VARCHAR ) RETURNS DATE OPTION ( bijection, RETURNS "http://localhost:8890/oraclehr/job_history/%d_%s#this" ) . CREATE IRI CLASS <http://localhost:8890/schemas/oraclehr/locations_iri> "http://^{URIQADefaultHost}^/oraclehr/locations/%d#this" ( IN LOCATION_ID INTEGER NOT NULL ) . ; -------------------------------------------------------------------- ------------- Create Quad Store ------------------------------------ sparql PREFIX hr: <http://localhost:8890/schemas/oraclehr/> ALTER QUAD STORAGE virtrdf:DefaultQuadStorage FROM HR.orama.COUNTRIES AS countries_tbl FROM HR.orama.REGIONS AS regions_tbl FROM HR.orama.DEPARTMENTS AS departments_tbl FROM HR.orama.EMPLOYEES AS employees_tbl FROM HR.orama.EMPLOYEES AS employees_tbl_1 ### alias required to represent recursive FK relationship ( hr: has_manager ) below. FROM HR.orama.JOBS AS jobs_tbl FROM HR.orama.JOB_HISTORY AS job_history_tbl FROM HR.orama.LOCATIONS AS locations_tbl { CREATE virtrdf:oraclehr AS GRAPH <http://localhost:8890/oraclehr> { hr:countries_iri(countries_tbl.COUNTRY_ID) a hr:countries AS virtrdf:countires_country_id ; hr:country_name countries_tbl.COUNTRY_NAME AS virtrdf:countries_country_name ; hr:region_id hr:regions_iri(regions_tbl.REGION_ID) WHERE ( ^{countries_tbl.}^.REGION_ID = ^{regions_tbl.}^.REGION_ID ) AS virtrdf:countries_region_id . hr:regions_iri(regions_tbl.REGION_ID) a hr:regions AS virtrdf:regions_region_id ; hr:region_name regions_tbl.REGION_NAME AS virtrdf:regions_region_name . hr:departments_iri(departments_tbl.DEPARTMENT_ID) a hr:departments AS virtrdf:departments_department_id ; hr:department_name departments_tbl.DEPARTMENT_NAME AS virtrdf:departments_department_name ; hr:location_id hr:locations_iri(locations_tbl.LOCATION_ID) WHERE (^{departments_tbl.}^.LOCATION_ID = ^{locations_tbl.}^.LOCATION_ID) AS virtrdf:departments_location_id ; hr:manager_id hr:employees_iri(employees_tbl.EMPLOYEE_ID) WHERE (^{departments_tbl.}^.MANAGER_ID = ^{employees_tbl.}^.EMPLOYEE_ID) AS virtrdf:departments_manager_id . hr:employees_iri(employees_tbl.EMPLOYEE_ID) a hr:employees AS virtrdf:employees_employee_id ; hr:department_id hr:departments_iri(departments_tbl.DEPARTMENT_ID) WHERE (^{employees_tbl.}^.DEPARTMENT_ID = ^{departments_tbl.}^.DEPARTMENT_ID) AS virtrdf:employees_department_id ; hr:job_id hr:jobs_iri(jobs_tbl.JOB_ID) WHERE (^{employees_tbl.}^.JOB_ID = ^{jobs_tbl.}^.JOB_ID) AS virtrdf:employees_job_id ; hr:manager_id employees_tbl.MANAGER_ID AS virtrdf:employees_manager_id ; hr:commissin_pct employees_tbl.COMMISSION_PCT AS virtrdf:employees_commission_pct ; hr:email employees_tbl.EMAIL AS virtrdf:employees_email ; hr:first_name employees_tbl.FIRST_NAME AS virtrdf:employees_first_name ; hr:hire_date employees_tbl.HIRE_DATE AS virtrdf:employees_hire_date ; hr:last_name employees_tbl.LAST_NAME AS virtrdf:employees_last_name ; hr:phone_number employees_tbl.PHONE_NUMBER AS virtrdf:employees_phone_number ; hr:salary employees_tbl.SALARY AS virtrdf:employees_salary ; hr:has_job_history hr:job_history_iri ( job_history_tbl.EMPLOYEE_ID, job_history_tbl.START_DATE ) WHERE (^{employees_tbl.}^.EMPLOYEE_ID = ^{job_history_tbl.}^.EMPLOYEE_ID) AS virtrdf:employees_has_job_history ; hr:has_manager hr:employees_iri(employees_tbl_1.EMPLOYEE_ID) WHERE (^{employees_tbl.}^.MANAGER_ID = ^{employees_tbl_1.}^.EMPLOYEE_ID) AS virtrdf:employees_has_manager . hr:locations_iri(locations_tbl.LOCATION_ID) a hr:locations AS virtrdf:locations_location_id ; hr:country_id hr:countries_iri(countries_tbl.COUNTRY_ID) WHERE (^{locations_tbl.}^.COUNTRY_ID = ^{countries_tbl.}^.COUNTRY_ID) AS virtrdf:locations_country_id ; hr:city locations_tbl.CITY AS virtrdf:locations_city ; hr:postal_code locations_tbl.POSTAL_CODE AS virtrdf:locations_postal_code ; hr:state_province locations_tbl.STATE_PROVINCE AS virtrdf:locations_state_province ; hr:street_address locations_tbl.STREET_ADDRESS AS virtrdf:locations_street_address . hr:jobs_iri(jobs_tbl.JOB_ID) a hr:jobs AS virtrdf:jobs_job_id ; hr:job_title jobs_tbl.JOB_TITLE AS virtrdf:jobs_job_title ; hr:max_salary jobs_tbl.MAX_SALARY AS virtrdf:jobs_max_salary ; hr:min_salary jobs_tbl.MIN_SALARY AS virtrdf:jobs_min_salary . hr:job_history_iri(job_history_tbl.EMPLOYEE_ID, job_history_tbl.START_DATE) a hr:job_history AS virtrdf:job_history_pk ; hr:employee_id hr:employees_iri(employees_tbl.EMPLOYEE_ID) WHERE (^{job_history_tbl.}^.EMPLOYEE_ID = ^{employees_tbl.}^.EMPLOYEE_ID) AS virtrdf:job_history_employee_id ; hr:department_id hr:departments_iri(departments_tbl.DEPARTMENT_ID) WHERE (^{job_history_tbl.}^.DEPARTMENT_ID = ^{departments_tbl.}^.DEPARTMENT_ID) AS virtrdf:job_history_department_id ; hr:job_id hr:jobs_iri(jobs_tbl.JOB_ID) WHERE (^{job_history_tbl.}^.JOB_ID = ^{jobs_tbl.}^.JOB_ID) AS virtrdf:job_history_job_id ; hr:start_date job_history_tbl.START_DATE AS virtrdf:job_history_start_date ; hr:end_date job_history_tbl.END_DATE AS virtrdf:job_history_end_date . } . } . ; DELETE FROM db.dba.url_rewrite_rule_list WHERE urrl_list LIKE 'oraclehr_rule%' ; DELETE FROM db.dba.url_rewrite_rule WHERE urr_rule LIKE 'oraclehr_rule%' ; DB.DBA.URLREWRITE_CREATE_REGEX_RULE ( 'oraclehr_rule1', 1, '(/[^#]*)', VECTOR('path'), 1, '/about/html/http/^{URIQADefaultHost}^%s', VECTOR('path'), NULL, '(text/html)|(\\*/\\*)', 0, 303 ) ; DB.DBA.URLREWRITE_CREATE_REGEX_RULE ( 'oraclehr_rule2', 1, '(/[^#]*)', VECTOR('path'), 1, '/sparql?query=DESCRIBE+%%3Chttp%%3A//localhost%%3A8890%U%%23this%%3E+%%3Chttp%%3A//localhost%%3A8890%U%%23this%%3E+FROM+%%3Chttp%%3A//localhost%%3A8890/oraclehr%%3E&format=%U', VECTOR('path', 'path', '*accept*'), NULL, '(text/rdf.n3)|(application/rdf.xml)', 0, NULL ) ; DB.DBA.URLREWRITE_CREATE_RULELIST ( 'oraclehr_rule_list1', 1, VECTOR ( 'oraclehr_rule1', 'oraclehr_rule2' ) ) ; -- ensure a VD for the IRIs which begins with / VHOST_REMOVE (lpath=>'/oraclehr'); VHOST_DEFINE ( lpath=>'/oraclehr', ppath=>'/DAV/oraclehr/', is_dav=>1, vsp_user=>'dba', is_brws=>0, opts=>VECTOR ('url_rewrite', 'oraclehr_rule_list1') ) ; DELETE FROM db.dba.url_rewrite_rule_list WHERE urrl_list LIKE 'oracle_schemas_rule%' ; DELETE FROM db.dba.url_rewrite_rule WHERE urr_rule LIKE 'oracle_schemas_rule%' ; DB.DBA.URLREWRITE_CREATE_REGEX_RULE ( 'oracle_schemas_rule1', 1, '(/[^#]*)', VECTOR('path'), 1, '/about/html/http/^{URIQADefaultHost}^%s', VECTOR('path'), NULL, '(text/html)|(\\*/\\*)', 0, 303 ) ; DB.DBA.URLREWRITE_CREATE_REGEX_RULE ( 'oracle_schemas_rule2', 1, '(/[^#]*)', VECTOR('path'), 1, '/sparql?query=CONSTRUCT+{+%%3Chttp%%3A//localhost%%3A8890%U%%3E+%%3Fp+%%3Fo+}%%0D%%0AFROM+%%3Chttp%%3A//localhost%%3A8890/schemas/oraclehr%%3E+%%0D%%0AWHERE+{+%%3Chttp%%3A//localhost%%3A8890%U%%3E+%%3Fp+%%3Fo+}&format=%U', VECTOR('path','path','*accept*'), NULL, '(text/rdf.n3)|(application/rdf.xml)', 0, NULL ) ; DB.DBA.URLREWRITE_CREATE_RULELIST ( 'oracle_schemas_rule_list1', 1, VECTOR ( 'oracle_schemas_rule1', 'oracle_schemas_rule2' ) ) ; -- ensure a VD for the IRIs which begins with / VHOST_REMOVE (lpath=>'/schema/oraclehr'); VHOST_DEFINE ( lpath=>'/schemas/oraclehr', ppath=>'/DAV/schemas/oraclehr/', is_dav=>1, vsp_user=>'dba', is_brws=>0, opts=>VECTOR ('url_rewrite', 'oracle_schemas_rule_list1') ) ; DB.DBA.XML_SET_NS_DECL ( 'hr', 'http://^{URIQADefaultHost}^/schemas/oraclehr/', 2 ) ;