I'm building a rather involved hierarchy of objects for a project of
mine. One of the situations (actually lots of them, but one has the
problem) has a 2-level inheritance situation, like this:
Node
Config
TelephoneNumberConfig
Inventory
TelephoneNumberInventory
where a TelephoneNumberConfig has a ForeignKey reference to a
TelephoneNumberInventory instance. For convenience I define a
SingleJoin on the TelephoneNumberInventory named "assigned", which does
a select, like so:
assigned = SingleJoin(
"TelephoneNumberConfig", joinColumn='inventory_id',
)
when I trace the SQL this is creating (inside joins.py, line 299 or so,
where it counts to see how many results there are), I see a missing
clause in the SQL, namely the clause that limits the node table to those
records selected with the rest of the query. That is, the following is
what is produced:
SELECT COUNT(*) FROM node, config, telephone_number_config WHERE
((((telephone_number_config.inventory_id) = (51)) AND
((config.child_name) = ('TelephoneNumberConfig'))) AND
((telephone_number_config.id) = (config.id)));
whereas it should be:
SELECT COUNT(*) FROM node, config, telephone_number_config WHERE
((((telephone_number_config.inventory_id) = (51)) AND
((config.child_name) = ('TelephoneNumberConfig'))) AND
((telephone_number_config.id) = (config.id))) AND config.id = node.id;
The result is that the grandparent table (Node) selects *all* records
for each result-row otherwise selected. I haven't been able to track
down through the machinery where the query goes awry. This is from a
TurboGears project created with quick-start running against PostgreSQL,
I've attached the model.py and test_model.py modules from that project.
Failure is seen in test_telephoneNumberAssignment, where all 8 Node
instances are returned, instead of just 1, resulting in the first item
being returned being of a different type than the TelephoneNumberConfig
class.
I haven't been able to isolate the problem into a simpler object-set
yet, I'm hoping that someone will look at the problem and just go "ah,
of course". It looks like it's just missing a recursion to walk up the
hierarchy to include all ancestors in the set of ANDed conditions. The
query obviously knows about the node table, it's just not adding the AND
for it.
Thanks for any pointers,
Mike
--
________________________________________________
Mike C. Fletcher
Designer, VR Plumber, Coder
http://www.vrplumber.com
http://blog.vrplumber.com
from datetime import datetime
from sqlobject import *
from sqlobject.inheritance import InheritableSQLObject
from turbogears import identity
from turbogears.database import PackageHub
hub = PackageHub("betterbill")
__connection__ = hub
# class YourDataClass(SQLObject):
# pass
(
BILLING_MONTHLY,
BILLING_YEARLY,
BILLING_MONTHLY_ANNIVERSARY,
BILLING_YEARLY_ANNIVERSARY,
) = ('monthly','yearly','monthly-a','yearly-a')
class Node( InheritableSQLObject ):
"""Root of the object hierarchy, a hierarchic node"""
owner = ForeignKey(
'User',
notNone = True,
)
def byOwner( cls, owner, **named ):
"""Retrieve all instances of the class by owner"""
return cls.select(
Node.q.ownerID == owner.id, **named
)
byOwner = classmethod( byOwner )
def children( self, ofType=None ):
"""Retrieve children of this node of given type"""
if ofType is not None:
clause = AND(
Dependency.q.parentID == self.id,
Dependency.q.type == ofType,
)
else:
clause = Dependency.q.parentID == self.id
return [
dependency.child
for dependency in Dependency.select(
clause,
)
]
class TypeNode( Node ):
"""Nodes which define types of other nodes"""
name = UnicodeCol(
length = 64, alternateID=True, notNone=True,
)
description = UnicodeCol(
notNone=True, default=u"",
)
class BillingType( TypeNode ):
"""Nodes which define a particular type of billing process"""
class ConfigType( TypeNode ):
"""Nodes which define a particular type of configuration set
Defines the various settings such as what templates and controllers
allow for setup, also controls the data-values which are allowed.
"""
class PackageType( TypeNode ):
"""Definition of parameters for a type of Package (purchased thing)"""
billingType = ForeignKey(
"BillingType",
)
# icons, html descriptions, html summaries
class EffectType( TypeNode ):
"""Definition of parameters for a type of Effect (technical effect)
enableFunction -- function to enable the technical effect
disableFunction -- function to disable the technical effect
viewFunction -- function to provide structured view of back-end settings
checkFunction -- function to compare viewFunction settings to expected and
report any errors detected
"""
enableFunction = UnicodeCol(
length = 256, alternateID=True, notNone=True,
)
disableFunction = UnicodeCol(
length = 256, alternateID=True, notNone=True,
)
viewFunction = UnicodeCol(
length = 256, alternateID=True, notNone=True,
)
checkFunction = UnicodeCol(
length = 256, alternateID=True, notNone=True,
)
class Package( Node ):
"""Billable recurring package of things purchased by the user"""
type = ForeignKey(
'PackageType',
notNone = True,
)
def state( self ):
"""Retrieve last state for a state-bearing node as a simple string"""
for item in StateTrace.select(
StateTrace.node==self.node_id,
orderBy=StateTrace.changeDate,
).reversed()[:1]:
return item.endState.name
return None
class Effect( Node ):
"""Technical effect created by the system"""
type = ForeignKey(
'EffectType',
notNone = True,
)
class Config( Node ):
"""Non-billable configuration/settings which control effects
Configs are attached to packages and have effects attached to them,
note that effects may attach to multiple configurations
"""
type = ForeignKey(
'ConfigType',
notNone = True,
)
class Inventory( Node ):
"""Object in inventory which may be sold/loaned to customer"""
class Dependency( SQLObject ):
"""Dependency from one Node to another"""
parent = ForeignKey(
'Node',
notNone = True,
)
child = ForeignKey(
'Node',
notNone = True,
)
type = EnumCol(
default = 'contains',
enumValues = [
'required-by',
'contains',
'part-of',
],
notNone = True,
)
class State( InheritableSQLObject ):
"""Base class for descriptions of states into which nodes can get"""
name = UnicodeCol(
length = 64, alternateID=True, notNone=True,
)
description = UnicodeCol(
notNone=True, default=u"",
)
leavePermission = ForeignKey(
# permission required to leave this state...
"Permission",
)
enterPermission = ForeignKey(
# permission required to enter this state...
"Permission",
)
leaveFunction = UnicodeCol(
# function to call whenever a package leaves this state
length = 256, alternateID=True,
)
enterFunction = UnicodeCol(
# function to call whenever a package enters this state
length = 256, alternateID=True,
)
class InventoryState( State ):
"""Descriptions of states in which inventory may be placed"""
class PackageState( State ):
"""Descriptions of states in which a package may be placed"""
billable = BoolCol(
default=True,notNone=True,
)
class StateTrace( SQLObject ):
"""Trace of state-changes for a given package (billing audit trail)"""
node = ForeignKey(
# the node changing state...
"Node", notNone=True,
)
startState = ForeignKey(
'State',
)
endState = ForeignKey(
'State', notNone=True,
)
actor = ForeignKey(
"User", notNone=True,
)
changeDate = DateTimeCol(
default=datetime.now , notNone=True,
)
description = UnicodeCol(
notNone=False,
)
# Now the actual application objects...
class ATAInventory( Inventory ):
"""ATA to be assigned/sold/rented to the customer"""
esn = StringCol(
length=12, alternateID=True,
notNone = True,
)
mac = StringCol(
length=12, alternateID=True,
notNone = True,
)
class TelephoneNumberInventory( Inventory ):
"""Telephone number in inventory for assignment to a customer"""
number = StringCol(
length=20, alternateID=True,
notNone = True,
)
assigned = SingleJoin(
"TelephoneNumberConfig", joinColumn='inventory_id',
)
class TelephoneNumberConfig( Config ):
"""Configuration required to provision a telephone number"""
inventory = ForeignKey(
'TelephoneNumberInventory',
default=None,
notNone = False,
)
class VoicemailConfig( Config ):
"""Configuration required to provision voicemail"""
password = StringCol(
length=6, alternateID=False,
)
class AccountConfig( Config ):
"""Configuration required to provision an account w/ access
Requires:
Identity Setup
Caller ID Setup
Diversion Setup
Access Rules Setup
"""
# Turbogears generic username/group framework follows...
class VisitIdentity(SQLObject):
visit_key = StringCol( length=40, alternateID=True,
alternateMethodName="by_visit_key" )
user_id = IntCol()
class Group(SQLObject):
"""
An ultra-simple group definition.
"""
# names like "Group", "Order" and "User" are reserved words in SQL
# so we set the name to something safe for SQL
class sqlmeta:
table="tg_group"
group_name = UnicodeCol( length=16, alternateID=True,
alternateMethodName="by_group_name" )
display_name = UnicodeCol( length=255 )
created = DateTimeCol( default=datetime.now )
# collection of all users belonging to this group
users = RelatedJoin( "User", intermediateTable="user_group",
joinColumn="group_id", otherColumn="user_id" )
# collection of all permissions for this group
permissions = RelatedJoin( "Permission", joinColumn="group_id",
intermediateTable="group_permission",
otherColumn="permission_id" )
class User(SQLObject):
"""
Reasonably basic User definition. Probably would want additional attributes.
"""
# names like "Group", "Order" and "User" are reserved words in SQL
# so we set the name to something safe for SQL
class sqlmeta:
table="tg_user"
user_name = UnicodeCol( length=16, alternateID=True,
alternateMethodName="by_user_name" )
email_address = UnicodeCol( length=255, alternateID=True,
alternateMethodName="by_email_address" )
display_name = UnicodeCol( length=255 )
password = UnicodeCol( length=40 )
created = DateTimeCol( default=datetime.now )
# groups this user belongs to
groups = RelatedJoin( "Group", intermediateTable="user_group",
joinColumn="user_id", otherColumn="group_id" )
packages = RelatedJoin(
"Package",
joinColumn="owner_id",
intermediateTable="node",
)
def _get_permissions( self ):
perms = set()
for g in self.groups:
perms = perms | set(g.permissions)
return perms
def _set_password( self, cleartext_password ):
"Runs cleartext_password through the hash algorithm before saving."
hash = identity.encrypt_password(cleartext_password)
self._SO_set_password(hash)
def set_password_raw( self, password ):
"Saves the password as-is to the database."
self._SO_set_password(password)
class Permission(SQLObject):
permission_name = UnicodeCol( length=32, alternateID=True,
alternateMethodName="by_permission_name" )
description = UnicodeCol( length=255 )
groups = RelatedJoin( "Group",
intermediateTable="group_permission",
joinColumn="permission_id",
otherColumn="group_id" )
class TNode( InheritableSQLObject ):
"""Root of the object hierarchy, a hierarchic node"""
class TConfig( TNode ):
"""Non-billable configuration/settings which control effects
Configs are attached to packages and have effects attached to them,
note that effects may attach to multiple configurations
"""
class TInventory( TNode ):
"""Inventory hierarchy base"""
class TTelephoneNumberInventory( TInventory ):
"""inventory to be assed to a telephone number config"""
assigned = SingleJoin(
"TTelephoneNumberConfig", joinColumn='inventory_id',
)
class TTelephoneNumberConfig( TConfig ):
"""Configuration required to provision a telephone number"""
inventory = ForeignKey( "TTelephoneNumberInventory", default=None )
"""Testing code for the model methods"""
import pkg_resources
pkg_resources.require("TurboGears")
import turbogears
turbogears.update_config(
configfile="dev.cfg",
modulename="betterbill.config"
)
import unittest
from betterbill.model import *
#testutil.database.set_db_uri("sqlite:///:memory:")
class TestPackageSetup(unittest.TestCase):
"""Tests the operations required to setup and manipulate packages"""
def setUp( self ):
"""Start a transaction for our changes"""
hub.begin()
def tearDown( self ):
"""Rollback all changes to the database"""
hub.rollback()
def test_usersGroups(self):
"""Are we able to create users/groups"""
u=User(
user_name="mcfletch",
email_address="[EMAIL PROTECTED]",
display_name="Mike Fletcher",
password="testing"
)
groups=[
Group( group_name="admin", display_name="Administrators" ),
Group( group_name="user", display_name="Users" ),
]
for g in groups:
u.addGroup( g )
return u
def test_billingType( self ):
"""Are we able to create a billing type?"""
u = self.test_usersGroups()
bt = BillingType(
owner=u,
name="Monthly Pro-rated",
description="""Start-of-month pro-rated-start with no discontinuation pro-rating""",
)
return u,bt
def test_serviceGroup( self ):
"""Can we create a service group package type and instance?"""
u,bt = self.test_billingType()
pt = PackageType(
owner = u,
name="VoIP Service",
description="""Base VoIP service provides core VoIP routing""",
billingTypeID=bt.id,
)
account = Package(
ownerID=u.id,
typeID=pt.id,
)
return u,bt,pt,account
def test_telephoneNumberDependency( self ):
"""Can we create a phone number with a dependency on the service group?
Test that we can get account's "children" and they include the phone
number.
"""
u,bt,pt,account = self.test_serviceGroup()
didType = PackageType(
owner = u,
name="Telephone Number",
description="""Telephone number, provides inbound and outbound phone calls (PSTN Access)""",
billingTypeID=bt.id,
)
did = Package(
ownerID=u.id,
typeID=didType.id,
)
# now create a dependency from the did to the account
dep = Dependency(
parent=account,
child = did,
type="contains",
)
assert account.children()[0] == did
configType = ConfigType(
owner=u,
name="Telephone Config",
description="""Configures your Telephone number (DID) settings""",
)
conf = TelephoneNumberConfig(
owner=u,
typeID=configType.id,
)
dep = Dependency(
parent=did,
child = conf,
type="part-of",
)
assert did.children()[0] == conf
return u,bt,didType,account,did, conf
def test_telephoneNumberInventory( self ):
"""Can we create a telephone number in inventory?"""
u = self.test_usersGroups()
t = TelephoneNumberInventory(
owner=u,
number='14165550000',
)
assert TelephoneNumberInventory.byNumber( '14165550000' ) == t
return t
def test_telephoneNumberAssignment( self ):
"""Can we assign inventory telephone number to the purchased number"""
u,bt,didType,account,did, conf = self.test_telephoneNumberDependency()
t = TelephoneNumberInventory(
owner=u,
number='14165550000',
)
assert TelephoneNumberInventory.byNumber( '14165550000' ) == t
conf.inventory = t
print 'assigned inventory', conf.inventory
assigned = t.assigned
print 't assigned', assigned
print 'conf', conf
assert t.assigned == conf
def test_userPackages( self ):
u,bt,didType,account,did, conf = self.test_telephoneNumberDependency()
packages = list(Package.select(
Node.q.ownerID == u.id
))
assert len(packages) == 2
assert account in packages
assert did in packages
## def test_selectGrandchild( self ):
## """Test bug with selection of grandchildren"""
## config = TTelephoneNumberConfig()
## inventory = TTelephoneNumberInventory()
## other = TTelephoneNumberInventory()
## other2 = TNode()
## other3 = TConfig()
## config.inventory = inventory
## items = list(inventory.assigned)
## assert len(items) == 1, items
##
if __name__ == "__main__":
unittest.main()
Using Tomcat but need to do more? Need to support web services, security?
Get stuff done quickly with pre-integrated technology to make your job easier
Download IBM WebSphere Application Server v.1.0.1 based on Apache Geronimo
http://sel.as-us.falkag.net/sel?cmd=lnk&kid=120709&bid=263057&dat=121642
_______________________________________________
sqlobject-discuss mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss
