incubator-empire-db-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From McKinley <mckinley1...@gmail.com>
Subject Re: Rowlimits: OFFSET and LIMIT
Date Tue, 19 Jan 2010 15:06:20 GMT
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).

Mime
View raw message