Understanding OBDal in Openbravo

OBDal is an extensively used class in Openbravo. If offers lots of useful functions to enable external access to the Data Access Layer. OBDal provides the basic functions like insert,save,remove, etc. OBDal forms the base for the Data Access Layer(DAL) in Openbravo.

DAL Architecture

Though there are lots of useful functions in OBDal, we often use only few basic functions like save, remove, etc. Lets see the use of  the methods in OBDal and its usage.

1. void commitAndClose():

This function commits the transaction and closes the transaction. It will not only commit the current transaction but all the pending transactions. However, having more than one open transactions at a time is not advisable. There are few reasons why we should not have more than one open transactions. One of the reason is, if one of the open transactions failed to commit, all the pending transactions will be rolled back. This can be used in the code as follows.

OBDal.getInstance().commitAndClose();

There may be situations where we still need the transaction but just commit it. But when we use this method, it will not only commit but also closes the transaction. In this scenario, we can make use of the class SessionHandler( This class is exclusively for maintaining hibernate session for transactions). This class has a method commitAndStart(). This method will commit the transaction and starts a new session. This can be used in the code as follows.

SessionHandler.getInstance().commitAndStart();

2. void rollbackAndClose()

This method is used to rollback the transaction. Similar to commitAndClose() Method, this method will rollback all the open transactions.

OBDal.getInstance().rollbackAndClose();

The SessionHandler has a method rollback() which can be used when we don’t want to close the session but rollback the transaction.

SessionHandler.getInstance().rollback();

The SessionHandler class has two other methods that enables us to mark the transactions that needs to be rolled back. This can be done using the method, void setDoRollback(boolean). If the argument is true, then the transaction will be marked for rollback and if it is false, the transaction will not be marked for rollback. The other method is boolean getDoRollback() which will return whether the transaction is marked for rollback or not.

 SessionHandler.getInstance().setDoRollback(true);
 SessionHandler.getInstance().getDoRollback();

3. void disableActiveFilter() and void enableActiveFilter()

By default, the active filters will be enabled. Consider the line below.

Client clientList = OBDal.getInstance().get(Client.class,null); //Client is the class which we are referring and null denotes there is no filter

This will return all the records in ad_client  table that are active. We can disable this by using the method disableActiveFilter(). After disabling, the above line will return all the records irrespective of either the record is active or not. enableActiveFilter() is used to enable the filter. This methods can be used as follows.

OBDal.getInstance().disableActiveFilter();
 OBQuery<Client> clientList = OBDal.getInstance().createQuery(Client.class,null);
 for(Client client : clientList.list()) {
 //Processing
 }
 OBDal.getIstance().enableActiveFilter();

4. boolean isActiveFilterEnabled()

This method is used to verify whether the active filter is enabled or not. At any point of time, the active filter can be enabled and disabled and this method is useful to verify the status.

if(OBDal.getInstance().isActiveFilterEnabled()) {
 OBDal.getInstance().disableActiveFilter();
 }

5. boolean exists(String entityName, Object id)

This method is used to check whether a particular record exists in the database.

 OBDal.getInstance().exists(ADClient.ENTITY_NAME,"0");//ADClient is the entity name for Client Class and "0" refers to ad_client_id

To understand the use of this method, consider the code below.

Client clientList = OBDal.getInstance().get(Client.class,"45");
 try {
 String name = clientList.getName();
 } catch(Exception e) {
 log.info(e.getMessage());
 }

In the above code, if the record with ad_client_id = “45” doesn’t exists, it will throw java.lang.NullPointerException since we are trying to access the value(clientList.getName()). To avoid this, we can use exists() method as follows.

