In SQLite, every value you store is stored alongside its type. This is unlike
other databases where the column determines the type and every value stored
against it share it. In SQLite you could have a table in which all value types
as stored contradict all column types as declared, if you so craft it (except
integer primary key). As for a column declared BLOB, it results in an affinity
of NONE meaning "perform no implicit conversions" (as long as lossless and
reversible), it doesn't mean "affinity binary" because there is no such thing
as something more generic than binary that should implicitly be cast to binary
upon storing it. So a blob-declared column would not try and implicitly
convert, but it will still store exactly what you put in, including its type,
inferred from the way in which you did the assignment, e.g., for set c = '1' it
will store {text-1} for integer it will store {integer-of-1-<nothing>} (a
special case), for x'01' it will store {blob-01}, and so on. This lower case
'blob' is the most generic case (your 'bytes') but to obtain that one must
cast() to a blob which means "strip from what I'm giving you any indication
that it may be a text, or an integer, or a real, as it is really neither".
Think of blob not as a type ('binary') but the absence of types. You can
further apply a check constraint to the column to ensure that no value stored
will be stored along with a tag meaning "I'm of a certain kind". This
check(typeof(c)='blob') will ensure the data you put in will be 'naked' so to
speak, and when the data you put in is naked, SQLite can only store it as
bytes, and by default return it as bytes. So you can see how it differs, blob
as column type meaning 'suggest no type' or 'add no type' and blob as value
type meaning 'a value with no type tag attached' or 'convey no type'. They're
complementary. Think of the two together as "none given - none taken" (or 'none
added'). Naturally the constraint will not let you set c = '1' because '1'
implies text by way of the quotes, same for Int (and how would it know if you
meant a one-byte int, or two, or four?), and I think that's what you want in
your application, but it will let you assign something in the x'' notation, or
a cast() expression. So a check constraint makes the column more 'demanding' so
to speak, type-wise, stricter, and closer to what you observe in other
databases. Just remember to tweak the check constraint to allow nulls for
nullable columns. After which, we'd expect that if no type marker was carried
in, none will come out upon querying it, unless you have some other layer
wrapped around it such as some ODBC driver assuming that it's its duty to
further cast a column to a target type based on the column's declared type. But
this is because some wrapper drivers assume the user will try and use SQLite as
he uses most other databases, or because the driver cannot support exposing a
series of column values of varying types. A native SQLite driver (one built in
accordance to how SQLite really works) shouldn't take this freedom.
You wrote:
>
> My assumption was that GetBytes() could be used to read
> the bytes of any column regardless of type. What difference
> should column type or affinity make if I'm asking for bytes?
> If I'm using GetBytes() on a column with REAL affinity,
> it should put 8 bytes in the read buffer, but it doesn't.
> If I send text to a BLOB column it's typeof() changes to
> TEXT which confuses GetBytes(). I think this is pretty silly
> because bytes are bytes, right? Why bother verifying type
> when the method name already tells you what you are getting
> and bytes can be obtained from any data type?
> (Rhetorical questions, but feel free to respond anyway.)
>
