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 sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users