def new_img(f, title, parent, userid, ip):
raw = f.read()
username = users.user_by_uid(userid)
if not imghdr.what(None, raw):
logger.info('failed to add image to {} by {} / {} '.format(
parent, username, ip))
return
image = base64.b64encode(raw)
title = title.strip()[:64]
sql = "insert into images (tag, parent, userid, image, ip) values (%(tag)s, %(parent)s, %(userid)s, %(image)s, %(ip)s);"
doquery(
sql, {
'tag': title,
'userid': userid,
'ip': utility.ip_uid(ip),
'parent': parent,
'image': image
})
# there is a potential race condition with last_insert_id()
sql = "select last_insert_id();"
imgid = doquery(sql)[0][0]
sql = "insert into imgmods (userid, imgid) values (%(userid)s, %(imgid)s);"
doquery(sql, {'userid': userid, 'imgid': imgid})
logger.info('new image added to {} by {} / {} '.format(
parent, username, ip))
return imgid
def __init__(self, name, lang=DEFAULTLANG):
if not name:
raise NoString(name, lang)
sql = 'select id, string from strings where name = %(name)s and lang = %(lang)s;'
result = doquery(sql, {'name': name, 'lang': lang})
try:
self.uid = result[0][0]
self.name = name
self.string = base64.b64decode(result[0][1])
self.lang = lang
except IndexError:
sql = 'select id, string from strings where name = %(name)s and lang = %(lang)s;'
result = doquery(sql, {
'name': name,
'lang': SiteString.DEFAULTLANG
})
try:
self.uid = result[0][0]
self.name = name
self.string = base64.b64decode(result[0][1])
self.lang = SiteString.DEFAULTLANG
except IndexError:
uid = new_string(name, name)
if not uid:
raise NoString(name)
self.uid = uid
self.name = name
self.string = name
self.lang = lang
def new_key(key, value):
"""
Create a new key/value pair in the database.
:param key: Name of the key. Will be hashed
:param value: The value to be stored.
"""
if not key:
return NoKey(key)
key = utility.digest(key)
try:
sql = 'select value from keyvalue where keyhash = %(key)s;'
result = doquery(sql, {'key': key})[0][0]
return
except IndexError:
pass
try:
sql = "insert into keyvalue (keyhash, value) values (%(key)s, %(value)s);"
doquery(sql, {'key': key, 'value': base64.b64encode(value)})
sql = "select last_insert_id();"
uid = doquery(sql)[0][0]
if uid is None:
raise NoKey(key)
except (MySQLdb.OperationalError, MySQLdb.DataError, Warning):
raise NoKey(key)
def delete(self):
"""
Delete an item. Might be dangerous.
"""
logger.info('deleted item id {}: {}'.format(self.uid, self.name))
item_cache = dict()
for image in self.images():
image.delete()
sql = 'delete from itemedits where itemid = %(uid)s;'
result = doquery(sql, {"uid": self.uid})
sql = 'delete from ownwant where itemid = %(itemid)s;'
result = doquery(sql, {"itemid": self.uid})
sql = 'delete from tradelist where itemid = %(itemid)s;'
result = doquery(sql, {"itemid": self.uid})
sql = 'delete from itemtags where itemid = %(uid)s;'
result = doquery(sql, {"uid": self.uid})
sql = 'delete from items where uid = %(uid)s;'
result = doquery(sql, {"uid": self.uid})
def new_string(name, string, lang=SiteString.DEFAULTLANG):
"""
Create a new string in the database.
:param name: Name of the string. Strings configurable by the user should have all-lowercase descriptive names so as not to conflict with translations. When used for translation the name should be the DEFAULTLANG version of the string itself.
:param string: The string to be stored.
:param lang: Optional parameter, defaults to DEFAULTLANG
:return: UID of the new string, or UID of the existing string if it already exists
"""
if not name or not string:
return NoString(name, string)
try:
sql = 'select id from strings where name = %(name)s and lang = %(lang)s;'
result = doquery(sql, { 'name': name, 'lang': lang })
return result[0][0]
except IndexError:
pass
try:
sql = "insert into strings (name, string, lang) values (%(name)s, %(string)s, %(lang)s);"
doquery(sql, {'name': name, 'string': base64.b64encode(string), 'lang': lang})
sql = "select last_insert_id();"
uid = doquery(sql)[0][0]
if uid is None:
raise NoString(name, lang)
except (MySQLdb.OperationalError, MySQLdb.DataError, Warning):
raise NoString(name, lang)
return uid
def new_key(key, value):
"""
Create a new key/value pair in the database.
:param key: Name of the key. Will be hashed
:param value: The value to be stored.
"""
if not key:
return NoKey(key)
key = utility.digest(key)
try:
sql = 'select value from keyvalue where keyhash = %(key)s;'
result = doquery(sql, {'key': key})[0][0]
return
except IndexError:
pass
try:
sql = "insert into keyvalue (keyhash, value) values (%(key)s, %(value)s);"
doquery(sql, {'key': key, 'value': base64.b64encode(value)})
sql = "select last_insert_id();"
uid = doquery(sql)[0][0]
if uid is None:
raise NoKey(key)
except (MySQLdb.OperationalError, MySQLdb.DataError, Warning):
raise NoKey(key)
def __init__(self, name, lang=DEFAULTLANG):
if not name:
raise NoString(name, lang)
sql = 'select id, string from strings where name = %(name)s and lang = %(lang)s;'
result = doquery(sql, { 'name': name, 'lang': lang })
try:
self.uid = result[0][0]
self.name = name
self.string = base64.b64decode(result[0][1])
self.lang = lang
except IndexError:
sql = 'select id, string from strings where name = %(name)s and lang = %(lang)s;'
result = doquery(sql, { 'name': name, 'lang': SiteString.DEFAULTLANG })
try:
self.uid = result[0][0]
self.name = name
self.string = base64.b64decode(result[0][1])
self.lang = SiteString.DEFAULTLANG
except IndexError:
uid = new_string(name, name)
if not uid:
raise NoString(name)
self.uid = uid
self.name = name
self.string = name
self.lang = lang
def delete(self):
"""
Delete an item. Might be dangerous.
"""
self.deleted = True
for image in self.images():
image.delete()
sql = 'delete from itemedits where itemid = %(uid)s;'
result = doquery(sql, {"uid": self.uid})
sql = 'delete from ownwant where itemid = %(itemid)s;'
result = doquery(sql, {"itemid": self.uid})
sql = 'delete from tradelist where itemid = %(itemid)s;'
result = doquery(sql, {"itemid": self.uid})
sql = 'delete from itemtags where itemid = %(uid)s;'
result = doquery(sql, {"uid": self.uid})
sql = 'delete from items where uid = %(uid)s;'
result = doquery(sql, {"uid": self.uid})
logger.info('deleted item id {}: {}'.format(self.uid, self.name))
self.uid = None
self.name = None
self.added = None
self.modified = None
def user_search(query, limit=10, offset=0, sort='name'):
ret = dict()
ret['users'] = list()
sql = 'select count(*) from users where username like %(query)s;'
ret['maxresults'] = doquery(sql, {'query': '%{}%'.format(query)})[0][0]
if ret['maxresults'] == 0:
return ret
sorts = {'username': '******', 'joined': 'date joined'}
if sort not in sorts.keys():
sort = 'username'
sql = 'select username from users where upper(username) like upper(%(query)s) order by {} limit %(limit)s offset %(offset)s;'.format(
sorts[sort])
result = doquery(sql, {
'query': '%{}%'.format(query),
'limit': limit,
'offset': offset
})
for item in result:
ret['users'].append(SiteUser.create(item[0]))
return ret
def __init__(self, username=None, uid=None):
try:
if username:
uid = uid_by_user(username)
if not uid:
raise NoUser(None)
sql = """select json
from user_profiles
where uid = %(uid)s; """
result = doquery(sql, {'uid': uid})
self.uid = uid
self.profile = json.loads(result[0][0])
except (Warning, IndexError):
# return defaults
# TODO: default profile dict
self.profile = dict()
self.profile['timezone'] = "America/Los_Angeles"
sql = "insert into user_profiles (uid, json) values (%(uid)s, %(json)s);"
result = doquery(sql, {
'uid': uid,
'json': json.dumps(self.profile)
})
def item_search(query, limit=10, offset=0, sort='name'):
ret = dict()
ret['items'] = list()
sql = 'select count(*) from items where name like %(query)s;'
ret['maxresults'] = doquery(sql, {'query': '%{}%'.format(query)})[0][0]
if ret['maxresults'] == 0:
return ret
sorts = {
'name': 'name asc',
'added': 'added desc',
'modified': 'modified desc'
}
if sort not in sorts.keys():
sort = 'name'
sql = 'select uid from items where name like %(query)s order by {} limit %(limit)s offset %(offset)s;'.format(
sorts[sort])
result = doquery(sql, {
'query': '%{}%'.format(query),
'limit': limit,
'offset': offset
})
for item in result:
ret['items'].append(SiteItem.create(item[0]))
return ret
def new_edit(itemid, description, userid, ip):
if userid == 0:
userid = None
username = users.user_by_uid(userid)
try:
date = datetime.datetime.utcnow().strftime("%Y-%m-%d %H:%M:%S")
sql = "insert into itemedits (date, itemid, userid, ip, body) values (%(date)s, %(itemid)s, %(userid)s, %(ip)s, %(body)s);"
doquery(
sql, {
'date': date,
'itemid': itemid,
'userid': userid,
'ip': ip_uid(ip),
'body': description
})
sql = "select uid from itemedits where date=%(date)s and itemid=%(itemid)s and ip=%(ip)s;"
edit = doquery(sql, {
'date': date,
'itemid': itemid,
'ip': ip_uid(ip)
})[0][0]
sql = "update items set description = %(edit)s, modified = %(modified)s where uid = %(uid)s;"
doquery(sql, {"uid": itemid, "edit": edit, "modified": date})
logger.info('item {} edited by {} / {} '.format(itemid, username, ip))
return edit
except MySQLdb.OperationalError, Warning:
logger.info('Error editing item {} by {} ({})'.format(
itemid, username, ip))
#FIXME: raise something else
raise NoItem(itemid)
def newaccesslevel(self, accesslevel):
logger.info('Accesslevel change for user {}, was {} is now {}'.format(
self.username, self.accesslevel, accesslevel))
self.accesslevel = int(accesslevel)
sql = "update users set accesslevel = %(level)s where uid = %(uid)s;"
doquery(sql, {"uid": self.uid, "level": self.accesslevel})
def new_user(username, password, email, ip):
"""
Register a new user
:param username: Username. Truncated to 200 characters
:param password: Cleartext password
:param email: email address. Truncated to 200 characters
:param ip: IP address of the requester
:raises NoUser: if an invalid email or username is given, or on general failure in creating the user
:return: UID of the new user or False if the username is taken
"""
username = unicode(username).strip()[:200]
email = email.strip()[:200]
pwhash = gen_pwhash(password)
if len(username) == 0:
raise NoUser(0)
if len(email) < 3:
raise NoUser(0)
joined = datetime.datetime.utcnow().strftime("%Y-%m-%d %H:%M:%S")
try:
sql = "select uid from users where username = %(username)s;"
uid = doquery(sql, {'username': username})[0][0]
# user exists
return False
except (Warning, IndexError):
# user doesn't exist
pass
try:
sql = "insert into users (username, pwhash, email, joined, accesslevel) values (%(username)s, %(pwhash)s, %(email)s, %(joined)s, '1');"
result = doquery(
sql, {
'username': username,
'pwhash': pwhash,
'email': email,
'joined': joined
})
uid = doquery("select last_insert_id();")[0][0]
if not uid:
raise NoUser(username)
sql = "insert into userstat_lastseen (date, uid) values (%(lastseen)s, %(uid)s);"
result = doquery(sql, {'uid': uid, 'lastseen': joined})
except MySQLdb.Error, e:
try:
logger.info('MySQL error adding new user {} - {}: {})'.format(
username, e.args[0], e.args[1]))
raise NoUser(username)
except IndexError:
logger.info('MySQL error adding new user {} - {})'.format(
username, e))
raise NoUser(username)
def delete(self):
logger.info('deleted image id {}: {}'.format(self.uid, self.tag))
siteimage_cache = dict()
#TODO image purgatory
sql = 'delete from imgmods where imgid = %(uid)s;'
result = doquery(sql, {'uid': self.uid})
sql = 'delete from images where uid = %(uid)s;'
result = doquery(sql, {'uid': self.uid})
def remove_tag(self, tag):
logger.info('tag {} removed from {}: {} '.format(tag, self.uid, self.name))
try:
self.tree.retrieve(tag)
except IndexError:
return
sql = "delete from itemtags where itemid=%(itemid)s and tag=%(tag)s;"
doquery(sql, { 'itemid': self.uid, 'tag': tag })
def delete(self):
logger.info('deleted image id {}: {}'.format(self.uid, self.tag))
siteimage_cache = dict()
#TODO image purgatory
sql = 'delete from imgmods where imgid = %(uid)s;'
result = doquery(sql, { 'uid': self.uid })
sql = 'delete from images where uid = %(uid)s;'
result = doquery(sql, { 'uid': self.uid })
def add_tradeitem(itemid, messageid, userid, acceptstatus):
sql = "insert into tradelist (itemid, messageid, userid, acceptstatus) values (%(itemid)s, %(messageid)s, %(userid)s, %(acceptstatus)s);"
doquery(
sql, {
'itemid': itemid,
'messageid': messageid,
'userid': userid,
'acceptstatus': acceptstatus
})
def delete(self, nodename):
if nodename == 'Unsorted':
return False
if nodename == self.root:
return False
sql = "delete from itemtags where tag=%(tag)s;"
doquery(sql, { 'tag': nodename })
return super(self.__class__, self).delete(nodename)
def ip_uid(ip, r=False):
try:
sql = "select uid from ip where ip = %(ip)s;"
result = doquery(sql, { 'ip': ip })
return result[0][0]
except IndexError:
if r:
return None
sql = "insert into ip (ip) values ( %(ip)s );"
result = doquery(sql, { 'ip': ip })
return ip_uid(ip, True)
def delete(self, nodename):
if nodename == 'Unsorted':
return False
if nodename == self.root:
return False
sql = "delete from itemtags where tag=%(tag)s;"
doquery(sql, {'tag': nodename})
return super(self.__class__, self).delete(nodename)
def ip_uid(ip, r=False):
try:
sql = "select uid from ip where ip = %(ip)s;"
result = doquery(sql, {'ip': ip})
return result[0][0]
except IndexError:
if r:
return None
sql = "insert into ip (ip) values ( %(ip)s );"
result = doquery(sql, {'ip': ip})
return ip_uid(ip, True)
def save(self, userid, ip, overwrite=False):
sql_image = base64.b64encode(self.preview().read())
if overwrite:
sql = "update images set image = %(image)s where uid = %(uid)s;"
else:
sql = "insert into images (tag, parent, userid, image, ip) values (%(tag)s, %(parent)s, %(userid)s, %(image)s, %(ip)s);"
doquery(sql, { 'tag': self.tag, 'userid': userid, 'ip': utility.ip_uid(ip), 'parent': self.parent, 'image': sql_image})
sql = "select last_insert_id();"
return doquery(sql)[0][0]
def newaccesslevel(self, accesslevel):
"""
Method to change a user's access level
:param accesslevel: The new accesslevel
"""
logger.info('Accesslevel change for user {}, was {} is now {}'.format(self.username, self.accesslevel, accesslevel))
self.accesslevel = int(accesslevel)
sql = "update users set accesslevel = %(level)s where uid = %(uid)s;"
doquery(sql, {"uid": self.uid, "level": self.accesslevel})
def new_item(name, description, userid, ip):
name = name.strip()[:64]
try:
sql = "insert into items (name, description, added, modified) values (%(name)s, 0, %(now)s, %(now)s);"
doquery(sql, { 'now': datetime.datetime.utcnow().strftime("%Y-%m-%d %H:%M:%S"), 'name': name })
sql = "select uid from items where name=%(name)s and description=0;"
itemid = doquery(sql, { 'name': name })[0][0]
except MySQLdb.OperationalError, Warning:
logger.info('Error adding item {} by {} ({})'.format(name, userid, ip))
raise NoItem(0)
def new_user(username, password, email, ip):
"""
Register a new user
:param username: Username. Truncated to 200 characters
:param password: Cleartext password
:param email: email address. Truncated to 200 characters
:param ip: IP address of the requester
:raises NoUser: if an invalid email or username is given, or on general failure in creating the user
:return: UID of the new user or False if the username is taken
"""
username = unicode(username).strip()[:200]
email = email.strip()[:200]
pwhash = gen_pwhash(password)
if len(username) == 0:
raise NoUser(0)
if len(email) < 3:
raise NoUser(0)
joined = datetime.datetime.utcnow().strftime("%Y-%m-%d %H:%M:%S")
try:
sql = "select uid from users where username = %(username)s;"
uid = doquery(sql, { 'username': username })[0][0]
# user exists
return False
except (Warning, IndexError):
# user doesn't exist
pass
try:
sql = "insert into users (username, pwhash, email, joined, accesslevel) values (%(username)s, %(pwhash)s, %(email)s, %(joined)s, '1');"
result = doquery(sql, { 'username': username, 'pwhash': pwhash, 'email': email, 'joined': joined })
uid = doquery("select last_insert_id();")[0][0]
if not uid:
raise NoUser(username)
sql = "insert into userstat_lastseen (date, uid) values (%(lastseen)s, %(uid)s);"
result = doquery(sql, { 'uid': uid, 'lastseen': joined })
except MySQLdb.Error, e:
try:
logger.info('MySQL error adding new user {} - {}: {})'.format(username, e.args[0], e.args[1]))
raise NoUser(username)
except IndexError:
logger.info('MySQL error adding new user {} - {})'.format(username, e))
raise NoUser(username)
def authenticate(self, password):
"""
Verify a user's password
:param password: The plaintext password to verify.
:return: None
:raises AuthFail: This exception will be raised if the user cannot be logged in for any reason.
"""
sql = """select users.pwhash
from users
where users.uid = %(uid)s;"""
result = doquery(sql, {'uid': self.uid})
try:
pwhash = result[0][0]
except IndexError:
logger.info('AuthFail for user {}: unable to find user'.format(
self.username))
raise AuthFail(self.username)
if self.accesslevel == 0:
logger.info('AuthFail for user {}: account has been banned'.format(
self.username))
raise AuthFail(self.username)
if not verify_pw(password, pwhash):
logger.info('AuthFail for user {}: invalid password'.format(
self.username))
raise AuthFail(self.username)
logger.info('Successful password authentication for user {}'.format(
self.username))
return None
def messages(self, trash=False):
"""
Get all trades and private messages for a user
:param trash: Only show deleted messages. Permanent deletion is not currently implemented.
:return: list of PrivateMessage and TradeMessage objects
"""
mwi_cache = dict()
ret = list()
sql = """select uid,status from messages
where fromuserid = %(fromuid)s or touserid = %(touid)s
order by sent desc;"""
result = doquery(sql, {'fromuid': self.uid, 'touid': self.uid})
for item in result:
if item[1]:
message = messages.TradeMessage.create(item[0])
else:
message = messages.PrivateMessage.create(item[0])
deleted = message.delete_status(self.username)
if trash:
deleted = not deleted
if not deleted:
ret.append(message)
return ret
def mwi(self):
"""
Get a count of unread messages and trades for a user
:return: (unread messages, unread trades)
"""
num_messages = 0
num_trades = 0
sql = """select uid,status from messages
where touserid = %(touid)s
order by sent desc limit 100;"""
result = doquery(sql, {'touid': self.uid})
for item in result:
message = messages.TradeMessage.create(item[0])
if message.delete_status(self.username):
continue
read = message.read_status(self.username)
if not read:
if item[1]:
num_trades = num_trades + 1
else:
num_messages = num_messages + 1
return (num_messages, num_trades)
def collection(self):
"""
List a user's collection
:return: list of SiteItem objects
"""
ret = list()
sql = """select ownwant.own, ownwant.willtrade, ownwant.want, ownwant.hidden, items.uid
from ownwant
join items on items.uid=ownwant.itemid
where ownwant.userid = %(uid)s
order by ownwant.willtrade desc"""
result = doquery(sql, {'uid': self.uid})
for item in result:
sitem = items.SiteItem.create(item[4])
sitem.have = item[0]
sitem.willtrade = item[1]
sitem.want = item[2]
sitem.hidden = item[3]
ret.append(sitem)
return ret
def crop(self, userid, ip, x1, y1, x2, y2):
image_string = cStringIO.StringIO(base64.b64decode(self.image()))
im = Image.open(image_string)
cropped = im.crop((x1, y1, x2, y2))
output = cStringIO.StringIO()
cropped.save(output, format="JPEG")
img_str = output.getvalue()
output.close()
sql_image = base64.b64encode(img_str)
sql = "insert into images (tag, parent, userid, image, ip) values (%(tag)s, %(parent)s, %(userid)s, %(image)s, %(ip)s);"
doquery(sql, { 'tag': self.tag, 'userid': userid, 'ip': utility.ip_uid(ip), 'parent': self.parent, 'image': sql_image})
sql = "select last_insert_id();"
return doquery(sql)[0][0]
def __init__(self, messageid):
super(self.__class__, self).__init__(messageid)
self.tradeitemstatus = tradeitemstatus
self.tradestatus = tradestatus
self.items = []
sql = 'select * from tradelist where messageid = %(uid)s;'
result = doquery(sql, {"uid": messageid})
complete = True
for item in result:
ti = TradeItem(item[0])
ti.itemid = item[1]
ti.messageid = item[2]
ti.userid = item[3]
ti.acceptstatus = item[4]
ti.item = items.SiteItem.create(ti.itemid)
ti.user = users.SiteUser.create(users.user_by_uid(ti.userid))
self.items.append(ti)
if (ti.acceptstatus != tradeitemstatus['accepted']):
complete = False
if complete == True and self.status < tradestatus['settled_trade']:
self.status = tradestatus['complete_trade']
def history(self):
"""
Get the edit history for an item.
:return: A list of objects with the following attributes:
* uid - The edit's uid, zero filled
* itemid - The item id
* date - Date of the edit
* userid - Editing user
* ip - IP address of the editor
"""
sql = """select itemedits.uid, itemedits.itemid, itemedits.date, itemedits.userid, ip.ip
from itemedits
join ip on itemedits.ip=ip.uid
where itemid = %(uid)s
order by uid desc;"""
edits = doquery(sql, { 'uid': self.uid })
ret = list()
for edit in edits:
editobject = ItemHist(edit[0])
editobject.uid = str(editobject.uid).zfill(8)
editobject.itemid = edit[1]
editobject.date = edit[2]
editobject.userid = edit[3]
editobject.ip = edit[4]
editobject.user = users.user_by_uid(editobject.userid)
ret.append(editobject)
return ret
def __init__(self, username=None, uid=None):
try:
if username:
uid = uid_by_user(username)
if not uid:
raise NoUser(None)
sql = """select json
from user_profiles
where uid = %(uid)s; """
result = doquery(sql, {'uid': uid})
self.uid = uid
self.profile = json.loads(result[0][0])
# Delete this at some point once all data has been moved, shouldn't take long
if 'avatar' in self.profile:
logger.info('avatar fixup applied for user id {}'.format(uid))
self.new_avatar(self.profile['avatar'])
del self.profile['avatar']
self.update()
except (Warning, IndexError):
# return defaults
self.profile = dict()
self.profile['timezone'] = "America/Los_Angeles"
def collection(self):
"""
List a user's collection
:return: list of SiteItem objects
"""
ret = list()
sql = """select ownwant.own, ownwant.willtrade, ownwant.want, ownwant.hidden, items.uid
from ownwant
join items on items.uid=ownwant.itemid
where ownwant.userid = %(uid)s
order by ownwant.willtrade desc"""
result = doquery(sql, { 'uid': self.uid })
for item in result:
sitem = items.SiteItem.create(item[4])
sitem.have = item[0]
sitem.willtrade = item[1]
sitem.want = item[2]
sitem.hidden = item[3]
ret.append(sitem)
return ret
def user_by_uid(uid):
sql = "select username from users where uid = %(uid)s;"
result = doquery(sql, {'uid': uid})
try:
return result[0][0]
except IndexError:
return None
def __init__(self, username=None, uid=None):
try:
if username:
uid = uid_by_user(username)
if not uid:
raise NoUser(None)
sql = """select json
from user_profiles
where uid = %(uid)s; """
result = doquery(sql, { 'uid': uid })
self.uid = uid
self.profile = json.loads(result[0][0])
# Delete this at some point once all data has been moved, shouldn't take long
if 'avatar' in self.profile:
logger.info('avatar fixup applied for user id {}'.format(uid))
self.new_avatar(self.profile['avatar'])
del self.profile['avatar']
self.update()
except (Warning, IndexError):
# return defaults
self.profile = dict()
self.profile['timezone'] = "America/Los_Angeles"
def mwi(self):
"""
Get a count of unread messages and trades for a user
:return: (unread messages, unread trades)
"""
num_messages = 0
num_trades = 0
sql = """select uid,status from messages
where touserid = %(touid)s
order by sent desc limit 100;"""
result = doquery(sql, { 'touid': self.uid })
for item in result:
message = messages.TradeMessage.create(item[0])
if message.delete_status(self.username):
continue
read = message.read_status(self.username)
if not read:
if item[1]:
num_trades = num_trades + 1
else:
num_messages = num_messages + 1
return (num_messages, num_trades)
def authenticate(self, password):
"""
Verify a user's password
:param password: The plaintext password to verify.
:return: None
:raises AuthFail: This exception will be raised if the user cannot be logged in for any reason.
"""
sql = """select users.pwhash
from users
where users.uid = %(uid)s;"""
result = doquery(sql, { 'uid': self.uid })
try:
pwhash = result[0][0]
except IndexError:
logger.info('AuthFail for user {}: unable to find user'.format(self.username))
raise AuthFail(self.username)
if self.accesslevel == 0:
logger.info('AuthFail for user {}: account has been banned'.format(self.username))
raise AuthFail(self.username)
if not verify_pw(password, pwhash):
logger.info('AuthFail for user {}: invalid password'.format(self.username))
raise AuthFail(self.username)
logger.info('Successful password authentication for user {}'.format(self.username))
return None
def messages(self, trash=False):
"""
Get all trades and private messages for a user
:param trash: Only show deleted messages. Permanent deletion is not currently implemented.
:return: list of PrivateMessage and TradeMessage objects
"""
mwi_cache = dict()
ret = list()
sql = """select uid,status from messages
where fromuserid = %(fromuid)s or touserid = %(touid)s
order by sent desc;"""
result = doquery(sql, { 'fromuid': self.uid, 'touid': self.uid })
for item in result:
if item[1]:
message = messages.TradeMessage.create(item[0])
else:
message = messages.PrivateMessage.create(item[0])
deleted = message.delete_status(self.username)
if trash:
deleted = not deleted
if not deleted:
ret.append(message)
return ret
def setstatus(self, status):
if self.uid > 0:
self.status = status
sql = "update messages set status = %(status)s where uid = %(uid)s;"
result = doquery(sql, {"uid": self.uid, "status": status})
else:
return None
def setstatus(self, status):
if self.uid > 0:
self.status = status
sql = "update messages set status = %(status)s where uid = %(uid)s;"
result = doquery(sql, {"uid": self.uid, "status": status})
else:
return None
def __init__(self, messageid):
super(self.__class__, self).__init__(messageid)
self.tradeitemstatus = tradeitemstatus
self.tradestatus = tradestatus
self.items = []
sql = 'select * from tradelist where messageid = %(uid)s;'
result = doquery(sql, {"uid": messageid})
complete = True
for item in result:
ti = TradeItem(item[0])
ti.itemid = item[1]
ti.messageid = item[2]
ti.userid = item[3]
ti.acceptstatus = item[4]
ti.item = items.SiteItem.create(ti.itemid)
ti.user = users.SiteUser.create(users.user_by_uid(ti.userid))
self.items.append(ti)
if (ti.acceptstatus != tradeitemstatus['accepted']):
complete = False
if complete == True and self.status < tradestatus['settled_trade']:
self.status = tradestatus['complete_trade']
def authenticate(self, password):
sql = """select users.pwhash
from users
where users.uid = %(uid)s;"""
result = doquery(sql, {'uid': self.uid})
try:
pwhash = result[0][0]
except IndexError:
logger.info('AuthFail for user {}: unable to find user'.format(
self.username))
raise AuthFail(self.username)
if self.accesslevel == 0:
logger.info('AuthFail for user {}: account has been banned'.format(
self.username))
raise AuthFail(self.username)
if not verify_pw(password, pwhash):
logger.info('AuthFail for user {}: invalid password'.format(
self.username))
raise AuthFail(self.username)
logger.info('Successful authentication for user {}'.format(
self.username))
def newpassword(self, password):
logger.info('Password reset for user {}'.format(self.username))
pwhash = gen_pwhash(password)
del password
sql = "update users set pwhash = %(pwhash)s where uid = %(uid)s;"
return doquery(sql, {"uid": self.uid, "pwhash": pwhash})
def new_user(username, password, email, ip):
username = unicode(username).strip()[:200]
email = email.strip()[:200]
pwhash = gen_pwhash(password)
if len(username) == 0:
raise NoUser(0)
if len(email) < 3:
raise NoUser(0)
joined = datetime.datetime.utcnow().strftime("%Y-%m-%d %H:%M:%S")
try:
sql = "select uid from users where username = %(username)s;"
uid = doquery(sql, {'username': username})[0][0]
# user exists
return False
except (Warning, IndexError):
# user doesn't exist
pass
try:
sql = "insert into users (username, pwhash, email, joined, accesslevel) values (%(username)s, %(pwhash)s, %(email)s, %(joined)s, '1');"
result = doquery(
sql, {
'username': username,
'pwhash': pwhash,
'email': email,
'joined': joined
})
uid = doquery("select last_insert_id();")[0][0]
if not uid:
raise NoUser(username)
sql = "insert into userstat_lastseen (date, uid) values (%(lastseen)s, %(uid)s);"
result = doquery(sql, {'uid': uid, 'lastseen': joined})
except MySQLdb.Error, e:
try:
logger.info('MySQL error adding new user {} - {}: {})'.format(
username, e.args[0], e.args[1]))
raise NoUser(username)
except IndexError:
logger.info('MySQL error adding new user {} - {})'.format(
username, e))
raise NoUser(username)
def item_by_uid(uid):
sql = 'select name from items where uid = %(uid)s;'
result = doquery(sql, { 'uid': uid })
try:
return result[0][0]
except IndexError:
return
def item_search(query):
sql = 'select uid from items where name like %(query)s;'
result = doquery(sql, {'query': '%{}%'.format(query)})
ret = list()
for item in result:
ret.append(SiteItem(item[0]))
return ret
def uid_by_item(item):
sql = 'select uid from items where name = %(name)s;'
result = doquery(sql, { 'name': item })
try:
return result[0][0]
except IndexError:
return
def items(self, tag):
sql = "select itemid from itemtags where tag = %(tag)s;"
tags = doquery(sql, { 'tag': tag })
ret = list()
for tag in tags:
ret.append(SiteItem.create(tag[0]))
return ret
def image(self):
sql = 'select image from images where uid = %(uid)s;'
result = doquery(sql, { 'uid': self.uid })
try:
return result[0][0]
except IndexError:
raise NoImage(self.uid)
def add_tag(self, tag, parent=None):
logger.info('tag {} added to {}: {} '.format(tag, self.uid, self.name))
try:
self.tree.retrieve(tag)
except IndexError:
if parent:
self.tree.insert_children([tag], parent)
else:
self.tree.insert_children([tag], 'Unsorted')
try:
sql = "insert into itemtags (itemid, tag) values (%(itemid)s, %(tag)s);"
doquery(sql, { 'itemid': self.uid, 'tag': tag })
except Exception as e:
if e[0] == 1062: # ignore duplicates
pass
else:
raise
def update(self):
"""
Update the database with the object's current name as well as the modified timestamp.
"""
logger.info('item updated {}: {} '.format(self.uid, self.name))
self.name = self.name.strip()[:64]
sql = "update items set name = %(name)s, modified = %(modified)s where uid = %(uid)s;"
return doquery(sql, {"uid": self.uid, "name": self.name, "modified": datetime.datetime.utcnow().strftime("%Y-%m-%d %H:%M:%S") })
def update(self):
"""
Writes the current object state back to the database
"""
logger.info('key updated: {} '.format(self.key))
sql = "update keyvalue set value = %(value)s where keyhash = %(key)s;"
sitekey_cache = dict()
return doquery(sql, {"value": base64.b64encode(self.value), "key": utility.digest(self.key)})
def get_contribs_table():
sql = """select count(*), users.username
from users
join itemedits on itemedits.userid=users.uid
group by users.uid, itemedits.userid
order by count(*) desc limit 10;"""
result = doquery(sql)
return result;
def avatar(self):
sql = """select avatar
from user_profiles
where uid = %(uid)s; """
result = doquery(sql, { 'uid': self.uid })
try:
return result[0][0]
except IndexError:
return None
