SQLGrammarException with PostgreSQL for with "getHotWeblogs" with SQL patch
---------------------------------------------------------------------------
Key: ROL-1584
URL:
http://opensource.atlassian.com/projects/roller/browse/ROL-1584
Project: Roller
Issue Type: Bug
Components: Database Access & Data Model
Affects Versions: 3.1
Environment: postgresql
Reporter: Nouguier Olivier
Assignee: Roller Unassigned
When asking for getHotWeblogs, a "native" sql query is used in the
Object.method : package
org.apache.roller.business.hibernate.HibernateWeblogManagerImpl
### BEGIN JAVA CODE ###
Query query = session.createQuery(
"from HitCountData hcd " +
"where hcd.weblog.enabled=true " +
"and hcd.weblog.active=true " +
"and hcd.weblog.lastModified > :startDate " +
"and hcd.dailyHits > 0 " +
"order by hcd.dailyHits desc");
query.setParameter("startDate", startDate);
### END JAVA CODE ###
With postgreql it result to a sql query:
### BEGIN SQL DUMP ###
select hitcountda0_.id as id17_, hitcountda0_.websiteid as websiteid17_,
hitcountda0_.dailyhits as dailyhits17_
from roller_hitcounts hitcountda0_, website websitedat1_ where
hitcountda0_.websiteid=websitedat1_.id and
websitedat1_.isenabled=1 and websitedat1_.isactive=1 and
websitedat1_.lastmodified>'2006-10-23 19:54:06.298000+02' and
hitcountda0_.dailyhits>0 order by hitcountda0_.dailyhits desc limit 25
### END SQL DUMP ###
Then to an:
ERROR: operator does not exist: boolean = integer
I don't hnow if it a Roller, mapping or an hibernate issue, but adding the
following to the postgresql sql creation script made it work for me.
It's just add the missing operator.
### BEGIN SQL PATCH ###
CREATE FUNCTION boolean_integer_compare(boolean,integer) RETURNS boolean AS $$
SELECT ($2 = 1 AND $1) OR ($2 = 0 AND NOT $1);
$$ LANGUAGE SQL;
CREATE OPERATOR = (
leftarg = boolean,
rightarg = integer,
procedure = boolean_integer_compare,
commutator = =
);
### END SQL PATCH ###
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://opensource.atlassian.com/projects/roller/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
