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