Re: [Sqlalchemy-users] Insane Difficult Modeling Question (Seems Easy)

Fri, 16 Jun 2006 09:39:13 -0700

Haha! I did exactly that this day! And it is indeed VERY cool.

I had the roof house thing working and now I am trying to implement my first question. It works, but I keep bumping into the same problem when I try to link to two tables within a polymorphic mapped class.

I think this is a bug. I included ready to roll python files.

If this is going to work, I'll be so happy!

from sqlalchemy import *

metadata = BoundMetaData('sqlite://', echo='true')

order = Table('orders', metadata, 
	Column('id', Integer, primary_key=True),
	Column('employee_id', Integer, ForeignKey('employees.id'), nullable=False),
	Column('type', Unicode(16)))
	
buyorder = Table('buyorders', metadata, 
	Column('order_id', Integer, ForeignKey('orders.id'), primary_key=True),
	Column('from_supplier_id', Integer, ForeignKey('suppliers.id'), nullable=False),
	Column('to_stock_id', Integer, ForeignKey('stocks.id'), nullable=False))
	
sellorder = Table('sellorders', metadata, 
	Column('order_id', Integer, ForeignKey('orders.id'), primary_key=True))

internorder = Table('internorders', metadata, 
	Column('order_id', Integer, ForeignKey('orders.id'), primary_key=True),
	Column('from_stock_id', Integer, ForeignKey('stocks.id'), nullable=False),
	Column('to_stock_id', Integer, ForeignKey('stocks.id'), nullable=False))

employee = Table('employees', metadata,
	Column('id', Integer, primary_key=True),
	Column('name', Unicode(16), unique=True, nullable=False))

location = Table('locations', metadata,
	Column('id', Integer, primary_key=True),
	Column('name', Unicode(255), nullable=False))

stock = Table('stocks', metadata,
	Column('id', Integer, primary_key=True),
	Column('location_id', Integer, ForeignKey("locations.id"), nullable=False),
	Column('name', Unicode(255), nullable=False),
	Column('sellable', Boolean, nullable=False))

supplier = Table('suppliers', metadata,
	Column('id', Integer, primary_key=True),
	Column('name', Unicode(255), nullable=False))
	
metadata.drop_all()
metadata.create_all()

# The order object and it's polymorph classes
class Order(object):
	def __init__(self, **kwargs):
		for key, value in kwargs.iteritems():
			setattr(self, key, value)
	def __repr__(self):
		return "<Order id=%s>" % self.id

class BuyOrder(Order):
	def __repr__(self):
		return "<BuyOrder id=%s>" % self.id

class SellOrder(Order):
	def __repr__(self):
		return "<SellOrder id=%s>" % self.id

class InternOrder(Order):
	def __repr__(self):
		return "<InternOrder id=%s>" % self.id

class Employee(object):
	def __init__(self, **kwargs):
		for key, value in kwargs.iteritems():
			setattr(self, key, value)
	def __repr__(self):
		return "<Employee id=%s>" % self.id
		
class Supplier(object):
	def __init__(self, **kwargs):
		for key, value in kwargs.iteritems():
			setattr(self, key, value)
	def __repr__(self):
		return "<Supplier id=%s>" % self.id

class Location(object):
	def __init__(self, **kwargs):
		for key, value in kwargs.iteritems():
			setattr(self, key, value)
	def __repr__(self):
		return "<Location id=%s>" % self.id

class Stock(object):
	def __init__(self, **kwargs):
		for key, value in kwargs.iteritems():
			setattr(self, key, value)
	def __repr__(self):
		return "<Stock id=%s>" % self.id
		
# create a union that represents both types of joins.  
order_join = polymorphic_union(
	{
		'buyorder':order.join(buyorder),
		'sellorder':order.join(sellorder),
		'internorder':order.join(internorder),
		'order':order.select(order.c.type=='order'),
	}, None, 'pjoin')

order_mapper = mapper(Order, order, 
	select_table=order_join, 
	polymorphic_on=order_join.c.type, 
	polymorphic_identity='order')

mapper(BuyOrder, buyorder, 
	inherits=order_mapper, 
	polymorphic_identity='buyorder',
	properties={
		'from_supplier': relation(Supplier, lazy=True),
		'to_stock': relation(Stock, lazy=True)})
		
mapper(SellOrder, sellorder, 
	inherits=order_mapper, 
	polymorphic_identity='sellorder')

mapper(InternOrder, internorder, 
	inherits=order_mapper, 
	polymorphic_identity='internorder',
	properties={
		'from_stock': relation(Stock, lazy=True, primaryjoin=internorder.c.from_stock_id==stock.c.id),
		'to_stock': relation(Stock, lazy=True, primaryjoin=internorder.c.to_stock_id==stock.c.id)})

