incubator-empire-db-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Vladimir Tsanev <tsac...@gmail.com>
Subject Re: Rowlimits: OFFSET and LIMIT
Date Tue, 19 Jan 2010 14:36:41 GMT
Another approach to 2 might be to extend DBExpr - and implement addSql
method.
But I do not know what should be the implementation of addReferencedColumns
(the other abstract method).

On Tue, Jan 19, 2010 at 3:50 PM, McKinley <mckinley1411@gmail.com> wrote:

> On Tue, Jan 19, 2010 at 12:49 PM, Jaco van Tonder <Jacovt@tebabank.com>
> wrote:
> > 1.       Would it be possible to implement LIMIT and OFFSET functionality
> > for the databases that support it. Basically to limit the amount of
> records
> > that are fetched from the database. Oracle unfortunately does not support
> > this, which brings me to 2.
>
> It should be simple to do, but there are no stated goals for this
> right now. Here are links to the feature request and prior discussion.
>
> http://issues.apache.org/jira/browse/EMPIREDB-62
>
> http://www.mail-archive.com/empire-db-dev@incubator.apache.org/msg00545.html
>
> There are many responses in that thread toward the bottom of the page.
>
>
> > 2.       Can anyone explain to me how to construct an empire-db query to
> > create the following SQL:
> >
> > SELECT last_name FROM
> >
> >   (SELECT last_name, ROW_NUMBER() OVER (ORDER BY last_name) R FROM
> > employees)
> >
> >  WHERE R BETWEEN 51 and 100;
>
> You cannot do this currently unless you extend the DBCommand class and
> probably your DBMS appropriate driver class. I did implement TOP
> support for SQL Server, but I will not submit a patch for it as I do
> not think it is a good idea to encourage its use.
>
> You might be able to introduce a function into the driver SQL
> definitions with the opening "ROW_NUMBER() OVER (ORDER BY " and the
> closing of ")". It might just work, but I don't know if it will
> support multiple columns and especially the DESC keyword. Look at
> multi-column functions in the Empire-db driver for your DBMS and see
> if there is something that matches closely.
>
> If you want to support paging without using DBMS support you can do it
> but it may be slow. Here is an example of what the second page query
> might be. Your DBMS may not optimize the following.
>
> SELECT
>    UserID,
>    FirstName,
>    (SELECT COUNT(b.UserID) as PreviousCount
>        WHERE b.FirstName >= 'McKinley' and b.UserID <> 45 and
> b.FirstName <= a.FirstName
>        FROM User b) as PreviousCount
> FROM User a
> WHERE FirstName >= 'McKinley' and UserID <> 45
> and PreviousCount < 10
>
> In this example you will need to pass the last primary key value and
> the last sort value to the paging function (i.e. 45 and 'McKinley').
>
> Regards,
>
> McKinley
>

Mime
View raw message