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 <johnmhee...@gmail.com> 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
