MariaDB Cookbook (2014)

Chapter 10. Exploring Dynamic and Virtual Columns in MariaDB

In this chapter, we will cover the following recipes:

· Creating tables with dynamic columns

· Inserting, updating, and deleting dynamic column data

· Reading data from a dynamic column

· Using virtual columns

Introduction

One recent trend in the database world has been the development and use of NoSQL databases. This trend arose from a realization that relational database servers that use SQL, such as MariaDB, are not always the right tool for the job. Sometimes nonrelational, specialized, scalable, and clustered key-value databases work better for specific tasks.

Another trend is the addition of virtual columns to databases. These columns don't change how the data is accessed as dynamic columns do. What they do is change how the data in them is stored. In short, the data is derived from the values of other columns in the row, similar to a spreadsheet.

The MariaDB developers see the value in such nontraditional database features, and have implemented these and others in MariaDB to make it as flexible and as capable a database server as possible.

Both the chapters following this one delve into a couple of additional NoSQL features of MariaDB, HandlerSocket and the Cassandra storage engine, respectively.

Tip

This chapter includes several syntax diagrams and data type definitions. The parts of these diagrams and definitions in square brackets [] are optional. Also, a series of three dots ... (also called an ellipsis) means that the previous part in the bracket can be repeated.

Creating tables with dynamic columns

Tables with dynamic columns are similar to regular tables, but not quite the same. Similar to standard tables, they have columns and rows. The difference is that each row can have a different number of columns holding the data and the data types that are appropriate for that row.

How to do it...

1. Launch the mysql command-line client and connect to our MariaDB server.

2. Create a test database and use it with the following command:

3. CREATE DATABASE IF NOT EXISTS test;

4. USE test;

5. Create a table with a standard PRIMARY KEY column and a BLOB column using the following commands:

6. CREATE TABLE dyn_example (

7. id SERIAL PRIMARY KEY,

8. dyn_cols BLOB

9. );

How it works...

The dynamic columns feature in MariaDB is a set of special functions that allow us to define and redefine the number of columns and their data types as needed on a row-by-row basis without altering our table configuration. These special columns exist and are defined as a standard BLOB column in our CREATE TABLE command. But unlike a regular BLOB column, we will only interact with this column using several special dynamic columns helper functions. We will cover these helper functions in the Inserting, updating, and deleting dynamic column data and Reading data from a dynamic column recipes in this chapter.

The two things that a table with dynamic columns needs are an id column (or something similar) for PRIMARY KEY and a column with the type BLOB. Other columns can also be a part of the definition, but these are the ones that need to be there.

There's more...

When using dynamic columns, there are a couple of limitations to know about. The first is that the maximum number of dynamic columns we can define inside a single dynamic column BLOB is 65,535. Next, the total length of a packed dynamic BLOB column is whatever the max_allowed_packet size variable is set to, up to one gigabyte.

Normally, the server handles all the interactions with dynamic columns and the client only calls the various dynamic columns functions. It is possible, however, for clients to directly manipulate and interact with dynamic columns using an API. The API is part of thelibmysql client library.

See also

· The full documentation of dynamic columns can be found at https://mariadb.com/kb/en/dynamic-columns/ and https://mariadb.com/kb/en/dynamic-columns-in-mariadb-10/

· The documentation of the dynamic columns API is available at https://mariadb.com/kb/en/dynamic-columns-api/

· Refer to the Inserting, updating, and deleting dynamic column data and Reading data from a dynamic column recipes in this chapter

Inserting, updating, and deleting dynamic column data

Inserting new data and updating existing data in a dynamic column is not the same as with traditional columns. Without some help from a set of special dynamic columns functions, the standard MariaDB INSERT, UPDATE, and DELETE statements do not understand how to work with a dynamic column or the data stored in it. They will only see it as a BLOB column. This recipe introduces and demonstrates the basic functions used when interacting with a dynamic column.

Getting ready

First, you need to complete the Creating tables with dynamic columns recipe.

How to do it...

1. Launch the mysql command-line client and connect to the test database in our MariaDB server.

2. Insert some values into the dyn_example table we created earlier:

3. INSERT INTO dyn_example (dyn_cols) VALUES

4. (COLUMN_CREATE('name','t-shirt', 'color','blue'

5. AS CHAR, 'size','XL' AS CHAR)),

6. (COLUMN_CREATE('name','t-shirt', 'color','blue'

7. AS CHAR, 'size','L' AS CHAR)),

8. (COLUMN_CREATE('name','t-shirt', 'color','black'

9. AS CHAR, 'size','M' AS CHAR)),

