Friday 18 July 2008

Oracle ADF/ JSF: Mind your flags!

I do not know why JSF UI elements have a Disabled property. I mean I would prefer it if they instead offered an Enabled one. ADF bindings on the other hand have enabled properties, so UI tags usually look this :

<af:commandButton actionListener="#{bindings.Create.execute}"
                     text="#{res['commands.create']}"
                     disabled="#{!bindings.Create.enabled}"
                     action="addEdit"/>

How imagine having to wire in the UserInfo bean, I was talking about the other day. Ideally you would like to have the above Create button enabled when your user is an administrator i.e. #{UserInfo.admin == true} and when the Create binding is enabled, meaning #{bindings.Create.enabled == true}.

Since we now have a Disabled property, this means that the value assigned should be the opposite of #{UserInfo.admin == true && bindings.Create.enabled == true}. The EL expression for this is #{!(UserInfo.admin == true && bindings.Create.enabled == true)}. Using De Morgan's low we end up with #{!UserInfo.admin || !bindings.Create.enabled}.

I understand that for most people this is more than trivial, but I have to admit that is took me a good while to figure it out. (Does age come at a price or what?) I want to learn by my own mistakes. Therefore I thought I 'd put it down in witting, so I won't run into this again.

Tuesday 15 July 2008

Oracle ADF: Viewlink based attributes vs Full Blown Queries

At one time or an other all of us must have dealt with an entity based View object whose SQL statement looked more or less like this :

SELECT UserInfo.USER_ID,
      UserInfo.USER_NAME,
      UserInfo.FULL_NAME,
      UserInfo.USER_ROLE,
      UserInfo.DEPARTMENT_ID,
      UserInfo.ACTIVE,
      Department.NAME,
      Department.ID
FROM USERS UserInfo, DEPARTMENTS Department
WHERE UserInfo.DEPARTMENT_ID = Department.ID (+)

This kind of VO may appear easy to create and manage when your schema contains two or three entities but things may end up pretty complicated as the number of associated entities increases. Let me show you the SQL statement of a view object displaying my version of the Service requests view in full detail.

  SELECT
      ServiceRequest.ID,
      ServiceRequest.REQUEST_DATE,
      TRUNC(REQUEST_DATE) AS SEARCH_DATE,
      ServiceRequest.REQUESTER_FIRST_NAME,
      upper( REQUESTER_FIRST_NAME) AS SEARCH_FIRST_NAME,
      ServiceRequest.REQUESTER_LAST_NAME,
      UPPER( REQUESTER_LAST_NAME) AS SEARCH_LAST_NAME,
      ServiceRequest.REQUESTER_TELEPHONE,
      ServiceRequest.LOCATION_STREET,
      ServiceRequest.LOCATION_NUMBER,
      ServiceRequest.LOCATION_AREA,
      ServiceRequest.LOCATION_ZIP,
      ServiceRequest.LOCATION_CITY,
      ServiceRequest.DEPARTMENT_FOR,
      Department.NAME AS DEPARTMENT_NAME,
      ServiceRequest.ASSIGNED_DATE,
      ServiceRequest.ASSIGNED_TO,
      AssignedTechnician.FULL_NAME AS TECHNICIAN_NAME,
      ServiceRequest.RESOLUTION_COMMENTS,
      ServiceRequest.REQUEST_STATUS,
      Status.NAME AS STATUS_NAME,
      ServiceRequest.PROBLEM_DESCRIPTION,
      AssignedTechnician.USER_ID,
      ServiceRequest.CREATED_BY,
      ServiceRequest.CREATED_ON,
      ServiceRequest.MODIFIED_BY,
      ServiceRequest.MODIFIED_ON,
      ServiceRequest.RESOLVED_ON,
      Department.ID AS ID1,
      Status.STATUS_ID,
      ServiceRequest.TELEPHONE_BY,
      TelephonedOperator.FULL_NAME AS TELEPHONE_OPERATOR_NAME,
      ServiceRequest.TELEPHONE_DATE,
      TelephonedOperator.USER_ID AS USER_ID1
 FROM
   SERVICE_REQUESTS ServiceRequest,
   DEPARTMENTS Department,
   STATUSES Status,
   USERS AssignedTechnician,
   USERS TelephonedOperator
 WHERE 
  (ServiceRequest.DEPARTMENT_FOR = Department.ID (+)) AND
  (ServiceRequest.REQUEST_STATUS = Status.STATUS_ID) AND
  (ServiceRequest.ASSIGNED_TO = AssignedTechnician.USER_ID(+)) AND
  (ServiceRequest.TELEPHONE_BY = TelephonedOperator.USER_ID(+))

