You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-user@db.apache.org by Tim Dudgeon <td...@informaticsmatters.com> on 2011/05/11 18:02:46 UTC
Restricted table function that narrows data
I'm trying to write a restricted table function that retrieves data from
a different database and needing some clarification on how the
"narrowing" of the data is supposed to work. The docs mentions that
initScan() is called like this:
initScan( new String[] { "ID", null, null, "FIRSTNAME", "LASTNAME" } ...)
e.g. that null values are specified for the unneeded columns.
Then the docs continues:
For each row, Derby calls:
MyVTIClass.getInt( 1 ) to get the id column.
MyVTIClass.getString( 4 ) to get the firstName column.
MyVTIClass.getString( 5 ) to get the lastName column.
e.g. Derby expects the ResultSet to be as wide as the full un-restricted
ResultSet
How is this supposed to work? Are columns 2 and 3 supposed to be
supplied, but have null values so that they do not have to be retreived
from the second database.
e.g. something like
select id, null, null, firstname, lastname from employee
(though that syntax is not legal).
Any advice gratefully received.
Thanks
Tim
Re: Restricted table function that narrows data
Posted by Tim Dudgeon <td...@informaticsmatters.com>.
Hi Rick,
Thanks for the info. Yes, I can confirm it works as you described.
For now I only need the simplistic approach, and seem to have this
mostly working, but your ForeignTableVTI class looks interesting, and
certainly the direction I was thinking of going in.
I do see some issues with the org.apache.derby.vti.Restriction classes,
but I'll ask that as a separate thread to keep things simple.
Thanks
Tim
On 11/05/2011 17:47, Rick Hillegas wrote:
>> I'm trying to write a restricted table function that retrieves data
>> from a different database and needing some clarification on how the
>> "narrowing" of the data is supposed to work. The docs mentions that
>> initScan() is called like this:
>>
>> initScan( new String[] { "ID", null, null, "FIRSTNAME", "LASTNAME" } ...)
>>
>> e.g. that null values are specified for the unneeded columns.
>> Then the docs continues:
>>
>> For each row, Derby calls:
>> MyVTIClass.getInt( 1 ) to get the id column.
>> MyVTIClass.getString( 4 ) to get the firstName column.
>> MyVTIClass.getString( 5 ) to get the lastName column.
>>
>> e.g. Derby expects the ResultSet to be as wide as the full
>> un-restricted ResultSet
>>
>> How is this supposed to work? Are columns 2 and 3 supposed to be
>> supplied, but have null values so that they do not have to be
>> retreived from the second database.
>> e.g. something like
>>
>> select id, null, null, firstname, lastname from employee
>>
>> (though that syntax is not legal).
>>
>> Any advice gratefully received.
>>
>> Thanks
>>
>> Tim
>>
>>
> Hi Tim,
>
> Your understanding of how RestrictedVTIs work is correct. One way to
> solve the problem is to write a ResultSet which supplies null values for
> the unused columns. Another way to solve the problem is to write a
> ResultSet which internally selects a smaller row and then remaps column
> ids, taking advantage of the fact that Derby will never ask for the
> unused columns.
>
> You might want to take a look at ForeignTableVTI, attached to
> https://issues.apache.org/jira/browse/DERBY-4962. This table function
> does exactly what you want. However, instead of cooking up
>
> select id, cast( null as int), cast( null as int), firstname, lastname
> from employee
>
> the table function cooks up
>
> select id, firstname, lastname from employee
>
> The magic is in the makeQuery() and mapColumnNumber() methods.
>
> You may want to subset ForeignTableVTI because it provides some related
> database procedures which you don't need: procedures to register and
> deregister vtis against every table in your foreign schema.
>
> Let me know if this is still muddled. I think what you need to do sounds
> fairly straightforward.
>
> Thanks,
> -Rick
>
>
Re: Restricted table function that narrows data
Posted by Rick Hillegas <ri...@oracle.com>.
> I'm trying to write a restricted table function that retrieves data
> from a different database and needing some clarification on how the
> "narrowing" of the data is supposed to work. The docs mentions that
> initScan() is called like this:
>
> initScan( new String[] { "ID", null, null, "FIRSTNAME", "LASTNAME" } ...)
>
> e.g. that null values are specified for the unneeded columns.
> Then the docs continues:
>
> For each row, Derby calls:
> MyVTIClass.getInt( 1 ) to get the id column.
> MyVTIClass.getString( 4 ) to get the firstName column.
> MyVTIClass.getString( 5 ) to get the lastName column.
>
> e.g. Derby expects the ResultSet to be as wide as the full
> un-restricted ResultSet
>
> How is this supposed to work? Are columns 2 and 3 supposed to be
> supplied, but have null values so that they do not have to be
> retreived from the second database.
> e.g. something like
>
> select id, null, null, firstname, lastname from employee
>
> (though that syntax is not legal).
>
> Any advice gratefully received.
>
> Thanks
>
> Tim
>
>
Hi Tim,
Your understanding of how RestrictedVTIs work is correct. One way to
solve the problem is to write a ResultSet which supplies null values for
the unused columns. Another way to solve the problem is to write a
ResultSet which internally selects a smaller row and then remaps column
ids, taking advantage of the fact that Derby will never ask for the
unused columns.
You might want to take a look at ForeignTableVTI, attached to
https://issues.apache.org/jira/browse/DERBY-4962. This table function
does exactly what you want. However, instead of cooking up
select id, cast( null as int), cast( null as int), firstname,
lastname from employee
the table function cooks up
select id, firstname, lastname from employee
The magic is in the makeQuery() and mapColumnNumber() methods.
You may want to subset ForeignTableVTI because it provides some related
database procedures which you don't need: procedures to register and
deregister vtis against every table in your foreign schema.
Let me know if this is still muddled. I think what you need to do sounds
fairly straightforward.
Thanks,
-Rick