Ah the sticky wicket that is "Type less" :-) We now have issues evolving as a direct result of that feature in our cute little database. We now seem to have by backing into it: Really Restricted Integer, Real, DateTime (sort of), and Text. BLOB and CLOB away at your own risk!
Is it time to officially declare/fully support some Types and clear the air? Fred > -----Original Message----- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] > Sent: Tuesday, November 01, 2005 9:43 AM > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2 > > > "Preston Zaugg" <[EMAIL PROTECTED]> wrote: > > As was discussed in the original > > post this would be NON-STANDARD behavior. > > The SQL-99 spec says that integer > > math remains an integer. > > > > The change I propose (and have now checked into CVS, btw, > though I might still back it out) does not violate this > specification. > > Think of it this way: SQLite supports only a single > numeric type which is REAL. We call that type "numeric". > But the type represents what we normally think of as real > numbers. > > We permit integer values to be read from and written to > the database as a convenience to the user. And internally, > some values are sometimes kept as machine integers for > computational and storage efficiency. But that is only > an optimization. At the end of the day, there is only a > single numeric data type and that type is real. > > An INTEGER PRIMARY KEY column seems like an exception to > this rule. But perhaps not. Think of an INTEGER PRIMARY > KEY column as holding a numeric value with restrictions. > It is as if we added to every INTEGER PRIMARY KEY named > "x" the following check constraint: > > CHECK( x >= -9223372036854775808 > AND x <= 923372036854775807 > AND x == round(x) ) > > So the values going in and out of an integer primary key > are still real values. It just happens that their range > is restricted somewhat and they do not have a fractional > part. > > If you look at things from this point of view, SQLite > does not support integer values. And so we never have > to worry about integer division. > > For complete consistency, I suppose we might want to > modify the built-in typeof() function to always return > "numeric" instead of "integer". I wonder.... > > -- > D. Richard Hipp <[EMAIL PROTECTED]> >
