I'd like to second Dennis' earlier remarks, and add some of my own.
One of my greatest hopes when I found SQLite (it's "value proposition") was in having a great little database that would operate in such a familiar way. By "familiar", I mean that it should execute the SQL language, and produce (for the same DDL, and the same DML) the same result as other SQL implementations. In short, I expected it to comply with standards. Using standards is a two-way street: * I should be able to bring existing structures, and existing code from SQL Server, or Informix, or PostgreSQL, and have it not only run, but produce the same result. * I also want to use SQLite to create and test code that I may later take to one of these other platforms, and I'd like it to run the same there as it has been running in SQLite. The better it allows me to do both of the above, the more valuable it is to me. My friends and I joke that one of the greatest things about standards is that everyone gets to choose their own. I'd call SQLite's manifest typing one of "it's own". It is one of SQLite's greatest strengths, as well as it's greatest weakness. Being able to informally type a field is awesome, when I choose to use it. But it is a weakness when it influences a result unexpectedly (when it doesn't allow me to choose MY own standard). Most of my difficulty, and my greatest disappointments in using SQLite to-date has been where I got unexpected results when it did not strictly heed my formal DML instructions. My specific challenges have been with char(n)'s, but I think the learning applies equally to the discussion of real/int/numeric. Paradoxically, its greatest opportunity to grow and become more valuable to me is in allowing me the flexibility to call upon it to behave more strictly standard. By "strictly standard", I mean: * If I specify "INT", in my DML, I'd like it to behave exactly as an INT in other implementations. If it would like to abbreviate the value for compactness of storage, that's great. But I don't want it to store, nor return anything that behaves differently from an int. * Likewise FLOAT/REAL. I really don't care if SQLite stores it as a machine float, as an IEEE float, or as text. But if the field is a float, I don't want it to store, nor ever return anything that behaves differently from a float. * Likewise CHAR(n). It should not store, nor ever return any more than n characters. In situations where informal typing applies well, I'd like to be able to select this behavior explicitly. For example, by defining a column as VARIANT, or NUMERIC. If the "power" of manifest typing is its ability to recognize a value and properly convert it, and store it in a form as compact as it likes -- can that power be leveraged to retrieve a value, no matter how stored, and properly convert it back to behave exactly as the type it is expected to be, rather than as the type it was coerced to for storage? To sum it up: I place more value in how the fields behave than in how they're stored. I'd like more control, not less, in how they behave. Doug -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Tuesday, November 01, 2005 10:55 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2 <snip> With the above clarification, I will restate the question: Suppose SQLite were to merge "integer" and "real" into a single datatype "numeric" that always worked the same way. Does anybody know of a (real) usage example where this would cause an actual hardship on developers? Are there any examples of things that you can do with separate "real" and "integer" types that you cannot do with a unified "numeric" type? -- D. Richard Hipp <[EMAIL PROTECTED]>
