Inclusion of the TEXT MySQL Type

[tabedzki]

Feature Request

Problem

As our lab still uses the DataJoint-MATLAB, we are looking for a solution to represent objects that that can be used from both the Python and MATLAB interfaces. We seek to use the JSON format to store an unstructured set of parameters that will vary between the experiments we use in the lab. Our backend uses MariaDB which implements the JSON type as LONGTEXT which is not yet implemented in either version of DataJoint. Therefore, we looked into encoding the JSON as LONGBLOB which can be read by both Python and MATLAB versions of DataJoint but we have had trouble writing BLOBs to the database from Python in a manner that DataJoint MATLAB can read.

Requirements

When using (LONG)TEXT, Python should be stored similarly to (LONG)BLOB since MySQL's implementation of TEXT is very similar to the implementation of BLOB according to MySQL documentation. I would be willing to help write the MATLAB version for interoperability.

Justification

Adding support for this feature would allow for the MariaDB version of JSON to work since MariaDB encodes JSON as LONGTEXT.

Alternative Considerations

Do you currently have a work-around for this?

• Only using MATLAB to write BLOB to the database
• If it is possible, can someone write an example that shows a proper encoding of the numpy array into the format that MATLAB DataJoint expects? When I tried encoding via np.array(['hi'],dtype='U2'), after inserting and retrieving I got back array(['hi'], dtype=object).

Related Errors

Please include steps to reproduce provided errors as follows:

• OS : Mac
• MATLAB Version 2024a
• MySQL Version
• MySQL Deployment Strategy (local-native | local-docker | remote): remote
• DataJoint Version: 0.14.3
• Minimum number of steps to reliably reproduce the issue:
  • Add a json as numpy string array via DataJoint python then try to read from MATLAB
• Complete error stack as a result of evaluating the above steps

Error using mym
Blob data ingested utilizing DataJoint-Python version >=0.12 not yet supported.