Multiple table join selectable update vs insert

[YKdvd]

I'm starting to work with an existing MySQL setup, where there's a master database (or, effectively, schema, it is all within one MySQL instance) with tables of general usefulness, and separate schemas for each specific project.  So there is a table "master.users" with all the basic information for a user, and each project would have something like project7.userlink with additional info.  "userlink" also has a couple of columns which duplicate those in users, so that, for instance, user.jobtitle can be overridden by userlink.jobtitle for that specific project, if the userlink column is non-NULL.  The existing usage (mainly PHP) has a view in the project database which presents the appropriate values via COALESCE, which was fine, but needs to change now that creating and updating the info is required (the view obviously wasn't updatable).

I've used the "Mapping a Class against Multiple Tables" pattern in the SQLAlchemy docs to use a join selectable:

MasterUser = Table("users", metadata, Column("id", Integer, primary_key=True) ... schema="master")

ProjUser = Table("userlink", metadata, Column("user_id", Integer, ForeignKey('master.users.id'), primary_key=True, )...)

UserMerge_join = join(MasterUser, ProjUser)

class UMerge(Base):

    __table__ = UserMerge_join

    id = column_property(MasterUser.c.id, ProjUser.c.user_id)

    ...

This seemed to work well for creating new users (no records yet in master.users and the project's userlink table), and retrieving those that have a userlink row in the whatever project database being connected to (which means the master.users row exists as well).  It failed trying to retrieve UserMerge instances where a matching master.users row existed but no row in the project's userlink table yet (not all users belong to every project), but I changed to the join to:

    UserMerge_join = join(MasterUser, ProjUser, isouter=True)

and I can retrieve non-member users if necessary, in preparation to add them to the project.  But if I then modify and commit, it fails with "StaleDataError: UPDATE statement on table 'userlink' expected to update 1 row(s); 0 were matched.".  SQLAlchemy seems to be saying that even though no row from userlink was present on the retrieval, it is expecting one on the update.  I can understand the error, since this same situation would be produced if indeed there had been a userlink record which was deleted by something outside the session.  I may be abusing the "isouter" feature, and I can handle this some other way (add existing users into a project by adding a bare ProjUser entry for them), but I thought I'd check to make sure there wasn't one more little trick I might be missing to have SQLAlchemy generate the necessary INSERT instead of UPDATE in this case for the "userlink" table portion of this composite object.

[Michael Bayer]

Mapping to an outerjoin is a pretty edge case, and i think your assessment is correct here, SQLAlchemy is going to want to be able to emit an UPDATE for all tables for which it knows the primary key.  So for this to work you'd need to treat the "ProjUser" table here as "read only" and not map its primary key column.   There might be ways SQLAlchemy could be enhanced to be more adept at the "mapping to a join" scenario here but at the moment that's how it works, since it's not a very common use case.

I'd be looking for other ways to model this.   Two other approaches for modeling two tables are joined table inheritance, and then just using relationship().   I think joined inheritance is not quite a fit here, because you have columns duplicated in the base and child tables, and you also have the use case where the userlink row is added after the fact to an existing entity.   So I'd probably model "User" and "UserLink" as two separate classes, link them with relationship(), and then use descriptors or other techniques to smooth out the interface for columns like "jobtitle", checking for the presence of the "userlink" attribute and if not present then falling back to that of "user".

--
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/4RqGSE-ywvsJ.
To post to this group, send email to sqlal...@googlegroups.com.
To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.