Ticket #701 (new defect)

Opened 2 years ago

Last modified 7 months ago

[PATCH][TEST] catwalk.Browse.join_foreign_key makes bad SQLObject column name from joinColumn

Reported by: nedwards@umiacs.umd.edu Assigned to: jorge.vargas
Priority: high Milestone: __unclassified__
Component: Toolbox.Catwalk Version: 1.0.4b1
Severity: major Keywords:
Cc:

Description

Just after upgrade from 0.9a1 to 0.9a2...

In catwalk.Browse.join_foreign_key (turbogears/toolbox/catwalk/browse.py:190), a SQLObject.SORelatedJoin column is passed in, and join_foreign_key needs to determine the SQLObject column name of the foreign key in the SQLObject that this column refers to. However, the SORelatedJoin column only has the database version of the foreign key column name, and attempts to reconstruct the SQLObject name of the column as follows:

foreign_key = "%sID"%column.joinColumn.split('_')[0]

which is bad in many ways, but in particular, suppose the SQL column name is a ForeignKey? multiWordIdentifier, then the database column name is multi_word_identifier_id, but this hack generates "multiID" and an exception ensues.

Current (very bad) patch is to ask the other table for all of its columns to see which one matches the .joinColumn string and use its name.

foreign_key = [ cl.name for cl in column.otherClass.sqlmeta.columnList if cl.dbName == column.joinColumn ][0]

Very ugly. However, I don't see any other facility for reverse mapping dbName to name.

nathan

model.py:

class PeptideMapping?(SQLObject):

codingSequence = ForeignKey?('CodingSequence?')

class CodingSequence?(SQLObject):

peptideMapping = RelatedJoin?('PeptideMapping?')

Exception:

31/Mar/2006:11:03:37 HTTP INFO Page handler: <bound method Browse.index of <turbogears.toolbox.catwalk.browse.Browse object at 0xb6cb1e0c>> Traceback (most recent call last):

File "/fs/hpcbioprojects/python/lib/python2.4/site-packages/CherryPy-2.2.0rc1-py2.4.egg/cherrypy/_cphttptools.py", line 99, in _run

self.main()

File "/fs/hpcbioprojects/python/lib/python2.4/site-packages/CherryPy-2.2.0rc1-py2.4.egg/cherrypy/_cphttptools.py", line 248, in main

body = page_handler(*virtual_path, **self.params)

File "<string>", line 3, in index File "/fs/hpcbioprojects/python/lib/python2.4/site-packages/TurboGears-0.9a2-py2.4.egg/turbogears/controllers.py", line 207, in expose

output = database.run_with_transaction(expose._expose,func, accept, allow_json, allow_json_from_config,*args, **kw)

File "/fs/hpcbioprojects/python/lib/python2.4/site-packages/TurboGears-0.9a2-py2.4.egg/turbogears/database.py", line 216, in run_with_transaction

retval = func(*args, **kw)

File "<string>", line 5, in _expose File "/fs/hpcbioprojects/python/lib/python2.4/site-packages/TurboGears-0.9a2-py2.4.egg/turbogears/controllers.py", line 228, in <lambda>

