On Apr 14, 2010, at 7:51 PM, [email protected] wrote:
> 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'
would need to see the strucure of completiontb, connecttb, etc. to work out the
above error as well as what triggers that error. You do need to say
"concrete=True" in an inheriting mapper which you'd like to be concrete versus
its parent.
>
> 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.
--
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.
