Hello!
COMMENTS WELCOME, but this is a test case highlighing a common programming
paradigm with SQLObject. I'm NOT interested in how to improve my usage of
SQLObject, I'm interested in improving SQLObject to make it smarter about
dealing with this situation.
As promised, here is the normal programmer case for wanting some kind of
eager loading. This is driving my urging of a performance enhancement
surrounding get-by-id being bound/prepared.
Here is a test case script, nicely generic and complete, that shows lots of
selects flying by for a simple case of common usage. We do this a lot, with
multiple other access in and around each one of the accesses like this, like
"print obj1.other.chain.field".
Or, more obviously, stuff like:
For thisuser in group.project.organization.users:
do stuff
print "user's city is ", thisUser.address.cityName
print "user's bank name is ", thisUser.bank.name
print "user's bank address is ", thisUser.bank.address.printableAddress
do other stuff with thisUser.
Here's the test case, should be runnable by anyone given a dsn:
# test multiple selects
# Kevin J. Rice (http://justanyone.com)
import site, sys, os
site.addsitedir(os.path.realpath(__file__))
site.addsitedir(os.path.realpath('Lib/external'))
from sqlobject import *
from pprint import pprint, pformat
from sqlobject.sqlbuilder import IN
from sqlobject.col import popKey, pushKey
class SOTextCol(SOStringCol):
def __init__(self, **kw):
popKey(kw, 'length')
popKey(kw, 'varchar')
SOStringCol.__init__(self, **kw)
class TextCol(Col):
baseClass = SOTextCol
def getTransaction():
dsn = "host=127.0.0.1 dbname=dbrel203 user=krice password=chicken4lunch"
con = PostgresConnection(dsn, debug=1)
return con
class Org(SQLObject):
class sqlmeta:
idName = 'OrgID'
table = 'Org'
orgName = TextCol(dbName='orgName', default=None)
users = MultipleJoin('OrgUser', joinColumn='myOrgID')
class OrgUser(SQLObject):
class sqlmeta:
idName = 'OrgUserID'
table = 'OrgUser'
firstName = TextCol(dbName='firstName', default=None)
myOrgID = KeyCol(dbName='myOrgID', default=None, foreignKey="Org")
t= getTransaction()
try:
Org.dropTable(connection=t)
OrgUser.dropTable(connection=t)
except:
pass
t= getTransaction()
Org.createTable(connection=t)
OrgUser.createTable(connection=t)
userNameList = '''bill bob dave ed john steve luke mark john
matt joseph randy andy wendy carrie will michelle rebecca lisa
mary'''.split()
orgNameList = '''Exxon Mobil Chevron Texaco Phillips66 BP Amoco
Aramco Lukoil Gazprom 3M UBS Warburg IBM JCPenny NYSE CPSE ABC'''.split()
i = 0
for oname in orgNameList:
newOrg = Org(orgName=oname, connection=t)
OrgUser(firstName=userNameList[i], myOrgID=newOrg.id, connection=t)
i += 1
OrgUser(firstName=userNameList[i], myOrgID=newOrg.id, connection=t)
i += 1
OrgUser(firstName=userNameList[i], myOrgID=newOrg.id, connection=t)
i += 1
OrgUser(firstName=userNameList[i], myOrgID=newOrg.id, connection=t)
i += 1
if i >= len(userNameList):
i = 0
t = getTransaction()
for thisOrg in Org.select(IN(Org.q.orgName, orgNameList[0:4]),
connection=t):
print "Have Org: %s, userslist=%s." % (thisOrg.orgName, \
pformat([x.firstName for x in thisOrg.users]))
# SQL statement for the same thing:
# select firstname, orgName from Org, OrgUser where Org.orgID =
OrgUser.myOrgID;
OUTPUT:
Have Org: Exxon, userslist=['bill', 'bob', 'dave', 'ed'].
1/QueryAll: SELECT OrgUserID FROM OrgUser WHERE myOrgID = (2)
1/QueryR : SELECT OrgUserID FROM OrgUser WHERE myOrgID = (2)
1/COMMIT : auto
1/QueryOne: SELECT firstName, myOrgID FROM OrgUser WHERE
((OrgUser.OrgUserID) = (5))
1/QueryR : SELECT firstName, myOrgID FROM OrgUser WHERE
((OrgUser.OrgUserID) = (5))
1/COMMIT : auto
1/QueryOne: SELECT firstName, myOrgID FROM OrgUser WHERE
((OrgUser.OrgUserID) = (6))
1/QueryR : SELECT firstName, myOrgID FROM OrgUser WHERE
((OrgUser.OrgUserID) = (6))
1/COMMIT : auto
1/QueryOne: SELECT firstName, myOrgID FROM OrgUser WHERE
((OrgUser.OrgUserID) = (7))
1/QueryR : SELECT firstName, myOrgID FROM OrgUser WHERE
((OrgUser.OrgUserID) = (7))
1/COMMIT : auto
1/QueryOne: SELECT firstName, myOrgID FROM OrgUser WHERE
((OrgUser.OrgUserID) = (8))
1/QueryR : SELECT firstName, myOrgID FROM OrgUser WHERE
((OrgUser.OrgUserID) = (8))
1/COMMIT : auto
Have Org: Mobil, userslist=['john', 'steve', 'luke', 'mark'].
1/QueryAll: SELECT OrgUserID FROM OrgUser WHERE myOrgID = (3)
1/QueryR : SELECT OrgUserID FROM OrgUser WHERE myOrgID = (3)
1/COMMIT : auto
1/QueryOne: SELECT firstName, myOrgID FROM OrgUser WHERE
((OrgUser.OrgUserID) = (9))
1/QueryR : SELECT firstName, myOrgID FROM OrgUser WHERE
((OrgUser.OrgUserID) = (9))
1/COMMIT : auto
1/QueryOne: SELECT firstName, myOrgID FROM OrgUser WHERE
((OrgUser.OrgUserID) = (10))
1/QueryR : SELECT firstName, myOrgID FROM OrgUser WHERE
((OrgUser.OrgUserID) = (10))
1/COMMIT : auto
1/QueryOne: SELECT firstName, myOrgID FROM OrgUser WHERE
((OrgUser.OrgUserID) = (11))
1/QueryR : SELECT firstName, myOrgID FROM OrgUser WHERE
((OrgUser.OrgUserID) = (11))
1/COMMIT : auto
1/QueryOne: SELECT firstName, myOrgID FROM OrgUser WHERE
((OrgUser.OrgUserID) = (12))
1/QueryR : SELECT firstName, myOrgID FROM OrgUser WHERE
((OrgUser.OrgUserID) = (12))
1/COMMIT : auto
-- Kevin
___________________________________
Kevin J. Rice
Senior Software Engineer, Textura LLC
51-K Sherwood Terrace, Lake Bluff IL
(847) 235-8437 (spells VISAFLUIDS)
(847) 845-7423 (845-RICE, cellphone)
___________________________________
-------------------------------------------------------------------------
This SF.net email is sponsored by: Splunk Inc.
Still grepping through log files to find problems? Stop.
Now Search log events and configuration files using AJAX and a browser.
Download your FREE copy of Splunk now >> http://get.splunk.com/
_______________________________________________
sqlobject-discuss mailing list
sqlobject-discuss@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss
