org.apache.derby.vti

Class ForeignTableVTI

java.lang.Object

org.apache.derby.vti.VTITemplateBase

org.apache.derby.vti.VTITemplate

org.apache.derby.vti.ForeignTableVTI

All Implemented Interfaces:

java.lang.AutoCloseable, java.sql.ResultSet, java.sql.Wrapper, RestrictedVTI

public class ForeignTableVTI
extends VTITemplate
implements RestrictedVTI

This class contains a table function which can be used to bulk-import data from a foreign database. Because the table function is a RestrictedVTI, it can also be used to periodically and efficiently integrate data streams from a foreign database.

If you need to siphon data out of the foreign database on an ongoing basis, you can restrict the data you SELECT. Note that the local views are backed by RestrictedVTIs. That means that the actual query sent to the foreign database will only involve the columns you SELECT. In addition, the query will include the WHERE clause, provided that it is simple enough (see the javadoc for RestrictedVTI):

The following script shows how to use this table function:

 -- create a foreign database with a table in it
 connect 'jdbc:derby:memory:db;create=true;user=test_dbo;password=test_dbopassword';
 
 call syscs_util.syscs_create_user( 'test_dbo', 'test_dbopassword' );
 
 create table employee
 (
     firstName   varchar( 50 ),
     lastName    varchar( 50 ),
     employeeID  int primary key
 );
 
 insert into employee values ( 'Billy', 'Goatgruff', 1 );
 insert into employee values ( 'Mary', 'Hadalittlelamb', 2 );
 
 connect 'jdbc:derby:memory:db;shutdown=true';
 
 -- now create the database where we will do our work
 connect 'jdbc:derby:memory:db1;create=true';
 
 -- register a table function with the shape of the foreign table
 create function employeeFunction
 (
     schemaName  varchar( 32672 ),
     tableName   varchar( 32672 ),
     connectionURL        varchar( 32672 )
 )
 returns table
 (
     firstName   varchar( 50 ),
     lastName    varchar( 50 ),
     employeeID  int    
 )
 language java parameter style derby_jdbc_result_set no sql
 external name 'org.apache.derby.vti.ForeignTableVTI.readForeignTable'
 ;
 
 -- create a convenience view to factor out the function parameters
 create view foreignEmployee
 as select firstName, lastName, employeeID
 from table
 (
     employeeFunction
     (
         'TEST_DBO',
         'EMPLOYEE',
         'jdbc:derby:memory:db;user=test_dbo;password=test_dbopassword'
     )
 ) s;
 
 -- now select from the view as though it were a local table
 select * from foreignEmployee;
 select lastName from foreignEmployee where employeeID = 2;
 

Field Summary

Fields

Modifier and Type	Field and Description
private java.lang.String[]	_columnNames
private int[]	_columnNumberMap
private static java.util.HashMap	_connections
private java.lang.String	_connectionURL
private java.sql.PreparedStatement	_foreignPreparedStatement
private java.sql.ResultSet	_foreignResultSet
private java.lang.String	_foreignSchemaName
private java.lang.String	_foreignTableName
private Restriction	_restriction

Fields inherited from interface java.sql.ResultSet

CLOSE_CURSORS_AT_COMMIT, CONCUR_READ_ONLY, CONCUR_UPDATABLE, FETCH_FORWARD, FETCH_REVERSE, FETCH_UNKNOWN, HOLD_CURSORS_OVER_COMMIT, TYPE_FORWARD_ONLY, TYPE_SCROLL_INSENSITIVE, TYPE_SCROLL_SENSITIVE

Constructor Summary

Constructors

Modifier	Constructor and Description
protected	ForeignTableVTI(java.lang.String foreignSchemaName, java.lang.String foreignTableName, java.lang.String connectionURL)

Method Summary

