Jay A. Kreibich wrote: > On Sun, Sep 23, 2012 at 09:25:06PM +0400, Yuriy Kaminskiy scratched on the > wall: >> Jim Dodgen wrote: > >>> I program mostly on Perl on Linux and it is a beautiful fit. Example >>> is I can have a date field with a POSIX time value (or offset) in it >>> or another date related value like "unknown" >> Very bad example. Standard SQL NULL is much better fit for "unknown". >> Besides, perl at least have "use strict;" and "use warnings;", sqlite does >> not. > > Yet SQLite's types are often more clearly defined than Perl's. Every > SQLite value has a specific, known type that will tell you exactly > how the bits are stored. The only difference with SQLite is that > columns are allowed to have mixed types. Don't confuse this with a > loosely typed language, however... again: every SQLite value has a > specific and known type. > > > From a formal mathematical sense, a relational NULL is considered a > "value-less type." That is, it is treated as a specific data type > that's value domain is the null-set. > > So, if you want to get real formal, all relational databases allow > multiple types (at least two) to be assigned to a row attribute. > I know that sounds contrived, but when you start to look at NULL > handling in SQL in this way, it suddenly makes a lot more sense. > And it means that all SQL databases already deal with disjoint types > within a column. > > If formal theory isn't your way thing, I'd point out that "traditional" > database do all kinds of automatic type conversions. When you input > a date in MySQL, you do so as a string. When you get a date or > duration value back, it is usually as a string. If you compare a > date column to a literal string (that, one assumes, represents a > date) the database will do its best to covert that string to > something that makes sense before doing the comparison. Similar > things can be said of different numeric types... "WHERE floatCol < 3" > will do automatic conversions and get on with it. > > The typical database has all kinds of automatic rules about dealing > with different types involved in the same operation. SQLite has all > these rules as well... and they're all clearly defined, and they all > work pretty much the same way. The fact that a column is only loosely > typed really doesn't come into play in a significant way, except that > the conversion rules for a comparison may come up in a JOIN, while > other databases would typically only see a converted comparison in > a WHERE. > > The end result is not mass chaos but, rather, rarely a surprise. > SQLite does a lot of type conversion-- just like every other database > out there-- to deal with disjoint types. Those conversion rules are > well documented and make sense. > > > > I'm a bit of purest, and when I first started using SQLite eight > years ago, I was also a bit off-put by what I saw as "fast and loose" > typing. Over many years of using SQLite for all kinds of things, I > can say that this has never been an issue. It has never surprised > me, it has never caused problems-- and it occasionally has been darn > handy. > > > > And finally, for anyone that really wants strong typing, that's easy > enough to do. Just add a check constraint to your column defs: > > CREATE TABLE t ( > i integer CHECK ( typeof( i ) == 'integer' ), > t text CHECK ( typeof( t ) == 'text' ), > r float CHECK ( typeof( r ) == 'real' ), > b blob CHECK ( typeof( b ) == 'blob' ) > );
Won't help with catching error in DELETE ... WHERE day >= 2012-03-15 -- oops, just deleted records from 1994 year Won't help with catching error when (SELECT ...) >= (SELECT ...) suddenly uses string comparison instead of numerical (9 >= 19 vs '9' >= '19'). Yes, sqlite rules documented, yes, you can always explain why it behaved the way it did, still nasty surprises happens, especially with newbies (and sometimes not only with newbies). _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
