Thanks for the quick response Oleg... On 12/22/2015 1:28 PM, Oleg Broytman wrote: > Hi! > > On Tue, Dec 22, 2015 at 12:50:09PM -0500, Nathan Edwards > <n...@georgetown.edu> wrote: >> >> A number of my students used some form of the ForeignKey abuse shown >> below. The first one B(anInt=2,afk=a.id) is pretty benign (though >> unnecessary), but the really strange one is B(anInt=4,afk='Strange!'). >> At least with sqlite3 as the backend, this is stored in the database as >> a string (!!!!), even though the schema declares it as an INT and >> formalizes the reference constraint to table A's id (also an INT). > > Well, SQLite allows that: https://www.sqlite.org/faq.html#q3 (and see > below...)
Wow. Quite the "feature". Gulp. > He-he. Yes, that a wart but it's hard to fix. The PRIMARY KEY (id) > column in a referenced table can be of string type[1] so ForeignKey > allows both ints and strings (a ForeignKey doesn't know the type of the > id column it points to). Hmmm. I thought this was probably the rationale. As a counterpoint, however, when it the foreign key is dereferenced, the get method of the Foreign table applies cls.sqlmeta.idType to the value as part of the dereferencing code. This is in fact where the exception is thrown when the string is not not the integer that SQLObject expects. This would suggest that ForeignKey *does* have a way to check the type of the value at insert time...except that the ForeignKey column object only has the name of the Foreign table as a string, not a table object, due to declaration order issues, though by the time we are inserting rows, it had better exist. Sigh. > If you want stricter foreign keys use stricter backends: MySQL with > InnoDB tables or Postgres. > > 1. http://sqlobject.org/FAQ.html#non-integer-ids Here, the documentation suggests that idType can be int() or str(), and other more complex non-integer ids be inserted by conversion to strings. Seems like idType from the foreign table could be used to provide this check (either calling it will each setting, or just testing for its two recommended values). Not sure how big an efficiency hit it would be to add this check, but all other values seem to be checked at insertion by the column type...I'll see if I can figure out a minimalist patch to propose... Thanks, - n -- Dr. Nathan Edwards n...@georgetown.edu Department of Biochemistry and Molecular & Cellular Biology Georgetown University Medical Center Room 1217, Harris Building, 3300 Whitehaven St, NW, Washington DC 20007 Phone: 202-687-7042, Fax: 202-687-0057 ------------------------------------------------------------------------------ _______________________________________________ sqlobject-discuss mailing list sqlobject-discuss@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss
