On Sat, Nov 06, 2010 at 08:35:10PM -0300, Tito Ciuro scratched on the wall:
> Hello,
>
> I have a question about manifest typing/data affinity. Assume I have
> created this table:
>
> CREATE TABLE foo (ROWID INTEGER PRIMARY KEY, key TEXT, attr TEXT, value NONE);
>
> I was reading the Using SQLite book and came across page #38 (#60 on
> the PDF version) where it states:
I hope the book is proving useful.
> "None: A column with a none affinity has no preference over storage
> class. Each value is stored as the type provided, with no attempt
> to convert anything."
>
> So it seems that using NONE is a hint that different data types are
> going to be stored in that column, correct?
I suppose you could look at it that way, but it isn't meant to be
explicit.
With the exception of an INTEGER PRIMARY KEY, any column can hold
row-values of any type. The affinity will influence stored values
to specific types, but it does not prevent other types from being
stored if the conversion cannot be made.
The NONE affinity simply indicates to the database that you always
store values in their provided type, and that no conversions should
be attempted, even if a conversion could be done in a loss-less way.
> My main question has to do with binding values to precompiled statements.
> For the value column, should I:
>
> a) use sqlite3_bind_value()?
sqlite3_bind_value() is for binding "sqlite3_value" data structures.
If you have one of those (from an sqlite3_column_value(), for
example), and it is in the type representation you want (text,
int, etc), then go ahead and use it.
If you're binding a more traditional value, such as a string or
integer, you should use one of the sqlite3_bind_text(),
sqlite3_bind_int(), or similar functions.
The bind function you choose will set the "type" of the value.
If the column has a NONE affinity, no conversion will be attempted
and the value, in the representation you provided, will be stored
directly.
> b) store it as a string using sqlite3_bind_text()? Will
> sqlite3_bind_text() allow SQLite to choose the proper data affinity
> even though I'm binding it as text?
An affinity is a property of a table column. The affinity of a
column is defined by the "SQL type" passed to CREATE TABLE. Once the
table has been created, the affinity of each column is set. So the
only time SQLite "chooses" an affinity is when it parses the CREATE
TABLE statement.
So, for example, if you have an INSERT statement that binds a text
value to a parameter used to set a column with a NONE affinity, the
value in the new row will *always* be stored as a text value,
regardless of the string value. If you were to bind an integer, the
value would always be stored as an integer, and so on.
This would work differently if the column had a NUMERIC affinity, for
example. In that case, binding the string "abc" would result in
storage of a text value, while binding the string "132" would result
in an integer and "85.3" would result in a floating-point. Binding
43 (the integer, not a two-character string, using sqlite3_bind_int())
would result in an integer as well.
As you might know, you can use different sqlite3_column_xxx() functions
to extract values in a specific representation (type), even if that
returned representation does not match the type of the stored value.
For example, if you know you're going to print out a value, you can
use sqlite3_column_text() to get a text representation of the value,
even if that value is stored in the database as an integer. The
conversions used for this are given in table 7.1 of Using SQLite.
On the input side, you can use different sqlite3_bind_xxx() function
to provide values in a representation (type) that might not match the
type used to actually store the value. The affinity defines your
"preferred" storage type, and is used as a hint to do input
conversions. So if you're taking use input for a number, you can
take the text value passed in by your applications GUI and pass that
directly to sqlite3_bind_text(), even if the string represents a
number, and you want to store it as a number (and have told the
database this by using an INTEGER, REAL, or NUMERIC affinity).
The NONE affinity is simply a way of saying that you will always
provide values in the representation that should be used to
store them. This means that the specific sqlite3_bind_xxx() function
you choose more or less sets the type of the value.
-j
--
Jay A. Kreibich < J A Y @ K R E I B I.C H >
"Intelligence is like underwear: it is important that you have it,
but showing it to the wrong people has the tendency to make them
feel uncomfortable." -- Angela Johnson
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
