D2R/Update Server tutorial

Author: Vadim Eisenberg

The following tutorial describes how to use the example database and mapping that appear in the D2RQ Mapping Language , for executing SPARQL/Update statements using D2R/Update Server. See also D2R Server tutorial

Download and uncompress D2R/Update Server and required jars.
1. Download http://vadimeisenberg.github.io/d2rqUpdate/downloads/d2r-update-server.zip or http://vadimeisenberg.github.io/d2rqUpdate/downloads/d2r-update-server.tgz
2. Extract the d2r-update-server directory
3. Download JSqlParser from http://sourceforge.net/projects/jsqlparser/
4. Extract the jsqlparser directory (no matter to which location).
5. Copy lib/jsqlparser.jar from the directory of jsqlparser to the lib directory inside the root directory of D2R/Update Server (the extracted directory d2r-update-server)
6. Download JGraphT from http://www.jgrapht.org/
  - For Windows:
    1. http://sourceforge.net/projects/jgrapht/files/JGraphT/Version%200.8.1/jgrapht-0.8.1.zip/download
    2. Extract the jgrapht directory (no matter to which location).
  - For other OS:
    1. http://sourceforge.net/projects/jgrapht/files/JGraphT/Version%200.8.1/jgrapht-0.8.1.tar.gz/download
    2. ```
    $ tar xfz jgrapht-0.8.1.tar.gz
```
7. Copy the jgrapht-jdk1.6.jar from the directory of the jgrapht to the lib directory inside the root directory of D2R/Update Server.
The steps 2-9 are identical to the steps in the D2R Server tutorial . You can use the same database and the same mapping that you used in D2R Server tutorial , D2R/Update Server runs exactly in the same manner with exactly the same input.
Download a dump of an example database (with some entries from the ISWC 2002 conference): http://www.d2rq.org/example/iswc-mysql.sql
Create a database "iswc" in MySQL
Feed the downloaded iswc-mysql.sql into mysql by running (you should specify the full path of the downladed iswc-mysql.sql if you run mysql in another directory):
```
$ mysql -u [username] -p[password] < iswc-mysql.sql
```
Check that the database was created OK. In MySql run, for example:
```
show tables;
```
and
```
select FirstName, LastName, email from persons;
```
Download the example mapping file mapping-iswc.ttl
Change the username in the downloaded mapping file to be your mysql username and add a password of your mysql (if the password is required for your mysql)
Run d2r-server in the directory you uncompressed it in the step 1 (you should specify the full path of the mapping-iswc.ttl if you run mysql in another directory):
```
d2r-server -p 8080 mapping-iswc.ttl
```
Open the following URL with your web browser: http://localhost:8080/snorql/ . You will see the same snorql webpage, except for the new section for input of SPARQL/Update statements.
Run an example SPARQL query. Write the query into the text of the SPARQL section and push the "Go!" button. You will see the results of the query in the SPARQL results section. The following query returns the phone number of Yolanda Gil:
```
SELECT ?phone WHERE { 
   ?person foaf:name 'Yolanda Gil'; 
           iswc:phone ?phone 
}
```
Verify that the phone number returned by the query is indeed the phone number in the database. Run the following SQL query:
```
SELECT Phone FROM persons WHERE FirstName = 'Yolanda' AND LastName = 'Gil';
```
Finally, the real action begins ! Let's change the number of Yolanda Gil to some other number, for example 222-222-2222, by running the following SPARQL/Update statement:
```
MODIFY DELETE { 
       ?person iswc:phone '310-448-8794'
}
INSERT { 
       ?person iswc:phone '222-222-2222'
}
WHERE {
       ?person foaf:name 'Yolanda Gil'   
}
```
Write this statement into the input area for SPARQL/Update:

Push the Go! button under the SPARQL/Update input area. You will see the SPARQL/Update statement disappears and the SPARQL result is updated to the new value.
Let's verify that the phone number has indeed been changed in the database. Run the previous SQL query:

Bingo ! The phone number is 222-222-2222, as was intended.
Let's try to delete the phone number of Yolanda Gil. Run the following SPARQL/Update statement:
```
DELETE { 
       ?person iswc:phone '222-222-2222'
}

WHERE {
       ?person foaf:name 'Yolanda Gil'   
}
```
You will see that SPARQL Result has changed to "no results":