Modifier and Type	Method and Description
void	close()
private static java.lang.String	delimitedID(java.lang.String text)
java.io.InputStream	getAsciiStream(int i)
java.math.BigDecimal	getBigDecimal(int i)
java.math.BigDecimal	getBigDecimal(int i, int scale)
java.io.InputStream	getBinaryStream(int i)
java.sql.Blob	getBlob(int i)
boolean	getBoolean(int i)
byte	getByte(int i)
byte[]	getBytes(int i)
java.io.Reader	getCharacterStream(int i)
java.sql.Clob	getClob(int i)
java.sql.Date	getDate(int i)
java.sql.Date	getDate(int i, java.util.Calendar cal)
double	getDouble(int i)
float	getFloat(int i)
private static java.sql.Connection	getForeignConnection(java.lang.String connectionURL)
int	getInt(int i)
long	getLong(int i)
java.sql.ResultSetMetaData	getMetaData()
java.lang.Object	getObject(int i)
short	getShort(int i)
java.lang.String	getString(int i)
java.sql.Time	getTime(int i)
java.sql.Time	getTime(int i, java.util.Calendar cal)
java.sql.Timestamp	getTimestamp(int i)
java.sql.Timestamp	getTimestamp(int i, java.util.Calendar cal)
void	initScan(java.lang.String[] columnNames, Restriction restriction) Initialize a scan of a ResultSet.
boolean	isClosed()
private java.lang.String	makeQuery() Build the query which will be sent to the foreign database.
private int	mapColumnNumber(int derbyNumber) Map a 1-based Derby column number to a 1-based column number in the foreign query.
boolean	next()
private static java.sql.PreparedStatement	prepareStatement(java.sql.Connection conn, java.lang.String text)
static ForeignTableVTI	readForeignTable(java.lang.String foreignSchemaName, java.lang.String foreignTableName, java.lang.String connectionURL) Table function to read a table in a foreign database.
boolean	wasNull()

Methods inherited from class org.apache.derby.vti.VTITemplate

getBigDecimal, getBigDecimal, getBoolean, getByte, getBytes, getDate, getDouble, getFloat, getInt, getLong, getObject, getShort, getString, getTime, getTimestamp

Methods inherited from class org.apache.derby.vti.VTITemplateBase

absolute, afterLast, beforeFirst, cancelRowUpdates, clearWarnings, deleteRow, findColumn, first, getArray, getArray, getAsciiStream, getBinaryStream, getBlob, getCharacterStream, getClob, getConcurrency, getCursorName, getDate, getFetchDirection, getFetchSize, getObject, getObject, getRef, getRef, getRow, getStatement, getTime, getTimestamp, getType, getUnicodeStream, getUnicodeStream, getURL, getURL, getWarnings, insertRow, isAfterLast, isBeforeFirst, isFirst, isLast, last, moveToCurrentRow, moveToInsertRow, notImplemented, previous, refreshRow, relative, rowDeleted, rowInserted, rowUpdated, setFetchDirection, setFetchSize, updateArray, updateArray, updateAsciiStream, updateAsciiStream, updateBigDecimal, updateBigDecimal, updateBinaryStream, updateBinaryStream, updateBlob, updateBlob, updateBoolean, updateBoolean, updateByte, updateByte, updateBytes, updateBytes, updateCharacterStream, updateCharacterStream, updateClob, updateClob, updateDate, updateDate, updateDouble, updateDouble, updateFloat, updateFloat, updateInt, updateInt, updateLong, updateLong, updateNull, updateNull, updateObject, updateObject, updateObject, updateObject, updateRef, updateRef, updateRow, updateShort, updateShort, updateString, updateString, updateTime, updateTime, updateTimestamp, updateTimestamp

Methods inherited from class java.lang.Object

clone, equals, finalize, getClass, hashCode, notify, notifyAll, toString, wait, wait, wait

Methods inherited from interface java.sql.ResultSet

