¶
16.8.12.Oracle using the demonstration 'Human Resources' 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);