## MultiSource Virtualization Plugin

### Overview

The multiple source query plugin allows SQuirreL users to create a virtual data source that may consist of multiple data sources on different servers and platforms. The user can enter one SQL query to combine and join information from multiple sources.

### Benefits

- The plugin allows SQuirreL to natively support multiple source queries.
- No data source or server changes are required.
- The plugin supports standard SQL including joins, group by, aggregation, LIMIT, and ordering where tables may come from one or more sources.
- The plugin will perform function translation where a user requests a function that is not supported on a certain source.

### Installation

1. Download and install SQuirreL.
2. Download the plugin at http://people.ok.ubc.ca/rlawrenc/multisource.zip.
3. Unzip the multisource.zip into the directory squirrel/plugins.

   Before Unzip | After Unzip
   --- | ---

4. Copy the unityjdbc.jar into the squirrel/lib directory or JAVA_HOME/jre/lib/ext directory.
5. For any other JDBC drivers used (say the driver for MySQL), they must be in the squirrel/lib directory or JAVA_HOME/jre/lib/ext directory as well.
6. Start SQuirreL. The multisource plugin should be visible in the plugin list.
7. Register the UnityJDBC driver in the driver list.

### How It Works

1. Register data source aliases as usual. Here we have created connections to a Microsoft SQL Server database, a MySQL database, an Oracle database, and a PostgreSQL database all containing the TPC-H benchmark schema. Note that any database with a JDBC driver is supported including those accessible using the JDBC-ODBC bridge.

   Microsoft SQL Server | MySQL
   --- | ---
   Oracle | Postgres

2. Make sure you have registered the UnityJDBC driver (during installation). Create an alias consisting of virtual sources. The name field can be any name. It does not have to be virtual.
3. The user adds each database alias to the virtual source.

   #### Before add MSSQL source:

   #### Prompt for source (alias) to add:

   #### After add Microsoft source. Tables of MSSQL sources are visible in object tree view.

4. User can add as many sources as they wish. You can also rename the source in the virtual view. It does not have to be the same as the alias name used by SQuirreL. When adding Oracle sources, make sure to specify a schema so that system tables and tables from all schemas are not extracted.

   #### Adding an Oracle Source with a Schema

   #### Object Tree View with all Four Sources Added

5. The user can execute an SQL query that spans multiple sources and get a single result. The virtualization is transparent to the user and SQuirreL.

   #### Join across two data sources.

   ### A Translation Example

   The UnityJDBC driver used to perform the virtualization will also translate functions that are not implemented by certain sources. For example, MSSQL does not support TRIM(), but you can do the same result using RTRIM(LTRIM()). Unity will automatically translate a TRIM() function specified in a MSSQL query to the correct syntax supported by the database.

   #### Example TRIM() Translation for MSSQL

   This translation is supported for common databases and can be freely extended by user-defined functions and translations for each database dialect.

   ### Plugin Limits

   The plugin source code, like all of SQuirreL, is released under the GNU Lesser General Public License. The UnityJDBC virtualization driver is released under a commercial license. However, the UnityJDBC driver included in the plugin is fully functioning with no time limits allowing an unlimited number of sources and queries. The only limitation is the size of the result set is limited to the first 100 rows. (Note there is no limit on the number of rows extracted from each source. So select count(*) from table with a 1 million row table is fine as it only returns one result row.) Use LIMIT 100 to get the first 100 results of a query.

   For More Information and Technical Support Contact:
   Dr. Ramon Lawrence, ramon.lawrence@ubc.ca, 250-807-9390
   Associate Professor, Computer Science, University of British Columbia Okanagan, Canada
   UnityJDBC driver information: www.unityjdbc.com