Defining Selector SQL Expression in the model

Overview

In MetaBoss model each Entity object can own any number of Selectors. Selector is a simplified defintion of the fitering query, which can be executed on the owner entity. Multiple selectors can be invoked in the chain to produce combined filtered down result.

Each selector definition in the model may contain definitions of the selector input arguments to be used during filtering. For exampe 'HeavyUsers' selector may have an input parameter 'DownloadThreshold' used to only select users with usage larger than supplied threshold value.

Each selector definition in the model has a SQLSelector text attribute, which must contain a portion of the SQL statement this selector is responsible for. Selector has no control over what is being selected (caller program may decide to select count of entities, some attributes or whole entity) and over where selection is made from (obviously it will always be made from the table representing the entity). The only required portion is an expression inside SQL WHERE clause. This expression is is really the essence of selector and represents filtering condition the selector wants to apply. Simple macro language is supported here to allow symbolic references to selector input arguments as well as Entities and Attributes. At the code generation time Entity and Attribute references are substituted by actual table and column names. Input parameter references are substituted at run time by actual values passed to the selector from calling program.

Brief description of macro language

SQL Expression definition macro language syntax is in Apache Ant / Velocity style with following reserved words supported:

• ${EntAttribute.<attribute name>} - the occurrence of this expression will be substituted with the name of the column, which represents specified attribute in the entity, which owns this selector. It must refer to one of the valid attributes within the owner entity. In addition the inserted column name will be prefixed with current alias of the table reresenting the owner entity. This very important feature allows chaining of selectors together and should always be used in the main, top level part of the SQLSelector text.
• ${Inputs.<input field>} - the occurrence of this expression will be substituted with the value passed in the specified input field. It must refer to one of the valid input fields within this selector.
• ${Entity} - the occurrence of this expression will be substituted with the name of the table, which represents the entity, which owns this selector. Please note that this feature is only recommended to be used in subqueries. This is because such substitution is not using table alias and therefore selectors are unable to be chained properly.
• ${Ent} - the occurrence of this expression will be substituted with current alias of the table reresenting the owner entity. This feature allows to define correlated subqueries (ie. link subquery to the table in the main query).
• ${Attribute.<attribute name>} - the occurrence of this expression will be substituted with the name of the column, which represents specified attribute in the entity which owns this selector. It must refer to the valid attribute in the entity. Please note that this feature is only recommended to be used in subqueries. This is because such substitution is not using table alias and therefore selectors are unable to be chained properly.
• ${Entities.<entity name>} - the occurrence of this expression will be substituted with the name of the table, which represents specified entity. It must refer to the valid entity in the domain. Please note that this feature is only recommended to be used in subqueries. This is because such substitution is not using table alias and therefore selectors are unable to be chained properly.
• ${Attributes.<entity name>.<attribute name>} - the occurrence of this expression will be substituted with the name of the column, which represents specified attribute in the specified entity. It must refer to one of the valid entities in this domain and one of the valid attributes within the entity, which in turn myust be a valid entity within the domain. Please note that this feature is only recommended to be used in subqueries. This is because such substitution is not using table alias and therefore selectors are unable to be chained properly.

Some examples

• ${EntAttribute.DayOfWeek} = 'Sunday'

  In this example selector will only match entities where DayOfWeek attribute equals constant value.

• ${EntAttribute.TradingDate} = ${Inputs.TheDate}

  In this example selector will only match entities where TradingDate attribute equals supplied value of the TheDate input parameter.

• (${EntAttribute.UnclearedAmount} + ${EntAttribute.ClearedAmount}) > 0

  In this example selector will only match entities where Sum of values in two attributes is greater than constant value.

• ${EntAttribute.TradingDate}=(select max(${Attribute.TradingDate}) from ${Entity})

  In this example selector will only match entities where TradingDate attribute equals maximum value out of all values of TradingDate attribute of all instances.

Additional Notes

The actual text of resulting SQL statement can be observed at run time if logging level for the corresponding domain storage implementation package is set to DEBUG. This is true for all standard storage implementation generators included with MetaBoss. If you are writing the new storage implementation generator - it is a good idea to implement logging of the resulting SQL statements.