Writing context-aware table functions
A context-aware table function is able to access context information
that is passed in to it from
.
functionscontext-aware table functions
Context-aware table functions are useful when both of the following are the
case:
- You want to bind a single Java method to many table functions, each of which
has a different row shape.
- You are able to determine the row shape, at runtime, from the
schema-qualified name of the table function which is being invoked.
A context-aware table function makes use of the
org.apache.derby.vti.AwareVTI interface and the
org.apache.derby.vti.VTIContext class. The VTIContext class, which
can be accessed through the AwareVTI interface, provides methods that
return the unqualified table function name, the name of the schema which holds
the table function, and the text of the statement which invoked the table
function. See the
public API documentation for more information about AwareVTI and
VTIContext.
For example, the ArchiveVTI table function performs a task
which many users have found useful: it provides a union of a main table with a
set of archive tables. The archive tables are created at regular intervals. When
a new archive table is created, the oldest rows from the main table are moved to
the archive table.
To use the ArchiveVTI table function, you need to include
derbyTesting.jar in your classpath along with other
jar files.
The following series of commands shows how to use the archiveVTI
method, which is included in the
test code. The source
code for the ArchiveVTI class is provided in the next topic.
In this
example, the method is bound to two table functions; one function returns a
three-column table, the other a two-column table.
java org.apache.derby.tools.ij
ij version 10.11
ij> connect 'jdbc:derby:memory:db;create=true';
ij> create table t1
(
keyCol int,
aCol int,
bCol int
);
0 rows inserted/updated/deleted
ij> create table t1_archive_001 as select * from t1 with no data;
0 rows inserted/updated/deleted
ij> create table t1_archive_002 as select * from t1 with no data;
0 rows inserted/updated/deleted
ij> insert into t1_archive_002 values ( 1, 100, 1000 ), ( 2, 200, 2000 ),
( 3, 300, 3000 );
3 rows inserted/updated/deleted
ij> insert into t1_archive_001 values ( 4, 400, 4000 ), ( 5, 500, 5000 ),
( 6, 600, 6000 );
3 rows inserted/updated/deleted
ij> insert into t1 values ( 7, 700, 7000 ), ( 8, 800, 8000 ),
( 9, 900, 9000 );
3 rows inserted/updated/deleted
ij> create table t2
(
keyCol int,
aCol int
);
0 rows inserted/updated/deleted
ij> create table t2_arc_001 as select * from t2 with no data;
0 rows inserted/updated/deleted
ij> create table t2_arc_002 as select * from t2 with no data;
0 rows inserted/updated/deleted
ij> insert into t2_arc_002 values ( 1, 100 ), ( 2, 200 ), ( 3, 300 );
3 rows inserted/updated/deleted
ij> insert into t2_arc_001 values ( 4, 400 ), ( 5, 500 ), ( 6, 600 );
3 rows inserted/updated/deleted
ij> insert into t2 values ( 7, 700 ), ( 8, 800 ), ( 9, 900 );
3 rows inserted/updated/deleted
ij> create function t1( archiveSuffix varchar( 32672 ) ) returns table
(
keyCol int,
aCol int,
bCol int
)
language java parameter style derby_jdbc_result_set reads sql data
external name
'org.apache.derbyTesting.functionTests.tests.lang.ArchiveVTI.archiveVTI';
0 rows inserted/updated/deleted
ij> create function t2( archiveSuffix varchar( 32672 ) ) returns table
(
keyCol int,
aCol int
)
language java parameter style derby_jdbc_result_set reads sql data
external name
'org.apache.derbyTesting.functionTests.tests.lang.ArchiveVTI.archiveVTI';
0 rows inserted/updated/deleted
ij> select * from table( t1( '_ARCHIVE_' ) ) s
where keyCol between 3 and 7
order by keyCol;
KEYCOL |ACOL |BCOL
-----------------------------------
3 |300 |3000
4 |400 |4000
5 |500 |5000
6 |600 |6000
7 |700 |7000
5 rows selected
ij> select * from table( t2( '_ARC_' ) ) s
where keyCol between 3 and 7
order by keyCol;
KEYCOL |ACOL
-----------------------
3 |300
4 |400
5 |500
6 |600
7 |700
5 rows selected