Chapter 5. The CONNECT Storage Engine

In this chapter, we will cover the following recipes:

• Installing the CONNECT storage engine

• Creating and dropping CONNECT tables

• Reading and writing CSV data using CONNECT

• Reading and writing XML data using CONNECT

• Accessing MariaDB tables using CONNECT

• Using the XCOL table type

• Using the PIVOT table type

• Using the OCCUR table type

• Using the WMI table type

• Using the MAC address table type

Introduction

In this chapter, we will explore some of the features of the CONNECT storage engine. This storage engine allows us to access data in various file formats such as XML, CSV, and other types of files stored on our host system. Its purpose is to connect MariaDB to these various data types. It's a very handy tool for bringing various pieces of an infrastructure together. So, the CONNECT storage engine tables are not exactly tables in the traditional sense (they may not even physically exist). With that in mind, there are some things we need to realize when working with this storage engine.

First, DROP TABLE does not delete content the way MyISAM, InnoDB, and other tables do. CONNECT tables are definitions of where the data we want to access is and what format it is in. For example, an XML file stored in a user's home directory. When we drop a CONNECT table using DROP, we are dropping the where-and-what definition stored in the CONNECT table and not the data itself.

Secondly, indexing...

Installing the CONNECT storage engine

The CONNECT storage engine is not installed by default. So, the first thing that we have to do is to install and enable it.

How to do it...

1. On Fedora, CentOS, or Red Hat systems, we can run the following command line:

   sudo yum install MariaDB-connect-engine
   

   On Debian, Ubuntu, or Linux Mint systems, we can run the following command line:

   sudo apt-get install mariadb-connect-engine-10.0
   

2. On all systems, launch the mysql command-line client and connect it to our MariaDB server with a user that has the SUPER privilege.

3. Enable the CONNECT storage engine by running the following command line:

   INSTALL SONAME 'ha_connect'; 
   

4. Verify the installation by running the following two commands and look for CONNECT in the output:

   SHOW ENGINES;
   SHOW PLUGINS;
   

How it works...

On Windows and MacOS, the CONNECT storage engine is included but not active. On Linux, we need to install the CONNECT package before we can enable it using the mysql command-line client.

See also

• The full...

Creating and dropping CONNECT tables

CONNECT tables are only superficially similar to other tables. In this recipe, we'll create a CONNECT DIR table.

Getting ready

Enable the CONNECT engine as specified in the Installing the CONNECT storage engine recipe at the beginning of this chapter.

How to do it...

1. Connect to MariaDB with the mysql command-line client and to the test database with a user that has the CREATE privilege. If the test database is absent, create one.

2. Run the following CREATE statement to create a table that lists the files in the data directory of the test database:

   CREATE TABLE test_data ( 
     path varchar(256) NOT NULL flag=1, 
     filename varchar(256) NOT NULL flag=2, 
     filesize double(12,0) NOT NULL flag=5 
   ) ENGINE=CONNECT DEFAULT CHARSET=latin1
     TABLE_TYPE=DIR FILE_NAME='*.frm'
     OPTION_LIST='subdir=1';
   

   Select everything in the table. The output will vary depending on the tables in the test database and their location and size. Though the columns will be similar to the following...

Reading and writing CSV data using CONNECT

CSV (comma separated values) is a very common data-interchange format. MariaDB can easily import CSV formatted files using the LOAD DATA INFILE command, and there is a CSV storage engine that stores data in the CSV format. However, neither of these handles cases where we need to be capable of querying CSV files that are updated outside of MariaDB and CSV files that we don't have to import before we can query them. The CONNECT storage engine's CSV data type allows us to do this easily.

Getting ready

We need to have some CSV data to work with for this recipe. We'll use data from the ISFDB database for this. To start with, perform the following steps:

1. Import the ISFDB database as described in the Importing the data exported by mysqldump recipe from Chapter 2, Diving Deep into MariaDB.

2. Install and enable the CONNECT storage engine as described in the Installing the CONNECT storage engine recipe at the beginning of this chapter.

3. Launch the mysql command...

Reading and writing XML data using CONNECT

There is a lot of data stored in XML format. MariaDB can easily export data as XML, but before the CONNECT engine, it did not have a way to easily read from and write to external XML documents.

Getting ready

Import the ISFDB database as described in the Importing the data exported by mysqldump recipe from Chapter 2, Diving Deep into MariaDB. Then, install and enable the CONNECT storage engine as described in the Installing the CONNECT storage engine recipe at the beginning of this chapter. Then, export the isfdb-001.xml file as described in the Producing XML output recipe from Chapter 2, Diving Deep into MariaDB. For this recipe, it is assumed that the XML file is located in /tmp/isfdb-001.xml, but it will be wherever we were when we exported it from MariaDB. We'll need to alter the FILE_NAME option in the recipe to point at it.

How to do it...

1. Launch the mysql command-line client application and connect to the isfdb database on our MariaDB server.

2. Run...

Accessing MariaDB tables using CONNECT

Using the CONNECT storage engine, we can set up connections to local or remote MariaDB database tables and have them appear as if they are part of our MariaDB database.

Getting ready

Import the ISFDB database as described in the Importing the data exported by mysqldump recipe from Chapter 2, Diving Deep into MariaDB. Then, install and enable the CONNECT storage engine as described in the Installing the CONNECT storage engine recipe at the beginning of this chapter.

How to do it...

1. Launch the mysql command-line client application and connect to the isfdb database on our MariaDB server.