10. (COLUMN_CREATE('name','flashlight', 'color','black'

11. AS CHAR, 'size','AAA' AS CHAR, 'num', 2 AS INT)),

12. (COLUMN_CREATE('name','shovel', 'length','5'));

13. Update a dynamic column in a single row using the following command:

14.UPDATE dyn_example SET

15. dyn_cols=COLUMN_ADD(dyn_cols, 'name', 'torch')

16.WHERE COLUMN_GET(dyn_cols, 'name' AS CHAR) = 'flashlight';

17. Add a dynamic column to a single row using the following command:

18.UPDATE dyn_example SET

19. dyn_cols=COLUMN_ADD(dyn_cols,'length', 6)

20.WHERE COLUMN_GET(dyn_cols, 'name' AS CHAR) = 'torch';

21. Delete a column from a single row using the following command:

22.UPDATE dyn_example SET

23. dyn_cols=COLUMN_DELETE(dyn_cols,'length')

24.WHERE COLUMN_GET(dyn_cols, 'name' AS CHAR) = 'shovel';

How it works...

The standard SQL INSERT, UPDATE, and DELETE statements do not work as expected on a dynamic column. These statements see the dynamic column as a regular blob column, and if we try to insert or update it directly, we will likely end up corrupting the row. To properly interact with this column, we need to use special dynamic columns functions. The functions for inserting, updating, and deleting data are COLUMN_CREATE, COLUMN_ADD, and COLUMN_DELETE.

Each dynamic column in a row can have a different number of columns, and each of these dynamically defined columns can have the following different data types:

Defining the data type is optional when creating a new dynamic column or updating an existing dynamic column, but it is mandatory to specify a data type when reading data from a dynamic column.

The COLUMN_CREATE function is used as part of an INSERT statement to both add a new row and to define the dynamic columns in that row. Unlike the COLUMN_ADD and COLUMN_DELETE functions, where we specify the dynamic columns BLOB column name inside the function, in the COLUMN_CREATE function, this is taken care of by the INSERT statement this function is a part of. The syntax of this function is as follows:

COLUMN_CREATE(column_name, value [AS type][, column_name, value [AS type]]...);

The COLUMN_ADD function is used as part of an UPDATE statement to either update an existing dynamic column in one or more existing rows or to add a new column to one or more existing rows. The syntax of this function is as follows:

COLUMN_ADD(dyncol_blob_name, column_name, value [AS type][, column_name, value [AS type]]...);

The COLUMN_DELETE function is used as part of an UPDATE statement to delete the specified dynamic column or columns. The syntax of this function is as follows:

COLUMN_DELETE(dyncol_blob_name, column_name[, column_name]...);

There's more...

The first version of the dynamic columns feature, introduced in MariaDB 5.3, only allowed for numbered columns. MariaDB 10.0 was the first version of MariaDB to support named dynamic columns. So, in MariaDB 5.3 and MariaDB 5.5, in the place where we now specify the column name, we will put a number instead. If we are working with the code that was developed originally for this first version of dynamic columns, we will see numbers instead of column names.

MariaDB 10.0 and later supports the old style of dynamic columns only so long as our code consistently refers to the columns by number. Once we start using names, our dynamic columns will be automatically upgraded to the new format for dynamic columns and we will be unable to continue using numbers to refer to our dynamic columns.

Nesting dynamic columns

Dynamic columns can be nested if we put one dynamic column function inside another. For example, we can perform the following query:

INSERT INTO dyn_example (dyn_cols) VALUES

(COLUMN_CREATE('type','parent', 'name', 'Mary',

'child1', COLUMN_CREATE('name', 'Sue', 'eyes','brown'),

'child2', COLUMN_CREATE('name', 'Bob',

'grandchild', COLUMN_CREATE('name', 'baby'))

));

This INSERT statement creates a dynamic column with two nested dynamic columns inside it, one of which has its own nested dynamic column. The names of each column in a dynamic column have to be unique, but we can duplicate names as long as they are in their own uniquely-named nested dynamic column.

The Reading nested dynamic columns section of the Reading data from a dynamic column recipe in this chapter has instructions on how to query and read nested dynamic column's data.

See also

· The full documentation of dynamic columns can be found at https://mariadb.com/kb/en/dynamic-columns/ and https://mariadb.com/kb/en/dynamic-columns-in-mariadb-10/

· Refer to the Creating tables with dynamic columns and Reading data from a dynamic column recipes in this chapter

Reading data from a dynamic column

