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 Jaco van Tonder <Ja...@tebabank.com> on 2010/01/19 13:49:57 UTC

Rowlimits: OFFSET and LIMIT

Hello Guys,

Two questions.


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.

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;

Note the Oracle function ROW_NUMBER() in the subselect statement.

Thanks in advance. :)
---Jaco

#####################################################################################
Attention:
The information contained in this message and or attachments is intended
only for the person or entity to which it is addressed and may contain
confidential and/or privileged material.  Any review, retransmission,
dissemination or other use of, or taking of any action in reliance upon,
this information by persons or entities other than the intended recipient
is prohibited. If you received this in error, please contact the sender and
delete the material from any system and destroy any copies.

Thank You.
#####################################################################################

#####################################################################################
This e-mail message has been scanned for Viruses and Content and cleared 
by MailMarshal
#####################################################################################

Re: Rowlimits: OFFSET and LIMIT

Posted by McKinley <mc...@gmail.com>.
FYI, it will likely be a subclass of DBAbstractFuncExpr or
DBColumnExpr on further thought.

On Tue, Jan 19, 2010 at 3:06 PM, McKinley <mc...@gmail.com> wrote:
> I'll create it as a subclass of DBFuncExpr

RE: Rowlimits: OFFSET and LIMIT

Posted by Jaco van Tonder <Ja...@tebabank.com>.
I had to make a couple minor adjustments to the original query to make it work properly. It seems if you do not alias the DBFuncExpr() AND the subquery you get weird results. Here is what works for me:


DBColumnExpr ROW_NUM = new DBFuncExpr(auditDb.T_AUDIT_RECORDS.C_ID, "ROW_NUMBER() OVER (ORDER BY ?)", null, null, false, DataType.INTEGER);

                DBCommand subCmd = auditDb.createCommand();
                subCmd.select(auditDb.T_AUDIT_RECORDS.C_ID);
                subCmd.select(auditDb.T_AUDIT_RECORDS.C_DESCRIPTION);
                subCmd.select(auditDb.T_AUDIT_RECORDS.C_APPLICATION);
                subCmd.select(auditDb.T_AUDIT_RECORDS.C_CATEGORY);
                subCmd.select(auditDb.T_AUDIT_RECORDS.C_UUID);
                subCmd.select(auditDb.T_AUDIT_RECORDS.C_DATE_CREATED);
                subCmd.select(auditDb.T_AUDIT_RECORDS.C_PAYLOAD);
                subCmd.select(ROW_NUM.as("R")); // <-- This seems to be important
                subCmd.where(auditDb.T_AUDIT_RECORDS.C_APPLICATION.is(applicationName));
                DBQuery subQuery = new DBQuery(subCmd);

                DBCommand mainCmd = auditDb.createCommand();
                mainCmd.select(subQuery.findQueryColumn(auditDb.T_AUDIT_RECORDS.C_ID));

                mainCmd.select(subQuery.findQueryColumn(auditDb.T_AUDIT_RECORDS.C_APPLICATION));
                mainCmd.select(subQuery.findQueryColumn(auditDb.T_AUDIT_RECORDS.C_VERSION));
                mainCmd.select(subQuery.findQueryColumn(auditDb.T_AUDIT_RECORDS.C_DESCRIPTION));
                mainCmd.select(subQuery.findQueryColumn(auditDb.T_AUDIT_RECORDS.C_CATEGORY));
                mainCmd.select(subQuery.findQueryColumn(auditDb.T_AUDIT_RECORDS.C_UUID));
                mainCmd.select(subQuery.findQueryColumn(auditDb.T_AUDIT_RECORDS.C_DATE_CREATED));
                mainCmd.select(subQuery.findQueryColumn(auditDb.T_AUDIT_RECORDS.C_PAYLOAD));
                mainCmd.where(subQuery.findQueryColumn(ROW_NUM.as("R")).isBetween(first, first + count));  // <-- Again. Note that first and count is offset and limit respectively.

Will see now if I can access the wiki. :)

I have not tried with a DESC order yet. Will see what happens, but I don't think it should be a problem as the rowid's are applied after the sort.

---Jaco



-----Original Message-----
From: McKinley [mailto:mckinley1411@gmail.com]
Sent: Friday, January 22, 2010 12:34 PM
To: empire-db-user@incubator.apache.org
Subject: Re: Rowlimits: OFFSET and LIMIT

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.