2. Run the following CREATE TABLE statement by altering the user:pass part of the CONNECTION option with a username and password that has rights to the isfdb database:

   CREATE TABLE websites_2 ( 
     site_id int(11), 
     site_name varchar(255), 
     site_url varchar(1024), 
     PRIMARY KEY (site_id) 
   ) ENGINE=CONNECT TABLE_TYPE=MYSQL 
   CONNECTION='mysql://user:pass@localhost/isfdb/websites...

Using the XCOL table type

In a perfect world, all data in a MariaDB database would be properly defined and normalized. We don't live in such a world, and sometimes, we have to work with tables that contain one or more catchall columns stuffed full of related values. The XCOL table type enables us to work with this data as if it was stored in a separate rather than a single column.

How to do it...

1. Launch the mysql command-line client application and connect to the test database on our MariaDB server. If the test database does not exist, create it first.

2. Run the following CREATE TABLE statement to create our example table:

   CREATE TABLE superheroes ( 
     team varchar(50), 
     heroes varchar(1024)
   ); 
   

3. Add some data to our new table:

   INSERT superheroes VALUES 
     ("The Avengers","Thor, Iron Man, Black Widow, Hawkeye, Hulk, Captain America"), 
     ("The Justice League", "Superman, Batman, Aquaman, Flash, Wonder Woman"), 
     ("The X-Men", "Storm, Cyclops, Wolverine, Rogue, Iceman");
   

4. Create an XCOL table...

Using the PIVOT table type

The PIVOT table type is very useful to sort and sum the columns in a table. It's similar to GROUP BY but with a more understandable layout. This sort of task is often used to sort and sum columns of data in a desktop spreadsheet program.

How to do it...

1. Launch the mysql command-line client application and connect to the test database on our MariaDB server. If the test database does not exist, create it first.

2. Run the following CREATE TABLE statement to create an expenses table:

   CREATE TABLE expenses ( 
     who varchar(64), 
     day varchar(10), 
     what varchar(64), 
     amount varchar(10) 
   );
   

3. Add some data to the table by executing the following command lines:

   INSERT expenses VALUES 
     ("Daniel","2013-09-01","Clothing",42.50), 
     ("Amy","2013-09-02","Food",5.22), 
     ("Daniel","2013-09-01","Clothing",27.75), 
     ("Daniel","2013-09-03","Food",10.27), 
     ("Amy","2013-09-03","Gas",42.84), 
     ("Amy","2013-09-01","Food",15.01), 
     ("Amy","2013-09-01","Clothing",11.00), 
     ("Daniel...

Using the OCCUR table type

If a table contains many columns, all of which contain similar types of data, it can be difficult to answer questions which deal with comparing those values. This is where the OCCUR data type can prove useful.

How to do it...

1. Launch the mysql command-line client application and connect to the test database on our MariaDB server. If the test database does not exist, create it first.

2. Run the following CREATE TABLE statement to create a gadgets table:

   CREATE TABLE gadgets (
     who varchar(64),
     phone int,
     tablet int,
     mp3player int,
     camera int
   
   );
   

3. Add some data to our gadgets table using the following statement:

   INSERT gadgets VALUES
     ("Jim",1,2,1,2),
     ("Bob",0,0,3,0),
     ("Tom",1,1,1,0),
     ("Joe",1,1,1,1),
     ("Rob",2,2,0,0),
     ("Tim",0,3,1,1)
   ;
   

4. Run the following statement to create our OCCUR table (replace username with a user that has read access rights to the gadget table without needing a password):

   CREATE TABLE gadgets_occur (
     who varchar(64) NOT NULL,
   ...

Using the WMI table type

Windows includes an interface through which various components of the operating system can provide useful system information. This interface is called Windows Management Instrumentation (WMI). The WMI table type allows us to easily connect to and display information from this interface.

Getting ready

As WMI is specific to the Windows operating system, this recipe uses Windows.

How to do it...

1. Launch the mysql command-line client application and connect to the test database on our MariaDB server. If the test database does not exist, create it first.

2. Run the following CREATE TABLE statement to create a WMI table:

   CREATE TABLE alias (
     friendlyname char(32) NOT NULL,
     target char(64) NOT NULL
   ) ENGINE=CONNECT TABLE_TYPE='WMI'
   OPTION_LIST='Namespace=root\\cli,Class=Msft_CliAlias';
   

3. Run the following SELECT statement to query the table:

   SELECT * FROM alias;
   

How it works...

The WMI table type maps rows to each instance of the related information. To accomplish this mapping...

Using the MAC address table type

The MAC table type allows us to look up and query various bits of information about the network connection and network setup of our local machine.

Getting ready

The MAC table type works only in Windows. So, this recipe requires the Windows OS.

How to do it...

1. Launch the mysql command-line client application and connect to the test database on our MariaDB server. If the test database does not exist, create it first.

2. Run the following CREATE TABLE statement to create a MAC table:

   CREATE TABLE host (
     hostname varchar(132) flag=1,
     domain   varchar(132) flag=2,
     ipaddr   char(16)     flag=15,
     gateway  char(16)     flag=17,
     dhcp     char(16)     flag=18,
     leaseexp datetime     flag=23
   ) ENGINE=CONNECT TABLE_TYPE=MAC;
   

3. Run the following SELECT statement to query the information about our current network settings:

   SELECT * FROM host;
   

How it works...

Information on our network cards and their current settings is actually pretty easy to get, so the MAC table type...