This email is a feature proposal for SQLite; I can rewrite it if desired.
Ted Rolle wrote (in the "Late data typing ..." thread):
> Doesn't dynamic data typing lead to bad data?
> And proliferation of home-grown editing routines?
> It seems that a strict data typing at column definition time would be
> MUCH better. For instance, date-editing routines...
I should point out, for people reading this thread, that it is very possible to
have both the amount of type flexibility that SQLite provides and have strict
typing, at the same time.
All you need to do, to retain SQLite's flexibility but also get strong typing,
is support data types that are defined as unions of other data types, and that
there should be a system-defined type that is a union of all data types; say
call it "UNIVERSAL" for example.
Then you could say something like:
CREATE TABLE mytbl (
myint INTEGER,
mytext TEXT,
myanything UNIVERSAL,
...
)
When this is supported, the DBMS can be strongly typed, and when users want to
be able to declare a field that accepts any value, they declare it UNIVERSAL,
and they use a more specific type otherwise.
Now, because SQLite already supports keeping any value in any field, it already
has the foundation necessary to do what I indicated, because then INTEGER/etc
is
simply UNIVERSAL plus a constraint, expressed in a terser form than CHECK.
Support for what I indicated could conceivably just be added like how support
for foreign keys was just added, and it could be turned on/off with a pragma
likewise to aid backwards compatibility, for people who wrote the column types
in their SQL but expected enforcement to be lax.
In fact this support might even be easier as it may only require enhancements
to
the SQL parser, which would generate VM opcodes like for a CHECK constraint,
unless further work is done to optimize for the presented cases, or to enhance
semantics.
I also want to emphasize that I strongly support the approach SQLite has taken
on this issue historically, in contrast to many other DBMSs, by supporting any
value in any field. Tacking on the ability to support any value in any field
would require a more substantial change to other DBMSs that have it deeply
ingrained that each field is just an INTEGER or TEXT or whatever.
I also want to clarify that I strongly support each VALUE being identified with
a type, with this being the most important thing, which SQLite does as far as
it
goes with its distinct Null|Int|Num|Text|Blob values. And so a type definition
for the purposes of a column definition is just a set of what values it allows.
-- Darren Duncan
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
