1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
|
"""mapper.py - defines mappers for domain objects, mapping operations"""
import zblog.tables as tables
import zblog.user as user
from zblog.blog import *
from sqlalchemy import *
from sqlalchemy.orm import *
import sqlalchemy.util as util
def zblog_mappers():
# User mapper. Here, we redefine the names of some of the columns to
# different property names. normally the table columns are all sucked in
# automatically.
mapper(user.User, tables.users, properties={
'id':tables.users.c.user_id,
'name':tables.users.c.user_name,
'group':tables.users.c.groupname,
'crypt_password':tables.users.c.password,
})
# blog mapper. this contains a reference to the user mapper, and also
# installs a "backreference" on that relationship to handle it in both
# ways. this will also attach a 'blogs' property to the user mapper.
mapper(Blog, tables.blogs, properties={
'id':tables.blogs.c.blog_id,
'owner':relation(user.User, lazy=False,
backref=backref('blogs', cascade="all, delete-orphan")),
})
# topic mapper. map all topic columns to the Topic class.
mapper(Topic, tables.topics)
# TopicAssocation mapper. This is an "association" object, which is
# similar to a many-to-many relationship except extra data is associated
# with each pair of related data. because the topic_xref table doesnt
# have a primary key, the "primary key" columns of a TopicAssociation are
# defined manually here.
mapper(TopicAssociation,tables.topic_xref,
primary_key=[tables.topic_xref.c.post_id,
tables.topic_xref.c.topic_id],
properties={
'topic':relation(Topic, lazy=False),
})
# Post mapper, these are posts within a blog.
# since we want the count of comments for each post, create a select that
# will get the posts and count the comments in one query.
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 if c.key != 'body'
]
) .alias('postswcount')
# then create a Post mapper on that query.
# we have the body as "deferred" so that it loads only when needed, the
# user as a Lazy load, since the lazy load will run only once per user and
# its usually only one user's posts is needed per page, the owning blog is
# a lazy load since its also probably loaded into the identity map
# already, and topics is an eager load since that query has to be done per
# post in any case.
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=backref('posts', cascade="all, delete-orphan")),
'blog':relation(Blog, lazy=True,
backref=backref('posts', cascade="all, delete-orphan")),
'topics':relation(TopicAssociation, lazy=False,
cascade="all, delete-orphan",
backref='post')
}, order_by=[desc(posts_with_ccount.c.datetime)])
# comment mapper. This mapper is handling a hierarchical relationship on
# itself, and contains a lazy reference both to its parent comment and its
# list of child comments.
mapper(Comment, tables.comments, properties={
'id':tables.comments.c.comment_id,
'post':relation(Post, lazy=True,
backref=backref('comments',
cascade="all, delete-orphan")),
'user':relation(user.User, lazy=False,
backref=backref('comments',
cascade="all, delete-orphan")),
'parent':relation(Comment,
primaryjoin=(tables.comments.c.parent_comment_id ==
tables.comments.c.comment_id),
foreign_keys=[tables.comments.c.comment_id],
lazy=True, uselist=False),
'replies':relation(Comment,
primaryjoin=(tables.comments.c.parent_comment_id ==
tables.comments.c.comment_id),
lazy=True, uselist=True, cascade="all"),
})
# we define one special find-by for the comments of a post, which is going to
# make its own "noload" mapper and organize the comments into their correct
# hierarchy in one pass. hierarchical data normally needs to be loaded by
# separate queries for each set of children, unless you use a proprietary
# extension like CONNECT BY.
def find_by_post(post):
"""returns a hierarchical collection of comments based on a given criterion.
Uses a mapper that does not lazy load replies or parents, and instead
organizes comments into a hierarchical tree when the result is produced.
"""
q = session().query(Comment).options(noload('replies'), noload('parent'))
comments = q.select_by(post_id=post.id)
result = []
d = {}
for c in comments:
d[c.id] = c
if c.parent_comment_id is None:
result.append(c)
c.parent=None
else:
parent = d[c.parent_comment_id]
parent.replies.append(c)
c.parent = parent
return result
Comment.find_by_post = staticmethod(find_by_post)
def start_session():
"""creates a new session for the start of a request."""
trans.session = create_session(bind_to=zblog.database.engine )
def session():
return trans.session
|
