AutoLoading Tips

Introduction

The following illustrations came directly from my recent experience using Elixir to model existing tables from a PostgreSQL database. I expect the tips to translate to other schemas and databases as well.

ManyToMany with ForeignKey, Primaryjoin and Secondaryjoin

The first model I tried to create was reflecting the User, and UserGroup relationship. In this case, every group can have multiple users, and every user can be a member of multiple groups. Here is a simplified SQL representation:

CREATE TABLE users (
    userid      bigint          DEFAULT '0' NOT NULL,
    name        varchar(100)    DEFAULT ''  NOT NULL,
    passwd      char(32)        DEFAULT ''  NOT NULL,
    PRIMARY KEY (userid)
) with OIDS;
CREATE TABLE usrgrp (
    usrgrpid        bigint      DEFAULT '0' NOT NULL,
    name        varchar(64)     DEFAULT ''  NOT NULL,
    gui_access      integer     DEFAULT '0' NOT NULL,
    PRIMARY KEY (usrgrpid)
) with OIDS;
CREATE TABLE users_groups (
    id          bigint      DEFAULT '0' NOT NULL,
    usrgrpid    bigint      DEFAULT '0' NOT NULL,
    userid      bigint      DEFAULT '0' NOT NULL,
    PRIMARY KEY (id)
) with OIDS;

If Elixir were creating a ManyToMany relationship like this, it would add foreign key constraints to the users_groups table. But, here we have none. As a result, it means that the Elixir model must explicitly declare the foreign keys.

If you're familiar with SQLAlchemy, then you might already know that such a declaration might look something like this:

user = Table('users', metadata, autoload=True)
usrgrp = Table('usrgrp', metadata, autoload=True)
users_groups = Table('users_groups', metadata, autoload=True)

class User(object): pass
class UsrGrp(object): pass

mapper(User, user, {
    'groups' : relation(UsrGrp,
        secondary=users_groups,
        foreign_keys=[users_groups.c.userid,usrgrp.c.usrgrpid,users_groups.c.usrgrpid],
        primaryjoin=user.c.userid==users_groups.c.userid,
        secondaryjoin=usrgrp.c.usrgrpid==users_groups.c.usrgrpid)
})
mapper(UsrGrp, usrgrp, {
    'users' : relation(User,
        secondary=users_groups,
        foreign_keys=[users_groups.c.userid,user.c.userid,users_groups.c.usrgrpid],
        primaryjoin=usrgrp.c.usrgrpid==users_groups.c.usrgrpid,
        secondaryjoin=user.c.userid==users_groups.c.userid)
})

Unfortunately, at declaration time with Elixir, we do not have access to to the table instances used in the foreign_keys, primaryjoin, and secondaryjoin arguments above. However, Elixir as well as SQLAlchemy allows these arguments to be callable, meaning that we can declare them, and they will not be interpreted until later. So, the following will work as we hope.

users_groups = Table('users_groups', metadata, autoload=True)

class User(Entity):
    using_options(tablename='users', autoload=True)
    groups = ManyToMany(
        'Group',
        table=users_groups,
        foreign_keys=lambda: [users_groups.c.userid, users_groups.c.usrgrpid],
        primaryjoin=lambda: User.userid == users_groups.c.userid,
        secondaryjoin=lambda: Group.usrgrpid == users_groups.c.usrgrpid,
    )

class Group(Entity):
    using_options(tablename='usrgrp', autoload=True)
    users = ManyToMany(
        'User',
        table=users_groups,
        foreign_keys=lambda: [users_groups.c.userid, users_groups.c.usrgrpid],
        primaryjoin=lambda: Group.usrgrpid == users_groups.c.usrgrpid,
        secondaryjoin=lambda: User.userid == users_groups.c.userid,
    )

ManyToOne and ForeignKey Constraints

The next relationship I needed to autoload, that again included no foreignkey constraints on the table definition, was a ManyToOne/OneToMany between the users table used above, and the media table below, that basically just contained an email address associated with the user object.

CREATE TABLE media (
    mediaid     bigint          DEFAULT '0' NOT NULL,
    userid      bigint          DEFAULT '0' NOT NULL,
    email       varchar(100)    DEFAULT ''  NOT NULL,
    PRIMARY KEY (mediaid)
) with OIDS;

The solution for this turned out to be similar to that above by specifying both the foreignkey and primaryjoin argument.

class Media(Entity):
    using_options(tablename='media', autoload=True)
    user = ManyToOne('User', ondelete="cascade",
                     primaryjoin=lambda: Media.userid == User.userid,
                     foreign_keys=lambda: [Media.userid])

class User(Entity):
    using_options(tablename='users', autoload=True)
    media = OneToMany('Media', cascade='all, delete-orphan',
                      primaryjoin=lambda: Media.userid == User.userid,
                      foreign_keys=lambda: [Media.userid])

In this example, the cascade and ondelete arguments guaranteed that if a user was deleted, that all associated media records would also be deleted with it.