db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Tim Dudgeon <tdudg...@informaticsmatters.com>
Subject Re: Restricted table function that narrows data
Date Fri, 13 May 2011 09:35:03 GMT
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.


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

View raw message