> I don't disagree, but this means we lose sight of the important point
>that, if you distill the problem to just "INTEGER", then sure, it looks
> silly, and sure, you can fix it with internal auto-CHECKing, but in
> SQLite the type affinity INTEGER stands father to LONGINT, MEDIUMINT,
Ok, I understand your argumentation now. But I don't think distinguishing
different integer types or strings with different length declarations would be
mandatory. IIRC, they were introduced to save memory in times when database
files were actually just a sequence of fixed-length records/structs. SQLite on
the other hand always uses the least memory-consuming way for storing data
anyway.
But I must admit that a disctinction between alternatives of the same "base
type" was something I didn't have in mind, so consider me convinced ;-)
> COLUMN xy VARCHAR(50)
> COLUMN xy MEDIUMTEXT
> COLUMN xy BIT
> COLUMN xy DECIMAL(5,2)
> Should those raise errors? Because to SQLite those are the exact same
> gibberish as:
I would appreciate if SQLite raised an error each time the declaration
mismatches the interpretation. In your example, all declarations are well-known
SQL. And, if I understand correctly, SQLite treats each of those as NUMERIC. So:
a) Yes, error, as I indent to store strings, but SQLite uses numbers.
b) Yes, error, same case.
c) Preferably yes, but not mandatory, as BIT is compatible to NUMERIC
d) No, as DECIMAL ist compatible to NUMERIC.
Let me explain my problem, something which happens to me quite often as many
programming languages name the thing "string" that DBMSs want to have named
"text" (why the hell??):
CREATE TABLE a (col1 STRING);
INSERT INTO a (col1) VALUES ("3.0");
SELECT * from a;
---> 3 // this should never happen!!
Instead of raising an error on the column definition (which would be most
elegant imho), it would be perfectly ok if SQLite treated the string ("text")
that I provide in the insert-statement (double quotes) actually as a string and
did not try to convert it. Or, more clearly spoken, if it recognized that the
conversion is not reversible to the original string.
Currently, there is no warning whatsoever for me to lose data after having used
an invalid column declaration. Meanwhile I'm more aware of that, but the first
time I had spent hours trying to figure out what's going wrong until I found
the problem.
INSERT INTO a (col1) VALUES (3.0);
SELECT * from a;
---> 3 // I would accept that as I have provided a number
Maybe, this could be a compromise?
In your previous post, you wrote:
> [...]
> VARCHAR(3)
> Truncate the string to "MAM" in MySQL, without an error at all, just kill
> some data and move on!
My example is exactly the same. SQLite continues without an error losing me
data. There is no way getting it back. Even this don't work:
SELECT CAST(col1 AS TEXT) FROM a;
---> 3 // <> "3.0" !!
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
