maria-discuss team mailing list archive

[Tom Worster <fsb@xxxxxxxxxx>]

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.

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.

Tom

From:  Peter Laursen <peter_laursen@xxxxxxxxxx>
Date:  Wednesday, January 28, 2015 at 4:25 PM
To:  Roberto Spadim <roberto@xxxxxxxxxxxxx>
Cc:  Tom Worster <fsb@xxxxxxxxxx>, maria-discuss email list
<maria-discuss@xxxxxxxxxxxxxxxxxxx>
Subject:  Re: [Maria-discuss] The future of Dynamic Columns

@Roberto.  A comment to "i think it's a nice human readable format for
arrays/objects, nothing less nothing more".

But why will you then need to *store* as JSON? Could not something like
"SELECT .. INTO JSON ..." do the trick?
(with hindsight: "SELECT .. INTO XML ..." could have been implemented in
MySQL when other XML functionalities were implemented but never was, it
seems. You can mysqldump to XML but not "SELECT .. INTO XML ..." - using
OUTFILE or not - refer
http://dev.mysql.com/doc/refman/5.6/en/select-into.html)


-- Peter

On Wed, Jan 28, 2015 at 7:29 PM, Roberto Spadim <roberto@xxxxxxxxxxxxx>
wrote:
> i think a good start is "expression index​es" and after json syntax to SQL
> language, and some new functions to handle json/etc
> 
> CREATE INDEX name ON table (  some_function(field)   )
> maybe dynamic column functions, or json functions? or any function
> deterministic (for example, NOW() function can't be used)
> 
> and use it at WHERE clausules
> 
> WHERE some_function(field) ='1234'   <- use index
> WHERE some_function(table.field) ='1234'
> WHERE some_function(field) IN ( sub select )
> WHERE some_function(field) LIKE "blabla%"
> WHERE some_function(field) LIKE "%blabla%"
> WHERE some_function(field) LIKE "%blabla"
> 
> _______________________________________________
> 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
>