Tuesday, August 14, 2007
Toplink Query Deficiencies
In any relational setting, it is wise to avoid this situation if you can:
TABLE_ONE
---------
object_id serial primary key not null
foo_id integer not null references TABLE_TWO(object_id)
bar_id integer not null references TABLE_TWO(object_id)
TABLE_TWO
---------
object_id serial primary key not null
some_field varchar(80) not null
There is no way to perform a single join from table_one to table_two to get a complete set of information, because of the references to multiple different rows in table_two. It's probably better to reorganize the relationship if you can.
The exception is if you don't necessarily need both fields. For example, if you can do without knowing anything about bar_id in most cases, you could always lazy load that field. That is, if lazy loading for 1:1 fields works in your JPA provider.
It still doesn't work with Toplink, and these bugs are STILL open:
https://glassfish.dev.java.net/issues/show_bug.cgi?id=2546
https://glassfish.dev.java.net/issues/show_bug.cgi?id=2554
The consequence is that I cannot static weave my model, so my only choice for now is to mark the fields as @Transient and ignore them for now. (I have a feature that tracks the changes made to every entry in a table with a changer that references an Account... for now, I just won't track the changer until I have time to come up with a better way of doing it or the glassfish folks fix their shit.)
TABLE_ONE
---------
object_id serial primary key not null
foo_id integer not null references TABLE_TWO(object_id)
bar_id integer not null references TABLE_TWO(object_id)
TABLE_TWO
---------
object_id serial primary key not null
some_field varchar(80) not null
There is no way to perform a single join from table_one to table_two to get a complete set of information, because of the references to multiple different rows in table_two. It's probably better to reorganize the relationship if you can.
The exception is if you don't necessarily need both fields. For example, if you can do without knowing anything about bar_id in most cases, you could always lazy load that field. That is, if lazy loading for 1:1 fields works in your JPA provider.
It still doesn't work with Toplink, and these bugs are STILL open:
https://glassfish.dev.java.net/issues/show_bug.cgi?id=2546
https://glassfish.dev.java.net/issues/show_bug.cgi?id=2554
The consequence is that I cannot static weave my model, so my only choice for now is to mark the fields as @Transient and ignore them for now. (I have a feature that tracks the changes made to every entry in a table with a changer that references an Account... for now, I just won't track the changer until I have time to come up with a better way of doing it or the glassfish folks fix their shit.)
Labels: glassfish, JPA, software, toplink
Saturday, April 21, 2007
Toplink Essentials: Buggier than a Roach Motel in Pensacola
Working with Toplink Essentials via JPAQL is quite a bit different than working with the commercial version of Toplink using its Expression class. With the commercial Toplink software, you generally get associated 1:1 objects fetched for you (ie eagerly rather than lazily) when you issue a query. In JPAQL, you get exactly what you ask for, which means if you want to get the associated objects in one query, you must use the JPAQL JOIN FETCH operator.
In my case, I needed LEFT JOIN FETCH, which works like an outer (left) join. My query ends up looking like this:
Sometimes Articles won't have a message board associated with them, though usually they will. For example, there's no point in putting a message board on an article that is in a Pending state, since nobody can see it anyway.
Without the LEFT JOIN FETCH, Toplink issues one query to get the Articles, and then one query for every associated object. So if you're requesting 10 articles, you're going to get 11 queries. With the LEFT JOIN FETCH, it is supposed to consolidate everything into just enough queries to get what you ask for, and in fact the query it issues is reasonable:
Unfortunately, Toplink's behaviour upon handling the results of running this query is NOT reasonable:
I've filed this one as https://glassfish.dev.java.net/issues/show_bug.cgi?id=2881. If past behaviour is any indication, the Glassfish people will change the priority on the bug to a P4 and decide not to fix it until we're all very old, despite it being a significant breakage of the API. They even pull that crap when the one-liner fix is already given in the bug report, and it would take longer to reset the priority and update the bug than it would to actually fix the damn problem.
In my case, I needed LEFT JOIN FETCH, which works like an outer (left) join. My query ends up looking like this:
Select x from Article x LEFT JOIN FETCH x.messageBoardRoot where x.createDate > ?1 and not(x.status = ?2) order by x.createDate descSometimes Articles won't have a message board associated with them, though usually they will. For example, there's no point in putting a message board on an article that is in a Pending state, since nobody can see it anyway.
Without the LEFT JOIN FETCH, Toplink issues one query to get the Articles, and then one query for every associated object. So if you're requesting 10 articles, you're going to get 11 queries. With the LEFT JOIN FETCH, it is supposed to consolidate everything into just enough queries to get what you ask for, and in fact the query it issues is reasonable:
SELECT t0.object_id, t0.thumbs_down, t0.spam_abuse, t0.MAILED, t0.change_summary, t0.VISIBLE, t0.ADJECTIVE, t0.BODY, t0.md5, t0.VIEWS, t0.fuzzy_md5_1, t0.VERSION, t0.fuzzy_md5_2, t0.thumbs_up, t0.create_date, t0.TITLE, t0.SUMMARY, t0.STATUS, t0.section, t0.changer, t0.creator, t1.object_id, t1.post_count, t1.last_post, t1.posting_permitted, t1.source_id, t1.post_count_24hr FROM ARTICLE t0 LEFT OUTER JOIN article_message_root t1 ON (t1.source_id = t0.object_id) WHERE ((t0.create_date > ?) AND NOT ((t0.STATUS = ?))) ORDER BY t0.create_date DESC
bind => [2007-04-14 14:46:15.593, P]
Unfortunately, Toplink's behaviour upon handling the results of running this query is NOT reasonable:
java.lang.NullPointerException
at oracle.toplink.essentials.mappings.ForeignReferenceMapping.buildClone(ForeignReferenceMapping.java:122)
at oracle.toplink.essentials.internal.descriptors.ObjectBuilder.populateAttributesForClone(ObjectBuilder.java:2136)
at oracle.toplink.essentials.internal.sessions.UnitOfWorkImpl.populateAndRegisterObject(UnitOfWorkImpl.java:2836)
I've filed this one as https://glassfish.dev.java.net/issues/show_bug.cgi?id=2881. If past behaviour is any indication, the Glassfish people will change the priority on the bug to a P4 and decide not to fix it until we're all very old, despite it being a significant breakage of the API. They even pull that crap when the one-liner fix is already given in the bug report, and it would take longer to reset the priority and update the bug than it would to actually fix the damn problem.
Labels: glassfish, software, toplink, toplink essentials
Subscribe to Posts [Atom]
