def charactersbyuser(targetid):
query = d.engine.execute("""
SELECT charid, unixtime, char_name, rating, settings
FROM character
WHERE userid = %(user)s
""",
user=targetid)
return [{
"contype":
20,
"userid":
targetid,
"charid":
item[0],
"unixtime":
item[1],
"title":
item[2],
"rating":
item[3],
"settings":
item[4],
"sub_media":
character.fake_media_items(item[0], targetid, "unused", item[4]),
} for item in query]
def charactersbyuser(userid, form):
if userid not in staff.MODS:
raise WeasylError("Unexpected")
query = d.execute("""
SELECT
ch.charid, pr.username, ch.unixtime,
ch.char_name, ch.age, ch.gender, ch.height, ch.weight, ch.species,
ch.content, ch.rating, ch.settings, ch.page_views, pr.config
FROM character ch
INNER JOIN profile pr ON ch.userid = pr.userid
INNER JOIN login ON ch.userid = login.userid
WHERE login.login_name = '%s'
""", [d.get_sysname(form.name)])
return [{
"contype": 20,
"userid": userid,
"charid": item[0],
"username": item[1],
"unixtime": item[2],
"title": item[3],
"rating": item[10],
"settings": item[11],
"sub_media": character.fake_media_items(item[0], userid, d.get_sysname(item[1]), item[11]),
} for item in query]
def _fake_media_items(i):
if i.contype == _CONTYPE_CHAR:
return character.fake_media_items(i.id, i.userid,
d.get_sysname(i.username),
i.settings)
else:
return None
def charactersbyuser(userid, form):
if userid not in staff.MODS:
raise WeasylError("Unexpected")
query = d.execute("""
SELECT
ch.charid, pr.username, ch.unixtime,
ch.char_name, ch.age, ch.gender, ch.height, ch.weight, ch.species,
ch.content, ch.rating, ch.settings, ch.page_views, pr.config
FROM character ch
INNER JOIN profile pr ON ch.userid = pr.userid
INNER JOIN login ON ch.userid = login.userid
WHERE login.login_name = '%s'
""", [d.get_sysname(form.name)])
return [{
"contype": 20,
"userid": userid,
"charid": item[0],
"username": item[1],
"unixtime": item[2],
"title": item[3],
"rating": item[10],
"settings": item[11],
"sub_media": character.fake_media_items(item[0], userid, d.get_sysname(item[1]), item[11]),
} for item in query]
def _fake_media_items(i):
if i.contype == _CONTYPE_CHAR:
return character.fake_media_items(
i.id,
i.userid,
d.get_sysname(i.username),
i.settings)
else:
return None
def select_char(userid, rating, limit, otherid=None, backid=None, nextid=None, config=None):
if config is None:
config = d.get_config(userid)
query = []
statement = ["""
SELECT ch.charid, ch.char_name, ch.rating, fa.unixtime, ch.userid, pr.username, ch.settings
FROM favorite fa
INNER JOIN character ch ON fa.targetid = ch.charid
INNER JOIN profile pr ON ch.userid = pr.userid
WHERE fa.type = 'f'
AND ch.settings !~ 'h'
"""]
if userid:
# filter own content in SFW mode
if d.is_sfw_mode():
statement.append(" AND (ch.rating <= %i)" % (rating,))
else:
statement.append(" AND (ch.userid = %i OR ch.rating <= %i)" % (userid, rating))
statement.append(m.MACRO_FRIENDUSER_CHARACTER % (userid, userid, userid))
statement.append(m.MACRO_IGNOREUSER % (userid, "ch"))
statement.append(m.MACRO_BLOCKTAG_CHAR % (userid, userid))
else:
statement.append(" AND ch.rating <= %i AND ch.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, 'f'))"
% (otherid, backid))
elif nextid:
statement.append(" AND fa.unixtime < "
"(SELECT unixtime FROM favorite WHERE (userid, targetid, type) = (%i, %i, 'f'))"
% (otherid, nextid))
statement.append(" ORDER BY fa.unixtime%s LIMIT %i" % ("" if backid else " DESC", limit))
from weasyl import character
query = [{
"contype": 20,
"charid": i[0],
"title": i[1],
"rating": i[2],
"unixtime": i[3],
"userid": i[4],
"username": i[5],
"sub_media": character.fake_media_items(i[0], i[4], d.get_sysname(i[5]), i[6]),
} for i in d.execute("".join(statement))]
return query[::-1] if backid else query
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(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(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_char(userid,
rating,
limit,
otherid=None,
backid=None,
nextid=None,
config=None):
if config is None:
config = d.get_config(userid)
query = []
statement = [
"""
SELECT ch.charid, ch.char_name, ch.rating, fa.unixtime, ch.userid, pr.username, ch.settings
FROM favorite fa
INNER JOIN character ch ON fa.targetid = ch.charid
INNER JOIN profile pr ON ch.userid = pr.userid
WHERE fa.type = 'f'
AND ch.settings !~ 'h'
"""
]
if userid:
# filter own content in SFW mode
if d.is_sfw_mode():
statement.append(" AND (ch.rating <= %i)" % (rating, ))
else:
statement.append(" AND (ch.userid = %i OR ch.rating <= %i)" %
(userid, rating))
statement.append(m.MACRO_FRIENDUSER_CHARACTER %
(userid, userid, userid))
statement.append(m.MACRO_IGNOREUSER % (userid, "ch"))
statement.append(m.MACRO_BLOCKTAG_CHAR % (userid, userid))
else:
statement.append(" AND ch.rating <= %i AND ch.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, 'f'))"
% (otherid, backid))
elif nextid:
statement.append(
" AND fa.unixtime < "
"(SELECT unixtime FROM favorite WHERE (userid, targetid, type) = (%i, %i, 'f'))"
% (otherid, nextid))
statement.append(" ORDER BY fa.unixtime%s LIMIT %i" %
("" if backid else " DESC", limit))
from weasyl import character
query = [{
"contype":
20,
"charid":
i[0],
"title":
i[1],
"rating":
i[2],
"unixtime":
i[3],
"userid":
i[4],
"username":
i[5],
"sub_media":
character.fake_media_items(i[0], i[4], d.get_sysname(i[5]), i[6]),
} for i in d.execute("".join(statement))]
return query[::-1] if backid else query
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
