Thread: [SQLObject] Problems understanding SingleJoins and ForeignKeys
SQLObject is a Python ORM.
Brought to you by:
ianbicking,
phd
From: Sam's L. <sam...@gm...> - 2006-02-23 02:39:41
|
So I'm happily developing a CherryPy-SqlObject-Cheetah application when i decide that I should really be using some more SqlObject features. Namely ForeignKeys and SingleJoins. After destroying my application, I wrote a simple test case.....hopefully someone can take a look at it and tell me what I'm doing wrong. #!/usr/bin/python from sqlobject import * class Wstore(SQLObject): companyId =3D StringCol(default=3DNone, alternateID=3DTrue, length=3D60= ) companyName =3D StringCol(default=3DNone) specialSearchR =3D SingleJoin('SpecialSearch') class SpecialSearch(SQLObject): companyId =3D ForeignKey('Wstore', alternateID=3DTrue) secondStageMatch =3D StringCol(default=3DNone) connection =3D connectionForURI("mysql://root:password@localhost/testdb") sqlhub.processConnection =3D connection Wstore._connection.debug =3D True SpecialSearch._connection.debug =3D True Wstore.createTable() SpecialSearch.createTable() x=3DWstore(companyId=3D"TheCompany ", companyName=3D"The Company") y=3DSpecialSearch(companyId=3Dx.id, secondStageMatch=3D"foo") z=3Dx.SpecialSearchR --------- So my first question is the line y=3DSpecialSearch(companyId=3Dx.id, secondStageMatch=3D"foo") That line seems to work, but I would have expected ForeignKeys to work more like y=3DSpecialSearch(companyId=3Dx, secondStageMatch=3D"foo") After all it's a foreign key to the object right? My second questions is the line z=3Dx.SpecialSearchR ...this doesn't work at all. I would have expected it to to return what I just stored in y. Any idea why it doesn't work? Thanks! |
From: Sam's L. <sam...@gm...> - 2006-02-24 13:58:23
|
[I sent this yesterday but it seems like it never went through. If for som= e reason you get two copies, I apologize.] So I'm happily developing a CherryPy-SqlObject-Cheetah application when i decide that I should really be using some more SqlObject features. Namely ForeignKeys and SingleJoins. After destroying my application, I wrote a simple test case.....hopefully someone can take a look at it and tell me what I'm doing wrong. #!/usr/bin/python from sqlobject import * class Wstore(SQLObject): companyId =3D StringCol(default=3DNone, alternateID=3DTrue, length=3D60= ) companyName =3D StringCol(default=3DNone) specialSearchR =3D SingleJoin('SpecialSearch') class SpecialSearch(SQLObject): companyId =3D ForeignKey('Wstore', alternateID=3DTrue) secondStageMatch =3D StringCol(default=3DNone) connection =3D connectionForURI("mysql://root:password@localhost/testdb") sqlhub.processConnection =3D connection Wstore._connection.debug =3D True SpecialSearch._connection.debug =3D True Wstore.createTable() SpecialSearch.createTable() x=3DWstore(companyId=3D"TheCompany ", companyName=3D"The Company") y=3DSpecialSearch(companyId=3Dx.id, secondStageMatch=3D"foo") z=3Dx.SpecialSearchR --------- So my first question is the line y=3DSpecialSearch(companyId=3Dx.id, secondStageMatch=3D"foo") That line seems to work, but I would have expected ForeignKeys to work more like y=3DSpecialSearch(companyId=3Dx, secondStageMatch=3D"foo") After all it's a foreign key to the object right? My second questions is the line z=3Dx.SpecialSearchR ...this doesn't work at all. I would have expected it to to return what I just stored in y. Any idea why it doesn't work? Thanks! |
From: Oleg B. <ph...@ma...> - 2006-02-24 14:40:41
|
On Tue, Feb 21, 2006 at 04:01:40PM -0300, Sam's Lists wrote: > I sent this yesterday but it seems like it never went through. I got it yesterday. I am going to look at it later... Oleg. -- Oleg Broytmann http://phd.pp.ru/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |
From: Oleg B. <ph...@ph...> - 2006-02-26 16:05:19
|
On Mon, Feb 20, 2006 at 11:55:38PM -0300, Sam's Lists wrote: > #!/usr/bin/python > > from sqlobject import * > > class Wstore(SQLObject): > companyId = StringCol(default=None, alternateID=True, length=60) > companyName = StringCol(default=None) > specialSearchR = SingleJoin('SpecialSearch') > > class SpecialSearch(SQLObject): > companyId = ForeignKey('Wstore', alternateID=True) > secondStageMatch = StringCol(default=None) > > connection = connectionForURI("mysql://root:password@localhost/testdb") > sqlhub.processConnection = connection > > Wstore._connection.debug = True > SpecialSearch._connection.debug = True > Wstore.createTable() > SpecialSearch.createTable() > > x=Wstore(companyId="TheCompany ", companyName="The Company") > y=SpecialSearch(companyId=x.id, secondStageMatch="foo") > > z=x.SpecialSearchR > > --------- > > So my first question is the line > y=SpecialSearch(companyId=x.id, secondStageMatch="foo") > > That line seems to work, but I would have expected ForeignKeys to work more > like > y=SpecialSearch(companyId=x, secondStageMatch="foo") This is fixed in the repository. Will be in SQLObject 0.8. > My second questions is the line > z=x.SpecialSearchR > ...this doesn't work at all. I would have expected it to to return what I > just stored in y. > > Any idea why it doesn't work? First, case msimatch: > specialSearchR = SingleJoin('SpecialSearch') Second, you've forgotten to name a column for join. Oleg. -- Oleg Broytmann http://phd.pp.ru/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |
From: Sam's L. <sam...@gm...> - 2006-02-27 23:43:45
|
Oleg... Thank you so much! I got the SingleJoin to work following your instructions. I'm still confused as to why I need to specify the joinColum= n though.... (besides the obvious reason that when I do it, and capitalize everything correctly it works....) I can't find any examples of SingleJoins on the web, but the examples I've seen of MultipleJoins don't specify the joinColumn. Is this somehow a unique requirement of SingleJoins? A tangent....has there been any thought to allowing comments directly in th= e documentation on the web or somehow wikifying the docs? I'd be glad to share the things I learn as I puzzle through the docs, but I don't have an easy way to do it now. Thanks again, Sam P.S. Any idea when 0.8 will be officially released? How stable is the cod= e that's now in the repository? On 2/26/06, Oleg Broytmann <ph...@ph...> wrote: > > On Mon, Feb 20, 2006 at 11:55:38PM -0300, Sam's Lists wrote: > > #!/usr/bin/python > > > > from sqlobject import * > > > > class Wstore(SQLObject): > > companyId =3D StringCol(default=3DNone, alternateID=3DTrue, length= =3D60) > > companyName =3D StringCol(default=3DNone) > > specialSearchR =3D SingleJoin('SpecialSearch') > > > > class SpecialSearch(SQLObject): > > companyId =3D ForeignKey('Wstore', alternateID=3DTrue) > > secondStageMatch =3D StringCol(default=3DNone) > > > > connection =3D connectionForURI("mysql://root:password@localhost/testdb= ") > > sqlhub.processConnection =3D connection > > > > Wstore._connection.debug =3D True > > SpecialSearch._connection.debug =3D True > > Wstore.createTable() > > SpecialSearch.createTable() > > > > x=3DWstore(companyId=3D"TheCompany ", companyName=3D"The Company") > > y=3DSpecialSearch(companyId=3Dx.id, secondStageMatch=3D"foo") > > > > z=3Dx.SpecialSearchR > > > > --------- > > > > So my first question is the line > > y=3DSpecialSearch(companyId=3Dx.id, secondStageMatch=3D"foo") > > > > That line seems to work, but I would have expected ForeignKeys to work > more > > like > > y=3DSpecialSearch(companyId=3Dx, secondStageMatch=3D"foo") > > This is fixed in the repository. Will be in SQLObject 0.8. > > > My second questions is the line > > z=3Dx.SpecialSearchR > > ...this doesn't work at all. I would have expected it to to return wha= t > I > > just stored in y. > > > > Any idea why it doesn't work? > > First, case msimatch: > > > specialSearchR =3D SingleJoin('SpecialSearch') > > Second, you've forgotten to name a column for join. > > Oleg. > -- > Oleg Broytmann http://phd.pp.ru/ ph...@ph... > Programmers don't die, they just GOSUB without RETURN. > > > ------------------------------------------------------- > This SF.Net email is sponsored by xPML, a groundbreaking scripting > language > that extends applications into web and mobile media. Attend the live > webcast > and join the prime developer group breaking into this new coding > territory! > http://sel.as-us.falkag.net/sel?cmd=3Dlnk&kid=3D110944&bid=3D241720&dat= =3D121642 > _______________________________________________ > sqlobject-discuss mailing list > sql...@li... > https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss > |
From: Oleg B. <ph...@ph...> - 2006-02-28 09:20:05
|
On Mon, Feb 27, 2006 at 08:43:37PM -0300, Sam's Lists wrote: > I'm still confused as to why I need to specify the joinColumn > though... Look at your classes: class Wstore(SQLObject): companyId = StringCol(default=None, alternateID=True, length=60) specialSearchR = SingleJoin('SpecialSearch') class SpecialSearch(SQLObject): companyId = ForeignKey('Wstore', alternateID=True) How can SQLObject guess by what column the join should be performed? > I can't find any examples of SingleJoins on the web, but the examples I've > seen of MultipleJoins don't specify the joinColumn. Is this somehow a > unique requirement of SingleJoins? No. I use joinColumn very often for all joins. Look, the code from the real program: class Service(Base): """Services""" title = UnicodeCol(length=255, default=None, dbEncoding=config.Database.encoding) groups = RelatedJoin("ServiceGroup") specimen = ForeignKey("Specimen", default=None) price = IntCol(default=0) class ServiceGroup(Base): """Service groups""" title = UnicodeCol(length=255, alternateID=True, dbEncoding=config.Database.encoding) services = RelatedJoin("Service") Note - no joinColumn here. But in other tables I have to use joinColumn: class Specimen(Base): """Specimens""" title = UnicodeCol(length=255, default=None, dbEncoding=config.Database.encoding) services = MultipleJoin("Service", joinColumn="specimen_id") class Order(Base): """Orders""" class sqlmeta: table = "orders" # the word "order" is reserved in SQL92 patient = ForeignKey("Patient", default=None, cascade=True) services = MultipleJoin("OrderService", joinColumn="order_id") class OrderService(Base): """Order's service""" order = ForeignKey("Order", cascade=True) service = ForeignKey("Service") > A tangent....has there been any thought to allowing comments directly in the > documentation on the web or somehow wikifying the docs? I'd be glad to > share the things I learn as I puzzle through the docs, but I don't have an > easy way to do it now. You are welcome: http://wiki.sqlobject.org > P.S. Any idea when 0.8 will be officially released? Not so soon. We are going to release 0.7.1 RSN, but it lacks the ability to freely interchange obj and obj.id. You can join the work at http://sourceforge.net/tracker/index.php?func=detail&aid=1353728&group_id=74338&atid=540674 > How stable is the code > that's now in the repository? Very stable, I think. I run the test suite with Python 2.3 and 2.4, with Postgres and SQLite, with psycopg1, psycopg2, PySQLite1 and PySQLite2 before committing an every single patch. I always ask developers who submit patches to provide tests and documentation (though they don't always obey ;)). And I do not allow patches that break tests. Read the entire page I mentioned above... Oleg. -- Oleg Broytmann http://phd.pp.ru/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |
From: Bill C. <sql...@ce...> - 2006-02-28 17:38:21
|
On Tue, Feb 28, 2006, Oleg Broytmann wrote: >On Mon, Feb 27, 2006 at 08:43:37PM -0300, Sam's Lists wrote: >> I'm still confused as to why I need to specify the joinColumn >> though... > > Look at your classes: > >class Wstore(SQLObject): > companyId = StringCol(default=None, alternateID=True, length=60) > specialSearchR = SingleJoin('SpecialSearch') > >class SpecialSearch(SQLObject): > companyId = ForeignKey('Wstore', alternateID=True) > > How can SQLObject guess by what column the join should be performed? > >> I can't find any examples of SingleJoins on the web, but the examples I've >> seen of MultipleJoins don't specify the joinColumn. Is this somehow a >> unique requirement of SingleJoins? > > No. I use joinColumn very often for all joins. Look, the code from the >real program: Speaking of Real Programs(tm) -- are there any open source programs that use sqlobject? I would love to grab some code to see how people do things. Bill -- INTERNET: bill@Celestial.COM Bill Campbell; Celestial Software LLC URL: http://www.celestial.com/ PO Box 820; 6641 E. Mercer Way FAX: (206) 232-9186 Mercer Island, WA 98040-0820; (206) 236-1676 ``Most people, sometime in their lives, stumble across truth. Most jump up, brush themselves off, and hurry on about their business as if nothing had happened.'' - Sir Winston Churchill |
From: Oleg B. <ph...@ma...> - 2006-02-28 17:42:51
|
On Tue, Feb 28, 2006 at 09:38:10AM -0800, Bill Campbell wrote: > Speaking of Real Programs(tm) -- are there any open source > programs that use sqlobject? I would love to grab some code to > see how people do things. http://sqlobject.org/links.html Oleg. -- Oleg Broytmann http://phd.pp.ru/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |