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

[Jim Eng]

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



-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://collab.sakaiproject.org/pipermail/evaluation/attachments/20110811/6e2eed99/attachment-0001.html