Apache Derby Database Engine and Embedded JDBC Driver 10.17.1.0 · org.apache.derby.vti

Support for Table Functions.

Derby lets you declare functions which return ResultSets. You can then use these function results as tables in your queries. This, in turn, lets you do the following:

Migrate - Bulk-load data from an external database. The external data source could be any vendor's database.
Integrate - Transform live data from an existing legacy server and load the data into Derby. This lets users build new Derby-powered apps against subsets of legacy data, keep the data current, but limit the burden which the new apps place on the legacy server.
Snapshot - Copy a subset of server data to a laptop before travelling.
Federate - Join data from multiple external data sources. The external sources could be other relational databases or they could be non-relational data feeds.

Here is an example of how to declare and invoke a Table Function:


CREATE FUNCTION externalEmployees
()
RETURNS TABLE
(
  employeeId    INT,
  lastName       VARCHAR( 50 ),
  firstName      VARCHAR( 50 ),
  birthday         DATE
)
LANGUAGE JAVA
PARAMETER STYLE DERBY_JDBC_RESULT_SET
NO SQL
EXTERNAL NAME 'com.acme.hrSchema.EmployeesTable.read'
;

INSERT INTO employees
  SELECT s.*
    FROM TABLE (externalEmployees() ) s;

The Derby optimizer makes some assumptions about these Table Functions:

Cost - The optimizer hard-codes a guess about how expensive it is to materialize a Table Function.
Count - The optimizer also hard-codes a guess about how many rows a Table Function returns.
Repeatability - The optimizer assumes that the same results come back each time you invoke a Table Function.

Based on these assumptions, the optimizer decides where to place the Table Function in the join order. Using the interfaces in this package, you may override the optimizer's guesses and force the optimizer to choose a better join order.

VTICosting - This interface exposes methods which let you override the optimizer's guesses.
VTIEnvironment - This is a state variable, created by the optimizer and passed to the methods in VTICosting. VTICosting methods use this state variable to communicate with one another and learn more about the operating environment.

Interface Summary

Modifier and Type	Interface and Description
public interface	AwareVTI Interface describing a table function which can be given information about the context in which it runs.
public interface	DeferModification This interface is implemented by a read/write VTI class that wants to control when modifications to the VTI are deferred, or to be notified that a it is to be modified.
public interface	IFastPath An internal api for VTIs to allow VTI's written in terms of the datatype system, e.g. returning rows.
public interface	IQualifyable
public interface	Pushable Support for pushing SQL statement information down into a virtual table.
public interface	RestrictedVTI Interface for Table Functions which can be told which columns need to be fetched plus simple bounds on those columns.
public interface	VTICosting VTICosting is the interface that the query optimizer uses to cost Table Functions.
public interface	VTIEnvironment VTIEnvironment is the state variable created by the optimizer to help it place a Table Function in the join order.

Class Summary

Modifier and Type	Class and Description
public class	ForeignTableVTI This class contains a table function which can be used to bulk-import data from a foreign database.
public class	ForwardingVTI This class contains a table function which forwards its behavior to another ResultSet wrapped inside it.
public abstract class	Restriction An expression to be pushed into a Table Function so that the Table Function can short-circuit its processing and return fewer rows.
public abstract class	StringColumnVTI This is an abstract table function which assumes that all columns are strings and which coerces the strings to reasonable values for various getXXX() methods.
public abstract class	UpdatableVTITemplate An abstract implementation of PreparedStatement (JDBC 3.0) that is useful when writing a read-write (updatable) virtual table interface (VTI).
public class	VTIContext Context parameter which is passed to an AwareVTI.
public abstract class	VTIMetaDataTemplate An abstract implementation of ResultSetMetaData (JDBC 1.2) that is useful when writing a VTI (virtual table interface).
public abstract class	VTITemplate An abstract implementation of ResultSet that is useful when writing table functions, read-only VTIs (virtual table interface), and the ResultSets returned by executeQuery in read-write VTI classes.
public class	XmlVTI This is a VTI designed to read XML files which are structured like row sets.

Package org.apache.derby.vti

Support for Table Functions.

Interface Summary

Class Summary