On Mon, Jul 31, 2017 at 4:37 PM, Chris Satterthwaite <[email protected]> wrote: > Conceptually, what I’m trying to do is: > > 1) Enable queries for a higher level type (e.g. Node) to return all > direct instances and sub-class instances (e.g. Node, [Servers, [UnixType, > [Linux, AIX]]]) > > 2) Enable a set of attributes to be inherited by all sub-types > > 3) Enable an attribute update at any level to update attributes seen by > all levels (e.g. time_updated in my example) > > > > 1 and 2 above “just work” by setting up SqlAlchemy with join inheritance. > But I’ve failed to implement #3. > > > > Versions: Python v3.6.1, SQLAlchemy v1.1.11, and Postgres v9.6. > > > Class definitions follow...
in these class definitions, there's two fields mentioned "time_updated" and then later "distribution", which are not mapped in the same way. "time_updated" is only on the base Node class; to make that update no matter what, do a before_update() event for the Node class with propagate=True and then set object.time_updated=func.now() in the event. For the "distribution" column, you've got the same-named column in two different tables. This is typically not advised as the information being stored is redundant; however if these two columns are indeed the same information you can map both columns to one attribute using the technique shown at http://docs.sqlalchemy.org/en/latest/orm/mapping_columns.html#using-column-property-for-column-level-options, and they would automatically update together. > > class Node(Base): > > __tablename__ = 'node' > > hostname = Column(String(256), primary_key=True) > > domain = Column(String(256), primary_key=True) > > object_id = Column(CHAR(32), unique=True, default=lambda :uuid.uuid4().hex) > > object_type = Column(String(16)) > > time_created = Column(DateTime(timezone=True), server_default=func.now()) > > time_updated = Column(DateTime(timezone=True), default=func.now(), > onupdate=func.now()) > > description = Column(String(256)) > > __mapper_args__ = {'with_polymorphic': '*', 'polymorphic_identity':'node', > 'polymorphic_on':object_type} > > > class Server(Node): > > __tablename__ = 'server' > > object_id = Column(None, ForeignKey(Node.object_id), primary_key=True) > > related_application = Column(String(512), nullable=True) > > __mapper_args__ = {'with_polymorphic': '*', 'polymorphic_identity':'server', > 'inherit_condition': object_id == Node.object_id} > > > class UnixType(Server): > > __tablename__ = 'unix_type' > > object_id = Column(None, ForeignKey(Server.object_id), primary_key=True) > > __mapper_args__ = {'polymorphic_identity':'node_windows', > 'with_polymorphic': '*', 'inherit_condition': object_id == Server.object_id} > > > class Linux(UnixType): > > __tablename__ = 'linux' > > object_id = Column(None, ForeignKey(UnixType.object_id), primary_key=True) > > distribution = Column(String(256), nullable=True) > > __mapper_args__ = {'with_polymorphic': '*', 'polymorphic_identity':'linux', > 'inherit_condition': object_id == UnixType.object_id} > > > class AIX(UnixType): > > __tablename__ = 'aix' > > object_id = Column(None, ForeignKey(UnixType.object_id), primary_key=True) > > distribution = Column(String(256), nullable=True) > > __mapper_args__ = {'with_polymorphic': '*', 'polymorphic_identity':'aix', > 'inherit_condition': object_id == UnixType.object_id} > > > > > I’d like my app to query the base class (Node) for either the two primary > keys (hostname and domain) or the unique key (object_id), and then be able > to directly update any attribute on all inherited class levels. Right now > I’m having to query for Node, then query the Linux class for the same ID (to > get access to all the attributes for my update), then update. And that > works ok unless I want automated actions on an inherited attribute – like > the last update time (time_update). It only updates when I specifically > update the class with that attribute (the Node class in my example). > > > > Complete script from my last iteration attached, along with sample output. > > > > In my four iterations of test cases (required to show updates at all > levels), I show the following: > > 1) Create a base type instance at the start, that can be sub-typed to a > more qualified subtype later (e.g. create Node instance, later sub-type into > AIX instance). I’m doing this by copy/delete/recreate-as-sub-type which > works ok now; I am assuming there is a better way. > > 2) Create a more qualified type at the start, and send updates for just > the base type (e.g. create AIX instance at the start, later update the > “related_application” on the Server instance, or the “time_updated” on the > Node instance) > > > > Samples show that the time_update attribute only changes when the base class > changes. > > > > I probably could update a Linux or AIX object, then re-query for its base > Node with the same ID, and then force a manual update on the timestamp. > Hoping there is another way. > > > > Sorry for the verbosity; I tried to be comprehensive. Appreciate your help. > > > > > > =============================================== > > Additional detail (probably unnecessary) – hence listing it last: > > I’ve spent about a week spinning my wheels, so I’m reaching out. I’ve tried > different variations from web posts, including: > > • relationships from parent-to-child and vice versa (to event off of in > order to propagate time_updated; hit different problems and gave up with > circular dependency on deletes) > > • with_polymorphic in the query and attributes (to load all attributes types > so I could set in just one level) > > • duplicated attribute with foreign keys in all classes (and specifying > inherit_condition - trying to get foreign keys to point to the same > time_updated attribute in the base class) > > • a mixin object to use declared_attr.cascading on forign keys > > > > Perhaps one of those is the right way to do this, but I can’t figure it out. > Some posts show solutions without join inheritance, and I can’t seem to > retrofit those solutions due to the foreign key magic. Most inheritance > posts show only 2-levels deep but the same solution doesn’t seem to work > with my model starting at 4-levels deep. > > -- > 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 [email protected]. > To post to this group, send email to [email protected]. > Visit this group at https://groups.google.com/group/sqlalchemy. > For more options, visit https://groups.google.com/d/optout. -- 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 [email protected]. To post to this group, send email to [email protected]. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
