R2RML SQL View Annotation

Database Connection

Can be established using a DSN or connection string, e.g.:

R2RConn('mysql:host=localhost;dbname=DB;user=USER;password=PASS');

An RDF serialization looks as follows:

MyMap    R2R:ConnectionString    "mysql:host=localhost;dbname=DB;user=USER;password=PASS" .

Namespace declaration

Enables the declaration and use of namespace prefixes.

Two distinguished namespaces exist:

• base - the base namespace, which is used as default namespace for all generated URIs
• schema - the namespace, which is used for generated schema elements (i.e. properties, classes)

R2RNS('foaf','http://xmlns.com/foaf/0.1/');
...
R2RNS('base','http://example.com/myblog/')
R2RNS('schema','http://triplify.org/vocabulary/blog/')

Table/View Annotation

Syntax:

R2RMap(VIEWNAME_OR_SQL_SELECT, PATH, CLASS, COLDEF1, COLDEF2, ... COLDEFn);

• VIEWNAME_OR_SQL_SELECT - either the name of a view or a complete SQL query
• PATH - the path fragment to be appended to the base namespace for generating instance URIs, these are generated by concatenating the base namespace, PATH and a unique key identified by a column definition named id (if omitted the table or view name will be used)
• CLASS - the RDF class instances should belong to
  • optional, since RDF does not require, that every entity belongs to a class, so we should not force people to do so. In certain cases you also might want to create the class membership dynamically from a column in the DB, e.g.: R2RMap("SELECT id, point, category AS 'rdf:type'"); In that case each instance would become member of a class obtained from the category column.
• COLDEFx - a definition of the xth column:
  • datatype: 'price^^xsd:decimal'
  • language: 'rdf:label@en'
  • object property: 'sioc:has_creator->user'
  • optional, when omitted the mapping will retrieve property names directly from the column names.

All parameters besides VIEWNAME_OR_SQL_SELECT are optional.

Example:

Let's assume the following table definition:

CREATE TABLE `person` (
  `id` INT UNSIGNED NOT NULL ,
  `name` VARCHAR( 255 ) NOT NULL ,
  `profile` TEXT NOT NULL ,
  `project` INT UNSIGNED NOT NULL ,
  UNIQUE (`id`)
)

The following view annotation would map this table to instances of the class foaf:Person with properties foaf:name and foaf:hasProject.

R2RMap('mydb.person',       // the view name (can be an SQL query alternatively)
 'person',                  // the linked data path
 'foaf:Person',             // the class
 'id',                      // 1st col - primary key for generating URIs
 'foaf:name',               // 2nd col - map to 'foaf:name' dtype prop
 'profile@en',              // 3rd col - map to 'profile' with lang 'en'
 'foaf:hasProject->project' // 4th col - map to object property ref proj
);

An RDF serialization would look as follows:

MyBridge1  R2R:belongsTo  MyMap .
MyBridge1  R2R:table      "mydb.person" .
MyBridge1  R2R:path       "person" .
MyBridge1  R2R:type       foaf:Person .
MyBridge1  R2R:col1       id .
MyBridge1  R2R:col2       foaf:name .
MyBridge1  R2R:col3       "profile@en" .
MyBridge1  R2R:col4       "foaf:hasProject->project" .

User defined output processing functions

can be either implemented in SQL and called in the view definition:

R2RMap('SELECT id, name AS "foaf:name", MYSHA1SUM(CONCAT("mailto:",email)) AS "foaf:mbox_sha1sum" FROM mydb.person', 'person', 'foaf:Person');

or implemented in a programming language supported by the mapping processor and registered as follows:

R2RMap('mydb.person', 'person', 'foaf:Person', 'id', 'foaf:name', R2RUDF('mysha1sum','foaf:mbox_sha1sum'));

Static Metadata

Static metadata (such as licensing information can be attached to all RDF entities or instances of a certain class:

R2RMeta('foaf:Person', N3_DESC);

Attribute-Value Tables

In some cases relational database schema already contain attribute value tables.

Instead of employing the column name (or column definition) for generating property URIs, the special column name (or column definition) 'r2r:unc' instructs to use the value obtained from the next column for generating property URIs:

R2RMap('mydb.attributes', 'person', 'foaf:Person', 'id', 'r2r:unc', 'value'));

Named Graphs

R2RMaps can be grouped into different named graphs:

R2RGraph(GRAPH_URI,
 R2RMap(...),
 R2RMap(...),
)

Update Logs

Can be generated using:

R2RUpdateLog("SELECT p.changed AS id,p.id AS 'update:updatedResource->project' FROM project p");

Provenance

It is easy to generate additional RDF triples according to a provenance vocabulary, e.g. http://sourceforge.net/apps/mediawiki/trdf/index.php?title=Guide_to_the_Provenance_Vocabulary

TODO

• define precise literal production rules
• precise definition of the semantic
• implement SQL2SPARQL rewriting