On 04/06/2016 02:13 AM, Hick Gunter wrote: > You are hopefully aware of the fact that SQLite associates type with the > actual values and not the containers(columns) used to hold these values? This > means that a data object of any type may be held/returned in a column, > irrespective of the declared type (which, for expressions, is NULL). > > What would your date routine do with the string 12.17.9.17.15?
it would raise an exception on the invalid date format, not any different from the string->date converters that take effect when you enter a date on a web form. Even though SQLite allows any kind of data to be stored in any row regardless of type affinity, in practice, applications typically remain faithful to the type of data they plan to store within columns. That is, even though SQLite has a dynamic per-row typing model, most of the world very much wants it to behave as a drop-in for MySQL or Postgresql and their applications assume schemas have fixed datatypes just like all other relational databases. > > -----Urspr?ngliche Nachricht----- > Von: sqlite-users-bounces at mailinglists.sqlite.org > [mailto:sqlite-users-bounces at mailinglists.sqlite.org] Im Auftrag von Mike > Bayer > Gesendet: Dienstag, 05. April 2016 21:46 > An: sqlite-users at mailinglists.sqlite.org > Betreff: Re: [sqlite] regression in 3.12.0 vs. 3.11.0, column type > information in PRAGMA missing > > > > On 04/05/2016 01:19 PM, Richard Hipp wrote: >> On 4/4/16, Mike Bayer <classic at zzzcomputing.com> wrote: >>> The "type" column in PRAGMA table_info() is now a blank string when >>> the target object is a view in 3.12.0. In 3.11.0 and prior versions, >>> the typing information is returned, >>> >> >> This could easily be considered a bug fix rather than a regression. >> Please explain why you think it is important to know the "type" of a >> column in a view? > > As others have noted, in the application space we often use these names as > suggestions for how data from such a column is to be handled once > transmitted outside of the SQLite layer. The most prominent example is > date values, where we apply converters on both sides of the data to convert > between language-specific date objects and a string representation on the > SQLite side. > > For example, here is the Python standard library SQLite database adapter: > > https://docs.python.org/2/library/sqlite3.html#converting-sqlite-values-to-custom-python-types > > In this example, we see the use of the constant sqlite3.PARSE_DECLTYPES, > which indicates "parse the name of the declared type delivered by SQLite > within a result set, in order to apply a converter". This specific > implementation is parsing the type affinity as delivered in the result set, > so is not impacted by this change. However, other database abstraction > systems rely upon the use of so-called "table metadata" > gathered up front about tables and views in order to know about the datatypes > that are expected from particular column names; on SQLite this > relies upon "pragma table_info()" to collect that information. If > it's no longer present, such systems would require the user to explicitly > state datatypes in the case of views on the application side, or to be > modified to rely upon typing information when a result set is received rather > than based on the schema of the constructs themselves. > But like in so many other cases, database-agnostic systems are designed > around the way all other relational databases behave, which in this area is > that of the "fixed type per-column" model where there's never been an issue > knowing the types that will be received from a table or view. That SQLite > also delivers these fields via "pragma table_info()" just made it that much > more possible for abstraction layers to emulate similar behavior in SQLite. > > > >> >> There are further inconsistencies here. Example: >> >> CREATE TABLE t1(x INTEGER); >> CREATE VIEW v1 AS SELECT x FROM t1; >> CREATE VIEW v2(x) AS SELECT x FROM t1; >> PRAGMA table_info('v1'); >> PRAGMA table_info('v2'); >> >> As of version 3.12.0, the two pragma's give the same answer. but in >> version 3.11.0, they were different. Which of the two answers >> returned by 3.11.0 is correct? > > Looking from the perspective of the consuming application, ultimately the > INTEGER affinity value is what's delivered so in an ideal world people would > expect the answer to be INTEGER. > > However I would reiterate the point of my original email, which is that if > this change in behavior is intentional, it should be documented in the change > notes at http://sqlite.org/releaselog/3_12_0.html ; I've looked through every > line item and see none that suggest a change in how PRAGMA behaves with > regards to views. I also notice that there's now a category of change called > "Potentially Disruptive Change"; I hope to illustrate here that whether or > not the 3.11 or 3.12 behavior is chosen, this change is definitely > "potentially disruptive" and should be noted > as such. Or even that this area of behavior could be explicitly > described at http://sqlite.org/datatype3.html ("type affinities do not > transfer to views at the view definition level"). > > >> >> Or, consider this situation: >> >> CREATE TABLE t2(w SHORT INT, x DECIMAL, y BIGINT, z REAL); >> CREATE VIEW v3 AS SELECT w+x+y+z FROM t2; >> >> What should "PRAGMA table_info('v3')" report as the column type? > > In the case of views propagating type affinities at the definition level, > ideally we'd take the summation of the type affinities themselves assuming > each column is populated with a type-compliant data value and return that, > which in this case would be NUMERIC, since all math operators coerce their > values to NUMERIC first. > > >> >> It seems to me that the most consistent answer is that the "type" of >> columns in a VIEW should always be an empty string. > > So to give some background where I'm coming from on this, I don't actually > need SQLite to behave either way, I'm the creator and maintainer of one of > many database abstraction layers that provides a SQLite translation layer and > it's *my* users who will come to me with this behavioral change (and in fact > they already have which is how I > know about this). I only need the change and/or the "correct" behavior > to be defined (that is, is part of SQLite's test suite and won't keep > changing across releases) and documented (so I can point my users to SQLite's > own instructions about this limitation). I will remove the tests from my > suite that have for many years expected SQLite to behave this way, and > possibly put a note in my own documentation pointing to SQLite's explanation, > and the problem is solved. That part of the userbase who relied upon this > behavior would be negatively impacted, however, as they'd now have to solve > their problem in a different way and would experience sudden behavioral > changes on an upgrade from 3.11 to 3.12. > > > > > >> > _______________________________________________ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > ___________________________________________ > Gunter Hick > Software Engineer > Scientific Games International GmbH > FN 157284 a, HG Wien > Klitschgasse 2-4, A-1130 Vienna, Austria > Tel: +43 1 80100 0 > E-Mail: hick at scigames.at > > This communication (including any attachments) is intended for the use of the > intended recipient(s) only and may contain information that is confidential, > privileged or legally protected. Any unauthorized use or dissemination of > this communication is strictly prohibited. If you have received this > communication in error, please immediately notify the sender by return e-mail > message and delete all copies of the original communication. Thank you for > your cooperation. > > > _______________________________________________ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >
