Customizing R2RML Scripts in Virtuoso

Virtuoso Universal Server Tips, Tricks, and HowTos
, , , ,
1

Generating RDF Linked Data from SQL Relational Data

Why

There are a wide range of shared ontologies/vocabularies (Friend-Of-A-Friend [FOAF], Organization, Bibliographic, etc.) available to any data integration practioner seeking to create RDF-based Linked Data. Naturally, this availability also extends to those seeking to create RDF Views over existing data, now represented as Tables in a typical ODBC/JDBC-accessible RDBMS that supports the SQL Query Language.

From the SQL RDBMS perspective, there are also cases where data straddles multiple columns across a variety of tables as part of a data alignment effort that underlies the creation of effective RDF Views.

In this post, I’ll describe how Virtuoso leverages that semantic richness in R2RML, creating RDF Views over SQL Tables using terms from multiple shared ontologies while also handling the structural disparity that exists in a typical RDBMS schema.

What

The Virtuoso Conductor can produce R2RML scripts through an automated wizard. These auto-generated scripts build a generic ontology from the SQL relational table(s).

Virtuoso users can customize these basic scripts to map automatically-generated column-types to class-properties from an external ontology, or to map multiple table columns to a single RDF relation, among other things.

How

Prerequisites

  • This article will use the Virtuoso sample Schools database ( schools_db_dav.vad ). This VAD can be downloaded (for Virtuoso 7.x or for Virtuoso 8.x) and installed through the System Admin → Packages tab of the Virtuoso Conductor. After installation, we’ll be focusing on two of its tables — "School"."DBA"."Course" and "School"."DBA"."Department" .
  • Three other VADs — the Faceted Browser ( fct_dav.vad ), the R2RML Processor Module ( rdb2rdf_dav.vad ), and the Linked Open Data Transformation Middleware (“Sponger”) ( cartridges_dav.vad ) — also need to be installed. The latest version of each of these VADs can be downloaded from the OpenLink website, and installed through the System Admin → Packages tab of the Virtuoso Conductor.

Generate base R2RML script

First the base R2RML script needs to be generated for the two tables, which can be done using the Virtuoso Conductor from the Linked Data → Views tab by:

[1] Select the School Database (qualified by the literal identifier "School" ) from the drop-down menu.

[2] Tick the checkboxes for the "School"."DBA"."Course" and "School"."DBA"."Department" tables.

Tables to be used in R2RML mapping

[3] Click on the Generate via Wizard button.

[4] Then click the Prepare to Execute button to generate a collection of RDF sentences/statements (a/k/a, an R2RML Graph) that describes how the selected tables are to be transformed. Note: at this point, you have an R2RML Graph that describes mapping rules using terms from the W3C’s R2RML ontology .

Virtuoso generated base R2RML Graph

[5] Copy the content of the area labeled R2RML Graph to a local text editor for customization purposes. ( Note: Whitespace in the code-blocks below has been adjusted to improve readability. Any sequence of multiple whitespace characters — spaces, tabs, line-feeds, etc. — may be collapsed to a single whitespace character, if you wish.)

Note: This article uses our live demo instance at http://demo.openlinksw.com , hence that domain is used in the prefix declarations used to qualify terms used in this particular R2RML Graph.

@prefix          rr: <http://www.w3.org/ns/r2rml#> .
@prefix      School: <http://demo.openlinksw.com/schemas/School/> .
@prefix school-stat: <http://demo.openlinksw.com/School/stat#> .
@prefix         rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#> .
@prefix        void: <http://rdfs.org/ns/void#> .
@prefix       scovo: <http://purl.org/NET/scovo#> .
@prefix        aowl: <http://bblfish.net/work/atom-owl/2006-06-06/> .
<#TriplesMapCourse> 
  a rr:TriplesMap ; 
  rr:logicalTable
    [ rr:tableSchema  "School" ; 
      rr:tableOwner   "DBA" ; 
      rr:tableName    "Course" 
    ] ; 
  rr:subjectMap
    [ rr:termType  rr:IRI ; 
      rr:template  "http://demo.openlinksw.com/School/course/CourseID/{CourseID}#this" ; 
      rr:class  School:Course; 
      rr:graph  <http://demo.openlinksw.com/School#> 
    ] ;
  rr:predicateObjectMap
    [ rr:predicateMap
        [ rr:constant  School:courseid ] ; 
      rr:objectMap
        [ rr:column    "CourseID" ] ; 
    ] ;
  rr:predicateObjectMap
    [ rr:predicateMap
        [ rr:constant  School:title ] ; 
      rr:objectMap
        [ rr:column    "Title" ] ; 
    ] ;
  rr:predicateObjectMap
    [ rr:predicateMap 
        [ rr:constant  School:credits ] ; 
      rr:objectMap
        [ rr:column    "Credits" ] ; 
    ] ;
  rr:predicateObjectMap
    [ rr:predicateMap
        [ rr:constant  School:course_has_department ] ; 
      rr:objectMap
        [ rr:termType  rr:IRI ; 
          rr:template  "http://demo.openlinksw.com/School/department/DepartmentID/{DepartmentID}#this" 
        ] ; 
    ] .
