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_streaming(userid, rating, limit, following=True, order_by=None):
statement = [
"SELECT userid, pr.username, pr.stream_url, pr.config, pr.stream_text, start_time "
"FROM profile pr "
"JOIN user_streams USING (userid) "
"WHERE end_time > %i" % (d.get_time(),)
]
if userid:
statement.append(m.MACRO_IGNOREUSER % (userid, "pr"))
if following:
pass # todo
if order_by:
statement.append(" ORDER BY %s LIMIT %i" % (order_by, limit))
else:
statement.append(" ORDER BY RANDOM() LIMIT %i" % limit)
ret = [{
"userid": i[0],
"username": i[1],
"stream_url": i[2],
"stream_text": i[4],
"stream_time": i[5],
} for i in d.execute("".join(statement)) if i[2]]
media.populate_with_user_media(ret)
return ret
def select_streaming(userid, rating, limit, following=True, order_by=None):
statement = [
"SELECT userid, pr.username, pr.stream_url, pr.config, pr.stream_text, start_time "
"FROM profile pr "
"JOIN user_streams USING (userid) "
"WHERE end_time > %i" % (d.get_time(), )
]
if userid:
statement.append(m.MACRO_IGNOREUSER % (userid, "pr"))
if following:
pass # todo
if order_by:
statement.append(" ORDER BY %s LIMIT %i" % (order_by, limit))
else:
statement.append(" ORDER BY RANDOM() LIMIT %i" % limit)
ret = [{
"userid": i[0],
"username": i[1],
"stream_url": i[2],
"stream_text": i[4],
"stream_time": i[5],
} for i in d.execute("".join(statement)) if i[2]]
media.populate_with_user_media(ret)
return ret
def offer(userid, submitid, otherid):
query = d.execute("SELECT userid, rating, settings FROM submission WHERE submitid = %i",
[submitid], options="single")
if not query or "h" in query[2]:
raise WeasylError("Unexpected")
elif userid != query[0]:
raise WeasylError("Unexpected")
# Check collection acceptability
if otherid:
rating = d.get_rating(otherid)
if rating < query[1]:
raise WeasylError("collectionUnacceptable")
if "f" in query[2]:
raise WeasylError("collectionUnacceptable")
if ignoreuser.check(otherid, userid):
raise WeasylError("IgnoredYou")
if ignoreuser.check(userid, otherid):
raise WeasylError("YouIgnored")
if blocktag.check(otherid, submitid=submitid):
raise WeasylError("collectionUnacceptable")
try:
d.execute("INSERT INTO collection (userid, submitid, unixtime) VALUES (%i, %i, %i)",
[otherid, submitid, d.get_time()])
except PostgresError:
raise WeasylError("collectionExists")
welcome.collectoffer_insert(userid, otherid, submitid)
def force_resetbirthday(userid, birthday):
if not birthday:
raise WeasylError("birthdayInvalid")
elif birthday > d.get_time():
raise WeasylError("birthdayInvalid")
d.execute("UPDATE userinfo SET birthday = %i WHERE userid = %i", [birthday, userid])
d.execute("UPDATE login SET settings = REPLACE(settings, 'i', '') WHERE userid = %i", [userid])
d.get_login_settings.invalidate(userid)
def edit_streaming_settings(my_userid,
userid,
profile,
set_stream=None,
stream_length=0):
if set_stream == 'start':
try:
stream_length = int(stream_length)
except:
raise WeasylError("streamDurationOutOfRange")
if stream_length < 1 or stream_length > 360:
raise WeasylError("streamDurationOutOfRange")
if set_stream == 'start' and not profile.stream_url:
raise WeasylError("streamLocationNotSet")
# unless we're specifically still streaming, clear the user_streams record
if set_stream != 'still':
d.execute("DELETE FROM user_streams WHERE userid = %i", [userid])
settings_flag = ''
stream_status = None
# if we're starting to stream, update user_streams to reflect that
if set_stream == 'start':
now = d.get_time()
stream_end = now + stream_length * 60 # stream_length is minutes; we need seconds
d.execute("INSERT INTO user_streams VALUES (%i, %i, %i)",
[userid, now, stream_end])
stream_status = 'n'
# if we're going to stream later, update profile.settings to reflect that
elif set_stream == 'later':
settings_flag = stream_status = 'l'
# if stream_status is None, any rows in `welcome` will get cleared. but, if
# the user is still streaming, that shouldn't happen. otherwise, `welcome`
# will get updated with the current stream state.
if set_stream != 'still':
welcome.stream_insert(userid, stream_status)
d.execute(
"UPDATE profile "
"SET (stream_text, stream_url, settings) = ('%s', '%s', REGEXP_REPLACE(settings, '[nli]', '') || '%s') "
"WHERE userid = %i",
[profile.stream_text, profile.stream_url, settings_flag, userid])
if my_userid != userid:
from weasyl import moderation
note_body = ('- Stream url: %s\n'
'- Stream description: %s\n'
'- Stream status: %s' %
(profile.stream_url, profile.stream_text,
STREAMING_ACTION_MAP[set_stream]))
moderation.note_about(my_userid, userid, 'Streaming settings updated:',
note_body)
def force_resetbirthday(userid, birthday):
if not birthday:
raise WeasylError("birthdayInvalid")
elif birthday > d.get_time():
raise WeasylError("birthdayInvalid")
d.execute("UPDATE userinfo SET birthday = %i WHERE userid = %i",
[birthday, userid])
d.execute(
"UPDATE login SET settings = REPLACE(settings, 'i', '') WHERE userid = %i",
[userid])
d.get_login_settings.invalidate(userid)
def insert(userid, submitid=None, charid=None, journalid=None):
if submitid:
content_table, id_field, target = "submission", "submitid", submitid
elif charid:
content_table, id_field, target = "character", "charid", charid
else:
content_table, id_field, target = "journal", "journalid", journalid
query = d.execute("SELECT userid, settings FROM %s WHERE %s = %i",
[content_table, id_field, target],
options="single")
if not query:
raise WeasylError("TargetRecordMissing")
elif userid == query[0]:
raise WeasylError("CannotSelfFavorite")
elif "f" in query[1] and not frienduser.check(userid, query[0]):
raise WeasylError("FriendsOnly")
elif ignoreuser.check(userid, query[0]):
raise WeasylError("YouIgnored")
elif ignoreuser.check(query[0], userid):
raise WeasylError("contentOwnerIgnoredYou")
try:
d.execute("INSERT INTO favorite VALUES (%i, %i, '%s', %i)", [
userid,
d.get_targetid(submitid, charid, journalid),
"s" if submitid else "f" if charid else "j",
d.get_time()
])
except PostgresError:
raise WeasylError("favoriteRecordExists")
# create a list of users to notify
notified = set(collection.find_owners(submitid))
# conditions under which "other" should be notified
def can_notify(other):
other_jsonb = d.get_profile_settings(other)
allow_notify = other_jsonb.allow_collection_notifs
not_ignored = not ignoreuser.check(other, userid)
return allow_notify and not_ignored
notified = set(filter(can_notify, notified))
# always notify for own content
notified.add(query[0])
for other in notified:
welcome.favorite_insert(userid,
submitid=submitid,
charid=charid,
journalid=journalid,
otherid=other)
def edit_streaming_settings(my_userid, userid, profile, set_stream=None, stream_length=0):
if set_stream == 'start':
try:
stream_length = int(stream_length)
except:
raise WeasylError("streamDurationOutOfRange")
if stream_length < 1 or stream_length > 360:
raise WeasylError("streamDurationOutOfRange")
if set_stream == 'start' and not profile.stream_url:
raise WeasylError("streamLocationNotSet")
# unless we're specifically still streaming, clear the user_streams record
if set_stream != 'still':
d.execute("DELETE FROM user_streams WHERE userid = %i", [userid])
settings_flag = ''
stream_status = None
# if we're starting to stream, update user_streams to reflect that
if set_stream == 'start':
now = d.get_time()
stream_end = now + stream_length * 60 # stream_length is minutes; we need seconds
d.execute("INSERT INTO user_streams VALUES (%i, %i, %i)", [userid, now, stream_end])
stream_status = 'n'
# if we're going to stream later, update profile.settings to reflect that
elif set_stream == 'later':
settings_flag = stream_status = 'l'
# if stream_status is None, any rows in `welcome` will get cleared. but, if
# the user is still streaming, that shouldn't happen. otherwise, `welcome`
# will get updated with the current stream state.
if set_stream != 'still':
welcome.stream_insert(userid, stream_status)
d.execute(
"UPDATE profile "
"SET (stream_text, stream_url, settings) = ('%s', '%s', REGEXP_REPLACE(settings, '[nli]', '') || '%s') "
"WHERE userid = %i",
[profile.stream_text, profile.stream_url, settings_flag, userid])
if my_userid != userid:
from weasyl import moderation
note_body = (
'- Stream url: %s\n'
'- Stream description: %s\n'
'- Stream status: %s' % (profile.stream_url, profile.stream_text, STREAMING_ACTION_MAP[set_stream]))
moderation.note_about(my_userid, userid, 'Streaming settings updated:', note_body)
def select_profile(userid,
avatar=False,
banner=False,
propic=False,
images=False,
commish=True,
viewer=None):
query = d.execute(
"""
SELECT pr.username, pr.full_name, pr.catchphrase, pr.unixtime, pr.profile_text,
pr.settings, pr.stream_url, pr.config, pr.stream_text, lo.settings, us.end_time
FROM profile pr
INNER JOIN login lo USING (userid)
LEFT JOIN user_streams us USING (userid)
WHERE userid = %i
""", [userid], ["single"])
if not query:
raise WeasylError('RecordMissing')
streaming_status = "stopped"
if query[6]: # profile.stream_url
if query[10] > d.get_time(): # user_streams.end_time
streaming_status = "started"
elif 'l' in query[5]:
streaming_status = "later"
return {
"userid": userid,
"user_media": media.get_user_media(userid),
"username": query[0],
"full_name": query[1],
"catchphrase": query[2],
"unixtime": query[3],
"profile_text": query[4],
"settings": query[5],
"stream_url": query[6],
"stream_text": query[8],
"config": query[7],
"show_favorites_bar": "u" not in query[7] and "v" not in query[7],
"show_favorites_tab": userid == viewer or "v" not in query[7],
"commish_slots": 0,
"banned": "b" in query[9],
"suspended": "s" in query[9],
"streaming_status": streaming_status,
}
def pending_accept(userid, submissions):
if not submissions:
return
d.engine.execute(
"UPDATE collection SET "
"unixtime = %(now)s, "
"settings = REGEXP_REPLACE(settings, '[pr]', '') "
"WHERE settings ~ '[pr]' "
"AND (submitid, userid) = ANY (%(submissions)s)",
submissions=submissions, now=d.get_time())
for s in submissions:
welcome.collection_insert(s[1], s[0])
welcome.collectrequest_remove(userid, s[1], s[0])
d._page_header_info.invalidate(userid)
def insert(userid, submitid=None, charid=None, journalid=None):
if submitid:
content_table, id_field, target = "submission", "submitid", submitid
elif charid:
content_table, id_field, target = "character", "charid", charid
else:
content_table, id_field, target = "journal", "journalid", journalid
query = d.execute("SELECT userid, settings FROM %s WHERE %s = %i",
[content_table, id_field, target], options="single")
if not query:
raise WeasylError("TargetRecordMissing")
elif userid == query[0]:
raise WeasylError("CannotSelfFavorite")
elif "f" in query[1] and not frienduser.check(userid, query[0]):
raise WeasylError("FriendsOnly")
elif ignoreuser.check(userid, query[0]):
raise WeasylError("YouIgnored")
elif ignoreuser.check(query[0], userid):
raise WeasylError("contentOwnerIgnoredYou")
try:
d.execute("INSERT INTO favorite VALUES (%i, %i, '%s', %i)", [
userid, d.get_targetid(submitid, charid, journalid),
"s" if submitid else "f" if charid else "j", d.get_time()
])
except PostgresError:
raise WeasylError("favoriteRecordExists")
# create a list of users to notify
notified = set(collection.find_owners(submitid))
# conditions under which "other" should be notified
def can_notify(other):
other_jsonb = d.get_profile_settings(other)
allow_notify = other_jsonb.allow_collection_notifs
not_ignored = not ignoreuser.check(other, userid)
return allow_notify and not_ignored
notified = set(filter(can_notify, notified))
# always notify for own content
notified.add(query[0])
for other in notified:
welcome.favorite_insert(userid, submitid=submitid, charid=charid, journalid=journalid, otherid=other)
def request(userid, submitid, otherid):
query = d.engine.execute(
"SELECT userid, rating, settings "
"FROM submission WHERE submitid = %(submission)s",
submission=submitid).first()
rating = d.get_rating(userid)
if not query or "h" in query.settings:
raise WeasylError("Unexpected")
if otherid != query.userid:
raise WeasylError("Unexpected")
# not checking for blocktags here because if you want to collect
# something with a tag you don't like that's your business
if rating < query.rating:
raise WeasylError("RatingExceeded")
if "f" in query.settings:
raise WeasylError("collectionUnacceptable")
if ignoreuser.check(otherid, userid):
raise WeasylError("IgnoredYou")
if ignoreuser.check(userid, otherid):
raise WeasylError("YouIgnored")
if _check_throttle(userid, otherid):
raise WeasylError("collectionThrottle")
settings = d.get_profile_settings(otherid)
if not settings.allow_collection_requests:
raise WeasylError("Unexpected")
request_settings = "r"
try:
d.engine.execute(
"INSERT INTO collection (userid, submitid, unixtime, settings) "
"VALUES (%(userid)s, %(submitid)s, %(now)s, %(settings)s)",
userid=userid,
submitid=submitid,
now=d.get_time(),
settings=request_settings)
except PostgresError:
raise WeasylError("collectionExists")
welcome.collectrequest_insert(userid, otherid, submitid)
def select_profile(userid, avatar=False, banner=False, propic=False, images=False, commish=True, viewer=None):
query = d.execute("""
SELECT pr.username, pr.full_name, pr.catchphrase, pr.unixtime, pr.profile_text,
pr.settings, pr.stream_url, pr.config, pr.stream_text, lo.settings, us.end_time
FROM profile pr
INNER JOIN login lo USING (userid)
LEFT JOIN user_streams us USING (userid)
WHERE userid = %i
""", [userid], ["single"])
if not query:
raise WeasylError('RecordMissing')
streaming_status = "stopped"
if query[6]: # profile.stream_url
if query[10] > d.get_time(): # user_streams.end_time
streaming_status = "started"
elif 'l' in query[5]:
streaming_status = "later"
return {
"userid": userid,
"user_media": media.get_user_media(userid),
"username": query[0],
"full_name": query[1],
"catchphrase": query[2],
"unixtime": query[3],
"profile_text": query[4],
"settings": query[5],
"stream_url": query[6],
"stream_text": query[8],
"config": query[7],
"show_favorites_bar": "u" not in query[7] and "v" not in query[7],
"show_favorites_tab": userid == viewer or "v" not in query[7],
"commish_slots": 0,
"banned": "b" in query[9],
"suspended": "s" in query[9],
"streaming_status": streaming_status,
}
def request(userid, submitid, otherid):
query = d.engine.execute("SELECT userid, rating, settings "
"FROM submission WHERE submitid = %(submission)s",
submission=submitid).first()
rating = d.get_rating(userid)
if not query or "h" in query.settings:
raise WeasylError("Unexpected")
if otherid != query.userid:
raise WeasylError("Unexpected")
# not checking for blocktags here because if you want to collect
# something with a tag you don't like that's your business
if rating < query.rating:
raise WeasylError("RatingExceeded")
if "f" in query.settings:
raise WeasylError("collectionUnacceptable")
if ignoreuser.check(otherid, userid):
raise WeasylError("IgnoredYou")
if ignoreuser.check(userid, otherid):
raise WeasylError("YouIgnored")
if _check_throttle(userid, otherid):
raise WeasylError("collectionThrottle")
settings = d.get_profile_settings(otherid)
if not settings.allow_collection_requests:
raise WeasylError("Unexpected")
request_settings = "r"
try:
d.engine.execute("INSERT INTO collection (userid, submitid, unixtime, settings) "
"VALUES (%(userid)s, %(submitid)s, %(now)s, %(settings)s)",
userid=userid, submitid=submitid, now=d.get_time(), settings=request_settings)
except PostgresError:
raise WeasylError("collectionExists")
welcome.collectrequest_insert(userid, otherid, submitid)
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