getHoldability, getNCharacterStream, getNCharacterStream, getNClob, getNClob, getNString, getNString, getObject, getObject, getRowId, getRowId, getSQLXML, getSQLXML, updateAsciiStream, updateAsciiStream, updateAsciiStream, updateAsciiStream, updateBinaryStream, updateBinaryStream, updateBinaryStream, updateBinaryStream, updateBlob, updateBlob, updateBlob, updateBlob, updateCharacterStream, updateCharacterStream, updateCharacterStream, updateCharacterStream, updateClob, updateClob, updateClob, updateClob, updateNCharacterStream, updateNCharacterStream, updateNCharacterStream, updateNCharacterStream, updateNClob, updateNClob, updateNClob, updateNClob, updateNClob, updateNClob, updateNString, updateNString, updateObject, updateObject, updateObject, updateObject, updateRowId, updateRowId, updateSQLXML, updateSQLXML

Methods inherited from interface java.sql.Wrapper

isWrapperFor, unwrap

Field Detail

_connections

private static java.util.HashMap _connections

_foreignSchemaName

private java.lang.String _foreignSchemaName

_foreignTableName

private java.lang.String _foreignTableName

_connectionURL

private java.lang.String _connectionURL

_columnNames

private java.lang.String[] _columnNames

_restriction

private Restriction _restriction

_columnNumberMap

private int[] _columnNumberMap

_foreignPreparedStatement

private java.sql.PreparedStatement _foreignPreparedStatement

_foreignResultSet

private java.sql.ResultSet _foreignResultSet

Constructor Detail

ForeignTableVTI

protected ForeignTableVTI(java.lang.String foreignSchemaName,
                          java.lang.String foreignTableName,
                          java.lang.String connectionURL)

Method Detail

readForeignTable

public static ForeignTableVTI readForeignTable(java.lang.String foreignSchemaName,
                                               java.lang.String foreignTableName,
                                               java.lang.String connectionURL)

Table function to read a table in a foreign database.

Parameters:

foreignSchemaName - Case-sensitive name of foreign schema

foreignTableName - Case-sensitive name of foreign table

connectionURL - URL for connecting to foreign database via DriverManager.getConnection()

close

public void close()
           throws java.sql.SQLException

Specified by:

close in interface java.lang.AutoCloseable

Specified by:

close in interface java.sql.ResultSet

Specified by:

close in class VTITemplate

Throws:

java.sql.SQLException

next

public boolean next()
             throws java.sql.SQLException

Specified by:

next in interface java.sql.ResultSet

Specified by:

next in class VTITemplate

Throws:

java.sql.SQLException

isClosed

public boolean isClosed()

Specified by:

isClosed in interface java.sql.ResultSet

wasNull

public boolean wasNull()
                throws java.sql.SQLException

Specified by:

wasNull in interface java.sql.ResultSet

Overrides:

wasNull in class VTITemplateBase

Throws:

java.sql.SQLException

getMetaData

public java.sql.ResultSetMetaData getMetaData()
                                       throws java.sql.SQLException

Specified by:

getMetaData in interface java.sql.ResultSet

Overrides:

getMetaData in class VTITemplateBase

Throws:

java.sql.SQLException

getAsciiStream

public java.io.InputStream getAsciiStream(int i)
                                   throws java.sql.SQLException

Specified by:

getAsciiStream in interface java.sql.ResultSet

Overrides:

getAsciiStream in class VTITemplateBase

Throws:

java.sql.SQLException

getBigDecimal

public java.math.BigDecimal getBigDecimal(int i)
                                   throws java.sql.SQLException

Specified by:

getBigDecimal in interface java.sql.ResultSet

Overrides:

getBigDecimal in class VTITemplateBase

Throws:

java.sql.SQLException

getBigDecimal

public java.math.BigDecimal getBigDecimal(int i,
                                          int scale)
                                   throws java.sql.SQLException

Specified by:

getBigDecimal in interface java.sql.ResultSet

Overrides:

getBigDecimal in class VTITemplateBase

Throws:

java.sql.SQLException

getBinaryStream

public java.io.InputStream getBinaryStream(int i)
                                    throws java.sql.SQLException

Specified by:

getBinaryStream in interface java.sql.ResultSet

Overrides:

getBinaryStream in class VTITemplateBase

