<commit_before><html>

<head>
<title>Farrago SQL/MED Plugin for JDBC</title>
<link rel="stylesheet" type="text/css" href="../stylesheet.css" />

</head>

<body>

<h1>Farrago SQL/MED Plugin for JDBC</h1>

Farrago provides a plugin implementing <a href="sqlmed.html">SQL/MED access</a>
to any foreign DBMS for which a JDBC driver is available.  

<p>

Farrago supports the SQL standard three-level qualifier scheme for the
names of tables: <code>catalog.schema.table</code>.  When using the
SQL/MED plugin for JDBC to make foreign data namespaces accessible
from Farrago SQL queries, it is important to keep a number of points
in mind:

<ul>

<li>Different JDBC drivers support different qualifier schemes.  Some
support catalogs and schemas; others only support schemas; and still
others support neither catalogs nor schemas.  Default values may or may
not be in effect depending on the server connection URL.  For this
reason, the user must be careful when defining the foreign server
in Farrago to get the desired mapping.

<li>When referencing foreign tables directly via a virtual catalog (without
importing their metadata into a local schema), use the foreign schema
and table name.  In this case, the catalog name will be the name given
the foreign server via CREATE SERVER.

<li>When referencing foreign table via locally defined or imported
metadata, use the local schema and table name.  In this case, the
catalog name will be LOCALDB.

</ul>

<h2>JDBC Foreign Data Wrapper Definition</h2>

Because this plugin is included as part of the standard Farrago
distribution, a corresponding foreign data wrapper instance named
<code>SYS_JDBC</code> is predefined by Farrago's initialization
scripts.  Normally, there is no need to define additional instances.

<h2>JDBC Foreign Server Definition</h2>

In order to access a particular JDBC data source, a corresponding
foreign server must be defined.  The following server-level options
can be specified:

<p>
<table border="1">
<tr>
<td><b>Name</b></td>
<td><b>Description</b></td>
</tr>
<tr>
<td>DRIVER_CLASS</td>
<td>Fully qualified name of the JDBC driver class to load</td>
</tr>
<tr>
<td>URL</td>
<td>JDBC URL specifying the data source to access</td>
</tr>
<tr>
<td>USER_NAME</td>
<td>User name for login to foreign server</td>
</tr>
<tr>
<td>PASSWORD</td>
<td>Password for login to foreign server</td>
</tr>
<tr>
<td>SCHEMA_NAME</td> 
<td>Artificial foreign schema name.  This option is only required for
servers which don't support schemas.  When specified, the plugin will
accept this schema name as a foreign qualifier when importing foreign tables
or accessing them directly, but will omit all schema references when
sending SQL to the foreign server.  For servers that support schemas,
this option should never be specified; reference the real foreign schema names 
instead.
</td>
</tr>
<tr>
<td>QUALIFYING_CATALOG_NAME</td> 
<td>Foreign catalog name to which this server should correspond.  This
option should only be used for servers which support multiple
catalogs.  Normally, a default catalog is always defined for a
connection, so this option isn't usually required.  To access multiple
catalogs from the same physical server, create multiple logical
servers, each with a different value for this option.
</td>
</tr>
<tr>
<td>TABLE_TYPES</td>
<td>Comma-separated list of table types used to restrict metadata
results for operations such as IMPORT FOREIGN SCHEMA.  For example,
some servers mix system tables into the same schema with user tables
and views.  To hide them, specify a value like 'TABLE,VIEW' for this
option.  To retrieve only tables and system tables, but no views, use
'TABLE,SYSTEM TABLE'.  In general, table type names may vary across
JDBC drivers, so check your driver's documentation or call your
driver's DatabaseMetaData.getTableTypes() method to list the available
types.  This option is never required; if not specified, metadata for
all table types is returned.  No spaces are allowed in the list
unless they make up part of the type name, e.g. 'SYSTEM TABLE'. 
</td>
</tr>
<tr>
<td>EXTENDED_OPTIONS</td>
<td>
If set to 'TRUE', unrecognized options are passed through to the JDBC
driver as properties; otherwise, they are ignored.  The default is 'FALSE'.
</td>
</tr>
<tr>
<td>TYPE_SUBSTITUTION</td>
<td>
If set to 'TRUE' (the default), datatypes which are not supported
by Farrago are replaced with the best available substitute.  If set to
'FALSE', unsupported datatypes result in errors.  Note that any 
TYPE_MAPPING specified is executed before TYPE_SUBSTITUTION is run.
</td>
</tr>
<tr>
<td>TYPE_MAPPING</td>
<td>
Semicolon-separated list of 'orig_datatype:subst_datatype'.  If set,
Farrago will replace each of the orig_datatype with the corresponding
subst_datatype.  Datatype(precision) or datatype(precision, scale) 
can be set on either orig_datatype or subst_datatype.  On orig_datatype,
precision and scale are used to more precisely limit the matching datatype.
If precision and scale are not specified on subst_datatype, the original
precision and scale are kept.  If the 'subst_datatype' is not a supported 
Farrago datatype, the mapping is ignored.  Note that TYPE_MAPPING is run 
before TYPE_SUBSTITUTION and does not depend on TYPE_SUBSTITUTION.
</td>
</tr>
</table>
<p>

