On Mar 29, 2012, at 11:09 AM, kevin c wrote: > I'm not sure if this is possible but this is my problem: We have 3 > 'user' tables that we're starting to centralize around a single > class. mmf_user, auth_user, and mmf_user_profile where auth_user and > mmf_user are very similar but need to be maintained because of a bad > upgrade path decision a while ago. > > Base = declarative_base() > class AuthUser(Base): > __tablename__ = 'auth_user' > #column definitions > id = Column(u'id', Integer, ForeignKey('user.user_id'), > primary_key=True, > nullable=False, autoincrement=True) > auth_username = Column(u'username', String(50), nullable=False) > auth_password = Column(u'password', String(50), nullable=False) > is_staff = Column(u'is_staff', SmallInteger, nullable=False) > is_active = Column(u'is_active', SmallInteger, nullable=False) > is_superuser = Column(u'is_superuser', SmallInteger, > nullable=False) > ..etc.. > > class MMFUser(Base): > __tablename__ = 'mmf_user' > #column definitions > id = Column(u'user_id', Integer,primary_key=True, > nullable=False, autoincrement=True) > username = Column(u'username', String(50), nullable=False) > password = Column(u'password', String(50), nullable=False) > gender = Column(u'sex', String(1), nullable=False) > .. etc.. > > class UserProfile(Base): > __tablename__ = 'mmf_user_profile' > #column definitions > id = Column(u'user_id', Integer, ForeignKey('user.user_id'), > primary_key=True, > nullable=False, autoincrement=True) > profile = Column(u'profile', Text, nullable=False) > ..etc.. > > > 1) How would I go about creating a super class of User() that > encompasses those 3 classes seamlessly > > 2) where User.username updates would update mmf_user.username as well > as auth_user.auth_username? > > 3) What about if mmf_user.username is the MD5() of the password while > auth_user.auth_password is a SHA1 salted version of it? > > Is this even possible or should we continue to maintain the 3 classes > separately and when we solve this underlying architecture problem, > make the code changes through our system?
There's ways to map to a join of three tables though this wouldn't necessarily perform spectacularly, normally here I'd have one class be the "lead" class and then have it maintain the other two based on state. If UserProfile is your lead class, you can maybe use relationship() to keep around the other two, then use events like @validates to update other attributes: from sqlalchemy.orm import validates class UserProfile(...): def __init__(self, ...): self.mmfuser = MMFUser(...) self.authuser = AuthUser(...) mmfuser = relationship("MMFUser", primaryjoin="UserProfile.username==MMFUser.username", foreign_keys="UserProfile.username", ) authuser = relationship("AuthUser", primaryjoin="AuthUser.username==UserProfile.username", foreign_keys="UserProfile.username") @validates('password') def update_password(self, key, value): self.mmfuser.password = MD5(value) self.authuser.auth_password = SHA1(value) return crypt(value) Above I made the relationship a many-to-one from UserProfile to the remote class, in fact I've never tried that pattern where UserProfile.username links out to two different relationships like that but I think it should work. Of course you can do one or both of the password/username synchronization it in SQL too using triggers, though the result of a trigger wouldn't be present in memory until you re-emitted a SELECT statement for the various other attributes. -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.