Monday, December 13, 2010

Programming with Magic

Many of today's software tools work by "magic". For example, Hibernate magically turns objects and method calls into database tables and SQL statements. JSF and Facelets turn markup and Java sbeans into rich web applications. Seam magically makes all of this work together.

Why do I say magic? As Arthur C. Clarke said, "Any sufficiently advanced technology is indistinguishable from magic." For the majority of developers, these tools qualify as sufficiently advanced technology. These tools just work, and the details of how they work under the covers is irrelevant. For the most part, this is great. The less brain power I have to spend on the infrastructure, the more brain power I have left to solve my domain specific problems. However, there is a danger.

The danger is when the tools don't work as expected, or you are trying to use them in new ways. This is where the "magical" qualities of these tools really shine through. Do some searches for these tools and you will find loads of questions on how to solve specific problems or accomplish specific tasks. Sometimes you'll find the questions answered succinctly. More often you'll see a dialog where multiple suggestions are made before a solution is found.  And sometimes the conversation has petered out with no solution. (I seem to always come across the latter when I am search for solutions).

Why are there so many questions, and so many not satisfactorily answered? It's because of the magic in the tools. When you are using magic, it is not clear what doing something new will do. I've had a number of problems that were fixed by adding an incantation to a config file. How was I supposed to know that without being an expert in the tool? That last question is the danger of magic. When you are using tools that you don't really understand, how are you are supposed to solve unexpected problems?

Example
Here's a specific problem I recently had involving Hibernate, Facelets, and Seam. I have two Hibernate entity classes:
  1 @Entity 
  2 public class Person {
  3   @Id Long id; 
  4   String firstName; 
  5   String lastName; 
  6 } 
  7  
  8 @Entity 
  9 public class Task {
 10   @Id Long id; 
 11   String name; 
 12   @ManyToOne(fetch = FetchType.LAZY) Person assignedPerson; 
 13 }
I have a Seam EntityQuery that returns a list of Tasks, as well as handling pagination and sorting (handled by Seam's EntityQuery class.
  1 @Name("taskList") 
  2 public class TaskList extends EntityQuery<Task> {
  3   public TaskList() { 
  4     setEjbql("SELECT task FROM Task task"); 
  5   } 
  6 }
I have a facelet page that has a table showing all the tasks. It looks something like:
  1 <rich:dataTable value="#{taskList.resultList}" var="task"> 
  2   <h:column> 
  3     <f:facet name="header"> 
  4       <s:link styleClass="columnHeader" value="Task ID"> 
  5         <f:param name="sort" value="task.id" /> 
  6       </s:link> 
  7     </f:facet> 
  8     #{task.id} 
  9   </h:column> 
 10   <h:column> 
 11     <f:facet name="header"> 
 12       <s:link styleClass="columnHeader" value="Name"> 
 13         <f:param name="sort" value="task.name" /> 
 14       </s:link> 
 15     </f:facet> 
 16     #{task.name} 
 17   </h:column> 
 18   <h:column> 
 19     <f:facet name="header"> 
 20       <s:link styleClass="columnHeader" value="Assigned Person"> 
 21         <f:param name="sort" value="task.assignedPerson.lastName" /> 
 22       </s:link> 
 23     </f:facet> 
 24     #{task.assignedPerson.lastName} 
 25   </h:column> 
 26 </rich:dataTable>
And just like that (after adding all the appropriate headers, config files, jar files, etc.) I have a web page that will show me a 3 column table of all my task objects that are in the database. Not only that, each column header is a link that when clicked returns the table sorted by the column. There's a lot of going on here. Explaining it all is beyond the scope of this post, just trust me. It's magic, after all.  :)

Problem
My test dataset included four tasks, two assigned to one person, one assigned to a different person, and one without an assignment (i.e. a null assignedPerson field).

Everything worked great until I tried to sort by last name. When I sorted by last name, it successfully sorted, but it suddenly only showed me the three tasks that were assigned to a person. The fourth task was missing. Why?
Task IDNameAssigned Person
1Buy GroceriesHaddox-Schatz
2Write BookClarke
3Write BlogHaddox-Schatz
4Achieve World PeaceProblem Row

Since these tools aren't really magic, they do sometimes provide helpful diagnostics. In this case, I had logging turned up so that I could see all of the SQL that was actually being run by Hibernate. When getting the list of tables, the code was effectively running the query
SELECT * FROM Task
and then running separate
SELECT lastName FROM Person WHERE Person.id=?
queries to get the names of the people. When sorting by task name the Task query became
SELECT * FROM Task ORDER BY Name

So what happens when you want to order by the assigned person's last name? Well, now you have to have a JOIN to order correctly. The query it generated when I tried sorting by last name was something like
SELECT * FROM Task t JOIN Person p 
         WHERE t.assignedPersonId = p.id ORDER BY p.lastName
And now the problem is highlighted. With the JOIN, only the tasks that have assigned people are returned by the query, which explains why I lose the one row when I sort by last name. The SQL solution here is to use a LEFT JOIN rather than a JOIN. i.e.
SELECT * FROM Task t LEFT JOIN Person p
         WHERE t.assignedPersonId = p.id ORDER BY p.lastName

Great, I have the solution... except that I don't.

I don't write the SQL, Hibernate does. Is there some command or configuration or something that I can do to get it to use a LEFT JOIN when it needs to do a join for ORDER BY? This is where the magic hurts. I don't know what incantation to utter. I don't even know how to find such an incantation, or to determine if it is even possible. This means I am left to my least favorite form of programming - googling for solutions.

Solution
I eventually found that if I changed my EJBQL in the task list constructor (line 4 in the second example above) to
SELECT task FROM Task task left join fetch task.assignedPerson
that it worked. Apparently, if you explicitly always do a LEFT JOIN, the ORDER BY will use that LEFT JOINed object. Oh, and despite EJBQLs case insensitivity when it comes to key words and my proclivity to write SQL keywords in all caps, you'll notice that I had the text "left join fetch" in lower case. If I had that in upper case then Seam's EntityQuery class doesn't handle it correctly.

Conclusion
In this particular case I found a solution to my problem, though I still don't understand why Hibernate's automagically generated SQL returns different result sets based on the ORDER BY clause. (i.e. why didn't they generate the LEFT JOIN to begin with?)  Sometimes I am unable to find a solution and so have to redesign my code around an apparent limitation of the tool. I say apparent, because if I am not an expert in the tool, its possible I am just missing the magical incantation I needed to accomplish my task. In either case, it is very unsatisfying as a developer to not really understand my tools and why I have to write my code the way I do.

I don't want to be a code wizard, I just want to be a software engineer.

No comments: