Projects:HQLExpressionField

Contents 1 Status 2 Team 3 Definition 3.1 Feature overview 3.2 Users & use cases 4 Design 4.1 Technical specification 4.1.1 Model Changes 4.1.2 Server side changes 4.1.2.1 Datasource querying 4.1.2.2 Inserting/Updating an Entity 4.1.2.3 Field generation 4.1.3 Client side changes 5 Planning 6 Development 6.1 Tracking 6.2 Implementation 6.3 Documentation 7 Resources

Status

• Stage: Definition
• Planned Release: MP5
• Status Notes: Proposal

Team

• Contact: Martin Taal (martin.taal@openbravo.com)

Definition

Currently the user can only link a field to a direct database column of the table of the tab. However, there are cases where the user would also like to add computed properties to a grid/form (as display field) and would also want to filter/sort using these columns. The sorting and filtering is done in the database, this means that the computed property needs to be expressed using a HQL query. This proposal outlines the changes necessary for implementing a HQL Expression Field.

This development is related to other developments (see resources below) which also 'break' the strict relation between ad_field and ad_column:

• CanvasField
• PropertyField

Feature overview

• Make it possible to specify a HQL expression in the ad_field to define a hql-computed property.
• Show the computed value in the form/grid as a display value
• Allow sorting and filtering by the hql expression field

Users & use cases

Design

The development is a combination of changes in the application dictionary, the query mechanism and the json serializer.

If a HQL expression is entered then it is not necessary to set the ad_column value in ad_field. Therefore 2 other columns have to be added to ad_field:

• ad_element_id: the be able to determine the label of the field in the user interface
• ad_reference_id and ad_reference_value: to specify the type of the field

The hql expression is executed by the datasource and the result is added to the json object. Filtering and sorting should also be possible.

The complexities of the development are:

• currently the datasource querying always assumes to query for a single entity (a single entity result). In the new solution one or more named HQL expressions should be added to the datasource query and the result set.
• when updating or inserting a new instance then the computed property needs to be recomputed for that specific entity. This is bit harder as the insert/update action only performs an update/insert query and not a full select query.

Technical specification

Model Changes

Add 3 news columns to AD_Field:

• hqlExpression: non-mandatory, varchar, 2000. If set then ad_column and the property or javascript function fields can be hidden (see related dev projects)
• ad_element_id: to be able to specify a label for a field (as the ad_column_id can not be used anymore)
• ad_reference_id and ad_reference_value_id: similar to the definition in ad_column_id

Server side changes

Datasource querying

The datasource querying needs to be adapted to allow passing in one or more named hql expressions. In correspondence with the extra properties parameter used for the data source, the proposal is to add a new parameter which is handled by the datasource querying: hql expressions. The idea is to encode the hql expressions as json:

{
quantitySum: 'select sum(e.quantity) from OrderLine e where e.order.id=:f.id 
}
 

The name of the json property is the alias used in the hql query and it is the alias used for passing in filter and sorting parameters.

When a datasource is generated for a tab the fields with hql expressions need to be collected. The HQL expressions should be encoded as json and passed on to the datasource java script creator to be added as a parameter in the datasource requests to the server.

The same expressions should also be re-computed when inserting/updating an entity.

The generation of the datasource javascript is done through the OBViewTab.getDataSourceJavaScript method which uses the DataSourceComponent.

To support retrieval, filtering and sorting for expression properties the following needs to be changed:

• The DataSourceComponent and its related template (see the templates sub package in that same module) should be adapted to support a new option: EXPRESSION_PROPERTIES.
• The value of the EXPRESSION_PROPERTIES should be used to generate additional datasource fields, these datasource fields should have a new property called hqlExpression.
• OBQuery should be adapted to allow for additional select clauses with names. The select clauses are combinations of name and expression. Note that the returned result of obquery is then not a BaseOBObject but an array of objects. Maybe an additional list method should be added which returns an array of objects.
• The new request params should be picked up in the DefaultJsonDataService java class and passed on to the DataEntityQueryService which can pass them on to the OBQuery. The DataEntityQueryService should be able to handle an array of objects in the return of OBQuery. The returned objects should be converted to json and the additional results from the select should be added to the json.
• the OBViewTab.getDataSourceJavaScript method should pick/collect all HQL expression fields from ad_field and collect them in one json object, the string version of this json object should be passed to the datasource component using the new EXPRESSION_PROPERTIES option.

Inserting/Updating an Entity

When an entity is updated or inserted the full entity is returned as json from the server to the client and is used by Smartclient to update internal caches. This is great functionality as it allows the server to make changes to the data while inserting/updating and make those changes visible in the client.

The same feature should be used to support recomputing expression properties when inserting/updating. The challenge is that expression properties need to be executed as part of a hql query and insert/update is not done through hql. The following solution is therefore proposed:

• After inserting/updating a query is generated which adds the expression properties to the select clause and in the where clause queries for the inserted/updated object only (where id=:id)
• The query will return an array of objects each matching the expression property. The objects should be added to the json returned to the client using the names of the expression properties.

Field generation

The new expression properties should be generated as display only fields (type is OBStaticTextItem). This change is needed in the templates and related components in the org.openbravo.client.application.templates/windows packages.

Client side changes

• In the js file ob-standard-view-datasource.js the expression properties should be picked up and added as extra request params (in the same way as currently the _extraProperties request param is constructed). The extra request params should contain the hql expressions and their names.

Planning

Development

• Changes to ad_field and its window: 4 hours
• Server side changes: 24 hours
• Client side changes: 4 hours
• Documentation: 2 hours
• Testing: 4 hours

Tracking

This feature development is tracked using the following issue(s): to-be-added

Implementation

Documentation

The expression field should be illustrated using a howto showing an example of a display only expression field.

Resources

• Hibernate Subqueries
• CanvasField
• PropertyField