View source for Lab: Ontop

==Topics==
* Creating SQLite3 databases
* Creating OBDA or R2RML mappings
* Setting up Ontop in GraphDB
* Querying SQLite3 databases through a virtual Ontop KG

==Useful materials==
* The [https://sqlite.org/index.html SQLite] project page.
* [https://graphdb.ontotext.com/documentation/10.8/virtualization.html Accessing relational databases with data virtualization] (GraphDB's Ontop documentation)
* The [https://github.com/ontop/ontop-examples/tree/master ontop-examples] repository on GitHub contains many OBDA mapping examples
* Section 2 in [https://www.w3.org/TR/sparql11-federated-query/ SPARQL 1.1 Federated Query]

==Tasks==
We recommend you run this exercise through the Ontop plugin to the free desktop version of OntoText's GraphDB tool. You can also use one of the [https://ontop-vkg.org/ open-source versions of Ontop].

===Installing and running SQLite3===
'''Task:''' Download and install a relational database system on your computer. We recommend the very light-weight and easy-to-install [https://sqlite.org/download.html SQLite3].

'''Task:''' Create a folder on your local computer for this exercise. Go to the new folder and open a Console or Terminal window. Start SQLite3 with the name of a new database, for example 'MyTestDB.sqlite'.

'''Task:''' Based on the example files ''Fig6.3-ReportClaimantTable-original.sql'' in [https://mitt.uib.no/courses/51914/files/folder/Examples the Examples folder in mitt.uib.no], create the following two tables and fill them with test data:

Table '''InvestigatedPeople:'''
{| class="wikitable"
|'''InvestigationID'''
|'''American'''
|'''CPDate'''
|'''CPDays'''
|'''IndictmentDays'''
|'''Investigation'''
|'''Name'''
|'''Outcome'''
|'''Overturned'''
|'''Pardoned'''
|-
|investigation_114
|true
|None
|None
|None
|bush-clinton-passport
|None
|None
|false
|false
|-
|investigation_100
|true
|1993-10-26
|1335
|789
|pierce
|Deborah Gore Dean
|conviction
|false
|false
|-
|investigation_113
|true
|None
|None
|None
|sealed-investigation-hwbush-2
|None
|None
|false
|false
|-
|investigation_10
|true
|1975-01-01
|592
|286
|watergate
|Robert C. Mardian
|conviction
|true
|false
|-
|investigation_11
|true
|None
|None
|<nowiki>-9</nowiki>
|watergate
|Maurice H. Stans
|indictment
|false
|false
|-
|investigation_115
|true
|1994-03-22
|<nowiki>-136</nowiki>
|<nowiki>-316</nowiki>
|whitewater
|David L. Hale
|guilty-plea
|false
|false
|}
Table '''Investigations:'''
{| class="wikitable"
|'''Investigation'''
|'''InvestigationDays'''
|'''InvestigationStart'''
|'''InvestigationEnd'''
|'''President'''
|-
|bush-clinton-passport
|1081
|1992-12-14
|1995-11-30
|Bill Clinton
|-
|pierce
|3162
|1990-03-01
|1998-10-27
|George H.W. Bush
|-
|sealed-investigation-hwbush-2
|453
|1991-04-19
|1992-07-15
|George H.W. Bush
|-
|watergate
|1492
|1973-05-19
|1977-06-19
|Richard Nixon
|-
|whitewater
|2770
|1994-08-05
|2002-03-06
|Bill Clinton
|}

''Tip:'' You can either enter SQL statements interactively one by one, or you can save them to a file and use SQLite3's ''.read'' command. Use the ''.exit'' command when you are done.

===OBDA and R2RML mappings===
'''Task:''' Draw an example graph on paper that represents the ''investigation_100'' and ''pierce'' rows of the two tables.

'''Task:''' Based on the example files ''Fig6.4-ReportClaimant-mapping.obda'' and/or ''Fig6.4-ReportClaimant-mapping.r2rml'' in [https://mitt.uib.no/courses/51914/files/folder/Examples the Examples folder], create a mapping from the two tables into an RDF graph.

===GraphDB Ontop configuration===
'''Task:''' You already have Ontop as part of your GraphDB installation, but you need an additional ''.jar''-file - a JDBC driver - for connecting to your database of choice. For SQLite3, you can download the driver from [https://github.com/xerial/sqlite-jdbc Xenial on GitHub]. Place the ''.jar''-file in the ''lib/''-subfolder of your GraphDB installation folder. Restart Ontop.

'''Task:''' 
* Create a new Ontop repository. 
* The ''Driver class'' should be <code>org.sqlite.JDBC</code>. The ''JDBC URL'' should be <code>jdbc:sqlite:PATH</code>, where PATH is the location of your SQLite3 database, for example <code>jdbc:sqlite:C:/Users/MyUser/OntopLab/MyTestDB.sqlite</code>. 
* Load the OBDA or R2RML mapping file. 
* Click ''Create'' and activate the new repository.

===Running SPARQL queries (and updates?)===
'''Task:''' Write SPARQL queries that answer the following questions:
* XXX

'''Task:''' Write the following SPARQL updates:
* XXX

==If you have more time==
===Running federated SPARQL queries===
'''Task:''' Create ''two separate'' SQLite3 databases, one for each of the two tables '''InvestigatedPeople''' and '''Investigations'''.

'''Task:''' Create ''two separate'' OBDA and/or R2RML mappings for the new databases.

'''Task:''' Create ''two separate'' GraphDB Ontop repositories '''InvestigatedPeople''' and '''Investigations''' using the new mappings.

'''Task:''' Write ''federated'' SPARQL queries that combine data from the two new repositories to answer the same questions as before.

''Tip:'' The URI/IRI for a GraphDB repository is available from its listing under ''Settings -> Repository .''