If you're using SQLite 3, the TEXT type stores data UTF encoded, while the BLOB type stores it raw. BLOB is more geared toward binary data storage.

If you're really concerned about problems due to UTF encoding, use a BLOB. It looks like Storable deals with UTF8 fine, but I don't know how much it likes UTF16. Since Storable generates a string, I would probably store it as a text column.

If you're using SQLite 2, all the datatypes are really strings. You could declare the column as an INTEGER and it wouldn't care. I would still pick TEXT, because it describes the data the best.

Edit: I misread the Storable docs. See below for details.

If you're using SQLite 3, the TEXT type stores data UTF encoded

I don't see SQLite performing any encoding conversion, so that's surely false. It stores whatever bytes it received, whether it's encoded using UTF-8 or not.

The difference will surface when someone tries to perform text operations (e.g. sorting) on the field.

BLOB is definitely more appropriate here, seeing as the data is binary, but it's my understanding that the type of a column doesn't matter in SQLite if all you do with the column is store and fetch.

If it does do encoding conversions, you need to stay away from TEXT for Storable data. You'll potentially waste space and time.

I would still pick TEXT, because it describes the data the best.

Storable returns binary data. BLOB should be picked because it describes the data best.

The way that I read the Storable doc, it seemed to indicate that it stored data in UTF8. Looking at it again, it only indicates that strings are stored that way. In light of that fact, BLOB would definitely be the right choice.

It looks like SQLite has started to actually make different data types matter recently. I started using it in version 2, when it said "everything is stored as string anyway". The version 3 docs don't make any such claims, but the main differences do appear to be with non store-fetch operations.

It is SQLite 3.6.1.   As long as the UTF-encoding is compatible with what Storable is doing, and “in all cases,” then everything is just fine as it is right now.   I just wasn’t 100%-sure what storage-representation format Storable is actually using ... if that somehow needed to be “binary” in order to be correctly decoded for any-and-all data.

“The data” is just a perfectly-boring hash of strings and numbers (that does not contain references).   As it happens, the text is UTF8.

P.S. I just “stumbled upon” the MLDBM module ...

Each value stored in an SQLite database (or manipulated by the database engine) has one of the following storage classes:

NULL. The value is a NULL value.

INTEGER. The value is a signed integer, stored in 1, 2, 3, 4, 6, or 8 bytes depending on the magnitude of the value.

REAL. The value is a floating point value, stored as an 8-byte IEEE floating point number.

TEXT. The value is a text string, stored using the database encoding (UTF-8, UTF-16BE or UTF-16LE).

BLOB. The value is a blob of data, stored exactly as it was input.

That implies that SQLite performs some manipulation (encoding and/or decoding) upon fields designated as TEXT.

Storable stores integers in binary, which means that some value, or some subset of the bytes making up that value might look like some unicode entity that is illegal or has some special meaning (like Byte Order Mark: 0xFFFE or 0xFEFF), that would cause the round trip into and out of the DB to change the content some how.

I'd be very much inclined to using BLOB for Storable data.

---

Examine what is said, not who speaks -- Silence betokens consent -- Love the truth but pardon error.

"Science is about questioning the status quo. Questioning authority".

In the absence of evidence, opinion is indistinguishable from prejudice.

RIP an inspiration; A true Folk's Guy

Okay, it sounds like the developing consensus here, based in particular on what BrowserUK has observed, is that BLOB would be the most appropriate data type.

I, too, have read that “the storage format is based on the value itself,” and that notion has always given me pause...

Well, in any case, I surmise that the fundamental data-representation used by Storable is “binary data,” not “a string.”   (This in direct contrast to other choices like YAML and JSON, which are designed with the HTTP protocol in mind.)   Consequently, BLOB would presumably give SQLite the most-direct advice that “what you should expect to find in this column is... ‘arbitrary binary data.’”   Hence, BLOB, and not TEXT, becomes the most appropriate choice.

“Going once ... going twice ...”

JSON::XS is as fast as Storable and is also human readable/editable. This can help with debugging; faster, easier, less error prone than writing new code to do the introspection/tweaking. Unless you're doing something special that needs more than JSON provides, I'd recommend it over Storable.