mORMot Open Source

cypriotcalm

Member

Registered: 2015-02-18

Posts: 122

read/write MariaDB dynamic columns binary json

Hi!

Does mORMot support the MariaDB dynamic columns?

I mean, if I execute the following Insert-SQL in MariaDB the JSON-data are stored in a BLOB-field as binary. In order to get the JSON-text I have to select it with COLUMN_GET(..):

INSERT INTO `person` (`ID`, `Data`) VALUES (1, COLUMN_CREATE('Firstname','Max','Lastname','Mustermann'));

SELECT `ID`, COLUMN_GET(`Data`, 'Firstname' AS CHAR) AS Firstname, COLUMN_GET(`Data`, 'Lastname' AS CHAR) AS Lastname FROM `person`;

In mORMot I can also save a JSON-object in a blob-field, but the framework saves it as a text.

Is there a possibility to write/read the JSON in the MariaDB compatible mode?

Thx for your answer!

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,662

Website

Re: read/write MariaDB dynamic columns binary json

This is not yet supported.

In fact, this is pretty close to the JSONB format of latest PostgreSQL.
But it sounds like if the COLUMN_CREATE feature is much more limited than JSONB, e.g. it does not support arrays.
See https://mariadb.com/kb/en/mariadb/dynamic-columns/
So IMHO I do not see any benefit of using it, in respect to a mORMot variant field, stored as text, and handled as TDocVariant in our framework.

cypriotcalm

Member

Registered: 2015-02-18

Posts: 122

Re: read/write MariaDB dynamic columns binary json

This is not yet supported.

In fact, this is pretty close to the JSONB format of latest PostgreSQL.
But it sounds like if the COLUMN_CREATE feature is much more limited than JSONB, e.g. it does not support arrays.
See https://mariadb.com/kb/en/mariadb/dynamic-columns/
So IMHO I do not see any benefit of using it, in respect to a mORMot variant field, stored as text, and handled as TDocVariant in our framework.

What I would like to do is to execute a SELECT-Satetment on a JSON-column in an SQL browser after I have saved some JSON-data. And if I save this data in the "MariaDB" compatible format (now only throug an INSERT using the command COLUMN_CREATE), I can execute a SELECT (see below) and I'll get in my result (ID, Firstname, Lastname) although the column `Data` is saved as JSON or rather JSONB.

SELECT `ID`, COLUMN_GET(`Data`, 'Firstname' AS CHAR) AS Firstname, COLUMN_GET(`Data`, 'Lastname' AS CHAR) AS Lastname FROM `person`;

I hope, you understand what I mean ;-)

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,662

Website

Re: read/write MariaDB dynamic columns binary json

I understand what you mean, but this is not supported by the ORM, and due to the very limited set of features (this is not JSON compatible, e.g. it does not allow to store arrays or nested objects), we would not support it soon.
I guess that JSONB support for latest PostgreSQL would be supported first on our side.

Of course, if you provide a patch to support this dynamic column feature, we would include it to mORMot!

cypriotcalm

Member

Registered: 2015-02-18

Posts: 122

Re: read/write MariaDB dynamic columns binary json

:-) I see... I would like to create a patch but I am not sure if I can do this and I don't know where and how to start because I have never coded for the open source projects!? :-(