roller-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Nouguier Olivier (JIRA)" <nore...@atlassian.com>
Subject [Roller-JIRA] Created: (ROL-1584) SQLGrammarException with PostgreSQL for with "getHotWeblogs" with SQL patch
Date Tue, 23 Oct 2007 19:14:22 GMT
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

        

Mime
View raw message