Connections binding (i.e. virtual connections)

Connection binding is a feature which allows to "bind" several connections and/or imported data sets into a single connection, which can be used almost like a regular connection.

This feature is useful when one needs to execute an SQL command (for example to extract data) taking into account data actually stored in different databases. Without this feature, the way to achieve the same result is by exporting the necessary data from each database and work on the data outside of the databases.

To create a new "bound" connection (or "virtual" connection), use the Connection ▸ Bind connection menu, or the Ctrl+i shortcut. This pops up a new dialog window from which elements to be included in the "bound" connection can be defined as shown below.

Dialog to create a new virtual connection

Here the current connection (c1) will be bound in the "c1" schema

Use the Add binding button to either:

  • bind a connection to include all the tables of that connection: use the Bind a connection option. This option simply adds a new element in the dialog, to specify a schema name and select an existing connection

  • bind a data set to import data from a text file as a single table: use the Bind a data set option. See section below for more information.

Use case for virtual connections

To illustrate virtual connection usage, let's define a simple use case and see how it's useful. Suppose you have a database which contains the list of servers which you are responsible for. For simplicity, let's assume that the database contains a table named "server" with the following definition:

CREATE TABLE servers (name string, location string);

Let's suppose this table contains the following information:

> select * from servers ;
name     | location
---------+---------
moon     | room1   
darkstar | room2   
flag     | room3   
alf      | room2   
vador    | room3   
(5 rows)
>
    

Now let's assume you have a third party program which gives you a list of servers for which there might be a problem, the output of this program is a CSV file with a column for the server name, a column for the error code, and a column describing the problem. Let's assume the CSV file has the following contents:

$ cat problems.csv 
Server name,Error code,Error description
darkstar,0,file system full
vador,1,outdated AV signature
darkstar,2,overloaded
flag,0,file system full
$
    

Let's suppose now you want to know all the servers, where they are located where there is a problem with error code 0. The solution is to bind the connection to the database (for example in the "c1" schema), import the problems CSV file (for example in the "tab" table), and execute the following SQL command:

SELECT 
  "server name", 
  location
FROM tab
  INNER JOIN c1.servers AS s ON (tab."server name" = s.name)
WHERE "Error code"=0
    

Which returns the expected results as shown in the following figure:

Expected results from virtual connection

Importing a data set into a virtual connection

To import a data set into a table in the virtual connection (for example to import the "problems.csv" file from the use case), use the Bind a data set option from the virtual connection creation dialog:

Import a data set into a virtual connection - 1

From there use the Import button which allows you to select a file to import and also define some import options (file encoding, file type, ...) After this step, the virtual connection is ready to be used:

Import a data set into a virtual connection - 2

The resulting virtual connection, identified as "c2" here is opened in its own new window:

New opened virtual connection

SQL usable with virtual connections

You can use the SQL understood by SQLite in any virtual connection. Be aware however that if you define database objects (outside of the extended SQL presented next section), they will be lost the next time the virtual connection is opened.

See the SQL understood by LDAP connections and virtual connections section for more information.