I did a lot of work this weekend on the relational algrebra features
of SA as well as being able to map to a wider variety of structures.
While you could sort of jam a Select statement into a Mapper all
along it wasnt working that great. Now its working better.
Hopefully good enough that people wont stump me with surprise issues
when they start trying to use it seriously.
Mapping against Relational Structures
----------------------------------------------------
When you make joins, aliases, and selects, they all have accessors
for 'columns', 'primary_key', and 'foreign_keys' just like a table.
The column and foreign key objects reported by these accessors are
usually not the original ones from the underlying tables but are
instead copies that are adjusted to have the relational object they
came from as their parent. You can now make a join against a table
alias and a plain table, or a join against a table and an arbitrary
select statement, and it can still figure out the proper join
conditions from the foreign keys of the underlying tables. I also
streamlined the creation of all these "exported" objects so that they
get created only when first called upon, and also with as little
redundant looping as possible.
As a stress-test I made a change to the Zblog demo in how it loads
the listing of posts and all its comment counts. My goal with
constructing mappers is usually focused on decreasing the number of
individual queries as much as possible since that is typically the
biggest speed hit. Previously, the homepage that listed out all the
posts in a blog would have to do another round trip for each post to
get the count of comments. Now I have managed to roll that count up
into the main mapper for the Post object.
So the query to select a Post along with its comment count looks like
this:
posts_with_ccount = select(
[c for c in tables.posts.c if c.key != 'body'] + [
func.count(tables.comments.c.comment_id).label('comment_count')
],
from_obj = [outerjoin(tables.posts, tables.comments)],
group_by=[c for c in tables.posts.c]
) .alias('postswcount')
The "body" column is left out since we are going to deferred load
that. When mapping against a regular table, the 'deferred' loaders
will take their corresponding column out of the select list. But
here we are removing it from the "inner" query (though it might not
matter for most databases if its data isnt ultimately sent over the
wire). Anyway, the select statement for this looks like:
SELECT
posts.post_id AS post_id,
posts.blog_id AS blog_id,
posts.user_id AS user_id,
posts.datetime AS datetime,
posts.headline AS headline,
posts.summary AS summary,
count(comments.comment_id) AS comment_count
FROM posts
LEFT OUTER JOIN comments ON posts.post_id = comments.post_id
GROUP BY posts.post_id,
posts.blog_id, posts.user_id, posts.datetime,
posts.headline, posts.summary, posts.body
Then we can now just map to that query, complete with the eager join
of the keywords, deferred loading of the post's body, etc.:
Post.mapper = mapper(Post, posts_with_ccount, properties={
'id':posts_with_ccount.c.post_id,
'body':deferred(tables.posts.c.body),
'user':relation(user.User, lazy=True, backref='posts'),
'blog':relation(Blog, lazy=True, backref='posts'),
'topics':relation(TopicAssociation, lazy=False, private=True,
association=Topic)
}, is_primary=True, order_by=[desc(posts_with_ccount.c.datetime)])
The "deferred" property on "body" is made using the column from the
underlying table. This has the effect of using just the original
table when it makes the second query for the body, instead of joining
against the bigger query used by the primary load. The generated
queries work with SQLite (which is lenient) and Postgres (which is
very strict), which bodes well for MySQL and Oracle which I havent
tested yet.
Lazy Load uses mapper.get() when possible
-------------------------------------------------------------
I made an additional improvement to lazy loading as well; above, when
the "user" property on the "Post" is loaded, since its searching for
a User object by its primary key, it will skip the query if the
desired User object has already been loaded. So "many-to-one" lazy
loads are less expensive in many cases.
objectstore.import_instance()
-------------------------------------------------
several people have made comments about how SQLAlchemy has fewer than
expected "surprises", partially because theres a lot of exceptions it
raises when constructing mappers and committing to make sure various
pitfalls dont occur. these exceptions are usually the result of my
own coming across a particular pitfall and then spending four hours
trying to figure out why; as this thing goes pretty deep, a small
error condition can easily lead to insanely confounding issues.
when you fall into a hole with this thing, the hole can be pretty
deep and dark.
so todays insanely confounding issue was due to something I knew
would be needed, the idea of "de-passivating" objects that come from
the outside, meaning outside the unit-of-work, into the current
session. Every object instance you deal with in SQLAlchemy is
stored in a thread local dictionary called an "identity map". This
is to insure that every object is unique to its given identity, and
that operations arent performed on two different copies of an object
that should only happen on a single instance. If this condition
does occur, you get things like attaching a User to a Post, which
should be the same User that was already there, results in a "delete"
of one copy and an "add" operation of the other via a backreference,
and the commit totally blows up since one operation wants to delete
the Post and the other wants to save it.
The "database identity" of an object as SA is concerned is
deterimined by an attribute it sets on your object called
"_instance_key". So as long as you dont screw with that attribute,
the only way to have two copies like this is to share an object
across threads or unit-of-work instances, which happens for example
when you serialize something into the session and then pull it
back. Now, if you try to commit the current unit of work and it
detects objects with _instance_key that arent in the identity map, it
will raise "Detected a mapped object not present in the current
thread's Identity Map: '%s'. Use objectstore.import_instance() to
place deserialized instances or instances from other threads".
So all that calls for is, when you are pulling something out of a
session or some other place not from a Mapper or from a new
constructor, is to say:
myobj = objectstore.import_instance(myobj)
which will either return the object you give it, or the one that was
already in the identity map. This is illustrated in the ZBlog demo
where it pulls the User object from the session.
Also keep in mind that you *can* use objects across threads and
serializations without dealing with import_instance(), as long as its
in a read-only fashion. The only time their "identity" becomes an
issue is when you change the objects, particularly when you go to
commit() them, or if you are relying upon in-memory identity
comparisons with them. So for objects that are to be modified and
saved, its best to insure that they are all loaded within the scope
of the current unit of work. For objects that just get loaded and
read by various threads and serializers, theres less to worry about.
-------------------------------------------------------
This SF.net email is sponsored by: Splunk Inc. Do you grep through log files
for problems? Stop! Download the new AJAX search engine that makes
searching your log files as easy as surfing the web. DOWNLOAD SPLUNK!
http://ads.osdn.com/?ad_id=7637&alloc_id=16865&op=click
_______________________________________________
Sqlalchemy-users mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/sqlalchemy-users
