On 2014-11-26, 7:53 AM, RSmith wrote:
> The fact that inside of SQLite an Integer can be stored in different
> ways is simply a code/data/space optimisation for SQLite, it is
> transparent to the user and transparent to the SQL - it is in no way
> intended as a data-feature or extension of the SQL Language. If you try
> to build on top of that (or maybe not on top of it, but in the same way)
> more arbitrary sub-types, yes of course it is possible, but it may leave
> you in a World of hurt in terms of using the DB as a relational database
> system for other normal activities.
This is a documented feature of SQLite and shouldn't be considered an
optimisation or a transparent feature. I've depended on this feature to
implement something very similar to OP where two columns together
described a value: 1 column represented the type, the other a BLOB
representing the value.
SQLite's ultimately untyped storage allowed me to index these types as
best as I could expect it to be done in a superb, efficient manner.
I'd include this feature of SQLite as a killer feature. For sure, if you
needed DB portability, you'd have to have a single type in a column
which could mean one column for each type you'd want to have. Yuck.
Here is an example of how I consumed this variant in C++:
template<typename T>
static
T
getAttribute( Wt::Dbo::Session & session,
dbo::id_type repositoryId,
String const & attrName )
{
return boost::get<T>(
findAttribute(session,repositoryId,attrName)
->value
);
}
The C++ magic deserialized the two column value into value. It has
turned out to be immensely useful.
My point again is: it's a documented feature of SQLite, not an optimisation.
Sohail
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
