maria-discuss team mailing list archive

[Roberto Spadim <roberto@xxxxxxxxxxxxx>]

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.scott@xxxxxxxxx>:

> 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 <sanja@xxxxxxxxxxxxxxxx
> > 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@xxxxxxxxxxxxxxxxxxx
>> Unsubscribe : https://launchpad.net/~maria-discuss
>> More help   : https://help.launchpad.net/ListHelp
>>
>
>
> _______________________________________________
> Mailing list: https://launchpad.net/~maria-discuss
> Post to     : maria-discuss@xxxxxxxxxxxxxxxxxxx
> Unsubscribe : https://launchpad.net/~maria-discuss
> More help   : https://help.launchpad.net/ListHelp
>
>


-- 
Roberto Spadim
SPAEmpresarial - Software ERP
Eng. Automação e Controle