Customizing R2RML Scripts in Virtuoso

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 theSchool.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>

Organization Ontology Class Mappings

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 theSchool.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:query 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:query 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:query:

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 thehttp://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