Client clientList = OBDal.getInstance().get(Client.class,"45");
 if(clientList.getInstance().exists(ADClient,"45"))  {
 try {
 String name = clientList.getName();
 } catch(Exception e) {
 log.info(e.getMessage());
 }

The above code will be executed only if the record is present in the database.

6. String getReadableClientsInClause() & String getReadableOrganizationsInClause()

This method returns an in-clause string of the clients that are readable by the current user. The In-Clause String looks like (“0″,”1000000”). This method is useful in many scenarios. Below is one such scenario.

While retrieving the records using OBDal, it will automatically take care of the security. However, when we use HQL Queries, we need to take care of the security. In order to retrieve the client and organization readable by the user, we can use this method.

For example,

String hql = "SELECT name  "
 +"FROM ADRole r"
 +"WHERE r.organization IN "+OBDal.getInstance().getReadableOrganizationsInClause()
 +" AND r.client IN "+OBDal.getInstance().getReadableClientsInClause()
 +" AND r.active=true ";
 Query query = OBDal.getInstance().getSession().createQuery(hql);

This query will return the name of the roles that are readable for the current user.

These are few methods that I felt important in OBDal that would ease development using DAL. For details on how to write DAL code, refer ([1] and  [2]).

Resolving runtime model error in Openbravo

Whenever we refer columns in Openbravo using foreign key  references, the Application reference for the same will be  mapped as TableDir by default. This will be set when we provide create columns from DB. This is a small but effective value-add provided by Openbravo for developers. It reduces our work by a fair share. But as with all systems, there is a small downside to it. First let us see how they map the reference ‘TableDir’ to the column. When we run the Create Columns from DB process, the process checks for the column name, if the column name ends with ‘id’, it will map that as ‘TableDir’. For more information on references, refer here.  For eg, if a column name is ad_client_id, it would be mapped to TableDir by default. But the downside is, even if my column is ‘paid’, it sets it as TableDir.

We will not realize this, until we compile. When we compile, we get the following error, “Reference column for paid not found in runtime model“.  To resolve this issue, we have to change the reference type of the Column from TableDir to the corresponding reference (String or Number) and recompile.

One more issue that we face commonly while compiling is, “Referenced property is null for tablename.field“, where tablename is the name of the particular table and the field is the name of the field. The problem with this columns is, in Tables and Columns in Application Dictionary, for this particular column ‘Link to Parent Column’ will be checked. But as in our example, the column paid is a String field and not linked to any table or column that is already present, that is the reason why the error shows, referenced property is null. To resolve this, deselect the ‘Link to parent Column’ check box in the Column Window. I have attached a screen shot below to show where to deselect ‘Link to parent column’ property and also how reference are mapped.

Working with DDL Model in PeopleSoft

PeopleSoft clients that use Oracle as their database, by popular practice, create a database user called SYSADM and nominate SYSADM as the owner of all PeopleSoft objects.

As shown in the picture, when a end-user accesses the PIA, Application server connects to the database using SYSADM is. Likewise, regardless of how a developer is connected to App Designer, either in 3-tier or 2-tier mode, the database connection is always established using SYSADM id.

Most clients do not share the SYSADM credentials to anyone except administrators and they do rightly so as a best practice. Instead, developers are given their own database user ids.

Build Process

In such environments, for a developer to be able to define a record definition in App Designer and access the table in SQL client, these three steps have to be followed:

  1. Define the record in Application Designer (Developer)
  2. Build the record (Administrator)
  3. Grant to developer the access to insert/update/delete/select from the table (Administrator)

Granting access is important, without which, the developer cannot log on to database through a SQL client, (SQL Plus, SQL Developer etc.), and access the table they just created. On describing the table, they would receive a “Table or View does not exist” error.

Administrators follow two methods to grant access:

Method 1. Hand-write the SQL statement to grant access and execute the statement along with the build script.
This method, however, requires a lot of manual effort. Imagine the plight of an administrator when a 100 tables are built.

Method 2. Some others, schedule a script to automatically grant access to all new tables. This method is very efficient, but not necessarily real-time. If the script is scheduled to run every 30 minutes, a developer might have to wait 29 minutes before gaining access to the table.

There’s another method which is both real-time and automatic. The solution lies in the power of DDL Model statements. DDL Model statements are defined at PeopleTools > Utilities > Administration > DDL Model Defaults.

Application designer, when asked to build the script for a table, refers to the DDL Model statement defined for that particular database type – Oracle for the purpose of this blog.

CREATE TABLE [TBNAME] ([TBCOLLIST]) TABLESPACE [TBSPCNAME] STORAGE (INITIAL **INIT** NEXT **NEXT** MAXEXTENTS **MAXEXT** PCTINCREASE **PCT**) PCTFREE **PCTFREE** PCTUSED **PCTUSED**;

[TBNAME] is replaced with the record being built.
[TBSPCNAME] is replaced with the tablespace assigned to the record etc.

To automatically, grant access whenever a table is created, I appended

GRANT SELECT, INSERT, UPDATE, DELETE ON [TBNAME] to PS_DEVELOPER;

PS_DEVELOPER is the database role assigned to all PeopleSoft developers.

With the grant statement included in your DDL, Application designer automatically grants developers with access to any new tables created.

Some known loopholes with this method are:

1. This method doesn’t work when altering tables. Grants will be provided to the temporary table PSYJOB, for example.

2. Grants cannot be provided to different roles. The role is always hard-coded in the DDL Model statement.

Its a nice way to extend the possibilities DDLMODEL though.

Postgresql database configuration for global access

Openbravo ERP supports  couple of database like Oracle and PostgreSql. But my inclination is for PostgreSql database, because it is Opensource, and has almost all features of an Enterprise level RDBMS (Relational Database Management System). For more information on PostgreSql, refer here. For installation of PostgreSql on Ubuntu, refer here and for Windows, you can download from here. An expanded overview is provided here.

Installing PostgreSql is pretty easy and there is tool pgAdmin that will further simplify the use of the database. By default, PostgreSql will be mapped to your localhost, but for accessing it across Systems and for connecting to other PostgreSql database listening across various Systems, couple of configurations are to be done. I have presented the configuration steps in Ubuntu and it is nearly the same in Windows machine also. Just that the folders will be little different.

Step 1 : Modifying pg_hba.conf

  • Type the following commands in the Terminal.

cd /etc/postgresql/8.4/main/

sudo gedit pg_hba.conf

  • This will open the pg_hba.conf configuration file in text editor. The pg_hba.conf file controls which hosts are allowed to connect, how clients are authenticated, which PostgreSql user names they can use, which databases they can access. There will be values like,

HOST       DATABASE  USER  CIDR-ADDRESS  METHOD  [OPTIONS]

  • In the Method, the values will be ident by default. We have to change all ‘ident’ to ‘md5’ because md5 sends encrypted passwords.
  • Under the heading IPV4 local connections, we should add rows for IP values that can be connected to our System. For that we have to provide the IP addresses that we want to connect. By default, the value will be 127.0.0.1/32 which is the localhost. If we provide 0.0.0.0/0 it will be available for connections to all Systems.
  • Though the connections are available for all Systems, you have to map which addresses the database should listen on. This is configured in postgresql.conf. That configuration is presented below.
Step 2: Modifying postgresql.conf
  • Type the following commands in the Terminal.
sudo gedit pg_hba.conf
  • This configuration file is read on server startup and when the server receives a SIGHUP signal.  If you edit the file on a running system, you have to SIGHUP the server for the changes to take effect, or use “pg_ctl reload”.
  • Under Connection Settings, there will be  a value for Listen Address. This provides us the information about what IP address(es) to listen on.
  • By default this value will be, listen_addresses = ‘localhost’, we have to modify it to listen_addresses = ‘*’. There will be  ‘#’ before this line. # character is used for commenting. So for this changes to take place, you have to uncomment it by removing the ‘#’ character.
You have to restart the PostgreSql database for these changes to take effect. Keep me posted here, if you have any doubts in this regard.
Happy Working..:)

Tree Structure for Custom Windows in Openbravo

Generating Tree Structure is a great innovative feature that is present in OpenbravoERP. If you are wondering what it looks like, refer to Organization window in the Client Mode or the Menu Window in System Admin mode. There will be an icon called Tree in the Toolbar. Click on that and you will see something similar to the screen shot below.For more information, refer here.

This functionality will find its use in most places where there is a hierarchy to be shown and segregations are to be visualized. One more appreciable feature is that you can drag and drop the items and rearrange it. Doesn’t it sound really cool to you? I was really excited to test this out for the custom windows and with the help of Wiki, Forge and of course my colleague, I was able to create a tree similar to the one shown above for a custom window called ‘Department’. I have attached a screen shot of my tree below.

This feature is really helpful in lot of cases. I have shared the sequence of steps that I followed that enabled me to create such a structure. We have categorized the steps that we performed into 3 sections:

1. Database changes

2. Changes to be done in the Application

3. Changes to be done in code

1. Database Changes

  • Create a new table for the department. Eg: XYZ_department.

The following are the key fields in the table.

        "value" character varying(40) NOT NULL,
        "name" character varying(60) NOT NULL,
        description character varying(255),
        issummary character(1) NOT NULL DEFAULT 'N'::bpchar,
  •  Create a new table for storing the tree nodes. Eg: XYZ_treenodedp (Structure similar to ad_treenode table)
  • Add logic in the Department Table Trigger to insert values in XYZ_treenodedp and the corresponding delete logic also. Refer Organisation table trigger for more information.
  • Add a new column in ad_clientinfo. Eg: em_XYZ_tree_department

