ToxOtis-db

From OpenTox
Jump to: navigation, search

ToxOtis-DB is a module of the ToxOtis suite is a persistence API for the ToxOtis core components. ToxOtis-DB can be used to read/write/update/delete OpenTox components like models, tasks and error reports against a relational database. Learn more about the structure of the ToxOtis database in this article where we provide detailed information about the entity relationships adopted.

JAQPOT-3 is powered by ToxOtis-DB and in fact since the migration from Hibernate to this ToxOtis module a great performance and stability increase has been observed. ToxOtis-DB undergoes a scrutiny of over 50 Unit tests including data integrity tests and DB crash tests before every release to leave little space to leave little room for bugs.

This article stands for a thorough documentation of the ToxOtis-DB API with code snippets and benchmarking results.

Contents

ToxOtis Database Module

Using ToxOtis-DB

ToxOtis-DB is available from our NEXUS repository since version 0.2.24. Older versions are also available from the AMBIT Nexus repository as a 3rd party artifact. Before one starts using ToxOtis-DB has to be acquainted with ToxOtis.

Overview

The objects that are persisted using ToxOtis-DB are the following:

  1. User
  2. BibTeX
  3. ErrorReport
  4. Model
  5. Parameter
  6. QprfReport
  7. Task

exactly what one needs in order to build an OpenTox-compliant Algorithm web service. The table structure of the ToxOtis-DB database will be explained in the following paragraphs.

Configure ToxOtis-DB

ToxOtis-DB is configured using the file c3p0.properties. Therein you can include any C3P0-related property - find documentation here. Additionally, you need to modify your Maven settings (the file is normally located at ~/.m2/settings.xml) and add the following:

<profiles>
  <profile>
    <id>ToxOtis_DataBase</id>
    <properties>
      <!--Database name
      Default value: toxotisdb-->
      <jaqpot.database.name>toxotisdb</jaqpot.database.name>
      <!--MySQL server port
      Default value: 3306-->
      <jaqpot.database.mysqlport>3306</jaqpot.database.mysqlport>
      <!--Database user
      Which user is used to access the database
      Default value: root-->
      <jaqpot.database.user>root</jaqpot.database.user>
      <!--Password for accessing your MySQL database server
      Default value: opensess@me [CHANGE IT!!!]-->
      <jaqpot.database.password>my_pass123</jaqpot.database.password>     
    </properties>
  </profile>
</profiles>

Do not forget to activate the profile e.g. appending the following piece of XML in your settings.xml file:

<activeProfiles>
  <activeProfile>ToxOtis_DataBase</activeProfile>
</activeProfiles>

One can configure C3P0 from within their Java application using DbConfiguration. Before any other DB-related code insert the following line:

DbConfiguration dbconfig = DbConfiguration.getInstance();
Properties props = new Properties();
props.setProperty("c3p0.maxPoolSize", "2000");
/* More props... */
dbconfig.setProperties(props);

Connect to the database

DataSourceFactory is a factory used to create DataSource objects from which a Database Connection is retrieved and used to perform a single database transaction. The connection is retrieved from C3P0 which is the underlying connection pool. Have you properly configured your C3P0 pool, you should be able to access an existing database:

DataSourceFactory factory = DataSourceFactory.getInstance();
Connection connection = factory.getDataSource().getConnection();

Important: If the database does not exist yet - which is the case the first time you use ToxOtis-DB, you can create it running the main method in CreateDatabase :

CreateDatabase.main();

The DataSourceFactory object created as above can be used to ping the database server:

boolean pingSuccessful = factory.ping(50);

Before shutting down your application you should consider closing gracefully the connection pool (otherwise MySQL server complains). Create a shutdown hook and use the following command to stop the pool:

DataSourceFactory.getInstance().close();

DB Operations

In this section we present a set of DbWriters which are used to register objects in the database. Each writer can carry out only a single database transaction. No SQL is required from the programmer. In what follows we use the random object generator ROG to create random objects which we register in the database and subsequently we retrieve them and check data integrity.

In what follows we present all DB entities along with the SQL commands used for their creation and some graphical representation for better comprehension.

General

The five fundamental DB operations, namely READ, WRITE, UPDATE, DELETE and COUNT are implemented in ToxOtis-DB and are exposed through the abstract classes DbReader, DbWriter, DbUpdater, DisableComponent/DeleteOldComponents and DbCount. Registration of an object in the database is mediated by a DbWriter using the method:

public abstract int write() throws DbException;

A DbException is thrown if the object cannot be registered in the database. An important method implemented by DbWriter and by all DbOperation is the method (javadoc):

 public void close() throws DbException;

Always invoke #close() after you use a DbWriter - enclose it properly in a try-catch-finally block to make sure it is always invoked. Here is an example:

try{
  DbWriter writer = ... ; // Initialize your DbWriter object
  writer.write();
} catch (final DbException ex){
  // Handle Exception (log it, throw it, etc)
} finally {  
  try{
    finder.close();
  } catch (final DbException dbex){
    // Handle the exception
  }
}

