def Newbie(count): db = gal_utils.get_db() with closing(db.cursor(buffered=True)) as cursor: cursor.execute(u'SELECT screenname, email, UNIX_TIMESTAMP(lastlogin), ' u'UNIX_TIMESTAMP(joinedon), ' u'numposts, numlogins, lastdesign, notify_of_comments, ccURI ' u'FROM gal_users WHERE numposts >= 1 ' u'ORDER BY joinedon DESC LIMIT %s', (count,)) users = [] for data in cursor: if (data is None or not isinstance(data[0], text) or not isinstance(data[1], text) or not isinstance(data[4], int) or not isinstance(data[5], int) or not isinstance(data[6], int) or not isinstance(data[7], int) or not isinstance(data[8], text)): return users user = User(data[0]) user.email = data[1] user.lastlogin = data[2] user.joinedon = data[3] user.numposts = data[4] user.numlogins = data[5] user.lastdesign = data[6] user.notify = data[7] != 0 user.ccURI = data[8] user.inGalUsers = True users.append(user) return users
def DesignbyID(design_id): db = gal_utils.get_db() with closing(db.cursor(dictionary=True, buffered=True)) as cursor: if design_id > 0: # get actual design query = Design.Query_base + u'WHERE designid=%s' cursor.execute(query, (design_id, )) else: # get latest design query = Design.Query_base + u'ORDER BY designid DESC LIMIT 1' cursor.execute(query) if cursor.rowcount != 1: return None design = Design(**cursor.fetchone()) if not design.ready4display(): return None design.tags, design.tagids = GetTags(design_id) fans = GetFans(design_id) if len(fans) > 0: design.fans = fans design.normalize() sz = GetSize(design.imagelocation) if sz is not None: design.imagesize = sz return design
def AddFave(design_id): db = gal_utils.get_db() with closing(db.cursor(buffered=True)) as cursor: cursor.execute( u'SELECT screenname, designid FROM gal_favorites WHERE ' u'screenname=%s AND designid=%s', (current_user.id, design_id)) res = cursor.fetchall() if cursor.rowcount > 0: flask.abort(400, u'Design already in favorites') cursor.execute( u'INSERT INTO gal_favorites (screenname, designid) VALUES (%s, %s)', (current_user.id, design_id)) if cursor.rowcount != 1: flask.abort(500, u'Could not add favorite') cursor.execute( 'UPDATE gal_designs SET numvotes = numvotes + 1 WHERE designid = %s', (design_id, )) if cursor.rowcount != 1: flask.abort(500, u'Could not add favorite.') ret = [] cursor.execute( u'SELECT screenname FROM gal_favorites WHERE designid=%s ' u'ORDER BY screenname', (design_id, )) rows = cursor.fetchall() for row in rows: ret.append(row[0]) return ret
def save(self): db = gal_utils.get_db() owner = current_user with closing(db.cursor(buffered=True)) as cursor: if self.designid == 0: cursor.execute( u'INSERT INTO gal_designs (owner, title, ' u'variation, tiled, ccURI, ccName, ccImage, S3, ' u'imageversion, numvotes, whenuploaded, notes) ' u'VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, NOW(), %s)', (self.owner, self.title, self.variation, self.tiled, self.ccURI, self.ccName, self.ccImage, u'Y' if self.S3 else u'N', self.imageversion, self.numvotes, self.notes)) self.designid = cursor.lastrowid if cursor.rowcount == 1: owner.numposts += 1 else: cursor.execute( u'UPDATE gal_designs SET title=%s, variation=%s, ' u'tiled=%s, ccURI=%s, ccName=%s, ccImage=%s, S3=%s, ' u'notes=%s WHERE designid=%s', (self.title, self.variation, self.tiled, self.ccURI, self.ccName, self.ccImage, u'Y' if self.S3 else u'N', self.notes, self.designid)) if cursor.rowcount == 1: owner.ccURI = self.ccURI owner.save() return self.designid return None
def UsersByPosts(ascending, start, num): direction = u'ASC' if ascending else 'DESC' query = MiniUser.Query_base + u'ORDER BY numposts ' + direction + u', screenname LIMIT %s,%s' db = gal_utils.get_db() with closing(db.cursor(buffered=True)) as cursor: cursor.execute(query, (start,num)) return complete(cursor)
def AllTags(): db = gal_utils.get_db() with closing(db.cursor(dictionary=True, buffered=True)) as cursor: cursor.execute( u'SELECT name, count FROM gal_tag_names WHERE count>0 ORDER BY name' ) data = cursor.fetchall() if data is None: flask.abort(500, u'Failed to get tags') return data
def DesignByRandomPopular(seed, start, num, ccOnly): db = gal_utils.get_db() if ccOnly: query = (Design.Query_base + u'WHERE numvotes > 7 AND ' + Design.Query_CC + u' ORDER BY RAND(%s) LIMIT %s,%s') else: query = Design.Query_base + u'WHERE numvotes > 7 ORDER BY RAND(%s) LIMIT %s,%s' with closing(db.cursor(dictionary=True, buffered=True)) as cursor: cursor.execute(query, (seed, start, num)) return complete(cursor)
def DesignByTitle(start, num, ccOnly): db = gal_utils.get_db() if ccOnly: query = (Design.Query_base + u'WHERE ' + Design.Query_CC + u' ORDER BY title LIMIT %s,%s') else: query = Design.Query_base + u'ORDER BY title LIMIT %s,%s' with closing(db.cursor(dictionary=True, buffered=True)) as cursor: cursor.execute(query, (start, num)) return complete(cursor)
def DeleteTags(tagids, design_id): db = gal_utils.get_db() with closing(db.cursor(buffered=True)) as cursor: for tagid in tagids: cursor.execute(u'DELETE FROM gal_tags WHERE item=%s AND tag=%s', (design_id, tagid)) if cursor.rowcount > 0: cursor.execute( u'UPDATE gal_tag_names SET count=count-1 ' u'WHERE id=%s AND count>0', (tagid, ))
def DeleteComment(commentid): if not isinstance(commentid, int) or commentid < 1: flask.abort(400, u'Bad comment id') db = gal_utils.get_db() with closing(db.cursor(buffered=True)) as cursor: cursor.execute( u"DELETE FROM gal_comments WHERE screenname=%s AND commentid=%s", (current_user.id, commentid)) if cursor.rowcount != 1: flask.abort(403, u'Cannot delete comment')
def GetFans(design_id): db = gal_utils.get_db() with closing(db.cursor(buffered=True)) as cursor: cursor.execute( u'SELECT screenname FROM gal_favorites WHERE designid=%s ' u'ORDER BY screenname', (design_id, )) fans = [] rows = cursor.fetchall() for row in rows: fans.append(row[0]) return fans
def NewerDesigns(design_id): if design_id == 0: return 0 db = gal_utils.get_db() with closing(db.cursor(buffered=True)) as cursor: cursor.execute(u'SELECT count(*) FROM gal_designs WHERE designid > %s', (design_id, )) datum = cursor.fetchone() if datum is None or type(datum[0]) is not int: flask.abort(400, u'Bad request.') return datum[0]
def UpdateComment(commentid, newComment): if not isinstance(commentid, int) or commentid < 1: flask.abort(400, u'Bad comment id') db = gal_utils.get_db() with closing(db.cursor(buffered=True)) as cursor: cursor.execute( u"UPDATE gal_comments SET comment = %s WHERE screenname=%s AND commentid=%s", (newComment, current_user.id, commentid)) if cursor.rowcount != 1: flask.abort(403, u'Cannot update comment') return CommentById(commentid)
def DesignByDate(oldest, start, num, ccOnly): db = gal_utils.get_db() if ccOnly: query = (Design.Query_base + u'WHERE ' + Design.Query_CC + (u'ORDER BY whenuploaded LIMIT %s,%s' if oldest else u'ORDER BY whenuploaded DESC LIMIT %s,%s')) else: query = Design.Query_base + (u'ORDER BY whenuploaded LIMIT %s,%s' if oldest else u'ORDER BY whenuploaded DESC LIMIT %s,%s') with closing(db.cursor(dictionary=True, buffered=True)) as cursor: cursor.execute(query, (start, num)) return complete(cursor)
def DesignByPopularity(start, num, ccOnly): db = gal_utils.get_db() if ccOnly: query = ( Design.Query_base + u'WHERE ' + Design.Query_CC + u' AND numvotes>0 ORDER BY numvotes DESC, whenuploaded DESC LIMIT %s,%s' ) else: query = (Design.Query_base + u'WHERE numvotes>0 ORDER BY numvotes DESC, ' u'whenuploaded DESC LIMIT %s,%s') with closing(db.cursor(dictionary=True, buffered=True)) as cursor: cursor.execute(query, (start, num)) return complete(cursor)
def GetTags(design_id): db = gal_utils.get_db() with closing(db.cursor(buffered=True)) as cursor: tags = [] tagids = [] cursor.execute( u'SELECT n.name, n.id FROM gal_tags AS t, gal_tag_names AS n WHERE t.item=%s ' u'AND t.tag=n.id', (design_id, )) if cursor.rowcount > 0: rows = cursor.fetchall() for row in rows: tags.append(row[0]) tagids.append(row[1]) return (tags, tagids)
def CreateComment(designid, newComment): if not isinstance(designid, int) or designid < 1: flask.abort(400, u'Bad design id') db = gal_utils.get_db() with closing(db.cursor(buffered=True)) as cursor: cursor.execute( u'INSERT INTO gal_comments (screenname, designid, whenposted, comment) ' u'VALUES (%s, %s, NOW(), %s)', (current_user.id, designid, newComment)) commentid = cursor.lastrowid if cursor.rowcount != 1: flask.abort(403, u'Cannot add comment') return CommentById(commentid)
def archive(self): db = gal_utils.get_db() with closing(db.cursor(buffered=True)) as cursor: cursor.execute(u'SELECT S3 FROM gal_designs WHERE designid=%s', (self.designid, )) if cursor.rowcount != 1: return False data = cursor.fetchone() if not isinstance(data[0], text): return False if data[0] == u'Y': return True cursor.execute( u'UPDATE gal_designs SET S3 = "Y" WHERE designid=%s', (self.designid, )) return cursor.rowcount == 1
def DesignByDesigner(name, start, num, ccOnly): if not gal_utils.legalOwner(name): flask.abort(400, u'Bad request.') db = gal_utils.get_db() if ccOnly: query = (Design.Query_base + u'WHERE owner=%s AND ' + Design.Query_CC + u' ORDER BY whenuploaded DESC LIMIT %s,%s') else: query = (Design.Query_base + u'WHERE owner=%s ORDER BY whenuploaded ' u'DESC LIMIT %s,%s') with closing(db.cursor(dictionary=True, buffered=True)) as cursor: cursor.execute(query, (name, start, num)) return complete(cursor)
def CommentById(commentid): if not isinstance(commentid, int) or commentid < 1: flask.abort(400, u'Bad comment id') db = gal_utils.get_db() with closing(db.cursor(dictionary=True, buffered=True)) as cursor: cursor.execute(Comment.Query_base + u'WHERE commentid=%s', (commentid, )) if cursor.rowcount != 1: flask.abort(404, u'Comment not in database') row = cursor.fetchone() comment = Comment(**row) comment.normalize() return comment
def get(username): if not gal_utils.legalOwner(username): return None db = gal_utils.get_db() with closing(db.cursor(buffered=True)) as cursor: cursor.execute(u'SELECT user_password, user_rank, user_email FROM phpbb3_users ' u'WHERE username=%s', (username,)) data = cursor.fetchone() if data is None or len(data) < 3 or not isinstance(data[1], int): return None if not isinstance(data[0], text) and not isinstance(data[0], bytearray): return None if not isinstance(data[2], text) and not isinstance(data[2], bytearray): return None user = User(username) if hasattr(data[0], 'decode'): user.password_hash = data[0].decode('utf-8') else: user.password_hash = data[0] user.is_admin = data[1] == 1 user.is_tagger = username in ['MtnViewJohn', 'MtnViewMark', 'kipling', 'Guigui'] if hasattr(data[2], 'decode'): user.email = data[2].decode('utf-8') else: user.email = data[2] cursor.execute(u'SELECT UNIX_TIMESTAMP(lastlogin), UNIX_TIMESTAMP(joinedon), ' u'numposts, numlogins, lastdesign, notify_of_comments, ccURI ' u'FROM gal_users WHERE screenname=%s', (username,)) data = cursor.fetchone() if data is None or len(data) < 7 or not isinstance(data[0], int) or \ not isinstance(data[1], int) or not isinstance(data[2], int) or \ not isinstance(data[3], int) or not isinstance(data[4], int) or \ not isinstance(data[5], int) or not isinstance(data[6], text): return user user.lastlogin = data[0] user.joinedon = data[1] user.numposts = data[2] user.numlogins = data[3] user.lastdesign = data[4] user.notify = data[5] != 0 user.ccURI = data[6] user.inGalUsers = True return user
def AddTags(tagnames, design_id): db = gal_utils.get_db() with closing(db.cursor(buffered=True)) as cursor: for tagname in tagnames: cursor.execute( u'INSERT INTO gal_tag_names (name,count) VALUES (%s,1) ' u'ON DUPLICATE KEY UPDATE count=count+1', (tagname, )) tagid = cursor.lastrowid if tagid == 0: flask.abort(500, u'Failed to add tag:' + tagname) cursor.execute( u'INSERT into gal_tags (item,tag,user,type) VALUES (%s,%s,"","")', (design_id, tagid)) if cursor.lastrowid == 0: flask.abort(500, u'Failed to add tag:' + tagname)
def CountByTitle(title, ccOnly): if not isinstance(title, text) or len(title) == 0 or len(title) > 100: flask.abort(400, u'Bad request.') db = gal_utils.get_db() if ccOnly: query = ( u'SELECT count(*) FROM gal_designs WHERE STRCMP(%s, title) > 0 AND ' + Design.Query_CC) else: query = u'SELECT count(*) FROM gal_designs WHERE STRCMP(%s, title) > 0' with closing(db.cursor(buffered=True)) as cursor: cursor.execute(query, (title, )) datum = cursor.fetchone() if datum is None or type(datum[0]) is not int: flask.abort(400, u'Bad request.') return datum[0]
def GetSize(path): if isinstance(path, int): db = gal_utils.get_db() with closing(db.cursor(buffered=True)) as cursor: cursor.execute( u'SELECT imagelocation FROM gal_designs WHERE designid=%s', (path, )) if cursor.rowcount != 1: return None path = cursor.fetchone()[0] try: im = Image.open(os.path.join(This_dir, path)) if im is not None: width, height = im.size return {'width': width, 'height': height} except: return None
def DesignFavorites(name, start, num, ccOnly): if not gal_utils.legalOwner(name): flask.abort(400, u'Bad request.') db = gal_utils.get_db() if ccOnly: query = ( Design.Query_base_d + ', gal_favorites AS f WHERE ' + Design.Query_CC + ' AND f.screenname=%s AND f.designid = d.designid ORDER BY d.designid ' 'DESC LIMIT %s,%s') else: query = ( Design.Query_base_d + ', gal_favorites AS f WHERE ' 'f.screenname=%s AND f.designid = d.designid ORDER BY d.designid ' 'DESC LIMIT %s,%s') with closing(db.cursor(dictionary=True, buffered=True)) as cursor: cursor.execute(query, (name, start, num)) return complete(cursor)
def CommentsByDesign(designid): if not isinstance(designid, int) or designid < 1: flask.abort(400, u'Bad design id') db = gal_utils.get_db() with closing(db.cursor(dictionary=True, buffered=True)) as cursor: cursor.execute( Comment.Query_base + u'WHERE designid=%s ORDER BY whenposted', (designid, )) if cursor.rowcount == 0: return [] rows = cursor.fetchall() ret = [] for row in rows: comment = Comment(**row) comment.normalize() ret.append(comment) return ret
def DesignTagged(tag, start, num, ccOnly): db = gal_utils.get_db() with closing(db.cursor(buffered=True)) as cursor: cursor.execute(u'SELECT id FROM gal_tag_names WHERE name=%s', (tag, )) datum = cursor.fetchone() if datum is None or type(datum[0]) is not int: flask.abort(400, u'Bad request.') tagid = datum[0] if ccOnly: query = (Design.Query_base_d + ', gal_tags AS t WHERE ' + Design.Query_CC + ' AND t.tag=%s AND t.item = d.designid ORDER BY d.designid ' 'DESC LIMIT %s,%s') else: query = (Design.Query_base_d + ', gal_tags AS t WHERE ' 't.tag=%s AND t.item = d.designid ORDER BY d.designid ' 'DESC LIMIT %s,%s') with closing(db.cursor(dictionary=True, buffered=True)) as cursor: cursor.execute(query, (tagid, start, num)) return complete(cursor)
def DeleteDesign(design_id): db = gal_utils.get_db() with closing(db.cursor(dictionary=True, buffered=True)) as cursor: query = Design.Query_base + u'WHERE designid=%s' cursor.execute(query, (design_id, )) if cursor.rowcount != 1: return flask.abort(404, u'Cannot find design to delete.') design = Design(**cursor.fetchone()) if not gal_utils.validateOwner(design.owner): flask.abort(401, u'Unauthorized to delete this design.') # TODO update tag counts cursor.execute(u'DELETE FROM gal_designs WHERE designid=%s', (design_id, )) if cursor.rowcount != 1: flask.abort(500, u'Design failed to be deleted.') cursor.execute(u'DELETE FROM gal_comments WHERE designid=%s', (design_id, )) cursor.execute(u'DELETE FROM gal_favorites WHERE designid=%s', (design_id, )) cursor.execute( u'UPDATE gal_users SET numposts = numposts - 1 WHERE screenname=%s', (design.owner, )) files = [ design.filelocation, design.imagelocation, design.thumblocation, design.sm_thumblocation ] for file in files: if os.path.isfile(file): try: os.unlink(file) except OSError: pass
def DeleteFave(design_id): db = gal_utils.get_db() with closing(db.cursor(buffered=True)) as cursor: cursor.execute( u'DELETE FROM gal_favorites WHERE screenname=%s AND designid=%s', (current_user.id, design_id)) if cursor.rowcount != 1: flask.abort(500, u'Could not delete favorite') cursor.execute( 'UPDATE gal_designs SET numvotes = numvotes - 1 WHERE designid = %s', (design_id, )) if cursor.rowcount != 1: flask.abort(500, u'Could not delete favorite.') ret = [] cursor.execute( u'SELECT screenname FROM gal_favorites WHERE designid=%s ' u'ORDER BY screenname', (design_id, )) rows = cursor.fetchall() for row in rows: ret.append(row[0]) return ret
def UnaddDesign(design_id): db = gal_utils.get_db() with closing(db.cursor(dictionary=True, buffered=True)) as cursor: query = Design.Query_base + u'WHERE designid=%s' cursor.execute(query, (design_id, )) if cursor.rowcount != 1: return designdata = cursor.fetchone() cursor.execute(u'DELETE FROM gal_designs WHERE designid=%s', (design_id, )) if cursor.rowcount != 1: return cursor.execute(u'DELETE FROM gal_comments WHERE designid=%s', (design_id, )) cursor.execute(u'DELETE FROM gal_favorites WHERE designid=%s', (design_id, )) cursor.execute( u'UPDATE gal_users SET numposts = numposts - 1 WHERE screenname=%s', (designdata['owner'], )) files = [] if 'filelocation' in designdata: files.append(designdata['filelocation']) if 'imagelocation' in designdata: files.append(designdata['imagelocation']) if 'thumblocation' in designdata: files.append(designdata['thumblocation']) if 'sm_thumblocation' in designdata: files.append(designdata['sm_thumblocation']) for file in files: if os.path.isfile(file): try: os.unlink(file) except OSError: pass