Throws:

java.sql.SQLException

getBlob

public java.sql.Blob getBlob(int i)
                      throws java.sql.SQLException

Specified by:

getBlob in interface java.sql.ResultSet

Overrides:

getBlob in class VTITemplateBase

Throws:

java.sql.SQLException

getBoolean

public boolean getBoolean(int i)
                   throws java.sql.SQLException

Specified by:

getBoolean in interface java.sql.ResultSet

Overrides:

getBoolean in class VTITemplateBase

Throws:

java.sql.SQLException

getByte

public byte getByte(int i)
             throws java.sql.SQLException

Specified by:

getByte in interface java.sql.ResultSet

Overrides:

getByte in class VTITemplateBase

Throws:

java.sql.SQLException

getBytes

public byte[] getBytes(int i)
                throws java.sql.SQLException

Specified by:

getBytes in interface java.sql.ResultSet

Overrides:

getBytes in class VTITemplateBase

Throws:

java.sql.SQLException

getCharacterStream

public java.io.Reader getCharacterStream(int i)
                                  throws java.sql.SQLException

Specified by:

getCharacterStream in interface java.sql.ResultSet

Overrides:

getCharacterStream in class VTITemplateBase

Throws:

java.sql.SQLException

getClob

public java.sql.Clob getClob(int i)
                      throws java.sql.SQLException

Specified by:

getClob in interface java.sql.ResultSet

Overrides:

getClob in class VTITemplateBase

Throws:

java.sql.SQLException

getDate

public java.sql.Date getDate(int i)
                      throws java.sql.SQLException

Specified by:

getDate in interface java.sql.ResultSet

Overrides:

getDate in class VTITemplateBase

Throws:

java.sql.SQLException

getDate

public java.sql.Date getDate(int i,
                             java.util.Calendar cal)
                      throws java.sql.SQLException

Specified by:

getDate in interface java.sql.ResultSet

Overrides:

getDate in class VTITemplateBase

Throws:

java.sql.SQLException

getDouble

public double getDouble(int i)
                 throws java.sql.SQLException

Specified by:

getDouble in interface java.sql.ResultSet

Overrides:

getDouble in class VTITemplateBase

Throws:

java.sql.SQLException

getFloat

public float getFloat(int i)
               throws java.sql.SQLException

Specified by:

getFloat in interface java.sql.ResultSet

Overrides:

getFloat in class VTITemplateBase

Throws:

java.sql.SQLException

getInt

public int getInt(int i)
           throws java.sql.SQLException

Specified by:

getInt in interface java.sql.ResultSet

Overrides:

getInt in class VTITemplateBase

Throws:

java.sql.SQLException

getLong

public long getLong(int i)
             throws java.sql.SQLException

Specified by:

getLong in interface java.sql.ResultSet

Overrides:

getLong in class VTITemplateBase

Throws:

java.sql.SQLException

getObject

public java.lang.Object getObject(int i)
                           throws java.sql.SQLException

Specified by:

getObject in interface java.sql.ResultSet

Overrides:

getObject in class VTITemplateBase

Throws:

java.sql.SQLException

getShort

public short getShort(int i)
               throws java.sql.SQLException

Specified by:

getShort in interface java.sql.ResultSet

Overrides:

getShort in class VTITemplateBase

Throws:

java.sql.SQLException

getString

public java.lang.String getString(int i)
                           throws java.sql.SQLException

Specified by:

getString in interface java.sql.ResultSet

Overrides:

getString in class VTITemplateBase

Throws:

java.sql.SQLException

getTime

public java.sql.Time getTime(int i)
                      throws java.sql.SQLException

Specified by:

getTime in interface java.sql.ResultSet

Overrides:

getTime in class VTITemplateBase

Throws:

java.sql.SQLException

getTime

public java.sql.Time getTime(int i,
                             java.util.Calendar cal)
                      throws java.sql.SQLException

Specified by:

getTime in interface java.sql.ResultSet

Overrides:

getTime in class VTITemplateBase

Throws:

java.sql.SQLException

getTimestamp

public java.sql.Timestamp getTimestamp(int i)
                                throws java.sql.SQLException

Specified by:

getTimestamp in interface java.sql.ResultSet

Overrides:

getTimestamp in class VTITemplateBase

Throws:

java.sql.SQLException

getTimestamp

public java.sql.Timestamp getTimestamp(int i,
                                       java.util.Calendar cal)
                                throws java.sql.SQLException

Specified by:

getTimestamp in interface java.sql.ResultSet

Overrides:

getTimestamp in class VTITemplateBase

Throws:

java.sql.SQLException

initScan

public void initScan(java.lang.String[] columnNames,
                     Restriction restriction)
              throws java.sql.SQLException

Description copied from interface: RestrictedVTI

Initialize a scan of a ResultSet. This method is called once before the scan begins. It is called before any ResultSet method is called. This method performs two tasks:

Column names - Tells the ResultSet which columns need to be fetched. Limits - Gives the ResultSet simple bounds to apply in order to limit which rows are returned. Note that the ResultSet does not have to enforce all of these bounds. Derby will redundantly enforce these limits on all rows returned by the ResultSet. That is, filtering not performed inside the ResultSet will still happen outside the ResultSet.

The columnNames argument is an array of columns which need to be fetched. This is an array of the column names declared in the Table Function's CREATE FUNCTION statement. Column names which were double-quoted in the CREATE FUNCTION statement appear case-sensitive in this array. Column names which were not double-quoted appear upper-cased. Derby asks the Table Function to fetch all columns mentioned in the query. This includes columns mentioned in the SELECT list as well as columns mentioned in the WHERE clause. Note that a column could be mentioned in the WHERE clause in a complex expression which could not be passed to the Table Function via the restriction argument.

The array has one slot for each column declared in the CREATE FUNCTION statement. Slot 0 corresponds to the first column declared in the CREATE FUNCTION statement and so on. If a column does not need to be fetched, then the corresponding slot is null. If a column needs to be fetched, then the corresponding slot holds the column's name.

Note that even though the array may have gaps, it is expected that columns in the ResultSet will occur at the positions declared in the CREATE FUNCTION statement. Consider the following declaration:

  create function foreignEmployeeTable()
  returns table
  (
      id        int,
      birthDay  date,
      firstName varchar( 100 ),
      lastName  varchar( 100 )
  )
 ...
 

and the following query:

 select lastName from table( foreignEmployeeTable() ) s
 

In this example, the array passed to this method will have 4 slots. Slots 0, 1, and 2 will be null and slot 3 will hold the String "LASTNAME". Last names will be retrieved from the ResultSet by calls to getString( 4 )--remember that JDBC column ids are 1-based.

The restriction argument is a simple expression which should be evaluated inside the Table Function in order to eliminate rows. The expression is a binary tree built out of ANDs, ORs, and column qualifiers. The column qualifiers are simple comparisons between constant values and columns in the Table Function. The Table Function only returns rows which satisfy the expression. The restriction is redundantly enforced by Derby on the rows returned by the ResultSet--this means that restriction gives the Table Function a hint about how to optimize its performance but the Table Function is not required to enforce the entire restriction.

Specified by:

initScan in interface RestrictedVTI

Throws:

java.sql.SQLException

getForeignConnection

private static java.sql.Connection getForeignConnection(java.lang.String connectionURL)
                                                 throws java.sql.SQLException

Throws:

java.sql.SQLException

makeQuery

private java.lang.String makeQuery()

Build the query which will be sent to the foreign database.

delimitedID

private static java.lang.String delimitedID(java.lang.String text)

prepareStatement

private static java.sql.PreparedStatement prepareStatement(java.sql.Connection conn,
                                                           java.lang.String text)
                                                    throws java.sql.SQLException

Throws:

java.sql.SQLException

mapColumnNumber

private int mapColumnNumber(int derbyNumber)

Map a 1-based Derby column number to a 1-based column number in the foreign query.