How to create a Computed Column
In MP13 the new concept: computed column, was introduced. A computed column is a column of a table which is computed using a sql expression and which does not exist explicitly in the database schema.
Computed columns have these characteristics:
- computed when a record is read from the database
- not persisted, there is no real column in the database schema for a computed column
- is computed using a sql expression which can use columns from the application dictionary table definition in which the column is defined
- can be used in the definition of a field (in the same way as a 'normal' column), is sortable and filterable but not editable
This howto is supported by an example module which shows example of the code shown and discussed in this howto.
The code of the example module can be downloaded from this mercurial repository: https://code.openbravo.com/erp/mods/org.openbravo.client.application.examples/
The example module is available through the Central Repository (See 'Client Application Examples'), for more information see the Examples Client Application project page.
Defining a computed column
Defining a computed column is quite easy. Goto the 'Tables and Columns' window to the table in which you want to add the computed column.
Then in the column child tab create a new column and set the sqllogic field.
The sqlLogic field must be a sql expression:
- If it is a select sql expression then the tables in the from clause all need to use an alias.
- you can refer to other columns of the table using their non-aliased name
This is an example of the total line quantity column shown above:
(SELECT sum(ol.qtyOrdered) FROM c_orderline AS ol WHERE ol.c_order_id=c_order_id)
What you can see is that the from clause uses an aliased table, the c_order_id at the end is the non-aliased column of the main table.
- a computed column can be used in the definition of a field for an Openbravo column, just like every other column.
- a computed column that uses a String reference should have a length greater than zero. Because this is the number of characters which are going to be displayed in form view.
- the field is always read-only, it is recomputed/set automatically when updating or inserting a record.
- filtering and sorting on computed columns/fields is possible. Note: see the performance section at the end of this wiki document.
Use case: show totals on a header
In the 'Defining a computed column' section an example sql expression was shown. The above example computes the sum of the quantities of a line. You can add a computed column as a field to a tab/window:
and display it in the grid:
or the form:
Computed columns have an impact on the performance of the queries used to load data in the grid. The following 2 aspects should be taken into account:
- the sql expression is used in the select clause of the underlying sql query, but when sorting or filtering the sql expression is repeated (so executed multiple times) in the where and/or or order by clause.
- Postgres and possibly also Oracle will execute the sql expression before limiting the sql result set to the page requested by the grid. So for example if your table has 1000 records and the grid reads them by 100, then the sql expression is executed for all 1000 records before the database limits the results to the page of 100. This is illustrated in the sql execution order screenshot below:
Because of the performance implications described in the last section, starting from 3.0MP27 computed columns are lazily evaluated. This means their query is not executed when the entity they are defined in is retrieved but when one of them is accessed.
For example, Delivery Status is a computed column of Order entity:
// load one order OBCriteria<Order> qOrder = OBDal.getInstance().createCriteria(Order.class); qOrder.setMaxResults(1); Order order = qOrder.list().get(0);
The code above loads an order, at this stage Delivery Status is not already computed meaning the SQL has not been executed in database.
If afterwards we have this code:
// load computed columns System.out.println(order.getDeliveryStatus());
is at this point when the computed column query is executed in database and Delivery Status property takes value.
Note that in case there are many computed columns in the same entity, they are evaluated all together when the first one is calculated.
In order to make computed columns lazy, they are mapped in their entity as a
many-to-one property (named
_computedColumns) linked to a virtual entity, is this virtual entity where the actual computed columns are located as hibernate formula properties.
This extra complexity is transparent when working with generated DAL Java classes, as seen before,
order.getDeliveryStatus() populates the value of the computed column, though behind the scenes it is retrieving it from the virtual entity.
But this model imposes also some limitations to be taken into account when manipulating computed columns in filters or sorting.
When working with HQL (i.e.
OBQuery) computed columns cannot be directly accessed as they are not properties of the entity.
So this valid query in versions prior to MP27:
-- NOTE: this code is not valid after 3.0MP027 FROM ORDER o WHERE o.deliveryStatus = 100
doesn't work anymore starting from MP27, its equivalent would be:
FROM ORDER o WHERE o._computedColumns.deliveryStatus = 100