Doco Home Tutorial Home

Database and Query config files

Database

The Database config file contains the DO section for the tutorial. It's located in a file called 'database.conf' in the src/data directory.

The DO.DBMS has been set to PostgreSQL. This value can be set to any of the DBMS recognised by DODS. See 'Database.DBMS[]' in <DODS_INSTALLATION>/output/dods/dods.conf for valid values.

For each of the tables, a DO.TABLE.tablename section is created. The SQL_NAME for each table is set and each column within the table gets a COLUMN subsection. Refer to the DO Section docs for information on the TABLE and COLUMN sections. Note that in the tutorial not all columns were specified as they weren't needed.

The following is a portion of the DO section for the Activity table (t_activity):

DO.TABLE.Activity.SQL_NAME=t_activity
DO.TABLE.Activity.COLUMN.ActivityName.SIZE=255
DO.TABLE.Activity.COLUMN.ActivityName.DODS_TYPE=String
DO.TABLE.Activity.COLUMN.ActivityName.NULL_ALLOWED="false"

DO.TABLE.Activity.COLUMN.UseByContractors.DODS_TYPE=Boolean
DO.TABLE.Activity.COLUMN.UseByContractors.NULL_ALLOWED="false"
DO.TABLE.Activity.COLUMN.UseByContractors.DEFAULT_VALUE="true"

DO.TABLE.Activity.COLUMN.ObjectId.DODS_TYPE=ObjectId
DO.TABLE.Activity.COLUMN.ObjectId.NULL_ALLOWED="false"
DO.TABLE.Activity.COLUMN.ObjectId.PRIMARY_KEY="true"

Note that Activity and t_activity must match the details created in DODS. Also the column names must match their DODS counterparts. The DODS type is obtained from the file <DODS_INSTALLATION>/output/dods/types.conf.

Query

The Query config file contains the QUERY section for the tutorial. It's located in a file called 'squirt.conf' in the src/data directory. Refer to the QUERY Section docs for information on the QUERY section.

Each QUERY section specifies an SQL query required in the tutorial. The name of the QUERY is used to create a method to handle the query. There are examples in the tutorial of each type of query that Squirt can currently handle.

The following is a portion of the QUERY section that shows each of the query types:

QUERY.findTimeEntry.DO = TimeEntry
QUERY.findTimeEntry.TYPE = OBJECTID

QUERY.getPerson.DO = Person
QUERY.getPerson.TYPE = EASY
QUERY.getPerson.PARAMETERS[] = FirstName, Surname, Title, Email, Phone, Fax, Type, UserName, CompanyId
QUERY.getPerson.PARAMTYPES[] = String, String, String, String, String, String, String, String, CompanyDO
QUERY.getPerson.ORDERBY[] = "Surname", "FirstName"

QUERY.getExistingPersons.DO = Person
QUERY.getExistingPersons.TYPE = CUSTOM
QUERY.getExistingPersons.PARAMETERS[] = FirstName, Surname, CompanyId
QUERY.getExistingPersons.PARAMTYPES[] = String, String, String
QUERY.getExistingPersons.NULL_ALLOWED[] = EXCLUDE, EXCLUDE, EXCLUDE
QUERY.getExistingPersons.SQL.WHERE.clause1.CODE = "FirstName LIKE '<FirstName>'"
QUERY.getExistingPersons.SQL.WHERE.clause2.OPERATOR = "AND"
QUERY.getExistingPersons.SQL.WHERE.clause2.CODE = "Surname LIKE '<Surname>'"
QUERY.getExistingPersons.SQL.WHERE.clause3.OPERATOR = "AND"
QUERY.getExistingPersons.SQL.WHERE.clause3.CODE = "CompanyId = <CompanyId>"

The findTimeEntry query generates code to return a TimeEntryDO for the ObjectId passed. The ObjectId is passed as a String.

The getPerson query generates code to find a number of people based on the parameters passed. The results are ordered by the Surname and then the FirstName. Each parameter passed is only included in the query if it is not null or empty. The code generated by Squirt needs to be hand modified for the CompanyId to correct the null detection test. If you examine the getPerson.squirt file and compare it to the getPerson method in the final PersonBO, you will see the difference.

The getExistingPersons query shows the CUSTOM query type. The most intersting part of this QUERY is the QUERY.getExistingPersons.SQL.WHERE details. This is the specification of the WHERE clause of the query. The code generated handles substitutions of the parameters passed to the method. Note that the parameter is only included in the WHERE clause if the value passed at runtime is not null or empty.