Hi Monty, all On 07/08/2010, at 11:27 PM, Michael Widenius wrote:

We have plans to add dynamic columns into MariaDB soon. This will probably happen in 5.3, assuming there is some interest in this feature.

With dynamic columns you can store 'any' number of columns in a blob. In effect, each row in the database may have it's own set of columns. One will be able to trivially access and update data in the dynamic columns and also add/drop columns inside the blob.

Example usage:

SELECT column_get(blob, 1, varchar(100)) from table_with_dynamic;

UPDATE table_with_dynamic SET blob=column_add(blob, 2, "hello") where id=1;

UPDATE table_with_dynamic SET blob=column_del(blob,4) where id=5;

Note that 'column_add()' will replace any old value with the given column_id.

Future ideas: - Allow indexing with name instead of numbers. When this is done we can drop the type as part of column_get() - Allow indexing on dynamic fields.

You can find a full specification of this feature here: http://askmonty.org/worklog/Server-BackLog/?tid=34

The idea is good, based on what Google's BigTable (see the Hypertable specs) introduced. It provides a mild level of denormalisation, enabling better scalability. And in the case of MariaDB, you keep the same familiar and convenient API. That's important. I think the word "column" is very wrong in the above example. It's more like "subattributes". See what the terminology in bigtable/hypertable was... Perhaps Mark has insights on this too. I'm for it, it solves a real need that people are already trying to solve (with desperately bad design decisions ;-). Regards, Arjen. -- Arjen Lentz, Exec.Director @ Open Query (http://openquery.com) Exceptional Services for MySQL at a fixed budget. Follow our blog at http://openquery.com/blog/ OurDelta: packages for MySQL and MariaDB @ http://ourdelta.org

Dan Meany wrote:

One thing about dynamic columns is that they have sped up our development cycle by a factor of 5, since we are not spending time on hard coded binding layers, and analysts can create application tables instead of developers and DBAs. We have other mechanisms in place to support typing, validation, relationships, and UI crud screens with master-detail, all dynamically, with no app restart or sql scripts. But to make it work we need really good indexing which is what we can do using a regular mysql table. The concern with the blob approach is we'd need to wait for indexing, or use an external index. I looked at couch db but it doesn't seem to have the level of transactional support we need, and mySQL does.

If someone were to submit a patch for the 64 limit would that be acceptable?

I think it would be useful unless you force us always to use this limit. In other words the maximum number of tables in the query should be a parameter of the server. Regards, Igor.

Dan --- On Sun, 8/8/10, Arjen Lentz wrote:

...

From: Arjen Lentz Subject: Re: [Maria-developers] 64 table join limit To: monty@askmonty.org Cc: "maria-developers" Date: Sunday, August 8, 2010, 8:33 PM Hi Monty, all

On 07/08/2010, at 11:27 PM, Michael Widenius wrote:

...

We have plans to add dynamic columns into MariaDB soon. This will probably happen in 5.3, assuming there is some interest in this feature.

With dynamic columns you can store 'any' number of

columns in a blob. > In effect, each row in the database may have it's own set of columns. > One will be able to trivially access and update data in the dynamic > columns and also add/drop columns inside the blob. > > Example usage: > > SELECT column_get(blob, 1, varchar(100)) from table_with_dynamic; > UPDATE table_with_dynamic SET blob=column_add(blob, 2, "hello") where id=1; > UPDATE table_with_dynamic SET blob=column_del(blob,4) where id=5; > Note that 'column_add()' will replace any old value with the given column_id. > Future ideas: > - Allow indexing with name instead of numbers. When this is done we can drop the > type as part of column_get() > - Allow indexing on dynamic fields. > > You can find a full specification of this feature here: > http://askmonty.org/worklog/Server-BackLog/?tid=34

The idea is good, based on what Google's BigTable (see the Hypertable specs) introduced. It provides a mild level of denormalisation, enabling better scalability. And in the case of MariaDB, you keep the same familiar and convenient API. That's important.

I think the word "column" is very wrong in the above example. It's more like "subattributes". See what the terminology in bigtable/hypertable was... Perhaps Mark has insights on this too.

I'm for it, it solves a real need that people are already trying to solve (with desperately bad design decisions ;-).

Regards, Arjen. --Arjen Lentz, Exec.Director @ Open Query (http://openquery.com) Exceptional Services for MySQL at a fixed budget.

Follow our blog at http://openquery.com/blog/ OurDelta: packages for MySQL and MariaDB @ http://ourdelta.org

_______________________________________________ Mailing list: https://launchpad.net/~maria-developers Post to : maria-developers@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-developers More help : https://help.launchpad.net/ListHelp

_______________________________________________ Mailing list: https://launchpad.net/~maria-developers Post to : maria-developers@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-developers More help : https://help.launchpad.net/ListHelp