View source | View content page | Page history | Printable version   

Projects:Data Access Layer/Technical Design - Prototype - Project Plan



The Openbravo Data Access Layer (DAL) provides functionality to automatically generate hibernate mapping and database schemes from the Application Dictionary. At runtime the Openbravo DAL provides a service layer which is used to retrieve, update and create business objects. The DAL takes care of standard security concepts (like organization/client filtering) and standard update actions. The DAL also contains an automatic thread-based session and transaction handling system.


Model Driven Software Development - Domain Model Definition

The Openbravo DAL is based on model driven software development concepts. The domain model is defined in Openbravo ERP itself and is the basis for generating different software artifacts.

Application Dictionary

The domain model is defined in the application dictionary. The application dictionary defines the tables and columns. In addition the application dictionary will define the mapping from the table and columns to java classes and fields. This mapping is used at runtime to generate hibernate mappings and to instantiate the correct java object (pojo) when retrieving information from the database.

Changes to Application Dictionary: to support the new DAL the application dictionary has to be adapted:

At runtime the application dictionary is used to create an in-memory runtime model. This runtime model is used by the system to support dynamic models and for application tasks such as security and data import and export.

Dynamic Domain Model - Dynamic entities

The Openbravo DAL will allow the domain model to be changed at runtime. This means that at runtime the database schema and hibernate mapping needs to be re-generated. To support a dynamic domain model the system should at runtime be able to extend the existing java entity classes with dynamic features/attributes. In addition it should be possible to add new tables at runtime with the mapping from the table to a dynamic business object.

The support for dynamic models will be implemented as follows:

The dynamic structure is type-checked at runtime using the in-memory domain model. The hibernate mapping generation uses the column information to map the dynamic members in a different way than the static members.

The above implementation choices means that the DAL can consist of a mix of statically created java entity classes and dynamic generic objects. Statically (development-time) create java classes are less flexible than dynamic business objects. However, the main reason for using statically created java entity classes is that IDE support (e.g. code completion, refactoring) for statically created java classes is far better. Resulting in faster development and higher code quality. To combine flexibility and development efficiency, the DAL will support all variants from completely statically created java entities to completely dynamic business objects.

See prototype details below for an example on a dynamically mapped entity (the unit of measure).

Extendability: factory approach

The DAL should be developed with extendability in mind.

The proposed changes to the application dictionary introduce runtime extendability/adaptability. The application dictionary defines which java class is used for each table. This information can be changed at runtime to use a custom java class with custom behavior.

The DAL development can be used to introduce factory-based instantiation in other parts of the system. This makes it possible to extend/adapt the internals of Openbravo ERP. From an implementation perspective there are two choices:

There are pros and cons for either of the two: using Spring/Guice is more standard but can be considered more 'heavy' from a configuration standpoint, using a custom approach is proprietary but probably more light-weight.

Generated Artifacts

The domain model definition in the application dictionary can be used to generate the following software artifacts:

Database schema generation

The database schema generation is currently available but needs to be adapted:

Hibernate Mapping generation

The application dictionary is the basis for the hibernate mapping. The application dictionary contains the mapping from the relational schema to the java classes/members. This information can be used directly to generate the hibernate mapping. The system will work with an in-memory hibernate mapping which is generated when the application initializes or when the DAL is re-configured (see next topic).

(Initial) Generation of Static Java Entity Classes

Openbravo ERP currently has about 425 tables. These tables translate to about 425 Java entity classes. Instead of manually creating these classes it can make sense to generate the initial set of Java entity classes. The next step is to make this generation step a core-function in the Openbravo ERP development environment. This can facilitate larger development efforts.

If a generative approach is chosen for the entity pojos then the proposal is to use the XPand templating language for java code generation. XPand is part of the Eclipse Model-To-Text project and is very popular. For more information see:

Dynamic Regeneration

The system should support update of the database schema and hibernate mapping at runtime. There should be a separate 'refresh/reconfigure' option which the administrator can run. This action should do the following:

Dynamic update of the database schema and in-memory hibernate mapping is straightforward to develop. There is however one topic which requires extra attention, nl. a database schema update normally/often requires a database lock. So during a database schema update it's not really possible for other users/programs to access the system. The system should support a blocking mechanism to temporarily block access to the system until a database schema update has been performed.

DAL Service Layer

The Openbravo ERP DAL will offer its functionality through a service layer. The service layer hides the internals of the DAL for (application) users of the DAL. The service layer will offer the following functionality:

