Templating SQL commands

LAS v7.x introduces the concept of templated SQL statements. All SQL statements (database queries) issued by the 'database' service are based on the processing of Velocity templates associated with the 'database' service. (See the Velocity User's Guide for more information on the Velocity templating language and syntax.) The use of this templating language means that database queries can be constructed based on information available in the complete LAS request.

This new capability greatly enhances the ability of LAS to obtain information from databases with complex schemas. Basically, your database is ready to be used with LAS as long as you can create an SQL statement that returns the columns of data that LAS needs. (See Preparing your database.) You can then code this SQL statement into a template and reference this template in the <database_access> properties of the dataset configuration file. (See Creating the dataset configuration file.)

LAS does not support a <database_access> property with which to specify a particular template. Instead, the armstrong/conf/server/operationsV7.xml file defines several <operation>s for the 'database' service. The service_action="..." attribute of each <operation ...> tag gives the name of the SQL template(s) to use. The actual template file(s) will have the '.vm' extension appended to the name.

If you have data in databases with different schemas you can create a unique set of SQL statements for each by adding conditional statements to the database template(s) referenced in your operationsV7.xml file. Create these conditional statements using information from the <database_access> properties in a Velocity template conditional statement. (See below.)

All database templates must be located in:

las.v7.x/JavaSource/resources/database/templates/

The sql.vm file in this directory provides default functionality for data that reside in a single table :

#set($table=$las_backendrequest.getDatabaseProperty("db_table"))
#set($time=$las_backendrequest.getDatabaseProperty("time"))
#set($lat=$las_backendrequest.getDatabaseProperty("latitude"))
#set($lon=$las_backendrequest.getDatabaseProperty("longitude"))
#set($depth=$las_backendrequest.getDatabaseProperty("depth"))
#set($cruiseID=$las_backendrequest.getDatabaseProperty("cruiseID"))
## Or you can get the individual values of the min and max X,Y,Z and T with
## $las_backendrequest.getXlo(), etc.
#set($region=$las_backendrequest.getRegionAsConstraint())
#set($missing=$las_backendrequest.getMissingConstraint())
#set($var = $las_backendrequest.getVariablesAsString())
#set($con = $las_backendrequest.getConstraintString("AND"))

#if ( $con == "" && $cruiseID == "" && $depth == "")
SELECT $lat,$lon,$time,$var FROM $table WHERE $missing AND $region
#elseif ( $con == "" && $cruiseID == "" && $depth != "")
SELECT $depth,$lat,$lon,$time,$var FROM $table WHERE $missing AND $region
#elseif ( $con == "" && $cruiseID != "" && $depth == "")
SELECT $cruiseID,$lat,$lon,$time,$var FROM $table WHERE $missing AND $region
#elseif ( $con == "" && $cruiseID != "" && $depth != "")
SELECT $cruiseID,$depth,$lat,$lon,$time,$var FROM $table WHERE $missing AND $region
#elseif ( $con != "" && $cruiseID == "" && $depth == "")
SELECT $lat,$lon,$time,$var FROM $table WHERE $missing AND $region AND $con
#elseif ( $con != "" && $cruiseID == "" && $depth != "")
SELECT $depth,$lat,$lon,$time,$var FROM $table WHERE $missing AND $region AND $con
#elseif ( $con != "" && $cruiseID != "" && $depth == "")
SELECT $cruiseID,$lat,$lon,$time,$var FROM $table WHERE $missing AND $region AND $con
#elseif ( $con != "" && $cruiseID != "" && $depth != "")
SELECT $cruiseID,$depth,$lat,$lon,$time,$var FROM $table WHERE $missing AND $region AND $con
#end

The most important items to note are

1. ## begins a Velocity comment
2. # begins a Velocity statement (See the Velocity User's Guide)
3. $ begins a Velocity reference
4. $las_backendrequest is a Velocity reference whose methods can be found in the following source file:
   $LAS_HOME/JavaSource/gov/noaa/pmel/tmap/las/jdom/LASBackendRequest.java.
5. All other lines are (conditionally executed) SQL statements (after Velocity replaces the contents of the Velocity references)