[
https://issues.apache.org/jira/browse/DERBY-6036?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13847246#comment-13847246
]
Richard Huddleston edited comment on DERBY-6036 at 12/13/13 8:25 AM:
---------------------------------------------------------------------
We are trying to work around this issue by calling the function directly
instead of the view, but have noticed that it would be much simpler if Derby
VTI exposed the function name to the VTI implementation ResultSet . i don't
believe there currently is a way to access the function name in the VTI
ResultSet class.
We are accessing n different remote data tables, each with its own table
definition. Thus in Derby, we must register n different functions. Note, we
only need 1 implementation class of the function to actually get the data and
return our custom ResultSet implementation. We'd prefer to NOT write a separate
class and redeploy our application each time someone adds a new remote data
table, as we have many actually derby instances running this codebase.
pseudo code / sql
we have to create register n function definitions in , for i in 1 .. n, where
tableName_i is our ith table name, (tableName_i is like customers)
register derby function selectFromRemote_tableName_i('tableName_i') java
method is selectFromRemote.read ,
then to call any specific function we have to write (pseudo sql)
select s.* from table ( selectFromRemote_customers('customers') ) s where
custId = 2
it would be MUCH cleaner if we could just
register a derby function with different names and no parameters
so instead of selectFromRemote_customers, we would just register function
"customers"
and derby function customers is java method selectFromRemote.read ,
and then be able to call
select c.* from table ( customers() ) c where custId = 2
then our ResultSet implementation could access some variable about the function
name, and we would use that in lieu of a function parameters (it would also
save us having to declare function params when we register functions, which is
tedious )
i think in the future you may want to add more information about VTI function
context,
would it be logical to add more stuff to VTIEnvironment and make VTIEnvironment
available / applicable to all VTI resultsets, not just those that implement
VTICosting ? side question, if we did implement VTICosting , is it guaranteed
that its methods will be called ?
perhaps there should be a new VTIContextAware interface that has something like
init(VTIContext context)
that VTIContext for now could have just getFunctionName, but it would be also
nice to have things like getFunctionComment so that instead of params to a
function, we could also dynamically change a function's behaviour by altering
JUST the comments of a regstered function, and NOT having to change all the
queries which call the function and pass arguments.
because that VTIContext is an interface you could continue to add methods to in
the future you expose more information to the function., note it would have
been nice if in RestrictedVTI, the initScan method took in an interface object
like InitScanParams which had currently getColumnNames and getRestriction, that
would probably have allowed this bug to be solved rather easily by just adding
"getFunctionName" on that InitScanParams interface.
Perhaps I should stop just being a writing critic and start writing something
myself how easy would it be for me to start hacking up and contributing to the
derby project myself ?
was (Author: rhuddusa):
We are trying to work around this issue by calling the function directly
instead of the view, but have noticed that it would be much simpler if Derby
VTI exposed the function name to the VTI implementation ResultSet . i don't
believe there currently is a way to access the function name in the VTI
ResultSet class.
We are accessing n different remote data tables, each with its own table
definition. Thus in Derby, we must register n different functions. Note, we
only need 1 implementation class of the function to actually get the data and
return our custom ResultSet implementation. We'd prefer to NOT write a separate
class and redeploy our application each time someone adds a new remote data
table, as we have many actually derby instances running this codebase.
pseudo code / sql
we have to create register n function definitions in , for i in 1 .. n, where
tableName_i is our ith table name, (tableName_i is like customers)
register derby function selectFromRemote_tableName_i('tableName_i') java
method is selectFromRemote.read ,
then to call any specific function we have to write (pseudo sql)
select s.* from table ( selectFromRemote_customers('customers') ) s where
custId = 2
it would be MUCH cleaner if we could just
register a derby function with different names and no parameters
so instead of selectFromRemote_customers, we would just register function
"customers"
and derby function customers is java method selectFromRemote.read ,
and then be able to call
select c.* from table ( customers() ) c where custId = 2
then our ResultSet implementation could access some variable about the function
name, and we would use that in lieu of a function parameters (it would also
save us having to declare function params when we register functions, which is
tedious )
i think in the future you may want to add more information about VTI function
context,
would it be logical to add more stuff to VTIEnvironment and make VTIEnvironment
available / applicable to all VTI resultsets, not just those that implement
VTICosting ? side question, if we did implement VTICosting , is it guaranteed
that its methods will be called ?
perhaps there should be a new VTIContextAware interface that has something like
init(VTIContext context)
and that VTIContext interface you could continue to add methods to in the
future you expose more information to the function., note it would have been
nice if in RestrictedVTI, the initScan method took in an interface object like
InitScanParams which had currently getColumnNames and getRestriction, that
would probably have allowed this bug to be solved rather easily by just adding
"getFunctionName" on that InitScanParams interface.
Perhaps I should stop just being a writing critic and start writing something
myself how easy would it be for me to start hacking up and contributing to the
derby project myself ?
> If you wrap a SELECT * view around a table, all of the columns are read from
> the base row even when you SELECT only a subset of the view columns.
> -------------------------------------------------------------------------------------------------------------------------------------------------
>
> Key: DERBY-6036
> URL: https://issues.apache.org/jira/browse/DERBY-6036
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.10.1.1
> Reporter: Rick Hillegas
> Labels: derby_triage10_11
> Attachments: derby-6036-01-aa-testForRestrictionPushing.diff,
> derby-6036.sql, derbyAST.xml
>
>
> This also affects SELECTs from views wrapping RestrictedVTIs. Restrictions
> are pushed into a restricted VTI if you wrap it in a view. However,
> projections are not. I will attach a script showing this problem.
--
This message was sent by Atlassian JIRA
(v6.1.4#6159)
