Last week Oleksandr "Sanja" Byelkin and I pushed Dynamic columns into MariaDB 5.3.
Dynamic columns allows you to store a different set of columns for every row in the table. This allows you to solve things that you can't normally do with a relational database, like the web store problem where you want to store attributes for different things, like a computer (memory, speed, weight) and t-shirt (size, color).
Dynamic columns works by storing the extra columns in a blob and having a small set of functions to manipulate it. The functions exist both in SQL and in the MariaDB client library to allow you to manipulate the data where it suits you best. The client code is BSD to make it easy to port the code to other languages.
You can use a dynamic column in SQL anywhere you can use a column or function:
SELECT name, COLUMN_GET(blob, 1 as char(10)) FROM t1 WHERE COLUMN_EXISTS(blob, 1);
SELECT COLUMN_GET(blob, 1 as char(10)) as color, count(*) FROM t1 group by COLUMN_GET(blob, 1 as char(10));
COLUMN_GET() is used above to access the dynamic column stored in the table colum 'blob'.
The full set of SQL functions are:
Create data for a dynamic column:
COLUMN_CREATE(column_nr, value [as type], [column_nr, value [as type]]...)
Add a new column or update an old one:
COLUMN_ADD(dynamic_col, column_nr, value [as type], [column_nr, value [as type]]...)
Delete a dynamic column:
COLUMN_DELETE(dynamic_column, column_nr, column_nr...);
Get the value from a dynamic column casted to a given type. (The cast is required as SQL is a strongly typed language and we need to send the result type to the client before we have executed the query)
COLUMN_GET(dynamic_column, column_nr as type)
Check if a column exists:
Get a list of the existing dynamic columns:
You can find more information in the MariaDB /MySQL knowledge base.
It's very nice to finally get this work done. I got the idea for Dynamic columns 8 years ago and always wanted to do this in MySQL but never managed to find the time to do it.
The recent trend of combining NoSQL with SQL finally pushed me to get this done as this also opens doors to do new existing things with MariaDB. There are people already working on using Dynamic columns as a building block for creating connections to NoSQL databases like HBase.
At the O'Reilly MySQL conference 2011 my talk about Dynamic columns was well attended. A large portion of the attendees said this is something they need and want to start testing / using right away as it can replace some things they are already doing / planning to do. We will have another talk about dynamic columns and combining NoSQL and SQL at Open Source bridge on June 21-24 in Portland, Oregon.
Please try this out and send your feedback to the MariaDB email list or come to Open Source bridge and discuss it with us in person.
MySQL shell prompt vs MongoDB shell prompt
10 hours ago