Database Extractor
The purpose of this extractor is to get data from a relational database and map it into an ontology format. We used D2RQ for this process and a self-written Turtle script.
Preparation
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 teco -p pervasive jdbc:oracle:thin:@teco32pc.teco.edu:49153/xe "
Linux/Mac: "sh generate-mapping -o timbus.ttl -d oracle.jdbc.driver.OracleDriver -u teco -p pervasive jdbc:oracle:thin:@teco32pc.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"
See Results & Run SPARQL Queries
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 dc: http://purl.org/dc/elements/1.1/
PREFIX foaf: http://xmlns.com/foaf/0.1/
PREFIX vcard: http://www.w3.org/2001/vcard-rdf/3.0#
PREFIX meta: http://www4.wiwiss.fu-berlin.de/bizer/d2r-server/metadata#
PREFIX dcterms: http://purl.org/dc/terms/
PREFIX iswc: http://annotation.semanticweb.org/iswc/iswc.daml#
PREFIX sensor: http://timbus.teco.edu/ontologies/DSOs/sensors.owl#
PREFIX rdfs: http://www.w3.org/2000/01/rdf-schema#
PREFIX d2r: http://sites.wiwiss.fu-berlin.de/suhl/bizer/d2r-server/config.rdf#
PREFIX map:
PREFIX xsd: http://www.w3.org/2001/XMLSchema#
PREFIX owl: http://www.w3.org/2002/07/owl#
PREFIX rdf: http://www.w3.org/1999/02/22-rdf-syntax-ns#
PREFIX vocab: http://localhost:2020/resource/vocab/
PREFIX skos: http://www.w3.org/2004/02/skos/core#
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).
Moreover, be careful to include all prefixes to the query.