Attribute Search

An attribute search allows the server to locate entities using SQL.

Namespace

urn:com.cohga.server.search.database#1.0

Tags

attribute

Properties

Name	Type	Required	Description
id	string	yes	Unique identifier for this search
entity	ref urn:com.cohga.server.entity#1.0:entity	yes	The id of the entity that this search will be searching for
label	string	yes	Text to be displayed to the user to represent this search
description	string	no	Description of the search that could be displayed to the user to explain this search
datasource	ref urn:com.cohga.server.datasource.jdbc#1.0:datasource	yes	The data source that should be connected to perform the search
table	string	yes	The name of the table in the datasource that is the root of the generated SQL
key	string	yes	The column in table that represents the unique id of the entity being searched for
acl	ref urn:com.cohga.server.acl#1.0:acl	no	A reference to an ACL to attach to the search

Sub-tags

Name	Type	Cardinality
from	urn:com.cohga.server.search.database#1.0:from	0..n
where	urn:com.cohga.server.search.database#1.0:where	0..n
parameter	urn:com.cohga.server.search.database#1.0:parameter	1..n
combination	urn:com.cohga.server.search.database#1.0:combination	0..n
acl	urn:com.cohga.server.acl#1.0:acl	0..1
cache	urn:com.cohga.server.cache#1.0:cache	0..1
options	urn:com.cohga.server.search.database#1.0:options	0..1

Notes

An ACL can either be defined in-line or referenced indirectly, but only one should be used (the in-line version will take priority)

parameter

Properties

Name	Type	Required	Default	Description
id	string	yes		A unique identifier for the parameter. As of Weave 2.6.8 "name" can be used instead of "id"
label	string	yes		The prompt text displayed when user input the parameter value
column	string	no		The name of the column within the table that this parameter references, if this isn't provided then a `where` sub-tag must be added to this parameter to describe how the parameter should be included in the generated SQL.
displayname	string	no	label	Provides a user-friendly name for the element
helptext	string	no		Additional text to display for the parameter to explain how to use the parameter
hidden	boolean	no	false	Hides the parameter from the parameter UI
alignment	'left', 'center', 'right', 'auto'	no	'auto'	How the items should appear in the UI
controltype	'listbox', 'checkbox', 'radiobutton', 'textbox'	no	'textbox'	The suggested type of UI control to use when displaying the parameter
datatype	'any', 'boolean', 'datetime', 'decimal', 'float', 'integer', 'string'	no	'string'	The data type for the parameter
allownull	boolean	no	false	Whether a null value is allowed for this parameter
allowblank	boolean	no	true	Give the user the choice of an empty value in the listbox (as opposed to a null value)
allownewvalues	boolean	no	false	Allow the user to enter values not in the listbox already. Note that setting this to true will convert the list box into a combo box, also note that the default was true before 2.5.30.
defaultvalue	any	no		The default value of the parameter
displayformat	string	no		the formatting instructions for the parameter value within the parameter UI
dataset	ref urn:com.cohga.server.data.database#1.0:datadefinition	no		Where to get the values for a listbox. Note the config will also accept “datadefinition” as the name for this attribute for backwards compatibility.
labelcolumn	string	no	label	The name of the parameter in the datadefinition specified by the dataset attribute that supplies the label of the list entry to show the user.
valuecolumn	string	no	value	The name of the parameter in the datadefinition specified by the dataset attribute that supplies the value of the list entry to use in the SQL
sortcolumn	`none`, `defined`, `label`, `value`, `default`	no	`label`	How should the list of values be sorted: `none` - Don’t perform any sorting `defined` - As defined in the data definition `label` - Based on the label `value` - Based on the value `default` - Use the default sorting (currently `label`) Note this only applied when the control type is list box, and sorting is done in ascending order, if you require descending order you need to do it in the data definition and use `defined` as the sort column type
uppercase	boolean	no	false	Should the value be converted to upper case in the generated SQL
scalarparametertype	string	no	'simple'	'simple' or 'multi-value' to determine of more than one value can be selected from a list.
matchtype	string	no	'start'	'start', 'middle' or 'end' to determine if list filtering is performed by matching against the start of the list entry, the middle or the end of the label.
width	integer	no		Set the width of the field
pagesize	integer	no	10	Number of items to show on a list page, 0 disables paging
autoselect	boolean	no	true	Should the first value in a list be pre-selected
minvalue	any	no		The minimum value allowed for a field. Available from 2.5.28.
maxvalue	any	no		The maximum value allowed for a field. Available from 2.5.28.
minlength	integer	no		The minimum length allowed for a field. Available from 2.5.28.
maxlength	integer	no		The maximum length allowed for a field. Available from 2.5.28.
increment	integer	no		The increment to use for fields that support it, the units are dependant upon the field type. Available from 2.5.28 and currently only for time fields.
trueValue	any	no		The value that equates to "true" in the underlying table, only suitable for checkboxes
falseValue	any	no		The value that equates to "false" in the underlying table, only suitable for checkboxes

