Re: [sqlite] How to select Strict Affinity or No Affinity modes?
Yes choice is good, Either way I'm greatful for an Excellent Tool, I can workaround and Live without strict Affinity. But the option to enable it would be a welcome feature. Would there be any performance implications of Strict Affinity, either positive or negative? Samuel Neff [EMAIL PROTECTED] wrote: But the important point is that no matter how much discussion we have, we will never all agree that untyped is better than typed or that typed is better than typed. That's why an option so individual developers can choose is good. We don't have to agree, with an option we can agree to disagree. Sam On Feb 7, 2008 11:46 PM, Roger Binns wrote: There are also a whole school of people who believe that dynamic typing as used in SQLite is far more productive and results in less code. For example the Python programming language is typed in a similar way. http://en.wikipedia.org/wiki/Duck_typing There are many sites with many discussion threads containing opinions, anecdotes and evidence and code supporting all the positions on this issue. Feel free to discuss the merits there. Roger ___ 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?
But the important point is that no matter how much discussion we have, we will never all agree that untyped is better than typed or that typed is better than typed. That's why an option so individual developers can choose is good. We don't have to agree, with an option we can agree to disagree. Sam On Feb 7, 2008 11:46 PM, Roger Binns [EMAIL PROTECTED] wrote: There are also a whole school of people who believe that dynamic typing as used in SQLite is far more productive and results in less code. For example the Python programming language is typed in a similar way. http://en.wikipedia.org/wiki/Duck_typing There are many sites with many discussion threads containing opinions, anecdotes and evidence and code supporting all the positions on this issue. Feel free to discuss the merits there. Roger ___ 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 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 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
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
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
Re: [sqlite] How to select Strict Affinity or No Affinity modes?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Lee Crain wrote: 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. There are also a whole school of people who believe that dynamic typing as used in SQLite is far more productive and results in less code. For example the Python programming language is typed in a similar way. http://en.wikipedia.org/wiki/Duck_typing There are many sites with many discussion threads containing opinions, anecdotes and evidence and code supporting all the positions on this issue. Feel free to discuss the merits there. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFHq96dmOOfHg372QQRAnA/AJ9cHWhoprB0l1HAczzaQPiwNMgLZwCgr6tC aop4JoSVzKZp5UPwvr88WFs= =I8av -END PGP SIGNATURE- ___ 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?
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
Re: [sqlite] How to select Strict Affinity or No Affinity modes?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Scott Chapman wrote: I take it that there's no way to work around this currently? You can leave out the type for each column when declaring the table schema and no affinity conversions will happen. Alternatively, if you require that a type be declared then you can make up your own scheme that doesn't include any of the strings that SQLite picks up, somewhat similar to how C++ compilers do it: http://en.wikipedia.org/wiki/Name_mangling Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFHqek7mOOfHg372QQRAm5XAJ0T2F/dA3TNEejFyFncfKAZkPIgywCglrG2 t0sPG/jg50Kevkb2eZQKUfs= =GlXb -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] How to select Strict Affinity or No Affinity modes?
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. Any pointers would be greatly appreciated. Thanks! Scott ___ 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?
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. D. Richard Hipp [EMAIL PROTECTED] ___ 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?
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