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