Darren Duncan wrote:
> Roger Binns wrote:
>
>>> 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.
>>>
>> It has been stated in several places that the increasing items in the parser
>> will lead to various fields needing to go to the next size up which would
>> increase memory consumption. There is also the whole backwards
>> compatibility angle - what would happen if the database was loaded into an
>> older version of SQLite which then ignored this whole UNIVERSAL thing
>> allowing "wrongly" typed data to be inserted?
>>
>
> An added type name like UNIVERSAL would be completely backwards compatible
> because, as far as I recall, if SQLite currently sees a type name it doesn't
> recognize, then the column has no affinity and will accept any value, so same
> behavior. And so then, for older systems using that keyword would be
> effectively a documentation convention.
>
This is not correct. See http://www.sqlite.org/datatype3.html, section 2.1:
"""
The type affinity of a column is determined by the declared type of the
column, according to the following rules:
1.
If the datatype contains the string "INT" then it is assigned
INTEGER affinity.
2.
If the datatype of the column contains any of the strings "CHAR",
"CLOB", or "TEXT" then that column has TEXT affinity. Notice that
the type VARCHAR contains the string "CHAR" and is thus assigned
TEXT affinity.
3.
If the datatype for a column contains the string "BLOB" or if no
datatype is specified then the column has affinity NONE.
4.
If the datatype for a column contains any of the strings "REAL",
"FLOA", or "DOUB" then the column has REAL affinity
5.
Otherwise, the affinity is NUMERIC.
If a table is created using a "CREATE TABLE <table> AS SELECT..."
statement, then all columns have no datatype specified and they are
given no affinity.
"""
#5 means that your UNIVERSAL type is not backwards-compatible, and #3
means that it is not necessary.
Your proposal would break any databases that store strings or blobs in
columns with unrecognized types. In particular, it would break all the
tables I have that store timestamps as text in columns declared as
TIMESTAMP. We could deal with this by adding a new affinity rule:
"""
If the datatype for a column contains either of the strings "DATE" or
"TIME", then the column has DATETIME affinity.
A column with DATETIME affinity behaves in the same way as a column with
NUMERIC affinity, except that in strict affinity mode TEXT and BLOB
values are allowed only if they are valid time strings (as determined by
the datetime() or julianday() function).
"""
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