2. Changes to be done in the Application

  • Create Window for the Department. Set the field ‘Tree Included’ to Yes in Fields Tab. Eg: In Department window
  • Import the  corresponding column created in ad_clientinfo table into the Application.
  • Add a new entry in the list reference ad_treetype. Eg: Search key: XYZ_DP Name: Department
  • Create a new entry in Tree and Node Image window both in System administrator and desired client admin. Eg. Name: Primary Department Tree type: Department in System Administrator and Name: F&B International Admin Department Tree Type: Department in F&B International Admin.

  • Update and set the value for the em_XYZ_tree_department field as the client’s tree entry. Eg: In F&B International Admin, Set the value as ‘F&B International Admin Department.

PrimaryDepartment

3. Changes to be done in code

  • In WindowTreeUtility.java, add the following lines in checkSpecificChanges() Method.
else if (TreeType.equals("XYZ_DP")) { //Department
 result = "";
 }

  • In WindowTreeUtility.java, add the following lines in getTreeType() Method.

else if (keyColumnName.equals("XYZ_Department_ID"))
 TreeType = "XYZ_DP";

  •  In WindowTreeUtility.java, add the following lines in getTree() Method.

else if (TreeType.equals("XYZ_DP"))
 data = WindowTreeData.selectDepartment(conn, vars.getUser(), strEditable, strParentID,  strNodeId, TreeID);



else if (TreeType.equals("XYZ_DP"))
 WindowTreeData.updateDP(conn, vars.getUser(), strParentID, strSeqNo, TreeID, strLink);

  •  In WindowTree_data.xsql, add two new sql methods to select and update the tree nodes.

<SqlMethod name="updateDP" type="preparedStatement" return="rowCount">
 <SqlMethodComment></SqlMethodComment>
 <Sql>
 UPDATE XYZ_TREENODEDP SET UPDATED=now(), UPDATEDBY = ?,                 PARENT_id = ?, SEQNO=TO_NUMBER(?)   WHERE AD_TREE_ID = ?
 AND NODE_ID = ?
 </Sql>
 <Parameter name="updatedby"/>
 <Parameter name="parentId"/>
 <Parameter name="seqno"/>
 <Parameter name="adTreeId"/>
 <Parameter name="nodeId"/>
 </SqlMethod>

