Openbravo ERP HQL prototyping: Hibernate Tools

Note: In Openbravo 3 we upgraded Hibernate library to 3.6.x version. It seems this new version of Hibernate is not already supported by Hibernate Tools Eclipse’s plugin. So till they support it, Openbravo developers will not be able to use it ūüė¶

 

With the inclussion of DAL in 2.50, Openbravo ERP provides a hibernate layer for accessing database. Complex queries can be executed in database using HQL. The purpose of this post is to explain how to configure Hibernate Tools to help to write HQL queries for Openbravo model.

Why

Before DAL, Openbravo’s database was always accessed using sqlc; this consists on writing SQL statements in a XML file that is transformed into a Java class to execute these queries exactly as they were written. Queries were typically prototyped in a database client (such as pgAdmin or SQL Developer) before moving them to XSQL files. These clients offer some advantages such as syntax checking, code completion and query execution which allows you to see whether the query works as you expected.

Now, with DAL there are other ways of prototyping.

HQL Query Tool is an Openbravo ERP module which facilitates this task using DAL. It is a nice tool where you can prototype HQL queries, execute them to see the result and see the properties each entity in Openbravo ERP has.

Hibernate Tools is an Eclipse plugin which can be easily configured to work together with Openbravo ERP. In addition to the advantages HQL Query Tool module has, this plugin offers code completion, which is really great. It also shows while you are typing your HQL query, the SQL that Hibernate will execute.

Hibernate Tools installation

The installation is really simple:

  1. Go to Help > Install New Software… > Add… and include this update site http://download.jboss.org/jbosstools/updates/stable.
  2. Select Hibernate tools and install it.

You can read additional information in the Hibernate Tools Reference Guide.

Configuration

Now it’s time to configure it to work with Openbravo ERP.

  1. Edit Openbravo.properties file and set hibernate.hbm.file property. This will generate the mapping file, if it is not set it is generated in memory but not persisted. You must set here a file within your Openbravo Eclipse’s project, for example path_to_your_OB_sources/config/ob.hbm.xml. Compile the application (ant smartbuild) or copy Openbravo.properties to WebContent/WEB-INF.
  2. Start Openbravo ERP, this will generate the file set in the previous step. Refresh project if you don’t have auto refresh set.
  3. Configure hibernate.cfg.xml file. There is a template for this file in config/eclipse directory, just copy it and edit to set your database properties. Note for Oracle users: hibernate.default_schema property must be set like your database username in upper case.
  4. Open Hibernate Perspective. Window > Open Perspective > Other… > Hibernate.
  5. In Hibernate Config view click on Add Configurationbutton. Complete the following parameters:
    • Project: openbravo
    • Configuration file: select your hibernate.cfg.xml file.
    • In Mappings tab add your hbm file (the one configured in step 1).

Prototyping HQL

Now you can start prototyping HQL. Click on the Open HQL Editor button in the Hibernate Config view and a HQL editor form will be open, there you can type your query with code completion, execute it to view the results (Hibernate Query Result view), view the generated SQL (Hibernate Dynamic SQL Preview view), see all the entities with their properties (Hibernate Config view, Configuration folder), etc.

 


Base References in Modules

Openbravo ERP 2.50MP12 is about to be released, one of the new cool features it includes is the capability of defining new Application Dictionary Base References within modules.

Application Dictionary References is the way Openbravo ERP defines data type of columns and how is rendered the UI for them. Before 2.50MP12, modules were not allowed to add base references, they only could use the ones provided by core and create new sub-references for these ones. This means that the UI and behavior for all fields in generated windows was set by the references in core, being impossible to define new ones in modules implementing different UI and behavior. The only way to perform all of this was creating manual windows where you are free to implement whatever you need, but as counterpart you loose the maintainability of having windows defined in Application Dictionary.

This limitation has now been broken. You can create base references in your module, which will allow to integrate within generated windows elements having new UI and behaving in a different way than the ones predefined in core.

UI selector

As a first example of how this new capability can improve Openbravo ERP usability you can take a look at the new UI Selector, which through a module (available as commercial) implements a completely new UI for selectors. Being also the first module that integrates SmartClient within generated windows.

Single Record UI pattern