Reading data from a dynamic column is not the same as with traditional columns. Without some help from a set of special dynamic columns functions, the standard MariaDB SELECT statements will not understand how to properly read the data stored in a dynamic columns BLOB. They will see it as a BLOB column and treat it like any other BLOB. This recipe introduces and demonstrates the basic functions used when reading a dynamic column.

Getting ready

Complete the Creating tables with dynamic columns recipe and the Inserting, updating, and deleting dynamic column data recipe in this chapter.

How to do it...

1. Launch the mysql command-line client. Connect to our MariaDB server and the test database.

2. Discover the columns in our data:

3. SELECT id, COLUMN_LIST(dyn_cols) FROM dyn_example;

The following screenshot displays the columns in our data:

4. Read data from our table using the following commands:

5. SELECT id,

6. COLUMN_GET(dyn_cols, 'name' AS CHAR) AS 'name',

7. COLUMN_GET(dyn_cols, 'color' AS CHAR) AS 'color',

8. COLUMN_GET(dyn_cols, 'size' AS CHAR) AS 'size',

9. COLUMN_GET(dyn_cols, 'num' AS INT) AS 'num'

10.FROM dyn_example;

The following screenshot displays data selected using the preceding command:

11. Select everything from our table and output each dynamic columns BLOB as a JSON object using the following command:

12.SELECT id, COLUMN_JSON(dyn_cols) FROM dyn_example;

The preceding command displays the following screenshot:

13. Check each dynamic columns blob to see if the num column exists in it:

14.SELECT id, COLUMN_EXISTS(dyn_cols, 'num')

15. FROM dyn_example;

The preceding command displays the following screenshot:

16. Check that each dynamic columns BLOB columns in each row is valid using the following command:

17.SELECT id, COLUMN_CHECK(dyn_cols)

18. FROM dyn_example;

The preceding command displays the following screenshot:

How it works...

To read dynamic columns, we must use either the COLUMN_GET or COLUMN_JSON helper functions. If we try to use a standard SELECT statement without using these functions, we will get data that appears to be garbled. In fact, it is a binary representation of our data that the dynamic column's API understands and can read but that SELECT, by itself, cannot. This is similar to how a music program understands how to read a binary MP3 file, but not a file containing a 3D model of an airplane.

The COLUMN_GET function requires us to specify the name of our dynamic columns BLOB column along with the name of the dynamic column inside the blob we want to read and the data type of that dynamic column. This is in contrast to the COLUMN_ADD and COLUMN_CREATEfunctions, where defining the data type is optional. Also, we must call this function for each individual column we want to retrieve, as it does not allow us to specify multiple columns at once. The syntax of the COLUMN_GET function is as follows:

COLUMN_GET(dyncol_blob_name, column_name AS type);

To discover what columns exist in a given dynamic columns BLOB of a row or of several rows, we use the COLUMN_LIST function. If we omit the WHERE clause, we will get a list of the columns in every dynamic columns blob for every row in our table. The syntax of this function is as follows:

COLUMN_LIST(dyncol_blob_name);

The COLUMN_EXISTS function allows us to check if a given column exists in a given dynamic columns BLOB of a given row or rows (or all rows if we omit a WHERE clause). The function returns 1 if the column exists, and 0 if it doesn't. The syntax of this function is as follows:

COLUMN_EXISTS(dyncol_blob_name, column_name);

The COLUMN_JSON function allows us to easily grab all of the columns in a dynamic columns BLOB and output it as a standard JSON object sentence. Because this function outputs all of the dynamic columns, we do not need to specify or know what columns are in the row or rows we are selecting. The syntax of this function is as follows:

COLUMN_JSON(dyncol_blob_name);

The COLUMN_CHECK function allows us to verify that a given dynamic columns BLOB is valid and not corrupted. The syntax of this function is as follows:

COLUMN_CHECK(dyncol_blob_name);

There's more...

The first version of dynamic columns included in MariaDB 5.3 and MariaDB 5.5 did not allow for column names. Instead, columns were referred to with numbers. These old-style dynamic columns are still supported in MariaDB 10.0 and above, but the output is slightly different. For example, the COLUMN_LIST function, if it is used to query one of these old-style dynamic column's blobs, will return a comma-separated list of column numbers instead of a comma-separated list of column names.

Reading nested dynamic columns

Nested dynamic columns represent a particular challenge when we want to read the data. For example, if we input the example nested dynamic columns as demonstrated in the Nesting dynamic columns section of the Inserting, updating, and deleting dynamic column data recipe, and if we try to get the data using the COLUMN_GET function as follows, our output result will appear garbled:

