def search_(request):
form = request.web_input(q="",
min="",
max="",
currency="",
pc="",
c="",
o="")
limit = 30
offset = define.get_int(form.o)
commishclass = form.pc if form.pc else form.c
commishclass = commishclass.lower()
results = commishinfo.select_commissionable(
request.userid,
form.q,
commishclass,
commishinfo.parse_currency(form.min),
commishinfo.parse_currency(form.max),
form.currency,
offset,
limit * 2,
)
rcount = len(results)
results = results[0:limit]
media.populate_with_user_media(results)
prev_index = None if offset == 0 else offset - limit if offset - limit > 0 else 0
next_index = offset + limit if rcount - limit > 0 else None
return Response(
define.webpage(request.userid, "etc/marketplace.html", [
results, form, commishinfo.CURRENCY_CHARMAP,
commishinfo.PRESET_COMMISSION_CLASSES, prev_index, next_index
]))
def select_followed(userid, otherid, limit=None, backid=None, nextid=None, choose=None, following=False):
"""
Returns the users who are following the specified user; note that
``following`` need never be passed explicitly.
"""
if following:
statement = ["SELECT wu.otherid, pr.username, pr.config FROM watchuser wu"
" INNER JOIN profile pr ON wu.otherid = pr.userid"
" WHERE wu.userid = %i" % (otherid,)]
else:
statement = ["SELECT wu.userid, pr.username, pr.config FROM watchuser wu"
" INNER JOIN profile pr ON wu.userid = pr.userid"
" WHERE wu.otherid = %i" % (otherid,)]
if userid:
statement.append(m.MACRO_IGNOREUSER % (userid, "pr"))
if backid:
statement.append(" AND pr.username < (SELECT username FROM profile WHERE userid = %i)" % (backid,))
elif nextid:
statement.append(" AND pr.username > (SELECT username FROM profile WHERE userid = %i)" % (nextid,))
if choose:
statement.append(" ORDER BY RANDOM() LIMIT %i" % (choose,))
else:
statement.append(" ORDER BY pr.username%s LIMIT %i" % (" DESC" if backid else "", limit))
query = [{
"userid": i[0],
"username": i[1],
} for i in d.execute("".join(statement))]
media.populate_with_user_media(query)
return query[::-1] if backid else query
def select_accepted(userid, limit=None, backid=None, nextid=None):
result = []
query = d.execute(
"SELECT fr.userid, p1.username, p1.config, fr.otherid, p2.username, p2.config, fr.settings FROM frienduser fr"
" INNER JOIN profile p1 ON fr.userid = p1.userid"
" INNER JOIN profile p2 ON fr.otherid = p2.userid"
" WHERE %i IN (fr.userid, fr.otherid) AND fr.settings !~ 'p'"
" ORDER BY p1.username", [userid])
for i in query:
if i[0] != userid:
result.append({
"userid": i[0],
"username": i[1],
"settings": i[6],
})
else:
result.append({
"userid": i[3],
"username": i[4],
"settings": i[6],
})
media.populate_with_user_media(result)
return result
def select(userid, ownerid, limit=None, staffnotes=False):
statement = ["""
SELECT
sh.commentid, sh.parentid, sh.userid, pr.username,
sh.content, sh.unixtime, sh.settings, sh.hidden_by
FROM comments sh
INNER JOIN profile pr USING (userid)
WHERE sh.target_user = %i
AND sh.settings %s~ 's'
""" % (ownerid, "" if staffnotes else "!")]
# moderators get to view hidden comments
if userid not in staff.MODS:
statement.append(" AND sh.settings !~ 'h'")
if userid:
statement.append(m.MACRO_IGNOREUSER % (userid, "sh"))
statement.append(" ORDER BY sh.commentid")
query = d.execute("".join(statement))
result = thread(query, reverse_top_level=True)
if limit:
result = result[:limit]
media.populate_with_user_media(result)
return result
def select(userid, otherid, limit=None, pending=False, backid=None, nextid=None, choose=None, gotrequest=False):
query = []
if gotrequest:
statement = ["SELECT fr.otherid, pr.username, fr.settings, pr.config FROM frienduser fr"
" INNER JOIN profile pr ON fr.otherid = pr.userid"
" WHERE fr.otherid = %i AND fr.settings ~ 'p'" % (userid,)]
else:
statement = ["SELECT fr.otherid, pr.username, fr.settings, pr.config FROM frienduser fr"
" INNER JOIN profile pr ON fr.otherid = pr.userid"
" WHERE fr.userid = %i AND fr.settings %s~ 'p'" % (otherid, "" if pending else "!")]
if backid:
statement.append(" AND pr.username < (SELECT username FROM profile WHERE userid = %i)" % (backid,))
elif nextid:
statement.append(" AND pr.username > (SELECT username FROM profile WHERE userid = %i)" % (nextid,))
statement.append(" ORDER BY pr.username" + (" DESC" if nextid else ""))
if limit:
statement.append(" LIMIT %i" % (limit,))
for i in d.execute("".join(statement)):
query.append({
"userid": i[0],
"username": i[1],
"settings": i[2],
})
ret = (d.get_random_set(query, choose) if choose else query[::-1] if backid else query)
media.populate_with_user_media(ret)
return ret
def manage_following(userid, limit, backid=None, nextid=None):
state = [
"SELECT pr.userid, pr.username, pr.config FROM watchuser wu"
" JOIN profile pr ON wu.otherid = pr.userid"
" WHERE wu.userid = %i" % (userid,)]
if backid:
state.append(" AND pr.username < (SELECT username FROM profile WHERE userid = %i)" % backid)
elif nextid:
state.append(" AND pr.username > (SELECT username FROM profile WHERE userid = %i)" % nextid)
state.append(" ORDER BY pr.username")
if backid:
state.append(" DESC")
state.append(" LIMIT %i" % limit)
query = [{
"userid": i[0],
"username": i[1],
} for i in d.execute("".join(state))]
media.populate_with_user_media(query)
return query[::-1] if backid else query
def select_accepted(userid, limit=None, backid=None, nextid=None):
result = []
query = d.execute(
"SELECT fr.userid, p1.username, p1.config, fr.otherid, p2.username, p2.config, fr.settings FROM frienduser fr"
" INNER JOIN profile p1 ON fr.userid = p1.userid"
" INNER JOIN profile p2 ON fr.otherid = p2.userid"
" WHERE %i IN (fr.userid, fr.otherid) AND fr.settings !~ 'p'"
" ORDER BY p1.username", [userid])
for i in query:
if i[0] != userid:
result.append({
"userid": i[0],
"username": i[1],
"settings": i[6],
})
else:
result.append({
"userid": i[3],
"username": i[4],
"settings": i[6],
})
media.populate_with_user_media(result)
return result
def select_streaming(userid, rating, limit, following=True, order_by=None):
statement = [
"SELECT userid, pr.username, pr.stream_url, pr.config, pr.stream_text, start_time "
"FROM profile pr "
"JOIN user_streams USING (userid) "
"WHERE end_time > %i" % (d.get_time(), )
]
if userid:
statement.append(m.MACRO_IGNOREUSER % (userid, "pr"))
if following:
pass # todo
if order_by:
statement.append(" ORDER BY %s LIMIT %i" % (order_by, limit))
else:
statement.append(" ORDER BY RANDOM() LIMIT %i" % limit)
ret = [{
"userid": i[0],
"username": i[1],
"stream_url": i[2],
"stream_text": i[4],
"stream_time": i[5],
} for i in d.execute("".join(statement)) if i[2]]
media.populate_with_user_media(ret)
return ret
def select(userid, ownerid, limit=None, staffnotes=False):
statement = ["""
SELECT
sh.commentid, sh.parentid, sh.userid, pr.username,
sh.content, sh.unixtime, sh.settings, sh.indent,
sh.hidden_by
FROM comments sh
INNER JOIN profile pr USING (userid)
WHERE sh.target_user = %i
AND sh.settings %s~ 's'
""" % (ownerid, "" if staffnotes else "!")]
# moderators get to view hidden comments
if userid not in staff.MODS:
statement.append(" AND sh.settings !~ 'h'")
if userid:
statement.append(m.MACRO_IGNOREUSER % (userid, "sh"))
statement.append(" ORDER BY sh.commentid")
query = d.execute("".join(statement))
result = thread(query, reverse_top_level=True)
if limit:
result = result[:limit]
media.populate_with_user_media(result)
return result
def manage_following(userid, limit, backid=None, nextid=None):
state = [
"SELECT pr.userid, pr.username, pr.config FROM watchuser wu"
" JOIN profile pr ON wu.otherid = pr.userid"
" WHERE wu.userid = %i" % (userid, )
]
if backid:
state.append(
" AND pr.username < (SELECT username FROM profile WHERE userid = %i)"
% backid)
elif nextid:
state.append(
" AND pr.username > (SELECT username FROM profile WHERE userid = %i)"
% nextid)
state.append(" ORDER BY pr.username")
if backid:
state.append(" DESC")
state.append(" LIMIT %i" % limit)
query = [{
"userid": i[0],
"username": i[1],
} for i in d.execute("".join(state))]
media.populate_with_user_media(query)
return query[::-1] if backid else query
def select_streaming(userid, rating, limit, following=True, order_by=None):
statement = [
"SELECT userid, pr.username, pr.stream_url, pr.config, pr.stream_text, start_time "
"FROM profile pr "
"JOIN user_streams USING (userid) "
"WHERE end_time > %i" % (d.get_time(),)
]
if userid:
statement.append(m.MACRO_IGNOREUSER % (userid, "pr"))
if following:
pass # todo
if order_by:
statement.append(" ORDER BY %s LIMIT %i" % (order_by, limit))
else:
statement.append(" ORDER BY RANDOM() LIMIT %i" % limit)
ret = [{
"userid": i[0],
"username": i[1],
"stream_url": i[2],
"stream_text": i[4],
"stream_time": i[5],
} for i in d.execute("".join(statement)) if i[2]]
media.populate_with_user_media(ret)
return ret
def select(userid, submitid=None, charid=None, journalid=None):
result = []
if submitid:
statement = ["""
SELECT
cm.commentid, cm.parentid, cm.userid, pr.username, lo.settings,
cm.content, cm.unixtime, cm.settings, cm.indent, pr.config,
cm.hidden_by
FROM comments cm
INNER JOIN profile pr USING (userid)
INNER JOIN login lo USING (userid)
WHERE cm.target_sub = %d
""" % (submitid,)]
else:
statement = ["""
SELECT
cm.commentid, cm.parentid, cm.userid, pr.username, lo.settings, cm.content, cm.unixtime, cm.settings,
cm.indent, pr.config, cm.hidden_by
FROM %scomment cm
INNER JOIN profile pr USING (userid)
INNER JOIN login lo USING (userid)
WHERE cm.targetid = %i
""" % ("submit" if submitid else "char" if charid else "journal", d.get_targetid(submitid, charid, journalid))]
# moderators get to view hidden comments
if userid not in staff.MODS:
statement.append(" AND cm.settings !~ 'h'")
if userid:
statement.append(m.MACRO_IGNOREUSER % (userid, "cm"))
statement.append(" ORDER BY COALESCE(cm.parentid, 0), cm.unixtime")
query = d.execute("".join(statement))
for i, comment in enumerate(query):
if comment[1]:
break
result.append({
"commentid": comment[0],
"parentid": comment[1],
"userid": comment[2],
"username": comment[3],
"status": "".join({"b", "s"} & set(comment[4])),
"content": comment[5],
"unixtime": comment[6],
"settings": comment[7],
"indent": comment[8],
"hidden": 'h' in comment[7],
"hidden_by": comment[10],
})
_thread(query, result, i)
media.populate_with_user_media(result)
return result
def select_user_list(userid,
rating,
limit,
otherid=None,
backid=None,
nextid=None,
config=None):
if config is None:
config = d.get_config(userid)
statement = [
"SELECT jo.journalid, jo.title, jo.userid, pr.username, pr.config, jo.rating, jo.unixtime"
" FROM journal jo"
" JOIN profile pr ON jo.userid = pr.userid"
" WHERE jo.settings !~ 'h'"
]
if otherid:
statement.append(" AND jo.userid = %i")
if userid:
# filter own content in SFW mode
if d.is_sfw_mode():
statement.append(" AND (jo.rating <= %i)" % (rating, ))
else:
statement.append(" AND (jo.userid = %i OR jo.rating <= %i)" %
(userid, rating))
statement.append(m.MACRO_FRIENDUSER_JOURNAL % (userid, userid, userid))
if not otherid:
statement.append(m.MACRO_IGNOREUSER % (userid, "jo"))
statement.append(m.MACRO_BLOCKTAG_JOURNAL % (userid, userid))
else:
statement.append(" AND jo.rating <= %i AND jo.settings !~ 'f'" %
(rating, ))
if backid:
statement.append(" AND jo.journalid > %i" % backid)
elif nextid:
statement.append(" AND jo.journalid < %i" % nextid)
statement.append(" ORDER BY jo.journalid%s LIMIT %i" %
("" if backid else " DESC", limit))
query = [{
"contype": 30,
"journalid": i[0],
"title": i[1],
"userid": i[2],
"username": i[3],
"rating": i[5],
"unixtime": i[6],
} for i in d.execute("".join(statement))]
media.populate_with_user_media(query)
return query[::-1] if backid else query
def select_friends(userid,
otherid,
limit=None,
backid=None,
nextid=None,
choose=False):
"""
Return accepted friends. If `choose` is an integer, results will be ordered
randomly.
"""
fr = d.meta.tables['frienduser']
pr = d.meta.tables['profile']
iu = d.meta.tables['ignoreuser']
friends = d.sa.union(
(d.sa.select([fr.c.otherid, pr.c.username, pr.c.config]).select_from(
fr.join(pr, fr.c.otherid == pr.c.userid)).where(
d.sa.and_(fr.c.userid == otherid,
fr.c.settings.op('!~')('p')))),
(d.sa.select([fr.c.userid, pr.c.username, pr.c.config]).select_from(
fr.join(pr, fr.c.userid == pr.c.userid)).where(
d.sa.and_(fr.c.otherid == otherid,
fr.c.settings.op('!~')('p')))))
friends = friends.alias('friends')
query = d.sa.select(friends.c)
if userid:
query = query.where(~friends.c.otherid.in_(
d.sa.select([iu.c.otherid]).where(iu.c.userid == userid)))
if backid:
query = query.where(
friends.c.username < d.sa.select([pr.c.username]).where(
pr.c.userid == backid))
elif nextid:
query = query.where(
friends.c.username > d.sa.select([pr.c.username]).where(
pr.c.userid == nextid))
if choose:
query = query.order_by('RANDOM()')
else:
query = query.order_by(
friends.c.username.desc() if backid else friends.c.username.asc())
query = query.limit(limit)
db = d.connect()
query = [{
"userid": r.otherid,
"username": r.username,
} for r in db.execute(query)]
ret = (d.get_random_set(query, choose)
if choose else query[::-1] if backid else query)
media.populate_with_user_media(ret)
return ret
def select_avatars(userids):
if not userids:
return {}
results = d.engine.execute(
'SELECT userid, username FROM profile WHERE userid = ANY (%(users)s)',
users=userids)
results = [dict(row) for row in results]
media.populate_with_user_media(results)
return {d['userid']: d for d in results}
def select_avatars(userids):
if not userids:
return {}
results = d.engine.execute(
'SELECT userid, username FROM profile WHERE userid = ANY (%(users)s)',
users=userids)
results = [dict(row) for row in results]
media.populate_with_user_media(results)
return {d['userid']: d for d in results}
def select_journal(userid, rating, limit, otherid, backid=None, nextid=None):
statement = [
"""
SELECT jo.journalid, jo.title, jo.rating, fa.unixtime, jo.userid, pr.username, pr.config
FROM favorite fa
INNER JOIN journal jo ON fa.targetid = jo.journalid
INNER JOIN profile pr ON jo.userid = pr.userid
WHERE fa.type = 'j'
AND jo.settings !~ 'h'
"""
]
if userid:
# filter own content in SFW mode
if d.is_sfw_mode():
statement.append(" AND (jo.rating <= %i)" % (rating, ))
else:
statement.append(" AND (jo.userid = %i OR jo.rating <= %i)" %
(userid, rating))
statement.append(m.MACRO_FRIENDUSER_JOURNAL % (userid, userid, userid))
statement.append(m.MACRO_IGNOREUSER % (userid, "jo"))
statement.append(m.MACRO_BLOCKTAG_JOURNAL % (userid, userid))
else:
statement.append(" AND jo.rating <= %i AND jo.settings !~ 'f'" %
(rating, ))
statement.append(" AND fa.userid = %i" % (otherid, ))
if backid:
statement.append(
" AND fa.unixtime > "
"(SELECT unixtime FROM favorite WHERE (userid, targetid, type) = (%i, %i, 'j'))"
% (otherid, backid))
elif nextid:
statement.append(
" AND fa.unixtime < "
"(SELECT unixtime FROM favorite WHERE (userid, targetid, type) = (%i, %i, 'j'))"
% (otherid, nextid))
statement.append(" ORDER BY fa.unixtime%s LIMIT %i" %
("" if backid else " DESC", limit))
query = [{
"contype": 30,
"journalid": i[0],
"title": i[1],
"rating": i[2],
"unixtime": i[3],
"userid": i[4],
"username": i[5],
} for i in d.execute("".join(statement))]
media.populate_with_user_media(query)
return query[::-1] if backid else query
def select(userid, submitid=None, charid=None, journalid=None, updateid=None):
is_hidden = "cm.settings ~ 'h'"
if submitid:
statement = [
"""
SELECT
cm.commentid, cm.parentid, cm.userid, pr.username,
cm.content, cm.unixtime, cm.settings ~ 'h', cm.hidden_by
FROM comments cm
INNER JOIN profile pr USING (userid)
WHERE cm.target_sub = %d
""" % (submitid, )
]
else:
unixtime = "cm.unixtime"
if charid:
table = "charcomment"
elif journalid:
table = "journalcomment"
elif updateid:
table = "siteupdatecomment"
is_hidden = "cm.hidden_at IS NOT NULL"
unixtime = "EXTRACT(EPOCH FROM cm.created_at)::int8 + %i" % (
UNIXTIME_OFFSET, )
else:
raise WeasylError("Unexpected")
statement = [
"""
SELECT
cm.commentid, cm.parentid, cm.userid, pr.username,
cm.content, %s, %s, cm.hidden_by
FROM %s cm
INNER JOIN profile pr USING (userid)
WHERE cm.targetid = %i
""" % (unixtime, is_hidden, table,
d.get_targetid(submitid, charid, journalid, updateid))
]
# moderators get to view hidden comments
if userid not in staff.MODS:
statement.append(" AND NOT (%s)" % (is_hidden, ))
if userid:
statement.append(m.MACRO_IGNOREUSER % (userid, "cm"))
statement.append(" ORDER BY cm.commentid")
query = d.execute("".join(statement))
result = thread(query, reverse_top_level=False)
media.populate_with_user_media(result)
return result
def select_avatars(userids):
if not userids:
return {}
results = d.execute(
"SELECT userid, username, config FROM profile pr WHERE userid IN %s" %
(d.sql_number_list(userids), ))
results = [{
"username": username,
"userid": userid,
} for userid, username, config in results]
media.populate_with_user_media(results)
return {d['userid']: d for d in results}
def select_requests(userid):
query = d.execute("SELECT fr.userid, pr.username, fr.settings FROM frienduser fr"
" INNER JOIN profile pr ON fr.userid = pr.userid"
" WHERE fr.otherid = %i AND fr.settings ~ 'p'", [userid])
ret = [{
"userid": i[0],
"username": i[1],
"settings": i[2],
} for i in query]
media.populate_with_user_media(ret)
return ret
def select_requests(userid, limit=None, backid=None, nextid=None):
query = d.execute("SELECT fr.userid, pr.username, pr.config, fr.settings FROM frienduser fr"
" INNER JOIN profile pr ON fr.userid = pr.userid"
" WHERE fr.otherid = %i AND fr.settings ~ 'p'", [userid])
ret = [{
"userid": i[0],
"username": i[1],
"settings": i[3],
} for i in query]
media.populate_with_user_media(ret)
return ret
def select_journal(userid, rating, limit, otherid=None, backid=None, nextid=None, config=None):
if config is None:
config = d.get_config(userid)
query = []
statement = ["""
SELECT jo.journalid, jo.title, jo.rating, fa.unixtime, jo.userid, pr.username, pr.config
FROM favorite fa
INNER JOIN journal jo ON fa.targetid = jo.journalid
INNER JOIN profile pr ON jo.userid = pr.userid
WHERE fa.type = 'j'
AND jo.settings !~ 'h'
"""]
if userid:
# filter own content in SFW mode
if d.is_sfw_mode():
statement.append(" AND (jo.rating <= %i)" % (rating,))
else:
statement.append(" AND (jo.userid = %i OR jo.rating <= %i)" % (userid, rating))
statement.append(m.MACRO_FRIENDUSER_JOURNAL % (userid, userid, userid))
statement.append(m.MACRO_IGNOREUSER % (userid, "jo"))
statement.append(m.MACRO_BLOCKTAG_JOURNAL % (userid, userid))
else:
statement.append(" AND jo.rating <= %i AND jo.settings !~ 'f'" % (rating,))
if otherid:
statement.append(" AND fa.userid = %i" % (otherid,))
if backid:
statement.append(" AND fa.unixtime > "
"(SELECT unixtime FROM favorite WHERE (userid, targetid, type) = (%i, %i, 'j'))"
% (otherid, backid))
elif nextid:
statement.append(" AND fa.unixtime < "
"(SELECT unixtime FROM favorite WHERE (userid, targetid, type) = (%i, %i, 'j'))"
% (otherid, nextid))
statement.append(" ORDER BY fa.unixtime%s LIMIT %i" % ("" if backid else " DESC", limit))
query = [{
"contype": 30,
"journalid": i[0],
"title": i[1],
"rating": i[2],
"unixtime": i[3],
"userid": i[4],
"username": i[5],
} for i in d.execute("".join(statement))]
media.populate_with_user_media(query)
return query[::-1] if backid else query
def select(userid, ownerid, limit=None, start=None, staffnotes=False):
result = []
statement = ["""
SELECT sh.commentid, sh.parentid, sh.userid, pr.username, lo.settings, sh.content, sh.unixtime,
sh.settings, sh.indent, pr.config, sh.hidden_by
FROM comments sh
INNER JOIN profile pr ON sh.userid = pr.userid
INNER JOIN login lo ON sh.userid = lo.userid
WHERE sh.target_user = %i
AND sh.settings %s~ 's'
""" % (ownerid, "" if staffnotes else "!")]
# moderators get to view hidden comments
if userid not in staff.MODS:
statement.append(" AND sh.settings !~ 'h'")
if userid:
statement.append(m.MACRO_IGNOREUSER % (userid, "sh"))
statement.append(" ORDER BY COALESCE(sh.parentid, 0), sh.unixtime")
query = d.execute("".join(statement))
for i in range(len(query) - 1, -1, -1):
if not query[i][1]:
result.append({
"commentid": query[i][0],
"parentid": query[i][1],
"userid": query[i][2],
"username": query[i][3],
"status": "".join(i for i in query[i][4] if i in "bs"),
"content": query[i][5],
"unixtime": query[i][6],
"settings": query[i][7],
"indent": query[i][8],
"hidden": 'h' in query[i][7],
"hidden_by": query[i][10],
})
_thread(query, result, i)
if limit:
if start:
ret = result[start:start + limit]
else:
ret = result[:limit]
else:
ret = result
media.populate_with_user_media(ret)
return ret
def select(**kwargs):
search = kwargs['search']
results, next_count, back_count = _find_without_media(**kwargs)
if search.find == 'submit':
media.populate_with_submission_media(results)
elif search.find == 'char':
for r in results:
r['sub_media'] = character.fake_media_items(
r['charid'], r['userid'], d.get_sysname(r['username']), r['settings'])
elif search.find == 'journal':
media.populate_with_user_media(results)
return results, next_count, back_count
def select_avatars(userids):
if not userids:
return {}
results = d.execute(
"SELECT userid, username, config FROM profile pr WHERE userid IN %s" % (d.sql_number_list(userids),))
results = [
{
"username": username,
"userid": userid,
}
for userid, username, config in results]
media.populate_with_user_media(results)
return {d['userid']: d for d in results}
def select(**kwargs):
search = kwargs['search']
results, next_count, back_count = _find_without_media(**kwargs)
if search.find == 'submit':
media.populate_with_submission_media(results)
elif search.find == 'char':
for r in results:
r['sub_media'] = character.fake_media_items(
r['charid'], r['userid'], d.get_sysname(r['username']), r['settings'])
elif search.find == 'journal':
media.populate_with_user_media(results)
return results, next_count, back_count
def select_view(userid, form):
report = (Report.query.options(
joinedload('comments', innerjoin=True).joinedload(
'poster', innerjoin=True)).get_or_404(int(form.reportid)))
report.old_style_comments = [{
'userid': c.userid,
'username': c.poster.profile.username,
'unixtime': c.unixtime,
'content': c.content,
'violation': _convert_violation(c.violation),
} for c in report.comments]
media.populate_with_user_media(report.old_style_comments)
report.old_style_comments.sort(key=lambda c: c['unixtime'])
return report
def select_followed(userid,
otherid,
limit=None,
backid=None,
nextid=None,
choose=None,
following=False):
"""
Returns the users who are following the specified user; note that
``following`` need never be passed explicitly.
"""
if following:
statement = [
"SELECT wu.otherid, pr.username, pr.config FROM watchuser wu"
" INNER JOIN profile pr ON wu.otherid = pr.userid"
" WHERE wu.userid = %i" % (otherid, )
]
else:
statement = [
"SELECT wu.userid, pr.username, pr.config FROM watchuser wu"
" INNER JOIN profile pr ON wu.userid = pr.userid"
" WHERE wu.otherid = %i" % (otherid, )
]
if userid:
statement.append(m.MACRO_IGNOREUSER % (userid, "pr"))
if backid:
statement.append(
" AND pr.username < (SELECT username FROM profile WHERE userid = %i)"
% (backid, ))
elif nextid:
statement.append(
" AND pr.username > (SELECT username FROM profile WHERE userid = %i)"
% (nextid, ))
if choose:
statement.append(" ORDER BY RANDOM() LIMIT %i" % (choose, ))
else:
statement.append(" ORDER BY pr.username%s LIMIT %i" %
(" DESC" if backid else "", limit))
query = [{
"userid": i[0],
"username": i[1],
} for i in d.execute("".join(statement))]
media.populate_with_user_media(query)
return query[::-1] if backid else query
def select(userid, submitid=None, charid=None, journalid=None, updateid=None):
is_hidden = "cm.settings ~ 'h'"
if submitid:
statement = ["""
SELECT
cm.commentid, cm.parentid, cm.userid, pr.username,
cm.content, cm.unixtime, cm.settings ~ 'h', cm.hidden_by
FROM comments cm
INNER JOIN profile pr USING (userid)
WHERE cm.target_sub = %d
""" % (submitid,)]
else:
unixtime = "cm.unixtime"
if submitid:
table = "submitcomment"
elif charid:
table = "charcomment"
elif journalid:
table = "journalcomment"
elif updateid:
table = "siteupdatecomment"
is_hidden = "cm.hidden_at IS NOT NULL"
unixtime = "EXTRACT(EPOCH FROM cm.created_at)::int8 + %i" % (UNIXTIME_OFFSET,)
else:
raise WeasylError("Unexpected")
statement = ["""
SELECT
cm.commentid, cm.parentid, cm.userid, pr.username,
cm.content, %s, %s, cm.hidden_by
FROM %s cm
INNER JOIN profile pr USING (userid)
WHERE cm.targetid = %i
""" % (unixtime, is_hidden, table, d.get_targetid(submitid, charid, journalid, updateid))]
# moderators get to view hidden comments
if userid not in staff.MODS:
statement.append(" AND NOT (%s)" % (is_hidden,))
if userid:
statement.append(m.MACRO_IGNOREUSER % (userid, "cm"))
statement.append(" ORDER BY cm.commentid")
query = d.execute("".join(statement))
result = thread(query, reverse_top_level=False)
media.populate_with_user_media(result)
return result
def select(userid,
otherid,
limit=None,
pending=False,
backid=None,
nextid=None,
choose=None,
gotrequest=False):
query = []
if gotrequest:
statement = [
"SELECT fr.otherid, pr.username, fr.settings, pr.config FROM frienduser fr"
" INNER JOIN profile pr ON fr.otherid = pr.userid"
" WHERE fr.otherid = %i AND fr.settings ~ 'p'" % (userid, )
]
else:
statement = [
"SELECT fr.otherid, pr.username, fr.settings, pr.config FROM frienduser fr"
" INNER JOIN profile pr ON fr.otherid = pr.userid"
" WHERE fr.userid = %i AND fr.settings %s~ 'p'" %
(otherid, "" if pending else "!")
]
if backid:
statement.append(
" AND pr.username < (SELECT username FROM profile WHERE userid = %i)"
% (backid, ))
elif nextid:
statement.append(
" AND pr.username > (SELECT username FROM profile WHERE userid = %i)"
% (nextid, ))
statement.append(" ORDER BY pr.username" + (" DESC" if nextid else ""))
if limit:
statement.append(" LIMIT %i" % (limit, ))
for i in d.execute("".join(statement)):
query.append({
"userid": i[0],
"username": i[1],
"settings": i[2],
})
ret = (d.get_random_set(query, choose)
if choose else query[::-1] if backid else query)
media.populate_with_user_media(ret)
return ret
def select_friends(userid, otherid, limit=None, backid=None, nextid=None, choose=False):
"""
Return accepted friends. If `choose` is an integer, results will be ordered
randomly.
"""
fr = d.meta.tables['frienduser']
pr = d.meta.tables['profile']
iu = d.meta.tables['ignoreuser']
friends = d.sa.union(
(d.sa
.select([fr.c.otherid, pr.c.username, pr.c.config])
.select_from(fr.join(pr, fr.c.otherid == pr.c.userid))
.where(d.sa.and_(fr.c.userid == otherid, fr.c.settings.op('!~')('p')))),
(d.sa
.select([fr.c.userid, pr.c.username, pr.c.config])
.select_from(fr.join(pr, fr.c.userid == pr.c.userid))
.where(d.sa.and_(fr.c.otherid == otherid, fr.c.settings.op('!~')('p')))))
friends = friends.alias('friends')
query = d.sa.select(friends.c)
if userid:
query = query.where(
~friends.c.otherid.in_(d.sa.select([iu.c.otherid]).where(iu.c.userid == userid)))
if backid:
query = query.where(
friends.c.username < d.sa.select([pr.c.username]).where(pr.c.userid == backid))
elif nextid:
query = query.where(
friends.c.username > d.sa.select([pr.c.username]).where(pr.c.userid == nextid))
if choose:
query = query.order_by('RANDOM()')
else:
query = query.order_by(
friends.c.username.desc() if backid else friends.c.username.asc())
query = query.limit(limit)
db = d.connect()
query = [{
"userid": r.otherid,
"username": r.username,
} for r in db.execute(query)]
ret = (d.get_random_set(query, choose) if choose else query[::-1] if backid else query)
media.populate_with_user_media(ret)
return ret
def select_view(userid, form):
report = (
Report.query
.options(joinedload('comments', innerjoin=True).joinedload('poster', innerjoin=True))
.get_or_404(int(form.reportid)))
report.old_style_comments = [
{
'userid': c.userid,
'username': c.poster.profile.username,
'unixtime': c.unixtime,
'content': c.content,
'violation': _convert_violation(c.violation),
} for c in report.comments]
media.populate_with_user_media(report.old_style_comments)
report.old_style_comments.sort(key=lambda c: c['unixtime'])
return report
def select(userid, ownerid, limit=None, staffnotes=False):
result = []
statement = ["""
SELECT sh.commentid, sh.parentid, sh.userid, pr.username, lo.settings, sh.content, sh.unixtime,
sh.settings, sh.indent, pr.config, sh.hidden_by
FROM comments sh
INNER JOIN profile pr ON sh.userid = pr.userid
INNER JOIN login lo ON sh.userid = lo.userid
WHERE sh.target_user = %i
AND sh.settings %s~ 's'
""" % (ownerid, "" if staffnotes else "!")]
# moderators get to view hidden comments
if userid not in staff.MODS:
statement.append(" AND sh.settings !~ 'h'")
if userid:
statement.append(m.MACRO_IGNOREUSER % (userid, "sh"))
statement.append(" ORDER BY COALESCE(sh.parentid, 0), sh.unixtime")
query = d.execute("".join(statement))
for i in range(len(query) - 1, -1, -1):
if not query[i][1]:
result.append({
"commentid": query[i][0],
"parentid": query[i][1],
"userid": query[i][2],
"username": query[i][3],
"status": "".join(c for c in query[i][4] if c in "bs"),
"content": query[i][5],
"unixtime": query[i][6],
"settings": query[i][7],
"indent": query[i][8],
"hidden": 'h' in query[i][7],
"hidden_by": query[i][10],
})
_thread(query, result, i)
if limit:
ret = result[:limit]
else:
ret = result
media.populate_with_user_media(ret)
return ret
def select_user_list(userid, rating, limit, otherid=None, backid=None, nextid=None, config=None):
if config is None:
config = d.get_config(userid)
statement = [
"SELECT jo.journalid, jo.title, jo.userid, pr.username, pr.config, jo.rating, jo.unixtime"
" FROM journal jo"
" JOIN profile pr ON jo.userid = pr.userid"
" WHERE jo.settings !~ 'h'"]
if otherid:
statement.append(" AND jo.userid = %i")
if userid:
# filter own content in SFW mode
if d.is_sfw_mode():
statement.append(" AND (jo.rating <= %i)" % (rating,))
else:
statement.append(" AND (jo.userid = %i OR jo.rating <= %i)" % (userid, rating))
statement.append(m.MACRO_FRIENDUSER_JOURNAL % (userid, userid, userid))
if not otherid:
statement.append(m.MACRO_IGNOREUSER % (userid, "jo"))
statement.append(m.MACRO_BLOCKTAG_JOURNAL % (userid, userid))
else:
statement.append(" AND jo.rating <= %i AND jo.settings !~ 'f'" % (rating,))
if backid:
statement.append(" AND jo.journalid > %i" % backid)
elif nextid:
statement.append(" AND jo.journalid < %i" % nextid)
statement.append(" ORDER BY jo.journalid%s LIMIT %i" % ("" if backid else " DESC", limit))
query = [{
"contype": 30,
"journalid": i[0],
"title": i[1],
"userid": i[2],
"username": i[3],
"rating": i[5],
"unixtime": i[6],
} for i in d.execute("".join(statement))]
media.populate_with_user_media(query)
return query[::-1] if backid else query
def select_user_list(userid, rating, limit, otherid=None, backid=None, nextid=None, config=None):
statement = ["SELECT jo.journalid, jo.title, jo.userid, pr.username, pr.config, jo.rating, jo.unixtime"]
statement.extend(select_query(userid, rating, otherid, backid, nextid, config))
statement.append(" ORDER BY jo.journalid%s LIMIT %i" % ("" if backid else " DESC", limit))
query = [{
"contype": 30,
"journalid": i[0],
"title": i[1],
"userid": i[2],
"username": i[3],
"rating": i[5],
"unixtime": i[6],
} for i in d.execute("".join(statement))]
media.populate_with_user_media(query)
return query[::-1] if backid else query
def select_user_list(userid, rating, limit, otherid=None, backid=None, nextid=None, config=None):
statement = ["SELECT jo.journalid, jo.title, jo.userid, pr.username, pr.config, jo.rating, jo.unixtime"]
statement.extend(select_query(userid, rating, otherid, backid, nextid, config))
statement.append(" ORDER BY jo.journalid%s LIMIT %i" % ("" if backid else " DESC", limit))
query = [{
"contype": 30,
"journalid": i[0],
"title": i[1],
"userid": i[2],
"username": i[3],
"rating": i[5],
"unixtime": i[6],
} for i in d.execute("".join(statement))]
media.populate_with_user_media(query)
return query[::-1] if backid else query
def select(limit=1):
ret = [{
"updateid": i[0],
"userid": i[1],
"username": i[2],
"title": i[3],
"content": i[4],
"unixtime": i[5],
} for i in d.execute("""
SELECT up.updateid, up.userid, pr.username, up.title, up.content, up.unixtime, pr.config
FROM siteupdate up
INNER JOIN profile pr USING (userid)
ORDER BY updateid DESC
LIMIT %i
""", [limit])]
media.populate_with_user_media(ret)
return ret
def select(limit=1):
ret = [{
"updateid": i[0],
"userid": i[1],
"username": i[2],
"title": i[3],
"content": i[4],
"unixtime": i[5],
} for i in d.execute(
"""
SELECT up.updateid, up.userid, pr.username, up.title, up.content, up.unixtime, pr.config
FROM siteupdate up
INNER JOIN profile pr USING (userid)
ORDER BY updateid DESC
LIMIT %i
""", [limit])]
media.populate_with_user_media(ret)
return ret
def select_users(q):
terms = q.lower().split()
statement = """
SELECT userid, full_name, unixtime, username FROM profile
WHERE LOWER(username) SIMILAR TO ('%%(' || %(terms)s || ')%%') ESCAPE ''
OR LOWER(full_name) SIMILAR TO ('%%(' || %(terms)s || ')%%') ESCAPE ''
ORDER BY username
LIMIT 100
"""
query = d.engine.execute(statement, terms="|".join(terms))
ret = [{
"contype": 50,
"userid": i.userid,
"title": i.full_name,
"rating": "",
"unixtime": i.unixtime,
"username": i.username,
} for i in query]
media.populate_with_user_media(ret)
return ret
def select_users(q):
terms = q.lower().split()
statement = """
SELECT userid, full_name, unixtime, username FROM profile
WHERE LOWER(username) SIMILAR TO ('%%(' || %(terms)s || ')%%') ESCAPE ''
OR LOWER(full_name) SIMILAR TO ('%%(' || %(terms)s || ')%%') ESCAPE ''
ORDER BY username
LIMIT 100
"""
query = d.engine.execute(statement, terms="|".join(terms))
ret = [{
"contype": 50,
"userid": i.userid,
"title": i.full_name,
"rating": "",
"unixtime": i.unixtime,
"username": i.username,
} for i in query]
media.populate_with_user_media(ret)
return ret
def select(userid, submitid=None, charid=None, journalid=None):
result = []
if submitid:
statement = [
"""
SELECT
cm.commentid, cm.parentid, cm.userid, pr.username, lo.settings,
cm.content, cm.unixtime, cm.settings, cm.indent, pr.config,
cm.hidden_by
FROM comments cm
INNER JOIN profile pr USING (userid)
INNER JOIN login lo USING (userid)
WHERE cm.target_sub = %d
""" % (submitid, )
]
else:
statement = [
"""
SELECT
cm.commentid, cm.parentid, cm.userid, pr.username, lo.settings, cm.content, cm.unixtime, cm.settings,
cm.indent, pr.config, cm.hidden_by
FROM %scomment cm
INNER JOIN profile pr USING (userid)
INNER JOIN login lo USING (userid)
WHERE cm.targetid = %i
""" % ("submit" if submitid else "char" if charid else "journal",
d.get_targetid(submitid, charid, journalid))
]
# moderators get to view hidden comments
if userid not in staff.MODS:
statement.append(" AND cm.settings !~ 'h'")
if userid:
statement.append(m.MACRO_IGNOREUSER % (userid, "cm"))
statement.append(" ORDER BY COALESCE(cm.parentid, 0), cm.unixtime")
query = d.execute("".join(statement))
for i, comment in enumerate(query):
if comment[1]:
break
result.append({
"commentid": comment[0],
"parentid": comment[1],
"userid": comment[2],
"username": comment[3],
"status": "".join({"b", "s"} & set(comment[4])),
"content": comment[5],
"unixtime": comment[6],
"settings": comment[7],
"indent": comment[8],
"hidden": 'h' in comment[7],
"hidden_by": comment[10],
})
_thread(query, result, i)
media.populate_with_user_media(result)
return result
def select(userid, rating, limit,
q, find, within, rated, cat, subcat, backid, nextid):
search = Query.parse(q)
search.ratings.update([_rating_codes[r] for r in rated])
if not search._find:
search._find = find
if search.find == "user":
terms = q.lower().split()
statement = """
SELECT userid, full_name, unixtime, username FROM profile
WHERE LOWER(username) SIMILAR TO ('%%(' || %(terms)s || ')%%') ESCAPE ''
OR LOWER(full_name) SIMILAR TO ('%%(' || %(terms)s || ')%%') ESCAPE ''
ORDER BY username
LIMIT 100
"""
query = d.engine.execute(statement, terms="|".join(terms))
ret = [{
"contype": 50,
"userid": i.userid,
"title": i.full_name,
"rating": "",
"unixtime": i.unixtime,
"username": i.username,
} for i in query]
media.populate_with_user_media(ret)
return ret, 0, 0
type_code, type_letter, table, select, subtype = _table_information[search.find]
search_dict = search.to_dict()
if not any(search_dict.values()):
raise web.seeother("/search?type=" + search.find)
# Begin statement
statement_from = ["FROM {table} content INNER JOIN profile ON content.userid = profile.userid"]
statement_where = ["WHERE content.rating <= %(rating)s AND content.settings !~ '[fhm]'"]
statement_group = []
if search.required_includes:
statement_from.append("INNER JOIN searchmap{find} ON targetid = content.{select}")
statement_from.append("INNER JOIN searchtag ON searchmap{find}.tagid = searchtag.tagid")
statement_where.append("AND searchtag.title = ANY (%(required_includes)s)")
statement_group.append(
"GROUP BY content.{select}, profile.username HAVING COUNT(searchtag.tagid) = %(required_include_count)s")
# Submission category or subcategory
if search.find == "submit":
if subcat:
statement_where.append("AND content.subtype = %(subcategory)s")
elif cat:
statement_where.append("AND content.subtype >= %(category)s AND content.subtype < %(category)s + 1000")
if userid:
if within == "notify":
# Search within notifications
statement_from.append("INNER JOIN welcome ON welcome.targetid = content.{select}")
statement_where.append("AND welcome.userid = %(userid)s")
statement_where.append({
"submit": "AND welcome.type IN (2010, 2030, 2040)",
"char": "AND welcome.type = 2050",
"journal": "AND welcome.type IN (1010, 1020)",
}[search.find])
elif within == "fave":
# Search within favorites
statement_from.append("INNER JOIN favorite ON favorite.targetid = content.{select}")
statement_where.append("AND favorite.userid = %(userid)s AND favorite.type = %(type)s")
elif within == "friend":
# Search within friends content
statement_from.append(
"INNER JOIN frienduser ON (frienduser.userid, frienduser.otherid) = (%(userid)s, content.userid)"
" OR (frienduser.userid, frienduser.otherid) = (content.userid, %(userid)s)")
elif within == "follow":
# Search within following content
statement_from.append(
"INNER JOIN watchuser ON (watchuser.userid, watchuser.otherid) = (%(userid)s, content.userid)")
# Search within rating
if userid and search.ratings:
statement_where.append("AND content.rating = ANY (%(ratings)s)")
# Blocked tags and ignored users
if userid:
statement_where.append("""
AND NOT EXISTS (
SELECT 0 FROM ignoreuser
WHERE userid = %(userid)s
AND otherid = content.userid)
AND NOT EXISTS (
SELECT 0 FROM searchmap{find}
WHERE targetid = content.{select}
AND tagid IN (SELECT tagid FROM blocktag WHERE userid = %(userid)s AND rating <= content.rating))
""")
if search.possible_includes:
statement_where.append("""
AND EXISTS (
SELECT 0 FROM searchmap{find}
WHERE targetid = content.{select}
AND tagid IN (SELECT tagid FROM searchtag WHERE title = ANY (%(possible_includes)s))
)
""")
if search.required_excludes:
statement_where.append("""
AND NOT EXISTS (
SELECT 0 FROM searchmap{find}
WHERE targetid = content.{select}
AND tagid IN (
SELECT tagid FROM searchtag
WHERE title = ANY (%(required_excludes)s)
)
)
""")
if search.required_user_includes:
statement_from.append("INNER JOIN login login_include ON content.userid = login_include.userid")
statement_where.append("AND login_include.login_name = ANY (%(required_user_includes)s)")
if search.required_user_excludes:
statement_from.append("INNER JOIN login login_exclude ON content.userid = login_exclude.userid")
statement_where.append("AND login_exclude.login_name != ALL (%(required_user_excludes)s)")
def make_statement(statement_select, statement_additional_where, statement_order):
return " ".join([
statement_select,
" ".join(statement_from),
" ".join(statement_where),
statement_additional_where,
" ".join(statement_group),
statement_order,
]).format(
table=table,
find=search.find,
select=select,
subtype=subtype,
title_field="char_name" if search.find == "char" else "title"
)
pagination_filter = (
"AND content.{select} > %(backid)s" if backid else
"AND content.{select} < %(nextid)s" if nextid else
"")
statement = make_statement(
"""
SELECT
content.{select}, content.{title_field} AS title, content.rating, content.unixtime, content.userid,
content.settings, profile.username, {subtype} as subtype
""",
pagination_filter,
"ORDER BY content.{{select}} {order} LIMIT %(limit)s".format(order="" if backid else "DESC"))
params = dict(
search_dict,
type=type_letter,
userid=userid,
rating=rating,
ratings=list(search.ratings),
category=cat,
subcategory=subcat,
limit=limit,
backid=backid,
nextid=nextid,
required_include_count=len(search.required_includes))
query = d.engine.execute(statement, **params)
ret = [{
"contype": type_code,
select: i[select],
"title": i.title,
"subtype": i.subtype,
"rating": i.rating,
"unixtime": i.unixtime,
"userid": i.userid,
"username": i.username,
"settings": i.settings,
} for i in query]
if search.find == "submit":
media.populate_with_submission_media(ret)
elif search.find == "char":
for r in ret:
r["sub_media"] = character.fake_media_items(
r["charid"], r["userid"], d.get_sysname(r["username"]), r["settings"])
elif search.find == "journal":
media.populate_with_user_media(ret)
if backid:
back_count = d.engine.execute(
make_statement("SELECT COUNT(*) FROM (SELECT 1", pagination_filter, ") _"), **params).scalar() - len(ret)
elif nextid:
back_count = (d.engine.execute(
make_statement("SELECT COUNT(*) FROM (SELECT 1", "AND content.{select} >= %(nextid)s", ") _"),
**params).scalar())
else:
back_count = 0
if backid:
next_count = (d.engine.execute(
make_statement("SELECT COUNT(*) FROM (SELECT 1", "AND content.{select} <= %(backid)s", ") _"),
**params).scalar())
return list(reversed(ret)), next_count, back_count
else:
next_count = d.engine.execute(
make_statement("SELECT COUNT(*) FROM (SELECT 1", pagination_filter, ") _"), **params).scalar() - len(ret)
return ret, next_count, back_count
def select(userid, rating, limit, q, find, within, rated, cat, subcat, backid,
nextid):
search = Query.parse(q)
search.ratings.update([_rating_codes[r] for r in rated])
if not search._find:
search._find = find
if search.find == "user":
terms = q.lower().split()
statement = """
SELECT userid, full_name, unixtime, username FROM profile
WHERE LOWER(username) SIMILAR TO ('%%(' || %(terms)s || ')%%') ESCAPE ''
OR LOWER(full_name) SIMILAR TO ('%%(' || %(terms)s || ')%%') ESCAPE ''
ORDER BY username
LIMIT 100
"""
query = d.engine.execute(statement, terms="|".join(terms))
ret = [{
"contype": 50,
"userid": i.userid,
"title": i.full_name,
"rating": "",
"unixtime": i.unixtime,
"username": i.username,
} for i in query]
media.populate_with_user_media(ret)
return ret, 0, 0
type_code, type_letter, table, select, subtype = _table_information[
search.find]
search_dict = search.to_dict()
if not any(search_dict.values()):
raise web.seeother("/search?type=" + search.find)
# Begin statement
statement_from = [
"FROM {table} content INNER JOIN profile ON content.userid = profile.userid"
]
statement_where = [
"WHERE content.rating <= %(rating)s AND content.settings !~ '[fhm]'"
]
statement_group = []
if search.required_includes:
statement_from.append(
"INNER JOIN searchmap{find} ON targetid = content.{select}")
statement_from.append(
"INNER JOIN searchtag ON searchmap{find}.tagid = searchtag.tagid")
statement_where.append(
"AND searchtag.title = ANY (%(required_includes)s)")
statement_group.append(
"GROUP BY content.{select}, profile.username HAVING COUNT(searchtag.tagid) = %(required_include_count)s"
)
# Submission category or subcategory
if search.find == "submit":
if subcat:
statement_where.append("AND content.subtype = %(subcategory)s")
elif cat:
statement_where.append(
"AND content.subtype >= %(category)s AND content.subtype < %(category)s + 1000"
)
if userid:
if within == "notify":
# Search within notifications
statement_from.append(
"INNER JOIN welcome ON welcome.targetid = content.{select}")
statement_where.append("AND welcome.userid = %(userid)s")
statement_where.append({
"submit":
"AND welcome.type IN (2010, 2030, 2040)",
"char":
"AND welcome.type = 2050",
"journal":
"AND welcome.type IN (1010, 1020)",
}[search.find])
elif within == "fave":
# Search within favorites
statement_from.append(
"INNER JOIN favorite ON favorite.targetid = content.{select}")
statement_where.append(
"AND favorite.userid = %(userid)s AND favorite.type = %(type)s"
)
elif within == "friend":
# Search within friends content
statement_from.append(
"INNER JOIN frienduser ON (frienduser.userid, frienduser.otherid) = (%(userid)s, content.userid)"
" OR (frienduser.userid, frienduser.otherid) = (content.userid, %(userid)s)"
)
elif within == "follow":
# Search within following content
statement_from.append(
"INNER JOIN watchuser ON (watchuser.userid, watchuser.otherid) = (%(userid)s, content.userid)"
)
# Search within rating
if userid and search.ratings:
statement_where.append("AND content.rating = ANY (%(ratings)s)")
# Blocked tags and ignored users
if userid:
statement_where.append("""
AND NOT EXISTS (
SELECT 0 FROM ignoreuser
WHERE userid = %(userid)s
AND otherid = content.userid)
AND NOT EXISTS (
SELECT 0 FROM searchmap{find}
WHERE targetid = content.{select}
AND tagid IN (SELECT tagid FROM blocktag WHERE userid = %(userid)s AND rating <= content.rating))
""")
if search.possible_includes:
statement_where.append("""
AND EXISTS (
SELECT 0 FROM searchmap{find}
WHERE targetid = content.{select}
AND tagid IN (SELECT tagid FROM searchtag WHERE title = ANY (%(possible_includes)s))
)
""")
if search.required_excludes:
statement_where.append("""
AND NOT EXISTS (
SELECT 0 FROM searchmap{find}
WHERE targetid = content.{select}
AND tagid IN (
SELECT tagid FROM searchtag
WHERE title = ANY (%(required_excludes)s)
)
)
""")
if search.required_user_includes:
statement_from.append(
"INNER JOIN login login_include ON content.userid = login_include.userid"
)
statement_where.append(
"AND login_include.login_name = ANY (%(required_user_includes)s)")
if search.required_user_excludes:
statement_from.append(
"INNER JOIN login login_exclude ON content.userid = login_exclude.userid"
)
statement_where.append(
"AND login_exclude.login_name != ALL (%(required_user_excludes)s)")
def make_statement(statement_select, statement_additional_where,
statement_order):
return " ".join([
statement_select,
" ".join(statement_from),
" ".join(statement_where),
statement_additional_where,
" ".join(statement_group),
statement_order,
]).format(
table=table,
find=search.find,
select=select,
subtype=subtype,
title_field="char_name" if search.find == "char" else "title")
pagination_filter = ("AND content.{select} > %(backid)s" if backid else
"AND content.{select} < %(nextid)s" if nextid else "")
statement = make_statement(
"""
SELECT
content.{select}, content.{title_field} AS title, content.rating, content.unixtime, content.userid,
content.settings, profile.username, {subtype} as subtype
""", pagination_filter,
"ORDER BY content.{{select}} {order} LIMIT %(limit)s".format(
order="" if backid else "DESC"))
params = dict(search_dict,
type=type_letter,
userid=userid,
rating=rating,
ratings=list(search.ratings),
category=cat,
subcategory=subcat,
limit=limit,
backid=backid,
nextid=nextid,
required_include_count=len(search.required_includes))
query = d.engine.execute(statement, **params)
ret = [{
"contype": type_code,
select: i[select],
"title": i.title,
"subtype": i.subtype,
"rating": i.rating,
"unixtime": i.unixtime,
"userid": i.userid,
"username": i.username,
"settings": i.settings,
} for i in query]
if search.find == "submit":
media.populate_with_submission_media(ret)
elif search.find == "char":
for r in ret:
r["sub_media"] = character.fake_media_items(
r["charid"], r["userid"], d.get_sysname(r["username"]),
r["settings"])
elif search.find == "journal":
media.populate_with_user_media(ret)
if backid:
back_count = d.engine.execute(
make_statement("SELECT COUNT(*) FROM (SELECT 1", pagination_filter,
") _"), **params).scalar() - len(ret)
elif nextid:
back_count = (d.engine.execute(
make_statement("SELECT COUNT(*) FROM (SELECT 1",
"AND content.{select} >= %(nextid)s", ") _"),
**params).scalar())
else:
back_count = 0
if backid:
next_count = (d.engine.execute(
make_statement("SELECT COUNT(*) FROM (SELECT 1",
"AND content.{select} <= %(backid)s", ") _"),
**params).scalar())
return list(reversed(ret)), next_count, back_count
else:
next_count = d.engine.execute(
make_statement("SELECT COUNT(*) FROM (SELECT 1", pagination_filter,
") _"), **params).scalar() - len(ret)
return ret, next_count, back_count
def select(userid, rating, limit, search, within, cat, subcat, backid, nextid):
type_code, type_letter, table, select, subtype = _table_information[
search.find]
# Begin statement
statement_from = [
"FROM {table} content INNER JOIN profile ON content.userid = profile.userid"
]
statement_where = [
"WHERE content.rating <= %(rating)s AND content.settings !~ '[fhm]'"
]
statement_group = []
if search.find == "submit":
statement_from.append(
"INNER JOIN submission_tags ON content.submitid = submission_tags.submitid"
)
if search.required_includes:
if search.find == "submit":
statement_from.append(
"AND submission_tags.tags @> %(required_includes)s")
else:
statement_from.append(
"INNER JOIN searchmap{find} ON targetid = content.{select}")
statement_where.append(
"AND searchmap{find}.tagid = ANY (%(required_includes)s)")
statement_group.append(
"GROUP BY content.{select}, profile.username HAVING COUNT(searchmap{find}.tagid) = %(required_include_count)s"
)
# Submission category or subcategory
if search.find == "submit":
if subcat:
statement_where.append("AND content.subtype = %(subcategory)s")
elif cat:
statement_where.append(
"AND content.subtype >= %(category)s AND content.subtype < %(category)s + 1000"
)
if userid:
if within == "notify":
# Search within notifications
statement_from.append(
"INNER JOIN welcome ON welcome.targetid = content.{select}")
statement_where.append("AND welcome.userid = %(userid)s")
statement_where.append({
"submit":
"AND welcome.type IN (2010, 2030, 2040)",
"char":
"AND welcome.type = 2050",
"journal":
"AND welcome.type IN (1010, 1020)",
}[search.find])
elif within == "fave":
# Search within favorites
statement_from.append(
"INNER JOIN favorite ON favorite.targetid = content.{select}")
statement_where.append(
"AND favorite.userid = %(userid)s AND favorite.type = %(type)s"
)
elif within == "friend":
# Search within friends content
statement_from.append(
"INNER JOIN frienduser ON (frienduser.userid, frienduser.otherid) = (%(userid)s, content.userid)"
" OR (frienduser.userid, frienduser.otherid) = (content.userid, %(userid)s)"
)
elif within == "follow":
# Search within following content
statement_from.append(
"INNER JOIN watchuser ON (watchuser.userid, watchuser.otherid) = (%(userid)s, content.userid)"
)
# Search within rating
if userid and search.ratings:
statement_where.append("AND content.rating = ANY (%(ratings)s)")
# Blocked tags and ignored users
if userid:
statement_where.append("""
AND NOT EXISTS (
SELECT 0 FROM ignoreuser
WHERE userid = %(userid)s
AND otherid = content.userid)
AND NOT EXISTS (
SELECT 0 FROM searchmap{find}
WHERE targetid = content.{select}
AND tagid IN (SELECT tagid FROM blocktag WHERE userid = %(userid)s AND rating <= content.rating))
""")
if search.possible_includes:
if search.find == "submit":
statement_where.append(
"AND submission_tags.tags && %(possible_includes)s")
else:
statement_where.append("""
AND EXISTS (
SELECT 0 FROM searchmap{find}
WHERE targetid = content.{select}
AND tagid = ANY (%(possible_includes)s)
)
""")
if search.required_excludes:
if search.find == "submit":
statement_where.append(
"AND NOT submission_tags.tags && %(required_excludes)s")
else:
statement_where.append("""
AND NOT EXISTS (
SELECT 0 FROM searchmap{find}
WHERE targetid = content.{select}
AND tagid = ANY (%(required_excludes)s)
)
""")
if search.required_user_includes:
statement_from.append(
"INNER JOIN login login_include ON content.userid = login_include.userid"
)
statement_where.append(
"AND login_include.login_name = ANY (%(required_user_includes)s)")
if search.required_user_excludes:
statement_from.append(
"INNER JOIN login login_exclude ON content.userid = login_exclude.userid"
)
statement_where.append(
"AND login_exclude.login_name != ALL (%(required_user_excludes)s)")
def make_statement(statement_select, statement_additional_where,
statement_order):
return " ".join([
statement_select,
" ".join(statement_from),
" ".join(statement_where),
statement_additional_where,
" ".join(statement_group),
statement_order,
]).format(
table=table,
find=search.find,
select=select,
subtype=subtype,
title_field="char_name" if search.find == "char" else "title")
pagination_filter = ("AND content.{select} > %(backid)s" if backid else
"AND content.{select} < %(nextid)s" if nextid else "")
statement = make_statement(
"""
SELECT
content.{select}, content.{title_field} AS title, content.rating, content.unixtime, content.userid,
content.settings, profile.username, {subtype} as subtype
""", pagination_filter,
"ORDER BY content.{{select}} {order} LIMIT %(limit)s".format(
order="" if backid else "DESC"))
all_names = (search.possible_includes | search.required_includes
| search.required_excludes)
tag_ids = searchtag.get_ids(all_names)
def get_ids(names):
return [tag_ids.get(name, -1) for name in names]
params = {
"possible_includes": get_ids(search.possible_includes),
"required_includes": get_ids(search.required_includes),
"required_excludes": get_ids(search.required_excludes),
"required_user_includes": list(search.required_user_includes),
"required_user_excludes": list(search.required_user_excludes),
"type": type_letter,
"userid": userid,
"rating": rating,
"ratings": list(search.ratings),
"category": cat,
"subcategory": subcat,
"limit": limit,
"backid": backid,
"nextid": nextid,
"required_include_count": len(search.required_includes),
}
query = d.engine.execute(statement, **params)
ret = [{
"contype": type_code,
select: i[select],
"title": i.title,
"subtype": i.subtype,
"rating": i.rating,
"unixtime": i.unixtime,
"userid": i.userid,
"username": i.username,
"settings": i.settings,
} for i in query]
if search.find == "submit":
media.populate_with_submission_media(ret)
elif search.find == "char":
for r in ret:
r["sub_media"] = character.fake_media_items(
r["charid"], r["userid"], d.get_sysname(r["username"]),
r["settings"])
elif search.find == "journal":
media.populate_with_user_media(ret)
if backid:
back_count = d.engine.execute(
make_statement("SELECT COUNT(*) FROM (SELECT 1", pagination_filter,
") _"), **params).scalar() - len(ret)
elif nextid:
back_count = (d.engine.execute(
make_statement("SELECT COUNT(*) FROM (SELECT 1",
"AND content.{select} >= %(nextid)s", ") _"),
**params).scalar())
else:
back_count = 0
if backid:
next_count = (d.engine.execute(
make_statement("SELECT COUNT(*) FROM (SELECT 1",
"AND content.{select} <= %(backid)s", ") _"),
**params).scalar())
return list(reversed(ret)), next_count, back_count
else:
next_count = d.engine.execute(
make_statement("SELECT COUNT(*) FROM (SELECT 1", pagination_filter,
") _"), **params).scalar() - len(ret)
return ret, next_count, back_count