Single record UI pattern has been recently added to Openbravo ERP r2.50 development branch (pi). This new pattern allows to define a tab as update only not allowing new record insertions nor deletions. This is useful for some special tabs that show information which the user can edit but not modify or create new records, usually the number of record is fixed or it is created by a process but not directly in the tab. An example for this is the System Information tab, which shows global information for the system, therefore it makes sense to change some of its values but never to create a new record; a different case where this pattern is now used is in the Business Partner > Customer sub tab, when the Business Partner is a customer this tab contains the information related to this customer, records in this tab shouldn’t be directly created because they must be created in the parent one.

To implement this pattern a new concept has been added to the tab definition: UI Pattern. Each tab has a single UI pattern that can be selected among the values in a new drop down list.

Currently there are three patterns:

  • Standard. It is the usual representation that allows creating, modifying and deleting records.
  • Read Only. It only allows to view the information but not to modify it. This pattern is not new in Openbravo ERP, but it has changed the way to define it. Previously there was a boolean value that set the tab to be read only, now that field has been deprecated and it is defined as a pattern in a new drop down list of values.
  • Single Record.Which is the one described in this post.

Update: This feature will be included within Openbravo ERP 2.50 MP3

New document: database model

Currently we are working on the Developers Guide for Openbravo ERP r2.50. Within these efforts, last week we have published the database model documentation. This documentation is automatically generated from the application’s on line help, and consists in the description for all tables in database and their columns. It is structured in Database_Model/Package/Table, so each table is included in its package chapter, the information for the columns in the table is included as sections in the table’s page.

The aim for all this, is not to have a readable document from head to bottom but to have a easily maintainable reference to be linked from any other document in the wiki, our goal is also to improve on line help and thus, enhance in parallel this document.

If you have any comment on this document: what you like/dislike, what would you include or improve, etc. please let me know and we’ll try to add this feedback.

EDITED on 4-7: This document replaces the Openbravo ERP ER published for previous releases.

New build process: keep it simple

(Old) Problems

For Openbravo ERP developers (specially for newcomers) it has always been difficult to decide which of the available build tasks was the best election to build the system after any development they had done. We had to take into account what modifications were done to know which ant task we should run. For example if we had modified a window we would use ant compile -Dtab=myWindow to generate the code just for that window and not for the rest of them.¬† It was even worse when working with subversion, each time we updated our working copy we had to look which were the files that had been updated to know if it was necessary to run update.database to synchronize Openbravo model database(database schema objects and applicatoin dictionary data) from XML files. And in case there were modifications there it was worth re-generate all the WAD windows because it was difficult to know which ones had been modified. So many times compile.complete was the “safe” but slow choice.

Upadate.database task had two more inconvenients: the first one was that in case Openbravo model was modified locally and not exported (ant export.database), when executing this task we would lose all the changes done in database for application dictionary. This annoying behavior had been reported as a bug. The other inconvenient was that during the r2.50 development cycle, specially because of the usage of DAL as part of the update process, this task was pretty unstable, making people not to be very confident about using it. As result people felt safer by recreating the whole system  (ant install.source) instead of executing a much faster incremental build (ant update.database compile.complete).

New task: smartbuild

For Openbravo ERP r2.50 we have resolved these problems by simplifying the build process with a new incremental build task: smartbuild, which is currently available in trunk (r12753) and will be release in the next alpha (aplha r11). This task performs all the required processes to build your  system but only the required ones, with a huge improvement in performance. It checks whether the database needs to be updated from xml sources and performs the update only if needed, generates the code that needs to be regenerated, compiles and deploys it.

The goal of this smartbuild is to replace most of the rest of tasks, making life a little simpler for developers. So now it is only needed to use two tasks: smartbuild for all the builds and export.database to export database to xml files. export.database is now smart to export only if needed, skipping the process if no changes have happened in the local Openbravo model.

Moreover update.database ensures before updating that no local changes have occured in Openbravo model since the last synchronization (export.database or update.database) to prevent people loosing their changes. In case of changes, people will be required to export their database before updating it.