mapper(Employee, employee,
	properties={
		'orders': relation(Order, lazy=True, backref='employee')})

mapper(Supplier, supplier)

mapper(Stock, stock)

mapper(Location, location,
	properties={
		'stocks': relation(Stock, lazy=True, backref='location')})

session = create_session()

e1 = Employee(name='Koen Bok')

#for item in range(25):
session.save(BuyOrder(
	employee=e1, 
	from_supplier=Supplier(name='Apple'), 
	to_stock=Stock(location=Location(name='Amsterdam'), name='Main', sellable=True)))

session.save(InternOrder(
	employee=e1, 
	to_stock=Stock(location=Location(name='Amsterdam'), name='Main', sellable=True)))

session.flush()

session2 = create_session()
print session2.query(Order).select()

from sqlalchemy import *

metadata = BoundMetaData('sqlite://', echo='true')

order = Table('orders', metadata, 
	Column('id', Integer, primary_key=True),
	Column('employee_id', Integer, ForeignKey('employees.id'), nullable=False),
	Column('type', Unicode(16)))
	
buyorder = Table('buyorders', metadata, 
	Column('order_id', Integer, ForeignKey('orders.id'), primary_key=True),
	Column('from_supplier_id', Integer, ForeignKey('suppliers.id'), nullable=False),
	Column('to_stock_id', Integer, ForeignKey('stocks.id'), nullable=False))
	
sellorder = Table('sellorders', metadata, 
	Column('order_id', Integer, ForeignKey('orders.id'), primary_key=True))

internorder = Table('internorders', metadata, 
	Column('order_id', Integer, ForeignKey('orders.id'), primary_key=True),
	Column('to_stock_id', Integer, ForeignKey('stocks.id'), nullable=False))

employee = Table('employees', metadata,
	Column('id', Integer, primary_key=True),
	Column('name', Unicode(16), unique=True, nullable=False))

location = Table('locations', metadata,
	Column('id', Integer, primary_key=True),
	Column('name', Unicode(255), nullable=False))

stock = Table('stocks', metadata,
	Column('id', Integer, primary_key=True),
	Column('location_id', Integer, ForeignKey("locations.id"), nullable=False),
	Column('name', Unicode(255), nullable=False),
	Column('sellable', Boolean, nullable=False))

supplier = Table('suppliers', metadata,
	Column('id', Integer, primary_key=True),
	Column('name', Unicode(255), nullable=False))
	
metadata.drop_all()
metadata.create_all()

# The order object and it's polymorph classes
class Order(object):
	def __init__(self, **kwargs):
		for key, value in kwargs.iteritems():
			setattr(self, key, value)
	def __repr__(self):
		return "<Order id=%s>" % self.id

class BuyOrder(Order):
	def __repr__(self):
		return "<BuyOrder id=%s>" % self.id

class SellOrder(Order):
	def __repr__(self):
		return "<SellOrder id=%s>" % self.id

class InternOrder(Order):
	def __repr__(self):
		return "<InternOrder id=%s>" % self.id

class Employee(object):
	def __init__(self, **kwargs):
		for key, value in kwargs.iteritems():
			setattr(self, key, value)
	def __repr__(self):
		return "<Employee id=%s>" % self.id
		
class Supplier(object):
	def __init__(self, **kwargs):
		for key, value in kwargs.iteritems():
			setattr(self, key, value)
	def __repr__(self):
		return "<Supplier id=%s>" % self.id

class Location(object):
	def __init__(self, **kwargs):
		for key, value in kwargs.iteritems():
			setattr(self, key, value)
	def __repr__(self):
		return "<Location id=%s>" % self.id

class Stock(object):
	def __init__(self, **kwargs):
		for key, value in kwargs.iteritems():
			setattr(self, key, value)
	def __repr__(self):
		return "<Stock id=%s>" % self.id
		
# create a union that represents both types of joins.  
order_join = polymorphic_union(
	{
		'buyorder':order.join(buyorder),
		'sellorder':order.join(sellorder),
		'internorder':order.join(internorder),
		'order':order.select(order.c.type=='order'),
	}, None, 'pjoin')

order_mapper = mapper(Order, order, 
	select_table=order_join, 
	polymorphic_on=order_join.c.type, 
	polymorphic_identity='order')

mapper(BuyOrder, buyorder, 
	inherits=order_mapper, 
	polymorphic_identity='buyorder',
	properties={
		'from_supplier': relation(Supplier, lazy=True),
		'to_stock': relation(Stock, lazy=True)})
		
mapper(SellOrder, sellorder, 
	inherits=order_mapper, 
	polymorphic_identity='sellorder')

