10 RDF Views: Relational Data as RDF

Example 10-6 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 10-7 Output of Example 10-6, "Using an RDF View with Direct Mapping"

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 10-8 Querying Multiple Data Sets

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

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

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