For example:

<pre><code>
create server ora_sales_server
foreign data wrapper sys_jdbc
options(
    driver_class 'oracle.jdbc.driver.OracleDriver',
    url 'jdbc:oracle:thin:@localhost:1521:ora1',
    user_name 'scott',
    password '<PASSWORD>');
</code></pre>

The following JDBC API calls are made in order to validate a foreign server
definition:<commit_msg>FARRAGO:  some additional clarifications on changes to JDBC SQL/MED plugin<commit_after><html>

<head>
<title>Farrago SQL/MED Plugin for JDBC</title>
<link rel="stylesheet" type="text/css" href="../stylesheet.css" />

</head>

<body>

<h1>Farrago SQL/MED Plugin for JDBC</h1>

Farrago provides a plugin implementing <a href="sqlmed.html">SQL/MED access</a>
to any foreign DBMS for which a JDBC driver is available.  

<p>

Farrago supports the SQL standard three-level qualifier scheme for the
names of tables: <code>catalog.schema.table</code>.  When using the
SQL/MED plugin for JDBC to make foreign data namespaces accessible
from Farrago SQL queries, it is important to keep a number of points
in mind:

<ul>

<li>Different JDBC drivers support different qualifier schemes.  Some
support catalogs and schemas; others only support schemas; and still
others support neither catalogs nor schemas.  Default values may or may
not be in effect depending on the server connection URL.  For this
reason, the user must be careful when defining the foreign server
in Farrago to get the desired mapping.

<li>When referencing foreign tables directly via a virtual catalog (without
importing their metadata into a local schema), use the foreign schema
and table name.  In this case, the catalog name will be the name given
the foreign server via CREATE SERVER.

<li>When referencing foreign table via locally defined or imported
metadata, use the local schema and table name.  In this case, the
catalog name will be LOCALDB.

</ul>

<h2>JDBC Foreign Data Wrapper Definition</h2>

Because this plugin is included as part of the standard Farrago
distribution, a corresponding foreign data wrapper instance named
<code>SYS_JDBC</code> is predefined by Farrago's initialization
scripts.  Normally, there is no need to define additional instances.

<p>

However, it may be useful to define additional wrapper instances
corresponding to specific DBMS types.  For the JDBC plugin, all
SQL/MED options declared on the wrapper are propagated to the server,
allowing common option settings to be factored out of individual
server definitions.  (Note that this is not necessarily true for other
plugins.)  Option settings on a server always take precedence over
settings inherited from a wrapper.

<h2>JDBC Foreign Server Definition</h2>

In order to access a particular JDBC data source, a corresponding
foreign server must be defined.  The following server-level options
can be specified:

<p>
<table border="1">
<tr>
<td><b>Name</b></td>
<td><b>Description</b></td>
</tr>
<tr>
<td>DRIVER_CLASS</td>
<td>Fully qualified name of the JDBC driver class to load; this must be 
available on the classpath.  Currently this requires either changing
the classpath used to invoke Farrago, or rolling the JDBC driver classes
into a new plugin jar combined with the default Farrago plugin contents.
(We plan to eventually support the SQL:2003 jar dependencies feature
to eliminate this repackaging burden.)</td>
</tr>
<tr>
<td>URL</td>
<td>JDBC URL specifying the data source to access</td>
</tr>
<tr>
<td>USER_NAME</td>
<td>User name for login to foreign server</td>
</tr>
<tr>
<td>PASSWORD</td>
<td>Password for login to foreign server</td>
</tr>
<tr>
<td>SCHEMA_NAME</td> 
<td>Artificial foreign schema name.  This option is only required for
servers which don't support schemas.  When specified, the plugin will
accept this schema name as a foreign qualifier when importing foreign tables
or accessing them directly, but will omit all schema references when
sending SQL to the foreign server.  For servers that support schemas,
this option should never be specified; reference the real foreign schema names 
instead.
</td>
</tr>
<tr>
<td>QUALIFYING_CATALOG_NAME</td> 
<td>Foreign catalog name to which this server should correspond.  This
option should only be used for servers which support multiple
catalogs.  Normally, a default catalog is always defined for a
connection, so this option isn't usually required.  To access multiple
catalogs from the same physical server, create multiple logical
servers, each with a different value for this option.
</td>
</tr>
<tr>
<td>TABLE_TYPES</td>
<td>Comma-separated list of table types used to restrict metadata
results for operations such as IMPORT FOREIGN SCHEMA.  For example,
some servers mix system tables into the same schema with user tables
and views.  To hide them, specify a value like 'TABLE,VIEW' for this
option.  To retrieve only tables and system tables, but no views, use
'TABLE,SYSTEM TABLE'.  In general, table type names may vary across
JDBC drivers, so check your driver's documentation or call your
driver's DatabaseMetaData.getTableTypes() method to list the available
types.  This option is never required; if not specified, metadata for
all table types is returned.  No spaces are allowed in the list
unless they make up part of the type name, e.g. 'SYSTEM TABLE'. 
</td>
</tr>
<tr>
<td>EXTENDED_OPTIONS</td>
<td>
If set to 'TRUE', unrecognized options are passed through to the JDBC
driver as properties; otherwise, they are ignored.  The default is 'FALSE'.
</td>
</tr>
<tr>
<td>TYPE_SUBSTITUTION</td>
<td>
If set to 'TRUE' (the default), datatypes which are not supported
by Farrago are replaced with the best available substitute.  If set to
'FALSE', unsupported datatypes result in errors.  Note that any 
TYPE_MAPPING specified is executed before TYPE_SUBSTITUTION is run.
</td>
</tr>
<tr>
<td>TYPE_MAPPING</td>
<td>
Semicolon-separated list of orig_datatype:subst_datatype pairs.  If
set, Farrago will replace the type of foreign columns matching
orig_datatype with the corresponding subst_datatype.  The format for
both datatype strings is DATATYPE_NAME[(PRECISION[,SCALE])] with no
spaces allowed.  On orig_datatype, precision and scale (if specified)
are used to more precisely limit the matching datatype.  In other
words, "DECIMAL" will match any decimal type, but "DECIMAL(10,2)" will
only match that specific precision and scale.  If precision and scale
are not specified on subst_datatype, the original precision and scale
are kept.  If the 'subst_datatype' is not a supported Farrago
datatype, the mapping is ignored.  Note that TYPE_MAPPING is run
before TYPE_SUBSTITUTION and does not depend on TYPE_SUBSTITUTION.
</td>
</tr>
</table>
<p>

For example:

<pre><code>
create server ora_sales_server
foreign data wrapper sys_jdbc
options(
    driver_class 'oracle.jdbc.driver.OracleDriver',
    url 'jdbc:oracle:thin:@localhost:1521:ora1',
    user_name 'scott',
    password '<PASSWORD>',
    type_mapping 'DATE:TIMESTAMP;DECIMAL(22,0):DOUBLE');
</code></pre>

(The Oracle JDBC driver incorrectly exposes datetime types without
nanoseconds as DATE, and exposes floating-point numerics as DECIMAL(22,0),
so the mappings above are usually required.)

<p>

The following JDBC API calls are made in order to validate a foreign server
definition:
{'fraction_non_alphanumeric': 0.07363136504900789, 'fraction_numerical': 0.002948442106940792, 'mean_word_length': 4.8837318330989214, 'pattern_counts': {'":': 0, '<': 213, '<?xml version=': 0, '>': 213, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '7035087', 'n_tokens_mistral': 3691, 'n_tokens_neox': 3558, 'n_words': 1738}