I've got a legacy database that I need to work through the following
scenario with.
There are four types of objects, completions, meters, tanks and equipment.
Each has its own table. There is no "master" table representing all the
entities. There is a connection table that describes the relationship
between the entities. Each has a numeric identifier and the four types
are distinguished in the connection table by another numeric id, between 1
and 4.
In the implementation presented, I am trying to create a network node
abstract object with each of the four types inheriting from this class
(NetworkNode). What I really want is to be able to query from an apex
node to its upstream objects and get a class of the specific type. So if
what I'm doing is going about it in the wrong way, please set me straight!
The problem I've run into is the following error. Since there is no base
table to define foreign keys on, and I'm not sure if I should define
properties of each object as relations to the connection table, I'm not
sure where to go with this. Any ideas?
Can't find any foreign key relationships between 'productionentities' and
'completiontb'
Here's the code (with some names changed and anything pertaining only to
tanks, meters or equipment left out). I apologize for the lengthy
message. I'm not sure that I can condense this issue any further.
from sqlalchemy import *
from sqlalchemy import sql
from sqlalchemy import orm
from pc.orm.tables import (tanktb, metertb, completiontb, equipmenttb,
connecttb)
from pc.orm.tables import metadata
# add the object types to this selectable to define an abstract master
table
# there's not much to know about the underlying tables except the columns
# listed here
cs = select([literal(1).label('mobjecttype'),
completiontb.c.mid.label('mobjectid'),
completiontb.c.wellpluscompletionname.label('objectname')])
ms = select([literal(2),metertb.c.mid,metertb.c.metername])
ts = select([literal(3),tanktb.c.mid,tanktb.c.tankname])
es = select([literal(4),equipmenttb.c.mid,equipmenttb.c.equipmentname])
s = union_all(cs,ms,ts,es).alias('productionentities')
# We'll call the abstract object a network node, since we are dealing with
# a network of objects
class NetworkNode(object):
pass
class Completion(NetworkNode):
pass
# Use an association object to represent the relationship between nodes
class Connection(object):
pass
connmapper = orm.mapper(Connection,connecttb)
# Define the upstream and downstream relationships on the abstract node
nnmapper = orm.mapper(NetworkNode,s,primary_key=[s.c.mobjecttype,
s.c.mobjectid],
polymorphic_on=s.c.mobjecttype,
properties=dict(
upstreamobjects=orm.relation(Connection,primaryjoin=
and_(connecttb.c.downstreamid==s.c.mobjectid,
connecttb.c.downstreamtype==s.c.mobjecttype)),
downstreamobjects=orm.relation(Connection,primaryjoin=
and_(connecttb.c.upstreamid==s.c.mobjectid,
connecttb.c.upstreamtype==s.c.mobjecttype))))
# the concrete types
cmapper = orm.mapper(Completion,completiontb,inherits=NetworkNode,
polymorphic_identity=1)
Thank you for the help!
Wes Dyk
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
