You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@empire-db.apache.org by Rainer Döbele <do...@esteam.de> on 2010/01/28 21:46:35 UTC
making DBOrderByInfo public
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 <do...@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.
> >
Re: making DBOrderByInfo public
Posted by McKinley <mc...@gmail.com>.
Very nice API for order by. Thanks so much.
On Thu, Jan 28, 2010 at 9:41 PM, Rainer Döbele <do...@esteam.de> wrote:
> Done.
>
> DBOrderByInfo is now DBOrderByExpr and public.
>
> Rainer
>
> P.S. You may now write cmd.orderBy(C1.asc(), C2.desc(), C3.asc(), ...)
>
> Rainer Döbele wrote:
>> making DBOrderByInfo public
>>
>> Hi McKinley,
re: making DBOrderByInfo public
Posted by Rainer Döbele <do...@esteam.de>.
Done.
DBOrderByInfo is now DBOrderByExpr and public.
Rainer
P.S. You may now write cmd.orderBy(C1.asc(), C2.desc(), C3.asc(), ...)
Rainer Döbele wrote:
> making DBOrderByInfo public
>
> 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 <do...@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.
> > >