A DbReader<T> can be used to read T-objects from the database. Subclasses of DbReader implement the method to output the results found in the database:

public abstract IDbIterator<T> list() throws DbException;

IDbIterator<T> is an interface that defines the following methods:

boolean hasNext() throws DbException;
T next() throws DbException;
public void close() throws DbException;

Here a simple use case:

try{
  DbReader<T> reader = ...;
  IDbIterator<T> iterator = reader.list();
} catch (final DbException ex){
  // Handle Exception (log it, throw it, etc)
} finally {
  try{
    iterator.close();
  } catch (final DbException dbex){
    // Do not throw this exception - log it though.
  }
  try{
    reader.close();
  } catch (final DbException dbex){
    // Handle the exception
  }
}

Regarding UPDATE, existing objects in the database, identified by their UID, can be updated using an SQL command of the general form:

UPDATE `Table_Name` SET `Update_Arguments` WHERE `Table_Name`.id='Given_ID'

There is only a single implementation of DbUpdater in ToxOtis-DB, namely UpdateTask but developers might be interested in subclassing DbUpdater to create their own updaters.

ToxOtis-DB does not support direct deletion of components for security reasons (not to delete something that you might want). However, you may disable a components by flagging it as deleted. All information is still there in the database but will not be exposed through the ToxOtis-DB API. Later you may consider un-deleting/enabling the component. Disabling/Enabling a component is carried out via DisableComponent. You can use the class DeleteOldComponents to completely remove from the database those components that were deleted a long time ago. In order to disable a component of known UID use the following snippet:

DisableComponent processor = new DisableComponent("c30449a1-7cd1-4bc0-81cb-3fe348cc667f");
try{
  processor.disable();
} catch (final DbException ex){
  /* Handle the exception */
} finally {
  try{
    processor.close();
  } catch (final DbException ex1){
    /* DbOperation cannot close - Handle the exception */
  }
}

Users

AddUser is a simple DB writer which we use here to register an instance of User in the database.

ROG rog = new ROG();
User mockUser = rog.nextUser();
AddUser au = new AddUser(mockUser);
int write = au.write();
// Always close the DB-writer after use!
au.close();

One can search for a user with a given uid or other attribute. This is accomplished by means of the class FindUser. Here is an example:

FindUser finder = new FindUser();
finder.setWhere("uid='chung'");
IDbIterator<User> iterator = finder.list();
User user = null;
if (iterator.hasNext()) {
  user = iterator.next();
} else {
  // No such user in the database
}

SQL-related wildcards are allowed in the above context. For example if we need to list all users whose email is on yahoo.com we would write:

FindUser finder = new FindUser();
finder.setWhere("mail LIKE '%@yahoo.com'");
IDbIterator<User> iterator = finder.list();
User user = null;
while (iterator.hasNext()) {
  user = iterator.next();
  // Do something with all these users...
} 

Important: Once you are over with the iterator and the finder and you do not use it any more consider closing them. Doing it in a finally block is the best practice:

try{
  /*
   * User Finder and DbIterator
   */
} catch (final DbException ex){
  // Handle Exception (log it, throw it, etc)
} finally {
  try{
    iterator.close();
  } catch (final DbException dbex){
    // Do not throw this exception - log it though.
  }
  try{
    finder.close();
  } catch (final DbException dbex){
    // Handle the exception
  }
}

The method ListUsers#list()::IDbIterator<String> can be used to get just a list of users' UIDs or names. In the following example, ListUsers is employed to list all usernames in the database:

ListUsers list = new ListUsers();
list.setMode(ListUsers.ListUsersMode.BY_NAME);
try {
  IDbIterator<String> userNames = list.list();
  String nextName = null;
  while (userNames.hasNext()) {
    nextName = userNames.next();
  }
} finally {
  list.close();
}

Tasks

Task-related database processors are found in the package org.opentox.toxotis.database.engine.task. Register a task in the database using the class AddTask. Here is an example (see also this test):

Task t = new Task(Services.ntua().augment("task", UUID.randomUUID()));
DbWriter writer = new AddTask(t);
writer.write();

It is important to underline that only UIDs are stored in the database to identify resources and not the whole URI. So, for example if you register a task with URI http://someserver.com:8080/opentox/task/1, only the value 1 will be stored in the database since (i) Tasks from other domains are not expected to be stored in the database maintained by our web application and (ii) the URI of the task can be retrieved just from the UID of the task. In [JAQPOT3] we have chosen to use UUIDs as UIDs for all of our resources so that UIDs are unique even over different domains.

A FindTask object is used to read tasks from the database. As you can see in the following example, the constructor of the DbReader requires three arguments, the first of which is the base URI of your web application.

FindTask ft = new FindTask(new VRI("http://alphaville:4000/jaqpot"), false, false);
ft.setWhere("Task.id LIKE 'ab%'");
IDbIterator<Task> iter = ft.list();


References

  1. ToxOtis DB structure
Personal tools