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)