Using Your Database

Acceptable Databases

VARS was originally developed on Microsoft SQL Server and was ported to Apache Derby with relatively little effort. However, some changes in the datatypes used in the knowlegebase were required. In the next few weeks there will be an effort to ensure that all the datatypes in VARS are SQL92 compliant. VARS should run on the following databases:

Setting up the Database

Schema

In order to run VARS you must set up the required database tables. VARS does not depend on any triggers or stored procedures in order to run. However, you may feel free to add them if needed. For example, you may want to setup triggers that log the history of changes for a particular table. Also, you may add whatever columns you wish to a table; for example, if you setup database replication you may need to add additional columns. However, do not remove any columns that are listed in the schema. They are required by VARS. There are currently 2 reference implementations available, I recommend that you use the SQL Server Schema as a base for your database schema.

Reference implementations:

Permissions

VARS is setup to use a database user when connecting. Be sure that the user has read and write permission to the VARS database tables. The default database for the user should also be set to VARS. You should not consider VARS to be a secure system.

Annotations View

The VARS Query application uses the Annotations view in order to retrieve data. You can modify the view to suit your needs, such as changing the names of the returned columns, adding additional columns or removing existing columns from the view. However, the following columns are required by the query application (i.e. do not remove or alter these columns):

Configuring VARS to use your database

JDBC Driver

To use your database you will need a JDBC Driver. Here is a list of possible JDBC drivers:

Place the JDBC driver jar file in VARS_HOME\lib. You will need to add it to the classpath of your application. If you are using the launch scripts included in VARS_HOME\bin then you will need to modify the VARS_CLASSPATH line in VARS_HOME/bin/varsSetup.bat (on windows) or VARS_HOME\bin\varsSetup (on everything else) to include your jar file.

Configuring vars_database.xml

The next step is to figure out the JDBC URL to use to contact your database. The format of the URL depends on the database and driver. You will need to refer to the documentation for your particular driver.

Once you have a URL in hand you will need to edit the file VARS_HOME\conf\vars_database.xml. First you will need to tell castor which database you are using. Reference on the different engine types can be found at http://www.castor.org/database-conf.html#The-Castor-configuration-file. Here is an example of a database configured for SQL Server: 

<database name="vars" engine="sql-server">
Finally, you must configure the data source. This includes the driver class, the URL to your database, the user that you are connecting to your database as, the password for the user. Here is an example configured for SQL Server using jTDS: 
<param name="driver-class-name" value="net.sourceforge.jtds.jdbc.Driver" />
<param name="username" value="someusername" />
<param name="password" value="somepassword" />
<param name="url" value="jdbc:jtds:sqlserver://somedatabase.mbari.org:1433/VARS" />

More Information

Connection Pooling

VARS uses Apache DBCP to handle connection pooling. You do not need a JDBC driver that has connection pooling support to use this feature.