def submissionsbyuser(userid, form): if userid not in staff.MODS: raise WeasylError("Unexpected") query = d.execute( """ SELECT su.submitid, su.title, su.rating, su.unixtime, su.userid, pr.username, su.settings FROM submission su INNER JOIN profile pr USING (userid) WHERE su.userid = (SELECT userid FROM login WHERE login_name = '%s') ORDER BY su.submitid DESC """, [d.get_sysname(form.name)]) ret = [{ "contype": 10, "submitid": i[0], "title": i[1], "rating": i[2], "unixtime": i[3], "userid": i[4], "username": i[5], "settings": i[6], } for i in query] media.populate_with_submission_media(ret) return ret
def select_list(userid, rating, limit, otherid=None, folderid=None, backid=None, nextid=None, subcat=None, options=[], profile_page_filter=False, index_page_filter=False, featured_filter=False): """ Selects a list from the submissions table. Args: userid: The current user rating: The maximum rating level to show limit: The number of submissions to get otherid: The user whose submissions to get folderid: Select submissions from this folder backid: Select the IDs that are less than this value nextid: Select the IDs that are greater than this value subcat: Select submissions whose subcategory is within this range (this value + 1000) options: List that can contain the following values: "critique": Submissions flagged for critique; additionally selects submissions newer than 3 days old "randomize": Randomize the ordering of the results profile_page_filter: Do not select from folders that should not appear on the profile page. index_page_filter: Do not select from folders that should not appear on the front page. featured_filter: Select from folders marked as featured submissions. Returns: An array with the following keys: "contype", "submitid", "title", "rating", "unixtime", "userid", "username", "subtype", "sub_media" """ if options not in [[], ['critique'], ['randomize']]: raise ValueError("Unexpected options: %r" % (options,)) randomize = bool(options) statement = [ "SELECT su.submitid, su.title, su.rating, su.unixtime, " "su.userid, pr.username, su.settings, su.subtype "] statement.extend(select_query( userid, rating, otherid, folderid, backid, nextid, subcat, options, profile_page_filter, index_page_filter, featured_filter)) statement.append( " ORDER BY %s%s LIMIT %i" % ("RANDOM()" if randomize else "su.submitid", "" if backid else " DESC", limit)) query = [{ "contype": 10, "submitid": i[0], "title": i[1], "rating": i[2], "unixtime": i[3], "userid": i[4], "username": i[5], "subtype": i[7], } for i in d.execute("".join(statement))] media.populate_with_submission_media(query) return query[::-1] if backid else query
def select_submit(userid, rating, limit, otherid=None, backid=None, nextid=None, config=None): statement = [ "SELECT su.submitid, su.title, su.rating, fa.unixtime, su.userid, pr.username, su.subtype" ] statement.extend( select_submit_query(userid, rating, otherid, backid, nextid, config)) statement.append(" ORDER BY fa.unixtime%s LIMIT %i" % ("" if backid else " DESC", limit)) 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))] media.populate_with_submission_media(query) return query[::-1] if backid else query
def select_recently_popular(): """ Get a list of recent, popular submissions. This operation is non-trivial and should not be used frequently without caching. To calculate scores, this method performs the following evaluation: item_score = log(item_fave_count) + log(item_view_counts) / 2 + submission_time / 180000 180000 is roughly two days. So intuitively an item two days old needs an order of magnitude more favorites/views compared to a fresh one. Also the favorites are quadratically more influential than views. The result is that this algorithm favors recent, heavily favorited items. :return: A list of submission dictionaries, in score-rank order. """ db = meta.Base.dbsession subq = (db.query( content.Favorite.targetid, sa.func.count().label('faves')).filter_by(type='s').group_by( content.Favorite.targetid).subquery()) score = ( sa.func.log(sa.func.greatest(sa.func.coalesce(subq.c.faves, 0), 1)) + sa.func.log(sa.func.greatest(content.Submission.page_views, 1)) / 2 + sa.cast(content.Submission.unixtime, sa.types.INTEGER) / 180000).label('score') q = (db.query(content.Submission, users.Profile, score).options(sa.orm.joinedload('tag_objects')).filter( ~content.Submission.is_hidden, ~content.Submission.is_friends_only).outerjoin( subq, content.Submission.submitid == subq.c.targetid).join( users.Profile, content.Submission.userid == users.Profile.userid).order_by(score.desc())) max_days = int(d.config_read_setting("popular_max_age_days", "21")) if max_days > 0: q = q.filter(content.Submission.unixtime > sa.func.extract( 'EPOCH', sa.func.now() - datetime.timedelta(days=max_days))) q = q.limit(128) submissions = [{ 'contype': 10, 'score': sc, 'submitid': s.submitid, 'title': s.title, 'rating': s.rating.code, 'subtype': s.subtype, 'unixtime': s.unixtime.timestamp, 'tags': list(s.tags), 'userid': s.userid, 'username': p.username, } for s, p, sc in q.all()] media.populate_with_submission_media(submissions) return submissions
def select_near(userid, rating, limit, otherid, folderid, submitid, config=None): if config is None: config = d.get_config(userid) statement = [ """ SELECT su.submitid, su.title, su.rating, su.unixtime, su.userid, pr.username, su.settings, 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: # 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[7], } 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_recently_popular(): """ Get a list of recent, popular submissions. This operation is non-trivial and should not be used frequently without caching. To calculate scores, this method performs the following evaluation: item_score = log(item_fave_count) + log(item_view_counts) / 2 + submission_time / 180000 180000 is roughly two days. So intuitively an item two days old needs an order of magnitude more favorites/views compared to a fresh one. Also the favorites are quadratically more influential than views. The result is that this algorithm favors recent, heavily favorited items. :return: A list of submission dictionaries, in score-rank order. """ db = meta.Base.dbsession subq = ( db.query(content.Favorite.targetid, sa.func.count().label('faves')) .filter_by(type='s') .group_by(content.Favorite.targetid) .subquery()) score = ( sa.func.log(sa.func.greatest(sa.func.coalesce(subq.c.faves, 0), 1)) + sa.func.log(sa.func.greatest(content.Submission.page_views, 1)) / 2 + sa.cast(content.Submission.unixtime, sa.types.INTEGER) / 180000).label('score') q = ( db.query(content.Submission, users.Profile, score) .options(sa.orm.joinedload('tag_objects')) .filter(~content.Submission.is_hidden, ~content.Submission.is_friends_only) .outerjoin(subq, content.Submission.submitid == subq.c.targetid) .join(users.Profile, content.Submission.userid == users.Profile.userid) .order_by(score.desc())) max_days = int(d.config_read_setting("popular_max_age_days", "21")) if max_days > 0: q = q.filter(content.Submission.unixtime > sa.func.extract('EPOCH', sa.func.now() - datetime.timedelta(days=max_days))) q = q.limit(128) submissions = [{ 'contype': 10, 'score': sc, 'submitid': s.submitid, 'title': s.title, 'rating': s.rating.code, 'subtype': s.subtype, 'unixtime': s.unixtime.timestamp, 'tags': list(s.tags), 'userid': s.userid, 'username': p.username, } for s, p, sc in q.all()] media.populate_with_submission_media(submissions) return submissions
def select(**kwargs): search = kwargs['search'] results, next_count, back_count = _find_without_media(**kwargs) if search.find == 'submit': media.populate_with_submission_media(results) elif search.find == 'char': for r in results: r['sub_media'] = character.fake_media_items( r['charid'], r['userid'], d.get_sysname(r['username']), r['settings']) elif search.find == 'journal': media.populate_with_user_media(results) return results, next_count, back_count
def select(**kwargs): search = kwargs['search'] results, next_count, back_count = _find_without_media(**kwargs) if search.find == 'submit': media.populate_with_submission_media(results) elif search.find == 'char': for r in results: r['sub_media'] = character.fake_media_items( r['charid'], r['userid'], d.get_sysname(r['username']), r['settings']) elif search.find == 'journal': media.populate_with_user_media(results) return results, next_count, back_count
def select_recently_popular(): """ Get a list of recent, popular submissions. This operation is non-trivial and should not be used frequently without caching. To calculate scores, this method performs the following evaluation: item_score = log(item_fave_count + 1) + log(item_view_counts) / 2 + submission_time / 180000 180000 is roughly two days. So intuitively an item two days old needs an order of magnitude more favorites/views compared to a fresh one. Also the favorites are quadratically more influential than views. The result is that this algorithm favors recent, heavily favorited items. :return: A list of submission dictionaries, in score-rank order. """ max_days = int(d.config_read_setting("popular_max_age_days", "21")) query = d.engine.execute(""" SELECT log(count(favorite.*) + 1) + log(submission.page_views + 1) / 2 + submission.unixtime / 180000.0 AS score, submission.submitid, submission.title, submission.rating, submission.subtype, submission.unixtime, submission_tags.tags, submission.userid, profile.username FROM submission INNER JOIN submission_tags ON submission.submitid = submission_tags.submitid INNER JOIN profile ON submission.userid = profile.userid LEFT JOIN favorite ON favorite.type = 's' AND submission.submitid = favorite.targetid WHERE submission.unixtime > EXTRACT(EPOCH FROM now() - %(max_days)s * INTERVAL '1 day')::INTEGER AND submission.settings !~ '[hf]' GROUP BY submission.submitid, submission_tags.submitid, profile.userid ORDER BY score DESC LIMIT 128 """, max_days=max_days) submissions = [dict(row, contype=10) for row in query] media.populate_with_submission_media(submissions) return submissions
def select_recently_popular(): """ Get a list of recent, popular submissions. This operation is non-trivial and should not be used frequently without caching. To calculate scores, this method performs the following evaluation: item_score = log(item_fave_count + 1) + log(item_view_counts) / 2 + submission_time / 180000 180000 is roughly two days. So intuitively an item two days old needs an order of magnitude more favorites/views compared to a fresh one. Also the favorites are quadratically more influential than views. The result is that this algorithm favors recent, heavily favorited items. :return: A list of submission dictionaries, in score-rank order. """ max_days = int(d.config_read_setting("popular_max_age_days", "21")) query = d.engine.execute(""" SELECT log(count(favorite.*) + 1) + log(submission.page_views + 1) / 2 + submission.unixtime / 180000.0 AS score, submission.submitid, submission.title, submission.rating, submission.subtype, submission.unixtime, submission_tags.tags, submission.userid, profile.username FROM submission INNER JOIN submission_tags ON submission.submitid = submission_tags.submitid INNER JOIN profile ON submission.userid = profile.userid LEFT JOIN favorite ON favorite.type = 's' AND submission.submitid = favorite.targetid WHERE submission.unixtime > EXTRACT(EPOCH FROM now() - %(max_days)s * INTERVAL '1 day')::INTEGER AND submission.settings !~ '[hf]' GROUP BY submission.submitid, submission_tags.submitid, profile.userid ORDER BY score DESC LIMIT 128 """, max_days=max_days) submissions = [dict(row, contype=10) for row in query] media.populate_with_submission_media(submissions) return submissions
def select_near(userid, rating, limit, otherid, folderid, submitid, config=None): if config is None: config = d.get_config(userid) statement = [""" SELECT su.submitid, su.title, su.rating, su.unixtime, su.userid, pr.username, su.settings, 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: # 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[7], } 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 submissionsbyuser(targetid): query = d.engine.execute(""" SELECT submitid, title, rating, unixtime, settings FROM submission WHERE userid = %(user)s """, user=targetid) ret = [{ "contype": 10, "userid": targetid, "submitid": i[0], "title": i[1], "rating": i[2], "unixtime": i[3], "settings": i[4], } for i in query] media.populate_with_submission_media(ret) return ret
def select_submit(userid, rating, limit, otherid=None, backid=None, nextid=None, config=None): statement = ["SELECT su.submitid, su.title, su.rating, fa.unixtime, su.userid, pr.username, su.subtype"] statement.extend(select_submit_query(userid, rating, otherid, backid, nextid, config)) statement.append(" ORDER BY fa.unixtime%s LIMIT %i" % ("" if backid else " DESC", limit)) 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))] media.populate_with_submission_media(query) return query[::-1] if backid else query
def select_recently_popular(): """ Get a list of recent, popular submissions. To calculate scores, this method performs the following evaluation: item_score = log(item_fave_count + 1) + log(item_view_counts) / 2 + submission_time / 180000 180000 is roughly two days. So intuitively an item two days old needs an order of magnitude more favorites/views compared to a fresh one. Also the favorites are quadratically more influential than views. The result is that this algorithm favors recent, heavily favorited items. :return: A list of submission dictionaries, in score-rank order. """ query = d.engine.execute(""" SELECT log(submission.favorites + 1) + log(submission.page_views + 1) / 2 + submission.unixtime / 180000.0 AS score, submission.submitid, submission.title, submission.rating, submission.subtype, submission.unixtime, submission_tags.tags, submission.userid, profile.username FROM submission INNER JOIN submission_tags ON submission.submitid = submission_tags.submitid INNER JOIN profile ON submission.userid = profile.userid WHERE submission.settings !~ '[hf]' AND submission.favorites IS NOT NULL ORDER BY score DESC LIMIT 128 """) submissions = [dict(row, contype=10) for row in query] media.populate_with_submission_media(submissions) return submissions
def submissionsbyuser(userid, form): if userid not in staff.MODS: raise WeasylError("Unexpected") query = d.execute(""" SELECT su.submitid, su.title, su.rating, su.unixtime, su.userid, pr.username, su.settings FROM submission su INNER JOIN profile pr USING (userid) WHERE su.userid = (SELECT userid FROM login WHERE login_name = '%s') ORDER BY su.submitid DESC """, [d.get_sysname(form.name)]) ret = [{ "contype": 10, "submitid": i[0], "title": i[1], "rating": i[2], "unixtime": i[3], "userid": i[4], "username": i[5], "settings": i[6], } for i in query] media.populate_with_submission_media(ret) return ret
def select(userid, rating, limit, q, find, within, rated, cat, subcat, backid, nextid): search = Query.parse(q) search.ratings.update([_rating_codes[r] for r in rated]) if not search._find: search._find = find if search.find == "user": terms = q.lower().split() statement = """ SELECT userid, full_name, unixtime, username FROM profile WHERE LOWER(username) SIMILAR TO ('%%(' || %(terms)s || ')%%') ESCAPE '' OR LOWER(full_name) SIMILAR TO ('%%(' || %(terms)s || ')%%') ESCAPE '' ORDER BY username LIMIT 100 """ query = d.engine.execute(statement, terms="|".join(terms)) ret = [{ "contype": 50, "userid": i.userid, "title": i.full_name, "rating": "", "unixtime": i.unixtime, "username": i.username, } for i in query] media.populate_with_user_media(ret) return ret, 0, 0 type_code, type_letter, table, select, subtype = _table_information[search.find] search_dict = search.to_dict() if not any(search_dict.values()): raise web.seeother("/search?type=" + search.find) # Begin statement statement_from = ["FROM {table} content INNER JOIN profile ON content.userid = profile.userid"] statement_where = ["WHERE content.rating <= %(rating)s AND content.settings !~ '[fhm]'"] statement_group = [] if search.required_includes: statement_from.append("INNER JOIN searchmap{find} ON targetid = content.{select}") statement_from.append("INNER JOIN searchtag ON searchmap{find}.tagid = searchtag.tagid") statement_where.append("AND searchtag.title = ANY (%(required_includes)s)") statement_group.append( "GROUP BY content.{select}, profile.username HAVING COUNT(searchtag.tagid) = %(required_include_count)s") # Submission category or subcategory if search.find == "submit": if subcat: statement_where.append("AND content.subtype = %(subcategory)s") elif cat: statement_where.append("AND content.subtype >= %(category)s AND content.subtype < %(category)s + 1000") if userid: if within == "notify": # Search within notifications statement_from.append("INNER JOIN welcome ON welcome.targetid = content.{select}") statement_where.append("AND welcome.userid = %(userid)s") statement_where.append({ "submit": "AND welcome.type IN (2010, 2030, 2040)", "char": "AND welcome.type = 2050", "journal": "AND welcome.type IN (1010, 1020)", }[search.find]) elif within == "fave": # Search within favorites statement_from.append("INNER JOIN favorite ON favorite.targetid = content.{select}") statement_where.append("AND favorite.userid = %(userid)s AND favorite.type = %(type)s") elif within == "friend": # Search within friends content statement_from.append( "INNER JOIN frienduser ON (frienduser.userid, frienduser.otherid) = (%(userid)s, content.userid)" " OR (frienduser.userid, frienduser.otherid) = (content.userid, %(userid)s)") elif within == "follow": # Search within following content statement_from.append( "INNER JOIN watchuser ON (watchuser.userid, watchuser.otherid) = (%(userid)s, content.userid)") # Search within rating if userid and search.ratings: statement_where.append("AND content.rating = ANY (%(ratings)s)") # Blocked tags and ignored users if userid: statement_where.append(""" AND NOT EXISTS ( SELECT 0 FROM ignoreuser WHERE userid = %(userid)s AND otherid = content.userid) AND NOT EXISTS ( SELECT 0 FROM searchmap{find} WHERE targetid = content.{select} AND tagid IN (SELECT tagid FROM blocktag WHERE userid = %(userid)s AND rating <= content.rating)) """) if search.possible_includes: statement_where.append(""" AND EXISTS ( SELECT 0 FROM searchmap{find} WHERE targetid = content.{select} AND tagid IN (SELECT tagid FROM searchtag WHERE title = ANY (%(possible_includes)s)) ) """) if search.required_excludes: statement_where.append(""" AND NOT EXISTS ( SELECT 0 FROM searchmap{find} WHERE targetid = content.{select} AND tagid IN ( SELECT tagid FROM searchtag WHERE title = ANY (%(required_excludes)s) ) ) """) if search.required_user_includes: statement_from.append("INNER JOIN login login_include ON content.userid = login_include.userid") statement_where.append("AND login_include.login_name = ANY (%(required_user_includes)s)") if search.required_user_excludes: statement_from.append("INNER JOIN login login_exclude ON content.userid = login_exclude.userid") statement_where.append("AND login_exclude.login_name != ALL (%(required_user_excludes)s)") def make_statement(statement_select, statement_additional_where, statement_order): return " ".join([ statement_select, " ".join(statement_from), " ".join(statement_where), statement_additional_where, " ".join(statement_group), statement_order, ]).format( table=table, find=search.find, select=select, subtype=subtype, title_field="char_name" if search.find == "char" else "title" ) pagination_filter = ( "AND content.{select} > %(backid)s" if backid else "AND content.{select} < %(nextid)s" if nextid else "") statement = make_statement( """ SELECT content.{select}, content.{title_field} AS title, content.rating, content.unixtime, content.userid, content.settings, profile.username, {subtype} as subtype """, pagination_filter, "ORDER BY content.{{select}} {order} LIMIT %(limit)s".format(order="" if backid else "DESC")) params = dict( search_dict, type=type_letter, userid=userid, rating=rating, ratings=list(search.ratings), category=cat, subcategory=subcat, limit=limit, backid=backid, nextid=nextid, required_include_count=len(search.required_includes)) query = d.engine.execute(statement, **params) ret = [{ "contype": type_code, select: i[select], "title": i.title, "subtype": i.subtype, "rating": i.rating, "unixtime": i.unixtime, "userid": i.userid, "username": i.username, "settings": i.settings, } for i in query] if search.find == "submit": media.populate_with_submission_media(ret) elif search.find == "char": for r in ret: r["sub_media"] = character.fake_media_items( r["charid"], r["userid"], d.get_sysname(r["username"]), r["settings"]) elif search.find == "journal": media.populate_with_user_media(ret) if backid: back_count = d.engine.execute( make_statement("SELECT COUNT(*) FROM (SELECT 1", pagination_filter, ") _"), **params).scalar() - len(ret) elif nextid: back_count = (d.engine.execute( make_statement("SELECT COUNT(*) FROM (SELECT 1", "AND content.{select} >= %(nextid)s", ") _"), **params).scalar()) else: back_count = 0 if backid: next_count = (d.engine.execute( make_statement("SELECT COUNT(*) FROM (SELECT 1", "AND content.{select} <= %(backid)s", ") _"), **params).scalar()) return list(reversed(ret)), next_count, back_count else: next_count = d.engine.execute( make_statement("SELECT COUNT(*) FROM (SELECT 1", pagination_filter, ") _"), **params).scalar() - len(ret) return ret, next_count, back_count
def select_submissions(userid, limit, backtime=None, nexttime=None): if backtime: time_filter = "AND unixtime > %(backtime)s" elif nexttime: time_filter = "AND unixtime < %(nexttime)s" else: time_filter = "" statement = """ SELECT * FROM ( SELECT 20 AS contype, ch.charid AS id, ch.char_name AS title, ch.rating, ch.unixtime, ch.userid, pr.username, ch.settings, we.welcomeid, 0 AS subtype FROM welcome we INNER JOIN character ch ON we.targetid = ch.charid INNER JOIN profile pr ON ch.userid = pr.userid WHERE we.type = 2050 AND we.userid = %(userid)s UNION SELECT 40 AS contype, su.submitid AS id, su.title, su.rating, su.unixtime, we.otherid AS userid, pr.username, su.settings, we.welcomeid, su.subtype FROM welcome we INNER JOIN submission su ON we.targetid = su.submitid INNER JOIN profile pr ON we.otherid = pr.userid WHERE we.type = 2030 AND we.userid = %(userid)s UNION SELECT 10 AS contype, su.submitid AS id, su.title, su.rating, su.unixtime, su.userid, pr.username, su.settings, we.welcomeid, su.subtype FROM welcome we INNER JOIN submission su ON we.targetid = su.submitid INNER JOIN profile pr ON su.userid = pr.userid WHERE we.type = 2010 AND we.userid = %(userid)s ) results WHERE rating <= %(rating)s {time_filter} ORDER BY unixtime DESC LIMIT %(limit)s """.format(time_filter=time_filter) query = d.engine.execute( statement, userid=userid, rating=d.get_rating(userid), nexttime=nexttime, backtime=backtime, limit=limit, ) results = [{ "contype": i.contype, "submitid" if i.contype != _CONTYPE_CHAR else "charid": i.id, "welcomeid": i.welcomeid, "title": i.title, "rating": i.rating, "unixtime": i.unixtime, "userid": i.userid, "username": i.username, "subtype": i.subtype, "sub_media": _fake_media_items(i), } for i in query] media.populate_with_submission_media( [i for i in results if i["contype"] != _CONTYPE_CHAR]) return results
def select_submissions(userid, limit, include_tags, backtime=None, nexttime=None): if backtime: time_filter = "AND we.unixtime > %(backtime)s" elif nexttime: time_filter = "AND we.unixtime < %(nexttime)s" else: time_filter = "" if include_tags: char_tags_select = ", COALESCE(array_agg(tagid) FILTER (WHERE tagid IS NOT NULL), '{}') AS tags" char_tags_join = "LEFT JOIN searchmapchar AS smc ON ch.charid = smc.targetid" char_tags_groupby = "GROUP BY ch.charid, pr.username, we.welcomeid" submission_tags_select = ", tags" submission_tags_join = "INNER JOIN submission_tags USING (submitid)" else: char_tags_select = char_tags_join = char_tags_groupby = submission_tags_select = submission_tags_join = "" statement = """ SELECT * FROM ( SELECT 20 AS contype, ch.charid AS id, ch.char_name AS title, ch.rating, we.unixtime, ch.userid, pr.username, ch.settings, we.welcomeid, 0 AS subtype {char_tags_select} FROM welcome we INNER JOIN character ch ON we.targetid = ch.charid INNER JOIN profile pr ON ch.userid = pr.userid {char_tags_join} WHERE we.type = 2050 AND we.userid = %(userid)s AND ch.rating <= %(rating)s {time_filter} {char_tags_groupby} ORDER BY welcomeid DESC LIMIT %(limit)s ) t UNION ALL SELECT * FROM ( SELECT 40 AS contype, su.submitid AS id, su.title, su.rating, we.unixtime, we.otherid AS userid, pr.username, su.settings, we.welcomeid, su.subtype {submission_tags_select} FROM welcome we INNER JOIN submission su ON we.targetid = su.submitid INNER JOIN profile pr ON we.otherid = pr.userid {submission_tags_join} WHERE we.type = 2030 AND we.userid = %(userid)s AND su.rating <= %(rating)s {time_filter} ORDER BY welcomeid DESC LIMIT %(limit)s ) t UNION ALL SELECT * FROM ( SELECT 10 AS contype, su.submitid AS id, su.title, su.rating, we.unixtime, su.userid, pr.username, su.settings, we.welcomeid, su.subtype {submission_tags_select} FROM welcome we INNER JOIN submission su ON we.targetid = su.submitid INNER JOIN profile pr ON su.userid = pr.userid {submission_tags_join} WHERE we.type = 2010 AND we.userid = %(userid)s AND su.rating <= %(rating)s {time_filter} ORDER BY welcomeid DESC LIMIT %(limit)s ) t ORDER BY welcomeid DESC LIMIT %(limit)s """.format( time_filter=time_filter, char_tags_select=char_tags_select, char_tags_join=char_tags_join, char_tags_groupby=char_tags_groupby, submission_tags_select=submission_tags_select, submission_tags_join=submission_tags_join, ) query = d.engine.execute( statement, userid=userid, rating=d.get_rating(userid), nexttime=nexttime, backtime=backtime, limit=limit, ).fetchall() if include_tags: all_tags = list(frozenset(chain.from_iterable(i.tags for i in query))) tag_map = {t.tagid: t.title for t in d.engine.execute("SELECT tagid, title FROM searchtag WHERE tagid = ANY (%(tags)s)", tags=all_tags)} results = [{ "contype": i.contype, "submitid" if i.contype != _CONTYPE_CHAR else "charid": i.id, "welcomeid": i.welcomeid, "title": i.title, "rating": i.rating, "unixtime": i.unixtime, "userid": i.userid, "username": i.username, "subtype": i.subtype, "tags": [tag_map[tag] for tag in i.tags], "sub_media": _fake_media_items(i), } for i in query] else: results = [{ "contype": i.contype, "submitid" if i.contype != _CONTYPE_CHAR else "charid": i.id, "welcomeid": i.welcomeid, "title": i.title, "rating": i.rating, "unixtime": i.unixtime, "userid": i.userid, "username": i.username, "subtype": i.subtype, "sub_media": _fake_media_items(i), } for i in query] media.populate_with_submission_media( [i for i in results if i["contype"] != _CONTYPE_CHAR]) return results
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(userid, rating, limit, search, within, cat, subcat, backid, nextid): type_code, type_letter, table, select, subtype = _table_information[ search.find] # Begin statement statement_from = [ "FROM {table} content INNER JOIN profile ON content.userid = profile.userid" ] statement_where = [ "WHERE content.rating <= %(rating)s AND content.settings !~ '[fhm]'" ] statement_group = [] if search.find == "submit": statement_from.append( "INNER JOIN submission_tags ON content.submitid = submission_tags.submitid" ) if search.required_includes: if search.find == "submit": statement_from.append( "AND submission_tags.tags @> %(required_includes)s") else: statement_from.append( "INNER JOIN searchmap{find} ON targetid = content.{select}") statement_where.append( "AND searchmap{find}.tagid = ANY (%(required_includes)s)") statement_group.append( "GROUP BY content.{select}, profile.username HAVING COUNT(searchmap{find}.tagid) = %(required_include_count)s" ) # Submission category or subcategory if search.find == "submit": if subcat: statement_where.append("AND content.subtype = %(subcategory)s") elif cat: statement_where.append( "AND content.subtype >= %(category)s AND content.subtype < %(category)s + 1000" ) if userid: if within == "notify": # Search within notifications statement_from.append( "INNER JOIN welcome ON welcome.targetid = content.{select}") statement_where.append("AND welcome.userid = %(userid)s") statement_where.append({ "submit": "AND welcome.type IN (2010, 2030, 2040)", "char": "AND welcome.type = 2050", "journal": "AND welcome.type IN (1010, 1020)", }[search.find]) elif within == "fave": # Search within favorites statement_from.append( "INNER JOIN favorite ON favorite.targetid = content.{select}") statement_where.append( "AND favorite.userid = %(userid)s AND favorite.type = %(type)s" ) elif within == "friend": # Search within friends content statement_from.append( "INNER JOIN frienduser ON (frienduser.userid, frienduser.otherid) = (%(userid)s, content.userid)" " OR (frienduser.userid, frienduser.otherid) = (content.userid, %(userid)s)" ) elif within == "follow": # Search within following content statement_from.append( "INNER JOIN watchuser ON (watchuser.userid, watchuser.otherid) = (%(userid)s, content.userid)" ) # Search within rating if userid and search.ratings: statement_where.append("AND content.rating = ANY (%(ratings)s)") # Blocked tags and ignored users if userid: statement_where.append(""" AND NOT EXISTS ( SELECT 0 FROM ignoreuser WHERE userid = %(userid)s AND otherid = content.userid) AND NOT EXISTS ( SELECT 0 FROM searchmap{find} WHERE targetid = content.{select} AND tagid IN (SELECT tagid FROM blocktag WHERE userid = %(userid)s AND rating <= content.rating)) """) if search.possible_includes: if search.find == "submit": statement_where.append( "AND submission_tags.tags && %(possible_includes)s") else: statement_where.append(""" AND EXISTS ( SELECT 0 FROM searchmap{find} WHERE targetid = content.{select} AND tagid = ANY (%(possible_includes)s) ) """) if search.required_excludes: if search.find == "submit": statement_where.append( "AND NOT submission_tags.tags && %(required_excludes)s") else: statement_where.append(""" AND NOT EXISTS ( SELECT 0 FROM searchmap{find} WHERE targetid = content.{select} AND tagid = ANY (%(required_excludes)s) ) """) if search.required_user_includes: statement_from.append( "INNER JOIN login login_include ON content.userid = login_include.userid" ) statement_where.append( "AND login_include.login_name = ANY (%(required_user_includes)s)") if search.required_user_excludes: statement_from.append( "INNER JOIN login login_exclude ON content.userid = login_exclude.userid" ) statement_where.append( "AND login_exclude.login_name != ALL (%(required_user_excludes)s)") def make_statement(statement_select, statement_additional_where, statement_order): return " ".join([ statement_select, " ".join(statement_from), " ".join(statement_where), statement_additional_where, " ".join(statement_group), statement_order, ]).format( table=table, find=search.find, select=select, subtype=subtype, title_field="char_name" if search.find == "char" else "title") pagination_filter = ("AND content.{select} > %(backid)s" if backid else "AND content.{select} < %(nextid)s" if nextid else "") statement = make_statement( """ SELECT content.{select}, content.{title_field} AS title, content.rating, content.unixtime, content.userid, content.settings, profile.username, {subtype} as subtype """, pagination_filter, "ORDER BY content.{{select}} {order} LIMIT %(limit)s".format( order="" if backid else "DESC")) all_names = (search.possible_includes | search.required_includes | search.required_excludes) tag_ids = searchtag.get_ids(all_names) def get_ids(names): return [tag_ids.get(name, -1) for name in names] params = { "possible_includes": get_ids(search.possible_includes), "required_includes": get_ids(search.required_includes), "required_excludes": get_ids(search.required_excludes), "required_user_includes": list(search.required_user_includes), "required_user_excludes": list(search.required_user_excludes), "type": type_letter, "userid": userid, "rating": rating, "ratings": list(search.ratings), "category": cat, "subcategory": subcat, "limit": limit, "backid": backid, "nextid": nextid, "required_include_count": len(search.required_includes), } query = d.engine.execute(statement, **params) ret = [{ "contype": type_code, select: i[select], "title": i.title, "subtype": i.subtype, "rating": i.rating, "unixtime": i.unixtime, "userid": i.userid, "username": i.username, "settings": i.settings, } for i in query] if search.find == "submit": media.populate_with_submission_media(ret) elif search.find == "char": for r in ret: r["sub_media"] = character.fake_media_items( r["charid"], r["userid"], d.get_sysname(r["username"]), r["settings"]) elif search.find == "journal": media.populate_with_user_media(ret) if backid: back_count = d.engine.execute( make_statement("SELECT COUNT(*) FROM (SELECT 1", pagination_filter, ") _"), **params).scalar() - len(ret) elif nextid: back_count = (d.engine.execute( make_statement("SELECT COUNT(*) FROM (SELECT 1", "AND content.{select} >= %(nextid)s", ") _"), **params).scalar()) else: back_count = 0 if backid: next_count = (d.engine.execute( make_statement("SELECT COUNT(*) FROM (SELECT 1", "AND content.{select} <= %(backid)s", ") _"), **params).scalar()) return list(reversed(ret)), next_count, back_count else: next_count = d.engine.execute( make_statement("SELECT COUNT(*) FROM (SELECT 1", pagination_filter, ") _"), **params).scalar() - len(ret) return ret, next_count, back_count
def select_submissions(userid, limit, include_tags, backtime=None, nexttime=None): if backtime: time_filter = "AND we.unixtime > %(backtime)s" elif nexttime: time_filter = "AND we.unixtime < %(nexttime)s" else: time_filter = "" if include_tags: char_tags_select = ", COALESCE(array_agg(tagid) FILTER (WHERE tagid IS NOT NULL), '{}') AS tags" char_tags_join = "LEFT JOIN searchmapchar AS smc ON ch.charid = smc.targetid" char_tags_groupby = "GROUP BY ch.charid, pr.username, we.welcomeid" submission_tags_select = ", tags" submission_tags_join = "INNER JOIN submission_tags USING (submitid)" else: char_tags_select = char_tags_join = char_tags_groupby = submission_tags_select = submission_tags_join = "" statement = """ SELECT * FROM ( SELECT 20 AS contype, ch.charid AS id, ch.char_name AS title, ch.rating, we.unixtime, ch.userid, pr.username, ch.settings, we.welcomeid, 0 AS subtype {char_tags_select} FROM welcome we INNER JOIN character ch ON we.targetid = ch.charid INNER JOIN profile pr ON ch.userid = pr.userid {char_tags_join} WHERE we.type = 2050 AND we.userid = %(userid)s AND ch.rating <= %(rating)s {time_filter} {char_tags_groupby} ORDER BY welcomeid DESC LIMIT %(limit)s ) t UNION ALL SELECT * FROM ( SELECT 40 AS contype, su.submitid AS id, su.title, su.rating, we.unixtime, we.otherid AS userid, pr.username, su.settings, we.welcomeid, su.subtype {submission_tags_select} FROM welcome we INNER JOIN submission su ON we.targetid = su.submitid INNER JOIN profile pr ON we.otherid = pr.userid {submission_tags_join} WHERE we.type = 2030 AND we.userid = %(userid)s AND su.rating <= %(rating)s {time_filter} ORDER BY welcomeid DESC LIMIT %(limit)s ) t UNION ALL SELECT * FROM ( SELECT 10 AS contype, su.submitid AS id, su.title, su.rating, we.unixtime, su.userid, pr.username, su.settings, we.welcomeid, su.subtype {submission_tags_select} FROM welcome we INNER JOIN submission su ON we.targetid = su.submitid INNER JOIN profile pr ON su.userid = pr.userid {submission_tags_join} WHERE we.type = 2010 AND we.userid = %(userid)s AND su.rating <= %(rating)s {time_filter} ORDER BY welcomeid DESC LIMIT %(limit)s ) t ORDER BY welcomeid DESC LIMIT %(limit)s """.format( time_filter=time_filter, char_tags_select=char_tags_select, char_tags_join=char_tags_join, char_tags_groupby=char_tags_groupby, submission_tags_select=submission_tags_select, submission_tags_join=submission_tags_join, ) query = d.engine.execute( statement, userid=userid, rating=d.get_rating(userid), nexttime=nexttime, backtime=backtime, limit=limit, ).fetchall() if include_tags: all_tags = list(frozenset(chain.from_iterable(i.tags for i in query))) tag_map = { t.tagid: t.title for t in d.engine.execute( "SELECT tagid, title FROM searchtag WHERE tagid = ANY (%(tags)s)", tags=all_tags) } results = [{ "contype": i.contype, "submitid" if i.contype != _CONTYPE_CHAR else "charid": i.id, "welcomeid": i.welcomeid, "title": i.title, "rating": i.rating, "unixtime": i.unixtime, "userid": i.userid, "username": i.username, "subtype": i.subtype, "tags": [tag_map[tag] for tag in i.tags], "sub_media": _fake_media_items(i), } for i in query] else: results = [{ "contype": i.contype, "submitid" if i.contype != _CONTYPE_CHAR else "charid": i.id, "welcomeid": i.welcomeid, "title": i.title, "rating": i.rating, "unixtime": i.unixtime, "userid": i.userid, "username": i.username, "subtype": i.subtype, "sub_media": _fake_media_items(i), } for i in query] media.populate_with_submission_media( [i for i in results if i["contype"] != _CONTYPE_CHAR]) return results
def select_list(userid, rating, limit, 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): """ Selects a list from the submissions table. Args: userid: The current user rating: The maximum rating level to show limit: The number of submissions to get otherid: The user whose submissions to get folderid: Select submissions from this folder backid: Select the IDs that are less than this value nextid: Select the IDs that are greater than this value subcat: Select submissions whose subcategory is within this range (this value + 1000) exclude: Exclude this specific submission ID options: List that can contain the following values: "critique": Submissions flagged for critique; additionally selects submissions newer than 3 days old "randomize": Randomize the ordering of the results "encore": Order results by sort time rather than submission id "offset": Select submissions older than half an hour config: Database config override profile_page_filter: Do not select from folders that should not appear on the profile page. index_page_filter: Do not select from folders that should not appear on the front page. featured_filter: Select from folders marked as featured submissions. Returns: An array with the following keys: "contype", "submitid", "title", "rating", "unixtime", "userid", "username", "subtype", "sub_media" """ statement = [ "SELECT su.submitid, su.title, su.rating, su.unixtime, " "su.userid, pr.username, su.settings, su.subtype "] statement.extend(select_query( userid, rating, otherid, folderid, backid, nextid, subcat, exclude, options, config, profile_page_filter, index_page_filter, featured_filter)) statement.append( " ORDER BY %s%s LIMIT %i" % ("RANDOM()" if "critique" in options or "randomize" in options else "su.sorttime" if "encore" in options else "su.submitid", "" if backid else " DESC", limit)) query = [{ "contype": 10, "submitid": i[0], "title": i[1], "rating": i[2], "unixtime": i[3], "userid": i[4], "username": i[5], "subtype": i[7], } for i in d.execute("".join(statement))] media.populate_with_submission_media(query) return query[::-1] if backid else query
def select_submissions(userid, limit, backtime=None, nexttime=None): if backtime: time_filter = "AND unixtime > %(backtime)s" elif nexttime: time_filter = "AND unixtime < %(nexttime)s" else: time_filter = "" statement = """ SELECT * FROM ( SELECT 20 AS contype, ch.charid AS id, ch.char_name AS title, ch.rating, ch.unixtime, ch.userid, pr.username, ch.settings, we.welcomeid, 0 AS subtype, array_agg(tags.title) AS tags FROM welcome we INNER JOIN character ch ON we.targetid = ch.charid INNER JOIN profile pr ON ch.userid = pr.userid LEFT JOIN searchmapchar AS smc ON ch.charid = smc.targetid LEFT JOIN searchtag AS tags USING (tagid) WHERE we.type = 2050 AND we.userid = %(userid)s GROUP BY ch.charid, pr.username, we.welcomeid UNION SELECT 40 AS contype, su.submitid AS id, su.title, su.rating, su.unixtime, we.otherid AS userid, pr.username, su.settings, we.welcomeid, su.subtype, array_agg(tags.title) AS tags FROM welcome we INNER JOIN submission su ON we.targetid = su.submitid INNER JOIN profile pr ON we.otherid = pr.userid LEFT JOIN searchmapsubmit AS sms ON su.submitid = sms.targetid LEFT JOIN searchtag AS tags USING (tagid) WHERE we.type = 2030 AND we.userid = %(userid)s GROUP BY su.submitid, pr.username, we.welcomeid UNION SELECT 10 AS contype, su.submitid AS id, su.title, su.rating, su.unixtime, su.userid, pr.username, su.settings, we.welcomeid, su.subtype, array_agg(tags.title) AS tags FROM welcome we INNER JOIN submission su ON we.targetid = su.submitid INNER JOIN profile pr ON su.userid = pr.userid LEFT JOIN searchmapsubmit AS sms ON su.submitid = sms.targetid LEFT JOIN searchtag AS tags USING (tagid) WHERE we.type = 2010 AND we.userid = %(userid)s GROUP BY su.submitid, pr.username, we.welcomeid ) results WHERE rating <= %(rating)s {time_filter} ORDER BY unixtime DESC LIMIT %(limit)s """.format(time_filter=time_filter) query = d.engine.execute( statement, userid=userid, rating=d.get_rating(userid), nexttime=nexttime, backtime=backtime, limit=limit, ) results = [{ "contype": i.contype, "submitid" if i.contype != _CONTYPE_CHAR else "charid": i.id, "welcomeid": i.welcomeid, "title": i.title, "rating": i.rating, "unixtime": i.unixtime, "userid": i.userid, "username": i.username, "subtype": i.subtype, "tags": i.tags, "sub_media": _fake_media_items(i), } for i in query] media.populate_with_submission_media( [i for i in results if i["contype"] != _CONTYPE_CHAR]) return results
def select(userid, rating, limit, q, find, within, rated, cat, subcat, backid, nextid): search = Query.parse(q) search.ratings.update([_rating_codes[r] for r in rated]) if not search._find: search._find = find if search.find == "user": terms = q.lower().split() statement = """ SELECT userid, full_name, unixtime, username FROM profile WHERE LOWER(username) SIMILAR TO ('%%(' || %(terms)s || ')%%') ESCAPE '' OR LOWER(full_name) SIMILAR TO ('%%(' || %(terms)s || ')%%') ESCAPE '' ORDER BY username LIMIT 100 """ query = d.engine.execute(statement, terms="|".join(terms)) ret = [{ "contype": 50, "userid": i.userid, "title": i.full_name, "rating": "", "unixtime": i.unixtime, "username": i.username, } for i in query] media.populate_with_user_media(ret) return ret, 0, 0 type_code, type_letter, table, select, subtype = _table_information[ search.find] search_dict = search.to_dict() if not any(search_dict.values()): raise web.seeother("/search?type=" + search.find) # Begin statement statement_from = [ "FROM {table} content INNER JOIN profile ON content.userid = profile.userid" ] statement_where = [ "WHERE content.rating <= %(rating)s AND content.settings !~ '[fhm]'" ] statement_group = [] if search.required_includes: statement_from.append( "INNER JOIN searchmap{find} ON targetid = content.{select}") statement_from.append( "INNER JOIN searchtag ON searchmap{find}.tagid = searchtag.tagid") statement_where.append( "AND searchtag.title = ANY (%(required_includes)s)") statement_group.append( "GROUP BY content.{select}, profile.username HAVING COUNT(searchtag.tagid) = %(required_include_count)s" ) # Submission category or subcategory if search.find == "submit": if subcat: statement_where.append("AND content.subtype = %(subcategory)s") elif cat: statement_where.append( "AND content.subtype >= %(category)s AND content.subtype < %(category)s + 1000" ) if userid: if within == "notify": # Search within notifications statement_from.append( "INNER JOIN welcome ON welcome.targetid = content.{select}") statement_where.append("AND welcome.userid = %(userid)s") statement_where.append({ "submit": "AND welcome.type IN (2010, 2030, 2040)", "char": "AND welcome.type = 2050", "journal": "AND welcome.type IN (1010, 1020)", }[search.find]) elif within == "fave": # Search within favorites statement_from.append( "INNER JOIN favorite ON favorite.targetid = content.{select}") statement_where.append( "AND favorite.userid = %(userid)s AND favorite.type = %(type)s" ) elif within == "friend": # Search within friends content statement_from.append( "INNER JOIN frienduser ON (frienduser.userid, frienduser.otherid) = (%(userid)s, content.userid)" " OR (frienduser.userid, frienduser.otherid) = (content.userid, %(userid)s)" ) elif within == "follow": # Search within following content statement_from.append( "INNER JOIN watchuser ON (watchuser.userid, watchuser.otherid) = (%(userid)s, content.userid)" ) # Search within rating if userid and search.ratings: statement_where.append("AND content.rating = ANY (%(ratings)s)") # Blocked tags and ignored users if userid: statement_where.append(""" AND NOT EXISTS ( SELECT 0 FROM ignoreuser WHERE userid = %(userid)s AND otherid = content.userid) AND NOT EXISTS ( SELECT 0 FROM searchmap{find} WHERE targetid = content.{select} AND tagid IN (SELECT tagid FROM blocktag WHERE userid = %(userid)s AND rating <= content.rating)) """) if search.possible_includes: statement_where.append(""" AND EXISTS ( SELECT 0 FROM searchmap{find} WHERE targetid = content.{select} AND tagid IN (SELECT tagid FROM searchtag WHERE title = ANY (%(possible_includes)s)) ) """) if search.required_excludes: statement_where.append(""" AND NOT EXISTS ( SELECT 0 FROM searchmap{find} WHERE targetid = content.{select} AND tagid IN ( SELECT tagid FROM searchtag WHERE title = ANY (%(required_excludes)s) ) ) """) if search.required_user_includes: statement_from.append( "INNER JOIN login login_include ON content.userid = login_include.userid" ) statement_where.append( "AND login_include.login_name = ANY (%(required_user_includes)s)") if search.required_user_excludes: statement_from.append( "INNER JOIN login login_exclude ON content.userid = login_exclude.userid" ) statement_where.append( "AND login_exclude.login_name != ALL (%(required_user_excludes)s)") def make_statement(statement_select, statement_additional_where, statement_order): return " ".join([ statement_select, " ".join(statement_from), " ".join(statement_where), statement_additional_where, " ".join(statement_group), statement_order, ]).format( table=table, find=search.find, select=select, subtype=subtype, title_field="char_name" if search.find == "char" else "title") pagination_filter = ("AND content.{select} > %(backid)s" if backid else "AND content.{select} < %(nextid)s" if nextid else "") statement = make_statement( """ SELECT content.{select}, content.{title_field} AS title, content.rating, content.unixtime, content.userid, content.settings, profile.username, {subtype} as subtype """, pagination_filter, "ORDER BY content.{{select}} {order} LIMIT %(limit)s".format( order="" if backid else "DESC")) params = dict(search_dict, type=type_letter, userid=userid, rating=rating, ratings=list(search.ratings), category=cat, subcategory=subcat, limit=limit, backid=backid, nextid=nextid, required_include_count=len(search.required_includes)) query = d.engine.execute(statement, **params) ret = [{ "contype": type_code, select: i[select], "title": i.title, "subtype": i.subtype, "rating": i.rating, "unixtime": i.unixtime, "userid": i.userid, "username": i.username, "settings": i.settings, } for i in query] if search.find == "submit": media.populate_with_submission_media(ret) elif search.find == "char": for r in ret: r["sub_media"] = character.fake_media_items( r["charid"], r["userid"], d.get_sysname(r["username"]), r["settings"]) elif search.find == "journal": media.populate_with_user_media(ret) if backid: back_count = d.engine.execute( make_statement("SELECT COUNT(*) FROM (SELECT 1", pagination_filter, ") _"), **params).scalar() - len(ret) elif nextid: back_count = (d.engine.execute( make_statement("SELECT COUNT(*) FROM (SELECT 1", "AND content.{select} >= %(nextid)s", ") _"), **params).scalar()) else: back_count = 0 if backid: next_count = (d.engine.execute( make_statement("SELECT COUNT(*) FROM (SELECT 1", "AND content.{select} <= %(backid)s", ") _"), **params).scalar()) return list(reversed(ret)), next_count, back_count else: next_count = d.engine.execute( make_statement("SELECT COUNT(*) FROM (SELECT 1", pagination_filter, ") _"), **params).scalar() - len(ret) return ret, next_count, back_count