-- 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
)
;