maria-discuss team mailing list archive

[Tom Worster <fsb@xxxxxxxxxx>]

On 1/28/15, 5: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.

Numeric types don't always match very well. JSON, like JavaScript, has
only one numeric type: number. Unfortunately it is the wrong type: float.
In the JSON spec it is decimal with an exponent but in real life is nearly
always an IEEE double on at least one side of the serialization. When you
do exact numerical work, that's not acceptable.

I suppose the database server has fulfilled its obligation if the decimal
JSON representation of the number is, on read, an exactly equivalent
representation of the number literal that was in the corresponding
COLUMN_CREATE() expression.
 

>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)

Yes.


>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.

Yes.


>
>>
>> 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?

No. 

When I use COLUMN_CREATE('x', ___ ) I provide in the ___ a literal that
has, in its SQL context, both a value and a datatype. So I have a way to
put something into the database that I cannot read back out.
COLUMN_CREATE() is the SQL way to deposit (value, datatype) into the
database. There is no corresponding way SQL way to make the withdrawal of
(value, datatype). My proposal was aimed at resolving that.

My understanding is that we have to fix the datatype of what will be
returned on a reading before any data is read. So we have been talking
about just fixing it as string and encode the (value, datatype) tuple into
it. OK. But why a JavaScript literal?

In documenting my ORM stuff, I wrote "In Maria the situation is worse.
Data is saved via SQL and retrieved via JSON. This may seem perverse
but..." And I end up with a lifecycle for a model in the ORM that's just
strange: Start in PHP machine encoding. Convert to SQL and send to DB.
Request JSON representation of the data from the DB. Convert the JSON back
to machine encoding. PHP isn't doing anything weird. It uses very
conventional representations just like in C.

PHP -> SQL -> JSON -> PHP. There is exactly one too many data
representations here.

Do I explain myself any better now?

Tom