def select_list(userid):
query = d.execute("SELECT classid, title, amount_min, amount_max, settings, priceid FROM commishprice"
" WHERE userid = %i ORDER BY classid, title", [userid])
content = d.execute("SELECT content FROM commishdesc WHERE userid = %i", [userid], ["element"])
return {
"class": [{
"classid": i[0],
"title": i[1],
} for i in d.execute("SELECT classid, title FROM commishclass WHERE userid = %i ORDER BY title", [userid])],
"price": [{
"classid": i[0],
"title": i[1],
"amount_min": i[2],
"amount_max": i[3],
"settings": i[4],
"priceid": i[5],
} for i in query if "a" not in i[4]] + [{
"classid": i[0],
"title": i[1],
"amount_min": i[2],
"amount_max": i[3],
"settings": i[4],
"priceid": i[5],
} for i in query if "a" in i[4]],
"content": content if content else "",
}
def resolve(userid, otherid, othername, myself=True):
"""
Attempts to determine the userid of a specified user; resolves using otherid,
othername, and userid (if myself is True), in that order. If no userid can be
resolved, returns 0 instead.
"""
result = None
if otherid:
result = d.execute("SELECT userid FROM login WHERE userid = %i", [d.get_int(otherid)], ["element"])
if result:
return result
elif othername:
result = d.execute("SELECT userid FROM login WHERE login_name = '%s'", [d.get_sysname(othername)], ["element"])
if result:
return result
result = d.execute("SELECT userid FROM useralias WHERE alias_name = '%s'", [d.get_sysname(othername)], ["element"])
if result:
return result
elif userid and myself:
return userid
return 0
def remove_request(userid, otherid):
d.execute(
"DELETE FROM frienduser "
"WHERE userid IN (%i, %i) "
"AND otherid IN (%i, %i)",
[userid, otherid, userid, otherid])
welcome.frienduserrequest_remove(userid, otherid)
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 edit_email_password(userid, username, password, newemail, newemailcheck,
newpassword, newpasscheck):
import login
# Check that credentials are correct
logid, logerror = login.authenticate_bcrypt(username, password, session=False)
if userid != logid or logerror is not None:
raise WeasylError("loginInvalid")
if newemail:
if newemail != newemailcheck:
raise WeasylError("emailMismatch")
elif login.email_exists(newemail):
raise WeasylError("emailExists")
if newpassword:
if newpassword != newpasscheck:
raise WeasylError("passwordMismatch")
elif not login.password_secure(newpassword):
raise WeasylError("passwordInsecure")
if newemail:
d.execute("UPDATE login SET email = '%s' WHERE userid = %i", [newemail, userid])
if newpassword:
d.execute("UPDATE authbcrypt SET hashsum = '%s' WHERE userid = %i", [login.passhash(newpassword), userid])
def resolve(userid, otherid, othername, myself=True):
"""
Attempts to determine the userid of a specified user; resolves using otherid,
othername, and userid (if myself is True), in that order. If no userid can be
resolved, returns 0 instead.
"""
result = None
if otherid:
result = d.execute("SELECT userid FROM login WHERE userid = %i",
[d.get_int(otherid)], ["element"])
if result:
return result
elif othername:
result = d.execute("SELECT userid FROM login WHERE login_name = '%s'",
[d.get_sysname(othername)], ["element"])
if result:
return result
result = d.execute(
"SELECT userid FROM useralias WHERE alias_name = '%s'",
[d.get_sysname(othername)], ["element"])
if result:
return result
elif userid and myself:
return userid
return 0
def select_list(userid):
query = d.execute(
"SELECT classid, title, amount_min, amount_max, settings, priceid FROM commishprice"
" WHERE userid = %i ORDER BY classid, title", [userid])
content = d.execute("SELECT content FROM commishdesc WHERE userid = %i",
[userid], ["element"])
return {
"class": [{
"classid": i[0],
"title": i[1],
} for i in d.execute(
"SELECT classid, title FROM commishclass WHERE userid = %i ORDER BY title",
[userid])],
"price": [{
"classid": i[0],
"title": i[1],
"amount_min": i[2],
"amount_max": i[3],
"settings": i[4],
"priceid": i[5],
} for i in query if "a" not in i[4]] + [{
"classid": i[0],
"title": i[1],
"amount_min": i[2],
"amount_max": i[3],
"settings": i[4],
"priceid": i[5],
} for i in query if "a" in i[4]],
"content":
content if content else "",
}
def offer(userid, submitid, otherid):
query = d.execute("SELECT userid, rating, settings FROM submission WHERE submitid = %i",
[submitid], options="single")
if not query or "h" in query[2]:
raise WeasylError("Unexpected")
elif userid != query[0]:
raise WeasylError("Unexpected")
# Check collection acceptability
if otherid:
rating = d.get_rating(otherid)
if rating < query[1]:
raise WeasylError("collectionUnacceptable")
if "f" in query[2]:
raise WeasylError("collectionUnacceptable")
if ignoreuser.check(otherid, userid):
raise WeasylError("IgnoredYou")
if ignoreuser.check(userid, otherid):
raise WeasylError("YouIgnored")
if blocktag.check(otherid, submitid=submitid):
raise WeasylError("collectionUnacceptable")
try:
d.execute("INSERT INTO collection (userid, submitid, unixtime) VALUES (%i, %i, %i)",
[otherid, submitid, d.get_time()])
except PostgresError:
raise WeasylError("collectionExists")
welcome.collectoffer_insert(userid, otherid, submitid)
def 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 edit_email_password(userid, username, password, newemail, newemailcheck,
newpassword, newpasscheck):
import login
# Check that credentials are correct
logid, logerror = login.authenticate_bcrypt(username,
password,
session=False)
if userid != logid or logerror is not None:
raise WeasylError("loginInvalid")
if newemail:
if newemail != newemailcheck:
raise WeasylError("emailMismatch")
elif login.email_exists(newemail):
raise WeasylError("emailExists")
if newpassword:
if newpassword != newpasscheck:
raise WeasylError("passwordMismatch")
elif not login.password_secure(newpassword):
raise WeasylError("passwordInsecure")
if newemail:
d.execute("UPDATE login SET email = '%s' WHERE userid = %i",
[newemail, userid])
if newpassword:
d.execute("UPDATE authbcrypt SET hashsum = '%s' WHERE userid = %i",
[login.passhash(newpassword), userid])
def create_price(userid, price, currency="", settings=""):
if not price.title:
raise error.WeasylError("titleInvalid")
elif price.amount_min > _MAX_PRICE:
raise error.WeasylError("minamountInvalid")
elif price.amount_max > _MAX_PRICE:
raise error.WeasylError("maxamountInvalid")
elif price.amount_max and price.amount_max < price.amount_min:
raise error.WeasylError("maxamountInvalid")
elif not d.execute("SELECT EXISTS (SELECT 0 FROM commishclass WHERE (classid, userid) = (%i, %i))",
[price.classid, userid], ["bool"]):
raise error.WeasylError("classidInvalid")
elif not price.classid:
raise error.WeasylError("classidInvalid")
# Settings are at most one currency class, and optionally an 'a' to indicate an add-on price.
# TODO: replace these character codes with an enum.
settings = "%s%s" % ("".join(i for i in currency if i in "epycmu")[:1],
"a" if "a" in settings else "")
# TODO: should have an auto-increment ID
priceid = d.execute("SELECT MAX(priceid) + 1 FROM commishprice WHERE userid = %i", [userid], ["element"])
try:
d.execute(
"INSERT INTO commishprice VALUES (%i, %i, %i, '%s', %i, %i, '%s')",
[priceid if priceid else 1, price.classid, userid, price.title, price.amount_min, price.amount_max, settings])
except error.PostgresError:
return error.WeasylError("titleExists")
def create_price(userid, price, currency="", settings=""):
if not price.title:
raise error.WeasylError("titleInvalid")
elif price.amount_min > _MAX_PRICE:
raise error.WeasylError("minamountInvalid")
elif price.amount_max > _MAX_PRICE:
raise error.WeasylError("maxamountInvalid")
elif price.amount_max and price.amount_max < price.amount_min:
raise error.WeasylError("maxamountInvalid")
elif not d.execute(
"SELECT EXISTS (SELECT 0 FROM commishclass WHERE (classid, userid) = (%i, %i))",
[price.classid, userid], ["bool"]):
raise error.WeasylError("classidInvalid")
elif not price.classid:
raise error.WeasylError("classidInvalid")
# Settings are at most one currency class, and optionally an 'a' to indicate an add-on price.
# TODO: replace these character codes with an enum.
settings = "%s%s" % ("".join(i for i in currency if i in "epycmu")[:1],
"a" if "a" in settings else "")
# TODO: should have an auto-increment ID
priceid = d.execute(
"SELECT MAX(priceid) + 1 FROM commishprice WHERE userid = %i",
[userid], ["element"])
try:
d.execute(
"INSERT INTO commishprice VALUES (%i, %i, %i, '%s', %i, %i, '%s')",
[
priceid if priceid else 1, price.classid, userid, price.title,
price.amount_min, price.amount_max, settings
])
except error.PostgresError:
return error.WeasylError("titleExists")
def remove(userid, tagid=None, title=None):
if tagid:
d.execute("DELETE FROM blocktag WHERE (userid, tagid) = (%i, %i)", [userid, tagid])
elif title:
d.execute("DELETE FROM blocktag WHERE (userid, tagid) = (%i, (SELECT tagid FROM searchtag WHERE title = '%s'))",
[userid, d.get_search_tag(title)])
cached_select.invalidate(userid)
def set(userid, username):
if login.username_exists(username):
raise WeasylError("usernameExists")
elif not d.get_premium(None, userid):
raise WeasylError("InsufficientPermissions")
d.execute("DELETE FROM useralias WHERE userid = %i AND settings ~ 'p'", [userid])
d.execute("INSERT INTO useralias VALUES (%i, '%s', 'p')", [userid, username])
def accept(userid, otherid):
if check(userid, otherid):
raise WeasylError("Unexpected")
d.execute("UPDATE frienduser SET settings = REPLACE(settings, 'p', '')"
" WHERE (userid, otherid) = (%i, %i)", [otherid, userid])
welcome.frienduseraccept_insert(userid, otherid)
welcome.frienduserrequest_remove(userid, otherid)
def select(userid, premium=True):
if premium:
return d.execute(
"SELECT alias_name FROM useralias WHERE userid = %i AND settings ~ 'p'",
[userid], ["element"])
else:
return d.execute("SELECT alias_name FROM useralias WHERE userid = %i",
[userid], ["element"])
def accept(userid, otherid):
if check(userid, otherid):
raise WeasylError("Unexpected")
d.execute(
"UPDATE frienduser SET settings = REPLACE(settings, 'p', '')"
" WHERE (userid, otherid) = (%i, %i)", [otherid, userid])
welcome.frienduseraccept_insert(userid, otherid)
welcome.frienduserrequest_remove(userid, otherid)
def force_resetbirthday(userid, birthday):
if not birthday:
raise WeasylError("birthdayInvalid")
elif birthday > d.get_time():
raise WeasylError("birthdayInvalid")
d.execute("UPDATE userinfo SET birthday = %i WHERE userid = %i", [birthday, userid])
d.execute("UPDATE login SET settings = REPLACE(settings, 'i', '') WHERE userid = %i", [userid])
d.get_login_settings.invalidate(userid)
def edit_class(userid, commishclass):
if not commishclass.title:
raise error.WeasylError("titleInvalid")
try:
d.execute("UPDATE commishclass SET title = '%s' WHERE (classid, userid) = (%i, %i)",
[commishclass.title, commishclass.classid, userid])
except error.PostgresError:
raise error.WeasylError("titleExists")
def create_commission_class(userid, title):
if not title:
raise error.WeasylError("titleInvalid")
classid = d.execute("SELECT MAX(classid) + 1 FROM commishclass WHERE userid = %i", [userid], ["element"])
try:
d.execute("INSERT INTO commishclass VALUES (%i, %i, '%s')", [classid if classid else 1, userid, title])
except error.PostgresError:
raise error.WeasylError("commishclassExists")
def set(userid, username):
if login.username_exists(username):
raise WeasylError("usernameExists")
elif not d.get_premium(None, userid):
raise WeasylError("InsufficientPermissions")
d.execute("DELETE FROM useralias WHERE userid = %i AND settings ~ 'p'",
[userid])
d.execute("INSERT INTO useralias VALUES (%i, '%s', 'p')",
[userid, username])
def select_view(userid, charid, rating, ignore=True, anyway=None):
query = define.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 = %i
""", [charid], options=["single", "list"])
if query and userid in staff.MODS and anyway == "true":
pass
elif not query or "h" in query[11]:
raise WeasylError("characterRecordMissing")
elif query[10] > rating and ((userid != query[0] and userid not in staff.MODS) or define.is_sfw_mode()):
raise WeasylError("RatingExceeded")
elif "f" in query[11] 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, charid=charid):
raise WeasylError("TagBlocked")
if define.common_view_content(userid, charid, "char"):
query[12] += 1
login = define.get_sysname(query[1])
return {
"charid": charid,
"userid": query[0],
"username": query[1],
"user_media": media.get_user_media(query[0]),
"mine": userid == query[0],
"unixtime": query[2],
"title": query[3],
"age": query[4],
"gender": query[5],
"height": query[6],
"weight": query[7],
"species": query[8],
"content": query[9],
"rating": query[10],
"settings": query[11],
"reported": report.check(charid=charid),
"favorited": favorite.check(userid, charid=charid),
"page_views": query[12],
"friends_only": "f" in query[11],
"hidden_submission": "h" in query[11],
# todo
"fave_count": define.execute("SELECT COUNT(*) FROM favorite WHERE (targetid, type) = (%i, 'f')",
[charid], ["element"]),
"comments": comment.select(userid, charid=charid),
"sub_media": fake_media_items(charid, query[0], login, query[11]),
"tags": searchtag.select(charid=charid),
}
def edit_class(userid, commishclass):
if not commishclass.title:
raise error.WeasylError("titleInvalid")
try:
d.execute(
"UPDATE commishclass SET title = '%s' WHERE (classid, userid) = (%i, %i)",
[commishclass.title, commishclass.classid, userid])
except error.PostgresError:
raise error.WeasylError("titleExists")
def insert(userid, submitid=None, charid=None, journalid=None):
if submitid:
content_table, id_field, target = "submission", "submitid", submitid
elif charid:
content_table, id_field, target = "character", "charid", charid
else:
content_table, id_field, target = "journal", "journalid", journalid
query = d.execute("SELECT userid, settings FROM %s WHERE %s = %i",
[content_table, id_field, target],
options="single")
if not query:
raise WeasylError("TargetRecordMissing")
elif userid == query[0]:
raise WeasylError("CannotSelfFavorite")
elif "f" in query[1] and not frienduser.check(userid, query[0]):
raise WeasylError("FriendsOnly")
elif ignoreuser.check(userid, query[0]):
raise WeasylError("YouIgnored")
elif ignoreuser.check(query[0], userid):
raise WeasylError("contentOwnerIgnoredYou")
try:
d.execute("INSERT INTO favorite VALUES (%i, %i, '%s', %i)", [
userid,
d.get_targetid(submitid, charid, journalid),
"s" if submitid else "f" if charid else "j",
d.get_time()
])
except PostgresError:
raise WeasylError("favoriteRecordExists")
# create a list of users to notify
notified = set(collection.find_owners(submitid))
# conditions under which "other" should be notified
def can_notify(other):
other_jsonb = d.get_profile_settings(other)
allow_notify = other_jsonb.allow_collection_notifs
not_ignored = not ignoreuser.check(other, userid)
return allow_notify and not_ignored
notified = set(filter(can_notify, notified))
# always notify for own content
notified.add(query[0])
for other in notified:
welcome.favorite_insert(userid,
submitid=submitid,
charid=charid,
journalid=journalid,
otherid=other)
def force_resetbirthday(userid, birthday):
if not birthday:
raise WeasylError("birthdayInvalid")
elif birthday > d.get_time():
raise WeasylError("birthdayInvalid")
d.execute("UPDATE userinfo SET birthday = %i WHERE userid = %i",
[birthday, userid])
d.execute(
"UPDATE login SET settings = REPLACE(settings, 'i', '') WHERE userid = %i",
[userid])
d.get_login_settings.invalidate(userid)
def create_commission_class(userid, title):
if not title:
raise error.WeasylError("titleInvalid")
classid = d.execute(
"SELECT MAX(classid) + 1 FROM commishclass WHERE userid = %i",
[userid], ["element"])
try:
d.execute("INSERT INTO commishclass VALUES (%i, %i, '%s')",
[classid if classid else 1, userid, title])
except error.PostgresError:
raise error.WeasylError("commishclassExists")
def remove(userid, submitid=None, charid=None, journalid=None):
d.execute(
"DELETE FROM favorite WHERE (userid, targetid, type) = (%i, %i, '%s')",
[
userid,
d.get_targetid(submitid, charid, journalid),
"s" if submitid else "f" if charid else "j"
])
welcome.favorite_remove(userid,
submitid=submitid,
charid=charid,
journalid=journalid)
def edit_price(userid,
price,
currency="",
settings="",
edit_prices=False,
edit_settings=False):
currency = "".join(i for i in currency if i in "epycmu")
settings = "".join(i for i in settings if i in "a")
query = d.execute(
"SELECT amount_min, amount_max, settings, classid FROM commishprice"
" WHERE (priceid, userid) = (%i, %i)", [price.priceid, userid],
options="single")
if not query:
raise error.WeasylError("priceidInvalid")
elif price.amount_min > _MAX_PRICE:
raise error.WeasylError("minamountInvalid")
elif price.amount_max > _MAX_PRICE:
raise error.WeasylError("maxamountInvalid")
elif price.amount_max and price.amount_max < price.amount_min:
raise error.WeasylError("maxamountInvalid")
argv = []
statement = ["UPDATE commishprice SET "]
if price.title:
statement.append("%s title = '%%s'" % ("," if argv else ""))
argv.append(price.title)
if edit_prices:
if price.amount_min != query[0]:
statement.append("%s amount_min = %%i" % ("," if argv else ""))
argv.append(price.amount_min)
if price.amount_max != query[1]:
statement.append("%s amount_max = %%i" % ("," if argv else ""))
argv.append(price.amount_max)
if edit_settings:
statement.append("%s settings = '%%s'" % ("," if argv else ""))
argv.append("%s%s" % (currency, settings))
if not argv:
return
statement.append(" WHERE (priceid, userid) = (%i, %i)")
argv.extend([price.priceid, userid])
d.execute("".join(statement), argv)
def insert(userid, otherid):
if ignoreuser.check(otherid, userid):
raise WeasylError("IgnoredYou")
elif ignoreuser.check(userid, otherid):
raise WeasylError("YouIgnored")
try:
d.execute("INSERT INTO watchuser VALUES (%i, %i, '%s')",
[userid, otherid, WatchSettings.from_code(d.get_config(userid)).to_code()])
except IntegrityError:
pass
welcome.followuser_remove(userid, otherid)
welcome.followuser_insert(userid, otherid)
def check(userid, otherid, pending=False, myself=True):
if not userid or not otherid:
return False
elif userid == otherid:
return myself
if pending:
return d.execute(
"SELECT EXISTS (SELECT 0 FROM frienduser WHERE (userid, otherid) = (%i, %i) OR (userid, otherid) = (%i, %i))",
[userid, otherid, otherid, userid], options="bool")
else:
return d.execute(
"SELECT EXISTS (SELECT 0 FROM frienduser WHERE ((userid, otherid) = (%i, %i) OR (userid, otherid) = (%i, %i))"
" AND settings !~ 'p')", [userid, otherid, otherid, userid], options="bool")
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_manage(userid):
query = d.execute(
"""
SELECT
lo.userid, lo.last_login, lo.email, pr.unixtime, pr.username, pr.full_name, pr.catchphrase, ui.birthday,
ui.gender, ui.country, pr.config
FROM login lo
INNER JOIN profile pr USING (userid)
INNER JOIN userinfo ui USING (userid)
WHERE lo.userid = %i
""", [userid], ["single"])
if not query:
raise WeasylError("Unexpected")
return {
"userid": query[0],
"last_login": query[1],
"email": query[2],
"unixtime": query[3],
"username": query[4],
"full_name": query[5],
"catchphrase": query[6],
"birthday": query[7],
"gender": query[8],
"country": query[9],
"config": query[10],
"staff_notes": shout.count(userid, staffnotes=True),
}
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=None, pending=False, backid=None, nextid=None, config=None, options=[]):
if config is None:
config = d.get_config(userid)
statement = ["SELECT su.submitid, su.title, su.rating, co.unixtime, "
"su.userid, pr.username, su.settings, cpr.username, cpr.userid "]
statement.extend(select_query(userid, rating, otherid, pending,
backid, nextid, config))
statement.append(" ORDER BY co.unixtime%s LIMIT %i" % ("" if backid else " DESC", limit))
query = []
for i in d.execute("".join(statement)):
query.append({
"contype": 10,
"collection": True,
"submitid": i[0],
"title": i[1],
"rating": i[2],
"unixtime": i[3],
"userid": i[4],
"username": i[5], # username of creator
"collector": i[7], # username of collector
"collectorid": i[8],
"sub_media": media.get_submission_media(i[0]),
})
return query[::-1] if backid else query
def check(userid, otherid, pending=False, myself=True):
if not userid or not otherid:
return False
elif userid == otherid:
return myself
if pending:
return d.execute(
"SELECT EXISTS (SELECT 0 FROM frienduser WHERE (userid, otherid) = (%i, %i) OR (userid, otherid) = (%i, %i))",
[userid, otherid, otherid, userid],
options="bool")
else:
return d.execute(
"SELECT EXISTS (SELECT 0 FROM frienduser WHERE ((userid, otherid) = (%i, %i) OR (userid, otherid) = (%i, %i))"
" AND settings !~ 'p')", [userid, otherid, otherid, userid],
options="bool")
def select_accepted(userid, limit=None, backid=None, nextid=None):
result = []
query = d.execute(
"SELECT fr.userid, p1.username, p1.config, fr.otherid, p2.username, p2.config, fr.settings FROM frienduser fr"
" INNER JOIN profile p1 ON fr.userid = p1.userid"
" INNER JOIN profile p2 ON fr.otherid = p2.userid"
" WHERE %i IN (fr.userid, fr.otherid) AND fr.settings !~ 'p'"
" ORDER BY p1.username", [userid])
for i in query:
if i[0] != userid:
result.append({
"userid": i[0],
"username": i[1],
"settings": i[6],
})
else:
result.append({
"userid": i[3],
"username": i[4],
"settings": i[6],
})
media.populate_with_user_media(result)
return result
def list_followed(userid,
settings=None,
within=False,
rating=ratings.GENERAL.code,
friends=False):
"""
Returns a list of users who are following the specified user.
"""
statement = [
"SELECT wu.userid FROM watchuser wu JOIN profile pr ON wu.userid = pr.userid WHERE wu.otherid = %i"
]
if settings:
statement.append(" AND wu.settings ~ '%s'")
if friends:
statement.append("""
AND (
wu.userid IN (SELECT fu.userid FROM frienduser fu WHERE fu.otherid = wu.otherid AND fu.settings !~ 'p')
OR wu.userid IN (SELECT fu.otherid FROM frienduser fu WHERE fu.userid = wu.otherid AND fu.settings !~ 'p'))
""")
if rating == ratings.EXPLICIT.code:
# Only notify users who view explicit
statement.append(" AND pr.config ~ 'p'")
elif rating == ratings.MATURE.code:
# Only notify users who view explicit or explicit
statement.append(" AND pr.config ~ '[ap]'")
elif rating == ratings.MODERATE.code:
# Only notify users who view mature or explicit or explicit
statement.append(" AND pr.config ~ '[map]'")
return d.execute("".join(statement),
[userid, settings] if settings else [userid],
options=["within"] if within else [])
def select_followed(userid, otherid, limit=None, backid=None, nextid=None, choose=None, following=False):
"""
Returns the users who are following the specified user; note that
``following`` need never be passed explicitly.
"""
if following:
statement = ["SELECT wu.otherid, pr.username, pr.config FROM watchuser wu"
" INNER JOIN profile pr ON wu.otherid = pr.userid"
" WHERE wu.userid = %i" % (otherid,)]
else:
statement = ["SELECT wu.userid, pr.username, pr.config FROM watchuser wu"
" INNER JOIN profile pr ON wu.userid = pr.userid"
" WHERE wu.otherid = %i" % (otherid,)]
if userid:
statement.append(m.MACRO_IGNOREUSER % (userid, "pr"))
if backid:
statement.append(" AND pr.username < (SELECT username FROM profile WHERE userid = %i)" % (backid,))
elif nextid:
statement.append(" AND pr.username > (SELECT username FROM profile WHERE userid = %i)" % (nextid,))
if choose:
statement.append(" ORDER BY RANDOM() LIMIT %i" % (choose,))
else:
statement.append(" ORDER BY pr.username%s LIMIT %i" % (" DESC" if backid else "", limit))
query = [{
"userid": i[0],
"username": i[1],
} for i in d.execute("".join(statement))]
media.populate_with_user_media(query)
return query[::-1] if backid else query
def list_followed(userid, settings=None, within=False, rating=ratings.GENERAL.code, friends=False):
"""
Returns a list of users who are following the specified user.
"""
statement = ["SELECT wu.userid FROM watchuser wu JOIN profile pr ON wu.userid = pr.userid WHERE wu.otherid = %i"]
if settings:
statement.append(" AND wu.settings ~ '%s'")
if friends:
statement.append("""
AND (
wu.userid IN (SELECT fu.userid FROM frienduser fu WHERE fu.otherid = wu.otherid AND fu.settings !~ 'p')
OR wu.userid IN (SELECT fu.otherid FROM frienduser fu WHERE fu.userid = wu.otherid AND fu.settings !~ 'p'))
""")
if rating == ratings.EXPLICIT.code:
# Only notify users who view explicit
statement.append(" AND pr.config ~ 'p'")
elif rating == ratings.MATURE.code:
# Only notify users who view explicit or explicit
statement.append(" AND pr.config ~ '[ap]'")
elif rating == ratings.MODERATE.code:
# Only notify users who view mature or explicit or explicit
statement.append(" AND pr.config ~ '[map]'")
return d.execute("".join(statement),
[userid, settings] if settings else [userid],
options=["within"] if within else [])
def select(userid, otherid, limit=None, pending=False, backid=None, nextid=None, choose=None, gotrequest=False):
query = []
if gotrequest:
statement = ["SELECT fr.otherid, pr.username, fr.settings, pr.config FROM frienduser fr"
" INNER JOIN profile pr ON fr.otherid = pr.userid"
" WHERE fr.otherid = %i AND fr.settings ~ 'p'" % (userid,)]
else:
statement = ["SELECT fr.otherid, pr.username, fr.settings, pr.config FROM frienduser fr"
" INNER JOIN profile pr ON fr.otherid = pr.userid"
" WHERE fr.userid = %i AND fr.settings %s~ 'p'" % (otherid, "" if pending else "!")]
if backid:
statement.append(" AND pr.username < (SELECT username FROM profile WHERE userid = %i)" % (backid,))
elif nextid:
statement.append(" AND pr.username > (SELECT username FROM profile WHERE userid = %i)" % (nextid,))
statement.append(" ORDER BY pr.username" + (" DESC" if nextid else ""))
if limit:
statement.append(" LIMIT %i" % (limit,))
for i in d.execute("".join(statement)):
query.append({
"userid": i[0],
"username": i[1],
"settings": i[2],
})
ret = (d.get_random_set(query, choose) if choose else query[::-1] if backid else query)
media.populate_with_user_media(ret)
return ret
def suggest(userid, target):
if not target:
return []
return d.execute("SELECT title FROM searchtag"
" WHERE title LIKE '%s%%' AND tagid NOT IN (SELECT tagid FROM blocktag WHERE userid = %i)"
" ORDER BY title LIMIT 10", [target, userid], options="within")
def select_accepted(userid, limit=None, backid=None, nextid=None):
result = []
query = d.execute(
"SELECT fr.userid, p1.username, p1.config, fr.otherid, p2.username, p2.config, fr.settings FROM frienduser fr"
" INNER JOIN profile p1 ON fr.userid = p1.userid"
" INNER JOIN profile p2 ON fr.otherid = p2.userid"
" WHERE %i IN (fr.userid, fr.otherid) AND fr.settings !~ 'p'"
" ORDER BY p1.username", [userid])
for i in query:
if i[0] != userid:
result.append({
"userid": i[0],
"username": i[1],
"settings": i[6],
})
else:
result.append({
"userid": i[3],
"username": i[4],
"settings": i[6],
})
media.populate_with_user_media(result)
return result
def manage_following(userid, limit, backid=None, nextid=None):
state = [
"SELECT pr.userid, pr.username, pr.config FROM watchuser wu"
" JOIN profile pr ON wu.otherid = pr.userid"
" WHERE wu.userid = %i" % (userid,)]
if backid:
state.append(" AND pr.username < (SELECT username FROM profile WHERE userid = %i)" % backid)
elif nextid:
state.append(" AND pr.username > (SELECT username FROM profile WHERE userid = %i)" % nextid)
state.append(" ORDER BY pr.username")
if backid:
state.append(" DESC")
state.append(" LIMIT %i" % limit)
query = [{
"userid": i[0],
"username": i[1],
} for i in d.execute("".join(state))]
media.populate_with_user_media(query)
return query[::-1] if backid else query
def _select_statistics(userid):
query = d.execute("""
SELECT
(SELECT page_views FROM profile WHERE userid = %i),
0,
(SELECT COUNT(*) FROM favorite WHERE userid = %i),
(SELECT
(SELECT COUNT(*) FROM favorite fa JOIN submission su ON fa.targetid = su.submitid
WHERE su.userid = %i AND fa.type = 's') +
(SELECT COUNT(*) FROM favorite fa JOIN character ch ON fa.targetid = ch.charid
WHERE ch.userid = %i AND fa.type = 'f') +
(SELECT COUNT(*) FROM favorite fa JOIN journal jo ON fa.targetid = jo.journalid
WHERE jo.userid = %i AND fa.type = 'j')),
(SELECT COUNT(*) FROM watchuser WHERE otherid = %i),
(SELECT COUNT(*) FROM watchuser WHERE userid = %i),
(SELECT COUNT(*) FROM submission WHERE userid = %i AND settings !~ 'h'),
(SELECT COUNT(*) FROM journal WHERE userid = %i AND settings !~ 'h'),
(SELECT COUNT(*) FROM comments WHERE target_user = %i AND settings !~ 'h' AND settings ~ 's')
""", [userid, userid, userid, userid, userid, userid, userid, userid, userid, userid], options="single")
return {
"page_views": query[0],
"submit_views": query[1],
"faves_sent": query[2],
"faves_received": query[3],
"followed": query[4],
"following": query[5],
"submissions": query[6],
"journals": query[7],
"staff_notes": query[8],
}
def charactersbyuser(userid, form):
if userid not in staff.MODS:
raise WeasylError("Unexpected")
query = d.execute("""
SELECT
ch.charid, 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 ON ch.userid = pr.userid
INNER JOIN login ON ch.userid = login.userid
WHERE login.login_name = '%s'
""", [d.get_sysname(form.name)])
return [{
"contype": 20,
"userid": userid,
"charid": item[0],
"username": item[1],
"unixtime": item[2],
"title": item[3],
"rating": item[10],
"settings": item[11],
"sub_media": character.fake_media_items(item[0], userid, d.get_sysname(item[1]), item[11]),
} for item in query]
def insert(userid, otherid):
if ignoreuser.check(otherid, userid):
raise WeasylError("IgnoredYou")
elif ignoreuser.check(userid, otherid):
raise WeasylError("YouIgnored")
try:
d.execute("INSERT INTO watchuser VALUES (%i, %i, '%s')", [
userid, otherid,
WatchSettings.from_code(d.get_config(userid)).to_code()
])
except IntegrityError:
pass
welcome.followuser_remove(userid, otherid)
welcome.followuser_insert(userid, otherid)
def select_settings(userid, otherid):
query = d.execute("SELECT settings FROM watchuser WHERE (userid, otherid) = (%i, %i)", [userid, otherid], ["single"])
if not query:
raise WeasylError("watchuserRecordMissing")
return query[0]
def twitter_card(userid):
username, full_name, catchphrase, profile_text, config, twitter = d.execute(
"SELECT pr.username, pr.full_name, pr.catchphrase, pr.profile_text, pr.config, ul.link_value "
"FROM profile pr "
"LEFT JOIN user_links ul ON pr.userid = ul.userid AND ul.link_type = 'twitter' "
"WHERE pr.userid = %i", [userid], ["single"])
ret = {
'card': 'summary',
'url': d.absolutify_url('/~%s' % (username, )),
'title': '%s on Weasyl' % (full_name, ),
}
if catchphrase:
description = '"%s"' % (catchphrase, )
elif profile_text:
description = strip_html(profile_text)
else:
description = "[%s has an empty profile, but is eggcelent!]" % (
full_name, )
ret['description'] = d.summarize(description)
media_items = media.get_user_media(userid)
ret['image:src'] = d.absolutify_url(
media_items['avatar'][0]['display_url'])
if twitter:
ret['creator'] = '@%s' % (twitter.lstrip('@'), )
return ret
def twitter_card(userid):
username, full_name, catchphrase, profile_text, config, twitter = d.execute(
"SELECT pr.username, pr.full_name, pr.catchphrase, pr.profile_text, pr.config, ul.link_value "
"FROM profile pr "
"LEFT JOIN user_links ul ON pr.userid = ul.userid AND ul.link_type = 'twitter' "
"WHERE pr.userid = %i",
[userid],
["single"])
ret = {
'card': 'summary',
'url': d.absolutify_url('/~%s' % (username,)),
'title': '%s on Weasyl' % (full_name,),
}
if catchphrase:
description = '"%s"' % (catchphrase,)
elif profile_text:
description = strip_html(profile_text)
else:
description = "[%s has an empty profile, but is eggcelent!]" % (full_name,)
ret['description'] = d.summarize(description)
media_items = media.get_user_media(userid)
ret['image:src'] = d.absolutify_url(media_items['avatar'][0]['display_url'])
if twitter:
ret['creator'] = '@%s' % (twitter.lstrip('@'),)
return ret
def select_relation(userid, otherid):
if not userid or userid == otherid:
return {
"follow": False,
"friend": False,
"ignore": False,
"friendreq": False,
"is_self": userid == otherid,
}
query = d.execute(
"""
SELECT
(SELECT EXISTS (SELECT 0 FROM watchuser WHERE (userid, otherid) = (%i, %i))),
(SELECT EXISTS (SELECT 0 FROM frienduser WHERE userid IN (%i, %i) AND otherid IN (%i, %i) AND settings !~ 'p')),
(SELECT EXISTS (SELECT 0 FROM ignoreuser WHERE (userid, otherid) = (%i, %i))),
(SELECT EXISTS (SELECT 0 FROM frienduser WHERE (userid, otherid) = (%i, %i) AND settings ~ 'p'))
""", [
userid, otherid, userid, otherid, userid, otherid, userid, otherid,
userid, otherid
], ["single"])
return {
"follow": query[0],
"friend": query[1],
"ignore": query[2],
"friendreq": query[3],
"is_self": False,
}
def select_streaming(userid, rating, limit, following=True, order_by=None):
statement = [
"SELECT userid, pr.username, pr.stream_url, pr.config, pr.stream_text, start_time "
"FROM profile pr "
"JOIN user_streams USING (userid) "
"WHERE end_time > %i" % (d.get_time(),)
]
if userid:
statement.append(m.MACRO_IGNOREUSER % (userid, "pr"))
if following:
pass # todo
if order_by:
statement.append(" ORDER BY %s LIMIT %i" % (order_by, limit))
else:
statement.append(" ORDER BY RANDOM() LIMIT %i" % limit)
ret = [{
"userid": i[0],
"username": i[1],
"stream_url": i[2],
"stream_text": i[4],
"stream_time": i[5],
} for i in d.execute("".join(statement)) if i[2]]
media.populate_with_user_media(ret)
return ret
def _select_statistics(userid):
query = d.execute("""
SELECT
(SELECT page_views FROM profile WHERE userid = %i),
0,
(SELECT COUNT(*) FROM favorite WHERE userid = %i),
(SELECT
(SELECT COUNT(*) FROM favorite fa JOIN submission su ON fa.targetid = su.submitid
WHERE su.userid = %i AND fa.type = 's') +
(SELECT COUNT(*) FROM favorite fa JOIN character ch ON fa.targetid = ch.charid
WHERE ch.userid = %i AND fa.type = 'f') +
(SELECT COUNT(*) FROM favorite fa JOIN journal jo ON fa.targetid = jo.journalid
WHERE jo.userid = %i AND fa.type = 'j')),
(SELECT COUNT(*) FROM watchuser WHERE otherid = %i),
(SELECT COUNT(*) FROM watchuser WHERE userid = %i),
(SELECT COUNT(*) FROM submission WHERE userid = %i AND settings !~ 'h'),
(SELECT COUNT(*) FROM journal WHERE userid = %i AND settings !~ 'h'),
(SELECT COUNT(*) FROM comments WHERE target_user = %i AND settings !~ 'h' AND settings ~ 's')
""", [
userid, userid, userid, userid, userid, userid, userid, userid, userid,
userid
],
options="single")
return {
"page_views": query[0],
"submit_views": query[1],
"faves_sent": query[2],
"faves_received": query[3],
"followed": query[4],
"following": query[5],
"submissions": query[6],
"journals": query[7],
"staff_notes": query[8],
}
def select_manage(userid):
query = d.execute("""
SELECT
lo.userid, lo.last_login, lo.email, pr.unixtime, pr.username, pr.full_name, pr.catchphrase, ui.birthday,
ui.gender, ui.country, pr.config
FROM login lo
INNER JOIN profile pr USING (userid)
INNER JOIN userinfo ui USING (userid)
WHERE lo.userid = %i
""", [userid], ["single"])
if not query:
raise WeasylError("Unexpected")
return {
"userid": query[0],
"last_login": query[1],
"email": query[2],
"unixtime": query[3],
"username": query[4],
"full_name": query[5],
"catchphrase": query[6],
"birthday": query[7],
"gender": query[8],
"country": query[9],
"config": query[10],
"staff_notes": shout.count(userid, staffnotes=True),
}
def select_streaming(userid, rating, limit, following=True, order_by=None):
statement = [
"SELECT userid, pr.username, pr.stream_url, pr.config, pr.stream_text, start_time "
"FROM profile pr "
"JOIN user_streams USING (userid) "
"WHERE end_time > %i" % (d.get_time(), )
]
if userid:
statement.append(m.MACRO_IGNOREUSER % (userid, "pr"))
if following:
pass # todo
if order_by:
statement.append(" ORDER BY %s LIMIT %i" % (order_by, limit))
else:
statement.append(" ORDER BY RANDOM() LIMIT %i" % limit)
ret = [{
"userid": i[0],
"username": i[1],
"stream_url": i[2],
"stream_text": i[4],
"stream_time": i[5],
} for i in d.execute("".join(statement)) if i[2]]
media.populate_with_user_media(ret)
return ret
def manage_following(userid, limit, backid=None, nextid=None):
state = [
"SELECT pr.userid, pr.username, pr.config FROM watchuser wu"
" JOIN profile pr ON wu.otherid = pr.userid"
" WHERE wu.userid = %i" % (userid, )
]
if backid:
state.append(
" AND pr.username < (SELECT username FROM profile WHERE userid = %i)"
% backid)
elif nextid:
state.append(
" AND pr.username > (SELECT username FROM profile WHERE userid = %i)"
% nextid)
state.append(" ORDER BY pr.username")
if backid:
state.append(" DESC")
state.append(" LIMIT %i" % limit)
query = [{
"userid": i[0],
"username": i[1],
} for i in d.execute("".join(state))]
media.populate_with_user_media(query)
return query[::-1] if backid else query
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