<#TriplesMapDepartment> 
  a rr:TriplesMap ; 
  rr:logicalTable
    [ rr:tableSchema  "School" ; 
      rr:tableOwner   "DBA" ; 
      rr:tableName    "Department" 
    ] ; 
  rr:subjectMap
    [ rr:termType  rr:IRI ; 
      rr:template  "http://demo.openlinksw.com/School/department/DepartmentID/{DepartmentID}#this"; 
      rr:class    School:Department; 
      rr:graph    <http://demo.openlinksw.com/School#> 
    ] ;
  rr:predicateObjectMap
    [ rr:predicateMap
        [ rr:constant School:departmentid ] ; 
      rr:objectMap
        [ rr:column   "DepartmentID" ] ; 
    ] ;
  rr:predicateObjectMap
    [ rr:predicateMap
        [ rr:constant School:name ] ; 
      rr:objectMap
        [ rr:column   "Name" ] ; 
    ] ;
  rr:predicateObjectMap
    [ rr:predicateMap
        [ rr:constant School:budget ] ; 
      rr:objectMap
        [ rr:column   "Budget" ] ; 
    ] ;
  rr:predicateObjectMap
    [ rr:predicateMap
        [ rr:constant School:startdate ] ; 
      rr:objectMap
        [ rr:column   "StartDate" ] ; 
    ] ;
  rr:predicateObjectMap
    [ rr:predicateMap
        [ rr:constant School:administrator ] ; 
      rr:objectMap
        [ rr:column   "Administrator" ] ; 
    ] ;
  rr:predicateObjectMap
    [ rr:predicateMap
        [ rr:constant  School:department_of_course ] ; 
      rr:objectMap
        [ rr:parentTriplesMap  <#TriplesMapCourse> ;  
          rr:joinCondition
            [ rr:child   "DepartmentID" ; 
              rr:parent  "DepartmentID"
            ] ; 
        ] ; 
    ] .

Customize Mappings using terms from a Shared Ontology

In this section, we’ll enhance the basic mapping template using a third party ontology. Here, we’ll map the School.DBA.Department table to a Class in the FOAF Ontology.

FOAF Ontology Class Mappings

Virtuoso includes a number of preloaded ontologies. You can obtain a list of Virtuoso’s “known” ontologies (i.e., those imported into the database instance at some point in time) from the Linked Data → Schemas tab of the Conductor, or by running a SPARQL query (as shown below) scoped to entities of type owl:Ontology . Note: ontologies only need to be loaded for use in the Conductor Linked Data View wizard to perform the mappings, such that classes and properties of the ontology can be presented for selection in the wizard.

SELECT DISTINCT ?onto ?name
WHERE
  {            ?onto  a               owl:Ontology .
    OPTIONAL { ?onto  dc:title        ?name }
    OPTIONAL { ?onto  dc:name         ?name }
    OPTIONAL { ?onto  skos:prefLabel  ?name }
    OPTIONAL { ?onto  skos:label      ?name }
    OPTIONAL { ?onto  schema:name     ?name }
    OPTIONAL { ?onto  schema:title    ?name }
    OPTIONAL { ?onto  rdfs:comment    ?name }
  }

The instances of owl:Ontology are identified by the variable ?onto . For one of these instances, the variable ?onto resolves to the constant http://xmlns.com/foaf/0.1/ , which is the HTTP URI that identifies the Friend of a Friend (FOAF) vocabulary. Thus, we can proceed to mapping the Name column of the School.DBA.Department table to the foaf:name property associated with the foaf:Organization class.

[1] Add the foaf: prefix declaration to the preamble of the R2RML script:

@prefix  foaf: <http://xmlns.com/foaf/0.1/> .

[2] Add the foaf:Organization class to the list of objects (values) for the rr:class relation (attribute) of the School.DBA.Department mapping:

rr:class  School:Department , 
          foaf:Organization ;

[3] Replace School:name with foaf:name as object of the rr:constant relation (attribute) thereby mapping it to the "Name" column of the School.DBA.Department table:

rr:predicateObjectMap
  [ rr:predicateMap
      [ rr:constant  foaf:name ] ; 
    rr:objectMap
      [ rr:column    "Name" ] ; 
  ] ;

[4] Having made these changes, we can see their effect in the generated RDF Linked Data Views— the foaf:name property mapping — with a query like the one below. Note: This query uses SPASQL, a/k/a SPARQL-in-SQL, through the iSQL interface. A similar query could be run through the /sparql endpoint, by dropping the leading SPARQL keyword and the trailing semicolon, which are part of SPASQL syntax, leaving only SPARQL syntax.

SQL> SPARQL SELECT * FROM <http://demo.openlinksw.com/School#> WHERE { ?s foaf:name ?o };
s                                                                                 o
LONG VARCHAR                                                                      NVARCHAR NOT NULL
_______________________________________________________________________________
http://demo.openlinksw.com/School/department/DepartmentID/1#this                       Engineering
http://demo.openlinksw.com/School/department/DepartmentID/2#this                       English
http://demo.openlinksw.com/School/department/DepartmentID/4#this                       Economics
http://demo.openlinksw.com/School/department/DepartmentID/7#this                       Mathematics
4 Rows. -- 21 msec.
SQL>

An ontology does not have to be preloaded into Virtuoso to be used in R2RML mapping scripts.

