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' )
);
-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
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
