A context-aware table function is able to access context information that is passed in to it from Derby.
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 Derby 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 Derby jar files.
The following series of commands shows how to use the archiveVTI method, which is included in the Derby 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