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