Sub-tags

Name	Type	Cardinality
from	urn:com.cohga.server.data.database#1.0:from	0..n
where	urn:com.cohga.server.data.database#1.0:where	0..n
parameter	urn:com.cohga.server.data.database#1.0:parameter	0..n
list	urn:com.cohga.server.search.database#1.0:list	0..n

Notes

If the controltype is listbox then a dataset must be provided, this dataset will provide the values to be displayed in the listbox.
If the listbox dataset only contains 1 column then that column will supply both the label and the value, if it contains two values then the first column will supply the label and the second the value.
If a parameter contains another parameter then you are defining a cascading parameter, where setting the first sub-parameter will enable, and filter, the second parameter, and setting the second parameter will do the same for the third, etc.
In a cascading parameter all sub-parameters must be of listbox type, and the dataset should be set in the outer parameter, not the sub parameters.
In a cascading parameter only one level of nesting should be used.
In a cascading parameter the dataset should supply the columns for all of the parameters, and valuecolumn properties should be set for each sub-parameter, and labelcolumn should be also set for all parameters if a different label is to be displayed to the user.
If the controltype is radiobutton then 2 or more list entries must be provided to populate the buttons. If no defaultValue is set, the first entry will be taken as the default.

combination

Properties

None

Sub-tags

Name	Type	Cardinality
from	urn:com.cohga.server.data.database#1.0:from	0..n
where	urn:com.cohga.server.data.database#1.0:where	1..n

from

Properties

Name	Type	Required	Description
table	string	yes	An additional table to include in the generated SQL

where

Properties

Name	Type	Required	Description
clause	string	yes	An additional clause to include in the generated SQL.
uppercase	boolean	no	If the clause uses parameter substitution should the value be converted to upper-case before being substituted

list

Properties

Name	Type	Required	Description
value	string	yes	The value used if this list item was chosen
label	string	yes	The label to display for this list item

options

Properties

Name	Type	Required	Description
limit	integer	no	Place a limit on the number of items that will be returned

Examples

Comprehensive search example

<data:datadefinition id="dd_roadtypes">
	<datasourcedataconnection datasource="datasource.main" table="ROADS" prefix="DISTINCT">
		<parameter type="string" name="roadname" column="ROADNAME"/>
		<parameter type="string" name="roadname_code" column="ROADNAME_CODE"/>
		<parameter type="string" name="roadtype" column="ROADTYPE"/>
		<parameter type="string" name="roadtype_code" column="ROADTYPE_CODE"/>
	</datasourcedataconnection>
</data:datadefinition>

<data:datadefinition id="dd_suburbs">
	<datasourcedataconnection datasource="datasource.main" table="SUBURBS" prefix="DISTINCT">
		<parameter name="label" column="PROPER(SUBURB)"/>
		<parameter name="value" column="SUBURB"/>
	</datasourcedataconnection>
</data:datadefinition>

<search:attribute id="property.address.entry">
	<entity>property</entity>
	<label>Address by Entry</label>
	<description>Locate a property or properties by their street address</description>
	<datasource>datasource.main</datasource>
	<table>PID_LINK</table>
	<key>PID</key>
	<from>
		<table>PROPERTY</table>
	</from>
	<where>
		<clause>LINK.ID=PROPERTY.ID</clause>
	</where>
	<parameter id="unit">
		<label>Unit</label>
		<column>UNIT</column>
	</parameter>
	<parameter id="house_from">
		<label>House From</label>
		<datatype>integer</datatype>
		<column>PROP_NO</column>
	</parameter>
	<parameter id="house_to">
		<label>House To</label>
		<datatype>integer</datatype>
		<column>PROP_NO</column>
	</parameter>
	<parameter id="suburb">
		<label>Suburb</label>
		<controltype>listbox</controltype>
		<allownull>true</allownull>
		<column>SUBURB</column>
		<dataset>dd_suburbs</dataset>
	</parameter>
	<parameter id="road">
		<dataset>dd_roadtypes</dataset>
		<parameter id="roadname">
			<label>Road Name</label>
			<controltype>listbox</controltype>
			<column>ROADNAME_CODE</column>
			<valuecolumn>roadname_code</valuecolumn>
			<labelcolumn>roadname</labelcolumn>
		</parameter>
		<parameter id="roadtype">
			<label>Road Type</label>
			<controltype>listbox</controltype>
			<column>ROADTYPE_CODE</column>
			<valuecolumn>roadtype_code</valuecolumn>
			<labelcolumn>roadtype</labelcolumn>
		</parameter>
	</parameter>
	<parameter id="postcode">
		<label>Postcode</label>
		<datatype>integer</datatype>
		<where>
			<clause><![CDATA[POSTCODE=${postcode}]]></clause>
		</where>
	</parameter>
	<combination>
		<where>
			<clause><![CDATA[PROP_NO >= ${house_from} and PROP_NO <= ${house_to}]]></clause>
		</where>
	</combination>
	<cache>
		<maxElementsInMemory>50</maxElementsInMemory>
		<eternal>false</eternal>
		<timeToIdleSeconds>0</timeToIdleSeconds>
		<timeToLiveSeconds>300</timeToLiveSeconds>
		<overflowToDisk>true</overflowToDisk>
		<maxElementsOnDisk>2000</maxElementsOnDisk>
		<diskPersistent>false</diskPersistent>
		<diskExpiryThreadIntervalSeconds>120</diskExpiryThreadIntervalSeconds>
		<memoryStoreEvictionPolicy>LRU</memoryStoreEvictionPolicy>
	</cache>
	<options>
		<limit>2000</limit>
	</options>
