def select_latest(userid, rating, otherid):
statement = ["SELECT jo.journalid, jo.title, jo.content, jo.unixtime FROM journal jo WHERE"]
if userid:
if d.is_sfw_mode():
statement.append(" (jo.rating <= %i)" % (rating,))
else:
statement.append(" (jo.userid = %i OR jo.rating <= %i)" % (userid, rating))
statement.append(m.MACRO_BLOCKTAG_JOURNAL % (userid, userid))
else:
statement.append(" jo.rating <= %i" % (rating,))
statement.append(
" AND jo.userid = %i AND jo.settings !~ '[%sh]'" % (otherid, "" if frienduser.check(userid, otherid) else "f"))
statement.append("ORDER BY jo.journalid DESC LIMIT 1")
query = d.engine.execute("".join(statement)).first()
if query:
return {
"journalid": query[0],
"title": query[1],
"content": query[2],
"unixtime": query[3],
"comments": d.engine.scalar(
"SELECT count(*) FROM journalcomment WHERE targetid = %(journal)s AND settings !~ 'h'",
journal=query[0],
),
}
def select_list(userid, rating, limit, otherid=None, backid=None, nextid=None):
statement = ["SELECT jo.journalid, jo.title, jo.unixtime FROM journal jo WHERE"]
if userid:
# filter own content in SFW mode
if d.is_sfw_mode():
statement.append(" (jo.rating <= %i)" % (rating,))
else:
statement.append(" (jo.userid = %i OR jo.rating <= %i)" % (userid, rating))
if not otherid:
statement.append(m.MACRO_IGNOREUSER % (userid, "jo"))
statement.append(m.MACRO_BLOCKTAG_JOURNAL % (userid, userid))
else:
statement.append(" jo.rating <= %i" % (rating,))
if otherid:
statement.append(
" AND jo.userid = %i AND jo.settings !~ '[%sh]'" % (otherid, "" if frienduser.check(userid, otherid) else "f"))
else:
statement.append(" AND jo.settings !~ 'h'")
statement.append("ORDER BY jo.journalid DESC LIMIT %i" % limit)
query = [{
"journalid": i[0],
"title": i[1],
"unixtime": i[2],
} for i in d.execute("".join(statement))]
return query[::-1] if backid else query
def select_query(userid, rating, otherid=None, backid=None, nextid=None, options=[], config=None):
statement = [" FROM character ch INNER JOIN profile pr ON ch.userid = pr.userid WHERE ch.settings !~ '[h]'"]
# Ignored users and blocked tags
if userid:
# filter own content in SFW mode
if define.is_sfw_mode():
statement.append(" AND (ch.rating <= %i)" % (rating,))
else:
statement.append(" AND (ch.rating <= %i OR ch.userid = %i)" % (rating, userid))
if not otherid:
statement.append(macro.MACRO_IGNOREUSER % (userid, "ch"))
statement.append(macro.MACRO_BLOCKTAG_CHAR % (userid, userid))
statement.append(macro.MACRO_FRIENDUSER_CHARACTER % (userid, userid, userid))
else:
statement.append(" AND ch.rating <= %i AND ch.settings !~ 'f'" % (rating,))
# Content owner
if otherid:
statement.append(" AND ch.userid = %i" % (otherid,))
# Browse selection
if backid:
statement.append(" AND ch.charid > %i" % (backid,))
elif nextid:
statement.append(" AND ch.charid < %i" % (nextid,))
return statement
def select_query(userid, rating, otherid=None, backid=None, nextid=None):
statement = [" FROM character ch INNER JOIN profile pr ON ch.userid = pr.userid WHERE ch.settings !~ '[h]'"]
# Ignored users and blocked tags
if userid:
# filter own content in SFW mode
if define.is_sfw_mode():
statement.append(" AND (ch.rating <= %i)" % (rating,))
else:
statement.append(" AND (ch.rating <= %i OR ch.userid = %i)" % (rating, userid))
if not otherid:
statement.append(macro.MACRO_IGNOREUSER % (userid, "ch"))
statement.append(macro.MACRO_BLOCKTAG_CHAR % (userid, userid))
statement.append(macro.MACRO_FRIENDUSER_CHARACTER % (userid, userid, userid))
else:
statement.append(" AND ch.rating <= %i AND ch.settings !~ 'f'" % (rating,))
# Content owner
if otherid:
statement.append(" AND ch.userid = %i" % (otherid,))
# Browse selection
if backid:
statement.append(" AND ch.charid > %i" % (backid,))
elif nextid:
statement.append(" AND ch.charid < %i" % (nextid,))
return statement
def select_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.unixtime FROM journal jo WHERE"]
if userid:
# filter own content in SFW mode
if d.is_sfw_mode():
statement.append(" (jo.rating <= %i)" % (rating,))
else:
statement.append(" (jo.userid = %i OR jo.rating <= %i)" % (userid, rating))
if not otherid:
statement.append(m.MACRO_IGNOREUSER % (userid, "jo"))
statement.append(m.MACRO_BLOCKTAG_JOURNAL % (userid, userid))
else:
statement.append(" jo.rating <= %i" % (rating,))
if otherid:
statement.append(
" AND jo.userid = %i AND jo.settings !~ '[%sh]'" % (otherid, "" if frienduser.check(userid, otherid) else "f"))
else:
statement.append(" AND jo.settings !~ 'h'")
statement.append("ORDER BY jo.journalid DESC LIMIT %i" % limit)
query = [{
"journalid": i[0],
"title": i[1],
"unixtime": i[2],
} for i in d.execute("".join(statement))]
return query[::-1] if backid else query
def select_list(userid, rating, limit, otherid):
statement = [
"SELECT jo.journalid, jo.title, jo.unixtime, jo.content FROM journal jo WHERE"
]
if userid:
# filter own content in SFW mode
if d.is_sfw_mode():
statement.append(" (jo.rating <= %i)" % (rating, ))
else:
statement.append(" (jo.userid = %i OR jo.rating <= %i)" %
(userid, rating))
statement.append(m.MACRO_BLOCKTAG_JOURNAL % (userid, userid))
else:
statement.append(" jo.rating <= %i" % (rating, ))
statement.append(
" AND jo.userid = %i AND jo.settings !~ '[%sh]'" %
(otherid, "" if frienduser.check(userid, otherid) else "f"))
statement.append("ORDER BY jo.journalid DESC LIMIT %i" % limit)
return [{
"journalid": i[0],
"title": i[1],
"created_at": arrow.get(i[2] - UNIXTIME_OFFSET),
"content": i[3],
} for i in d.execute("".join(statement))]
def select_latest(userid, rating, otherid=None, config=None):
if config is None:
config = d.get_config(userid)
statement = ["SELECT jo.journalid, jo.title, jo.content, jo.unixtime FROM journal jo WHERE"]
if userid:
if d.is_sfw_mode():
statement.append(" (jo.rating <= %i)" % (rating,))
else:
statement.append(" (jo.userid = %i OR jo.rating <= %i)" % (userid, rating))
if not otherid:
statement.append(m.MACRO_IGNOREUSER % (userid, "jo"))
statement.append(m.MACRO_BLOCKTAG_JOURNAL % (userid, userid))
else:
statement.append(" jo.rating <= %i" % (rating,))
if otherid:
statement.append(
" AND jo.userid = %i AND jo.settings !~ '[%sh]'" % (otherid, "" if frienduser.check(userid, otherid) else "f"))
statement.append("ORDER BY jo.journalid DESC LIMIT 1")
query = d.execute("".join(statement), options="single")
if query:
return {
"journalid": query[0],
"title": query[1],
"content": query[2],
"unixtime": query[3],
"comments": d.execute("SELECT COUNT(*) FROM journalcomment WHERE targetid = %i AND settings !~ 'h'",
[query[0]], ["element"]),
}
def select_query(userid, rating, 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):
if config is None:
config = d.get_config(userid)
statement = [
"FROM submission su "
"INNER JOIN profile pr ON su.userid = pr.userid "
"LEFT JOIN folder f USING (folderid) "
"WHERE su.settings !~ 'h'"]
if profile_page_filter:
statement.append(" AND COALESCE(f.settings !~ 'u', true)")
if index_page_filter:
statement.append(" AND COALESCE(f.settings !~ 'm', true)")
if featured_filter:
statement.append(" AND COALESCE(f.settings ~ 'f', false)")
# Logged in users will see their own submissions regardless of rating
# EXCEPT if they are in SFW mode
if userid and not d.is_sfw_mode():
statement.append(" AND (su.rating <= %i OR su.userid = %i)" % (rating, userid))
else:
statement.append(" AND su.rating <= %i" % (rating,))
if otherid:
statement.append(" AND su.userid = %i" % (otherid,))
if folderid:
statement.append(" AND su.folderid = %i" % (folderid,))
if exclude:
statement.append(" AND su.submitid != %i" % (exclude,))
if subcat:
statement.append(" AND su.subtype >= %i AND su.subtype < %i" % (subcat, subcat + 1000))
if "critique" in options:
statement.append(" AND su.settings ~ 'q' AND su.unixtime > %i" % (d.get_time() - 259200,))
if backid:
statement.append(" AND su.submitid > %i" % (backid,))
elif nextid:
statement.append(" AND su.submitid < %i" % (nextid,))
elif "offset" in options:
statement.append(" AND su.unixtime < %i" % (d.get_time() - 1800,))
if userid:
statement.append(m.MACRO_FRIENDUSER_SUBMIT % (userid, userid, userid))
if not otherid:
statement.append(m.MACRO_IGNOREUSER % (userid, "su"))
statement.append(m.MACRO_BLOCKTAG_SUBMIT % (userid, userid))
else:
statement.append(" AND su.settings !~ 'f'")
return statement
def select_query(userid, rating, 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):
if config is None:
config = d.get_config(userid)
statement = [
"FROM submission su "
"INNER JOIN profile pr ON su.userid = pr.userid "
"LEFT JOIN folder f USING (folderid) "
"WHERE su.settings !~ 'h'"]
if profile_page_filter:
statement.append(" AND COALESCE(f.settings !~ 'u', true)")
if index_page_filter:
statement.append(" AND COALESCE(f.settings !~ 'm', true)")
if featured_filter:
statement.append(" AND COALESCE(f.settings ~ 'f', false)")
# Logged in users will see their own submissions regardless of rating
# EXCEPT if they are in SFW mode
if userid and not d.is_sfw_mode():
statement.append(" AND (su.rating <= %i OR su.userid = %i)" % (rating, userid))
else:
statement.append(" AND su.rating <= %i" % (rating,))
if otherid:
statement.append(" AND su.userid = %i" % (otherid,))
if folderid:
statement.append(" AND su.folderid = %i" % (folderid,))
if exclude:
statement.append(" AND su.submitid != %i" % (exclude,))
if subcat:
statement.append(" AND su.subtype >= %i AND su.subtype < %i" % (subcat, subcat + 1000))
if "critique" in options:
statement.append(" AND su.settings ~ 'q' AND su.unixtime > %i" % (d.get_time() - 259200,))
if backid:
statement.append(" AND su.submitid > %i" % (backid,))
elif nextid:
statement.append(" AND su.submitid < %i" % (nextid,))
elif "offset" in options:
statement.append(" AND su.unixtime < %i" % (d.get_time() - 1800,))
if userid:
statement.append(m.MACRO_FRIENDUSER_SUBMIT % (userid, userid, userid))
if not otherid:
statement.append(m.MACRO_IGNOREUSER % (userid, "su"))
statement.append(m.MACRO_BLOCKTAG_SUBMIT % (userid, userid))
else:
statement.append(" AND su.settings !~ 'f'")
return statement
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_query(userid, rating, otherid=None, pending=False, backid=None, nextid=None, config=None, options=[]):
"""
Build a query to select a list of collections, joined on submission table
and profile of the submitter
:param userid: the user accessing this content.
:param rating: maximum rating of collections to display
:param otherid: the owner of the collections being accessed (collector)
:param pending: TRUE to give only pending collections, otherwise give accepted ones.
:param backid: will not return submissions older than the one with this ID
:param nextid: will not return submissions newer than the one with this ID
:param config: unused
:param options: unused
:return: a statement created based on options given
"""
statement = [
"FROM collection co INNER JOIN"
" submission su ON co.submitid = su.submitid"
" INNER JOIN profile pr ON su.userid = pr.userid"
" INNER JOIN profile cpr ON co.userid = cpr.userid"
" WHERE su.settings !~ 'h'"]
# filter own content in SFW mode
if d.is_sfw_mode():
statement.append(" AND (su.rating <= %i)" % (rating,))
else:
statement.append(" AND (su.rating <= %i OR co.userid = %i OR su.userid = %i)" % (rating, userid, userid))
if pending:
# in the case of pending, for OFFERED (p) show those collected by me
# for REQUESTED (r) show those authored by me
statement.append(" AND ((co.settings ~ 'p' AND co.userid = %i)"
" OR (co.settings ~ 'r' AND su.userid = %i))"
% (otherid, userid))
else:
statement.append(" AND co.settings !~ '[pr]'")
statement.append(" AND co.userid = %i" % otherid)
if backid:
statement.append(" AND co.unixtime > (SELECT unixtime FROM collection WHERE (userid, submitid) = (%i, %i))"
% (otherid, backid))
elif nextid:
statement.append(" AND co.unixtime < (SELECT unixtime FROM collection WHERE (userid, submitid) = (%i, %i))"
% (otherid, nextid))
if userid:
statement.append(m.MACRO_FRIENDUSER_SUBMIT % (userid, userid, userid))
if not otherid:
statement.append(m.MACRO_IGNOREUSER % (userid, "su"))
statement.append(m.MACRO_BLOCKTAG_SUBMIT % (userid, userid))
else:
statement.append(" AND su.settings !~ 'f'")
return statement
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_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_journal_and_check(userid,
journalid,
rating=None,
ignore=True,
anyway=False,
increment_views=True):
"""Selects a journal, after checking if the user is authorized, etc.
Args:
userid (int): Currently authenticating user ID.
journalid (int): Character ID to fetch.
rating (int): Maximum rating to display. Defaults to None.
ignore (bool): Whether to respect ignored or blocked tags. Defaults to True.
anyway (bool): Whether ignore checks and display anyway. Defaults to False.
increment_views (bool): Whether to increment the number of views on the submission. Defaults to True.
Returns:
A journal and all needed data as a dict.
"""
query = d.engine.execute("""
SELECT jo.userid, pr.username, jo.unixtime, jo.title, jo.content, jo.rating, jo.settings, jo.page_views, pr.config
FROM journal jo JOIN profile pr ON jo.userid = pr.userid
WHERE jo.journalid = %(id)s
""",
id=journalid).first()
if not query:
# If there's no query result, there's no record, so fast-fail.
raise WeasylError('journalRecordMissing')
elif journalid and userid in staff.MODS and anyway:
pass
elif not query or 'h' in query.settings:
raise WeasylError('journalRecordMissing')
elif query.rating > rating and (
(userid != query.userid and userid not in staff.MODS)
or d.is_sfw_mode()):
raise WeasylError('RatingExceeded')
elif 'f' in query.settings and not frienduser.check(userid, query.userid):
raise WeasylError('FriendsOnly')
elif ignore and ignoreuser.check(userid, query.userid):
raise WeasylError('UserIgnored')
elif ignore and blocktag.check(userid, journalid=journalid):
raise WeasylError('TagBlocked')
query = dict(query)
if increment_views and d.common_view_content(userid, journalid, 'journal'):
query['page_views'] += 1
return query
def _select_character_and_check(userid,
charid,
rating=None,
ignore=True,
anyway=False,
increment_views=True):
"""Selects a character, after checking if the user is authorized, etc.
Args:
userid (int): Currently authenticating user ID.
charid (int): Character ID to fetch.
rating (int): Maximum rating to display. Defaults to None.
ignore (bool): Whether to respect ignored or blocked tags. Defaults to True.
anyway (bool): Whether to ignore checks and display anyway. Defaults to False.
increment_views (bool): Whether to increment the number of views on the submission. Defaults to True.
Returns:
A character and all needed data as a dict.
"""
query = define.engine.execute("""
SELECT
ch.userid, 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 USING (userid)
WHERE ch.charid = %(charid)s
""",
charid=charid).first()
if query and userid in staff.MODS and anyway:
pass
elif not query or 'h' in query.settings:
raise WeasylError('characterRecordMissing')
elif query.rating > rating and (
(userid != query.userid and userid not in staff.MODS)
or define.is_sfw_mode()):
raise WeasylError('RatingExceeded')
elif 'f' in query.settings and not frienduser.check(userid, query.userid):
raise WeasylError('FriendsOnly')
elif ignore and ignoreuser.check(userid, query.userid):
raise WeasylError('UserIgnored')
elif ignore and blocktag.check(userid, charid=charid):
raise WeasylError('TagBlocked')
query = dict(query)
if increment_views and define.common_view_content(userid, charid, 'char'):
query['page_views'] += 1
return query
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_near(userid, rating, limit, otherid, folderid, submitid):
statement = [
"""
SELECT su.submitid, su.title, su.rating, su.unixtime, su.userid,
pr.username, 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:
if d.is_sfw_mode():
statement.append(" AND su.rating <= %i" % (rating, ))
else:
# Outside of SFW mode, 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[6],
} 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_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_submit_query(userid,
rating,
otherid=None,
backid=None,
nextid=None,
config=None):
if config is None:
config = d.get_config(userid)
statement = [
" FROM favorite fa INNER JOIN"
" submission su ON fa.targetid = su.submitid"
" INNER JOIN profile pr ON su.userid = pr.userid"
" WHERE fa.type = 's' AND su.settings !~ 'h'"
]
if userid:
# filter own content in SFW mode
if d.is_sfw_mode():
statement.append(" AND (su.rating <= %i)" % (rating, ))
else:
statement.append(" AND (su.userid = %i OR su.rating <= %i)" %
(userid, rating))
statement.append(m.MACRO_IGNOREUSER % (userid, "su"))
statement.append(m.MACRO_BLOCKTAG_SUBMIT % (userid, userid))
statement.append(m.MACRO_FRIENDUSER_SUBMIT % (userid, userid, userid))
else:
statement.append(" AND su.rating <= %i" % (rating, ))
statement.append(" AND su.settings !~ 'f'")
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, 's'))"
% (otherid, backid))
elif nextid:
statement.append(
" AND fa.unixtime < "
"(SELECT unixtime FROM favorite WHERE (userid, targetid, type) = (%i, %i, 's'))"
% (otherid, nextid))
return statement
def select_latest(userid, rating, otherid=None, config=None):
if config is None:
config = d.get_config(userid)
statement = [
"SELECT jo.journalid, jo.title, jo.unixtime FROM journal jo WHERE"
]
if userid:
if d.is_sfw_mode():
statement.append(" (jo.rating <= %i)" % (rating, ))
else:
statement.append(" (jo.userid = %i OR jo.rating <= %i)" %
(userid, rating))
if not otherid:
statement.append(m.MACRO_IGNOREUSER % (userid, "jo"))
statement.append(m.MACRO_BLOCKTAG_JOURNAL % (userid, userid))
else:
statement.append(" jo.rating <= %i" % (rating, ))
if otherid:
statement.append(
" AND jo.userid = %i AND jo.settings !~ '[%sh]'" %
(otherid, "" if frienduser.check(userid, otherid) else "f"))
statement.append("ORDER BY jo.journalid DESC LIMIT 1")
query = d.execute("".join(statement), options="single")
if query:
return {
"journalid":
query[0],
"title":
query[1],
"unixtime":
query[2],
"content":
files.read("%s%s%i.txt" % (m.MACRO_SYS_JOURNAL_PATH,
d.get_hash_path(query[0]), query[0])),
"comments":
d.execute(
"SELECT COUNT(*) FROM journalcomment WHERE targetid = %i AND settings !~ 'h'",
[query[0]], ["element"]),
}
def select_submit_query(userid, rating, otherid=None, backid=None, nextid=None, config=None):
if config is None:
config = d.get_config(userid)
statement = [
" FROM favorite fa INNER JOIN"
" submission su ON fa.targetid = su.submitid"
" INNER JOIN profile pr ON su.userid = pr.userid"
" WHERE fa.type = 's' AND su.settings !~ 'h'"]
if userid:
# filter own content in SFW mode
if d.is_sfw_mode():
statement.append(" AND (su.rating <= %i)" % (rating,))
else:
statement.append(" AND (su.userid = %i OR su.rating <= %i)" % (userid, rating))
statement.append(m.MACRO_IGNOREUSER % (userid, "su"))
statement.append(m.MACRO_BLOCKTAG_SUBMIT % (userid, userid))
statement.append(m.MACRO_FRIENDUSER_SUBMIT % (userid, userid, userid))
else:
statement.append(" AND su.rating <= %i" % (rating,))
statement.append(" AND su.settings !~ 'f'")
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, 's'))"
% (otherid, backid))
elif nextid:
statement.append(" AND fa.unixtime < "
"(SELECT unixtime FROM favorite WHERE (userid, targetid, type) = (%i, %i, 's'))"
% (otherid, nextid))
return statement
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_view(userid, submitid, rating, ignore=True, anyway=None):
query = d.execute("""
SELECT
su.userid, pr.username, su.folderid, su.unixtime, su.title, su.content, su.subtype, su.rating, su.settings,
su.page_views, su.sorttime, pr.config, fd.title
FROM submission su
INNER JOIN profile pr USING (userid)
LEFT JOIN folder fd USING (folderid)
WHERE su.submitid = %i
""", [submitid], options=["single", "list"])
# Sanity check
if query and userid in staff.MODS and anyway == "true":
pass
elif not query or "h" in query[8]:
raise WeasylError("submissionRecordMissing")
elif query[7] > rating and ((userid != query[0] and userid not in staff.MODS) or d.is_sfw_mode()):
raise WeasylError("RatingExceeded")
elif "f" in query[8] and not frienduser.check(userid, query[0]):
raise WeasylError("FriendsOnly")
elif ignore and ignoreuser.check(userid, query[0]):
raise WeasylError("UserIgnored")
elif ignore and blocktag.check(userid, submitid=submitid):
raise WeasylError("TagBlocked")
# Get submission filename
submitfile = media.get_submission_media(submitid).get('submission', [None])[0]
# Get submission text
if submitfile and submitfile['file_type'] in ['txt', 'htm']:
submittext = files.read(submitfile['full_file_path'])
else:
submittext = None
embedlink = d.text_first_line(query[5]) if "v" in query[8] else None
google_doc_embed = None
if 'D' in query[8]:
db = d.connect()
gde = d.meta.tables['google_doc_embeds']
q = (sa.select([gde.c.embed_url])
.where(gde.c.submitid == submitid))
results = db.execute(q).fetchall()
if not results:
raise WeasylError("can't find embed information")
google_doc_embed = results[0]
tags, artist_tags = searchtag.select_with_artist_tags(submitid)
settings = d.get_profile_settings(query[0])
return {
"submitid": submitid,
"userid": query[0],
"username": query[1],
"folderid": query[2],
"unixtime": query[3],
"title": query[4],
"content": (d.text_first_line(query[5], strip=True) if "v" in query[8] else query[5]),
"subtype": query[6],
"rating": query[7],
"settings": query[8],
"page_views": (
query[9] + 1 if d.common_view_content(userid, 0 if anyway == "true" else submitid, "submit") else query[9]),
"fave_count": d.execute(
"SELECT COUNT(*) FROM favorite WHERE (targetid, type) = (%i, 's')",
[submitid], ["element"]),
"mine": userid == query[0],
"reported": report.check(submitid=submitid),
"favorited": favorite.check(userid, submitid=submitid),
"friends_only": "f" in query[8],
"hidden_submission": "h" in query[8],
"collectors": collection.find_owners(submitid),
"no_request": not settings.allow_collection_requests,
"text": submittext,
"sub_media": media.get_submission_media(submitid),
"user_media": media.get_user_media(query[0]),
"submit": submitfile,
"embedlink": embedlink,
"embed": embed.html(embedlink) if embedlink is not None else None,
"google_doc_embed": google_doc_embed,
"tags": tags,
"artist_tags": artist_tags,
"removable_tags": searchtag.removable_tags(userid, query[0], tags, artist_tags),
"can_remove_tags": searchtag.can_remove_tags(userid, query[0]),
"folder_more": select_near(userid, rating, 1, query[0], query[2], submitid),
"folder_title": query[12] if query[12] else "Root",
"comments": comment.select(userid, submitid=submitid),
}
def select_view(userid, submitid, rating, ignore=True, anyway=None):
query = d.engine.execute("""
SELECT
su.userid, pr.username, su.folderid, su.unixtime, su.title, su.content, su.subtype, su.rating, su.settings,
su.page_views, fd.title, su.favorites, su.image_representations
FROM submission su
INNER JOIN profile pr USING (userid)
LEFT JOIN folder fd USING (folderid)
WHERE su.submitid = %(id)s
""",
id=submitid).first()
# Sanity check
if query and userid in staff.MODS and anyway == "true":
pass
elif not query or "h" in query[8]:
raise WeasylError("submissionRecordMissing")
elif query[7] > rating and (
(userid != query[0] and userid not in staff.MODS) or d.is_sfw_mode()):
raise WeasylError("RatingExceeded")
elif "f" in query[8] and not frienduser.check(userid, query[0]):
raise WeasylError("FriendsOnly")
elif ignore and ignoreuser.check(userid, query[0]):
raise WeasylError("UserIgnored")
elif ignore and blocktag.check(userid, submitid=submitid):
raise WeasylError("TagBlocked")
# Get submission filename
submitfile = media.get_submission_media(submitid).get(
'submission', [None])[0]
# Get submission text
if submitfile and submitfile['file_type'] in ['txt', 'htm']:
submittext = files.read(submitfile['full_file_path'])
else:
submittext = None
embedlink = d.text_first_line(query[5]) if "v" in query[8] else None
google_doc_embed = None
if 'D' in query[8]:
db = d.connect()
gde = d.meta.tables['google_doc_embeds']
q = (sa.select([gde.c.embed_url]).where(gde.c.submitid == submitid))
results = db.execute(q).fetchall()
if not results:
raise WeasylError("can't find embed information")
google_doc_embed = results[0]
tags, artist_tags = searchtag.select_with_artist_tags(submitid)
settings = d.get_profile_settings(query[0])
if query[12] is None:
sub_media = media.get_submission_media(submitid)
else:
sub_media = media.deserialize_image_representations(query[12])
return {
"submitid":
submitid,
"userid":
query[0],
"username":
query[1],
"folderid":
query[2],
"unixtime":
query[3],
"title":
query[4],
"content": (d.text_first_line(query[5], strip=True)
if "v" in query[8] else query[5]),
"subtype":
query[6],
"rating":
query[7],
"settings":
query[8],
"page_views": (query[9] + 1 if d.common_view_content(
userid, 0 if anyway == "true" else submitid, "submit") else
query[9]),
"fave_count":
query[11],
"mine":
userid == query[0],
"reported":
report.check(submitid=submitid),
"favorited":
favorite.check(userid, submitid=submitid),
"friends_only":
"f" in query[8],
"hidden_submission":
"h" in query[8],
"collected":
collection.owns(userid, submitid),
"no_request":
not settings.allow_collection_requests,
"text":
submittext,
"sub_media":
sub_media,
"user_media":
media.get_user_media(query[0]),
"submit":
submitfile,
"embedlink":
embedlink,
"embed":
embed.html(embedlink) if embedlink is not None else None,
"google_doc_embed":
google_doc_embed,
"tags":
tags,
"artist_tags":
artist_tags,
"removable_tags":
searchtag.removable_tags(userid, query[0], tags, artist_tags),
"can_remove_tags":
searchtag.can_remove_tags(userid, query[0]),
"folder_more":
select_near(userid, rating, 1, query[0], query[2], submitid),
"folder_title":
query[10] if query[10] else "Root",
"comments":
comment.select(userid, submitid=submitid),
}
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_journal_and_check(userid, journalid, rating=None, ignore=True, anyway=False, increment_views=True):
"""Selects a journal, after checking if the user is authorized, etc.
Args:
userid (int): Currently authenticating user ID.
journalid (int): Character ID to fetch.
rating (int): Maximum rating to display. Defaults to None.
ignore (bool): Whether to respect ignored or blocked tags. Defaults to True.
anyway (bool): Whether ignore checks and display anyway. Defaults to False.
increment_views (bool): Whether to increment the number of views on the submission. Defaults to True.
Returns:
A journal and all needed data as a dict.
"""
query = d.engine.execute("""
SELECT jo.userid, pr.username, jo.unixtime, jo.title, jo.content, jo.rating, jo.settings, jo.page_views, pr.config
FROM journal jo JOIN profile pr ON jo.userid = pr.userid
WHERE jo.journalid = %(id)s
""", id=journalid).first()
if journalid and userid in staff.MODS and anyway:
pass
elif not query or 'h' in query.settings:
raise WeasylError('journalRecordMissing')
elif query.rating > rating and ((userid != query.userid and userid not in staff.MODS) or d.is_sfw_mode()):
raise WeasylError('RatingExceeded')
elif 'f' in query.settings and not frienduser.check(userid, query.userid):
raise WeasylError('FriendsOnly')
elif ignore and ignoreuser.check(userid, query.userid):
raise WeasylError('UserIgnored')
elif ignore and blocktag.check(userid, journalid=journalid):
raise WeasylError('TagBlocked')
query = dict(query)
if increment_views and d.common_view_content(userid, journalid, 'journal'):
query['page_views'] += 1
return query
def _select_character_and_check(userid, charid, rating=None, ignore=True, anyway=False, increment_views=True):
"""Selects a character, after checking if the user is authorized, etc.
Args:
userid (int): Currently authenticating user ID.
charid (int): Character ID to fetch.
rating (int): Maximum rating to display. Defaults to None.
ignore (bool): Whether to respect ignored or blocked tags. Defaults to True.
anyway (bool): Whether to ignore checks and display anyway. Defaults to False.
increment_views (bool): Whether to increment the number of views on the submission. Defaults to True.
Returns:
A character and all needed data as a dict.
"""
query = define.engine.execute("""
SELECT
ch.userid, 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 USING (userid)
WHERE ch.charid = %(charid)s
""", charid=charid).first()
if query and userid in staff.MODS and anyway:
pass
elif not query or 'h' in query.settings:
raise WeasylError('characterRecordMissing')
elif query.rating > rating and ((userid != query.userid and userid not in staff.MODS) or define.is_sfw_mode()):
raise WeasylError('RatingExceeded')
elif 'f' in query.settings and not frienduser.check(userid, query.userid):
raise WeasylError('FriendsOnly')
elif ignore and ignoreuser.check(userid, query.userid):
raise WeasylError('UserIgnored')
elif ignore and blocktag.check(userid, charid=charid):
raise WeasylError('TagBlocked')
query = dict(query)
if increment_views and define.common_view_content(userid, charid, 'char'):
query['page_views'] += 1
return query
def select_preview(userid, otherid, rating, limit=3):
"""
Picks out random folders up to the limit, and get a count, name, and random
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 users whose folders we're viewing.
rating: The maximum rating of submissions that will be considered for
counts or a preview.
limit: The maximum number of folders to consider. Defaults to 3.
Returns:
An array of dicts, each of which has a folderid, a title, a count, and
sub_media to use for a preview.
"""
query = []
folder_query = d.engine.execute("""
SELECT
fd.folderid, fd.title,
(SELECT COUNT(*)
FROM submission su
WHERE folderid = fd.folderid
AND settings !~ '[hu]'
AND (rating <= %(rating)s OR (userid = %(userid)s AND NOT %(sfwmode)s))
AND (settings !~ 'f'
OR su.userid = %(userid)s
OR EXISTS (SELECT 0
FROM frienduser
WHERE ((userid, otherid) = (%(userid)s, su.userid)
OR (userid, otherid) = (su.userid, %(userid)s))
AND settings !~ 'p')))
FROM folder fd
WHERE fd.userid = %(otherid)s
AND fd.settings !~ '[hu]'
AND EXISTS (SELECT 0 FROM submission
WHERE folderid = fd.folderid
AND (rating <= %(rating)s OR (userid = %(userid)s AND NOT %(sfwmode)s)))
ORDER BY RANDOM()
LIMIT %(limit)s
""", rating=rating, userid=userid, otherid=otherid, limit=limit, sfwmode=d.is_sfw_mode())
for i in folder_query:
submit = d.engine.execute("""
SELECT submitid, settings FROM submission su
WHERE (rating <= %(rating)s OR (userid = %(userid)s AND NOT %(sfwmode)s))
AND folderid = %(folderid)s AND settings !~ 'h'
AND (settings !~ 'f' OR su.userid = %(userid)s
OR EXISTS (SELECT 0 FROM frienduser
WHERE ((userid, otherid) = (%(userid)s, su.userid)
OR (userid, otherid) = (su.userid, %(userid)s))
AND settings !~ 'p'))
ORDER BY RANDOM() LIMIT 1
""", rating=rating, folderid=i.folderid, userid=userid, sfwmode=d.is_sfw_mode()).first()
if submit:
query.append({
"folderid": i.folderid,
"title": i.title,
"count": i.count,
"userid": otherid,
"sub_media": media.get_submission_media(submit.submitid),
})
return query
