incubator-empire-db-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Jaco van Tonder <Jac...@tebabank.com>
Subject RE: Rowlimits: OFFSET and LIMIT
Date Tue, 19 Jan 2010 15:56:23 GMT
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 <tsachev@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
#####################################################################################

Mime
View raw message