Missing synonyms with multi-inheritance

[Derek Lambert]

I'm running into an issue in a hierarchy of single-table inheritance objects with multiple inheritance. The objects represent users/groups/etc. from various directories and applications.

Retrieving the list of synonyms from an object at the bottom of the inheritance tree doesn't return the entire list of synonyms.

When I make some of the "mixin" type objects abstract the synonyms returned are as expected, but I lose the ability to query those objects.

Maybe I'm overlooking a simpler implementation, or simply using SQLAlchemy in a way that wasn't intended?

Here's a simplified subset of the code. In practice any object ending with Entry and the base DirectoryUser and DirectoryGroup wouldn't be created.

import sqlalchemy as sa

import sqlalchemy.orm as orm

from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class DirectoryEntry(Base):

    guid               = sa.Column(sa.Integer, primary_key=True)

    _type              = sa.Column(sa.String, nullable=False, index=True)

    distinguished_name = sa.Column(sa.String, index=True)

    name               = sa.Column(sa.String, index=True)

    __tablename__   = 'directory_entry'

    __mapper_args__ = {

        'polymorphic_on':       _type,

        'polymorphic_identity': 'directory_entry',

    }

class DirectoryUser(DirectoryEntry):

    first_name = sa.Column(sa.String)

    last_name  = sa.Column(sa.String)

    email      = sa.Column(sa.String)

    username   = sa.Column(sa.String)

    __mapper_args__ = {

        'polymorphic_identity': 'directory_user',

    }

class LdapEntry(DirectoryEntry):

    cn = orm.synonym('name')

    __mapper_args__ = {

        'polymorphic_identity': 'ldap_entry',

    }

class LdapUser(DirectoryUser, LdapEntry):

    givenName = orm.synonym('first_name')

    sn        = orm.synonym('last_name')

    __mapper_args__ = {

        'polymorphic_identity': 'ldap_user',

    }

class ActiveDirectoryEntry(LdapEntry):

    distinguishedName = orm.synonym('distinguished_name')

    __mapper_args__ = {

        'polymorphic_identity': 'active_directory_entry',

    }

class ActiveDirectoryUser(LdapUser, ActiveDirectoryEntry):

    mail           = orm.synonym('email')

    sAMAccountName = orm.synonym('username')

    __mapper_args__ = {

        'polymorphic_identity': 'active_directory_user'

    }

engine_url = 'postgresql+psycopg2://postgres@localhost/inherit_test'

engine     = sa.create_engine(engine_url, echo=True)

Base.metadata.create_all(engine)

session = orm.sessionmaker(bind=engine)()

ad_user = ActiveDirectoryUser(

        cn='John Doe',

        sAMAccountName='jdoe',

        distinguishedName='ou=domain',

        givenName='John'

)

session.add(ad_user)

session.commit()

user1 = session.query(DirectoryUser).filter(DirectoryUser.username == 'jdoe').one()

user3 = session.query(LdapUser).filter(LdapUser.username == 'jdoe').one()

user2 = session.query(ActiveDirectoryUser).filter(ActiveDirectoryUser.username == 'jdoe').one()

user4 = session.query(DirectoryEntry).filter(DirectoryEntry.name == 'John Doe').one()

assert(user1 == user2 == user3 == user4)

mapper   = sa.inspect(ad_user.__class__)

synonyms = mapper.synonyms.keys()

assert(synonyms == ['mail', 'sAMAccountName', 'givenName', 'sn', 'cn', 'distinguishedName'])

Any help is appreciated!

[Mike Bayer]

On Mon, Apr 30, 2018 at 1:33 PM, Derek Lambert
<dlam...@dereklambert.com> wrote:
> I'm running into an issue in a hierarchy of single-table inheritance objects
> with multiple inheritance. The objects represent users/groups/etc. from
> various directories and applications.
>
> Retrieving the list of synonyms from an object at the bottom of the
> inheritance tree doesn't return the entire list of synonyms.
>
> When I make some of the "mixin" type objects abstract the synonyms returned
> are as expected, but I lose the ability to query those objects.

mmm what do you mean by "mixin" here, it looks like every class you
have is mapped.

I will say that what you are doing here:

class LdapUser(DirectoryUser, LdapEntry):
givenName = orm.synonym('first_name')
sn = orm.synonym('last_name')
__mapper_args__ = {
'polymorphic_identity': 'ldap_user',
}

where DirectoryUser and LdapEntry are also both mapped, I'm amazed
that even works. That's not at all anything that has ever been
supported or attempted, as each mapper only "inherits" from at most
one mapped class - while declarative supports actual "mixin" classes,
where by "mixin" we mean "non-mapped class", nothing in SQLAlchemy ORM
is expecting multiple inheritance at the mapper level. Above, I
guess it's picking one superclass mapper at random to be "inherits",
an ignoring the other, and that is likely the source of your issue.
Unfortunately I think you have to work out this hierarchy in terms of
single-inhertanace for classes that are actually mapped, which means
adding some non-mapped "mixin" classes that just accommodate for the
extra synonyms, something like:

class DirectoryEntry(Base):

class AbstractDirectoryUser(object):
# synonyms

class DirectoryUser(AbstractDirectoryUser, DirectoryEntry):

class LdapEntry(DirectoryEntry):

class LdapUser(AbstractDirectoryUser, LdapEntry):

this a heavy set of inheritance and I might also use composition
instead, though that would change your DB design.

