Tuesday, February 19, 2013

Spell checking input fields

Most browsers now (except for IE) have a built in spell checker that underlines unrecognized words as you type.   Firefox by default only underlines words in multi-line input fields but you can always right click and select spell check from the context menu to spell check a single line af:inputText element.   There is a configuration setting in firefox that can be turned on to enable spell checking on single line input fields but it's not easily accessed and since single line inputs are often used for common names, addresses, products etc that are not in the dictionary you may not want this turned on globally.   These browsers now support the spellcheck attribute on the html input element that you can use to force a field to be spell checked or else turn off spell checking.  We had a need to turn this on for a single line input field in firefox so I was able to make it work by adding a client listener to the af:inputText element that calls a javascript function when the element gets focus that sets the spellcheck attribute to true.  The client listener looks like this.

<af:clientListener 
method="(function(evt){evt.getSource().getPeer().getDomElement().setAttribute('spellcheck','true');})"
type="focus" />

Note that I used an inline function but you should put this in a .js file to reduce page size, especially if it is used for more than one input element on the page.

Monday, February 18, 2013

Using ANSI Joins with ADF business components


If you select the Model project and bring up project properties, there is a project setting (under business components/View Objects)  you can change to make entity based view objects use ANSI style joins instead of the Oracle style.  
i.e.
instead of having this in the where clause
AND (XrefSalesTransactionAccount.ACCOUNT_ID = Account.ACCOUNT_ID(+))
You would have this join clause predicate
LEFT OUTER JOIN ACCOUNT Account ON (XrefSalesTransactionAccount.ACCOUNT_ID = Account.ACCOUNT_ID)


I see three advantages to using ANSI style joins.  
  •  They are clearer and easier to read 
  • They give separation between the table joins and the where clause criteria so that when you add extra where clause criteria it doesn’t get mixed in with the table join criteria, so is easier to change in the future.  i.e.  everything in the query clauses input box will be custom sql added by the developer, not mixed in with the join criteria put there by jdeveloper. 
  •  If you add new entities to the view in the future, jdeveloper won’t have to update the where clause so it will be easier to keep the original where clause intact when you get the message “The query has a   custom where clause.  Would you like to overwrite the customized where clause by the default where clause?  If not you may need to manually add a join clause for the new entity in the where clause”.  There seems to be a bug in jdeveloper in that if you select “No”  it still deletes the custom where clause, except now you can save off the whole thing and paste it back in without having to separate the custom part from the generated part.
One problem you may discover after changing this setting is that if you open any pre-existing view object and edit anything (including hint text),  it will convert it to use ANSI joins, however it will leave the original "where" clause predicates in place containing the old join sequence.   The query may still work unless it contained outer joins but there will be redundant criteria in the "where" clause that should be deleted to make it clean.

Also if you have a view links that was customized to contain the Oracle outer join (+) syntax.  This type of view link won't work if the view being linked uses ANSI joins because the link is created using bind variables in the where clause and you can't combine Oracle and ANSI joins.  You could potentially use this type of link to a ANSI style view if the view uses expert mode and wraps the entire query in a extra select clause so that the where clause created by the link is added outside of the inner query but I don't recommend using Expert mode.

I  discovered one bug when using ANSI joins that occurs when you attempt to create a view object containing a Many-to-Many association between two entities.   A Many-to-Many association is when you have a intersecting xref table that joins the two tables together.   ADF will generate invalid SQL when you attempt to use this association in your view object and you will get  
(java.sql.SQLSyntaxErrorException) ORA-00933: SQL command not properly ended.
The work-around is to include the intersecting entity in your view object and use a "one to many" association instead of "many to many".  I have reported this bug to Oracle and verified it exists in 11.1.1.4 and 11.1.1.6
update: Oracle has confirmed and opened Bug 16781328 : ERROR ORA-00933 USING ANSI JOIN IN BUSINESS COMPONENTS.


I don't like the fact that the ANSI join style is a project setting and not a view object setting.   I think the determination of style should be on a case by case basis to mitigate the problems described above. It's easy enough to switch the setting on and off but I shouldn't have to.

I would be interested to hear from others who have used ANSI joins in ADF so please reply to this posting if you've had any experience or problems using them.

Tuesday, January 29, 2013

Should you use entity based views for read only queries?


Oracle documentation states under section

"Best Practice:
When you need to create a read-only view object for data lookup, you should use the entity-based view object and deselect the Updatable option in the Entity Objects page of the view object overview editor. The approach benefits from the design time editors which aid in generating the SQL query. The alternative of creating an expert-mode view object requires writing a SQL query. Expert mode queries are still useful for cases where Unions and Group By queries cannot be expressed using entity objects."

We've been using ADF 11g since it was in alpha releases and were given the same advice from Oracle at the time.   After several years of use I have come to the conclusion that I disagree with this advice.   We often have to refactor queries due to data model changes and in our agile process when it comes to sizing a user story we generally triple the size for refactoring a view object that is entity based over one that is a read only query.    Many times the result columns did not change, only the query or tables being joined.   If it's a read only query, you just go in and update the SQL and you're done.   When it's a Entity based view more often than not, the developer struggles because it breaks things when you replace one entity with another or change the association between entities.  ADF does not have a refactor option that lets you swap out a view attribute keeping the same name with one in a different entity.   The view might contains a dozen entities each with their own association to other entities and selecting the correct relationship and verifying the desired query is more time consuming than updating the sql yourself.   You always have to double check the sql it generated to make sure it was what you wanted so there's not much advantage to having ADF generate the query for you.  Also the query may have some extra parameters added to the where clause.  When you change any entity in your view, the custom where clause gets dropped, requiring you to save it off before hand and do some comparisons after the change to add the custom "where" clause back.  This requires you to eyeball the "where" clause to determine which part was generated and which part was custom.  The next problem is if there are generated Impl classes for the entity or view object, sometimes these have to be regenerated from scratch because when you change entity relationships the Impl's don't always update correctly and you have to delete them and let ADF recreate them.   Then you must add back any custom code that was added.  For these reasons I now always define the view as non-entity based when creating a read-only query.

Secondly,  for updatable entity based view objects I try to steer clear of using expert mode.  It may be a little harder to create the desired query using normal mode but most queries can be constructed without resorting to expert mode.  We've had view objects using expert mode that have gotten in a broken state when trying to refactor.   The sql works correctly when you run it externally but the attributes don't display their values when used in the UI.  Changing the view back to normal mode did not fix it and the view object had to be rebuilt from scratch to fix.  A time consuming task for large complicated queries containing dozens of columns.

My quote for the day:
The advantage of using a large framework like ADF is that it does a lot of stuff for you.
The disadvantage of using a large framework like ADF is that it does a lot of stuff for you.  (meaning... when it doesn't do it right its hard to fix)

Tuesday, December 18, 2012

Add an isDirty attribute to your entity

Here is a useful Groovy expression that can be used to add a transient attribute to your Entity that will return true if any attribute in your entity is new or has been modified

 (entityState != 1)

See Image below


Then add this attribute to your view object and use it in an expression to highlight rows that have been modified and not yet commited,.

 inlineStyle="#{row.isDirty ? 'background-color:#FFF9B3;' : ''}"







 Here's another Groovy expression that will display the original posted value of any attribute in your entity that has been modified but not yet posted

adf.object.getPostedAttribute(adf.object.getAttributeIndexOf("Name"))

For an overview of using Groovy expression in ADF see this white paper by Grant Ronald


Tuesday, November 27, 2012

How to cancel a long running query (Part 2)

Earlier this year I wrote about a possible way to cancel a long running query from the UI
       see How to cancel a long running query from the UI
 I finally got around to  experimenting with this more and have created a sample app that can be downloaded from here 


A screenshot is below.   The sample was created with jdev 11.1.1.4 but It should work with 11.1.1.6 without any problems. This demonstrates the creation of a dynamic view displayed in a dynamic table and includes the cancel button.    You can type any valid sql into the input box and the table will display all of the columns returned.   If you create a particularly slow query you can test the cancel query button and you should get the error message displayed below.  Obviously you wouldn't want to give the user the ability to provide the SQL for the dynamic query but it makes for a good example of how a dynamic query can be created and displayed.
Since ADF blocks all requests going through the faces servlet while the server is processing the query,  I implemented the cancel button using a client listener that cancels the event then creates it's own ajax call to a plain old servlet.    That servlet retrieves the view object (if it exists) from a transient map object stored on the session by the CustomViewObjectImpl class and calls cancelQuery() on it.  If the cancel is successful the executeQuery method will throw an exception that is displayed in the UI.    

When you set a query timeout on the view object, ADF will spawn a monitor thread that sleeps for the timeout period.  When the thread wakes up it calls cancelQuery() on the ViewObject.    This is the very same thing the servlet is doing except it is initiated by the user instead of the monitor thread.
  
The view object is stored in a transient map so that Serialization will not be attempted however the view object is only stored on the Session for the duration of the execute method and then removed so it should never attempt serialization anyway.


Note this will not work in a clustered environment not configured for sticky load balancing.   Obviously the request must make it to the same server where the query is running in order to cancel it.  I don't know of an easy way around that.  Ideally it would be nice if you could send a command to the database containing a key to the session needing it's query canceled then it wouldn't matter which server the cancel request arrived on. That feature is not available apart from the Linux command line.


This sample app will cancel all queries running on the users session if for example the user had multiple windows/tabs open, each running their own queries (not too likely) but you can pass up a key on the cancel request if you want to limit the cancel to a specific query.  

Note:  I initially could not get the cancel to work when running on Windows with the integrated development environment.  It ran fine however when I deployed it to the server running Linux.  
The query timeout setting was also not being honored when running under Windows.   I know this used to work for me so I'm thinking there must have been a network configuration change or upgrade that broke it.  I then discovered that I could get the cancel to run under Windows by adding the following JVM startup parameter to the "Launch Settings"

 -Doracle.net.disableOob=true 

This disables "Out of Band" breaks and instead uses "In Band" Breaks.  The sample app is configured this way but you may or may not need it depending on your network drivers and configuration.



Feel free to comment if you find this useful.
To progress this concept further I would like to add a page to our monitoring application that will iterate over the sessions and display a list of all long running queries and the users that are running them.



 

 

 

Friday, October 26, 2012

Allowing Read-Only Access to your application

We recently had a requirement to allow user logins with any assigned role but with read only access to the application.   User should be able to login and see all of the pages associated with the assigned role but cannot do anything that will add or change data in the database.    This turned out to be fairly simple to implement.   We created a new role "readOnlyAccessAllowed".  We already have a custom base Application Module class that all of our Application Modules extend.    So the change was simply to override the beforeCommit method, check if the user has the read only access role and throw an exception if so.  We also display a tag on the home page to assure the user that they have read only access.

If you do not already have a base class defined for your application modules, this is done by editing the Model.jpr  project properties.  Under Business Components/Base Classes you define a base class for your View Objects and Application Modules.   If you do this after your application modules have been defined and your app module has a java implementation, you will need to edit those java implementations and verify that they extend your new base app module.  Newly created app modules will automatically extend your base app module.

Below is sample code showing the implementation. The were 2 or 3 app modules used for storing user preferences, saved searches and login history that we still wanted to be allowed so I excluded those.  I also added a method allowOneTimeCommitForReadOnlyUser that can be called in the code immediately before calling commit() to allow for other exceptions to the rule.



 
    /* overriden in order to prevent commiting data when user has read only user role
     * beforeCommit is called for both the root app module and the app module
     * containing the view object being commited.
     */
    @Override
    public void beforeCommit(TransactionEvent e) { 
        boolean isReadOnlyAccess = getDBTransaction().getSession().isUserInRole("readOnlyAccessAllowed");
        String appModuleName = this.getRootApplicationModule().getName();
        // allow commits for login history, performance monitorng and user preferences
        boolean allowCommit=(!isReadOnlyAccess || 
                             "AppCtrlAM".equals(appModuleName) || 
                             "SearchAM".equals(appModuleName)   ); // allow readOnlyAccess users to save searches criteria
        // this is used to allow writing the account audit info for USER_ACTIVITY table       
        if (allowOneTimeCommitForReadOnlyUser){
          allowCommit=true;
          allowOneTimeCommitForReadOnlyUser=false;
        } 
            
        if(!allowCommit){ 
            displayReadOnlyMessage();
            throw new ReadOnlyAttrException(0,"","",this.getName(),"");
        }
       
        super.beforeCommit(e);
    }
    
    public static void displayReadOnlyMessage(){
      FacesContext context = FacesContext.getCurrentInstance();
      FacesMessage fm = new FacesMessage(FacesMessage.SEVERITY_ERROR,"Read Only","You are allowed read only access.  Data cannot be added or changed.");
      context.addMessage(null, fm);
    }

    public void allowOneTimeCommitForReadOnlyUser() {
        ApplicationModule am = this.getRootApplicationModule();
        if (am instanceof CAAppModuleImpl){
          ((CAAppModuleImpl)am).allowOneTimeCommitForReadOnlyUser = true;
        }
        this.allowOneTimeCommitForReadOnlyUser = true;
    }

Here's a link to another solution.  That solution makes input fields display as read-only output fields.
We didn't do that because we didn't want to change the Application look in any way because we use Selenium non-invasive tests to regression test our pages in production so we want the page to look the same as it does for a normal user but still prevent the test or user from committing data.


Thursday, September 6, 2012

Selenium WebDriver utility for determining when page has finished rendering

We are using Selenium for automated testing of our web app.   We use the new Selenium WebDriver API to create java based unit tests for interfacing with the UI.  One problem we had was determining when the page had completely finished rendering as the page may still be in the process up updating after the initial page is loaded.   There is an ADF javascript function for checking this
the helper method below can be called by the test class to determine when the page is fully synchronized with the server and the next action can be performed.



public static void waitForPageToFinishRendering(WebDriver oDriver, int timeout) {
        ExpectedCondition e = new ExpectedCondition() {
            public Boolean apply(WebDriver d) {
              JavascriptExecutor js = (JavascriptExecutor) d;
              Boolean isReady = (Boolean)js.executeScript("return AdfPage.PAGE.isSynchronizedWithServer()");
              return isReady;
            }
          };
          WebDriverWait w = new WebDriverWait(oDriver,
timeout,100);
          w.until(e);
    }


For a Good overview of using Selenium with ADF see 
http://www.oracle.com/technetwork/articles/adf/part6-094560.html
That article talks about using waitForElementPresent before performing the next action but I have found using the technique above to be more reliable.  Especially for complex pages where there is a lot of lazy loading going on.

Tuesday, April 24, 2012

The danger of using the default settings of a view object for committing data

We recently had a problem in production where the weblogic servers randomly ( once or twice per day) became extremely slow (basically unusable) for a period of time before recovering.  We discovered that the server was  running low in memory causing a perpetual garbage collection cycle to occur.   This begin occurring after loading a large amount of data (several million rows)  that was transferred from our old application.  Up until this point the system had been humming along fine.  An analysis of the thread dump showed that during this time of crisis the server was looping in the following code.


                oracle.jbo.server.QueryCollection.hasNextInResultSet(QueryCollection.java:4611)
                oracle.jbo.server.ViewObjectImpl.hasNextForCollection(ViewObjectImpl.java:6899)F
                oracle.jbo.server.QueryCollection.hasNext(QueryCollection.java:4579)
                oracle.jbo.server.QueryCollection.populateRow(QueryCollection.java:3553)
                oracle.jbo.server.QueryCollection.fetch(QueryCollection.java:3387)
                oracle.jbo.server.QueryCollection.get(QueryCollection.java:2188)
                oracle.jbo.server.ViewRowSetImpl.getRow(ViewRowSetImpl.java:5016)
                oracle.jbo.server.ViewRowSetImpl.getRow(ViewRowSetImpl.java:3242)  
                oracle.jbo.server.ViewObjectImpl.activateTransients(ViewObjectImpl.java:18328)
                oracle.jbo.server.ViewObjectImpl.activateTransients(ViewObjectImpl.java:18289)
                oracle.jbo.server.ViewObjectImpl.activateState(ViewObjectImpl.java:18512)
                oracle.jbo.server.ViewObjectImpl.activateState(ViewObjectImpl.java:18407)
                oracle.jbo.server.ViewRowSetIteratorImpl.activateIteratorState(ViewRowSetIteratorImpl.java:4025)
                oracle.jbo.server.ViewRowSetImpl.activateIteratorState(ViewRowSetImpl.java:7235)
                oracle.jbo.server.ViewObjectImpl.activateIteratorState(ViewObjectImpl.java:18742)
                oracle.jbo.server.ApplicationModuleImpl.activateVOs(ApplicationModuleImpl.java:8172)
                oracle.jbo.server.ApplicationModuleImpl.doActivateState(ApplicationModuleImpl.java:7918)
                oracle.jbo.server.ApplicationModuleImpl.doActivateAMState(ApplicationModuleImpl.java:7884)
                oracle.jbo.server.Serializer.activate(Serializer.java:296)
                oracle.jbo.server.DBSerializer.activateRootAM(DBSerializer.java:330)
                oracle.jbo.server.ApplicationModuleImpl.activateState(ApplicationModuleImpl.java:6207)



 The looping was occuring in the getRow method of ViewRowSetImpl.  The stack trace also shows that the looping is occuring as the result of a activation event to restore a previously passivated application module.  This demonstrates the importance of testing code with the jbo.ampooling=false  setting in order to test for passivation problems.   In this case however we still might not have notice this problem because it requires both a large table and a specific sequence of user actions in order to reproduce.  

Here is the getRow method from ViewRowSetImpl.   The section in red was where the looping was occuring


   public Row getRow(Key key)
   {
      Row row;

      if (key != null)
      {
         Object keyHandle = key.getRowHandle();

         if (keyHandle != null && mQRef.getSignature() == key.getSignature())
         {
            row = getRowFromHandle(keyHandle);
            if (row != null && key.equals(row.getKey()))
            {
               return row;
            }
         }
      }

      //if there are rows in the collection, then match those before calling execute.
      if (getFetchedRowCount() > 0)
      {
         for (int j = 0; (row = getRow(j)) != null; j++)
         {
            if (key == null || row.getKey().equals(key))
            {
               return row;
            }
         }
      }

      //make sure that the query is executed before calling getRow(int);
      if (!isExecuted())
      {
         synchronized(getSyncLock())
         {
            execute(false /*force*/, false /*notify*/); //no need for eventing here.
         }
      }

     for (int j = 0; (row = getRow(j)) != null; j++)
      {
         if (key == null || row.getKey().equals(key))
         {
            return row;
         }
      }

      return null;
   }

Here is what I  did to duplicate the problem on my local jdeveloper instance.

  • We have an entity based view object to a table that contains a large (2+ million) row set.  The view object was unbounded (no bind variables)
  • The range size was set to 15. 
  •  There is an iterator binding on the page bound to this view that is bound to a popup form for adding or inserting  one row of data. 
  •  When the popup displays the unbounded query executes because the iterator executes when first used (the default setting).  This may not be good for database performance (which was taking ~3 seconds) but it only retrieves the 1st range of 15 rows from the database into memory and this did not normally cause a problem so was not noticed.
  •  The data was entered on the form and a button “save and add another” is clicked which commits the current row and advances the form to a new row ready for input. 
  •  At this point we have a view object that contains 1 range of rows (15) that were returned by the orignal query plus the newly inserted row that we just committed (16 rows in total).
  •  Now to duplicate the problem we force a passivation of this users app module.  We did this by setting the pool size to 1 and opening up the same page in another browser window.
  • We then cancel out of the form popup.  Before the form closes it presents an “Are you sure?” dialog and  it must re-activate the app module it is bound to.   During activation the listed getRow() method in ViewRowSetImpl is called in order to find and sync up all of the 16 rows that were in the view when it was passivated.  Since there was a newly inserted row it may be located near the end of the result set that would be returned by the unbounded query. (there was no sort order)   The loop in red is the problem.   The ADF framework was searching for this row by retrieving one row at a time from the database.  It basically had to go through all 2 million rows and pull them into memory before it would find it.  On my local debug machine this was happening extremely slow.  (About 50 per second) and I would run out of memory before it finished (and there was only 390,000 rows in our dev database)
This  could have been instantaneous if ADF would run a query with the ID to retrieve it, instead of searching for it row by row.  I really think this is a serious design flaw but the feedback from Oracle was that this was working as designed.   I don't understand why the activation process event needs to find the rows from the database.   It's purpose is to restore the state of the view object to what it was prior to passivation so why not just read the passivation record and be done with it?   This problem could occur on any large table that is used for both scrolling through the data and inserting rows using the same view object. 

Bottom Line and Lessons Learned.
  1. If you have a view object that is only used for inserting rows, make sure that you set the Tuning parameter to Retrieve from the Database "No Rows" or "At Most One Row"  
  2. If you do need to display a table that can filter and scroll over a very large result set and also insert new rows, use a read-only view object for displaying the data and a separate Entity based view object for inserting new rows.  
  3. If the view object had had a sort order that caused newly inserted rows to appear at the beginning of the result set, we wouldn't have noticed this problem. 
  4. This shows how critical it is to set the tuning parameters appropriately when dealing with large tables.   In our case this was a hidden time bomb waiting to happen.