Re: [sqlite] R: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2
Zibetti Paolo <[EMAIL PROTECTED]> wrote: > > Insert into foo values(5.34); > Insert into foo values(3.0); > > Table foo will contain two rows that both contain a real-type number, so, to > read the values back from the DB, I can always use sqlite3_column_double(). > With your proposed change it appears to me that for each row I will have to > first test for the type of the field and then decide whether to use > sqlite3_column_double() or sqlite3_column_int(). > No. SQLite has always allowed values to be extracted in any datatype you want. You can use sqlite3_column_double() to retrieve an integer value and it will convert the integer to a double for you automatically. Likewise, you can do sqlite3_column_int64() on a double and it will automatically do the conversion. Or you can do sqlite3_column_text() and it will convert the value to a string and return the string. This has always been the case and it will not change. -- D. Richard Hipp <[EMAIL PROTECTED]>
Re: [sqlite] R: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2
Given what I've read so far, you shouldn't have to change any of your code. Conceptually speaking, all numbers would be stored as reals internally, though actually some would be stored as integers if possible for efficiency. When you invoke a SQLite accessor function such as double() or int() then the value you asked for will be coerced into the requested data type, and then returned that way. This is how it would have to work, considering that external C code actually considers those types to be different machine native formats. No C code changes should be necessary. Only some SQL code may need changing. -- Darren Duncan At 10:10 AM +0100 11/2/05, Zibetti Paolo wrote: Most of the discussion so far was about proposed change number 2, on the contrary I'm concerned about proposed change number 1. Does this mean that a number that can be stored as an integer will be stored as an integer and, thus, I will need to read it back as an integer ? Here is what I mean: with SQLIte 3.2.x, if I run these two statements Insert into foo values(5.34); Insert into foo values(3.0); Table foo will contain two rows that both contain a real-type number, so, to read the values back from the DB, I can always use sqlite3_column_double(). With your proposed change it appears to me that for each row I will have to first test for the type of the field and then decide whether to use sqlite3_column_double() or sqlite3_column_int(). Is this correct ? If so, changes will be required to the existing code to port it to Sqlite 3.3.x. Bye -Messaggio originale- Da: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Inviato:martedì 1 novembre 2005 15.00 A: sqlite-users@sqlite.org Oggetto:[sqlite] Proposed 3.3.0 changes. Was: 5/2==2 (1) Floating point values are *always* converted into integers if it is possible to do so without loss of information.
[sqlite] R: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2
Most of the discussion so far was about proposed change number 2, on the contrary I'm concerned about proposed change number 1. Does this mean that a number that can be stored as an integer will be stored as an integer and, thus, I will need to read it back as an integer ? Here is what I mean: with SQLIte 3.2.x, if I run these two statements Insert into foo values(5.34); Insert into foo values(3.0); Table foo will contain two rows that both contain a real-type number, so, to read the values back from the DB, I can always use sqlite3_column_double(). With your proposed change it appears to me that for each row I will have to first test for the type of the field and then decide whether to use sqlite3_column_double() or sqlite3_column_int(). Is this correct ? If so, changes will be required to the existing code to port it to Sqlite 3.3.x. Bye -Messaggio originale- Da: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Inviato:martedì 1 novembre 2005 15.00 A: sqlite-users@sqlite.org Oggetto:[sqlite] Proposed 3.3.0 changes. Was: 5/2==2 I am proposing to make the changes outlined below in SQLite version 3.3.0 and I am wondering if these changes will cause any severe hardship. Two changes working together: (1) Floating point values are *always* converted into integers if it is possible to do so without loss of information. (2) Division of two integers returns a floating point value if necessary to preserve the fractional part of the result. The effect of change (1) is to combine the integer affinity and the numeric affinity column types into a single type. The new type is called numeric affinity, but it works like integer affinity. Change (2) resolves Ralf Junker's division paradox. The only code that I can think of that this change might break is cases where the user is depending on the division of two integers returning an integer result. Such code will need to be modified to use the "round()" function to obtain the same result. I am thinking that such code should be very uncommon and that this change will have minimal impact. Nevertheless, the impact is non-zero so I will increment the minor version number as part of this change. If you can think of any other adverse impact that this change might have, please let me know. -- D. Richard Hipp <[EMAIL PROTECTED]>