</search:attribute>

Searching on a date or a range of dates

<search:attribute id="property.by_sale_date">
	<entity>property</entity>
	<displayName>by Sale Date</displayName>
	<description>Locate a property by the date of its sale</description>
	<dataSource>datasource.property</dataSource>
	<table>PROP_SALE</table>
	<key>PROPERTYID</key>
	<parameter id="startdate">
		<promptText>Start Date</promptText>
		<controlType>text-box</controlType>
		<dataType>date</dataType>
		<column>SALEDATE</column>
	</parameter>
	<parameter id="enddate">
		<promptText>End Date</promptText>
		<controlType>text-box</controlType>
		<dataType>date</dataType>
		<column>SALEDATE</column>
	</parameter>
	<combination>
		<where>
			<clause><![CDATA[SALEDATE between ${startdate} and ${enddate}]]></clause>
		</where>
	</combination>
</search:attribute>

The trueValue/falseValue settings in the checkbox in the following example only work for Weave 2.5+

Search with checkbox and radiobutton

<search:attribute id="certificate.by_status">
	<entity>certificate</entity>
	<displayName>by Status</displayName>
	<description>Locate a certificate by its current status</description>
	<dataSource>datasource.main</dataSource>
	<table>CERTIFICATES</table>
	<key>ID</key>
	<parameter id="current">
		<label>Current</label>
		<controlType>checkbox</controlType>
		<column>IS_CURRENT</column>
		<trueValue>Y</trueValue>
		<falseValue xsi:nil="true"/>
	</parameter>
	<parameter id="state">
		<label>State</label>
		<controlType>radio-button</controlType>
		<column>STATE</column>
		<defaultValue>P</defaultValue>
		<list>
			<label>Pending</label>
			<value>P</value>
		</list>
		<list>
			<label>Registered</label>
			<value>R</value>
		</list>
		<list>
			<label>Active</label>
			<value>A</value>
		</list>
		<list>
			<label>Closed</label>
			<value>C</value>
		</list>
		<list>
			<label>Unknown</label>
			<value xsi:nil="true"/>
		</list>
	</parameter>
</search:attribute>

Wildcards

When searching text columns the default SQL generated will use an 'equals' expression rather than a 'like' expression in the where clause, unless the user includes a '%' in the search value.

You can override this by not setting a "column" in the parameter definition but instead adding a "where" clause to the parameter that defines exactly the SQL you want included, so for example:

which would become (unless the user entered a value that included a %)

in the generated SQL.

You should instead use:

which uses ${text_param} as a place marker for the value the user entered for the parameter named 'text_param', and would become

To be safe, it is recommended that the where clause is wrapped in CDATA tags, so the where clause would be

Make sure you remove the column attribute if you use a where clause that references the column, otherwise you'll have both the default where clause and your custom where clause which will likely result in nothing matching the search.

As of Weave 2.6.10 when using a where clause in place of a column attribute it is now possible to use a ? as a marker for the value to be replaced within the where clause, rather than using the syntax ${name}.
This was added to allow for the database driver to be responsible for converting the value provided for the search to the data type appropriate for the underlying database column.

<where clause="COLUMN_NAME = ${param_name}"/>

would become

<where clause="COLUMN_NAME = ?"/>

It is not required to use the new syntax and is only provided as an advanced option for when the standard parameter value handling is not suitable.

Date parameters in SQL Server

The date being passed from the client to the server is in the following format yyyy-mm-ddThh:mi:ss.mmm (no spaces). If you are overriding the where clause of a parameter you will need to convert the date string from the client into a SQL Server date. To do this use the CONVERT function using style 126. For more information see the following page

https://msdn.microsoft.com/en-AU/library/ms187928.aspx

As an example here are two date parameters in a single search that override the where clause.

New alternate syntax

To make search and data definition configuration more consistent as of Weave 2.6.8 it's possible to define a search with a similar structure to a data definition, that is having a generic "search" wrapper tag around an implementation-specific search config, e.g. This

could become this

Where the containing <search:implementation> tag would become a generic <search:search> tag that contains the <implementation> tag within it (along with just the id of the search, no other content is allowed).

In addition to this parameters can have their id set using a "name" attribute rather than "id", which also matches data definitions (and parameters used elsewhere), in fact, all parameters (let me know if you find otherwise) can now have their id set using either "name" or "id"