def submissionsbyuser(userid, form):
if userid not in staff.MODS:
raise WeasylError("Unexpected")
query = d.execute(
"""
SELECT su.submitid, su.title, su.rating, su.unixtime, su.userid, pr.username, su.settings
FROM submission su
INNER JOIN profile pr USING (userid)
WHERE su.userid = (SELECT userid FROM login WHERE login_name = '%s')
ORDER BY su.submitid DESC
""", [d.get_sysname(form.name)])
ret = [{
"contype": 10,
"submitid": i[0],
"title": i[1],
"rating": i[2],
"unixtime": i[3],
"userid": i[4],
"username": i[5],
"settings": i[6],
} for i in query]
media.populate_with_submission_media(ret)
return ret
def select_list(userid, rating, limit, otherid=None, folderid=None,
backid=None, nextid=None, subcat=None,
options=[], profile_page_filter=False,
index_page_filter=False, featured_filter=False):
"""
Selects a list from the submissions table.
Args:
userid: The current user
rating: The maximum rating level to show
limit: The number of submissions to get
otherid: The user whose submissions to get
folderid: Select submissions from this folder
backid: Select the IDs that are less than this value
nextid: Select the IDs that are greater than this value
subcat: Select submissions whose subcategory is within this range
(this value + 1000)
options: List that can contain the following values:
"critique": Submissions flagged for critique; additionally selects
submissions newer than 3 days old
"randomize": Randomize the ordering of the results
profile_page_filter: Do not select from folders that should not appear
on the profile page.
index_page_filter: Do not select from folders that should not appear on
the front page.
featured_filter: Select from folders marked as featured submissions.
Returns:
An array with the following keys: "contype", "submitid", "title",
"rating", "unixtime", "userid", "username", "subtype", "sub_media"
"""
if options not in [[], ['critique'], ['randomize']]:
raise ValueError("Unexpected options: %r" % (options,))
randomize = bool(options)
statement = [
"SELECT su.submitid, su.title, su.rating, su.unixtime, "
"su.userid, pr.username, su.settings, su.subtype "]
statement.extend(select_query(
userid, rating, otherid, folderid, backid, nextid, subcat, options, profile_page_filter,
index_page_filter, featured_filter))
statement.append(
" ORDER BY %s%s LIMIT %i" % ("RANDOM()" if randomize else "su.submitid", "" if backid else " DESC", limit))
query = [{
"contype": 10,
"submitid": i[0],
"title": i[1],
"rating": i[2],
"unixtime": i[3],
"userid": i[4],
"username": i[5],
"subtype": i[7],
} for i in d.execute("".join(statement))]
media.populate_with_submission_media(query)
return query[::-1] if backid else query
def select_submit(userid,
rating,
limit,
otherid=None,
backid=None,
nextid=None,
config=None):
statement = [
"SELECT su.submitid, su.title, su.rating, fa.unixtime, su.userid, pr.username, su.subtype"
]
statement.extend(
select_submit_query(userid, rating, otherid, backid, nextid, config))
statement.append(" ORDER BY fa.unixtime%s LIMIT %i" %
("" if backid else " DESC", limit))
query = [{
"contype": 10,
"submitid": i[0],
"title": i[1],
"rating": i[2],
"unixtime": i[3],
"userid": i[4],
"username": i[5],
"subtype": i[6],
} for i in d.execute("".join(statement))]
media.populate_with_submission_media(query)
return query[::-1] if backid else query
def select_recently_popular():
"""
Get a list of recent, popular submissions. This operation is non-trivial and should
not be used frequently without caching.
To calculate scores, this method performs the following evaluation:
item_score = log(item_fave_count) + log(item_view_counts) / 2 + submission_time / 180000
180000 is roughly two days. So intuitively an item two days old needs an order of
magnitude more favorites/views compared to a fresh one. Also the favorites are
quadratically more influential than views. The result is that this algorithm favors
recent, heavily favorited items.
:return: A list of submission dictionaries, in score-rank order.
"""
db = meta.Base.dbsession
subq = (db.query(
content.Favorite.targetid,
sa.func.count().label('faves')).filter_by(type='s').group_by(
content.Favorite.targetid).subquery())
score = (
sa.func.log(sa.func.greatest(sa.func.coalesce(subq.c.faves, 0), 1)) +
sa.func.log(sa.func.greatest(content.Submission.page_views, 1)) / 2 +
sa.cast(content.Submission.unixtime, sa.types.INTEGER) /
180000).label('score')
q = (db.query(content.Submission, users.Profile,
score).options(sa.orm.joinedload('tag_objects')).filter(
~content.Submission.is_hidden,
~content.Submission.is_friends_only).outerjoin(
subq,
content.Submission.submitid == subq.c.targetid).join(
users.Profile, content.Submission.userid ==
users.Profile.userid).order_by(score.desc()))
max_days = int(d.config_read_setting("popular_max_age_days", "21"))
if max_days > 0:
q = q.filter(content.Submission.unixtime > sa.func.extract(
'EPOCH',
sa.func.now() - datetime.timedelta(days=max_days)))
q = q.limit(128)
submissions = [{
'contype': 10,
'score': sc,
'submitid': s.submitid,
'title': s.title,
'rating': s.rating.code,
'subtype': s.subtype,
'unixtime': s.unixtime.timestamp,
'tags': list(s.tags),
'userid': s.userid,
'username': p.username,
} for s, p, sc in q.all()]
media.populate_with_submission_media(submissions)
return submissions
def select_near(userid,
rating,
limit,
otherid,
folderid,
submitid,
config=None):
if config is None:
config = d.get_config(userid)
statement = [
"""
SELECT su.submitid, su.title, su.rating, su.unixtime, su.userid,
pr.username, su.settings, su.subtype
FROM submission su
INNER JOIN profile pr ON su.userid = pr.userid
WHERE su.userid = %i
AND su.settings !~ 'h'
""" % (otherid, )
]
if userid:
# Users always see their own content.
statement.append(" AND (su.rating <= %i OR su.userid = %i)" %
(rating, userid))
statement.append(m.MACRO_IGNOREUSER % (userid, "su"))
statement.append(m.MACRO_FRIENDUSER_SUBMIT % (userid, userid, userid))
statement.append(m.MACRO_BLOCKTAG_SUBMIT % (userid, userid))
else:
statement.append(" AND su.rating <= %i AND su.settings !~ 'f'" %
(rating, ))
if folderid:
statement.append(" AND su.folderid = %i" % folderid)
query = [{
"contype": 10,
"submitid": i[0],
"title": i[1],
"rating": i[2],
"unixtime": i[3],
"userid": i[4],
"username": i[5],
"subtype": i[7],
} for i in d.execute("".join(statement))]
query.sort(key=lambda i: i['submitid'])
older = [i for i in query if i["submitid"] < submitid][-limit:]
newer = [i for i in query if i["submitid"] > submitid][:limit]
media.populate_with_submission_media(older + newer)
return {
"older": older,
"newer": newer,
}
def select_recently_popular():
"""
Get a list of recent, popular submissions. This operation is non-trivial and should
not be used frequently without caching.
To calculate scores, this method performs the following evaluation:
item_score = log(item_fave_count) + log(item_view_counts) / 2 + submission_time / 180000
180000 is roughly two days. So intuitively an item two days old needs an order of
magnitude more favorites/views compared to a fresh one. Also the favorites are
quadratically more influential than views. The result is that this algorithm favors
recent, heavily favorited items.
:return: A list of submission dictionaries, in score-rank order.
"""
db = meta.Base.dbsession
subq = (
db.query(content.Favorite.targetid, sa.func.count().label('faves'))
.filter_by(type='s')
.group_by(content.Favorite.targetid)
.subquery())
score = (
sa.func.log(sa.func.greatest(sa.func.coalesce(subq.c.faves, 0), 1)) +
sa.func.log(sa.func.greatest(content.Submission.page_views, 1)) / 2 +
sa.cast(content.Submission.unixtime, sa.types.INTEGER) / 180000).label('score')
q = (
db.query(content.Submission, users.Profile, score)
.options(sa.orm.joinedload('tag_objects'))
.filter(~content.Submission.is_hidden, ~content.Submission.is_friends_only)
.outerjoin(subq, content.Submission.submitid == subq.c.targetid)
.join(users.Profile, content.Submission.userid == users.Profile.userid)
.order_by(score.desc()))
max_days = int(d.config_read_setting("popular_max_age_days", "21"))
if max_days > 0:
q = q.filter(content.Submission.unixtime >
sa.func.extract('EPOCH', sa.func.now() - datetime.timedelta(days=max_days)))
q = q.limit(128)
submissions = [{
'contype': 10,
'score': sc,
'submitid': s.submitid,
'title': s.title,
'rating': s.rating.code,
'subtype': s.subtype,
'unixtime': s.unixtime.timestamp,
'tags': list(s.tags),
'userid': s.userid,
'username': p.username,
} for s, p, sc in q.all()]
media.populate_with_submission_media(submissions)
return submissions
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(**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_recently_popular():
"""
Get a list of recent, popular submissions. This operation is non-trivial and should
not be used frequently without caching.
To calculate scores, this method performs the following evaluation:
item_score = log(item_fave_count + 1) + log(item_view_counts) / 2 + submission_time / 180000
180000 is roughly two days. So intuitively an item two days old needs an order of
magnitude more favorites/views compared to a fresh one. Also the favorites are
quadratically more influential than views. The result is that this algorithm favors
recent, heavily favorited items.
:return: A list of submission dictionaries, in score-rank order.
"""
max_days = int(d.config_read_setting("popular_max_age_days", "21"))
query = d.engine.execute("""
SELECT
log(count(favorite.*) + 1) +
log(submission.page_views + 1) / 2 +
submission.unixtime / 180000.0 AS score,
submission.submitid,
submission.title,
submission.rating,
submission.subtype,
submission.unixtime,
submission_tags.tags,
submission.userid,
profile.username
FROM submission
INNER JOIN submission_tags ON submission.submitid = submission_tags.submitid
INNER JOIN profile ON submission.userid = profile.userid
LEFT JOIN favorite ON favorite.type = 's' AND submission.submitid = favorite.targetid
WHERE
submission.unixtime > EXTRACT(EPOCH FROM now() - %(max_days)s * INTERVAL '1 day')::INTEGER AND
submission.settings !~ '[hf]'
GROUP BY submission.submitid, submission_tags.submitid, profile.userid
ORDER BY score DESC
LIMIT 128
""",
max_days=max_days)
submissions = [dict(row, contype=10) for row in query]
media.populate_with_submission_media(submissions)
return submissions
def select_recently_popular():
"""
Get a list of recent, popular submissions. This operation is non-trivial and should
not be used frequently without caching.
To calculate scores, this method performs the following evaluation:
item_score = log(item_fave_count + 1) + log(item_view_counts) / 2 + submission_time / 180000
180000 is roughly two days. So intuitively an item two days old needs an order of
magnitude more favorites/views compared to a fresh one. Also the favorites are
quadratically more influential than views. The result is that this algorithm favors
recent, heavily favorited items.
:return: A list of submission dictionaries, in score-rank order.
"""
max_days = int(d.config_read_setting("popular_max_age_days", "21"))
query = d.engine.execute("""
SELECT
log(count(favorite.*) + 1) +
log(submission.page_views + 1) / 2 +
submission.unixtime / 180000.0 AS score,
submission.submitid,
submission.title,
submission.rating,
submission.subtype,
submission.unixtime,
submission_tags.tags,
submission.userid,
profile.username
FROM submission
INNER JOIN submission_tags ON submission.submitid = submission_tags.submitid
INNER JOIN profile ON submission.userid = profile.userid
LEFT JOIN favorite ON favorite.type = 's' AND submission.submitid = favorite.targetid
WHERE
submission.unixtime > EXTRACT(EPOCH FROM now() - %(max_days)s * INTERVAL '1 day')::INTEGER AND
submission.settings !~ '[hf]'
GROUP BY submission.submitid, submission_tags.submitid, profile.userid
ORDER BY score DESC
LIMIT 128
""", max_days=max_days)
submissions = [dict(row, contype=10) for row in query]
media.populate_with_submission_media(submissions)
return submissions
def select_near(userid, rating, limit, otherid, folderid, submitid, config=None):
if config is None:
config = d.get_config(userid)
statement = ["""
SELECT su.submitid, su.title, su.rating, su.unixtime, su.userid,
pr.username, su.settings, su.subtype
FROM submission su
INNER JOIN profile pr ON su.userid = pr.userid
WHERE su.userid = %i
AND su.settings !~ 'h'
""" % (otherid,)]
if userid:
# Users always see their own content.
statement.append(" AND (su.rating <= %i OR su.userid = %i)" % (rating, userid))
statement.append(m.MACRO_IGNOREUSER % (userid, "su"))
statement.append(m.MACRO_FRIENDUSER_SUBMIT % (userid, userid, userid))
statement.append(m.MACRO_BLOCKTAG_SUBMIT % (userid, userid))
else:
statement.append(" AND su.rating <= %i AND su.settings !~ 'f'" % (rating,))
if folderid:
statement.append(" AND su.folderid = %i" % folderid)
query = [{
"contype": 10,
"submitid": i[0],
"title": i[1],
"rating": i[2],
"unixtime": i[3],
"userid": i[4],
"username": i[5],
"subtype": i[7],
} for i in d.execute("".join(statement))]
query.sort(key=lambda i: i['submitid'])
older = [i for i in query if i["submitid"] < submitid][-limit:]
newer = [i for i in query if i["submitid"] > submitid][:limit]
media.populate_with_submission_media(older + newer)
return {
"older": older,
"newer": newer,
}
def submissionsbyuser(targetid):
query = d.engine.execute("""
SELECT submitid, title, rating, unixtime, settings
FROM submission
WHERE userid = %(user)s
""", user=targetid)
ret = [{
"contype": 10,
"userid": targetid,
"submitid": i[0],
"title": i[1],
"rating": i[2],
"unixtime": i[3],
"settings": i[4],
} for i in query]
media.populate_with_submission_media(ret)
return ret
def select_submit(userid, rating, limit, otherid=None, backid=None, nextid=None, config=None):
statement = ["SELECT su.submitid, su.title, su.rating, fa.unixtime, su.userid, pr.username, su.subtype"]
statement.extend(select_submit_query(userid, rating, otherid, backid, nextid, config))
statement.append(" ORDER BY fa.unixtime%s LIMIT %i" % ("" if backid else " DESC", limit))
query = [{
"contype": 10,
"submitid": i[0],
"title": i[1],
"rating": i[2],
"unixtime": i[3],
"userid": i[4],
"username": i[5],
"subtype": i[6],
} for i in d.execute("".join(statement))]
media.populate_with_submission_media(query)
return query[::-1] if backid else query
def select_recently_popular():
"""
Get a list of recent, popular submissions.
To calculate scores, this method performs the following evaluation:
item_score = log(item_fave_count + 1) + log(item_view_counts) / 2 + submission_time / 180000
180000 is roughly two days. So intuitively an item two days old needs an order of
magnitude more favorites/views compared to a fresh one. Also the favorites are
quadratically more influential than views. The result is that this algorithm favors
recent, heavily favorited items.
:return: A list of submission dictionaries, in score-rank order.
"""
query = d.engine.execute("""
SELECT
log(submission.favorites + 1) +
log(submission.page_views + 1) / 2 +
submission.unixtime / 180000.0 AS score,
submission.submitid,
submission.title,
submission.rating,
submission.subtype,
submission.unixtime,
submission_tags.tags,
submission.userid,
profile.username
FROM submission
INNER JOIN submission_tags ON submission.submitid = submission_tags.submitid
INNER JOIN profile ON submission.userid = profile.userid
WHERE
submission.settings !~ '[hf]'
AND submission.favorites IS NOT NULL
ORDER BY score DESC
LIMIT 128
""")
submissions = [dict(row, contype=10) for row in query]
media.populate_with_submission_media(submissions)
return submissions
def submissionsbyuser(userid, form):
if userid not in staff.MODS:
raise WeasylError("Unexpected")
query = d.execute("""
SELECT su.submitid, su.title, su.rating, su.unixtime, su.userid, pr.username, su.settings
FROM submission su
INNER JOIN profile pr USING (userid)
WHERE su.userid = (SELECT userid FROM login WHERE login_name = '%s')
ORDER BY su.submitid DESC
""", [d.get_sysname(form.name)])
ret = [{
"contype": 10,
"submitid": i[0],
"title": i[1],
"rating": i[2],
"unixtime": i[3],
"userid": i[4],
"username": i[5],
"settings": i[6],
} for i in query]
media.populate_with_submission_media(ret)
return ret
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_submissions(userid, limit, backtime=None, nexttime=None):
if backtime:
time_filter = "AND unixtime > %(backtime)s"
elif nexttime:
time_filter = "AND unixtime < %(nexttime)s"
else:
time_filter = ""
statement = """
SELECT * FROM (
SELECT
20 AS contype,
ch.charid AS id,
ch.char_name AS title,
ch.rating,
ch.unixtime,
ch.userid,
pr.username,
ch.settings,
we.welcomeid,
0 AS subtype
FROM welcome we
INNER JOIN character ch ON we.targetid = ch.charid
INNER JOIN profile pr ON ch.userid = pr.userid
WHERE
we.type = 2050 AND
we.userid = %(userid)s
UNION SELECT
40 AS contype,
su.submitid AS id,
su.title,
su.rating,
su.unixtime,
we.otherid AS userid,
pr.username,
su.settings,
we.welcomeid,
su.subtype
FROM welcome we
INNER JOIN submission su ON we.targetid = su.submitid
INNER JOIN profile pr ON we.otherid = pr.userid
WHERE
we.type = 2030 AND
we.userid = %(userid)s
UNION SELECT
10 AS contype,
su.submitid AS id,
su.title,
su.rating,
su.unixtime,
su.userid,
pr.username,
su.settings,
we.welcomeid,
su.subtype
FROM welcome we
INNER JOIN submission su ON we.targetid = su.submitid
INNER JOIN profile pr ON su.userid = pr.userid
WHERE
we.type = 2010 AND
we.userid = %(userid)s
) results
WHERE
rating <= %(rating)s
{time_filter}
ORDER BY unixtime DESC
LIMIT %(limit)s
""".format(time_filter=time_filter)
query = d.engine.execute(
statement,
userid=userid,
rating=d.get_rating(userid),
nexttime=nexttime,
backtime=backtime,
limit=limit,
)
results = [{
"contype": i.contype,
"submitid" if i.contype != _CONTYPE_CHAR else "charid": i.id,
"welcomeid": i.welcomeid,
"title": i.title,
"rating": i.rating,
"unixtime": i.unixtime,
"userid": i.userid,
"username": i.username,
"subtype": i.subtype,
"sub_media": _fake_media_items(i),
} for i in query]
media.populate_with_submission_media(
[i for i in results if i["contype"] != _CONTYPE_CHAR])
return results
def select_submissions(userid, limit, include_tags, backtime=None, nexttime=None):
if backtime:
time_filter = "AND we.unixtime > %(backtime)s"
elif nexttime:
time_filter = "AND we.unixtime < %(nexttime)s"
else:
time_filter = ""
if include_tags:
char_tags_select = ", COALESCE(array_agg(tagid) FILTER (WHERE tagid IS NOT NULL), '{}') AS tags"
char_tags_join = "LEFT JOIN searchmapchar AS smc ON ch.charid = smc.targetid"
char_tags_groupby = "GROUP BY ch.charid, pr.username, we.welcomeid"
submission_tags_select = ", tags"
submission_tags_join = "INNER JOIN submission_tags USING (submitid)"
else:
char_tags_select = char_tags_join = char_tags_groupby = submission_tags_select = submission_tags_join = ""
statement = """
SELECT * FROM (
SELECT
20 AS contype,
ch.charid AS id,
ch.char_name AS title,
ch.rating,
we.unixtime,
ch.userid,
pr.username,
ch.settings,
we.welcomeid,
0 AS subtype
{char_tags_select}
FROM welcome we
INNER JOIN character ch ON we.targetid = ch.charid
INNER JOIN profile pr ON ch.userid = pr.userid
{char_tags_join}
WHERE
we.type = 2050 AND
we.userid = %(userid)s AND
ch.rating <= %(rating)s
{time_filter}
{char_tags_groupby}
ORDER BY welcomeid DESC LIMIT %(limit)s
) t
UNION ALL SELECT * FROM (
SELECT
40 AS contype,
su.submitid AS id,
su.title,
su.rating,
we.unixtime,
we.otherid AS userid,
pr.username,
su.settings,
we.welcomeid,
su.subtype
{submission_tags_select}
FROM welcome we
INNER JOIN submission su ON we.targetid = su.submitid
INNER JOIN profile pr ON we.otherid = pr.userid
{submission_tags_join}
WHERE
we.type = 2030 AND
we.userid = %(userid)s AND
su.rating <= %(rating)s
{time_filter}
ORDER BY welcomeid DESC LIMIT %(limit)s
) t
UNION ALL SELECT * FROM (
SELECT
10 AS contype,
su.submitid AS id,
su.title,
su.rating,
we.unixtime,
su.userid,
pr.username,
su.settings,
we.welcomeid,
su.subtype
{submission_tags_select}
FROM welcome we
INNER JOIN submission su ON we.targetid = su.submitid
INNER JOIN profile pr ON su.userid = pr.userid
{submission_tags_join}
WHERE
we.type = 2010 AND
we.userid = %(userid)s AND
su.rating <= %(rating)s
{time_filter}
ORDER BY welcomeid DESC LIMIT %(limit)s
) t
ORDER BY welcomeid DESC LIMIT %(limit)s
""".format(
time_filter=time_filter,
char_tags_select=char_tags_select,
char_tags_join=char_tags_join,
char_tags_groupby=char_tags_groupby,
submission_tags_select=submission_tags_select,
submission_tags_join=submission_tags_join,
)
query = d.engine.execute(
statement,
userid=userid,
rating=d.get_rating(userid),
nexttime=nexttime,
backtime=backtime,
limit=limit,
).fetchall()
if include_tags:
all_tags = list(frozenset(chain.from_iterable(i.tags for i in query)))
tag_map = {t.tagid: t.title for t in d.engine.execute("SELECT tagid, title FROM searchtag WHERE tagid = ANY (%(tags)s)", tags=all_tags)}
results = [{
"contype": i.contype,
"submitid" if i.contype != _CONTYPE_CHAR else "charid": i.id,
"welcomeid": i.welcomeid,
"title": i.title,
"rating": i.rating,
"unixtime": i.unixtime,
"userid": i.userid,
"username": i.username,
"subtype": i.subtype,
"tags": [tag_map[tag] for tag in i.tags],
"sub_media": _fake_media_items(i),
} for i in query]
else:
results = [{
"contype": i.contype,
"submitid" if i.contype != _CONTYPE_CHAR else "charid": i.id,
"welcomeid": i.welcomeid,
"title": i.title,
"rating": i.rating,
"unixtime": i.unixtime,
"userid": i.userid,
"username": i.username,
"subtype": i.subtype,
"sub_media": _fake_media_items(i),
} for i in query]
media.populate_with_submission_media(
[i for i in results if i["contype"] != _CONTYPE_CHAR])
return results
def select_preview(userid, otherid, rating):
"""
Pick out recently updated folders up to the limit, and get a count, name,
and the latest submission to use as a preview for each.
The rules below ensure that the following images won't be used or counted:
Hidden images, friends only images from non-friends, submissions above the
specified rating. Except for hidden images, these rules are ignored when
a user views their own folders.
Params:
userid: The id of the viewing user.
otherid: The id of the user whose folders we're viewing.
rating: The maximum rating of submissions that will be considered for
counts or a preview.
Returns:
An array of dicts, each of which has a folderid, a title, a count, and
sub_media to use for a preview.
"""
folder_query = d.engine.execute("""
SELECT fd.folderid, fd.title, count(su.*), max(ARRAY[su.submitid, su.subtype]) AS most_recent
FROM folder fd
INNER JOIN submission su USING (folderid)
INNER JOIN submission_tags USING (submitid)
WHERE fd.userid = %(otherid)s
AND fd.settings !~ '[hu]'
AND su.settings !~ 'h'
AND (su.rating <= %(rating)s OR (su.userid = %(userid)s AND NOT %(sfwmode)s))
AND (
su.settings !~ 'f'
OR su.userid = %(userid)s
OR EXISTS (
SELECT FROM frienduser
WHERE settings !~ 'p' AND (
(userid, otherid) = (%(userid)s, su.userid)
OR (userid, otherid) = (su.userid, %(userid)s)
)
)
)
AND (
su.userid = %(userid)s
OR NOT tags && (SELECT coalesce(array_agg(tagid), '{}') FROM blocktag WHERE userid = %(userid)s)
)
GROUP BY fd.folderid
ORDER BY max(su.submitid) DESC
LIMIT %(limit)s
""",
rating=rating,
userid=userid,
otherid=otherid,
limit=_PREVIEW_COUNT,
sfwmode=d.is_sfw_mode())
previews = [{
"folderid": i.folderid,
"title": i.title,
"count": i.count,
"userid": otherid,
"submitid": i.most_recent[0],
"subtype": i.most_recent[1],
} for i in folder_query]
media.populate_with_submission_media(previews)
return previews
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
def select_submissions(userid,
limit,
include_tags,
backtime=None,
nexttime=None):
if backtime:
time_filter = "AND we.unixtime > %(backtime)s"
elif nexttime:
time_filter = "AND we.unixtime < %(nexttime)s"
else:
time_filter = ""
if include_tags:
char_tags_select = ", COALESCE(array_agg(tagid) FILTER (WHERE tagid IS NOT NULL), '{}') AS tags"
char_tags_join = "LEFT JOIN searchmapchar AS smc ON ch.charid = smc.targetid"
char_tags_groupby = "GROUP BY ch.charid, pr.username, we.welcomeid"
submission_tags_select = ", tags"
submission_tags_join = "INNER JOIN submission_tags USING (submitid)"
else:
char_tags_select = char_tags_join = char_tags_groupby = submission_tags_select = submission_tags_join = ""
statement = """
SELECT * FROM (
SELECT
20 AS contype,
ch.charid AS id,
ch.char_name AS title,
ch.rating,
we.unixtime,
ch.userid,
pr.username,
ch.settings,
we.welcomeid,
0 AS subtype
{char_tags_select}
FROM welcome we
INNER JOIN character ch ON we.targetid = ch.charid
INNER JOIN profile pr ON ch.userid = pr.userid
{char_tags_join}
WHERE
we.type = 2050 AND
we.userid = %(userid)s AND
ch.rating <= %(rating)s
{time_filter}
{char_tags_groupby}
ORDER BY welcomeid DESC LIMIT %(limit)s
) t
UNION ALL SELECT * FROM (
SELECT
40 AS contype,
su.submitid AS id,
su.title,
su.rating,
we.unixtime,
we.otherid AS userid,
pr.username,
su.settings,
we.welcomeid,
su.subtype
{submission_tags_select}
FROM welcome we
INNER JOIN submission su ON we.targetid = su.submitid
INNER JOIN profile pr ON we.otherid = pr.userid
{submission_tags_join}
WHERE
we.type = 2030 AND
we.userid = %(userid)s AND
su.rating <= %(rating)s
{time_filter}
ORDER BY welcomeid DESC LIMIT %(limit)s
) t
UNION ALL SELECT * FROM (
SELECT
10 AS contype,
su.submitid AS id,
su.title,
su.rating,
we.unixtime,
su.userid,
pr.username,
su.settings,
we.welcomeid,
su.subtype
{submission_tags_select}
FROM welcome we
INNER JOIN submission su ON we.targetid = su.submitid
INNER JOIN profile pr ON su.userid = pr.userid
{submission_tags_join}
WHERE
we.type = 2010 AND
we.userid = %(userid)s AND
su.rating <= %(rating)s
{time_filter}
ORDER BY welcomeid DESC LIMIT %(limit)s
) t
ORDER BY welcomeid DESC LIMIT %(limit)s
""".format(
time_filter=time_filter,
char_tags_select=char_tags_select,
char_tags_join=char_tags_join,
char_tags_groupby=char_tags_groupby,
submission_tags_select=submission_tags_select,
submission_tags_join=submission_tags_join,
)
query = d.engine.execute(
statement,
userid=userid,
rating=d.get_rating(userid),
nexttime=nexttime,
backtime=backtime,
limit=limit,
).fetchall()
if include_tags:
all_tags = list(frozenset(chain.from_iterable(i.tags for i in query)))
tag_map = {
t.tagid: t.title
for t in d.engine.execute(
"SELECT tagid, title FROM searchtag WHERE tagid = ANY (%(tags)s)",
tags=all_tags)
}
results = [{
"contype": i.contype,
"submitid" if i.contype != _CONTYPE_CHAR else "charid": i.id,
"welcomeid": i.welcomeid,
"title": i.title,
"rating": i.rating,
"unixtime": i.unixtime,
"userid": i.userid,
"username": i.username,
"subtype": i.subtype,
"tags": [tag_map[tag] for tag in i.tags],
"sub_media": _fake_media_items(i),
} for i in query]
else:
results = [{
"contype": i.contype,
"submitid" if i.contype != _CONTYPE_CHAR else "charid": i.id,
"welcomeid": i.welcomeid,
"title": i.title,
"rating": i.rating,
"unixtime": i.unixtime,
"userid": i.userid,
"username": i.username,
"subtype": i.subtype,
"sub_media": _fake_media_items(i),
} for i in query]
media.populate_with_submission_media(
[i for i in results if i["contype"] != _CONTYPE_CHAR])
return results
def select_list(userid, rating, limit, otherid=None, folderid=None,
backid=None, nextid=None, subcat=None, exclude=None,
options=[], config=None, profile_page_filter=False,
index_page_filter=False, featured_filter=False):
"""
Selects a list from the submissions table.
Args:
userid: The current user
rating: The maximum rating level to show
limit: The number of submissions to get
otherid: The user whose submissions to get
folderid: Select submissions from this folder
backid: Select the IDs that are less than this value
nextid: Select the IDs that are greater than this value
subcat: Select submissions whose subcategory is within this range
(this value + 1000)
exclude: Exclude this specific submission ID
options: List that can contain the following values:
"critique": Submissions flagged for critique; additionally selects
submissions newer than 3 days old
"randomize": Randomize the ordering of the results
"encore": Order results by sort time rather than submission id
"offset": Select submissions older than half an hour
config: Database config override
profile_page_filter: Do not select from folders that should not appear
on the profile page.
index_page_filter: Do not select from folders that should not appear on
the front page.
featured_filter: Select from folders marked as featured submissions.
Returns:
An array with the following keys: "contype", "submitid", "title",
"rating", "unixtime", "userid", "username", "subtype", "sub_media"
"""
statement = [
"SELECT su.submitid, su.title, su.rating, su.unixtime, "
"su.userid, pr.username, su.settings, su.subtype "]
statement.extend(select_query(
userid, rating, otherid, folderid, backid, nextid, subcat, exclude, options, config, profile_page_filter,
index_page_filter, featured_filter))
statement.append(
" ORDER BY %s%s LIMIT %i" % ("RANDOM()" if "critique" in options or "randomize" in options else
"su.sorttime" if "encore" in options else
"su.submitid", "" if backid else " DESC", limit))
query = [{
"contype": 10,
"submitid": i[0],
"title": i[1],
"rating": i[2],
"unixtime": i[3],
"userid": i[4],
"username": i[5],
"subtype": i[7],
} for i in d.execute("".join(statement))]
media.populate_with_submission_media(query)
return query[::-1] if backid else query
def select_submissions(userid, limit, backtime=None, nexttime=None):
if backtime:
time_filter = "AND unixtime > %(backtime)s"
elif nexttime:
time_filter = "AND unixtime < %(nexttime)s"
else:
time_filter = ""
statement = """
SELECT * FROM (
SELECT
20 AS contype,
ch.charid AS id,
ch.char_name AS title,
ch.rating,
ch.unixtime,
ch.userid,
pr.username,
ch.settings,
we.welcomeid,
0 AS subtype,
array_agg(tags.title) AS tags
FROM welcome we
INNER JOIN character ch ON we.targetid = ch.charid
INNER JOIN profile pr ON ch.userid = pr.userid
LEFT JOIN searchmapchar AS smc ON ch.charid = smc.targetid
LEFT JOIN searchtag AS tags USING (tagid)
WHERE
we.type = 2050 AND
we.userid = %(userid)s
GROUP BY
ch.charid,
pr.username,
we.welcomeid
UNION SELECT
40 AS contype,
su.submitid AS id,
su.title,
su.rating,
su.unixtime,
we.otherid AS userid,
pr.username,
su.settings,
we.welcomeid,
su.subtype,
array_agg(tags.title) AS tags
FROM welcome we
INNER JOIN submission su ON we.targetid = su.submitid
INNER JOIN profile pr ON we.otherid = pr.userid
LEFT JOIN searchmapsubmit AS sms ON su.submitid = sms.targetid
LEFT JOIN searchtag AS tags USING (tagid)
WHERE
we.type = 2030 AND
we.userid = %(userid)s
GROUP BY
su.submitid,
pr.username,
we.welcomeid
UNION SELECT
10 AS contype,
su.submitid AS id,
su.title,
su.rating,
su.unixtime,
su.userid,
pr.username,
su.settings,
we.welcomeid,
su.subtype,
array_agg(tags.title) AS tags
FROM welcome we
INNER JOIN submission su ON we.targetid = su.submitid
INNER JOIN profile pr ON su.userid = pr.userid
LEFT JOIN searchmapsubmit AS sms ON su.submitid = sms.targetid
LEFT JOIN searchtag AS tags USING (tagid)
WHERE
we.type = 2010 AND
we.userid = %(userid)s
GROUP BY
su.submitid,
pr.username,
we.welcomeid
) results
WHERE
rating <= %(rating)s
{time_filter}
ORDER BY unixtime DESC
LIMIT %(limit)s
""".format(time_filter=time_filter)
query = d.engine.execute(
statement,
userid=userid,
rating=d.get_rating(userid),
nexttime=nexttime,
backtime=backtime,
limit=limit,
)
results = [{
"contype": i.contype,
"submitid" if i.contype != _CONTYPE_CHAR else "charid": i.id,
"welcomeid": i.welcomeid,
"title": i.title,
"rating": i.rating,
"unixtime": i.unixtime,
"userid": i.userid,
"username": i.username,
"subtype": i.subtype,
"tags": i.tags,
"sub_media": _fake_media_items(i),
} for i in query]
media.populate_with_submission_media(
[i for i in results if i["contype"] != _CONTYPE_CHAR])
return results
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
