Thread: [SQLObject] Strange ForeignKey behavior...
SQLObject is a Python ORM.
Brought to you by:
ianbicking,
phd
Menu
▾
▴
sqlobject-discuss
|
From: Nathan E. <nj...@ge...> - 2015-12-22 18:12:34
|
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).
Python 2.7.3, SQLObject version 2.1.2.
from sqlobject import *
class A(SQLObject):
aFloat = FloatCol()
class B(SQLObject):
anInt = IntCol()
afk = ForeignKey("A")
sqlhub.processConnection = connectionForURI('sqlite:test.db3?debug=1')
A.dropTable(ifExists=True)
B.dropTable(ifExists=True)
A.createTable()
B.createTable()
a = A(aFloat=1.0)
b = B(anInt=1,afk=a)
b = B(anInt=2,afk=a.id)
b = B(anInt=3,afk=str(a.id))
# This succeeds and is inserted to sqlite database!
b = B(anInt=4,afk='Strange!')
# IntCols are checked!
# This throws a formencode exception
# b = B(anInt='a string',afk=a)
for a in A.select():
print a
for b in B.select():
# ValueError exception thrown in attempt to dereference
print b,b.afk
- n
--
Dr. Nathan Edwards nj...@ge...
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
|
|
From: Oleg B. <ph...@ph...> - 2015-12-22 18:28:36
|
Hi! On Tue, Dec 22, 2015 at 12:50:09PM -0500, Nathan Edwards <nj...@ge...> 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...) > Python 2.7.3, SQLObject version 2.1.2. > > from sqlobject import * > > class A(SQLObject): > aFloat = FloatCol() > > class B(SQLObject): > anInt = IntCol() > afk = ForeignKey("A") > > sqlhub.processConnection = connectionForURI('sqlite:test.db3?debug=1') > > A.dropTable(ifExists=True) > B.dropTable(ifExists=True) > A.createTable() > B.createTable() > > a = A(aFloat=1.0) > > b = B(anInt=1,afk=a) > b = B(anInt=2,afk=a.id) > b = B(anInt=3,afk=str(a.id)) > # This succeeds and is inserted to sqlite database! > b = B(anInt=4,afk='Strange!') 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). If you want stricter foreign keys use stricter backends: MySQL with InnoDB tables or Postgres. 1. http://sqlobject.org/FAQ.html#non-integer-ids > # IntCols are checked! > # This throws a formencode exception > # b = B(anInt='a string',afk=a) > > for a in A.select(): > print a > for b in B.select(): > # ValueError exception thrown in attempt to dereference > print b,b.afk > > - n > > -- > Dr. Nathan Edwards nj...@ge... > 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 Oleg. -- Oleg Broytman http://phdru.name/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |
|
From: Nathan E. <nj...@ge...> - 2015-12-22 19:49:27
|
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 <nj...@ge...> 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 nj...@ge... 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 |
|
From: Oleg B. <ph...@ph...> - 2015-12-22 20:14:36
|
On Tue, Dec 22, 2015 at 02:19:12PM -0500, Nathan Edwards <nj...@ge...> wrote:
> On 12/22/2015 1:28 PM, Oleg Broytman wrote:
> > On Tue, Dec 22, 2015 at 12:50:09PM -0500, Nathan Edwards <nj...@ge...> wrote:
> class B(SQLObject):
> anInt = IntCol()
> afk = ForeignKey("A")
...
> # This succeeds and is inserted to sqlite database!
> b = B(anInt=4,afk='Strange!')
>
> > 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.
Not at all. ForeignKeys just don't do any validation or conversion.
Would be worth fixing.
> I'll see if I can figure out a minimalist patch to
> propose...
Eagerly waiting! But if you don't succeed notify me -- I'll at least
add a TODO item.
> Thanks,
>
> - n
>
> --
> Dr. Nathan Edwards nj...@ge...
> 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
Oleg.
--
Oleg Broytman http://phdru.name/ ph...@ph...
Programmers don't die, they just GOSUB without RETURN.
|
|
From: Nathan E. <nj...@ge...> - 2015-12-23 17:10:00
Attachments:
patch.txt
test_ForeignKey_validation.py
|
>> I'll see if I can figure out a minimalist patch to
>> propose...
>
> Eagerly waiting! But if you don't succeed notify me -- I'll at least
> add a TODO item.
Not sure if this will be up to your coding standards, but here is an
attempt - it uses the validator infrastructure to attempt conversion
using the idType function of the foreign table. This will suffice to
ensure values can be converted to the correct type, next level of
validation would be to require the converted value to be a valid id for
the foreign table (probably too much overhead). I think this approach is
relatively consistent with the current style and conventions, but I'm
sure you'll tell me if not. :-)
Patch for col.py and a new test module attached.
Cheers!
- n
--
Dr. Nathan Edwards nj...@ge...
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
|
|
From: Oleg B. <ph...@ph...> - 2015-12-23 19:35:22
|
Hi!
On Wed, Dec 23, 2015 at 12:09:27PM -0500, Nathan Edwards <nj...@ge...> wrote:
>
> >> I'll see if I can figure out a minimalist patch to
> >> propose...
> >
> > Eagerly waiting! But if you don't succeed notify me -- I'll at least
> > add a TODO item.
>
> Not sure if this will be up to your coding standards, but here is an
> attempt - it uses the validator infrastructure to attempt conversion
> using the idType function of the foreign table. This will suffice to
> ensure values can be converted to the correct type, next level of
> validation would be to require the converted value to be a valid id for
> the foreign table (probably too much overhead). I think this approach is
> relatively consistent with the current style and conventions, but I'm
> sure you'll tell me if not. :-)
>
> Patch for col.py and a new test module attached.
Thank you! I need to work on it: the test doesn't work with Postgres;
I'd like to rename 'to_python' to 'from_python' and remove 'from_python'
assignment (I think there is no need to validate foreign keys when
they're fetched from the database); and I want to fix style.
> Cheers!
>
> - n
>
> --
> Dr. Nathan Edwards nj...@ge...
> 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
Oleg.
--
Oleg Broytman http://phdru.name/ ph...@ph...
Programmers don't die, they just GOSUB without RETURN.
|
|
From: Oleg B. <ph...@ph...> - 2015-12-23 22:09:47
|
On Wed, Dec 23, 2015 at 08:35:10PM +0100, Oleg Broytman <ph...@ph...> wrote:
> Hi!
> On Wed, Dec 23, 2015 at 12:09:27PM -0500, Nathan Edwards <nj...@ge...> wrote:
> >
> > >> I'll see if I can figure out a minimalist patch to
> > >> propose...
> > >
> > > Eagerly waiting! But if you don't succeed notify me -- I'll at least
> > > add a TODO item.
> >
> > Not sure if this will be up to your coding standards, but here is an
> > attempt - it uses the validator infrastructure to attempt conversion
> > using the idType function of the foreign table.
> >
> > Patch for col.py and a new test module attached.
>
> Thank you! I need to work on it: the test doesn't work with Postgres;
> I'd like to rename 'to_python' to 'from_python' and remove 'from_python'
> assignment (I think there is no need to validate foreign keys when
> they're fetched from the database); and I want to fix style.
I fixed all that but in required a few major changes.
1. Postgres. Until now SQLObject used to create ForeignKeys of type
idType of the current table, i.e. usually INT. I fixed that (by adding a
lookup of idType of the referenced table), but that a big
backward-incompatible change and I don't want to apply it to bugfix
releases. So I think the patches will go to 2.2.0b2 and 3.0.0a2.
2. 'to_python' => 'from_python': works.
3. Test applied, but I have to create a helper function to setup
mutually referenced classes; before that test framework didn't work with
cyclic references (but see test_cyclic_reference.py).
4. Style mostly fixed. I'll do more fixes when merge the code into
branch 3.0.
> > Cheers!
> >
> > - n
> >
> > --
> > Dr. Nathan Edwards nj...@ge...
> > 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
Oleg.
--
Oleg Broytman http://phdru.name/ ph...@ph...
Programmers don't die, they just GOSUB without RETURN.
|
|
From: Oleg B. <ph...@ph...> - 2015-12-23 22:11:10
|
On Wed, Dec 23, 2015 at 11:09:35PM +0100, Oleg Broytman <ph...@ph...> wrote:
> 1. Postgres. Until now SQLObject used to create ForeignKeys of type
> idType of the current table, i.e. usually INT. I fixed that (by adding a
> lookup of idType of the referenced table), but that a big
> backward-incompatible change and I don't want to apply it to bugfix
> releases. So I think the patches will go to 2.2.0b2 and 3.0.0a2.
BTW, see the patches (attached).
> > > Cheers!
> > >
> > > - n
> > >
> > > --
> > > Dr. Nathan Edwards nj...@ge...
> > > 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
Oleg.
--
Oleg Broytman http://phdru.name/ ph...@ph...
Programmers don't die, they just GOSUB without RETURN.
|
×
Want the latest updates on software, tech news, and AI?
Get latest updates about software, tech news, and AI from SourceForge directly in your inbox once a month.
Thanks for helping keep SourceForge clean.
X