The filter mechanism should specifically support filtering on:

Openbravo Context: OBContext

The DAL requires a context concept to be aware of the environment it currently runs in. This context concept is called the OBContext.

The OBContext contains the following information:

The OBContext is stored in a ThreadLocal during the thread, and between threads it's stored in a http session variable.

When a request starts the OBContext is retrieved from the http session. If it's not there then a new OBContext is created. The next step is that the information in the http session is used to set the information in the OBContext. This is done for every request, only the changed information is updated.

After the OBContext has been initialized it is placed in a ThreadLocal.

The application code can always retrieve the OBContext by calling the method OBContext.getOBContext().

Session/transaction handling

The DAL uses the open session in view pattern. This pattern is implemented using a servlet filter and a session handler class:

As mentioned above, the current implementation performs the transaction commit at the end of the request.

Hibernate performs many actions at commit time (for example flush SQL statements to the database). This means that in a late stage of the request/response cycle, errors can still occur. A better concept is to use two transactions within one request/response cycle (this approach is implemented by Seam). This approach can be considered in a future version when also the UI layer of Openbravo ERP is redesigned.

Model: Common Object Features/Class Hierarchy

The DAL has a Java pojo for each business object in the Openbravo ERP application. A business object corresponds to a table in the current Openbravo ERP system. Many Openbravo ERP business objects have a number of features in common:

The idea is to design a class hierarchy which implements part of the features at a higher level than the direct Java entity. The class hierarchy should take care of the implementation while the actual definition of the concept is done through interfaces. For example the following interfaces can be envisioned:

The 'outside world' should use the interfaces and not the class hierarchy itself.

A first proposal for a class hierarchy (implemented in the prototype):

The Java entities themselves should be organised in packages which corresponds to the current table naming/division. The prototype follows this approach:


The DAL should support standard security checking (for example client/organisation) for update, removal and retrieving of information. The security logic can be implemented in two distinct locations:

The second choice has as advantage that it is ensured that all updates through hibernate are security checked. On the other hand the disadvantage is that there is certain system information which is updated as part of a user action (for example update log or audit table). Directly updating this information by the user is not allowed. For the save/update event listeners it is not possible to distinguish between a direct user update of information (the user directed the system to update an object in the db) or an update which is driven by the system itself (as the result of a user update of information).

Performing security checks in the DAL service layer has as advantage that the security is checked on the objects which the user wants to update directly. So the security check is done in the layer which knows the action taken by the user. On the other hand the system is 'less' secure because it is possible to update information through hibernate even when the user does not have enough authorisation. For example Hibernate will automatically update related objects if an object is saved. A security check in the DAL layer can not catch/check/prevent the update of these related object.

The proposal is to support read security checking in the DAL service layer and write security checking and validation in the database/hibernate layer (using interceptors). The rest of the application should not be worried about security. In addition the cascade update behavior described in the previous paragraph is less relevant for Openbravo ER because most (if not all) relations are many-to-one relations for which update-cascades should be disabled anyway.

Database Triggers

The functionality currently implemented in database triggers will over time be moved to Java. During the transition period from trigger to Java the DAL needs to operate correctly with database triggers and support them. There are two different types of database trigger functionalities:

  1. a database trigger which change the information of the saved/updated object itself
  2. a database trigger which changes other information in the database

Case 1 can be handled by refreshing an object after it has been saved. This can be implemented in the combination of the DAL and an event listener. To tag which objects need to be refreshed the proposal is to add a new interface DBTriggered or to maintain a hardcoded list of triggered types in-memory.

Case 2 does not require extra actions, the testcases show that triggers are executed when an object is saved.

Database Independence

The generated mapping, the DAL and the business logic in the DAL should be completely database independent. Database specifics should be handled through configuration of Hibernate using Hibernate properties.

The hibernate prototype works for both Postgresql and Oracle. The Hibernate Oracle dialect class has been adapted to solve a small number of mapping errors. For Postgresql no special Dialect class is required.


The DAL should be testable directly and separately from the main Openbravo ERP application using the DAL service layer. The DAL testcases are implemented in a separate development: OpenbravoTest.

The OBContext and transaction/session handling implementation facilitates testing by making it easier to setup a environment in which to run the testcases. The OBContext completely defines the environment of the DAL and the session/transaction handling is transparent for a testrun.

Data Access Layer Hibernate Prototype Specifics

Prototype Software Package Structure

The data access layer (DAL) is implemented in org.openbravo.dal package in the main Openbravo ERP development project (inside the src source directory). The DAL consists of a number of subpackages:

DAL Service Layer: filtering, querying, paging and sorting

The DAL service layer is implemented in the org.openbravo.dal.service package. It consists of three classes:

The OBDal layer internally passes the OBFilter instance to the OBFilterQuery class to create a filtered query. The OBDal layer will automatically also add organisation and client filtering (for security reasons).

Dynamic Business Object

The prototype contains an example of a dynamic business object, see the unit of measure mapping in the org.openbravo.dal.model.c package. There is no UOM Java class but the unit of measure is still mapped. The UOM is read from the database as a CommonOBObject. The CommonOBObject has a data member (a hashmap) which contains the data of the unit of measure. The DAL test case contains an example of reading dynamic entities.


The DalThreadHandler and DalRequestFilter classes are implemented in the org.openbravo.dal.core packages. These classes ensure that when a thread runs inside the Openbravo ERP business layer it has always an Openbravo context object available and the thread has access to a Hibernate Session.

Threadcontext Pattern

The DAL makes use of ThreadLocals to keep state throughout the lifetime of a thread. The thread concept makes use of the ThreadHandler class (see the org.openbravo.dal.core package in the prototype). This class implements a common pattern to run the actual logic within a try/finally block and perform initialize and clean up actions when the thread returns.

In a servlet environment it's particularly important to clean up the thread context at the end because threads are re-used by the Tomcat servlet container.

ORM Topics: Mapping, identifier


The business objects are mapped to Hibernate using a separate mapping file per type. The mapping file can be found in the same class as the business objects. Although the business objects are modeled in a class hierarchy, it is not possible to follow the same structure in the mapping file. This is because the id is stored in a different column for each table, for example the id of the currency is stored in the currency_id column, the id of the user is stored in the user_id field. If all types would have used the same column name then it would have been possible to map certain properties at superclass level.

A few specific remarks regarding the mapping:

Identifier Creation

Openbravo ERP controls the id-ranges of the instances of business objects through the ad_sequence table. This table stores, for each type, the next available id. When a new instance is created then the id is retrieved from this table and the ad_sequence table is updated. Before this logic was implemented in a stored procedure.

When using Hibernate this logic has to be implemented in a separate IdentifierGenerator class. The DAL implements this in the OBIdentifierGenerator class (org.openbravo.dal.core). The id retrieval and update is done in a separate transaction. So no stored procedure is used. The OBIdentifierGenerator is used in the mapping of each business object.

Initialization and Configuration (Second-Level caching)

Hibernate is initialized when the DalRequestFilter object is initialized. Hibernate reads the in the root of the classpath (during development). In a later version the will be placed in the WEB-INF directory. The Hibernate SessionFactory is managed by the SessionFactoryController (org.openbravo.dal.core) class. The classes which are persisted through Hibernate are added to Hibernate in this class.

The DAL will use ehcache as the second level cache provider. The configuration of the second level cache is done through the ehcache.xml file in the root of the classpath (see the src directory in the Openbravo ERP project).

Update/Save Event Handling

Hibernate makes it possible to add event handling logic when an object is saved, loaded or deleted. The DAL adds specific behavior at save (=new object) and update (=existing object). This behavior is implemented in the OBEventListener class (org.openbravo.dal.core). This class implements the following behavior:

Process versus View Objects

The DAL requirements document makes a distinction between process and view objects. Process objects only contain primitive data (string, integer, etc.) and no references to other types. View objects on the other hand have both the primitive data and have resolved references to other types. The idea behind the distinction between the process and view object is that retrieving process objects is faster because no references to other types need to be resolved (resulting in less joins and extra queries).

Hibernate in combination with lazy-loaded proxies makes it possible to remove the distinction between a process and a view object. When an object is retrieved from the database then its references are by default not resolved (it is basically a process object). When its references are accessed then Hibernate will automatically resolve them (resulting in extra queries or cache hits), supporting the view object requirement.

Validation and Security

The Data Access Layer should offer specific functionality for supporting validation and security as it is defined in the application dictionary.

The topics marked with TBD have not yet been implemented. The other topics have been implemented.


The application dictionary defines validation rules on column and table level and in separate tables.

Defined on column level:

Defined on table level:

Define allowed values for fixed value lists:


Security can be divided into two levels of definition: data access privileges and client/organization validation and filtering.

Data Access Privileges

Data access privileges is related to the permissions assigned to roles for reading and writing -including insert, update and delete- in a table. Currently it's described by the information in the AD_Window_Access and AD_Table_Access tables. For a role to be able to read/write in a table it's required to have read/write privileges in a window (AD_Window_Access) that includes a tab linked to that table and not to have an excluding rule to read or write in that table (AD_Table_Access).

Client/Organization validation and filtering

Client/Organization validation and filtering is related to the way that Openbravo ERP defines multi-client and multi-organization operation. An Openbravo ERP instance can run multiple clients. Activity (and information) in a client is fully isolated from activity (and information) in other clients. The only exception happens for information assigned to a special client (System) that stores information for the whole system and that all clients in the Openbravo ERP instance can use. Within a client there could be multiple organizations defined in a hierarchy. There is one special organization (*) which is the parent node of the organizations hierarchy in a client. It stores information to be shared between all organizations in the client. Further details on multi-client and multi-organization.

Client/Organization validation can be split into two groups of rules: data model validation -independent of the role who performs the action- and role privileges regarding client/organization access.

Data Model Validation

All objects in an Openbravo ERP database are assigned to a client and to an organization. Tables in an Openbravo ERP database can have different table access level:

DAL should guarantee that the client and organization assigned to an object matches these rules summarized in the next table:

Client Org
System 0 *
System/Client Any *
Organization No 0 No *
Client/Organization No 0 Any
All Any Any

Also DAL should guarantee that an object may only refer to another object if the other object is in the same client as the object itself (or client System) and in an organization in the natural organization tree as the object itself. If an object belongs to client X then it may only refer to objects which are in client X or client System. If an object belongs to organisation A then it may only refer to objects which are equal to organisation A or belong to a parent or child of A.

Client/Organization role privileges

Roles are assigned to one client and to one or many organizations in that client through the AD_Role_Org_Access.

To enforce the defined multi-client and multi-organization behaviour DAL should guarantee this rules:

Derived Read Access

When considering read access there are two modes which apply. The first mode is that a user has direct read access to a specific type. This is defined by the window_access and table_access discussed above. Then there is a second type of read-access allowed: for each entity type referenced by a directly readable entity the user is allowed to see the identifier properties and id. This is called derived read access.

For example assume that a user has read access to the Invoice entity type and assume that an Invoice can reference the currency. Then the user is allowed to view the id and identifier of the currency type for all currencies belonging to the readable organisations. Other properties (than the identifier ones) are not visible to the user.

Technical Implementation

The validation and security checking is done before an object is saved in the database by hibernate. Security checking and validation will be implemented in separate objects. Each Entity (=table) in the in-memory model will have a Entity Validator and Security Checker attached to it. Each Entity validator will consist of one or more column validators. The Data Access Layer uses the Entity/Column validators and security checker to check if an object may be created/updated/deleted.

The validator/security checker are to be developed such that the performance is optimized, also for cases when no validation/security checking is required.


The default value defined for a column is not implemented in the DAL for the 2.50 release. This is because the expressions used currently in the defaultvalue definition are not suited for the data access layer. The same for readonlylogic on column level and ad_val_rule.

The following validation will be handled in a future release:

In a future release a new expression language will be designed which can be used to specify default values and other expression content (readonly, etc.).

The callout feature of a column is handled in a later release.

In a later release a new security system to manage access/permissions per business object will be designed. AD_Window_Access should be replaced by AD_Business_Object_Access and security should be extended with capabilities to define privileges at column level, row level and a combination of both. Row level security will be based on pre-defined filters that can use context parameters.

Future Topics

Handling the Identifier

Each entity has an identifier which is used to display the object in lists and comboboxes. The identifier of an entity is defined by the columns in its table definition (in the application dictionary) which are flagged as being part of the identifier.

In the new DAL approach with Java pojos as business objects the proposal is to compute the identifier of an entity at runtime using the in-memory runtime model. The runtime model, for each type, contains the list of identifier columns and their related Java member. This information can be used to compute the identifier at runtime. The exact implementation should include some pre-computed IdentifierProvider instances, one for each type. The identifier generation should also handle translation of data.

Translatable Data

Some of the tables in Openbravo ERP have a separate translation table which holds the translated content for several languages. For example the ad_country table has a ad_country_trl table which holds the translated country names. The translated information is used in various locations (to fill listboxes for example).

The DAL should provide a standardized way to retrieve the translated version of a translatable object. There are different options.

Using the database directly with second-level cache

