On Tue, Oct 25, 2016 at 10:46 PM, Robert C
<robertchristopherde...@gmail.com> wrote:
> I am modeling a simple hierarchical database structure. My model is designed
> as follows:
>
> class ChatMessage(Base):
> __tablename__ = 'chat_message'
> sender_id = Column(Integer, ForeignKey('user.id'), primary_key=True)
> receiver_id = Column(Integer, ForeignKey('user.id'), primary_key=True)
> text = Column(String(50))
>
>
> class User(Base):
> __tablename__ = 'user'
>
>
> id = Column(Integer, primary_key=True)
> phone_number = Column(PhoneNumberType())
> type = Column(String(50))
>
> __mapper_args__ = {
> 'polymorphic_identity': 'user',
> 'polymorphic_on': type
> }
>
> sent_messages = relationship(
> 'ChatMessage',
> foreign_keys='ChatMessage.sender_id',
> backref='sending_user'
> )
>
> received_messages = relationship(
> 'ChatMessage',
> foreign_keys='ChatMessage.sender_id',
> backref='sending_user'
> )
>
>
> class InvitedUser(TBHUser):
> __tablename__ = 'invited_user'
> id = Column(Integer, ForeignKey('user.id'), primary_key=True)
>
> __mapper_args__ = {
> 'polymorphic_identity': 'invited_user',
> }
>
>
> class VerifiedUser(TBHUser):
> __tablename__ = 'verified_user'
> id = Column(Integer, ForeignKey('user.id'), primary_key=True)
>
> __mapper_args__ = {
> 'polymorphic_identity': 'verified_user',
> }
>
>
>
>
> I'll give a brief breakdown of some structural business rules present in my
> application. A single user is able to chat with anyone in their contacts. If
> a user messages a friend who is not registered in the app, the chat message
> will be sent and the receiving user will receive an invitation to download
> the app. Invited users are stored inside the 'InvitedUser' table. After an
> invited user signs up, the 'InvitedUser' instance will be deleted and will
> be replaced with a 'VerifiedUser' instance. The purpose of the 'InvitedUser'
> entity is to persist received chat messages prior to signup.
>
> While deleting an InvitedUser from the database, I am receiving the error
> below.
>
>
> AssertionError: Dependency rule tried to blank-out primary key column
> 'chat_message.receiver_id' on instance '<ChatMessage at 0x1044a9cf8>'
>
>
>
> It appears that SQLAlchemy is not allowing the deletion of an InvitedUser in
> order to preserve integrity of the ChatMessages received by the user. A
> simple solution would be to have one user class and potentially a 'status'
> attribute, however, that did not seem optimal.
>
> Is there a specific way to tell SQLAlchemy to transfer an 'InvitedUser' to
> 'VerifiedUser'? Possibly my models are overcomplicated and there is a
> different way of solving this problem.
>
When using inheritance with the ORM, I don't think there's a direct
way to tell SQLAlchemy to change the class of a record. I think you'd
have to make the changes behind the ORM's back and expunge any
existing instance from the session. In your case that would mean
issuing a DELETE against invited_user and an INSERT against
verified_user.
If you really don't have any columns in your invited_user and
verified_user tables other than the primary key, I don't think that
joined-table inheritance is really necessary here. Single-table
inheritance would be simpler. I don't think you'd need another
"status" column, as your "type" column will already contain either
"verified_user" or "invited_user". To change the class, you'd have to
UPDATE the type column and expunge any instances.
In my opinion, having a single class and a "status" attribute is the
simplest solution. Changing the class of an object, while technically
possible, is a bit nasty. Why do you consider the attribute to be
non-optimal?
Simon
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable
Example. See http://stackoverflow.com/help/mcve for a full description.
---
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
