Example 9-7 Using an RDF View with Direct Mapping

-- Use the following relational tables.
 
CREATE TABLE dept (
  deptno NUMBER CONSTRAINT pk_DeptTab_deptno PRIMARY KEY,
  dname VARCHAR2(30),
  loc VARCHAR2(30)
);
 
CREATE TABLE emp ( 
  empno NUMBER PRIMARY KEY,
  ename VARCHAR2(30),
  job VARCHAR2(20),
  deptno NUMBER REFERENCES dept (deptno)
);
 
-- Insert some data.
 
INSERT INTO dept (deptno, dname, loc)
  VALUES (1, 'Sales', 'Boston');
INSERT INTO dept (deptno, dname, loc)
  VALUES (2, 'Manufacturing', 'Chicago');
INSERT INTO dept (deptno, dname, loc)
  VALUES (3, 'Marketing', 'Boston');
 
INSERT INTO emp (empno, ename, job, deptno)
  VALUES (1, 'Alvarez', 'SalesRep', 1);
INSERT INTO emp (empno, ename, job, deptno)
  VALUES (2, 'Baxter', 'Supervisor', 2);
INSERT INTO emp (empno, ename, job, deptno)
  VALUES (3, 'Chen', 'Writer', 3);
INSERT INTO emp (empno, ename, job, deptno)
  VALUES (4, 'Davis', 'Technician', 2);
 
-- Create an RDF view model using direct mapping of two tables, EMP and DEPT, 
-- with a base prefix of http://empdb/.
-- Specify KEY_BASED_REF_PROPERTY=T for the options parameter.
 
BEGIN
  sem_apis.create_rdfview_model(
    model_name => 'empdb_model',
    tables => SYS.ODCIVarchar2List('EMP', 'DEPT'),
    prefix => 'http://empdb/',
    options => 'KEY_BASED_REF_PROPERTY=T'
  );
END;
/
 
-- Query an RDF view using SPARQL in a SEM_MATCH-based SQL query. 
-- The next statament is a query against an RDF view named empdb_model 
-- to find the employees who work for any department located in Boston.

SELECT emp
  FROM TABLE(SEM_MATCH(
  '{?emp emp:ref-DEPTNO ?dept . ?dept dept:LOC "Boston"}',
  SEM_Models('empdb_model'),
  NULL,
  SEM_ALIASES(
    SEM_ALIAS('dept','http://empdb/TESTUSER.DEPT#'),
    SEM_ALIAS('emp','http://empdb/TESTUSER.EMP#')
  ),
  null));

-- The preceding query is functionally comparable to this:
SELECT e.empno FROM emp e, dept d WHERE e.deptno = d.deptno AND d.loc = 'Boston';

Example 9-8 Output of Example 9-7

SQL> -- Use the following relational tables.
SQL> 
SQL> CREATE TABLE dept (
  2    deptno NUMBER CONSTRAINT pk_DeptTab_deptno PRIMARY KEY,
  3    dname VARCHAR2(30),
  4    loc VARCHAR2(30)
  5  );
 
Table created.
 
SQL> 
SQL> CREATE TABLE emp (
  2    empno NUMBER PRIMARY KEY,
  3    ename VARCHAR2(30),
  4    job VARCHAR2(20),
  5    deptno NUMBER REFERENCES dept (deptno)
  6  );
 
Table created.
 
SQL> 
SQL> -- Insert some data.
SQL> 
SQL> INSERT INTO dept (deptno, dname, loc)
  2    VALUES (1, 'Sales', 'Boston');
 
1 row created.
 
SQL> INSERT INTO dept (deptno, dname, loc)
  2    VALUES (2, 'Manufacturing', 'Chicago');
 
1 row created.
 
SQL> INSERT INTO dept (deptno, dname, loc)
  2    VALUES (3, 'Marketing', 'Boston');
 
1 row created.
 
SQL> 
SQL> INSERT INTO emp (empno, ename, job, deptno)
  2    VALUES (1, 'Alvarez', 'SalesRep', 1);
 
1 row created.
 
SQL> INSERT INTO emp (empno, ename, job, deptno)
  2    VALUES (2, 'Baxter', 'Supervisor', 2);
 
1 row created.
 
SQL> INSERT INTO emp (empno, ename, job, deptno)
  2    VALUES (3, 'Chen', 'Writer', 3);
 
1 row created.
 
SQL> INSERT INTO emp (empno, ename, job, deptno)
  2    VALUES (4, 'Davis', 'Technician', 2);
 
1 row created.
 
SQL> 
SQL> -- Create an RDF view model using direct mapping of two tables, EMP and DEPT,
SQL> -- with a base prefix of http://empdb/.
SQL> -- Specify KEY_BASED_REF_PROPERTY=T for the options parameter.
SQL> 
SQL> BEGIN
  2    sem_apis.create_rdfview_model(
  3  	 model_name => 'empdb_model',
  4  	 tables => SYS.ODCIVarchar2List('EMP', 'DEPT'),
  5  	 prefix => 'http://empdb/',
  6  	 options => 'KEY_BASED_REF_PROPERTY=T'
  7    );
  8  END;
  9  /
 
PL/SQL procedure successfully completed.
 
SQL> 
SQL> -- Query an RDF view using SPARQL in a SEM_MATCH-based SQL query.
SQL> -- The next statament is a query against an RDF view named empdb_model
SQL> -- to find the employees who work for any department located in Boston.
SQL> 
SQL> SELECT emp
  2    FROM TABLE(SEM_MATCH(
  3  	 '{?emp emp:ref-DEPTNO ?dept . ?dept dept:LOC "Boston"}',
  4  	 SEM_Models('empdb_model'),
  5  	 NULL,
  6  	 SEM_ALIASES(
  7  	   SEM_ALIAS('dept','http://empdb/TESTUSER.DEPT#'),
  8  	   SEM_ALIAS('emp','http://empdb/TESTUSER.EMP#')
  9  	 ),
 10  	 null));
 
EMP                                                                             
--------------------------------------------------------------------------------
http://empdb/TESTUSER.EMP/EMPNO=1                                               
http://empdb/TESTUSER.EMP/EMPNO=3                                               
 
SQL> 
SQL> -- The preceding query is functionally comparable to this:
SQL> SELECT e.empno FROM emp e, dept d WHERE e.deptno = d.deptno AND d.loc = 'Boston';
 
     EMPNO                                                                      
----------                                                                      
         1                                                                      
         3  

Example 9-9 Querying Multiple Data Sets

Example 9-9 queries multiple data sets. In this query, the first triple pattern { ?x rdf:type :Person } will go against native model m1 as usual, but { ?x :name ?name } will go against the local native model m2, and { ?x :email ?email } will go against the local RDB2RDF model rdfview1.

select * from table (sem_match(
'SELECT ?x ?name ?email
 WHERE {
  ?x rdf:type :Person .
  OPTIONAL { SERVICE oram:m2 { ?x :name ?name } }
  OPTIONAL { SERVICE orardbm:rdfview1 { ?x :email ?email } }
}'
sem_models('m1'), null, null, null, null, ' '));

Overloaded SERVICE use is only allowed with a single model specified in the models argument of SEM_MATCH. Overloaded SERVICE queries do not allow multiple models or a rulebase as input. A virtual model that contains multiple models and/or entailments should be used instead for such combinations. In addition, the index_status argument for SEM_MATCH will only check the entailment contained in the virtual model passed as input in the models parameter. This means the status of entailments that are referenced in overloaded SERVICE calls will not be checked.

Example 9-10 Querying Virtual RDB2RDF Data and Native RDF Data

Example 9-10 queries two data sets: the empdb_model from Example 9-7 and a native model named people.

-- Create native model people --
create table atab (gval varchar2(4000), tri sdo_rdf_triple_s);
 
execute sem_apis.create_sem_model('people','atab','tri');
 
create table stab(RDF$STC_GRAPH varchar2(4000), RDF$STC_sub varchar2(4000),
                  RDF$STC_pred varchar2(4000), RDF$STC_obj varchar2(4000));
grant select on stab to mdsys;
grant insert on atab to mdsys;
 
insert into stab values (null, '<http://empdb/TESTUSER.EMP/EMPNO=1>', '<http://people.org/age>', '"35"^^<http://www.w3.org/2001/XMLSchema#int>');
insert into stab values (null, '<http://empdb/TESTUSER.EMP/EMPNO=2>', '<http://people.org/age>', '"39"^^<http://www.w3.org/2001/XMLSchema#int>');
insert into stab values (null, '<http://empdb/TESTUSER.EMP/EMPNO=3>', '<http://people.org/age>', '"30"^^<http://www.w3.org/2001/XMLSchema#int>');
insert into stab values (null, '<http://empdb/TESTUSER.EMP/EMPNO=4>', '<http://people.org/age>', '"42"^^<http://www.w3.org/2001/XMLSchema#int>');
commit;
 
exec sem_apis.bulk_load_from_staging_table('people','testuser','stab');
 
-- Querying multiple datasets --
SELECT emp, age
  FROM TABLE(SEM_MATCH(
    'SELECT ?emp ?age WHERE{
       ?emp peop:age ?age
       SERVICE orardbm:empdb_model { ?emp emp:ref-DEPTNO ?dept . ?dept dept:LOC "Boston" }
    }',
    SEM_Models('people'),
    NULL,
    SEM_ALIASES(
          SEM_ALIAS('dept','http://empdb/TESTUSER.DEPT#'),
          SEM_ALIAS('emp','http://empdb/TESTUSER.EMP#'),
          SEM_ALIAS('peop','http://people.org/')
        ),
    NULL));