incubator-empire-db-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rainer Döbele <doeb...@esteam.de>
Subject making DBOrderByInfo public
Date Thu, 28 Jan 2010 20:46:35 GMT
Hi McKinley,

I had to have a look at it first.

Before I make DBOrderByInfo public, I am going to rename it.
It's proper name should be DBOrderByExpr.

And there should be overrides on DBColumnExpr for Asc and Desc.

Might do that tonight. I'll let you know when I am done.

Regards
Rainer


McKinley wrote:
> Re: Rowlimits: OFFSET and LIMIT
> 
> I'll submit a patch for it. DBOrderByInfo works really well with it so
> far. Can you make it public and not protected? I am having you use my
> own copy of DBOrderByInfo for now.
> 
> Thanks,
> 
> McKinley
> 
> On Wed, Jan 27, 2010 at 12:11 AM, Rainer Döbele <doebele@esteam.de>
> wrote:
> > Hi McKinley,
> >
> > I have not analyzed your problem in detail but probably in this case
> (multiple order by columns) DBFuncExpr is not suitable.
> > You really should consider creating a class derived from DBColumnExpr
> specifically for ROW_NUMBER() OVER...
> > You may take the implementation of DBFuncExpr as a guideline.
> >
> > We may then be able to add this expression to the
> org.apache.empire.db.expr.column package.
> > Good luck.
> >
> > Rainer
> >
> >
> > McKinley wrote:
> >> Re: Rowlimits: OFFSET and LIMIT
> >>
> >> Wow, I had to really hack to make DBFuncExpr work with multiple
> >> order-by columns.  Take note of the SQL comment in my function
> >> template. This is because I could not cleanly use the question mark
> >> with a DBOrderByInfo (yes, I know, I know). Anyway, the Object[]
> >> params work great on the {#}, but the ? want the first argument to
> be
> >> a DBColumnExpr and not a DBExpr which DBOrderByInfo. I tried to have
> >> the first column hit the ? and direct the " DESC" or "" string to
> the
> >> first {0}. That got type checked to DATETIME (the type of the
> column)
> >> in this case. If I just ignore using ? every works great.
> >>
> >> Any thoughts on how this usage might be facilitated by changes to
> the
> >> function and abstract function arrangement?
> >>
> >>       public void page(int limit, int offset, DBOrderByInfo[]
> orderBy)
> >> {
> >>               this.limit = limit;
> >>               this.offset = offset;
> >>               this.orderBy = orderBy;
> >>               if(rowNum != null) subCmd.removeSelect(rowNum);
> >>
> >>               StringBuilder sql = new StringBuilder("ROW_NUMBER()
> OVER
> >> (ORDER BY {0}");
> >>               if(rowNumParams == null || rowNumParams.length !=
> >> orderBy.length)
> >>                       rowNumParams = new Object[orderBy.length];
> >>               rowNumParams[0] = orderBy[0];
> >>               for(int i = 1; i < orderBy.length; i++){
> >>                       rowNumParams[i] = orderBy[i];
> >>                       sql.append(", {").append(i).append('}');
> >>               }
> >>               sql.append(')').append("--remove ? \n"); // HACK!!!
> >>
> >>               rowNum = new DBFuncExpr(orderBy[0].expr,
> sql.toString(),
> >> rowNumParams, null, false, DataType.INTEGER)
> >>                       .as("RowNum");
> >>               subCmd.select(rowNum);
> >>               subQuery = new DBQuery(subCmd);
> >>         this.select(subQuery.getQueryColumns());
> >>         this.select.remove(subQuery.findQueryColumn(etsr.UserID));
> >>
> this.where(subQuery.findQueryColumn(rowNum).isBetween(offset,
> >> offset + limit));
> >>       }
> >>
> >> Thanks,
> >>
> >> McKinley
> >>
> >> >>>> Thanks for reporting back. Did you use DESC in your ORDER BY?
> If
> >> you
> >> >>>> don't have wiki access you might consider posting the wiki
> markup
> >> in
> >> >>>> the issue tracker. I registered for the wiki, but didn't now
> get
> >> >>>> access by default.
> >> >>>>
> >> >>>> I'll be testing ROW_NUMBER on SQL Server tomorrow and will
let
> you
> >> >>>> know what I find.
> >

Mime
View raw message