MySource Matrix Developer

Data Source asset represents one query from a Database and provides an interface to build this query. Currently it is able to serve queries like 'Get names and ids of all users with balance below 10 dollars' or 'Get name, id and price of products added on the 1st of March'. Once set up, the Asset will always return the results of this query. The only time the set of results produced by this asset will change is when the contents of the queried database change.

In order to get all users whose balance is below 100 dollars it would be required to create a new Data Source asset and specify the query similar to the above example only it will have 100 instead of 10 in the WHERE clause. Creating copies of assets quickly becomes inconvenient if many variations of the same query are required and/or when there is a need to use the same formatting to display results that come from these different Data Source assets (i.e. an Asset Listing).

It seems logical to allow the query to obtain the parameter dynamically instead of always relying on the static value. So this document proposes the introduction of dynamic parameters to Data Source assets which can be used in both manually specified queries and those constructed using the Query Builder GUI.

Data Source Asset modifications

New Attribute of type 'Parameter Map'

This attribute is responsible for holding the settings for dynamic parameters. 'parameter_map' is a standard attribute type. It is used by the listing engine to obtain the dynamic root node. This attribute should:

• Be configured in the management class to display an option to 'Add Dynamic Parameter' on the administration interface.
• Only accept data from Post and Get request variables.
• Not allow any whitespace in the parameter name

New Screen - 'Dynamic Parameters'

This screen contains the interface for customising the settings of the parameter map.

Keywords for SQL queries

When dynamic parameters have been set-up, they can be used in SQL queries in the form of keywords. Each keyword will have the following syntax:

%%

PARAMETER_NAME_LOWERCASE is the name of the Request variable as specified in the 'Parameter Map' interface in lower case letters.

% is a special SQL character used as a wildcard in regular expressions. Two percent signs %% are used for keywords as opposed to the matrix standard one %. This is done to distinguish LIKE queries from keywords. I.e. % this% and %%. %%%% means that keyword is used inside the LIKE construct with wildcards around it.

These keywords have to be usable in any SQL query, even with the LIKE statement and percent signs.

Query Editor interface

Manual query editor should receive a dropdown box with each dynamic parameter available. The name displayed in the box is the name specified in the parameter map interface. When selected, the keyword will be added into the editor field at the current cursor location.

On the query builder interface, a dropdown should be present next to each value input box in the 'WHERE/HAVING' clause section. Currently existent value input box shold be there as well. When both the keyword and value are specified, the static value is used as the default in case the dynamic parameter is not supplied at run-time.

All dynamic parameters default to empty strings which doesn't break SQL, but may cause unexpected behaviour. A keyword in the query, which does not have a corresponding dynamic parameter defined is replaced with an empty string. If an input parameter keyword is specified and request variable is supplied, but the parameter map is not setup to handle this parameter, the keyword is replaced with an empty string.

Query Processing

Dynamic Parameters are replaced into the query before cache is checked, so all different combinations of dynamic parameters are cached separately.