Verify that the phone number has been deleted (set to NULL) - run the previous SQL query:

Now we can insert a phone number for Yolanda Gil (let's return the previous number back) by running the following SPARQL/Update statement:

INSERT { 
       ?person iswc:phone '310-448-8794'
}
WHERE {
       ?person foaf:name 'Yolanda Gil'   
}

Please note that we could have combined the previous two steps into one MODIFY-DELETE-INSERT statement:

MODIFY DELETE { 
       ?person iswc:phone '222-222-2222'
}
INSERT { 
       ?person iswc:phone '310-448-8794'
}
WHERE {
       ?person foaf:name 'Yolanda Gil'   
}

Let's see what happens if we try to insert an additional phone number to Yolanda Gil. SPARQL/Update considers the properties of a table that has primary keys, as functional, that means that such property can have only one value. So if we try to INSERT a new value without deleting the previous one by DELETE statement, SPARQL/Update will return an error:

That means that in order to insert a new value (for a functional property) we have to delete the previous value, in two separate statements - DELETE and INSERT, or in one combined statement MODIFY-DELETE-INSERT.

We can insert a whole new row, or several rows into several tables, by one insert statement. Let's insert some information about Richard Cyganiak by the following SPARQL/Update statement:

INSERT {
<http://localhost:8080/resource/persons/14> rdf:type iswc:Researcher;
                                            foaf:name 'Richard Cyganiak';
		                            iswc:address 'Office 102, Deri Galway';
				            foaf:mbox <mailto:Richard.Cyganiak@fakemail.com>;
				            foaf:homepage <http://richard.cyganiak.de>;
			                    iswc:phone '777-77-7777';
			                    iswc:research_interests <http://localhost:8080/resource/topics/5>;
			                    iswc:research_interests <http://localhost:8080/resource/topics/10> 
}

We have assigned Richard the URI <http://localhost:8080/resource/persons/14> that corresponds to PerID 14. Let's see that the information about Richard has been added to the persons and to the rel_person_topic tables:

We can delete several rows from several tables in one DELETE statement. Let's delete the previously inserted information by the corresponding DELETE statement:
```
DELETE {
<http://localhost:8080/resource/persons/14> rdf:type iswc:Researcher;
                                            foaf:name 'Richard Cyganiak';
		                            iswc:address 'Office 102, Deri Galway';
				            foaf:mbox <mailto:Richard.Cyganiak@fakemail.com>;
				            foaf:homepage <http://richard.cyganiak.de>;
			                    iswc:phone '777-77-7777';
			                    iswc:research_interests <http://localhost:8080/resource/topics/5>;
			                    iswc:research_interests <http://localhost:8080/resource/topics/10> 
}
```
Let's see that all the information about Richard has been deleted from both the persons and the rel_person_topic tables:

Please note that the order of the triples in the ISNERT or DELETE statements is unimportant - the D2RQ/Update groups the triples by subject and table in order to perform as little SQL INSERT/UPDATE/DELETE operations as possible, one operation per one row in a table. So, for example, the last SPARQL/Update DELETE statement of eight triples is performed by only three SQL DELETE statements. You can see which SQL statements are executed by D2R/Update Sever in the DEBUG log printings of ExecutionEngine:

In addition, D2RQ/Update executes SQL INSERT/UPDATE/DELETE operations according to foreign key dependencies between tables. Please note that the last DELETE operations were executed first on the rel_person_topic table and second on persons table, disregarding the original order of the triples in the SPARQL/Update DELETE statement. Such order was chosen by D2RQ/Update since there is a foregn key dependecy from the PersonId column of rel_person_topic table on the column PerID of the persons table.
You can see more examples of SPARQL/Update statements on the example database in the functional tests of D2RQ/Update - https://github.com/VadimEisenberg/d2rqUpdateDBUnitTests/tree/master/src/il/ac/technion/cs/d2rqUpdate/DBUnit/functionalTests. You can browse the Java code of the tests and see SPARQL/Update statements in calls to the sparqlUpdate method.