On Jan 2, 2013, at 10:08 PM, YKdvd wrote:
> 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.
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 sqlalchemy@googlegroups.com.
> To unsubscribe from this group, send email to
> sqlalchemy+unsubscr...@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/sqlalchemy?hl=en.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
