¶
16.8.11. Oracle Demonstration 'HR' Database
Live links to a sample instance
Script to set up your own instance
-- 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://example.com/schemas/oraclehr/> . hr:countries a rdfs:Class ; rdfs:isDefinedBy <http://example.com/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://example.com/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://example.com/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://example.com/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://example.com/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://example.com/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://example.com/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://example.com/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://example.com/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://example.com/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://example.com/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://example.com/schemas/oraclehr/countries_iri> "http://^{URIQADefaultHost}^/oraclehr/countries/%s#this" (in COUNTRY_ID varchar not null) . create iri class <http://example.com/schemas/oraclehr/regions_iri> "http://^{URIQADefaultHost}^/oraclehr/regions/%d#this" (in REGION_ID integer not null) . create iri class <http://example.com/schemas/oraclehr/departments_iri> "http://^{URIQADefaultHost}^/oraclehr/departments/%d#this" (in DEPARTMENT_ID integer not null) . create iri class <http://example.com/schemas/oraclehr/employees_iri> "http://^{URIQADefaultHost}^/oraclehr/employees/%d#this" (in EMPLOYEE_ID integer not null) . create iri class <http://example.com/schemas/oraclehr/jobs_iri> "http://^{URIQADefaultHost}^/oraclehr/jobs/%s#this" (in JOB_ID varchar not null) . create iri class <http://example.com/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://example.com/oraclehr/job_history/%d_%s#this") . create iri class <http://example.com/schemas/oraclehr/locations_iri> "http://^{URIQADefaultHost}^/oraclehr/locations/%d#this" (in LOCATION_ID integer not null) . ; -------------------------------------------------------------------- ------------- Create Quad Store ------------------------------------ SPARQL prefix hr: <http://example.com/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://example.com/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);