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