SQLite Forum

General question concerning database stucture and number of databases to use.
There is no difference between a BLOB and TEXT provided that the interfaces used to access the *text* field are the same as the database encoding.  That is, if the default encoding is UTF8 and the APIs you use to access the field are the _text variant (which expect to input 8bit characters and output 8 bit characters) *OR* the database encoding is UTF16 and the APIs used to access the data is only ever the _text16 variants, then there will be no "diddling around" with the data you have provided when it goes "into" or "out of" the database record.

The *only* difference between a BLOB and TEXT is that TEXT is assumed to contain TEXT, meaning that is can be converted between the various API and internal encoding formats, UTF-8 and UTF-16BE and UTF=16LE, depending on which combinations of APIs for dealing with TEXT fields and what the default database encoding is set as, and the particular platform.  Functions for dealing with "TEXT" also assume that a 0 codepoint terminates the text (since this is part of the innate definition of what comprises a text string).

If you declare something as "TEXT" when it is not well-formed TEXT may result in various explosions, meltdowns, and proper behaviours which the user feels are improper because they failed to compy with the requirements that TEXT be properly formed and therefore shot themself in the foot.

There is nothing which prevents you from storing TEXT in a BLOB field.  How your application "interprets" the bytes is entirely a matter for your application.

The attribute TEXT is only important if you want SQLite3 to treat the data as TEXT (for the purposes of diddling about with it and interpreting its contents) in which case it must be "properly formed" text.