Yes, the bug is now fixed. I checked out the current mercurial trunk for web2py Version 1.79.2 (2010-06-08 16:40:21)
All I had to do to confirm the bug was fixed was change a web2py db.Field type from a 'char' type to a 'date' type and confirm database administration worked for the relevant table of an uploaded application. Thanks for the quick change. Seems surreal that such a subtle data integrity bug could be fixed in this manner! John Heenan On Jul 9, 11:04 pm, mdipierro <[email protected]> wrote: > Thanks for the explanation. It helps clarify a long standing problem. > I have a solution in trunk based on your comment. Please check it and > let us know if this is what you had in mind. > > Massimo > > On 9 Lug, 07:55, John Heenan <[email protected]> wrote: > > > This is a report of a subtle data integrity bug that is difficult to > > describe in a short manner. However the bug is trivial to fix. > > > I noticed the bug when I imported data directly into sqlite3 that had > > date fields with empty dates. I used the sqlite3 utility .import > > command as I needed to preserve the ROWID primary keys. The dates are > > termination dates and should only be filled in when a termination > > occurs. > > > If the corresponding web2py db.Field type is 'date' and the imported > > value of the date field is empty then the following error message > > occurs from the web2py database administrator. > > Invalid Query invalid literal for int() with base 10: '' > > > If web2py is used to enter data then no error arises. > > > However if the web2py entered record is exported from sqlite and then > > reimported the error occurs. > > > This should be enough to ring bells that there appears to be a data > > integrity bug. > > > The first reaction of experts is likely to be that the error is > > related to dynamic typing of sqlite and the requirement that text > > entered date follows a strict ISO8601 format for sqlite3 internal date > > functions to work correctly. However this is irrelevant as web2py > > stores and manipulates date data without using sqlite internal date > > functions and web2py does not necessarily use a complete ISO8601 > > string. > > > The next paragraph might appear bizarre and wrong to a database > > expert, however to anyone familiar with the dynamic typing rules of > > sqlite there is nothing wrong. > > > The key to the problem is examining the type of dynamic data stored by > > sqlite3. The typename in the create statement for the date fileld is > > 'DATE'. Since 'DATE' is not matched by sqlite3's dynmaic typing column > > affinity rules, the affinity of the column defaults to 'NUMERIC'. The > > actual type of the sqlite imported date with an empty date is 'text' > > but the type of the date field when entered through web2py is 'null' > > instead! > > > This means the problem lies with web2py and is easily fixed. All > > web2py needs to do when parsing dates is to treat a null and an empty > > string the same way! > > > John Heenan
