async def get_payout_stats(context, limit=250):
"""Get payout stats for building treemap."""
db = context['db']
limit = valid_limit(limit, 250)
stats = PayoutStats.instance()
await stats.generate()
sql = """
SELECT hc.name, hc.title, author, payout, posts, authors
FROM payout_stats
LEFT JOIN hive_communities hc ON hc.id = community_id
WHERE (community_id IS NULL AND author IS NOT NULL)
OR (community_id IS NOT NULL AND author IS NULL)
ORDER BY payout DESC
LIMIT :limit
"""
rows = await db.query_all(sql, limit=limit)
items = list(map(_row, rows))
sql = """SELECT SUM(payout) FROM payout_stats WHERE author IS NULL"""
total = await db.query_one(sql)
sql = """SELECT SUM(payout) FROM payout_stats
WHERE community_id IS NULL AND author IS NULL"""
blog_ttl = await db.query_one(sql)
return dict(items=items, total=float(total), blogs=float(blog_ttl))
async def list_communities(context,
last='',
limit=100,
query=None,
observer=None):
"""List all communities, paginated. Returns lite community list."""
limit = valid_limit(limit, 100)
db = context['db']
assert not query, 'query not yet supported'
seek = ''
if last:
seek = """AND rank > (SELECT rank
FROM hive_communities
WHERE name = :last)"""
sql = """SELECT id FROM hive_communities
WHERE rank > 0 AND (num_pending > 0 OR LENGTH(about) > 3) %s
ORDER BY rank LIMIT :limit""" % seek
ids = await db.query_col(sql, last=last, limit=limit)
if not ids: return []
communities = await load_communities(db, ids, lite=True)
if observer:
observer_id = await get_account_id(db, observer)
await _append_observer_subs(db, communities, observer_id)
await _append_observer_roles(db, communities, observer_id)
return [communities[_id] for _id in ids]
async def list_following(context, account, start='', limit=50, observer=None):
"""Get a list of all accounts `account` follows."""
following = await get_following(
context['db'],
valid_account(account),
valid_account(start, allow_empty=True),
'blog', valid_limit(limit, 100))
return await accounts_by_name(context['db'], following, observer, lite=True)
async def fetch_more_children(context, root_id, last_sibling_id, sort='top',
limit=20, observer=None):
"""Fetch truncated siblings from tree."""
db = context['db']
return await _fetch_children(db, root_id, last_sibling_id,
valid_comment_sort(sort),
valid_limit(limit, 50),
observer)
async def list_account_blog(context, account, limit=10, observer=None, last_post=None):
"""Get a blog feed (posts and reblogs from the specified account)"""
db = context['db']
post_ids = await pids_by_blog(
db,
valid_account(account),
*split_url(last_post, allow_empty=True),
valid_limit(limit, 50))
return await posts_by_id(db, post_ids, observer)
async def fetch_tree(context, root, sort='top', limit=20, observer=None):
"""Fetch comment tree. Includes comments and lite author data.
If community: follows/applies mod rules
If blog: hides comments by any muted accounts of the author's
Sort: new, old, hot, payout"""
db = context['db']
root_id = await url_to_id(db, root)
return await _fetch_children(db, root_id, None, valid_comment_sort(sort),
valid_limit(limit, 50), observer)
async def list_account_posts(context, account, limit=10, observer=None, last_post=None):
"""Get an account's posts and comments"""
db = context['db']
start_author, start_permlink = split_url(last_post, allow_empty=True)
assert not start_author or (start_author == account)
post_ids = await pids_by_comments(
db,
valid_account(account),
valid_permlink(start_permlink),
valid_limit(limit, 50))
return await posts_by_id(db, post_ids, observer)
async def list_community_posts(context,
community,
sort='trending',
start='',
limit=10,
observer=None):
"""Paginated list of posts in a community. Includes pinned posts at the beginning.
Observer: includes vote/reblog status on each post.
Community:
- `all`: renders site default
- `my`: render's observer's subs
- (blank): show global trending
- (string): show community trending
"""
db = context['db']
pinned_ids = []
if not community:
# global trending: prefix home posts
communities = []
#if not start: pinned_ids = _pinned(db, DEFAULT_COMMUNITY)
elif community[0] == '#':
# feed for specific tag
communities = [community[1:]]
elif community[0] == '@':
# user's subscribed communities feed
communities = await _subscribed(db, community[1:])
#if not start: pinned_ids = _pinned(db, DEFAULT_COMMUNITY)
else:
# specific community feed
communities = [community]
if not start: pinned_ids = await _pinned(db, community)
post_ids = await ranked_pids(
db,
sort=valid_sort(sort),
start_id=await url_to_id(db, start) if start else None,
limit=valid_limit(limit, 50),
communities=communities)
# TODO: fetch account role/title, include in response
# NOTE: consider including & interspercing promoted posts here
posts = await posts_by_id(db, pinned_ids + post_ids, observer=observer)
# Add `pinned` flag to all pinned
for pinned_id in pinned_ids:
posts[pinned_id]['is_pinned'] = True
return posts
async def list_communities(context,
last='',
limit=100,
query=None,
sort='rank',
observer=None):
"""List all communities, paginated. Returns lite community list."""
# pylint: disable=too-many-arguments, too-many-locals
limit = valid_limit(limit, 100)
db = context['db']
assert sort in ('rank', 'new', 'subs'), 'invalid sort'
where = []
field, order = dict(rank=('rank', 'ASC'),
new=('created_at', 'DESC'),
subs=('subscribers', 'DESC'))[sort]
search = None
if query:
where.append(
"to_tsvector('english', title || ' ' || about) @@ plainto_tsquery(:search)"
)
search = query
#where.append("to_tsvector('english', title || ' ' || about) @@ to_tsquery(:search)")
#assert not query, 'query not yet supported'
#search = ' | '.join(query.split(' '))
if field == 'rank':
where.append('rank > 0')
if last:
field_cmp = '>' if order == 'ASC' else '<'
where.append("""%s %s (SELECT %s FROM hive_communities
WHERE name = :last)""" %
(field, field_cmp, field))
filt = 'WHERE ' + ' AND '.join(where) if where else ''
sql = """SELECT id FROM hive_communities %s
ORDER BY %s %s LIMIT :limit""" % (filt, field, order)
ids = await db.query_col(sql, last=last, limit=limit, search=search)
if not ids: return []
# append observer context, leadership data
communities = await load_communities(db, ids, lite=True)
if observer:
observer_id = await get_account_id(db, observer)
await _append_observer_subs(db, communities, observer_id)
await _append_observer_roles(db, communities, observer_id)
await _append_admins(db, communities)
return [communities[_id] for _id in ids]
async def list_pop_communities(context, limit=25):
"""List communities by new subscriber count. Returns lite community list."""
limit = valid_limit(limit, 25)
sql = """SELECT name, title
FROM hive_communities
JOIN (
SELECT community_id, COUNT(*) newsubs
FROM hive_subscriptions
WHERE created_at > :cutoff
GROUP BY community_id
) stats
ON stats.community_id = id
ORDER BY newsubs DESC
LIMIT :limit"""
out = await context['db'].query_all(sql, limit=limit)
return [(r[0], r[1]) for r in out]
async def list_account_feed(context, account, limit=10, observer=None, last_post=None):
"""Get all posts (blogs and resteems) from `account`'s follows."""
db = context['db']
ids_with_reblogs = await pids_by_feed_with_reblog(
context['db'],
valid_account(account),
*split_url(last_post, allow_empty=True),
valid_limit(limit, 50))
reblog_by = dict(ids_with_reblogs)
post_ids = [r[0] for r in ids_with_reblogs]
posts = await posts_by_id(db, post_ids, observer)
# Merge reblogged_by data into result set
for post in posts:
rby = set(reblog_by[post['post_id']].split(','))
rby.discard(post['author'])
if rby: post['reblogged_by'] = list(rby)
return posts
async def post_notifications(context,
author,
permlink,
min_score=25,
last_id=None,
limit=100):
"""Load notifications for a specific post."""
# pylint: disable=too-many-arguments
db = context['db']
limit = valid_limit(limit, 100)
post_id = await get_post_id(db, author, permlink)
seek = ' AND hn.id < :last_id' if last_id else ''
sql = _notifs_sql("post_id = :post_id" + seek)
rows = await db.query_all(sql,
min_score=min_score,
post_id=post_id,
last_id=last_id,
limit=limit)
return [_render(row) for row in rows]
async def account_notifications(context,
account,
min_score=25,
last_id=None,
limit=100):
"""Load notifications for named account."""
db = context['db']
limit = valid_limit(limit, 100)
account_id = await get_account_id(db, account)
if account[:5] == 'hive-': min_score = 0
seek = ' AND hn.id < :last_id' if last_id else ''
col = 'hn.community_id' if account[:5] == 'hive-' else 'dst_id'
sql = _notifs_sql(col + " = :dst_id" + seek)
rows = await db.query_all(sql,
min_score=min_score,
dst_id=account_id,
last_id=last_id,
limit=limit)
return [_render(row) for row in rows]