expose._expose.when(rule)(lambda _func, accept, allow_json, allow_json_from_config,*args,**kw: _execute_func(

File "/fs/hpcbioprojects/python/lib/python2.4/site-packages/TurboGears-0.9a2-py2.4.egg/turbogears/controllers.py", line 246, in _execute_func

output = errorhandling.try_call(func, *args, **kw)

File "/fs/hpcbioprojects/python/lib/python2.4/site-packages/TurboGears-0.9a2-py2.4.egg/turbogears/errorhandling.py", line 59, in try_call

output = func(self, *args, **kw)

File "/fs/hpcbiohomes/nedwards/projects/python/lib/python2.4/site-packages/TurboGears-0.9a2-py2.4.egg/turbogears/toolbox/catwalk/browse.py", line 20, in index

total,rows = self.rows_for_model(object_name,start,page_size,filters)

File "/fs/hpcbiohomes/nedwards/projects/python/lib/python2.4/site-packages/TurboGears-0.9a2-py2.4.egg/turbogears/toolbox/catwalk/browse.py", line 119, in rows_for_model

relations = self.relation_values(object_name,rows)

File "/fs/hpcbiohomes/nedwards/projects/python/lib/python2.4/site-packages/TurboGears-0.9a2-py2.4.egg/turbogears/toolbox/catwalk/browse.py", line 138, in relation_values

where=AND(

File "/fs/hpcbiohomes/nedwards/projects/python/lib/python2.4/site-packages/TurboGears-0.9a2-py2.4.egg/turbogears/toolbox/catwalk/browse.py", line 195, in join_foreign_key

return getattr(column.otherClass.q,foreign_key)

File "/fs/hpcbioprojects/python/lib/python2.4/site-packages/SQLObject-0.7.1dev_r1457-py2.4.egg/sqlobject/sqlbuilder.py", line 362, in getattr

self.soClass.sqlmeta.columns[attr].dbName,

KeyError?: 'codingID' Request Headers:

COOKIE: tg-visit=1e956a910599a39aac022a574d7591f2b7e20cf5 Content-Length: ACCEPT-CHARSET: ISO-8859-1,utf-8;q=0.7,*;q=0.7 USER-AGENT: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.8.0.1) Gecko/20060111 Firefox/1.5.0.1 CONNECTION: keep-alive HOST: codon.umiacs.umd.edu:8888 ACCEPT: text/xml,application/xml,application/xhtml+xml,text/html;q=0.9,text/plain;q=0.8,image/png,*/*;q=0.5 Remote-Addr: 128.8.141.94 ACCEPT-LANGUAGE: en-us,en;q=0.5 Content-Type: Remote-Host: 128.8.141.94 ACCEPT-ENCODING: gzip,deflate KEEP-ALIVE: 300

128.8.141.94 - - [31/Mar/2006:11:03:37] "GET /catwalk/browse/?object_name=CodingSequence? HTTP/1.1" 500 3535

Attachments

test_catwalk_701.py (1.7 kB) - added by JoostM on 02/09/07 08:06:40.
testcase
701.diff (0.6 kB) - added by JoostM on 02/09/07 08:08:24.
patch against the trunk

Change History

04/14/06 09:49:44 changed by anonymous

  • version set to 0.9a4.

09/22/06 21:22:54 changed by jorge.vargas

  • milestone set to 1.1.

does this still happens?

09/23/06 17:50:34 changed by jorge.vargas

  • component changed from Toolbox to Toolbox.Catwalk.

11/08/06 14:48:33 changed by hads

Yes, this still occurs.

11/08/06 14:58:38 changed by hads

The solution above does not seem to work for me.

12/16/06 16:26:04 changed by Joost

The catwalk code could be improved by using the same syntax as used in sqlobject.joins: 

foreign_key = column.soClass.sqlmeta.style.dbColumnToPythonAttr(column.joinColumn)

However, SQLObject is self seems to be broken when it comes to naming. E.g: 

>>> from sqlobject import *
>>> from turbogears.database import AutoConnectHub
>>> hub = AutoConnectHub()
>>> __connection__ = hub
>>> class Pet(SQLObject):
...     class sqlmeta:
...         table = 'tg_pet'
...     name = StringCol()
...     user = ForeignKey("User")
...
>>> class User(SQLObject):
...     class sqlmeta:
...         table = "tg_user"
...     name = StringCol()
...     my_pet = SingleJoin("Pet")
...
>>> from turbogears import database
>>> database.set_db_uri("sqlite:///:memory:")
>>> User.createTable(ifNotExists=True)
>>> Pet.createTable(ifNotExists=True)
>>> u = User(name="jhon")
>>> u.my_pet
Traceback (most recent call last):
  File "<stdin>", line 1, in ?
  File "<string>", line 1, in <lambda>
  File "c:\python24\lib\site-packages\SQLObject-0.7.2-py2.4.egg\sqlobject\joins.py", line 293, in performJoin
    results = self.otherClass.select(
  File "c:\python24\lib\site-packages\SQLObject-0.7.2-py2.4.egg\sqlobject\sqlbuilder.py", line 374, in __getattr__
    raise AttributeError("%s instance has no attribute '%s'" % (self.soClass.__name__, attr))
AttributeError: Pet instance has no attribute 'tgUserID'

02/09/07 08:06:40 changed by JoostM

  • attachment test_catwalk_701.py added.

testcase

02/09/07 08:08:24 changed by JoostM

  • attachment 701.diff added.

patch against the trunk

02/09/07 08:09:06 changed by JoostM

  • summary changed from catwalk.Browse.join_foreign_key makes bad SQLObject column name from joinColumn to [PATCH][TEST] catwalk.Browse.join_foreign_key makes bad SQLObject column name from joinColumn.

02/11/07 00:43:08 changed by jorge.vargas

  • owner changed from anonymous to jorge.vargas.

i'll try to take a look at this and commit it if possible.

02/26/07 17:19:57 changed by harrybozack

I attempted the proposed solution by Joost and I still get similar errors.

03/28/07 12:42:00 changed by alberto

  • milestone changed from 1.1 to __unclassified__.

Batch moved into unclassified from 1.1 to properly track progress on the later

10/28/07 11:58:45 changed by Kylotan

  • version changed from 0.9a4 to 1.0.4b1.

I just posted on bug 1412, but this is perhaps more relevant to my case. CamelCase foreign key names seem to lose the second word when the join is constructed, and end up looking for an ID that doesn't exist. eg. A join between MotorCar? and MotorCarDefinition? ends up failing because it can't find MotorID in the second object, when it should be looking for MotorCarID instead.