How it works

  • Determine if database needs to be updated. To do this smartbuild generates a checksum for the xml files and compares it with an existent one. This one is generated each time database is synchronized from xml files or to xml files. If the two checksums are different it means that xml files are different so database is updated.
  • Decide which code needs to be re-generated. Whenever a build process is done a timestamp with the current time is stored in database. This timestamp is compared with the audit info for the application dictionary objects that participate in the code generation so now WAD is able to generate code only for those elements that have been created or modified after the last build. Additionally when exporting database to xml files the audit info is not longer exported and when updating the audit info is recalculated for the current time, thus it also works in case the modifications in application dictionary came from an update.database. There’s only one case when this check doesn’t work: it is when application dictionary elements are modified directly in databse through insert/update SQL statements without updating audit info. In this case the developer will have to generate the code in the old way (using compile -Dtab=modifiedWindows).
  • Check if database has been changed. This check allows to export only if there’re changes in database and prevents data loses when updating database. To check this it is used the same timestamp as in the first point. Modifications in data are calculeted by DAL and modifications in database structure are queried directly to database. The query for database structure last modification has no problem in Oracle becuase User_Objects table stores the last physical change for each database object, but in PostgreSQL that information is not stored in database. This has been solved for PostgreSQL generating a checksum in database from all the elements in database that can be exported to xml files, that’s the reason why in PostgreSQL this check takes longer than in Oracle.

New stuff for Openbravo ERP developers

Recently modularity project has been merged back to Openbravo ERP 2.50 trunk. Apart from the features described within the project, some other useful utilities have been developed, these features are usable from r2.50 by any development. Some of them are:

GenericTree

GenericTree

GenericTree allows to represent an ajax tree for any tree data structure in Openbravo ERP. It draws the user interface for the tree and manages the ajax calls for opening and closing nodes.

GenericTree is an abstract Java class that can be extended by other classes to implement different trees. These subclasses just need to populate a FieldProvider object with the information for the concrete tree. Once this is done in order to show an ajax tree it is only needed to instantiate a subclass.

Let’s see some code:

This is the Java piece of code

ModuleTree tree = new ModuleTree(this);
tree.setLanguage(vars.getLanguage());
//Obtains a tree for the installed modules
xmlDocument.setParameter("moduleTree", tree.toHtml());
//Obtains a box for display the modules descriptions
xmlDocument.setParameter("moduleTreeDescription", tree.descriptionToHtml());

And here is the HTML side


<tr>
<PARAMETER_TMP id="
moduleTree"/> <!-- Prints module tree 4 cols -->
<td/>
<td/>
<tr>

<tr>
<PARAMETER_TMP id="moduleTreeDescription"/> <!-- Prints module tree desc  4 cols -->
<td/>
<td/>
<tr>

In this example ModuleTree class is a GenericTree’s subclass which implements the queries for the tree of modules, creating a new instance of this class and setting the toHTML() in the HTML template will display the User Interface and manage all the ajax requests.

FieldProviderFactory

This class allows to transform any object with setter methods into a FieldProvider object. This is useful to represent this non-FieldProvider object within a structure inside a xmlEngine template.

Here’s the code:


WebServiceImplServiceLocator loc = new WebServiceImplServiceLocator();
WebServiceImpl ws = (WebServiceImpl) loc.getWebService();
Module module = ws.moduleDetail(recordId);
ModuleDependency[] dependencies = module.getDependencies();
xmlDocument.setData("dependencies", FieldProviderFactory.getFieldProviderArray(dependencies));

In this example ModuleDependency is a class that has setter methods, so to use it to fill a HTML template it is possible to convert it into a FieldProvider using FieldProviderFactory class.

AntExecutor

AntExecutor is able to execute any ant task from any build.xml file. It is also possible to set different loggers, for example a file log or an OBPrintStream which can be used to display the generated log in real time.

This is a basic example that just creates a new AntExecutor for a buil.xml file, adds a task and a property and executes the task.


AntExecutor ant=new AntExecutor("/path/to/build.xml");
Vector tasks = new Vector();
tasks.add("apply.modules");
ant.setProperty("module", "test1");
ant.runTask(tasks);

Zip

Zip class zips and unzips files.

It is easy to use:


Zip.zip("/path/to/zip/", "/file/to.zip");
Zip.unzip("/file/to/un.zip", "/path/to/unzip");