> --
> 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+...@googlegroups.com.
> To post to this group, send email to sqlal...@googlegroups.com.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.

[Derek Lambert]

mmm what do you mean by "mixin" here, it looks like every class you
have is mapped.

They are mapped in the code, but that's only so I can query them. I attempted to make LdapEntry and ActiveDirectoryEntry true mixin's by setting __abstract__ = True.

 

this a heavy set of inheritance and I might also use composition
instead, though that would change your DB design.

 

The design isn't in production yet so now would be the time to change it. Are you aware of any SQLAlchemy projects using composition I could review?

Thanks,

Derek

[Mike Bayer]

On Mon, Apr 30, 2018 at 4:18 PM, Derek Lambert
<dlam...@dereklambert.com> wrote:
>>
>> mmm what do you mean by "mixin" here, it looks like every class you
>> have is mapped.
>>
>
> They are mapped in the code, but that's only so I can query them. I
> attempted to make LdapEntry and ActiveDirectoryEntry true mixin's by setting
> __abstract__ = True.
>
>>
>>
>> this a heavy set of inheritance and I might also use composition
>> instead, though that would change your DB design.
>>
>
> The design isn't in production yet so now would be the time to change it.
> Are you aware of any SQLAlchemy projects using composition I could review?

mmm not specifically, it means you might do something like store
"Entry" concepts in one table and "User" concepts in another.
looking more closely this seems like it would be akward also.

looking more closely at your mappings it looks like only
DirectoryEntry and DirectoryUser actually have any columns. The rest
is all synonyms. I'd likely use mixins for all those synonym sets.

[Derek Lambert]

That was my conclusion too after consulting the googles.

I've done as you suggested and things are working as expected. Thanks!

[Derek Lambert]

I'm replying to my original post since it's related.

I'm still seeing missing synonyms on child classes when they are defined in a mixin imported on a parent.

import sqlalchemy as sa

from sqlalchemy import orm

from sqlalchemy.ext.declarative import declarative_base, declared_attr

Base = declarative_base()

class DirectoryEntry(Base):

    guid               = sa.Column(sa.Integer, primary_key=True)

    _type              = sa.Column(sa.String, nullable=False)

    distinguished_name = sa.Column(sa.String)

    name               = sa.Column(sa.String)

    __tablename__   = 'directory_entry'

    __mapper_args__ = {

        'polymorphic_on':       _type,

        'polymorphic_identity': 'directory_entry',

    }

class DirectoryGroup(DirectoryEntry):

    __mapper_args__ = {

        'polymorphic_identity': 'directory_group',

    }

class ActiveDirectoryEntry:

    @declared_attr

    def distinguishedName(self):

        return orm.synonym('distinguished_name')

class ActiveDirectoryGroup(ActiveDirectoryEntry, DirectoryGroup):

    __mapper_args__ = {

        'polymorphic_identity': 'active_directory_group'

    }

class AnotherChild(ActiveDirectoryGroup):

    __mapper_args__ = {

        'polymorphic_identity': 'another_child'

    }

engine = sa.create_engine('postgresql+psycopg2://postgres@localhost/postgres', echo=True, isolation_level='AUTOCOMMIT')

engine.execute('DROP DATABASE IF EXISTS inherit_test')

engine.execute('CREATE DATABASE inherit_test')

engine = sa.create_engine('postgresql+psycopg2://postgres@localhost/inherit_test', echo=True)

Base.metadata.create_all(engine)

session = orm.sessionmaker(bind=engine)()

group   = ActiveDirectoryGroup(

        name='Users',

        distinguishedName='cn=Users,ou=domain',

)

child   = AnotherChild(

        name='Admins',

        distinguishedName='cn=Admins,ou=domain',

)

session.add(group)

session.add(child)

session.flush()

session.commit()

group          = session.query(ActiveDirectoryGroup).filter(ActiveDirectoryGroup.name == 'Users').one()

group_mapper   = sa.inspect(group.__class__)

group_synonyms = group_mapper.synonyms.keys()

child          = session.query(AnotherChild).filter(AnotherChild.name == 'Admins').one()

child_mapper   = sa.inspect(child.__class__)

child_synonyms = child_mapper.synonyms.keys()

assert child_synonyms == group_synonyms

Maybe I'm off in unsupported land again? I could define AnotherChild identical to ActiveDirectoryGroup, but there are additional synonyms on ActiveDirectoryGroup they both should have.

Thanks,

Derek

[Mike Bayer]

the synonym() is only applied to ActiveDirectoryGroup because it is
naturally inherited by AnotherChild:

child_synonyms = child_mapper.inherits.synonyms.keys()

assert child_synonyms == group_synonyms

but it's not getting mapped anyway, which seems like a bug. works
with @cascading though:

class ActiveDirectoryEntry:
@declared_attr.cascading
def distinguishedName(cls):
return orm.synonym('distinguished_name')


c1 = session.query(AnotherChild.distinguishedName).filter(
AnotherChild.distinguishedName == "cn=Admins,ou=domain").scalar()
g1 = session.query(ActiveDirectoryGroup.distinguishedName).filter(
ActiveDirectoryGroup.distinguishedName == "cn=Users,ou=domain").scalar()

print(g1)
print(c1)

[Mike Bayer]

it's a recent regression as of 1.2.12:
https://bitbucket.org/zzzeek/sqlalchemy/issues/4350/synonym-single-inheritance-mixins

will fix

[Derek Lambert]

Thanks. I'll use cascading as a workaround for the time being.