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_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