Farrago SQL/MED Support

Overview

• An SQL DDL interface for defining access to foreign data.
• An underlying programming-language-neutral service provider interface to be implemented by foreign data wrappers (drivers for various kinds of data). This interface is wide, complex, and non-trivial to implement. For example, in order to support metadata import, an implementation must support SQL queries against the standard INFORMATION_SCHEMA (probably not something the developer of a flat-file driver wants to deal with).
• Datalink interfaces (not covered in this document).

• FTRS: for access to data stored by Fennel in transactional row format. This is not really foreign data, but is represented as such for architectural uniformity. A normal CREATE TABLE statement uses this implicitly. The CREATE TABLE statement has been extended to allow creation of other types of table storage (e.g. FCS for a hypothetical Fennel column-store format).
• JDBC: for access to data stored in foreign SQL databases.
• MDR: for access to metadata stored in an MDR repository (including Farrago's own system catalog).
• MOCK: for testing or dummy tables.

Foreign Data Wrapper Definition

CREATE FOREIGN DATA WRAPPER foreign-data-wrapper-name
[ LIBRARY 'libraryName' ]
LANGUAGE language-name
[ OPTIONS ( opt-name1 'opt-value1' [, opt-name2 'opt-value2' ... ] ) ]

CREATE FOREIGN DATA WRAPPER xml_tcpip
LIBRARY '/home/jvs/wrappers/FarragoXmlSourceReader.jar'
LANGUAGE JAVA
OPTIONS ( protocol 'TCP/IP' )

As an extension, Farrago also allows a foreign data wrapper to be specified as the fully-qualified name of a class available on the server's classpath (instead of in a particular JAR file):

CREATE FOREIGN DATA WRAPPER xml_
LIBRARY 'class com.yoyodyne.farrago.plugin.xml.FarragoXmlDataWrapper'
LANGUAGE JAVA
OPTIONS ( protocol 'TCP/IP' )

Foreign Server Definition

CREATE SERVER foreign-server-name
[ TYPE 'server-type' ]
[ VERSION 'server-version' ]
FOREIGN DATA WRAPPER foreign-data-wrapper-name
[ OPTIONS ( ... ) ]

CREATE SERVER movie_catalog
TYPE 'ANY'
VERSION '1.1'
FOREIGN DATA WRAPPER xml_tcpip
OPTIONS ( url 'http://www.movie-fun-facts.org' )

According to the SQL/MED standard, defining a foreign server does not make its data accessible (the CREATE FOREIGN TABLE or IMPORT FOREIGN SCHEMA statements described later are required first). However, Farrago introduces a non-standard mechanism for direct access. Each foreign server defined implies a corresponding top-level virtual catalog. These catalogs are virtual in the sense that no local metadata is stored for them (and no INFORMATION_SCHEMA is defined). Instead, metadata is acquired on-the-fly during query processing. For example, after the above CREATE SERVER example, the following query might be legal:

SELECT DISTINCT r.name
FROM movie_catalog.review_facts.reviewers r,
     movie_catalog.movie_facts.actors a
WHERE r.name=a.name

SELECT * 
FROM movie_catalog.information_schema.table

Virtual catalogs are read-only (it is not possible to create new schemas, tables, or other objects under them).

Foreign Table Definition

CREATE FOREIGN TABLE table-name
SERVER foreign-server-name
[ ( basic-column-definition1 [, basic-column-definition2 ... ] ) ]
[ OPTIONS ( ... ) ]

For example:

CREATE FOREIGN TABLE movie_schema.reviewers
SERVER movie_catalog
OPTIONS ( directory 'review_facts', file 'reviewers.xml' )

SELECT * 
FROM movie_schema.reviewers

SELECT column_name
FROM localhost.information_schema
WHERE table_schema = 'MOVIE_SCHEMA' 
AND table_name = 'REVIEWERS'

Foreign Schema Import

IMPORT FOREIGN SCHEMA foreign-schema-name
[ 
 { LIMIT TO | EXCEPT }
 { ( table1 [, table2 ... ] ) | TABLE_NAME LIKE 'pattern' }
]
FROM SERVER foreign-server-name
INTO local-schema-name

For the running example (this imports only table names starting with R):

IMPORT FOREIGN SCHEMA movie_facts
LIMIT TO TABLE_NAME LIKE 'R%'
FROM SERVER movie_catalog
INTO localhost.movie_schema

Additional DDL

Java Interfaces

Repository Model