SELECT

COLUMN_GET(dyn_cols, 'child1' AS CHAR) as 'child1',

COLUMN_GET(dyn_cols, 'child2' AS CHAR) as 'child2'

FROM dyn_example WHERE

COLUMN_GET(dyn_cols, 'type' AS CHAR) = 'parent';

The output result will appear as shown in the following screenshot:

Instead, we must use the COLUMN_JSON function to properly select the nested dynamic columns data, using the following command:

SELECT COLUMN_JSON(dyn_cols)

FROM dyn_example WHERE

COLUMN_GET(dyn_cols, 'type' AS CHAR) = 'parent';

The preceding command displays the output shown in the following screenshot:

See also

· The full documentation of dynamic columns can be found at https://mariadb.com/kb/en/dynamic-columns/ and https://mariadb.com/kb/en/dynamic-columns-in-mariadb-10/

· Refer to the Creating tables with dynamic columns and Inserting, updating, and deleting dynamic column data recipes in this chapter

Using virtual columns

The virtual columns feature of MariaDB allows us to create columns which contain precalculated or calculated on-the-fly values.

How to do it...

1. Launch the mysql command-line client and connect to our MariaDB database.

2. Create a test database and switch to that database using the following command:

3. CREATE DATABASE IF NOT EXISTS test;

4. USE test;

5. Create a table with virtual columns using the following command:

6. CREATE TABLE virt_cols (

7. id SERIAL PRIMARY KEY,

8. surname VARCHAR(64),

9. givenname VARCHAR(64),

10. uid INT AS (id + 1000) VIRTUAL,

11. username VARCHAR(6) AS

12. (LOWER(CONCAT(LEFT(givenname,1),(LEFT(surname,5))))) PERSISTENT);

13. Examine the structure of the table using the following command:

14.DESCRIBE virt_cols;

The DESCRIBE command displays the structure of the table as shown in the following screenshot:

15. Show a CREATE TABLE command that will recreate the exact table (including the virtual columns):

16.SHOW CREATE TABLE virt_cols\G

The preceding command displays the following output:

17. Insert some data as follows:

18.INSERT INTO virt_cols (surname,givenname) VALUES

19. ('Packer','Boyd'),('Uchtdorf','Dieter'),

20. ('Ballard','Russell'),('Holland','Jeffrey'),

21. ('Cook','Quentin'),('Bednar','David');

22.INSERT INTO virt_cols (surname,givenname,uid,username) VALUES

23. ('Christofferson','Todd', DEFAULT, DEFAULT),

24. ('Andersen','Neil', DEFAULT, DEFAULT);

25. Select the data from our virt_cols table using the following command:

26.SELECT * FROM virt_cols;

The preceding command displays the following output:

How it works...

The virtual columns feature of MariaDB allows us to create special columns in our table that have calculated values based on the output of a function (or a combination of functions). These values can be either PERSISTENT, meaning the value is stored in the database and only updated when the row is updated, or VIRTUAL, meaning the value is calculated each time the row is read.

When using a standard DESCRIBE statement to examine the structure of a table with virtual columns, the EXTRA column in the output will tell us whether a column is virtual or not with the presence of the text VIRTUAL or PERSISTENT to identify each type of virtual column. What the DESCRIBE statement will not do is show us the function or combination of functions and operators which determine the value of the virtual column. For that, we need to use the SHOW CREATE TABLE command.

When inserting or updating data in a table with virtual columns, we can either choose to not specify the columns in our SQL statement or to go ahead and specify them but use the DEFAULT key word instead of providing a value. In our recipe, we perform both actions.

Selecting data from a table with virtual columns is just like selecting from a table without virtual columns. The only difference will be if the function calculating the value of a VIRTUAL column takes a noticeable amount of time to run. A PERSISTENT virtual column, becauseits calculated value is stored in the database, will return results as fast as regular columns in the table, but a VIRTUAL column's value is calculated every time the table is queried.

There's more...

Virtual columns have some limitations. For starters, they can only be used with InnoDB, XtraDB, Aria, and MyISAM tables.

Also, indexes are only partially supported for virtual columns. This is because virtual columns do not support primary keys. It is possible to have an index on a PERSISTENT virtual column, but even then statements such as UPDATE CASCADE, ON UPDATE SET NULL, and ON DELETE SET NULL are not allowed.

That said, things such as triggers and stored procedures are fully supported by virtual columns.

See also

· The full documentation of virtual columns in MariaDB is available at https://mariadb.com/kb/en/virtual-columns/