FarragoMedBrowseConnect
From Eigenpedia
This page describes Farrago management support for browsing the available SQL/MED foreign data wrappers and interrogating them for connection parameters via SQL statements.
Many applications based on SQL/MED require the ability to create new connections to foreign servers via a user interface. When the correct foreign server options (such as wrapper to use, data source URL, and authentication credentials) are not known ahead of time, such a user interface requires "browse connect" functionality from the DBMS. The Farrago SQL/MED SPI allows foreign data wrapper implementations to provide this functionality, and the Farrago management schema exposes this via views and UDX functions:
- view sys_boot.mgmt.browse_connect_foreign_wrappers: lists all foreign wrappers which have been tagged as suitable for browse connect interaction
- UDX sys_boot.mgmt.browse_connect_foreign_server: supports iterative interaction for filling in connection parameters against a particular data wrapper, leading up to creation of a foreign server
- UDX sys_boot.mgmt.browse_foreign_schemas: supports retrieval of a list of foreign schemas after a foreign server has been created; these schemas can then be used as the source for the IMPORT FOREIGN SCHEMA command
The rest of this page walks through interactions based on these in detail.
When a Farrago catalog is initialized, many foreign data wrappers may be created; some of these may be for internal use by the system, while others are intended for activation by end-users. To discriminate these, Farrago overloads SQL/MED option name/value pairs as a tagging mechanism. A foreign data wrapper with an option named BROWSE_CONNECT_DESCRIPTION set is considered to be suitable for exposure to end-users as a candidate for creating new foreign servers. The value of the option becomes the wrapper description presented to an end-user; this may be different from the wrapper's description attribute in the catalog. Suppose the catalog is initialized via the following DDL:
create foreign data wrapper hsqldb_wrapper library 'plugin/FarragoMedJdbc.jar' language java options( browse_connect_description 'Hypersonic Database', driver_class 'org.hsqldb.jdbcDriver', url 'jdbc:hsqldb:path/to/data' ); create foreign data wrapper csv_wrapper library 'plugin/FarragoMedJdbc.jar' language java options( browse_connect_description 'CSV Files', driver_class 'org.relique.jdbc.csv.CsvDriver', url 'jdbc:relique:csv:path/to/data' ); create foreign data wrapper mdr_wrapper library 'class net.sf.farrago.namespace.mdr.MedMdrForeignDataWrapper' language java;
Only hsqldb_wrapper and csv_wrapper have been tagged as suitable for activation by end-users. Note that both of these are using the same underlying wrapper jar (FarragoMedJdbc.jar), but configured with a different description, JDBC driver, and template URL for JDBC connection. To the end users, they will look like completely different animals. To get back the list of tagged wrappers, the UI can query browse_connect_foreign_wrappers:
0: jdbc:farrago:> select * from sys_boot.mgmt.browse_connect_foreign_wrappers; +-----------------------+-----------------------------+ | FOREIGN_WRAPPER_NAME | BROWSE_CONNECT_DESCRIPTION | +-----------------------+-----------------------------+ | HSQLDB_WRAPPER | Hypersonic Database | | CSV_WRAPPER | CSV Files | +-----------------------+-----------------------------+
Now, suppose the user selects HSQLDB_WRAPPER, and wants to create a corresponding foreign server. The first step is to get back a list of connection options to be filled in:
0: jdbc:farrago:> select * from table( . . . . . . . . > sys_boot.mgmt.browse_connect_foreign_server( . . . . . . . . > 'HSQLDB_WRAPPER', . . . . . . . . > cursor( . . . . . . . . > select '' as option_name, '' as option_value . . . . . . . . > from sys_boot.jdbc_metadata.empty_view))) . . . . . . . . > order by option_ordinal, option_choice_ordinal; +-----------------+-------------------+-----------------------------------------------------+---------------------+------------------------+---------------------------+ | OPTION_ORDINAL | OPTION_NAME | OPTION_DESCRIPTION | IS_OPTION_REQUIRED | OPTION_CHOICE_ORDINAL | OPTION_CHOICE_VALUE | +-----------------+-------------------+-----------------------------------------------------+---------------------+------------------------+---------------------------+ | 0 | DRIVER_CLASS | Fully-qualified class name of JDBC driver to load | true | -1 | org.hsqldb.jdbcDriver | | 1 | URL | JDBC URL for data source | true | -1 | jdbc:hsqldb:path/to/data | | 2 | USER_NAME | User name for authentication in source DBMS | false | -1 | | | 3 | PASSWORD | Password for authentication in source DBMS | false | -1 | | | 4 | EXTENDED_OPTIONS | Whether driver-specific options should be accepted | true | -1 | FALSE | | 4 | EXTENDED_OPTIONS | Whether driver-specific options should be accepted | true | 0 | FALSE | | 4 | EXTENDED_OPTIONS | Whether driver-specific options should be accepted | true | 1 | TRUE | +-----------------+-------------------+-----------------------------------------------------+---------------------+------------------------+---------------------------+
The results come back in NQNF (not quite normal form) to handle the case where certain options have multiple choices available (in this example, the EXTENDED_OPTIONS flag). In such cases, the OPTIONAL_ORDINAL column repeats along with other columns, and the OPTION_CHOICE_ORDINAL distinguishes the choices. When OPTION_CHOICE_ORDINAL is -1, the row represents the "current" setting for the option (so EXTENDED_OPTIONS is set to FALSE); non-negative choice ordinals represent the available choices. In this case, the "current" settings were chosen by the wrapper itself based on defaults, because the input cursor to the UDX was empty_view. Now suppose that the user fills in certain values, providing a real path for the URL and setting EXTENDED_OPTIONS to TRUE. The UI transmits these choices to the next iteration by passing in a non-empty cursor:
0: jdbc:farrago:> select option_name, option_choice_ordinal, option_choice_value from table(
. . . . . . . . > sys_boot.mgmt.browse_connect_foreign_server(
. . . . . . . . > 'HSQLDB_WRAPPER',
. . . . . . . . > cursor(
. . . . . . . . > values ('URL', 'jdbc:hsqldb:testcases/hsqldb/scott'),
. . . . . . . . > ('EXTENDED_OPTIONS', 'TRUE'))))
. . . . . . . . > order by option_ordinal, option_choice_ordinal;
+-------------------+------------------------+-------------------------------------+
| OPTION_NAME | OPTION_CHOICE_ORDINAL | OPTION_CHOICE_VALUE |
+-------------------+------------------------+-------------------------------------+
| DRIVER_CLASS | -1 | org.hsqldb.jdbcDriver |
| URL | -1 | jdbc:hsqldb:testcases/hsqldb/scott |
| USER_NAME | -1 | |
| PASSWORD | -1 | |
| EXTENDED_OPTIONS | -1 | TRUE |
| EXTENDED_OPTIONS | 0 | FALSE |
| EXTENDED_OPTIONS | 1 | TRUE |
| user | -1 | |
| password | -1 | |
| get_column_name | -1 | true |
| get_column_name | 0 | true |
| get_column_name | 1 | false |
| ifexists | -1 | |
| ifexists | 0 | true |
| ifexists | 1 | false |
| default_schema | -1 | |
| default_schema | 0 | true |
| default_schema | 1 | false |
| shutdown | -1 | |
| shutdown | 0 | true |
| shutdown | 1 | false |
+-------------------+------------------------+-------------------------------------+
Notice that a number of new options are now present. This is the result of setting EXTENDED_OPTIONS to TRUE. (These extended options are specific to HSQLDB.) The implication is that the UI may need to be dynamic.
Also note that the URL specified by the user is now reflected in the results.
The BROWSE_CONNECT_FOREIGN_SERVER function is based on the FarragoMedDataWrapperInfo.getServerPropertyInfo SPI call. If the foreign data wrapper does not support this call, it should not be tagged for browse connect.
After a series of such interactions, the user may finally accept the option settings, resulting in the creation of a new foreign server:
0: jdbc:farrago:> create server hsqldb_server . . . . . . . . > foreign data wrapper hsqldb_wrapper . . . . . . . . > options( . . . . . . . . > url 'jdbc:hsqldb:testcases/hsqldb/scott', . . . . . . . . > user_name 'SA');
The UI can now display the foreign schemas available for import via a query like this:
0: jdbc:farrago:> select * from table(sys_boot.mgmt.browse_foreign_schemas('HSQLDB_SERVER'))
. . . . . . . . > order by schema_name;
+---------------------+--------------+
| SCHEMA_NAME | DESCRIPTION |
+---------------------+--------------+
| INFORMATION_SCHEMA | |
| PUBLIC | |
| SALES | |
+---------------------+--------------+
This call relies on the foreign data wrapper supporting the FarragoMedNameDirectory.queryMetadata SPI call with the result object type being OTN_SCHEMA.
