If you want to import foreign integer keys directly into sqlite
without web2py but rely on the schema produced by web2py then a change
is required to the sqlite schema produced by web2py.
The easiest solution is simply to modify the following lines in gluon
\dal.py and in the legacy gluon\sql.py for sqlite by adding the single
word INTEGER so as
'reference': 'REFERENCES %(foreign_key)s ON DELETE %
(on_delete_action)s',
becomes
'reference': 'INTEGER REFERENCES %(foreign_key)s ON DELETE %
(on_delete_action)s',
and then restarting we2py.
This change ensures that any keys imported into sqlite by sqlite
directly have a sqlite dynamic typing storage class of 'integer; and
not of 'text'.
This should not effect the way web2py works internally. In fact web2py
uses integer keys. The fact that web2py works correctly without
'INTEGER' added is accidental.
To understand why web2py will not work with external imported data
without the change, a relevant discussion of dynamic typing issues of
sqlite can be viewed in my recent post 'Subtle data integrity bug with
web2p' at
http://groups.google.com/group/web2py/browse_thread/thread/310ca5f727b70c84#
John Heenan
