Re: [sqlite] How to select Strict Affinity or No Affinity modes?Attention: DRH
I found that it was a fairly simple change to Sqlite to stop it changing formats and causing grief. Out of the box it merges nicely with scripting environments like Javascript, Python and TCL but can be a pain in other places. Fortunately the changes needed where format changes are detrimental are tiny. Fowler, Jeff wrote: > I agree. After many years with SQL Server and Oracle (but new to > SQLite), the concept of storing different datatypes within the same > field is something I've had difficulty grasping. I'm not saying it's a > bad thing, but from a business perspective I can't think of a situation > where we would not want strict affinity. So if it becomes an option > we'll use it throughout our application. > > - Jeff > > > -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On Behalf Of Samuel Neff > Sent: Friday, February 08, 2008 11:52 AM > To: General Discussion of SQLite Database > Subject: Re: [sqlite] How to select Strict Affinity or No Affinity > modes?Attention: DRH > > I would like to have strict affinity mode too. In our schemas we use > check constraints to enforce strict affinity. Unless you're working in > a dynamic typed environment, I can't imagine why you would want to have > inconsistent data within a single database field. Also for consistency > with (every?) other database engine out there, a strict affinity mode > would be good. > Strict affinity will also benefit all wrapper writers who write wrappers > following a framework that assumes strict field typing (which I think is > pretty much all of them since all other db's have strongly typed > fields). > > Thanks, > > Sam > > > On Feb 8, 2008 11:09 AM, Ken <[EMAIL PROTECTED]> wrote: > >> I second the strict affinity mode as an optional feature, for the same > >> reasons as Lee. >> >>A while back I ran into a problem while using the bit and feature >> of sqlite and got unexpected results because sqlite changed the type >> from a 64bit integer into a real. (I think)... In this case it would >> have been simpler to debug, if there had been a type conversion > warning or a failure. >> Regards, >> Ken >> >> > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to select Strict Affinity or No Affinity modes?Attention: DRH
I agree. After many years with SQL Server and Oracle (but new to SQLite), the concept of storing different datatypes within the same field is something I've had difficulty grasping. I'm not saying it's a bad thing, but from a business perspective I can't think of a situation where we would not want strict affinity. So if it becomes an option we'll use it throughout our application. - Jeff -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Samuel Neff Sent: Friday, February 08, 2008 11:52 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] How to select Strict Affinity or No Affinity modes?Attention: DRH I would like to have strict affinity mode too. In our schemas we use check constraints to enforce strict affinity. Unless you're working in a dynamic typed environment, I can't imagine why you would want to have inconsistent data within a single database field. Also for consistency with (every?) other database engine out there, a strict affinity mode would be good. Strict affinity will also benefit all wrapper writers who write wrappers following a framework that assumes strict field typing (which I think is pretty much all of them since all other db's have strongly typed fields). Thanks, Sam On Feb 8, 2008 11:09 AM, Ken <[EMAIL PROTECTED]> wrote: > I second the strict affinity mode as an optional feature, for the same > reasons as Lee. > >A while back I ran into a problem while using the bit and feature > of sqlite and got unexpected results because sqlite changed the type > from a 64bit integer into a real. (I think)... In this case it would > have been simpler to debug, if there had been a type conversion warning or a failure. > > Regards, > Ken > > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to select Strict Affinity or No Affinity modes? Attention: DRH
I would like to have strict affinity mode too. In our schemas we use check constraints to enforce strict affinity. Unless you're working in a dynamic typed environment, I can't imagine why you would want to have inconsistent data within a single database field. Also for consistency with (every?) other database engine out there, a strict affinity mode would be good. Strict affinity will also benefit all wrapper writers who write wrappers following a framework that assumes strict field typing (which I think is pretty much all of them since all other db's have strongly typed fields). Thanks, Sam On Feb 8, 2008 11:09 AM, Ken <[EMAIL PROTECTED]> wrote: > I second the strict affinity mode as an optional feature, for the same > reasons as Lee. > >A while back I ran into a problem while using the bit and feature of > sqlite and got unexpected results because sqlite changed the type from a > 64bit integer into a real. (I think)... In this case it would have been > simpler to debug, if there had been a type conversion warning or a failure. > > Regards, > Ken > > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to select Strict Affinity or No Affinity modes? Attention: DRH
I second the strict affinity mode as an optional feature, for the same reasons as Lee. A while back I ran into a problem while using the bit and feature of sqlite and got unexpected results because sqlite changed the type from a 64bit integer into a real. (I think)... In this case it would have been simpler to debug, if there had been a type conversion warning or a failure. Regards, Ken Lee Crain <[EMAIL PROTECTED]> wrote: DRH, ">> Can you explain why you think strict affinity mode >> might be beneficial to you? If somebody can provide a good >> enough rational to justify strict affinity mode, we might just put it >> in." My response to your request for an example of a benefit is that I have always been an adherent of strict datatyping as a means of trapping inadvertent software development errors. It's just one of the many aspects of my self-checking software that makes certain everything is done on purpose and not by chance. Lee Crain -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Scott Chapman Sent: Wednesday, February 06, 2008 8:23 AM To: D. Richard Hipp Cc: sqlite-users@sqlite.org Subject: Re: [sqlite] How to select Strict Affinity or No Affinity modes? I take it that there's no way to work around this currently? Scott Scott Chapman wrote: > D. Richard Hipp wrote: > >> On Feb 2, 2008, at 7:57 PM, Scott Chapman wrote: >> >> >> >>> I've looked high and low and can't find a way to invoke the other 2 >>> affinity modes. Are they available? I'm on 3.5.4. >>> >>> >> The concept of "strict" affinity mode was briefly discussed years >> ago, but we never implemented it, having never seen any benefit >> for such a thing. Can you explain why you think strict affinity mode >> might be beneficial to you? If somebody can provide a good >> enough rational to justify strict affinity mode, we might just put it >> in. >> >> > I'm working on a Python adapter that goes on top of APSW. It will > enable you to use the column types NUMERIC, DATE, TIME, TIMESTAMP and > automatically convert these to and from Python's respective data types. > > The case I'm dealing with that is not working like I want is the case of > NUMERIC column type. In SQLite, this column type gets an affinity of > REAL. If I put in a value to the column as a string literal, say > '123.23', it's stored as a REAL even though I specified it as a string > in quotes. I want it to store it as a string. The only way I've found > to fix this is to use a column type of NUMERIC_TEXT. The presense of > "TEXT" in the column type changes the affinity to string. This is not > very elegant and I was looking for any other way to make this work > correctly. "No Affinity" would probably work, if I understand it > correctly. > > I want to avoid the use of REAL types in this case because they can lead > to rounding errors, which is the whole purpose of the NUMERIC type to > begin with, in my understanding. I also would like to be able to make > the column type just NUMERIC as that is compilant with the SQL standard. > > Strict Affinity and No Affinity are mentioned in the SQLite3 Datatypes > page. If there are no plans to implement these, please consider > removing them from the docs. > > Thanks! > Scott > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to select Strict Affinity or No Affinity modes? Attention: DRH
DRH, ">> Can you explain why you think strict affinity mode >> might be beneficial to you? If somebody can provide a good >> enough rational to justify strict affinity mode, we might just put it >> in." My response to your request for an example of a benefit is that I have always been an adherent of strict datatyping as a means of trapping inadvertent software development errors. It's just one of the many aspects of my self-checking software that makes certain everything is done on purpose and not by chance. Lee Crain -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Scott Chapman Sent: Wednesday, February 06, 2008 8:23 AM To: D. Richard Hipp Cc: sqlite-users@sqlite.org Subject: Re: [sqlite] How to select Strict Affinity or No Affinity modes? I take it that there's no way to work around this currently? Scott Scott Chapman wrote: > D. Richard Hipp wrote: > >> On Feb 2, 2008, at 7:57 PM, Scott Chapman wrote: >> >> >> >>> I've looked high and low and can't find a way to invoke the other 2 >>> affinity modes. Are they available? I'm on 3.5.4. >>> >>> >> The concept of "strict" affinity mode was briefly discussed years >> ago, but we never implemented it, having never seen any benefit >> for such a thing. Can you explain why you think strict affinity mode >> might be beneficial to you? If somebody can provide a good >> enough rational to justify strict affinity mode, we might just put it >> in. >> >> > I'm working on a Python adapter that goes on top of APSW. It will > enable you to use the column types NUMERIC, DATE, TIME, TIMESTAMP and > automatically convert these to and from Python's respective data types. > > The case I'm dealing with that is not working like I want is the case of > NUMERIC column type. In SQLite, this column type gets an affinity of > REAL. If I put in a value to the column as a string literal, say > '123.23', it's stored as a REAL even though I specified it as a string > in quotes. I want it to store it as a string. The only way I've found > to fix this is to use a column type of NUMERIC_TEXT. The presense of > "TEXT" in the column type changes the affinity to string. This is not > very elegant and I was looking for any other way to make this work > correctly. "No Affinity" would probably work, if I understand it > correctly. > > I want to avoid the use of REAL types in this case because they can lead > to rounding errors, which is the whole purpose of the NUMERIC type to > begin with, in my understanding. I also would like to be able to make > the column type just NUMERIC as that is compilant with the SQL standard. > > Strict Affinity and No Affinity are mentioned in the SQLite3 Datatypes > page. If there are no plans to implement these, please consider > removing them from the docs. > > Thanks! > Scott > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