mapper(InternOrder, internorder, 
	inherits=order_mapper, 
	polymorphic_identity='internorder',
	properties={
		'to_stock': relation(Stock, lazy=True, primaryjoin=internorder.c.to_stock_id==stock.c.id)})

mapper(Employee, employee,
	properties={
		'orders': relation(Order, lazy=True, backref='employee')})

mapper(Supplier, supplier)

mapper(Stock, stock)

mapper(Location, location,
	properties={
		'stocks': relation(Stock, lazy=True, backref='location')})

session = create_session()

e1 = Employee(name='Koen Bok')

#for item in range(25):
session.save(BuyOrder(
	employee=e1, 
	from_supplier=Supplier(name='Apple'), 
	to_stock=Stock(location=Location(name='Amsterdam'), name='Main', sellable=True)))

session.save(InternOrder(
	employee=e1, 
	to_stock=Stock(location=Location(name='Amsterdam'), name='Main', sellable=True)))

session.flush()

session2 = create_session()
print session2.query(Order).select()


On 16-jun-2006, at 18:28, Michael Bayer wrote:

this assumes each House will point to a unique Roof object (i.e., its a one-to-one relationship to a Roof).  also if you use the latest trunk (or 2.3 when i release it this weekend) you can declare the mappers in any order.

houses
----------
house_id  integer pk
name       varchar

roofs
-------
roof_id integer pk
house_id integer references houses(house_id)    -- or put roof_id on the houses table, same diff
name varchar
type  varchar

concrete_roofs
--------------------
roof_id integer pk references roofs(roof_id)
hardness varchar

wood_roofs
----------------
roof_id integer pk references roofs(roof_id)
treename varchar

metadata = BoundMetaData('some://db')

(houses, roofs, concete_roofs, wood_roofs) = [Table(name, metadata, autoload=True) for name in ['houses', 'roofs', 'concrete_roofs', 'wood_roofs']]

class House(object):pass
class Roof(object):pass
class ConcreteRoof(Roof):pass
class WoodRoof(Roof):pass

mapper(House, houses, properties={
     'roof':relation(Roof, uselist=False, cascade="all, delete-orphan", backref=backref("house"))
})

roof_union = polymorphic_union(
   {
      'concrete':concrete_roofs.join(roofs),
     'wood':wood_roofs.join(roofs),
   },
    None, 'roof_union'
)
mapper(Roof, roofs, select_table=roof_union, polymorphic_on=roofs.c.type)
mapper(ConcreteRoof, concrete_roofs, inherits=Roof, polymorphic_identity='concrete')
mapper(WoodRoof, wood_roofs, inherits=Roof, polymorphic_identity='wood')


...and if that all works (which it should.....) then we really have something cool going on here...


On Jun 16, 2006, at 7:43 AM, Koen Bok wrote:

Maybe a little metaphor to make it more simple.

Let's say I make houses. The roof of the house can either be concrete or wood. Concrete and wood come in many types.

Design:

House
id
name

Wood
id
name
treename

Concrete
id
name
hardness

Now if I have a House object it should have a roof relation which either links to a Wood object or a Concrete object. It NEEDS to be linked to one of them.

Like this:

>>> house = session.query(House).get(1)
>>> print house.roof
<Concrete id=123 name=SuperBeautiful hardness=500>

>>> house = session.query(House).get(2)
>>> print house.roof
< Wood id=321 name=Oak treename='Oak Tree'>


On 16-jun-2006, at 5:24, Michael Bayer wrote:

whats wrong with this ?

order_types
---------------
type_id
name
from_stock_id
to_stock_id

?

On Jun 15, 2006, at 6:44 PM, Koen Bok wrote:

Within my organisation we have orders. Orders move products between three entities: Suppliers, Stocks and Customers.

The type of an order gets defined by the origin and the destination it is moving products. This way we can have the following types of orders:

From: To: -> Stock BUYORDER
Stock -> Supplier RETOURBUYORDER
Stock -> Stock INTERNORDER
Stock -> Customer SELLORDER
Customer ! -> Stock RETOURSELLORDER

How can I design objects/mappers to do this:

>>> order = session.query(Order).get(1)
>>> print order.type
<Type id=123 name='INTERNORDER' from=<Stock id=2> to=<Stock id=4>>

>>> order = session.query(Order).get(2)
>>> print order.type
<Type id=456 name='SELLORDER' from=<Stock id=4> to=<Customer id=1928>>
_______________________________________________
Sqlalchemy-users mailing list



_______________________________________________
Sqlalchemy-users mailing list

Attachment: smime.p7s
Description: S/MIME cryptographic signature

_______________________________________________
Sqlalchemy-users mailing list
Sqlalchemy-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/sqlalchemy-users






















					Reply via email to