[Contrib: Evaluation System] AARON: Using SQL in eval DAO

[Aaron Zeckoski]

Hibernate doesn't really make it possible to identify the
dialect/vendor as far as I can tell. My recommendation would be to
write your query as a join instead of a subselect since most databases
can efficiently compile joins but mysql is bad at compiling subselects
in most cases.

You could also do it as 2 queries (though there are limits to this
option and it will possibly be slower than a system which can compile
the query effectively).

If that won't work, we can do a hack to pull in the sakai vendor via
spring but we can't just add externallogic as a dependency of the dao
as that creates a loop in the dependencies. Pulling in the vendor is a
poor solution so I would only want to do that if there is no other way
(but I think there are other ways).

<bean id="sakaiDatabaseType"
factory-bean="org.sakaiproject.db.api.SqlService"
factory-method="getVendor" />

-AZ


On Thu, Aug 11, 2011 at 10:15 AM, Jim Eng <jimeng at umich.edu> wrote:
> Hi Aaron,
> I am working on consolidated notifications, in which a student gets one
> announcement or reminder a day instead of one message for each eval they can
> take. I have written HQL in the DAO impl to update records to indicate that
> an announcement has been sent.  The query works fine in Oracle but it is
> deadly slow in MySQL.  I have tried revising the HQL to generate a better
> MySQL query, but it seems that quirks in hibernate and MySQL make this
> unlikely.  I have written SQL that performs very well in MySQL (but only
> MySQL).
> It's easy elsewhere in sakai to write custom SQL for specific vendors.
>  Hibernate usually takes care of that, but it fails in this case.  If we had
> a way in the DAO of identifying the vendor, I could do this:
> if("mysql".equalsIgnoreCase(dialect)) {
> StringBuilder sqlBuffer = new StringBuilder();
> sqlBuffer.append("update EVAL_ASSIGN_USER eau, EVAL_EMAIL_PROCESSING_QUEUE
> epq ");
> if(sendingAvailableEmails) {
> sqlBuffer.append("set eau.AVAILABLE_EMAIL_SENT=:dateSent ");
> } else {
> sqlBuffer.append("set eau.REMINDER_EMAIL_SENT=:dateSent ");
> }
> sqlBuffer.append("where eau.id = epq.EAU_ID and epq.USER_ID=:userId and
> epq.EMAIL_TEMPLATE_ID=:emailTemplateId");
>
> updateQuery = session.createSQLQuery(sqlBuffer.toString());
>
> } else {
> StringBuilder hqlBuffer = new StringBuilder();
> hqlBuffer.append("update EvalAssignUser ");
> if(sendingAvailableEmails) {
> hqlBuffer.append("set availableEmailSent = :dateSent ");
> } else {
> hqlBuffer.append("set reminderEmailSent = :dateSent ");
> }
> hqlBuffer.append("where id in (select eauId from EvalEmailProcessingData
> where emailTemplateId = :emailTemplateId and userId = :userId)");
>
> updateQuery = session.createQuery(hqlBuffer.toString());
> }
>
> Unfortunately, hibernate does not surface that information that I can see.
>  I have tried accessing the "hibernate.dialect" property in various ways,
> and I've looked for anything in the hibernate API's that might identify the
> dialect.  Haven't found anything.
> So I'd like to ask this.  The database vendor is readily accessible from
> sakai's SqlService.  Suppose we added this method to EvalExternalLogic:
> public String getDatabaseVendor();
> What would you think about calling an external logic method in the DAO impl?
>  Can you see another way to do this, other than just hanging MySQL users out
> to dry?
> Thanks.
> Jim
>
>
>



-- 
Aaron Zeckoski - Software Architect - http://tinyurl.com/azprofile