yeap, i agree in my opnion... today without "expression index" to allow a faster search (on attribute or value) it's not possible to only use dynamic columns cause we get full table scan, today a second table with attribute-value should be necessary in this case to have a better performace
what we can do today is virtual column + index, but this consume more space with data+index store, instead of only index store, and we must rewrite queries to use virtual column 2015-01-28 23:20 GMT-02:00 Adam Scott <adam.c.sc...@gmail.com>: > Just wanted to add, any application that wants to store forms data without > having to create a new table every time you create a new form could benefit > from dynamic columns. > > In the database design world, any design that is of the EAV > (Entity-Attribute-Value) style would benefit from dynamic columns. Having > to extract and analyze EAV tables, for the developer, is tricky and > burdensome. This model seems to show up in the literature first in > Medical settings and is used in basic science research as well. (see > http://en.wikipedia.org/wiki/Entity%E2%80%93attribute%E2%80%93value_model) > > For those that need it, dynamic columns is powerful and makes life much > easier. Unfortunately, often those that need dynamic columns do not know > it exists. If they did, they would jump for joy. > > Adam > > > On Wed, Jan 28, 2015 at 3:41 PM, Oleksandr Byelkin <sa...@montyprogram.com > > wrote: > >> Hi, Tom! >> >> On 28.01.15 23:01, Tom Worster wrote: >> >>> While functions for getting and setting via json might be useful, I >>> think they are peripheral to the core datatype problem here. >>> >>> The interface to dynamic columns in Maria is SQL. The internal >>> datatypes are SQLish. We can be completely sure of what was set, in SQL >>> terms, by a COLUMN_CREATE(). But if we use a JSON getter then we might get >>> something different because JSON datatypes don't match SQL datatypes. >>> Sometimes it's no problem but not in general -- depends on the datatypes >>> involved and the application's tolerance of differences. >>> >> AFAIK we lack only boolean type and arrays (could be emulated now but you >> can't say if it is array or object with numeric keys) to fit json types. >> >> column_create/alter do not require type (detect automatically), so type >> there can be mentioned to enforce some encoding you want (store dates as >> dates but not string) >> >> column_get() require type of output, if you do not care use CHAR. But >> this is required by SQL expression implementation. There is json output >> which do not need type of columns. >> >> >>> What's missing is a getter that tells us the value and datatype of a >>> dynamic column, a getter that tells us what was set. >>> >>> What if we had a getter that returns a string like: >>> >>> COLUMN_GET_SQL(`product`, 'price') >>> >> "CAST(123.456 AS DECIMAL(11,3))" >>> >>> And Maria promises us that we could use that expression to write the >>> value back and the result will be the exact same thing. A sort of >>> idempotency promise (kinda). >>> >>> This doesn't make the interface any less clunky (on the contrary) but I >>> have resigned myself to a clunky interface. The only alternative is to >>> forget all this and start something different. SQL is an insanely clunky >>> old language and that's why we use some kind of abstraction software to >>> make it nicer. If we're going to use SQL we may as well try to use what's >>> there. >>> >>> Something like this COLUMN_GET_SQL() would at least allow the >>> application the possibility to get back what it put in. >>> >> >> I am not sure that I understand what you need. Is it ability to know >> internal storage type? >> >> [skip] >> >> _______________________________________________ >> Mailing list: https://launchpad.net/~maria-discuss >> Post to : maria-discuss@lists.launchpad.net >> Unsubscribe : https://launchpad.net/~maria-discuss >> More help : https://help.launchpad.net/ListHelp >> > > > _______________________________________________ > Mailing list: https://launchpad.net/~maria-discuss > Post to : maria-discuss@lists.launchpad.net > Unsubscribe : https://launchpad.net/~maria-discuss > More help : https://help.launchpad.net/ListHelp > > -- Roberto Spadim SPAEmpresarial - Software ERP Eng. Automação e Controle
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
