What format to use to store expressions in database?

Question

I am storing lists of expressions into a MySQL database. Each list is of form

{_String, {_String, _Real}, _Real}

What data format should each column of the database table be?

I am thinking TEXT, TEXT, FLOAT. Any suggestions?

I need to be able to import values back to Mathematica (with SQLExecute[], SQLSelect[], etc.) and be able to work with expressions in Mathematica program.

Answer 1

It sounds like all you are looking for is a generic storage using MySQL. Then your translation of _String to TEXT makes sense, but I would consider _Real as a DOUBLE column, depending on your precision requirements.

Additionally, I would probably separate the inner list:

{_String, {_String, _Real}, _Real}

into:

{_String, _String, _Real, _Real}

So that you would have four columns (TEXT, TEXT, DOUBLE, DOUBLE). You can translate back and forth with some patterns like:

{a_String, b_String, c_Real, d_Real} -> {a, {b, c}, d}

and

{a_String, {b_String, c_Real}, d_Real} -> {a, b, c, d}

Before and after import.

If you need to store MMA expressions, then the most portable way, I believe, is to store them as InputForm, as mentioned by FJRA in the comment to the question. To do so, you would need to do:

ToString[InputForm[expression]]

And then the reverse:

ToExpression[importedString,InputForm]