For reference, see http://sqlite.org/version3.html and specifically the section titled "Manifest Typing and BLOB Support":
SQLite version 2.8 had the feature that any type of data could be stored in any table column regardless of the declared type of that column. This feature is retained in version 3.0, though in a slightly modified form. Each table column will store any type of data, though columns have an affinity for the format of data defined by their declared datatype. When data is inserted into a column, that column will make at attempt to convert the data format into the columns declared type. All SQL database engines do this. The difference is that SQLite 3.0 will still store the data even if a format conversion is not possible.
I also reference http://sqlite.org/datatype3.html in the section titled "6. Other Affinity Modes":
The above sections describe the operation of the database engine in 'normal' affinity mode. SQLite version 3 will feature two other affinity modes, as follows:
Strict affinity mode. In this mode if a conversion between storage classes is ever required, the database engine returns an error and the current statement is rolled back.
Something I would like SQLite 3 to support (unless it already does) is behaviour that is part way between "strict affinity mode" and "normal affinity mode". Something like this:
Each table column will store exactly one type of data, which is the type that the column has affinity for, as defined by their declared datatype. That is, each column will store the NULL storage class plus exactly one of [INTEGER, REAL, TEXT, BLOB]. When data is inserted into a column, that column will make an attempt to convert the data format into the column's declared type, if it isn't already in that type. All SQL database engines do this. If a format conversion is not possible, the database engine returns an error and the current statement is rolled back.
In brief, I want an option that is like "normal" except that an error is returned if the data can not be converted to the column's declared type; I want some input flexibility, but I don't want the engine to store values that are not of the correct type.
Now, if "strict" mode already does what I want, then I suggest that the documentation be updated to say so, because this was in no way clear to me.
I believe that the affinity mode for a database should be set at database creation / open() time (like the UTF-8 vs UTF-16 option is) and not be changeable afterwards, for safety. The reason I suggest that a file format change may be required is that the affinity mode chosen for a database needs to be stored in the header block for the database file (read-only).
If the affinity mode is already implemented with the scope or duration that I mention, then please say so as I didn't notice it.
If not, then hopefully my suggestion will be very easy to implement. I expect it would only need maybe a dozen lines of code.
Thank you. -- Darren Duncan
P.S. On to the corruption fix thing.
--------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