Thanks,

McKinley

On Fri, Jan 22, 2010 at 10:21 AM, Jaco van Tonder <Ja...@tebabank.com> wrote:
> Rainer,
>
> Thanks for this info. This works well for Oracle. I would like to document this on the wiki for future reference until a proper limit/offset implementation can be implemented.
>
> ---Jaco
>
#####################################################################################
Attention:
The information contained in this message and or attachments is intended
only for the person or entity to which it is addressed and may contain
confidential and/or privileged material.  Any review, retransmission,
dissemination or other use of, or taking of any action in reliance upon,
this information by persons or entities other than the intended recipient
is prohibited. If you received this in error, please contact the sender and
delete the material from any system and destroy any copies.

Thank You.
#####################################################################################

#####################################################################################
This e-mail message has been scanned for Viruses and Content and cleared 
by MailMarshal
#####################################################################################

Re: Rowlimits: OFFSET and LIMIT

Posted by Martijn Dashorst <ma...@gmail.com>.
On Fri, Jan 22, 2010 at 2:17 PM, Francis De Brabandere
<fr...@gmail.com> wrote:
> McKinley, you should have write access now I think. But I am not sure
> if we should ask you to first sign a ICLA
> (http://www.apache.org/licenses/)

Unless empire-db decides to distribute the contents of the WIKI, use
it as the official website (i.e.
http://incubator.apache.org/empire-db, and later on
http://empire-db.apache.org) or make it more official than a user
wiki/writeboard the ICLA is not mandatory.

That said, submitting an ICLA makes it much easier to become a
committer and to have your contributions included in any Apache
project that I urge anyone aspiring more than the occasional bug
report to send in an ICLA. You might never know when you get invited
to the party.

Martijn

Re: Rowlimits: OFFSET and LIMIT

Posted by Francis De Brabandere <fr...@gmail.com>.
Jaco, could you create a wiki user account on:
http://cwiki.apache.org/confluence/display/empiredb/ so that I can add
you

McKinley, you should have write access now I think. But I am not sure
if we should ask you to first sign a ICLA
(http://www.apache.org/licenses/)

Martijn, could you help us out here?

Cheers,
Francis

On Fri, Jan 22, 2010 at 2:02 PM, Francis De Brabandere
<fr...@gmail.com> wrote:
> I'll ask on the infra list how we can give RW access
>
> On Fri, Jan 22, 2010 at 11:34 AM, McKinley <mc...@gmail.com> wrote:
>> 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.
>>
>> Thanks,
>>
>> McKinley
>>
>> On Fri, Jan 22, 2010 at 10:21 AM, Jaco van Tonder <Ja...@tebabank.com> wrote:
>>> Rainer,
>>>
>>> Thanks for this info. This works well for Oracle. I would like to document this on the wiki for future reference until a proper limit/offset implementation can be implemented.
>>>
>>> ---Jaco
>>>
>>
>
>
>
> --
> http://www.somatik.be
> Microsoft gives you windows, Linux gives you the whole house.
>



-- 
http://www.somatik.be
Microsoft gives you windows, Linux gives you the whole house.

Re: Rowlimits: OFFSET and LIMIT

Posted by Francis De Brabandere <fr...@gmail.com>.
I'll ask on the infra list how we can give RW access

On Fri, Jan 22, 2010 at 11:34 AM, McKinley <mc...@gmail.com> wrote:
> 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.
>
> Thanks,
>
> McKinley
>
> On Fri, Jan 22, 2010 at 10:21 AM, Jaco van Tonder <Ja...@tebabank.com> wrote:
>> Rainer,
>>
>> Thanks for this info. This works well for Oracle. I would like to document this on the wiki for future reference until a proper limit/offset implementation can be implemented.
>>
>> ---Jaco
>>
>



-- 
http://www.somatik.be
Microsoft gives you windows, Linux gives you the whole house.

Re: Rowlimits: OFFSET and LIMIT

Posted by McKinley <mc...@gmail.com>.
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.

Thanks,

McKinley

On Fri, Jan 22, 2010 at 10:21 AM, Jaco van Tonder <Ja...@tebabank.com> wrote:
> Rainer,
>
> Thanks for this info. This works well for Oracle. I would like to document this on the wiki for future reference until a proper limit/offset implementation can be implemented.
>
> ---Jaco
>

RE: Rowlimits: OFFSET and LIMIT

Posted by Jaco van Tonder <Ja...@tebabank.com>.
Rainer,

Thanks for this info. This works well for Oracle. I would like to document this on the wiki for future reference until a proper limit/offset implementation can be implemented.

---Jaco

-----Original Message-----
From: Rainer Döbele [mailto:doebele@esteam.de]
Sent: Tuesday, January 19, 2010 10:27 PM
To: empire-db-user@incubator.apache.org
Subject: re: Rowlimits: OFFSET and LIMIT

Hi,

If you have not found out already, the simplest way to implement the row number expression is:

DBColumnExpr ROW_NUM = new DBFuncExpr(EMP.PHONE_NUMBER, "ROW_NUMBER() OVER (ORDER BY ?)", null, null, false, DataType.INTEGER);
cmd.select(ROW_NUM);
cmd.where(ROW_NUM.isBetween(51, 100));

You may also use that in a subquery, by wrapping it with a DBQuery and then obtaining the Row Numer by using findQueryColumn(...)

DBQuery q = new DBQuery(cmd);
DBColumnExpr Q_RN = q.findQueryColumn(ROW_NUM);

Since not all databases support ROW_NUM(), I am not quite sure how to best integtate it.
When providing a feature people expect it to work for all databases and they are dissappointed when the the a runtime exeption just because their database does not. The other option is to implement it only for a particular database (as e.g. with Oracle's connect by prior in DBCommandOracle) but then people may not find it and it's inconvient since it requires a type cast.

The same applies to Limit and Offset.

But thanks everyone for your links, they are very helpful.

Regards
Rainer


Jaco van Tonder wrote:
> RE: Rowlimits: OFFSET and LIMIT
>
> I too, am a bit cautious of using addSql() (SQL injection comes to
> mind). :P
>
> McKinley: Here is what I found. Seems like Oracle and MSSQL is pretty
> close to each other with regards to the window functions (OVER...).
> http://www.troels.arvin.dk/db/rdbms/#select-limit-offset
>
> I just had a look at the hibernate dialect for Oracle and it seems that
> they are implementing the LIMIT and OFFSET using the Window Function
> method.
> http://viewvc.jboss.org/cgi-
> bin/viewvc.cgi/hibernate/core/trunk/core/src/main/java/org/hibernate/di
> alect/Oracle9Dialect.java?view=markup
> Look at the getLimitString() method.
>
> MSSQL uses the TOP syntax:
> http://viewvc.jboss.org/cgi-
> bin/viewvc.cgi/hibernate/core/trunk/core/src/main/java/org/hibernate/di
> alect/SQLServerDialect.java?view=markup
>
> PostgreSQL/MySQL uses the LIMIT OFFSET method:
> http://viewvc.jboss.org/cgi-
> bin/viewvc.cgi/hibernate/core/trunk/core/src/main/java/org/hibernate/di
> alect/PostgreSQLDialect.java?view=markup
>
> Wondering how difficult it would be to "port" that functionality?
>
> ---Jaco
>
>
> -----Original Message-----
> From: McKinley [mailto:mckinley1411@gmail.com]
> Sent: Tuesday, January 19, 2010 5:06 PM
> To: empire-db-user@incubator.apache.org
> Subject: Re: Rowlimits: OFFSET and LIMIT
>
> I am afraid that an addSql method would quickly turn into an
> addSqlInjection method if you catch my meaning. All the pieces are
> there already to support ROW_NUMBER OVER (ORDER BY ...).  They just
> need to be abstracted from their current use as necessary and
> integrated into the function support.
>
> In fact, I have to make use of ROW_NUMBER OVER (ORDER BY ...) in SQL
> Server 2005 for an upcoming project. I'll implement it for Oracle too
> if someone can show me the necessary Oracle documentation.
>
> I'll create it as a subclass of DBFuncExpr with an internal
> ArrayList<DBOrderByInfo> with all the usual orderBy and clearOrderBy
> methods.  The orderBy will be housed in some sort of "over" object.
> "over" will also house the partitionBy and clearPartitionBy methods. I
> will probably *not* throw an error upon SQL generation if the
> partitionBy contains a column not in the FROM clause unless anyone can
> tell me that it is not safe. Your DBMS will catch the error. Also, I
> will not do any checks on orderBy for the same if you combine this
> with a ranking function. Again, the DBMS will catch the error and it
> is within your control to conditionally modify the ROW_NUMBER function
> partionBy and orderBy and the FROM to make sure your query will
> actually run.
>
> I'll submit the patch to the issue track on Saturday and post a link
> to this thread.
>
> Thanks,
>
> McKinley
>
> On Tue, Jan 19, 2010 at 2:36 PM, Vladimir Tsanev <ts...@gmail.com>
> wrote:
> > 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).
> #######################################################################
> ##############
> Attention:
> The information contained in this message and or attachments is
> intended
> only for the person or entity to which it is addressed and may contain
> confidential and/or privileged material.  Any review, retransmission,
> dissemination or other use of, or taking of any action in reliance
> upon,
> this information by persons or entities other than the intended
> recipient
> is prohibited. If you received this in error, please contact the sender
> and
> delete the material from any system and destroy any copies.
>
> Thank You.
> #######################################################################
> ##############
>
> #######################################################################
> ##############
> This e-mail message has been scanned for Viruses and Content and
> cleared
> by MailMarshal
> #######################################################################
> ##############
#####################################################################################
Attention:
The information contained in this message and or attachments is intended
only for the person or entity to which it is addressed and may contain
confidential and/or privileged material.  Any review, retransmission,
dissemination or other use of, or taking of any action in reliance upon,
this information by persons or entities other than the intended recipient
is prohibited. If you received this in error, please contact the sender and
delete the material from any system and destroy any copies.

Thank You.
#####################################################################################

#####################################################################################
This e-mail message has been scanned for Viruses and Content and cleared 
by MailMarshal
#####################################################################################

re: Rowlimits: OFFSET and LIMIT

Posted by Rainer Döbele <do...@esteam.de>.
The ? may only be used once per template and it is replaced a source column that this expression is based on.
Most functions are based on a single source expression which is provided as the first param of DBFuncExpr like e.g. most string functions and aggregations.

The params parameter is for additional parameters that the sql function needs.
For each parameter a placeholder with a number wrapped in curly brackets is required in the template.
Take e.g. the substring function. It requires a source column and two additional params: the offset and the length.
Hence the template is "substring(?, {0}, {1})".

Most (but not all) column transformations can be declared using a DBFuncExpr.

Regards
Rainer


McKinley wrote:
> Re: Rowlimits: OFFSET and LIMIT
> 
> Thanks Rainer! Do you happen to know what the behavior on ? in the
> template when params is not null null? Does it ignore the first
> argument expr? I ask this because a more rigorous usage would probably
> look like this:
> 
> Object[] params = {tableA.City, tableA.State, new
> DBOrderByInfo(tableA.Age, true), tableA.LastName, tableA.FirstName};
> DBFuncExpr(..., ROW_NUMBER() OVER (PARTITION BY ?, ? ORDER BY ?, ?, ?
> ), params, ...)
> 
> Looking at the addSql methods that get called the Object[] params do
> get type checked for by DBExpr so I suppose the proper 'DESC' strings
> will get appended. This may be all that is needed then for most of us
> to proceed with some of our trickiest requirements. Formal support
> could be added in the future, but this function template looks like it
> will work.
> 
> Thanks,
> 
> McKinley
> 
> On Tue, Jan 19, 2010 at 8:26 PM, Rainer Döbele <do...@esteam.de>
> wrote:
> > Hi,
> >
> > If you have not found out already, the simplest way to implement the
> row number expression is:
> >
> > DBColumnExpr ROW_NUM = new DBFuncExpr(EMP.PHONE_NUMBER, "ROW_NUMBER()
> OVER (ORDER BY ?)", null, null, false, DataType.INTEGER);
> > cmd.select(ROW_NUM);
> > cmd.where(ROW_NUM.isBetween(51, 100));
> >

Re: Rowlimits: OFFSET and LIMIT

Posted by McKinley <mc...@gmail.com>.
Thanks Rainer! Do you happen to know what the behavior on ? in the
template when params is not null null? Does it ignore the first
argument expr? I ask this because a more rigorous usage would probably
look like this:

Object[] params = {tableA.City, tableA.State, new
DBOrderByInfo(tableA.Age, true), tableA.LastName, tableA.FirstName};
DBFuncExpr(..., ROW_NUMBER() OVER (PARTITION BY ?, ? ORDER BY ?, ?, ?
), params, ...)

Looking at the addSql methods that get called the Object[] params do
get type checked for by DBExpr so I suppose the proper 'DESC' strings
will get appended. This may be all that is needed then for most of us
to proceed with some of our trickiest requirements. Formal support
could be added in the future, but this function template looks like it
will work.

Thanks,

McKinley

On Tue, Jan 19, 2010 at 8:26 PM, Rainer Döbele <do...@esteam.de> wrote:
> Hi,
>
> If you have not found out already, the simplest way to implement the row number expression is:
>
> DBColumnExpr ROW_NUM = new DBFuncExpr(EMP.PHONE_NUMBER, "ROW_NUMBER() OVER (ORDER BY ?)", null, null, false, DataType.INTEGER);
> cmd.select(ROW_NUM);
> cmd.where(ROW_NUM.isBetween(51, 100));
>

re: Rowlimits: OFFSET and LIMIT

Posted by Rainer Döbele <do...@esteam.de>.
Hi,

If you have not found out already, the simplest way to implement the row number expression is:

DBColumnExpr ROW_NUM = new DBFuncExpr(EMP.PHONE_NUMBER, "ROW_NUMBER() OVER (ORDER BY ?)", null, null, false, DataType.INTEGER);
cmd.select(ROW_NUM);
cmd.where(ROW_NUM.isBetween(51, 100));

You may also use that in a subquery, by wrapping it with a DBQuery and then obtaining the Row Numer by using findQueryColumn(...) 

DBQuery q = new DBQuery(cmd);
DBColumnExpr Q_RN = q.findQueryColumn(ROW_NUM);

Since not all databases support ROW_NUM(), I am not quite sure how to best integtate it.
When providing a feature people expect it to work for all databases and they are dissappointed when the the a runtime exeption just because their database does not. The other option is to implement it only for a particular database (as e.g. with Oracle's connect by prior in DBCommandOracle) but then people may not find it and it's inconvient since it requires a type cast.

The same applies to Limit and Offset.

But thanks everyone for your links, they are very helpful.

Regards
Rainer


Jaco van Tonder wrote:
> RE: Rowlimits: OFFSET and LIMIT
> 
> I too, am a bit cautious of using addSql() (SQL injection comes to
> mind). :P
> 
> McKinley: Here is what I found. Seems like Oracle and MSSQL is pretty
> close to each other with regards to the window functions (OVER...).
> http://www.troels.arvin.dk/db/rdbms/#select-limit-offset
> 
> I just had a look at the hibernate dialect for Oracle and it seems that
> they are implementing the LIMIT and OFFSET using the Window Function
> method.
> http://viewvc.jboss.org/cgi-
> bin/viewvc.cgi/hibernate/core/trunk/core/src/main/java/org/hibernate/di
> alect/Oracle9Dialect.java?view=markup
> Look at the getLimitString() method.
> 
> MSSQL uses the TOP syntax:
> http://viewvc.jboss.org/cgi-
> bin/viewvc.cgi/hibernate/core/trunk/core/src/main/java/org/hibernate/di
> alect/SQLServerDialect.java?view=markup
> 
> PostgreSQL/MySQL uses the LIMIT OFFSET method:
> http://viewvc.jboss.org/cgi-
> bin/viewvc.cgi/hibernate/core/trunk/core/src/main/java/org/hibernate/di
> alect/PostgreSQLDialect.java?view=markup
> 
> Wondering how difficult it would be to "port" that functionality?
> 
> ---Jaco
> 
> 
> -----Original Message-----
> From: McKinley [mailto:mckinley1411@gmail.com]
> Sent: Tuesday, January 19, 2010 5:06 PM
> To: empire-db-user@incubator.apache.org
> Subject: Re: Rowlimits: OFFSET and LIMIT
> 
> I am afraid that an addSql method would quickly turn into an
> addSqlInjection method if you catch my meaning. All the pieces are
> there already to support ROW_NUMBER OVER (ORDER BY ...).  They just
> need to be abstracted from their current use as necessary and
> integrated into the function support.
> 
> In fact, I have to make use of ROW_NUMBER OVER (ORDER BY ...) in SQL
> Server 2005 for an upcoming project. I'll implement it for Oracle too
> if someone can show me the necessary Oracle documentation.
> 
> I'll create it as a subclass of DBFuncExpr with an internal
> ArrayList<DBOrderByInfo> with all the usual orderBy and clearOrderBy
> methods.  The orderBy will be housed in some sort of "over" object.
> "over" will also house the partitionBy and clearPartitionBy methods. I
> will probably *not* throw an error upon SQL generation if the
> partitionBy contains a column not in the FROM clause unless anyone can
> tell me that it is not safe. Your DBMS will catch the error. Also, I
> will not do any checks on orderBy for the same if you combine this
> with a ranking function. Again, the DBMS will catch the error and it
> is within your control to conditionally modify the ROW_NUMBER function
> partionBy and orderBy and the FROM to make sure your query will
> actually run.
> 
> I'll submit the patch to the issue track on Saturday and post a link
> to this thread.
> 
> Thanks,
> 
> McKinley
> 
> On Tue, Jan 19, 2010 at 2:36 PM, Vladimir Tsanev <ts...@gmail.com>
> wrote:
> > 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).
> #######################################################################
> ##############
> Attention:
> The information contained in this message and or attachments is
> intended
> only for the person or entity to which it is addressed and may contain
> confidential and/or privileged material.  Any review, retransmission,
> dissemination or other use of, or taking of any action in reliance
> upon,
> this information by persons or entities other than the intended
> recipient
> is prohibited. If you received this in error, please contact the sender
> and
> delete the material from any system and destroy any copies.
> 
> Thank You.
> #######################################################################
> ##############
> 
> #######################################################################
> ##############
> This e-mail message has been scanned for Viruses and Content and
> cleared
> by MailMarshal
> #######################################################################
> ##############

Re: Rowlimits: OFFSET and LIMIT

Posted by McKinley <mc...@gmail.com>.
On Tue, Jan 19, 2010 at 3:56 PM, Jaco van Tonder <Ja...@tebabank.com> wrote:
> McKinley: Here is what I found. Seems like Oracle and MSSQL is pretty close to each other with regards to the window functions (OVER...). http://www.troels.arvin.dk/db/rdbms/#select-limit-offset

Thanks for the links. Very thorough. However, I did not see the
partition clause mentioned which I know exists for SQL Server. Does it
exist for Oracle?

> MSSQL uses the TOP syntax:
> http://viewvc.jboss.org/cgi-bin/viewvc.cgi/hibernate/core/trunk/core/src/main/java/org/hibernate/dialect/SQLServerDialect.java?view=markup

Could MSSQL use the windowing function only and not TOP? It seems like
to me it can. Perhaps they are using TOP to support SQL Server 2000.

> Wondering how difficult it would be to "port" that functionality?

It will not be difficult in the slightest. However, ROW_NUMBER OVER()
works very different from LIMIT and OFFSET in that ROW_NUMBER OVER()
requires that it be a sub query to be limited.  I think limit and
offset would be the first DBCommand level methods that would so
radically alter a query. Because ROW_NUMBER OVER() is already not
portable, perhaps the limit and offset should not automatically sub
query the original DBCommand. It would probably be best to make the
API user only apply limit and offset to sub queries. The DBMS can
catch their error.

For now my patch will require you to use ROW_NUMBER OVER() with
BETWEEN, >, < etc. I will not abstract it into limit and offset yet.

Thanks,

McKinley

RE: Rowlimits: OFFSET and LIMIT

Posted by Jaco van Tonder <Ja...@tebabank.com>.
I too, am a bit cautious of using addSql() (SQL injection comes to mind). :P

McKinley: Here is what I found. Seems like Oracle and MSSQL is pretty close to each other with regards to the window functions (OVER...). http://www.troels.arvin.dk/db/rdbms/#select-limit-offset

I just had a look at the hibernate dialect for Oracle and it seems that they are implementing the LIMIT and OFFSET using the Window Function method.
http://viewvc.jboss.org/cgi-bin/viewvc.cgi/hibernate/core/trunk/core/src/main/java/org/hibernate/dialect/Oracle9Dialect.java?view=markup
Look at the getLimitString() method.

MSSQL uses the TOP syntax:
http://viewvc.jboss.org/cgi-bin/viewvc.cgi/hibernate/core/trunk/core/src/main/java/org/hibernate/dialect/SQLServerDialect.java?view=markup

PostgreSQL/MySQL uses the LIMIT OFFSET method:
http://viewvc.jboss.org/cgi-bin/viewvc.cgi/hibernate/core/trunk/core/src/main/java/org/hibernate/dialect/PostgreSQLDialect.java?view=markup

Wondering how difficult it would be to "port" that functionality?

---Jaco


-----Original Message-----
From: McKinley [mailto:mckinley1411@gmail.com]
Sent: Tuesday, January 19, 2010 5:06 PM
To: empire-db-user@incubator.apache.org
Subject: Re: Rowlimits: OFFSET and LIMIT

I am afraid that an addSql method would quickly turn into an
addSqlInjection method if you catch my meaning. All the pieces are
there already to support ROW_NUMBER OVER (ORDER BY ...).  They just
need to be abstracted from their current use as necessary and
integrated into the function support.

In fact, I have to make use of ROW_NUMBER OVER (ORDER BY ...) in SQL
Server 2005 for an upcoming project. I'll implement it for Oracle too
if someone can show me the necessary Oracle documentation.

I'll create it as a subclass of DBFuncExpr with an internal
ArrayList<DBOrderByInfo> with all the usual orderBy and clearOrderBy
methods.  The orderBy will be housed in some sort of "over" object.
"over" will also house the partitionBy and clearPartitionBy methods. I
will probably *not* throw an error upon SQL generation if the
partitionBy contains a column not in the FROM clause unless anyone can
tell me that it is not safe. Your DBMS will catch the error. Also, I
will not do any checks on orderBy for the same if you combine this
with a ranking function. Again, the DBMS will catch the error and it
is within your control to conditionally modify the ROW_NUMBER function
partionBy and orderBy and the FROM to make sure your query will
actually run.

I'll submit the patch to the issue track on Saturday and post a link
to this thread.

Thanks,

McKinley

On Tue, Jan 19, 2010 at 2:36 PM, Vladimir Tsanev <ts...@gmail.com> wrote:
> 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).
#####################################################################################
Attention:
The information contained in this message and or attachments is intended
only for the person or entity to which it is addressed and may contain
confidential and/or privileged material.  Any review, retransmission,
dissemination or other use of, or taking of any action in reliance upon,
this information by persons or entities other than the intended recipient
is prohibited. If you received this in error, please contact the sender and
delete the material from any system and destroy any copies.

Thank You.
#####################################################################################

#####################################################################################
This e-mail message has been scanned for Viruses and Content and cleared 
by MailMarshal
#####################################################################################

Re: Rowlimits: OFFSET and LIMIT

Posted by McKinley <mc...@gmail.com>.
I am afraid that an addSql method would quickly turn into an
addSqlInjection method if you catch my meaning. All the pieces are
there already to support ROW_NUMBER OVER (ORDER BY ...).  They just
need to be abstracted from their current use as necessary and
integrated into the function support.

In fact, I have to make use of ROW_NUMBER OVER (ORDER BY ...) in SQL
Server 2005 for an upcoming project. I'll implement it for Oracle too
if someone can show me the necessary Oracle documentation.

I'll create it as a subclass of DBFuncExpr with an internal
ArrayList<DBOrderByInfo> with all the usual orderBy and clearOrderBy
methods.  The orderBy will be housed in some sort of "over" object.
"over" will also house the partitionBy and clearPartitionBy methods. I
will probably *not* throw an error upon SQL generation if the
partitionBy contains a column not in the FROM clause unless anyone can
tell me that it is not safe. Your DBMS will catch the error. Also, I
will not do any checks on orderBy for the same if you combine this
with a ranking function. Again, the DBMS will catch the error and it
is within your control to conditionally modify the ROW_NUMBER function
partionBy and orderBy and the FROM to make sure your query will
actually run.

I'll submit the patch to the issue track on Saturday and post a link
to this thread.

Thanks,

McKinley

On Tue, Jan 19, 2010 at 2:36 PM, Vladimir Tsanev <ts...@gmail.com> wrote:
> 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).

Re: Rowlimits: OFFSET and LIMIT

Posted by Vladimir Tsanev <ts...@gmail.com>.
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 <mc...@gmail.com> wrote:

> On Tue, Jan 19, 2010 at 12:49 PM, Jaco van Tonder <Ja...@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
>

Re: Rowlimits: OFFSET and LIMIT

Posted by McKinley <mc...@gmail.com>.
On Tue, Jan 19, 2010 at 12:49 PM, Jaco van Tonder <Ja...@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