Difference between revisions of "Working with the Database Backend in OWL"

From Protege Wiki
Jump to: navigation, search
(→‎Before you start)
 
(12 intermediate revisions by 3 users not shown)
Line 2: Line 2:
  
 
__TOC__
 
__TOC__
 +
  
 
== Before you start ==  
 
== Before you start ==  
Line 7: Line 8:
 
First, this example assumes that you have a working mysql database and that there is an available database (which in this case is called protege) in the mysql database.
 
First, this example assumes that you have a working mysql database and that there is an available database (which in this case is called protege) in the mysql database.
  
−
In addition, the mysql jdbc drivers are available to Protege.  To make the drivers available, download the drivers from [http://smi-protege.stanford.edu/repos/protege/protege-core/trunk/dbdrivers/mysql-connector-java-5.0.0-beta-bin.jar  here], copy the jar file into the Protege installation directory and call it driver.jar.  In theory, any jdbc database should work but we have only done testing for mysql, postgres, oracle 9g and the microsoft sql server.  The drivers for all of these options can be found [http://smi-protege.stanford.edu/repos/protege/protege-core/trunk/dbdrivers here].
+
In addition, the mysql jdbc drivers are available to Protege.  To make the drivers available, download the drivers from [http://smi-protege.stanford.edu/repos/protege/protege-core/trunk/dbdrivers/mysql-connector-java-5.0.0-beta-bin.jar  here], '''copy the jar file into the Protege installation directory and call it driver.jar'''.  In theory, any jdbc database should work but we have only done testing for mysql (Innodb only - MyIASM doesn't work), postgres, oracle 9g and the microsoft sql server.  The drivers for all of these options can be found [http://smi-protege.stanford.edu/repos/protege/protege-core/trunk/dbdrivers here].
  
 
== Opening an ontology using a web uri ==  
 
== Opening an ontology using a web uri ==  
  
 
This note will show by example how to convert an owl project and its imports to a database backend project.  We will use the [http://proton.semanticweb.org proton] ontology as an example because it has a few non-trivial imports.  The first step is to open it from the web.  We start protege and in the welcome screen we select "New Project".
 
This note will show by example how to convert an owl project and its imports to a database backend project.  We will use the [http://proton.semanticweb.org proton] ontology as an example because it has a few non-trivial imports.  The first step is to open it from the web.  We start protege and in the welcome screen we select "New Project".
 +
  
 
[[Image:OwlDatabaseBackendProtegeWelcome.png]]
 
[[Image:OwlDatabaseBackendProtegeWelcome.png]]
 +
  
 
Note that if Protege is already open and you don't see the welcome screen then you can access the screen below by clicking on the File menu and selecting "New Project".
 
Note that if Protege is already open and you don't see the welcome screen then you can access the screen below by clicking on the File menu and selecting "New Project".
Line 21: Line 24:
 
* select ''OWL/RDF Files'' and
 
* select ''OWL/RDF Files'' and
 
* click next.
 
* click next.
 +
  
 
[[Image:OwlDatabaseBackendCreateNewProject.png]]
 
[[Image:OwlDatabaseBackendCreateNewProject.png]]
 +
  
 
Finally we enter the url (http://proton.semanticweb.org/2005/04/protonu) for the upper Proton ontology and click finish.
 
Finally we enter the url (http://proton.semanticweb.org/2005/04/protonu) for the upper Proton ontology and click finish.
 +
  
 
[[Image:OwlDatabaseBackendChooseURLAndFinish.png]]
 
[[Image:OwlDatabaseBackendChooseURLAndFinish.png]]
  
 
Wait a bit and the ontology will come up.  When it does, you will see the reason that this ontology was chosen for this demonstration when you unravel the imports in the imports tab.  There are two imports (protont and protons) which occur in two different places.
 
Wait a bit and the ontology will come up.  When it does, you will see the reason that this ontology was chosen for this demonstration when you unravel the imports in the imports tab.  There are two imports (protont and protons) which occur in two different places.
 +
  
 
[[Image:OwlDatabaseBackendProtonUImports.png]]
 
[[Image:OwlDatabaseBackendProtonUImports.png]]
 +
  
 
== Converting a Project to a Database Project ==
 
== Converting a Project to a Database Project ==
 +
 +
'''Note''' ''If you prefer to make the OWL file to the database backend conversion using a script, we do provide an ant script for this purpose. Please refer to this [[ConvertToDBScript|wiki page]] for more information.''
  
 
Now we will convert the upper Proton ontology to a database project. To do this click on the Protege File menu and select "Convert Project to Format".  In this window select the "OWL / RDF Database" format and click ok.
 
Now we will convert the upper Proton ontology to a database project. To do this click on the Protege File menu and select "Convert Project to Format".  In this window select the "OWL / RDF Database" format and click ok.
 +
  
 
[[Image:OwlDatabaseBackendSelectProjectFormat.png]]
 
[[Image:OwlDatabaseBackendSelectProjectFormat.png]]
 +
  
 
Once you click ok, you will see a dialog with several database settings.  You can fill them as shown below.  If you don't want to type out the entire path of where to save the project then press the box with a superscript + sign in the upper right hand side of the dialog and Protege will let you browse to where the project file should be saved.
 
Once you click ok, you will see a dialog with several database settings.  You can fill them as shown below.  If you don't want to type out the entire path of where to save the project then press the box with a superscript + sign in the upper right hand side of the dialog and Protege will let you browse to where the project file should be saved.
 +
 +
NOTE: The driver class name will vary from the image below. For example, the name for the MySQL driver downloaded from the above link is 'org.gjt.mm.mysql.Driver' and the PostgreSQL driver name in the jar linked to above is 'org.postgresql.Driver' (omit quotes in both cases)
 +
  
 
[[Image:OwlDatabaseBackendSelectDbSettings.png]]
 
[[Image:OwlDatabaseBackendSelectDbSettings.png]]
  
−
The jdbc:mysql://localhost/protege is parsed as follows. The jdbc:mysql: prefix says that this url is a mysql jdbc url and that the database can be loaded using jdbc with the mysql drivers.  The localhost says that the database is running on the local machine and the protege says to use the mysql protege database (which I confgured using mysql tools before I started.)
 
  
−
Click finish and the database project will be created.  Use whatever tool is available and you will find that a table has been added to the mysql database.  In addition, it  is not difficult to show that this project is an example of a database project importing two owl projects that are loaded from the web.  To see this, close protege and save the project, turn off your internet connection and try to reload the database ontology.  If you can do this then you will see the following popup dialog
+
The jdbc:mysql://localhost/protege is parsed as follows. The jdbc:mysql: prefix says that this url is a mysql jdbc url and that the database can be loaded using jdbc with the mysql drivers.  The localhost says that the database is running on the local machine and the protege says to use the mysql protege database (which I confgured using mysql tools before I started.) Note that if you are using international characters, you may want to consider adding some unicode options to the url as follows:
 +
<pre>
 +
    jdbc:mysql://localhost/protege?useUnicode=true&characterEncoding=UTF-8
 +
</pre>
 +
 
 +
Click finish and the database project will be created.  Use whatever tool is available and you will find that a table has been added to the mysql database.  In addition, it  is not difficult to show that this project is an example of a database project importing two owl projects that are loaded from the web.  The easy way to do this is to observe the two log messages on the Protege console:
 +
Loading triples from: http://proton.semanticweb.org/2005/04/protons
 +
    Completed triple loading after 102 ms
 +
Importing http://proton.semanticweb.org/2005/04/protont from location: http://proton.semanticweb.org/2005/04/protont
 +
Loading triples from: http://proton.semanticweb.org/2005/04/protont
 +
    Completed triple loading after 475 ms
 +
Alternatively, close protege and save the project, turn off your internet connection and try to reload the database ontology.  If you can do this then you will see the following popup dialog
 +
 
  
 
[[Image:OwlDatabaseBackendUnresolvedImport.png]]
 
[[Image:OwlDatabaseBackendUnresolvedImport.png]]
 +
  
 
and you will see the following exception (this is just a small bit of the full exception) on the console.
 
and you will see the following exception (this is just a small bit of the full exception) on the console.
Line 72: Line 99:
  
 
projects to database projects.  For this example we assume that both projects are converted to database projects using the same protege mysql database.  However with small changes to the instructions below, one could use other databases for  the other two projects including using postgresql or oracle database.  In other words it is perfectly feasible for a mysql project to import a postgresql database project.
 
projects to database projects.  For this example we assume that both projects are converted to database projects using the same protege mysql database.  However with small changes to the instructions below, one could use other databases for  the other two projects including using postgresql or oracle database.  In other words it is perfectly feasible for a mysql project to import a postgresql database project.
 +
 +
'''Important Note!:''' It is important that different projects using the same database (e.g. protege) use different table names.  The diagrams below show the settings that I used for the two databases and it shows that I use the table ProtonTop and
 +
ProtonSystem for the table names.
 +
 +
 +
[[Image:OwlDatabaseBackendProtonTSettings.png]]
 +
 +
 +
[[Image:OwlDatabaseBackendProtonSSettings.png]]
 +
 +
 +
Now that all three ontologies are stored on the local disk in a database, we can arrange that Protege  will load all three ontologies from the database without going to the internet.  To do this go back and reload the upper Proton ontology.  Click on the OWL menu and select "Ontology repositories...".
 +
 +
 +
[[Image:OwlDatabaseBackendOWLRepositoriesMenu.png]]
 +
 +
 +
This will bring up a view of the current repositories that are being used for owl imports.  The two repository repository items already there indicate that the repository manager is finding the protons and protont ontologies off the web.  We want to change this.
 +
 +
 +
[[Image:OwlDatabaseBackendOWLRepositoryManagerUI.png]]
 +
 +
 +
To replace the repositories currently being used by Protege, first remove the existing ontologies by clicking on the minus sign beside them.  Now click on the plus sign at the top right of the repository manager and you will get a list  of repository types  that can be added. Choose "database repository" and click next.
 +
 +
 +
[[Image:OwlDatabaseBackendSetDbRepositorySettings.png]]
 +
 +
 +
Then fill in the database settings and click finish.  If everything has gone well you should see the screen below.  The three entries in the window indicate that
 +
the repository manager has found these three ontologies in the database and future imports of these ontologies will load these ontologies from database mode.  Click the "Close button". 
 +
 +
 +
[[Image:OwlDatabaseBackendDbRepositoryWorks.png]]
 +
 +
 +
At this point there will be a dialog box asking if you want to reload the ontology.  Click "Reload".  The proton ontology will then be reloaded but now all three ontologies are in database mode.  As before you can verify this by loading the ontology with the internet turned off or by looking at the log messages that are generated while the ontology loads:
 +
Importing http://proton.semanticweb.org/2005/04/protons from location: Table protonsystem of the database jdbc:mysql://localhost/protege
 +
Importing http://proton.semanticweb.org/2005/04/protont from location: Table protontop of the database jdbc:mysql://localhost/protege
 +
 +
 +
== Converting an OWL file to the database backend using a script ==
 +
 +
If you prefer to make the OWL file to the database backend conversion using a script, we do provide an ant script for this purpose. Please refer to this [[ConvertToDBScript|wiki page]] for more information.

Latest revision as of 12:15, January 20, 2010

Working with the Database Backend in OWL


Before you start

First, this example assumes that you have a working mysql database and that there is an available database (which in this case is called protege) in the mysql database.

In addition, the mysql jdbc drivers are available to Protege. To make the drivers available, download the drivers from here, copy the jar file into the Protege installation directory and call it driver.jar. In theory, any jdbc database should work but we have only done testing for mysql (Innodb only - MyIASM doesn't work), postgres, oracle 9g and the microsoft sql server. The drivers for all of these options can be found here.

Opening an ontology using a web uri

This note will show by example how to convert an owl project and its imports to a database backend project. We will use the proton ontology as an example because it has a few non-trivial imports. The first step is to open it from the web. We start protege and in the welcome screen we select "New Project".


OwlDatabaseBackendProtegeWelcome.png


Note that if Protege is already open and you don't see the welcome screen then you can access the screen below by clicking on the File menu and selecting "New Project".

Now since we want to create a Protege OWL project using a url from the internet, we

  • select Use existing sources,
  • select OWL/RDF Files and
  • click next.


OwlDatabaseBackendCreateNewProject.png


Finally we enter the url (http://proton.semanticweb.org/2005/04/protonu) for the upper Proton ontology and click finish.


OwlDatabaseBackendChooseURLAndFinish.png

Wait a bit and the ontology will come up. When it does, you will see the reason that this ontology was chosen for this demonstration when you unravel the imports in the imports tab. There are two imports (protont and protons) which occur in two different places.


OwlDatabaseBackendProtonUImports.png


Converting a Project to a Database Project

Note If you prefer to make the OWL file to the database backend conversion using a script, we do provide an ant script for this purpose. Please refer to this wiki page for more information.

Now we will convert the upper Proton ontology to a database project. To do this click on the Protege File menu and select "Convert Project to Format". In this window select the "OWL / RDF Database" format and click ok.


OwlDatabaseBackendSelectProjectFormat.png


Once you click ok, you will see a dialog with several database settings. You can fill them as shown below. If you don't want to type out the entire path of where to save the project then press the box with a superscript + sign in the upper right hand side of the dialog and Protege will let you browse to where the project file should be saved.

NOTE: The driver class name will vary from the image below. For example, the name for the MySQL driver downloaded from the above link is 'org.gjt.mm.mysql.Driver' and the PostgreSQL driver name in the jar linked to above is 'org.postgresql.Driver' (omit quotes in both cases)


OwlDatabaseBackendSelectDbSettings.png


The jdbc:mysql://localhost/protege is parsed as follows. The jdbc:mysql: prefix says that this url is a mysql jdbc url and that the database can be loaded using jdbc with the mysql drivers. The localhost says that the database is running on the local machine and the protege says to use the mysql protege database (which I confgured using mysql tools before I started.) Note that if you are using international characters, you may want to consider adding some unicode options to the url as follows: 

    jdbc:mysql://localhost/protege?useUnicode=true&characterEncoding=UTF-8

Click finish and the database project will be created. Use whatever tool is available and you will find that a table has been added to the mysql database. In addition, it is not difficult to show that this project is an example of a database project importing two owl projects that are loaded from the web. The easy way to do this is to observe the two log messages on the Protege console: 

Loading triples from: http://proton.semanticweb.org/2005/04/protons
    Completed triple loading after 102 ms
Importing http://proton.semanticweb.org/2005/04/protont from location: http://proton.semanticweb.org/2005/04/protont
Loading triples from: http://proton.semanticweb.org/2005/04/protont
    Completed triple loading after 475 ms

Alternatively, close protege and save the project, turn off your internet connection and try to reload the database ontology. If you can do this then you will see the following popup dialog


OwlDatabaseBackendUnresolvedImport.png


and you will see the following exception (this is just a small bit of the full exception) on the console. 

SEVERE: Exception caught -- java.net.UnknownHostException: proton.semanticweb.org
   at java.net.PlainSocketImpl.connect(PlainSocketImpl.java:177)
   at java.net.Socket.connect(Socket.java:520)
   at sun.net.NetworkClient.doConnect(NetworkClient.java:152)
   at sun.net.www.http.HttpClient.openServer(HttpClient.java:387)
   at sun.net.www.http.HttpClient.openServer(HttpClient.java:522)
   at sun.net.www.http.HttpClient.<init>(HttpClient.java:231)
   at sun.net.www.http.HttpClient.New(HttpClient.java:304)

What these messages mean is that Protege needed to get the imported ontologies from the internet but was unable to find them. The next step is to show how to put all the data on the local machine in the same mysql database so that this ontology can be accessed without requiring an internet connection.

Making OWL Database projects import OWL Database projects

To continue, we must convert the 

http://proton.semanticweb.org/2005/04/protont

and the 

http://proton.semanticweb.org/2005/04/protons

projects to database projects. For this example we assume that both projects are converted to database projects using the same protege mysql database. However with small changes to the instructions below, one could use other databases for the other two projects including using postgresql or oracle database. In other words it is perfectly feasible for a mysql project to import a postgresql database project.

Important Note!: It is important that different projects using the same database (e.g. protege) use different table names. The diagrams below show the settings that I used for the two databases and it shows that I use the table ProtonTop and ProtonSystem for the table names.


OwlDatabaseBackendProtonTSettings.png


OwlDatabaseBackendProtonSSettings.png


Now that all three ontologies are stored on the local disk in a database, we can arrange that Protege will load all three ontologies from the database without going to the internet. To do this go back and reload the upper Proton ontology. Click on the OWL menu and select "Ontology repositories...".


OwlDatabaseBackendOWLRepositoriesMenu.png


This will bring up a view of the current repositories that are being used for owl imports. The two repository repository items already there indicate that the repository manager is finding the protons and protont ontologies off the web. We want to change this.


OwlDatabaseBackendOWLRepositoryManagerUI.png


To replace the repositories currently being used by Protege, first remove the existing ontologies by clicking on the minus sign beside them. Now click on the plus sign at the top right of the repository manager and you will get a list of repository types that can be added. Choose "database repository" and click next.


OwlDatabaseBackendSetDbRepositorySettings.png


Then fill in the database settings and click finish. If everything has gone well you should see the screen below. The three entries in the window indicate that the repository manager has found these three ontologies in the database and future imports of these ontologies will load these ontologies from database mode. Click the "Close button".


OwlDatabaseBackendDbRepositoryWorks.png


At this point there will be a dialog box asking if you want to reload the ontology. Click "Reload". The proton ontology will then be reloaded but now all three ontologies are in database mode. As before you can verify this by loading the ontology with the internet turned off or by looking at the log messages that are generated while the ontology loads: 

Importing http://proton.semanticweb.org/2005/04/protons from location: Table protonsystem of the database jdbc:mysql://localhost/protege
Importing http://proton.semanticweb.org/2005/04/protont from location: Table protontop of the database jdbc:mysql://localhost/protege


Converting an OWL file to the database backend using a script

If you prefer to make the OWL file to the database backend conversion using a script, we do provide an ant script for this purpose. Please refer to this wiki page for more information.

Retrieved from "https://protegewiki.stanford.edu/index.php?title=Working_with_the_Database_Backend_in_OWL&oldid=6866"