<SqlMethod name="selectDepartment" type="preparedStatement" return="multiple">
 <SqlMethodComment></SqlMethodComment>
 <Sql>
 SELECT tn.Node_ID,tn.Parent_ID,tn.SeqNo,                             m.XYZ_Department_ID AS ID, m.Name,m.Description,m.IsSummary
 FROM XYZ_TreeNodedp tn,
 XYZ_Department m
 WHERE tn.Node_ID = m.XYZ_Department_ID
 AND tn.AD_Tree_ID = ?
 ORDER BY COALESCE(tn.Parent_ID, '-1'), tn.SeqNo
 </Sql>
 <Parameter name="editable" optional="true" type="none" after="WHERE "                 text="tn.IsActive='Y' AND m.isActive='Y' AND "/>
 <Parameter name="parentId" optional="true" after="WHERE " text="tn.Parent_ID = ?             AND "/>
 <Parameter name="nodeId" optional="true" after="WHERE " text="tn.Node_ID = ?             AND "/>
 <Parameter name="adTreeId"/>
 </SqlMethod>

Traversing the Department Tree – PSTREENODE

In PeopleSoft, it is very common to come across requirements where the program gets a department ID as input and fetches data of that department and its sub-departments. In such scenarios we cannot escape from accessing the department security tree to pull the sub-departments of a department.

SQL Expert Vs SQR Smarty
When we speak of a problem there is always more than one problem solving technique. When I came through this type of requirement for the first time, I was shooting up my SQL skills. Hence I came up with a single select statement that will fetch the sub-departments from PSTREENODE table. Below is the select query that I used in my SQR program.

select t.tree_node from pstreenode t 
where t.setid = 'GBL'               
and t.tree_name = 'DEPT_SECURITY'
and t.tree_node_num >= (select t1.tree_node_num from pstreenode t1 
                                                     where t1.tree_node = <input dept>
                                                     and t1.effdt = t.effdt
                                                     and t1.setid = t.setid
                                                     and t1.setcntrlvalue = t.setcntrlvalue
                                                     and t1.tree_name = t.tree_name
                                                     and t1.tree_branch = t.tree_branch)
and t.tree_node_num_end <= (select t2.tree_node_num_end from pstreenode t2 
                                                       where t2.tree_node = <input dept>
                                                       and t2.effdt = t.effdt
                                                       and t2.setid = t.setid
                                                       and t2.setcntrlvalue = t.setcntrlvalue
                                                       and t2.tree_name = t.tree_name
                                                       and t2.tree_branch = t.tree_branch)
and t.effdt = (select max (t3.effdt) from pstreenode t3
                                                  where t3.effdt<=sysdate 
                                                  and t3.tree_node = t.tree_node
                                                  and t3.setid = t.setid
                                                  and t3.setcntrlvalue = t.setcntrlvalue
                                                  and t3.tree_name = t.tree_name
                                                  and t3.tree_branch = t.tree_branch
                                                  and t3.tree_node_num = t.tree_node_num) 

The SQL works perfectly well, but an execution plan might reveal that the "costs" are a bit heavy.

After a long time, when I encountered the same requirement – once again in an SQR program, I thought of implementing something different and probably better. And I came up with a couple of SQR procedures to accomplish the same effect.

!---------------------------------------------------------------
Begin-Procedure Get-Departments
!---------------------------------------------------------------
    Let $deptid = <input_dept_id>
    Let $leaf_node = ‘N'
    Let $DEPT_CLAUSE = '('''||$deptid||''')'   
                   
    While $leaf_node <> 'Y'
            Let $dept_string = ''
            Let $leaf_node = 'Y' 
            Do Fetch-Subdept
            Let $DEPT_CLAUSE = '(''' || $dept_string || ''')'
    End-While
End- Procedure
!---------------------------------------------------------------
Begin-Procedure Fetch-Subdept
!---------------------------------------------------------------
BEGIN-SELECT
A.TREE_NODE
A.PARENT_NODE_NAME
    Move &A.TREE_NODE to $treenode
    Let $leaf_node = 'N'
    If $dept_string <> ''
          Let $dept_string = $dept_string || ','
    End-if   
          Let $dept_string = $dept_string || '''' || $treenode  || ''''
FROM PSTREENODE A
WHERE A.PARENT_NODE_NAME IN [$DEPT_CLAUSE]
END-SELECT       
End-Procedure

The above method will traverse the department tree on Breadth-first Search. Performance-wise, the SQR procedures were a tad faster than the SQL.