When dealing with pure entity based view objects -- that is without a custom where clause -- then JDeveloper makes some descent effort in keeping your where clause straight. If you decide to add bind variables however, then JDeveloper completely looses it and you end up having to manage everything by hand. Where clauses have to be tampered with if you also decide that you need outer left joins and you cannot get away without using the primary keys of all the participating entities and then having to learn how to hide them.

I am never too happy when dealing with such monsters but users are always hungry for more and more information. I guess that this why every self respecting SAP table has at least 70 or more columns :-) ....

Anyway the thing that triggered my search for alternatives was the fact that on many occasions the reference fields of multi entity view objects would not update their values correctly especially after edits, until you issued a final commit, thus giving users one more reason to grumble. So instead of using the SQL query mode, I ended up using view link based transient attributes.

The idea is very simple. Create single entity based view objects containing all the relevant information. In our example Users and Departments. Then create a view link between the two and expose accessors to the client. Enable Java code generation for the primary view row. Last create a transient attribute to the source View (Users) that exposes the department name through the view link provided departments row member.

Let's follow the steps in more details....

For starters we can create two view objects UsersFullList and DepartmentsList with SQL statements like the following :

SELECT UserInfo.USER_ID,
      UserInfo.USER_NAME,
      UserInfo.FULL_NAME,
      UserInfo.USER_ROLE,
      UserInfo.DEPARTMENT_ID,
      UserInfo.ACTIVE
FROM USERS UserInfo

... for the Users View and ...

SELECT Department.ID,
      Department.NAME,
      Department.IS_TECH
FROM DEPARTMENTS Department

... for the departments. If you do not intend to edit Departments through your application, you may very well create a read only view object. Next we shall create a view link named UserAssignedToDept . Creating the view link is trivial, so I will show only you the images where we define the connection ....

and the view link properties :

The important thing to remember here is that since the view link cardinality is set to 1 → ∞ the department accessor will be of type Row instead of RowIterator. So if we next edit the UsersFullList view and create a Java class for the view row, then the code for the DepartmentInfo attribute will look exactly like this :

   /**Gets the associated Row using master-detail link DepartmentInfo
    */
   public Row getDepartmentInfo()
   {
       return (Row)getAttributeInternal(DEPARTMENTINFO);
   }

Creating a department name attribute for the the new view is now done by defining a transient attribute to the UsersFullList view

and then providing a getter method that looks like this :

   public String getDepartmentName()
   {
       String deptName;
       try {
           Row deptRow = this.getDepartmentInfo();
           deptName = (String )deptRow.getAttribute("Name");
       } catch (Exception e) {
           deptName = "";
       }
       return deptName;
   }

From the controller's side the view object looks exactly like it would if based on SQL and you don't have to hide the participating entity key attributes.

So is it worth it you might ask. The answer is -- as usually -- yes and no depending on the size of your project the complexity of your view objects and whether or not you prefer tampering with SQL code than Java. As I explained in the beginning of this posting the main advantage of this method is that your reference attributes will always display correctly since they are composed of pointers to the actual data of the view cache instead of cached entries. On the other hand if you project contains two view objects each composed from two entities, going through all that will be like killing a fly with a machine gun.

Friday 11 July 2008

Oracle ADF: My simple UserInfo bean class

Some time ago, I posted a link to the JDeveloper forum regarding how to use he isUserInRole() method of the ExternalContext class in order to determine whether or not the current application user is assigned a role.

Since I have lost the original post, I am giving to display sample code showing how to reference the function, how to declare the bean in faces-config.xml and also how to use it.

Let's suppose that you our application has two roles. User and admin. Definitions ofr these roles is included in jazn-data.xml.

package mycompany.myapplication.view.beans;

import javax.faces.context.ExternalContext;
import javax.faces.context.FacesContext;

public class UserInfo {
   
    private boolean admin;
    private boolean user;

    public UserInfo()
    {
        FacesContext fc = FacesContext.getCurrentInstance();
        ExternalContext ec = fc.getExternalContext();
        
        user = ec.isUserInRole("user");
        admin = ec.isUserInRole("admin");
    }
    
    public boolean isUser()
    {
        return user;
    }
    
    public boolean isAdmin()
    {
        return admin;
    }
}

The next step is to define the bean in faces-config.xml

  <managed-bean>
    <managed-bean-name>UserInfo</managed-bean-name>
    <managed-bean-class>mycompany.myapplication.view.beans.UserInfo</managed-bean-class>
    <managed-bean-scope>session</managed-bean-scope>
  </managed-bean>

Finally enabling or disabling a menu item property based on whether the user is an admin, is done like this

             <af:commandMenuItem text="#{res['menutabs.AdminAction']}"
                                    action="adminAction"
                                    disabled="#{!UserInfo.admin}"/>

By the same token preventing a page from being display is done like this

          <af:panelPage title="#{res['pages.pricelist.BasicFees.title']}"
                        rendered="#{UserInfo.admin}">

The truth of the matter is that I ended up using all that because I could not succeed in making j2ee security work for my application. Despite Chris Muir's hints on the JDeveloper forum, there was no way I could deny access to certain parts of my application, so I ended up enforcing security using my simple UserInfo bean that I hooked into the disabled and rendered properties of menu and panelPage tags of the protected pages like I showed before.

That way if anyone runs the application by going from the standard path, then he won't be able to access the admin pages and if he calls the pages directly, then he will see nothing.

Thursday 3 July 2008

Oracl;e SQL Developer: Unable to dubug PL/SQL

I have to thank Brian from Oracle support for this one, but let me first give you the story.

If you are trying to debug PL/SQL using Oracle SQLDeveloper 1.5.1 and getting an like this :

then you are probably facing one of two options :

  • You are trying to access you database server from a machine getting its address from DHCP. In that case you will need to supply your ip address each time your start a debugging session. This is accomplished by checking the Prompt for Debugger Host for Database debugging checkbox in the Debuger page of the preferences dialog as shown in the image below.
  • The other option is that your machine firewall is blocking access to the debugging process between your machine and the DB server. In that case you need to reconfigure SQL Developer to use a speciffic port range and also have the firewall allow traffic through these ports.
    In my case all I had to do was change my debugger preferences so the dialog page looks exactly like this :

    and also change my firewall setup to allow access through port 4000.

One final comment. When I run into this problem and asked for help, deep inside I was already blaming my unsupported CentOS Linux version. I had already run into the same problem with my openSUSE machine and found no way out. As it turned out the solution was not machine or distro specific but rather platform independent. So next time, I promise to have a little more faith in my own devices,

Now SQLDeveloper 1.5.1 runs very nicely on both machines at home and at work, and me having regained my trust on the platform, I even run it using the 64 bit 1.6.0_06 JDK. To be honest I haven't seen any real changes in terms of speed, but I tell myself that now I can savour one more taste of the 64 bit glamour. ;-)

Thanks again Brian.

Wednesday 2 July 2008

PHP: Number of rows from query

The idea comes from the developers of Oracle ADF, I have seen it work on JDeveloper and I thought that I can use it in PHP. The trick here is to place the entire query inside a SELECT count(*) from ($query) statement.

Once you know what to do implementation is as easy as witting the code below. The only comment is that the function references an already initialized external mysqli class variable that connects to the database, which if you feel like it may also be passed as a function parameter.

    /**
      * returns the number of records  of the query passed as parameter 
      */
    function numRecordsOfQuery( $a_query) 
    {
        global $mysqli;

        $count_query = " 
                   SELECT count(*) as TotalLines 
                     FROM ($a_query) MyQuery";
        $result = $mysqli->query($count_query);
        // echo $count_query;
        $row = $result->fetch_array( MYSQLI_ASSOC);
        $numRecords = $row['TotalLines'];
        $result->close();
        
        return $numRecords;
    }

Note The MyQuery references is mandatory. MySQL will return an error if the enclosed query is not referenced.