Customizing Your Database

Overview

It is likely that you will want to add additional data fields to the VARS database. For annotations, the most likely data will be some sort of physical measurements or parameters. This can be accomplished by adding additional fields to the PhysicalData class, mapping file and database table. This document will outline the steps needed to modify your database.

Modifying PhysicalData

In this example we will add a field named 'density' to the VARS application

Modifying PhysicalData.java

Open up the file src\main\java\org\mbari\vars\annotation\model\PhysicalData.java in your favorite editor or IDE. You will need to add a private field with public accessor methods

private float density;

public float getDensity() {
	return density;
}

public void setDensity(float density) {
	this.density = density;
}

Modifying annotation_mapping.xml

Open up the file resources\conf\annotation_mapping.xml in a text editor. Locate the mapping for the PhysicalData class; it will start with <class name="org.mbari.vars.annotation.model.PhysicalData". Add the following lines to it:

<field name="density" type="float">
    <sql name="Density" type="real" dirty="ignore"/>
</field>

Modifying the PhysicalData Database Table

The next step is to add a density column to the PhysicalData table in the VARS database. The SQL to do this is:

ALTER TABLE PhysicalData 
ADD Density REAL

Modifying the Annotations View

The last step is to add density to the Annotations view. This allows the query application to query against and return density. No modifications to the query application is required since it generates it's user interface dynamically based on the Annotations view. Here is an example that alters the Annotations view and adds density:

ALTER VIEW Annotations AS
SELECT  
    Observation.ObservationDTG AS ObservationDate, 
    Observation.Observer, 
    Observation.ConceptName, 
    Observation.Notes, 
    VideoFrame.TapeTimeCode, 
    VideoFrame.RecordedDtg AS RecordedDate, 
    VideoFrame.InSequence,
    VideoArchive.videoArchiveName, 
    VideoArchiveSet.TrackingNumber, 
    VideoArchiveSet.ShipName, 
    VideoArchiveSet.PlatformName AS RovName, 
    VideoArchiveSet.FormatCode, 
    CameraPlatformDeployment.SeqNumber AS DiveNumber, 
    CameraPlatformDeployment.ChiefScientist, 
    CameraPlatformDeployment.UsageStartDTG AS DiveStartDate, 
    CameraPlatformDeployment.UsageEndDTG AS DiveEndDate, 
    CameraData.Name AS CameraName, 
    CameraData.Direction AS CameraDirection, 
    CameraData.Zoom, 
    CameraData.Focus, 
    CameraData.Iris, 
    CameraData.FieldWidth, 
    CameraData.StillImageURL AS Image, 
    PhysicalData.Depth, 
    PhysicalData.Density
    PhysicalData.Temperature, 
    PhysicalData.Salinity, 
    PhysicalData.Oxygen, 
    PhysicalData.Light, 
    PhysicalData.Latitude, 
    PhysicalData.Longitude, 
    Observation.id AS ObservationID_FK, 
    Association.id AS AssociationID_FK, 
    Association.LinkName, 
    Association.ToConcept, 
    Association.LinkValue, 
    Association.LinkName + ' | ' + Association.ToConcept + ' | ' + Association.LinkValue AS Associations, 
    VideoFrame.id AS VideoFrameID_FK
FROM         
    Association LEFT OUTER JOIN
    Association_Child_Parent ON Association.id = Association_Child_Parent.Parent_AssociationID RIGHT OUTER JOIN
    Observation ON Association.ObservationID_FK = Observation.id LEFT OUTER JOIN
    PhysicalData RIGHT OUTER JOIN
    VideoFrame ON dbo.PhysicalData.VideoFrameID_FK = VideoFrame.id LEFT OUTER JOIN
    CameraData ON dbo.VideoFrame.id = CameraData.VideoFrameID_FK LEFT OUTER JOIN
    CameraPlatformDeployment RIGHT OUTER JOIN
    VideoArchiveSet ON CameraPlatformDeployment.VideoArchiveSetID_FK = VideoArchiveSet.id RIGHT OUTER JOIN
    VideoArchive ON dbo.VideoArchiveSet.id = VideoArchive.VideoArchiveSetID_FK ON 
    VideoFrame.VideoArchiveID_FK = VideoArchive.id ON Observation.VideoFrameID_FK = VideoFrame.id