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