Relational Database Extractor

The purpose of this extractor is to gather data from a relational database and map it into an ontology format. This is a necessary step e.g. in the Civil Engineering use case.
All sensor measurements and additional meta-information are stored in a relational Oracle database. This data was supposed to be extracted and populated into the sensors domain-specific ontology.

The extraction is done using D2RQ a DB to sparql mapper mainly developed by the FU Berling.

The extractor currently provides a config file for mapping to the SensorDSO.

Prerequisites

1. D2RQ
2. a Relational database with a jdbc driver
   
3. JavaVM

General Usage Instructions

Download newest version of D2RQ from website: http://d2rq.org/

Download the specific jdbc driver for your database client, e.g. ojdbc6.jar for Oracle 11g (http://www.oracle.com/technetwork/database/enterprise-edition/jdbc-112010-090769.html).

Unzip the package and go to the extracted folder. Move the downloaded JDBC driver into the "lib/db-drivers" directory.

Go back to the main folder. Run "generate-mapping.bat [options] jdbcURL", with the jdbcURL for the database connection, and options for the output file (-o), JDBC driver (-d), username (-u) and password (-p).
Example:

Windows:

"generate-mapping.bat -o timbus.ttl -d oracle.jdbc.driver.OracleDriver -u user -p pass jdbc:oracle:thin:@timbus.teco.edu:49153/xe "

Linux/Mac:

"sh generate-mapping -o timbus.ttl -d oracle.jdbc.driver.OracleDriver -u pass -p pass jdbc:oracle:thin:@timbus.teco.edu:49153/xe "

You may choose to include/exclude some tables. This can be specified by using:
" --skip- [schema.]table[.column]"
e.g.: "--tables EXTRESISTENCIA,EXTRESISTENCIARES" to just include these two tables.

Now, a first version of a mapping file (timbus.ttl) was created. You now open this Turtle file and adjust is to your needs (e.g. a specific ontology), by specifying the prefix and the required mappings.

An example file archive can be found in the SVN: https://timbus.teco.edu/svn/timbus/WP8/T8.3%20Interfacing%20with%20Devices%20Sensors%20as%20Services/SensorDSO%20mapping/d2rq-example.rar
You can unzip it and you will find all d2rq files and a mapping file created as explained above.
The mapping file that is currently used for a SensorDSO mapping in the context of T8.3 is appended, but can also be found in the SVN (in the same folder as the d2rq-example.rar).

Start Service

To run the service, first open your command line/terminal. Change the folder and go to the d2rq main folder. Run the d2rq-server.bat or the shell script in the following way:

Windows:

"d2r-server.bat timbus.ttl"

Linux/Mac:

"sh d2r-server timbus.ttl"

Expected Output

The output will be a SensorDSO graph that can be queried.

There are several ways to acquire the results. One way is to use the GUI. Go to the adress specified in the mapping file (e.g. "localhost:2020"). There, you can browse through the graph and also run SPARQL queries.

To run the queries, you can go to "localhost:2020/snorql" and run queries online and extract information.

Alternatively, you can use s-query by Jena Fuseki and query the SPARQL endpoint "localhost:2020/sparql" and get the results directly (console output, possibly: output into file). The command looks like this:
"s-query --service==http://localhost:2020/sparql 'query'

Where query is your SPARQL query. An example is this:

PREFIX sensor: <http://timbus.teco.edu/ontologies/DSOs/sensors.owl#>
PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#>
Construct {?d sensor:hasName "Pedrógão" . ?s sensor:isPartOfStructure ?d . ?s ?p ?o . } WHERE { ?d sensor:hasName "Pedrógão" . ?s sensor:isPartOfStructure ?d . ?s rdf:type sensor:Sensor . ?s ?p ?o . }

Be aware of the following things:
Online SPARQL query in the GUI is via SNORQL (therefore: http://localhost:2020/SNORQL) but the command line version uses the SPARQL endpoint (therefore: http://localhost:2020/SPARQL).