# What format to use to store expressions in database?

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.

-
What are you aiming for? Minimum packed size, ease of putting things back into MMA, cross compatibility, speed? Could you say a few more words about the requirements? –  tkott May 10 '12 at 0:46
For example ToString[x, InputForm] will let you go back to MMA but it would be better to answer @tkott to get a better answer. –  FJRA May 10 '12 at 13:13
See addition in the OP. –  Problemaniac May 10 '12 at 15:42

## 3 Answers

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]

-
It's a requirement that {_String, _Real} should not be split into two columns. Sorry if I'm not clear with that. Also, the depth and length of the list is indefinite. So separating it is not generally possible. –  Problemaniac May 10 '12 at 20:55
About the pair of transformations ToString[expr, InputForm] and ToExpression[str, InputForm], is it necessary for expressions that's NumericQ True? –  Problemaniac May 10 '12 at 20:57
@MaThEmAtika no, for NumericQ===True, you should be safe saving the data in the float or double mysql column. As for the extra list then I would keep it as a text and use tostring/toexpression –  tkott May 10 '12 at 21:19

In case you need to store real numbers exactly as Mathematica has calculated them you can store them as a string. This may be required if you retrieve numbers off your database for comparison - the database will probably reduce the precision resulting in mismatches. To store a real as a string you can make use of InputForm. However, exponential forms have to be reinterpreted. That is done by the code here:

Convert Real to String : http://mathematica.stackexchange.com/a/17723/363

E.g.

a = 123456789012345678901234567.123456789012345678901234567;

ans = longform[a]


"123456789012345678901234567.123456789012345678901234567"

This is similar in principle to what tkott achieves with ToString[InputForm[expression]], except now reals rendered in exponential form in InputForm can be read from the database by programs other that Mathematica, since they do not require re-interpretation.

-

I use SQLExpr and it works nicely. Here is how you can implement it:

SQLInsert[yourConn, "yourTableName", {"otherColumn", "exprColumn"},
{otherData, SQLExpr[yourExpr]}]


The data type for storing SQLExpr stuff must be CHAR, VARCHAR or its derivatives (i.e., TEXT, LONGTEXT, etc., ...).

Note that this technique requires that you load the DatabaseLink package:

Needs["DatabaseLink"]
`
-