Here we’ll use steps similar to the prior section to apply additional customization to the mapping template, this time using terms from the Organization Ontology to map the School.DBA.Department table to the org:Organization property of the org:OrganizationalUnit class.

[1] Add the org: prefix declaration to the preamble of the R2RML script:

@prefix  org: <http://www.w3.org/ns/org#> .

[2] Add the org:OrganizationalUnit class to the rr:class attribute of the School.DBA.Department R2RML mapping. Note: one table column may be mapped to multiple relations (attributes) in RDF.

rr:class  School:Department , 
          foaf:Organization , 
          org:OrganizationalUnit ;

[3] Here’s the final mapping:

@prefix          rr: <http://www.w3.org/ns/r2rml#> .
@prefix      School: <http://demo.openlinksw.com/schemas/School/> .
@prefix school-stat: <http://demo.openlinksw.com/School/stat#> .
@prefix         rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#> .
@prefix        void: <http://rdfs.org/ns/void#> .
@prefix       scovo: <http://purl.org/NET/scovo#> .
@prefix        aowl: <http://bblfish.net/work/atom-owl/2006-06-06/> .
@prefix        foaf: <http://xmlns.com/foaf/0.1/> . 
@prefix         org: <http://www.w3.org/ns/org#> .
<#TriplesMapDepartment>
  foaf:name  "Department Table Mappings" ;
  a          rr:TriplesMap ; 
  rr:logicalTable
    [ rr:tableSchema "School" ; 
      rr:tableOwner  "DBA" ; 
      rr:tableName   "Department"
    ] ; 
  rr:subjectMap
    [ rr:termType  rr:IRI ; 
      rr:template "http://demo.openlinksw.com/School/department/DepartmentID/{DepartmentID}#this" ; 
      rr:class  School:Department , 
                foaf:Organization , 
                org:OrganizationalUnit ; 
      rr:graph  <http://demo.openlinksw.com/School#> ] ;
  rr:predicateObjectMap
    [ rr:predicateMap
        [ rr:constant  School:departmentid ] ; 
      rr:objectMap
        [ rr:column  "DepartmentID" ] ; 
    ] ;
  rr:predicateObjectMap
    [ rr:predicateMap
        [ rr:constant  org:Organization ] ; 
      rr:objectMap
        [ rr:column    "Name" ] ; 
    ] ;
  rr:predicateObjectMap
    [ rr:predicateMap
        [ rr:constant  School:budget ] ; 
      rr:objectMap
        [ rr:column    "Budget" ] ; 
    ] ;
  rr:predicateObjectMap
    [ rr:predicateMap
        [ rr:constant  School:startdate ] ; 
      rr:objectMap
        [ rr:column    "StartDate" ] ; 
    ] ;
  rr:predicateObjectMap
    [ rr:predicateMap
        [ rr:constant  School:administrator ] ; 
      rr:objectMap
        [ rr:column    "Administrator" ] ; 
    ] ;
  rr:predicateObjectMap
    [ rr:predicateMap
        [ rr:constant  School:department_of_course ] ; 
      rr:objectMap
        [ rr:parentTriplesMap <#TriplesMapCourse> ;  
          rr:joinCondition
            [ rr:child  "DepartmentID" ; 
              rr:parent "DepartmentID" ] ; 
        ] ; 
    ] .

[4] As before, having made these changes, we can see their effect in the generated RDF Linked Data Views — the org:Organization property mapping — with a query like the one below.

SQL> SPARQL PREFIX org: <http://www.w3.org/ns/org#> SELECT * FROM <http://demo.openlinksw.com/School#> WHERE { ?s org:Organization ?o };
s                                                                                 o
LONG VARCHAR                                                                      NVARCHAR NOT NULL
_______________________________________________________________________________
http://demo.openlinksw.com/School/department/DepartmentID/1#this                       Engineering
http://demo.openlinksw.com/School/department/DepartmentID/2#this                       English
http://demo.openlinksw.com/School/department/DepartmentID/4#this                       Economics
http://demo.openlinksw.com/School/department/DepartmentID/7#this                       Mathematics
4 Rows. -- 21 msec.
SQL>

Map multiple columns to a single RDF relation

Multiple columns, from one or multiple tables, can be mapped to a single RDF relation by using the R2RML rr:sqlQuery specifier to construct a custom SQL query to merge the column names together.

Here, we’ll combine the "School"."DBA"."Department"."Name" column with the "School"."DBA"."Course"."Title" column to produce a composite of the Department Name and Course Title, i.e., "Department — Title" with the query:

rr:sqlQuery 
  """SELECT 
  "CourseID", 
  "d"."Name", 
  "c"."Title", 
  CONCAT("d"."Name", ' - ', "c"."Title") AS "DeptTitle" 
FROM "School"."DBA"."Course" "c", 
     "School"."DBA"."Department" "d" 
WHERE "c"."DepartmentID" = "d"."DepartmentID" 
ORDER BY "CourseID" ;"""

A R2RML mapping can also be created directly from a query, rather than going through a table definition, by using the rr:sqlQuery relation instead of the rr:logicalTable relation:

<#TriplesMapDeptCourse> 
  foaf:name  "Department Table Mappings" ;
  a          rr:TriplesMap; 
  rr:logicalTable
    [ rr:sqlQuery """SELECT "CourseID", "d"."Name", "c"."Title", CONCAT("d"."Name", ' - ', "c"."Title") AS "DeptTitle" FROM "School"."DBA"."Course" "c", "School"."DBA"."Department" "d" WHERE "c"."DepartmentID" = "d"."DepartmentID" ORDER BY "CourseID" ;""" ] ; 
  rr:subjectMap
    [ rr:termType  rr:IRI ; 
      rr:template "http://demo.openlinksw.com/School/DeptCourse/CourseID={CourseID}#this" ; 
      rr:class  School:DeptCourse ; 
      rr:graph  <http://demo.openlinksw.com/School#> 
    ] ;
  rr:predicateObjectMap
    [ rr:predicateMap
        [ rr:constant  School:courseid ] ; 
          rr:objectMap
        [ rr:column "CourseID" ] ; 
    ] ;
  rr:predicateObjectMap
    [ rr:predicateMap
        [ rr:constant School:name ] ; 
      rr:objectMap
        [ rr:column "Name" ] ; 
    ] ;
  rr:predicateObjectMap
    [ rr:predicateMap
        [ rr:constant  School:title ] ; 
      rr:objectMap
        [ rr:column "Title" ] ; 
    ] ;
  rr:predicateObjectMap
    [ rr:predicateMap
        [ rr:constant School:depttitle ] ; 
      rr:objectMap
        [ rr:column "DeptTitle" ] ; 
    ] .

The following triple pattern is the mapping of the DeptTitle column alias, which is a concatenation of the two columns (i.e., concat(d.Name,'-', c.Title) ) in the query specified in rr:sqlQuery :

rr:predicateObjectMap
  [ rr:predicateMap 
      [ rr:constant  School:depttitle ] ; 
        rr:objectMap [ rr:column "DeptTitle" 
  ] ;

Having made these latest changes, we can see their effect in the generated RDF Linked Data Views — the School:depttitle mapping of the concatenated column values — with a query like the one below.

SQL> SPARQL SELECT * FROM <http://demo.openlinksw.com/School#> WHERE { ?s <http://demo.openlinksw.com/schemas/School/depttitle> ?o };
s                                                                                 o
LONG VARCHAR                                                                      NVARCHAR
_______________________________________________________________________________
http://demo.openlinksw.com/School/DeptCourse/CourseID=1045#this                        Mathematics - Calculus
http://demo.openlinksw.com/School/DeptCourse/CourseID=1050#this                        Engineering - Chemistry
http://demo.openlinksw.com/School/DeptCourse/CourseID=1061#this                        Engineering - Physics
http://demo.openlinksw.com/School/DeptCourse/CourseID=2021#this                        English - Composition
http://demo.openlinksw.com/School/DeptCourse/CourseID=2030#this                        English - Poetry
http://demo.openlinksw.com/School/DeptCourse/CourseID=2042#this                        English - Literature
http://demo.openlinksw.com/School/DeptCourse/CourseID=3141#this                        Mathematics - Trigonometry
http://demo.openlinksw.com/School/DeptCourse/CourseID=4022#this                        Economics - Microeconomics
http://demo.openlinksw.com/School/DeptCourse/CourseID=4041#this                        Economics - Macroeconomics
http://demo.openlinksw.com/School/DeptCourse/CourseID=4061#this                        Economics - Quantitative
10 Rows. -- 2 msec.
SQL>

Complete Script for Creation of Linked Data Views

The Conductor’s Linked Data View wizard also generates a set of URL rewrite rules for generating dereferenceable Linked Data View URIs for the generated triples, enabling them to be viewed with a Browser or other user agent using Virtuoso’s built-in /describe or /about proxy services.

The following SQL script shows how the custom R2RML mappings created in this post can be used to repeatably generate the dereferenceable Linked Data Views for the two sample tables.

Note: This script was generated for execution against our live demo instance at demo.openlinksw.com , which listens at the default HTTP port, 80. To execute this script against another Virtuoso instance, simply replace all occurrences of http://demo.openlinksw.com/ in the script with the http://hostname:portnumber/ of your Virtuoso instance.

-- Clean up existing Quad Maps & Graphs
SPARQL DROP QUAD MAP <urn:school:tables:r2rml:mappings>;
SPARQL CLEAR GRAPH <urn:school:tables:r2rml:mappings>;
SPARQL CLEAR GRAPH <http://demo.openlinksw.com/schema/School/>;
-- Grant SPARQL select privileges on Tables
GRANT SELECT ON "School"."DBA"."Course" TO SPARQL_SELECT;
GRANT SELECT ON "School"."DBA"."Department" TO SPARQL_SELECT;
-- Load RDF Schema Ontology
ttlp (
'
@prefix     owl: <http://www.w3.org/2002/07/owl#> .
@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    aowl: <http://bblfish.net/work/atom-owl/2006-06-06/> .
@prefix virtrdf: <http://www.openlinksw.com/schemas/virtrdf#> .
@prefix  School: <http://demo.openlinksw.com/schemas/School/> .
@prefix    foaf: <http://xmlns.com/foaf/0.1/> . 
@prefix     org: <http://www.w3.org/ns/org#> .
School:  a  owl:Ontology .
# School.DBA.Course
School:Course  a  rdfs:Class .
School:Course  rdfs:isDefinedBy  School: .
School:Course  rdfs:label  "School.DBA.Course" .
School:Course  rdfs:subClassOf  foaf:Document .
School:courseid  a owl:DatatypeProperty .
School:courseid  rdfs:range  xsd:int .
School:courseid  rdfs:domain  School:Course .
School:courseid  rdfs:isDefinedBy  School: .
School:courseid  rdfs:label  "CourseID" .
School:credits  a  owl:DatatypeProperty .
School:credits  rdfs:range  xsd:int .
School:credits  rdfs:domain  School:Course .
School:credits  rdfs:isDefinedBy  School: .
School:credits  rdfs:label  "Credits" .
School:departmentid  a  owl:DatatypeProperty .
School:departmentid  rdfs:range  xsd:int .
School:departmentid  rdfs:domain  School:Course .
School:departmentid  rdfs:isDefinedBy  School: .
School:departmentid  rdfs:label  "DepartmentID" .
School:has_department  a  owl:ObjectProperty .
School:has_department  rdfs:domain  School:Course .
School:has_department  rdfs:range  School:Department .
School:has_department  rdfs:label  "Relation to School.DBA.Department" .
School:has_department  rdfs:isDefinedBy  School: .
# School.DBA.Department
School:Department  a  rdfs:Class .
School:Department  rdfs:isDefinedBy  School: .
School:Department  rdfs:label  "School.DBA.Department"  .
School:Department  rdfs:subClassOf  foaf:Organisation .
School:Department  rdfs:subClassOf  org:Organisation .
School:departmentid  a  owl:DatatypeProperty .
School:departmentid  rdfs:range  xsd:int .
School:departmentid  rdfs:domain  School:Department .
School:departmentid  rdfs:isDefinedBy  School: .
School:departmentid  rdfs:label  "DepartmentID" .
School:name  rdfs:subPropertyOf  virtrdf:label . 
School:name  rdfs:range  xsd:string .
School:name  rdfs:domain  School:Department .
School:name  rdfs:isDefinedBy  School: .
School:name  rdfs:label  "Name" .
School:startdate  a  owl:DatatypeProperty .
School:startdate  rdfs:range  xsd:dateTime .
School:startdate  rdfs:domain  School:Department .
School:startdate  rdfs:isDefinedBy  School: .
School:startdate  rdfs:label  "StartDate" .
School:administrator  a  owl:DatatypeProperty .
School:administrator  rdfs:range  xsd:int .
School:administrator  rdfs:domain  School:Department .
School:administrator  rdfs:isDefinedBy  School: .
School:administrator  rdfs:label  "Administrator" .
School:department_of  a  owl:ObjectProperty .
School:department_of  rdfs:domain  School:Department .
School:department_of  rdfs:range  School:Course .
School:department_of  rdfs:label  "Relation to School.DBA.Course" .
School:department_of  rdfs:isDefinedBy  School: .
# School.DBA.DeptTitle
School:DeptCourse  a  rdfs:Class .
School:DeptCourse  rdfs:isDefinedBy  School: .
School:DeptCourse  rdfs:label  "School.DBA.DeptCourse" .
School:DeptCourse  rdfs:subClassOf  foaf:Agent .
School:courseid  a  owl:DatatypeProperty .
School:courseid  rdfs:range  xsd:int .
School:courseid  rdfs:domain  School:DeptTitle .
School:courseid  rdfs:isDefinedBy  School: .
School:courseid  rdfs:label  "CourseID" .
School:name  rdfs:subPropertyOf  virtrdf:label . 
School:name  rdfs:range  xsd:string .
School:name  rdfs:domain  School:Department .
School:name  rdfs:isDefinedBy  School: .
School:name  rdfs:label  "Name" .
School:title  rdfs:subPropertyOf  virtrdf:label . 
School:title  rdfs:range  xsd:string .
School:title  rdfs:domain  School:Course .
School:title  rdfs:isDefinedBy  School: .
School:title  rdfs:label  "Title" .
School:depttitle  rdfs:subPropertyOf  virtrdf:label . 
School:depttitle  rdfs:range  xsd:string .
School:depttitle  rdfs:domain  School:DeptTile .
School:depttitle  rdfs:isDefinedBy  School: .
School:depttitle  rdfs:label  "DeptTitle" .
', '', 'http://demo.openlinksw.com/schema/School/', 0);
-- Load R2RML script
SPARQL
prefix          rr: <http://www.w3.org/ns/r2rml#> 
prefix      School: <http://demo.openlinksw.com/schemas/School/> 
prefix school-stat: <http://demo.openlinksw.com/School/stat#> 
prefix         rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#> 
prefix        void: <http://rdfs.org/ns/void#> 
prefix       scovo: <http://purl.org/NET/scovo#> 
prefix        aowl: <http://bblfish.net/work/atom-owl/2006-06-06/> 
prefix        foaf: <http://xmlns.com/foaf/0.1/> 
prefix         org: <http://www.w3.org/ns/org#> 
INSERT { GRAPH <urn:school:tables:r2rml:mappings> 
{ <a>  a           foaf:Document ;
       foaf:topic  <#TriplesMapCourse> ,  
                   <#TriplesMapDepartment> , 
                   <#TriplesMapDeptCourse> .
  <#TriplesMapCourse> 
    foaf:name  "Course Table Mappings" ;
    a          rr:TriplesMap ; 
    rr:logicalTable
      [ rr:tableSchema  "School" ; 
        rr:tableOwner   "DBA" ; 
        rr:tableName    "Course" 
      ] ; 
    rr:subjectMap
      [ rr:termType  rr:IRI ; 
        rr:template  "http://demo.openlinksw.com/School/course/CourseID/{CourseID}#this" ; 
        rr:class  School:Course ; 
        rr:graph  <http://demo.openlinksw.com/School#> 
      ] ;
    rr:predicateObjectMap
      [ rr:predicateMap
          [ rr:constant  School:courseid ] ; 
        rr:objectMap
          [ rr:column    "CourseID" ] ; 
      ] ;
    rr:predicateObjectMap
      [ rr:predicateMap
          [ rr:constant  School:title ] ; 
        rr:objectMap
          [ rr:column    "Title" ] ; 
      ] ;
    rr:predicateObjectMap
      [ rr:predicateMap
          [ rr:constant  School:credits ] ; 
        rr:objectMap
          [ rr:column    "Credits" ] ; 
      ] ;
    rr:predicateObjectMap
      [ rr:predicateMap
          [ rr:constant  School:course_has_department ] ; 
        rr:objectMap
         [ rr:termType  rr:IRI ; 
           rr:template "http://demo.openlinksw.com/School/department/DepartmentID/{DepartmentID}#this" 
         ] ; 
      ] .
  <#TriplesMapDepartment>
    foaf:name  "Department Table Mappings" ;
    a          rr:TriplesMap; 
    rr:logicalTable
      [ rr:tableSchema  "School" ; 
        rr:tableOwner   "DBA" ; 
        rr:tableName    "Department" 
      ] ; 
    rr:subjectMap
      [ rr:termType  rr:IRI ; 
        rr:template "http://demo.openlinksw.com/School/department/DepartmentID/{DepartmentID}#this" ; 
        rr:class  School:Department , 
                  foaf:Organization , 
                  org:OrganizationalUnit ; 
        rr:graph  <http://demo.openlinksw.com/School#> 
      ] ;
    rr:predicateObjectMap
      [ rr:predicateMap
          [ rr:constant  School:departmentid ] ; 
        rr:objectMap
          [ rr:column    "DepartmentID" ] ; 
      ] ;
    rr:predicateObjectMap
      [ rr:predicateMap
          [ rr:constant  foaf:name ] ; 
        rr:objectMap
          [ rr:column    "Name" ] ; 
      ] ;
    rr:predicateObjectMap
      [ rr:predicateMap
          [ rr:constant  School:budget ] ; 
        rr:objectMap
          [ rr:column    "Budget" ] ; 
      ] ;
    rr:predicateObjectMap
     [ rr:predicateMap
         [ rr:constant  School:startdate ] ; 
       rr:objectMap
         [ rr:column    "StartDate" ] ; 
     ] ;
   rr:predicateObjectMap
     [ rr:predicateMap
         [ rr:constant  School:administrator ] ; 
       rr:objectMap
         [ rr:column "Administrator" ] ; 
     ] ;
   rr:predicateObjectMap
     [ rr:predicateMap
         [ rr:constant  School:department_of_course ] ; 
       rr:objectMap
         [ rr:parentTriplesMap  <#TriplesMapCourse> ;  
           rr:joinCondition
             [ rr:child  "DepartmentID" ; 
               rr:parent "DepartmentID"
             ] ; 
         ] ; 
     ] .
<#TriplesMapDeptCourse> 
    foaf:name  "Department Table Mappings" ;
    a          rr:TriplesMap ; 
    rr:logicalTable
      [ rr:sqlQuery  """SELECT "CourseID", "d"."Name", "c"."Title", CONCAT("d"."Name", ' - ', "c"."Title") AS "DeptTitle" FROM "School"."DBA"."Course" "c", "School"."DBA"."Department" "d" WHERE "c"."DepartmentID" = "d"."DepartmentID" ORDER BY "CourseID" ;""" ] ; 
    rr:subjectMap
      [ rr:termType  rr:IRI ; 
        rr:template  "http://demo.openlinksw.com/School/DeptCourse/CourseID={CourseID}#this" ; 
        rr:class  School:DeptCourse ; 
        rr:graph  <http://demo.openlinksw.com/School#> 
      ] ;
    rr:predicateObjectMap
      [ rr:predicateMap
          [ rr:constant  School:courseid ] ; 
        rr:objectMap
          [ rr:column    "CourseID" ] ; 
      ] ;
    rr:predicateObjectMap
      [ rr:predicateMap
          [ rr:constant  School:name ] ; 
        rr:objectMap
          [ rr:column    "Name" ] ; 
      ] ;
    rr:predicateObjectMap
      [ rr:predicateMap
          [ rr:constant  School:title ] ; 
        rr:objectMap
          [ rr:column "Title" ] ; 
      ] ;
    rr:predicateObjectMap
      [ rr:predicateMap
          [ rr:constant  School:depttitle ] ; 
        rr:objectMap
          [ rr:column "DeptTitle" ] ; 
      ] .
  }
};
-- Generate & Execute Virtual RDF Views 
-- from R2RML mappings
EXEC ('SPARQL ' || DB.DBA.R2RML_MAKE_QM_FROM_G ('urn:school:tables:r2rml:mappings'));
-- Test RDF generated Virtual RDF Views
-- Test 1
SPARQL
SELECT SAMPLE(?s) AS ?sample 
         COUNT(1) AS ?count 
                     ?o
FROM <http://demo.openlinksw.com/School#> 
WHERE { ?s  a  ?o }
GROUP BY ?o
ORDER BY DESC(?count)
LIMIT 50 ;
-- Test 2 (workaround)
SPARQL
SELECT                      ?o 
       IRI(max(str(?s))) AS ?sample 
                COUNT(1) AS ?count
FROM <http://demo.openlinksw.com/School#> 
WHERE { ?s  a  ?o }
GROUP BY ?o
ORDER BY DESC(?count)
LIMIT 50 ;
-- Create Virtual directories and rewrite rules 
-- for Link Data Views
-- Virtual directories for instance data
DB.DBA.URLREWRITE_CREATE_REGEX_RULE
(
  'school_rule2',
  1,
  '(/[^#]*)',
  vector('path'),
  1,
  '/sparql?query=DESCRIBE+%%3Chttp%%3A//^{URIQADefaultHost}^%U%%23this%%3E+FROM+%%3Chttp%%3A//^{URIQADefaultHost}^/School%%23%%3E&format=%U',
  vector('path', '*accept*'),
  null,
  '(text/rdf.n3)|(application/rdf.xml)|(text/n3)|(application/json)',
  2,
  null
);
DB.DBA.URLREWRITE_CREATE_REGEX_RULE (
  'school_rule4',
  1,
  '/School/stat([^#]*)',
  vector('path'),
  1,
  '/sparql?query=DESCRIBE+%%3Chttp%%3A//^{URIQADefaultHost}^/School/stat%%23%%3E+%%3Fo+FROM+%%3Chttp%%3A//^{URIQADefaultHost}^/School%%23%%3E+WHERE+{+%%3Chttp%%3A//^{URIQADefaultHost}^/School/stat%%23%%3E+%%3Fp+%%3Fo+}&format=%U',
  vector('*accept*'),
  null,
  '(text/rdf.n3)|(application/rdf.xml)|(text/n3)|(application/json)',
  2,
  null
);
DB.DBA.URLREWRITE_CREATE_REGEX_RULE 
(
  'school_rule6',
  1,
  '/School/objects/([^#]*)',
  vector('path'),
  1,
  '/sparql?query=DESCRIBE+%%3Chttp%%3A//^{URIQADefaultHost}^/School/objects/%U%%3E+FROM+%%3Chttp%%3A//^{URIQADefaultHost}^/School%%23%%3E&format=%U',
  vector('path', '*accept*'),
  null,
  '(text/rdf.n3)|(application/rdf.xml)|(text/n3)|(application/json)',
  2,
  null
);
DB.DBA.URLREWRITE_CREATE_REGEX_RULE 
(
  'school_rule1',
  1,
  '([^#]*)',
  vector('path'),
  1,
  '/describe/?url=http%%3A//^{URIQADefaultHost}^%U%%23this&graph=http%%3A//^{URIQADefaultHost}^/School%%23&distinct=0',
  vector('path'),
  null,
  null,
  2,
  303
);
DB.DBA.URLREWRITE_CREATE_REGEX_RULE 
(
  'school_rule7',
  1,
  '/School/stat([^#]*)',
  vector('path'),
  1,
  '/describe/?url=http%%3A//^{URIQADefaultHost}^/School/stat%%23&graph=http%%3A//^{URIQADefaultHost}^/School%%23',
  vector('path'),
  null,
  null,
  2,
  303
);
DB.DBA.URLREWRITE_CREATE_REGEX_RULE 
(
  'school_rule5',
  1,
  '/School/objects/(.*)',
  vector('path'),
  1,
  '/services/rdf/object.binary?path=%%2FSchool%%2Fobjects%%2F%U&accept=%U',
  vector('path', '*accept*'),
  null,
  null,
  2,
  null
);
DB.DBA.URLREWRITE_CREATE_RULELIST 
( 
  'school_rule_list1', 
  1,
  vector ( 'school_rule1', 
           'school_rule7', 
           'school_rule5',
           'school_rule2', 
           'school_rule4', 
           'school_rule6'
         )
);
DB.DBA.VHOST_REMOVE 
( lpath=>'/School' );
DB.DBA.VHOST_DEFINE 
( lpath=>'/School', 
  ppath=>'/', 
  vsp_user=>'dba', 
  is_dav=>0,
  is_brws=>0, 
  opts=>vector ( 'url_rewrite', 
                 'school_rule_list1'
               )
);
-- Virtual directories for ontology
DB.DBA.URLREWRITE_CREATE_REGEX_RULE 
(
  'school_owl_rule2',
  1,
  '(/[^#]*)',
  vector('path'),
  1,
  '/sparql?query=DESCRIBE+%%3Chttp%%3A//^{URIQADefaultHost}^%U%%3E+FROM+%%3Chttp%%3A//^{URIQADefaultHost}^/schemas/School%%23%%3E&format=%U',
  vector('path', '*accept*'),
  null,
  '(text/rdf.n3)|(application/rdf.xml)|(text/n3)|(application/json)',
  2,
  null
);
DB.DBA.URLREWRITE_CREATE_REGEX_RULE 
(
  'school_owl_rule1',
  1,
  '([^#]*)',
  vector('path'),
  1,
  '/describe/?url=http://^{URIQADefaultHost}^%U',
  vector('path'),
  null,
  null,
  2,
  303
);
DB.DBA.URLREWRITE_CREATE_RULELIST 
( 
  'school_owl_rule_list1', 
  1,
  vector ( 'school_owl_rule1', 
           'school_owl_rule2'
         )
);
DB.DBA.VHOST_REMOVE (lpath=>'/schemas/School');
DB.DBA.VHOST_DEFINE 
( lpath=>'/schemas/School', 
  ppath=>'/', 
  vsp_user=>'dba', 
  is_dav=>0,
  is_brws=>0, 
  opts=>vector ( 'url_rewrite',
                 'school_owl_rule_list1'
               )
);

Sample Linked Data View Pages

The screenshots below show some of the HTML pages generated from these Linked Data Views. Note: The caption of each screenshot is a live hyperlink that identifies a sample entity generated from these mappings.

http://demo.openlinksw.com/School/department/DepartmentID/1#this

http://demo.openlinksw.com/School/DeptCourse/CourseID=4061#this

Related

3

Hi hwilliams,
your post is really interesting!!!

I tried to reproduce the example case, and it works for the Departments, but I get this error when I go to consult any element of the Course class for example: http://localhost:8890/School/DeptCourse/CourseID=4061#this

This is the error:

Error 22003

SR030: Too few (only 1) arguments for __box_flags_tweak.

Can you understand what this is due to, and how to fix it?

4

Please try upgrading to this latest Faceted Browser VAD to see if it resolves the problem …

1 Like
5

Solved!
I’m a beginner in this filed and I think that your work really matters to the whole community.
Thank you very much!

6

Hello,
after customizing the R2RML I generated the virtual graph using the command:
EXEC ('SPARQL ' || DB.DBA.R2RML_MAKE_QM_FROM_G ('urn:geca:tables:r2rml:mappings'));
like the tutorial.

I later tried to delete some triples via iSQL,
and the result was fine:
delete 1366 (or less) and insert 1366 (or less) triples -- done
but they keep appearing when querying the virtual graph.

Why I can’t eliminate triples on a virtual graph?

7

For Virtual Graphs, you also have to drop the Virtuoso quad map storage used for host the virtual graph definitions, with the command SPARQL DROP QUAD MAP <urn:geca:tables:r2rml:mappings>; in addition to deleting the SPARQL graph. As is done in the Customizing R2RML Scripts in Virtuoso sample scripts …

8

Hello, hwilliams. As to Prerequisites of this post, the link for downloading “schools_db_dav.vad” is invalid. I can not download fct_dav.vad or cartridges_dav.vad either according to the tips. Could you please kindly update and give us the packages? Thank you.

9

The Virtuoso 7 & 8 schools_db_dav.vad links in the post work for me, so in what way are they seen as invalid for you ?

Note this post was written specifically for the Virtuoso commercial edition, although should work with open source also with the following provisos:

The open source Facet Browser and Cartridges VAD packages are not the same as those fo commercial, thus I have hyperlinked those here.

10

Hello, I just clicked on it: no sooner had another window popped up than that window shut automatically and instantly.
Thank you for sharing those two packages for open source.
Could you please give me a quote for payable consultancy via junjun.cao@mcgill.ca?

11

Hello, you might have given a wrong link for the open source Catridges.
I clicked on that and it downloaded the “fct_dav.vad” instead. I think the right one might be called “cartridges_dav.vad”.
I got one “cartridges_dav.vad” from Free Evaluation License Generator. But I am not sure whether it is for commercial or open source.
Thanks.

12

The open source cartridges VAD is called rdf_mappers_dav.vad that being the original name before the commercial and open source VADs diverged when the commercial VADs name was changed.

13

Hi, there is a little confusion to me:
I get that copying the content R2RML Graph to a local text editor is for customization purposes. However, I am having trouble understanding how to reflect these changes to the actual databases in Virtuoso.
I tried writing the changed R2RML Graph content back to the same place, applying the changes, and executing it,

image
image1920Ă—1200 176 KB

but nothing was reflected in the Linked Data Views after I tried the SPARQL select statement.
Are there any possible incorrect or missing actions?

Thank you,
Joe Z

14

Have you worked through the complete custom R2RML script in this post ? As it documents that the R2RML generated by the Conductor needs to be copied externally and customised as required, but cannot then be reapplied to the Conductor R2RML section. Rather a manual script generated that it run from command line to generate the R2RML mappings in Virtuoso, as shown in the Complete Script for Creation of Linked Data Views section of the post.
Note if you just what the R2RML mappings of the SQL relations to transformed to RDF physical or virtual graphs then this can be done with the Conductor R2RML Import UI tab as detailed in the documentation. Although this will not create dereferencible Linked Data Views, with require HTTP rewrite rules etc which this post demonstrates.

1 Like
15

Do you mean the following?
I have a revised R2RML document that I would like to apply to Virtuoso and then see the changes in that updated mapping reflected in new RDF Views?

1 Like