Inheritance + Foreign Keys

Question

I have been absolutely racking my brain over this, and can't seem to work out how to get around the issue. Please note that I have cut alot of irrelevant fields out of my models

I am in the middle of coding up my SQL-Alchemy models, and have encountered the following issue:

Due to multiple billing systems, each with completely different attributes, and due to the different types of Subscription attributes (i.e. usernames, node locations, etc), I have had to go down the polymorphic inheritance road.

class Subscription(Base):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(255))
    secret = db.Column(postgresql.BYTEA)
    type = db.Column(SubscriptionType.db_type())
    status = db.Column(StatusType.db_type())
    subscription_id = db.Column(db.Integer)

    __tablename__ = 'subscription'
    __table_args__ = {'schema':'public'}

    __mapper_args__ = {'polymorphic_on' : type}

    def __repr__(self):
        return '<Subscription: %r>' % self.id

class Product1(Subscription):
    __mapper_args__ = {'polymorphic_identity' : SubscriptionType.product1}    
    id = db.Column(db.Integer, db.ForeignKey('public.subscription.id'), 
        primary_key = True)
    billing_system = db.Column(
        db.Integer, 
        db.ForeignKey('public.billing_system.id')
    )

class BillingSystem(Base):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(255))
    type = db.Column(BillingSystemType.db_type())

    __tablename__ = 'billing_system'
    __table_args__ = {'schema':'public'}

    __mapper_args__ = {'polymorphic_on' : type}

    def __repr__(self):
        return '<Subscription: %r>' % self.id

class BillingSystem1(BillingSystem):
    __mapper_args__ = {'polymorphic_identity' : BillingSystemType.billingsystem1}    
    id = db.Column(db.Integer, db.ForeignKey('public.billing_system.id'), 
        primary_key = True)
    billing_system = db.Column(
        db.Integer, 
        db.ForeignKey('public.billing_system.id')
    )
    foo = db.Column(db.Integer)
    bar = db.Column(db.Integer)

 class BillingSystem2(BillingSystem):
    __mapper_args__ = {'polymorphic_identity' : BillingSystemType.billingsystem2}    
    id = db.Column(db.Integer, db.ForeignKey('public.billing_system.id'), 
        primary_key = True)
    billing_system = db.Column(
        db.Integer, 
        db.ForeignKey('public.billing_system.id')
    )
    bing = db.Column(db.Integer)
    boo = db.Column(db.Integer)


    __tablename__ = 'billing_system_product2'
    __table_args__ = {'schema':'public'}

It is all working fine, except for one thing.

Say that I run the following:

>>> a = Product1()
>>> b = BillingSystem.objects.get(1)
>>> a.billing_system = b
>>> session.add(a)
>>> session.commit()

I will receive the following error.

sqlalchemy.exc.ProgrammingError: (ProgrammingError) can't adapt type 'BillingSystem1' 'INSERT INTO

I understand what it is saying, and I have tried the following.

>>> a.billing_system = b.id

This only stores the ID, and when I try to retreive the associated object, I instead receive an integer. This involves me having to do an additional query. I would expect that this isn't the way to go.

I have also tried adding foreign keys for all of the billing system IDs to the Product1 Model

class BillingSystem1(BillingSystem):
    __mapper_args__ = {'polymorphic_identity' : BillingSystemType.billingsystem1}    
    id = db.Column(db.Integer, db.ForeignKey('public.billing_system.id'), 
        primary_key = True)
    billing_system = db.Column(
        db.Integer, 
        db.ForeignKey('public.billing_system.id'),
        db.ForeignKey('public.billing_system1.id'),
        db.ForeignKey('public.billing_system2.id'),
    )
    foo = db.Column(db.Integer)
    bar = db.Column(db.Integer)

This also didn't work at all, and I received the same ProgrammingError exception stating that the type can't adapt.

I have trawled the manual, and can't find how to do it, but I need some form of magical option to allow this to happen:

>>> a = BillingSystem.query.get(1)
>>> type(a)
BillingSystem

Instead of:

>>> a = BillingSystem.query.get(1)
>>> type(a)
BillingSystem1

Is anyone able to shed any light on how I can query my set of Polymorphic models for an ID, and only obtain the class of the base model?

I feel that this will solve my problem, im just not sure of how to work it out.

Thanks for taking the time to read this, and I would really like to find out where I am going wrong (I have also been awake way too long, so that isn't helping).

Cheers, Rhys

Answer 1

class Subscription(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(255))
    secret = db.Column(postgresql.BYTEA)
    type = db.Column(SubscriptionType.db_type())
    status = db.Column(StatusType.db_type())
    subscription_id = db.Column(db.Integer)

    billing_system_id = db.Column(
        db.Integer, 
        db.ForeignKey('public.billing_system.id')
    )
    billing_system = db.relationship('BillingSystem', backref='subscriptions')

So what I have done is:

1 ) Shifted the Subscription.billing_system_id Foreign Key up to the Base Subscription Model 2 ) Added in the Subscription.billing_system Relationship

So now I am doing this:

>>> o = BillingSystem.query.get(1)
>>> a = Product1()
>>> a.billing_system_id = o.id

Which results in:

>>> a.billing_system.subscriptions
[<cPanel Hosting Reseller: 2>]
>>> a.billing_system_id
2

So unless I am doing something wrong here, it seems to work. I just can't pass the actual BillingSystem object, I actually have to set the ID. It is still referencially enforced when the model is saved though, so I don't see too many issues with it.

Thanks for your help :)