## 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. Any database that has a JDBC driver is supported including NoSQL databases such as MongoDB.

### 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 when a user requests a function or SQL feature/syntax that is not supported by a certain source.
- Users can export the virtualization information and use the virtualization driver in other Java programs and reporting software.

### Installation

1. Download and install SQuirreL. Add the MultiSource plugin during installation.
2. The plugin contains `unityjdbc.jar` in the folder `squirrel/plugins/multisource`. Copy this jar into the `squirrel/lib` folder. Make sure to add other database JDBC jars into the `squirrel/lib` folder or JRE classpath. The plugin cannot access drivers in custom classpaths used by SQuirreL.

   UnityJDBC JAR Location | Put UnityJDBC JAR in `squirrel/lib` folder
   --- | ---

3. Start SQuirreL. The multisource plugin should be visible in the plugin list.

### How It Works

1. Register data source aliases in SQuirreL as usual. This example has connections to a Microsoft SQL Server database, a MySQL database, an Oracle database, a PostgreSQL database, and a MongoDB database all containing the TPC-H benchmark data. Note that any database with a JDBC driver is supported including those accessible using the JDBC-ODBC bridge. For MongoDB support, the latest MongoDB Java driver should also be in the `squirrel/lib` folder.

   Microsoft SQL Server | MySQL
   --- | ---
   Oracle | Postgres
   Register MongoDB Driver | MongoDB

2. Make sure you have registered the UnityJDBC MultiSource Virtualization driver (during installation). Create an alias consisting of virtual sources. The name field can be any name. It does not have to contain "virtual".

   Create Virtual Source | Connect to Virtual Source
   --- | ---

3. Add existing JDBC connections (SQuirreL aliases), to the virtual source. The example adds all five of the sources created above. On the source name (in this case `virtualDemo`), right click and select `(Virtualization) Add source`.

   #### Before add Microsoft SQL (mssql) source:

   #### Select the source to add and click the Add button. Progress is shown.

   #### After adding the Microsoft source. Tables are visible in the object tree view.

4. Users 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. You can also filter tables added by catalog name, schema name, and table filters. The table filters to include are specified in SQL (JDBC) syntax with a % as a wild-card match. The table exclusion filters are specified as Java regular expressions.

   #### Adding an Oracle Source with a Schema

   #### Object Tree View with all Five 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.

   #### SQL Query joining tables in MySQL, Microsoft SQL Server, Oracle, PostgreSQL, and MongoDB

   ### A Translation Example

   The plugin uses features of the UnityJDBC driver to perform virtualization and 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.

   EXPLAIN can be used to understand how a SQL query is translated to queries on individual sources.

   ### Encryption

   The plugin saves its configuration information in the folder `multisource` under the SQuirreL user folder. If no password is specified when creating an alias, schema and connection informtaion (including passwords) are stored in plain text. To encrypt the configuration files, specify a password during connection (user id is ignored).

   #### Configuration File Location

   #### Specify Encryption by Providing a Password

   #### Virtualization Export for use with Other Programs and Reporting Software

   Once a virtualization has been created, all the information necessary is available in the XML files created and stored in the `plugin/multisource` folder in the SQuirreL user directory. These files can be copied and moved to another location and used with any Java/JDBC program or reporting software. These files can be copied directly or exported from within SQuirreL.

   Existing XML configuration files can be used by specifying an absolute or relative path in the JDBC URL. For example, if the user has saved the `sources.xml` file (and the associated schema files for sources in the directory `C:\tmp`, then a JDBC connection URL is: `jdbc:unity://c:\tmp\sources.xml`.

   ### 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 works 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 for the MultiSource plugin 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