On 2018/06/30 9:04 AM, Thomas Kurz wrote:
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.
I'm sorry, upon re-reading I see the real point I tried to make got lost
in that I concentrated too much on the physical examples. The real point
is that, in c for example, SQLite is not understanding BIT as a type
and it is not responsible for understanding all types used in all
database engines. Imagine that you couldn't use BIT in MySQL, it's an
MSSQL construct - why do you require SQLite to interpret it? Moreover,
what if a brand new type arise today? Let's call it CAT_MOUSE_DOG as an
example, and let's say Oracle, MSSQL and Postgres ALL adopt this new type.
Do you still expect SQLite to error out when you declare a column with
that type?
SQLite doesn't "know" which types/conventions will arise in future,
hence, since its birth (before many of the modern types) it has allowed
UNKNOWN types in the declaration, and then made an effort to guess the
best affinity upon it, which works well mostly, but not always. More
pertinently, once it does actually lock down a "meaning" upon a type or
affinity, it can never go back. It's worth being under-zealous.
As to your statement about the problem with STRING - Yes, let's agree
that SQLite dropped the ball on that one many many moons ago (so much so
they have had enough backlash [just like your case] that they have
amended the documentation to warn about that very specific case. By now
there are billions of schemas in the world, many of which might have
STRING declarations and have adopted a checking mechanism in their own
code to deal with it (much like you might do now), so changing it may
harm those implementations.
And yes, you might think (like me) that this is silly and they should
just fix it, warning people about the new way for a good few versions,
and then just roll it out. But, that would mean there will exist schemas
in the World that will be interpreted differently by different versions,
and the selfish comfort to me comes when I think: "If I make a DB today
that works... it will work for all those years/upgrades to come, and I
can trust that it will because if the SQLite Devs won't fix STRING, then
they are unlikely to change anything that will harm MY systems."
Cheers!
Ryan
PS: Anyone here that takes web servers and the like through upgrades to
different Apache, or PHP versions, or different MySQL/MariaDB versions
will know the effort of re-engineering your work from years ago to fit
the new upgrade. SQLite is the only "fire-and-forget" weapon out there,
I feel like that feature is worth a few TYPE pains - but that's just my
feeling and it might well be antiquated.
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
