gistfile1.py

from datetime import date

from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base

Base= declarative_base()


pool_invites = Table(
    'pool_invite', Base.metadata,
    Column('pool_id', Integer, ForeignKey('pool.id'), primary_key=True),
    Column('human_id', Integer, ForeignKey('human.id'), primary_key=True)
)

class Human(Base):

    __tablename__ = 'human'

    id = Column(Integer, primary_key=True)
    login = Column(String(50))

    def __init__(self, login):
        self.login = login


class Form(Base):

    __tablename__ = 'form'

    id = Column(Integer, primary_key=True)
    title =  Column(String(50))
    owner_id = Column(Integer, ForeignKey('human.id'), nullable=False)

    owner = relationship(Human, innerjoin=True, backref='forms')

    def __init__(self, title, owner):
        self.title = title
        self.owner = owner


class Result(Base):

    __tablename__ = 'result'

    id = Column(Integer, primary_key=True)
    name =  Column(String(50))
    owner_id = Column(Integer, ForeignKey('human.id'), nullable=False)
    form_id = Column(Integer, ForeignKey('form.id'), nullable=False)

    owner = relationship(Human, innerjoin=True, backref='results')
    form = relationship(Form, innerjoin=True, backref='results')

    def __init__(self, name, owner, form):
        self.name = name
        self.owner = owner
        self.form = form


class Pool(Base):

    __tablename__ = 'pool'

    id = Column(Integer, primary_key=True)
    name =  Column(String(50))
    owner_id = Column(Integer, ForeignKey('human.id'))

    owner = relationship(Human, innerjoin=True, backref='pools')
    invited_users = relationship(Human, secondary=pool_invites,
                                 backref='groups')

    def __init__(self, name, owner):
        self.name = name
        self.owner = owner


class PoolInviteResult(Base):

    __tablename__ = 'pool_invite_result'
    __table_args__ = (
            ForeignKeyConstraint(
                ['pool_invite_pool_id', 'pool_invite_human_id'],
                ['pool_invite.pool_id', 'pool_invite.human_id']
            ),
        )

    pool_invite_pool_id = Column(Integer, primary_key=True)
    pool_invite_human_id = Column(Integer, primary_key=True)
    result_id = Column(Integer, ForeignKey('result.id'), primary_key=True)
    added = Column(Date)

    pool = relationship(
            Pool, secondary=pool_invites, uselist=False, innerjoin=True,
#            secondaryjoin=pool_invites.c.pool_id==Pool.id,
            backref='pir_assoc'
        )

    invited_user = relationship(
            Human, secondary=pool_invites, uselist=False, innerjoin=True,
#            secondaryjoin=pool_invites.c.human_id==Human.id,
            backref='pir_assoc'
        )

    result = relationship(Result, backref='pir_assoc')

    def __init__(self, pool, human, result, added=None):
        self.pool = pool
        self.invited_user = human
        self.result = result
        self.added = added


#e = create_engine("sqlite://", echo=True)
e = create_engine('postgresql://xxx:xxx@127.0.0.1/foo')

Base.metadata.create_all(e)

s = Session(e)

# Humans
julien = Human('Julien')
sonia = Human('Sonia')
mike = Human('Mike')
marie = Human('Marie')

s.add_all((julien, sonia, mike, marie))

# Forms
julien_form = Form('Julien form', julien)
sonia_form = Form('Sonia form', sonia)

s.add_all((julien_form, sonia_form))

# Results
julien_r1 = Result('Julien result 1 for Sonia form', julien, sonia_form)
julien_r2 = Result('Julien result 2 for Sonia form', julien, sonia_form)
julien_r3 = Result('Julien result 3 for Sonia form', julien, sonia_form)
sonia_r1 = Result('Sonia result 1 for Sonia form', sonia, sonia_form)
sonia_r2 = Result('Sonia result 1 for Julien form', sonia, julien_form)

s.add_all((julien_r1, julien_r2, julien_r3, sonia_r1, sonia_r2))

# Pools
mike_pool = Pool('Mike pool', mike)
sonia_pool = Pool('Sonia pool', sonia)

s.add_all((mike_pool, sonia_pool))

# Operations on relationships

# Mike decides to invite Julien, Sonia, and Marie to submit result(s) for his 
# pool
mike_pool.invited_users.append(julien)
mike_pool.invited_users.append(sonia)
mike_pool.invited_users.append(marie)

s.commit()

# Julien decides to submit two of his results to mike's pool

today = date.today()

# XXX: don't work
julien_submit_1 = PoolInviteResult(mike_pool, julien, julien_r1, today)
julien_submit_2 = PoolInviteResult(mike_pool, julien, julien_r2, today)

s.add_all((julien_submit_1, julien_submit_2))

# Later, Mike decided to remove Julien and Sonia from his pool:

# Should work
mike_pool.invited_users.remove(sonia)

# The problematic part: rows must be deleted from pool_invite _and_
# pool_invite_result .. 
mike_pool.invited_users.remove(julien)

s.commit()