Essentially, I suggest making SQLite a little bit less typeless.
You already have multiple underlying data type representations as I recall, such as numbers, character strings, and binary strings. Currently, this type is bound to each individual value stored in a table column.
What I suggest instead is have typed columns where all values stored in that column are represented in the same way; eg numbers or strings. You would have as many possible column types as you have underlying representations. If you do this, then you no longer have any problem in figuring out how to sort. All values in a column declared as a character string will sort as their character representations. Likewise, all values in a number column will sort as numbers. You will not have to cast between a string and a number when sorting.
Now, even with "typed" columns as I mention, they are still largely typeless compared to other databases. Fields are not fixed width, do not have maximum lengths, are stored internally as character strings, have a distinct null value, and so on.
When a literal string is compared to a column, or assigned to it, then the literal will be implicitly cast to the same data type as the column.
Such a solution should be more or less backwards compatible, quite easy and/or simple to implement, and simplify a number of things. It also provides a bit more strictness for those of us who want it. (For example, if I declare a numerical column, I would be happy for all values in that column to be treated like numbers regardless of the syntax used to insert the values.)
The main drawback I see is that this change may break some applications which assume numbers-treated-as-numbers and characters-treated-as-characters are stored in the same column at the same time. But then again, I can't think of any real world example where this would be the case.
So what do you think?
-- Darren Duncan
--------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
