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:
Some examples:
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:
COLUMN_EXISTS(dynamic_column, column_nr);
Get a list of the existing dynamic columns:
COLUMN_LIST(dynamic_column);
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.
Subscribe to:
Post Comments (Atom)
Posts
3 comments:
That's all nice and well but essentially not something you couldn't have achieved with plain MySQL, XML snippets in blobs and Xpath functions. Only this way it's a bit more formal and most probably with less overhead.
The important question is: Will I be able to put an index on a dynamic column? Right now the major downside of storing XML snippets is that searching for specific values always forces a full table scan.
Yes, you can do some of this by using XML, but these functions are notable faster and takes a fraction of the space. They are also easier to use on the client side.
For now, the only way to index a dynamic column is by using virtual functions (something we introduced in MariaDB 5.2).
We plan to extend the storage engine API to make it easy for a storage engine to use functional indexes. When this is done, you can use this to index virtual columns!
To make Dynamic Columns even easier to use, I think support for the feature in a really good ORM is the way to go. So for my use I added support for 10.0 dyncols to the Active Record ORM in the Yii 2 Framework:
- https://github.com/tom--/dynamic-ar
For even more convenience, I also made a rough-and-ready bundle for Sequel Pro
- https://github.com/tom--/sequel-pro-maria-dynamic-column
I hope you don't mind me tooting my own horn like this. But I'm for anything that will help people use Maria's Dynamic Columns.
Post a Comment