The current idea is to introduce a separate interface translatable with a method: getTranslated() which returns the translated version of the object (using the current user's language). The translatable business objects (such as currency and country) should implement this method to return a translated version of themselves. The Translatable interface can be used by business functionality to determine if an object is translatable and use the translated version if required.

The getTranslated method will use Hibernate to retrieve the translated version. For performance reasons the second level cache should be initialized for these types of objects. As translatable objects will not change that much it can be safely assumed that they will be present in the second-level cache and remain there.

Using in-memory structure

The translated data is probably fairly small (unit of measure data, country descriptions etc.) and fairly static. So it can be a choice to cache the translated data in an in-memory cache. When the underlying data is changed then the cache is refreshed. This can be implemented using standard hibernate event listeners.

CRUD Webservice

The DAL service layer will also be made available as a webservice to the outside world. The main topic in the webservice layer is the (de-)serialization of business objects to and from xml.

The (de-)serialization logic should an xml message to and from an object graph containing business objects and their references. The transformation process should be able to instantiate business objects defined in the xml itself as well as resolve references to business objects outside of the scope of the xml message itself. The runtime model is of great use in supporting automatic conversion of business objects from and to xml. The entity and columnnames can be used directly in the xml message. Also knowledge about which tags represent references and which represent a specific primitive type can be computed using the application dictionary.

After an incoming message has been converted into an object tree then the next step is to match the object tree with the database. An object graph can consist of objects which already exist in the database (need to be updated) and of objects which need to be re-created. Hibernate provides excellent support for handling these situations (by supporting transparently save/update of objects).

For an outgoing message it is important to define the scope of which part of the object graph needs to be serialized to xml. The references to outside objects need to be serialized such that they can be handled when the message is returned with updated information.

Project Plan: main development steps

The main development topics and their logical ordering. The following remarks apply:

This estimate needs to be reviewed and maybe re-formulated to adhere to Openbravo ERP project plan formats.

Incorporate Hibernate dependencies and setup in build infrastructure

In the prototype the hibernate library and its dependencies has been added manually to the project. In addition small changes were made to web.xml (configuration file). These changes need to be incorporated into the standard Openbravo ERP build infrastructure.

Estimate: 3d

Add new fields to application dictionary

The first step in the development is to extend the current application dictionary with a number of fields as outlined above. The fields should be initially filled using table and column names.

The development work consists of:

Estimate: 5d

Implement runtime model

The runtime model is an in-memory representation of the ad_table, ad_column and ad_reference. The runtime model is used to create hibernate mappings and to handle runtime instance to model mapping.

The development work consists of:

Estimate: 5d

Implement hibernate mapping generation

The runtime model is the basis for the hibernate mapping logic. The hibernate mapping is created when Openbravo ERP initializes. The hibernate mapping generation results in an in-memory mapping xml String which is passed to hibernate.

Estimate: 5-7d

Generate Database Schema

Currently in Openbravo ERP the database schema is not directly generated from the application dictionary. The main building blocks are already available but need to be assembled and combined. The result needs to be tested on multiple databases.

Estimate: 5-7d

Design Class Hierarchy for Business Objects

The Java entity classes and the dynamic business objects can benefit from a focused class hierarchy which models specific features at a higher level.

The prototype contains an example class hierarchy. This first proposal probably needs to be re-designed and discussed thoroughly.

Estimate: 2d

Generate Java entity classes

The runtime model can be used to generate an initial set of Java pojo entity classes. The generation logic and templating needs to be developed.

Estimate: 5d

Context and Security

The prototype context concept can probably be used directly. Some development work should probably be done to move it from a prototype to production code level. The same applies to security checking code.

Estimate: 2d

Develop DAL API

The prototype covers a first small set of a DAL API. This API probably has to be extended and redesigned. From a development perspective initially we should start with a simple API and extend it when the need arises for other interfaces.

Estimate: 2d


The DAL should handle translations (the _trl tables) and message translation. This technical design proposes two approaches, one of the approaches has to be selected and implemented.

Estimate: 2d


The DAL service should be made available as a webservice for standard CRUD operations. The main topic in implementing this functionality is parsing xml-data to and from business object trees and then matching the object tree to the database. The runtime model plays an important role in automatic mapping from business objects to and from xml.

Estimate: 5-10d

Dynamic Model

The DAL should also support a dynamic model which is changed (extended) at runtime. This development topic encompasses:

Estimate: 5-10d

Retrieved from ""

This page has been accessed 10,144 times. This page was last modified on 8 June 2012, at 05:27. Content is available under Creative Commons Attribution-ShareAlike 2.5 Spain License.