def remove_listing_from_category(lid, cat_id):
sql = """\
DELETE FROM listing_category
WHERE lid = ? AND
cat_id = ?"""
with db.DatabaseCursor() as cursor:
cursor.execute(sql, (lid, cat_id))
def listings_count(query):
sql = """\
SELECT COUNT(*) AS count FROM listing_search
WHERE content MATCH ?"""
with db.DatabaseCursor() as cursor:
row = cursor.execute(sql, (query + '*', )).fetchone()
return row['count']
def get(self, key, user):
with db.DatabaseCursor() as cursor:
cursor.execute(
'SELECT * FROM `objects` WHERE `key` = %s AND `user` = %s AND `uploading` = %s',
(key, user, False))
items = list(cursor.fetchall())
if len(items) != 1:
return None
try:
content = self._retrieveKey(items[0]['key'], 'pri')
except Exception as e:
self.log.log(msg='Primary retrieval for %s failed: %s' %
(items[0]['id'], str(e)),
context='GET')
try:
content = self._retrieveKey(items[0]['key'], 'sec')
except Exception as e:
self.log.log(msg='Secondary retrieval for %s failed: %s' %
(items[0]['id'], str(e)),
context='GET')
raise
return {
'name': items[0]['name'],
'content': base64.b64encode(content).decode('ascii'),
'mimeType': items[0]['mimeType']
}
def update_existing_user(user_id, name, bio):
sql = """\
UPDATE user
SET name = ?, bio = ?
WHERE uid = ?"""
with db.DatabaseCursor() as cursor:
cursor.execute(sql, (name, bio, user_id))
def update_password(uid, new_password):
password_hash = get_password_hash(new_password)
sql = """\
UPDATE user
SET password_hash = ?
WHERE uid = ?"""
with db.DatabaseCursor() as cursor:
cursor.execute(sql, (password_hash, uid))
def get_comment_info(comment_id):
sql = """\
SELECT *
FROM comment
WHERE cid = ?"""
with db.DatabaseCursor() as cursor:
obj = cursor.execute(sql, (comment_id,)).fetchone()
return obj
def get_listing_info(listing_id):
sql = """\
SELECT *
FROM listing
WHERE lid = ?"""
with db.DatabaseCursor() as cursor:
obj = cursor.execute(sql, (listing_id, )).fetchone()
return obj
def create_category(cat_label):
sql = """\
INSERT INTO category (label)
VALUES (?)"""
with db.DatabaseCursor() as cursor:
cursor.execute(sql, (cat_label, ))
new_id = cursor.lastrowid
return new_id
def create_or_retrieve_category(label):
sql = """\
SELECT cat_id FROM category WHERE label = ?"""
with db.DatabaseCursor() as cursor:
obj = cursor.execute(sql, (label.upper(), )).fetchone()
if obj and obj['cat_id']:
return obj['cat_id']
return create_category(label.upper())
def update_existing_listing(listing_id, title, description):
sql = """\
UPDATE listing
SET title = ?, description = ?, last_update_time = ?
WHERE lid = ?"""
with db.DatabaseCursor() as cursor:
now = datetime.datetime.now()
cursor.execute(sql, (title, description, now, listing_id))
def cat_ids_to_labels(cat_ids):
sql = """\
SELECT label
FROM category
WHERE cat_id IN (%s)
""" % ','.join('?' * len(cat_ids))
with db.DatabaseCursor() as cursor:
rows = cursor.execute(sql, cat_ids).fetchall()
return [x['label'] for x in rows]
def listing_categories(lid):
sql = """\
SELECT c.cat_id, c.label
FROM listing_category l, category c
WHERE l.lid = ? AND
l.cat_id = c.cat_id"""
with db.DatabaseCursor() as cursor:
rows = cursor.execute(sql, (lid, )).fetchall()
return rows
def get_number_of_listings_in_cat_ids(cat_ids):
sql = """\
SELECT COUNT(*) as count
FROM listing l, listing_category c
WHERE l.lid = c.lid AND
c.cat_id IN (%s)
""" % ','.join('?' * len(cat_ids))
with db.DatabaseCursor() as cursor:
row = cursor.execute(sql, cat_ids).fetchone()
return row['count']
def listings(query, limit, offset=0):
"""Returns a list of listing_ids matching the query."""
sql = """\
SELECT lid FROM listing_search
WHERE content MATCH ?
LIMIT ? OFFSET ?"""
with db.DatabaseCursor() as cursor:
matching_listings = \
cursor.execute(sql, (query+'*', limit, offset)).fetchall()
return [x['lid'] for x in matching_listings]
def maybe_populate_virtual_table():
if has_created():
return
# Populate fts table with existing data.
sql = """\
INSERT INTO listing_search(lid, content)
SELECT lid, title || " " || description
FROM listing"""
with db.DatabaseCursor() as cursor:
cursor.execute(sql)
def create_new_user(name, email, password):
password_hash = get_password_hash(password)
sql = """\
INSERT INTO user (name, email, password_hash)
VALUES (?, ?, ?)"""
with db.DatabaseCursor() as cursor:
cursor.execute(sql, (name, email, password_hash))
new_user_id = cursor.lastrowid
return new_user_id
def get_comments_for_listing(lid, time_ordered=True):
sql = """\
SELECT *
FROM listing l, comment c, user u
WHERE l.lid = c.lid AND
l.lid = ? AND
c.uid = u.uid"""
if not time_ordered:
sql += " ORDER BY cid DESC"
with db.DatabaseCursor() as cursor:
rows = cursor.execute(sql, (lid,)).fetchall()
return rows
def verify_login(email, password):
sql = "SELECT uid, password_hash FROM user WHERE email = ?"
with db.DatabaseCursor() as cursor:
user = cursor.execute(sql, (email, )).fetchone()
if user is None:
return False
if check_password(password, user['password_hash']):
return user['uid']
return False
def get_lids_by_cat_ids(cat_ids, limit, offset=0):
sql = """\
SELECT l.lid
FROM listing l, listing_category c
WHERE l.lid = c.lid AND
c.cat_id IN (%s)
ORDER BY l.lid DESC
LIMIT ? OFFSET ?
""" % ','.join('?' * len(cat_ids))
with db.DatabaseCursor() as cursor:
cat_ids.extend([limit, offset])
obj = cursor.execute(sql, cat_ids).fetchall()
return [x['lid'] for x in obj]
def filter_lids_by_cat_ids(lids, cat_ids):
sql = """\
SELECT l.lid
FROM listing l, listing_category c
WHERE l.lid = c.lid AND
c.cat_id IN (%s) AND
l.lid IN (%s)
ORDER BY l.lid DESC
""" % (','.join('?' * len(cat_ids)), ','.join('?' * len(lids)))
with db.DatabaseCursor() as cursor:
cat_ids.extend(lids)
obj = cursor.execute(sql, cat_ids).fetchall()
return [x['lid'] for x in obj]
def create_new_listing(title, description, owner_id):
# Check that owner_id exists.
if not model.user.get_user_info(owner_id):
raise Exception('No such user.')
sql = """\
INSERT INTO listing (title, description, owner_id)
VALUES (?, ?, ?)"""
with db.DatabaseCursor() as cursor:
cursor.execute(sql, (title, description, owner_id))
new_id = cursor.lastrowid
return new_id
def _generateKey(self):
while True:
with db.DatabaseCursor() as cursor:
key = ''.join(
random.choice(string.ascii_letters + string.digits)
for x in range(256))
try:
cursor.execute(
'INSERT INTO `objects` (`key`, `uploading`) VALUES (%s, %s)',
(key, True))
id = cursor.lastrowid()
break
except Exception as e:
pass
return (id, key)
def receiveFile(self, name, content, mimeType):
(id, key) = self._generateKey()
try:
self._storeKey(key, content, 'pri')
with db.DatabaseCursor() as cursor:
cursor.execute(
'UPDATE `objects` SET `name` = %s, `mimeType` = %s, `uploading` = %s, `pri` = %s WHERE `key` = %s',
(name, mimeType, False, True, key))
except Exception as e:
self.log.log(msg='Primary storage for %s failed: %s' %
(id, str(e)),
context='RECV')
try:
self._storeKey(key, content, 'sec')
with db.DatabaseCursor() as cursor:
cursor.execute(
'UPDATE `objects` SET `name` = %s, `mimeType` = %s, `uploading` = %s, `sec` = %s WHERE `key` = %s',
(name, mimeType, False, True, key))
except Exception as e:
self.log.log(msg='Secondary storage for %s failed: %s' %
(id, str(e)),
context='RECV')
return None
return key
def get_listings_for_user(uid):
sql = """\
SELECT *
FROM listing l, user u, (
SELECT l.lid, COUNT(c.cid) AS comment_count
FROM listing l
LEFT JOIN comment c
ON l.lid = c.lid
GROUP BY l.lid) c
WHERE l.owner_id = u.uid AND
c.lid = l.lid AND
l.owner_id = ?
ORDER BY l.lid DESC"""
with db.DatabaseCursor() as cursor:
obj = cursor.execute(sql, (uid, )).fetchall()
return obj
def get_listings_info(listing_ids):
sql = """\
SELECT *
FROM listing l, user u, (
SELECT l.lid, COUNT(c.cid) AS comment_count
FROM listing l
LEFT JOIN comment c
ON l.lid = c.lid
GROUP BY l.lid) c
WHERE l.lid IN (%s) AND
c.lid = l.lid AND
l.owner_id = u.uid
""" % ','.join('?' * len(listing_ids))
with db.DatabaseCursor() as cursor:
rows = cursor.execute(sql, listing_ids).fetchall()
return rows
def get_latest_listings(limit, offset=0):
sql = """\
SELECT *
FROM listing l, user u, (
SELECT l.lid, COUNT(c.cid) AS comment_count
FROM listing l
LEFT JOIN comment c
ON l.lid = c.lid
GROUP BY l.lid) c
WHERE l.owner_id = u.uid AND
c.lid = l.lid
ORDER BY l.lid DESC
LIMIT ? OFFSET ?"""
with db.DatabaseCursor() as cursor:
obj = cursor.execute(sql, (limit, offset)).fetchall()
return obj
def create_new_comment(body, uid, lid):
# Check that uid exists.
if not model.user.get_user_info(uid):
raise Exception('No such user.')
# Check that listing exists.
if not model.listing.get_listing_info(lid):
raise Exception('No such listing.')
sql = """\
INSERT INTO comment (body, uid, lid)
VALUES (?, ?, ?)"""
with db.DatabaseCursor() as cursor:
cursor.execute(sql, (body, uid, lid))
new_id = cursor.lastrowid
return new_id
def get_related_listings(listing_id, limit, offset=0):
# TODO(michael): tmp implementation.
sql = """\
SELECT *
FROM listing l, user u, (
SELECT l.lid, COUNT(c.cid) AS comment_count
FROM listing l
LEFT JOIN comment c
ON l.lid = c.lid
GROUP BY l.lid) c
WHERE l.owner_id = u.uid AND
c.lid = l.lid AND
l.owner_id = (
SELECT owner_id FROM listing
WHERE lid = ?) AND
l.lid <> ?
ORDER BY l.lid DESC
LIMIT ? OFFSET ?"""
with db.DatabaseCursor() as cursor:
obj = cursor.execute(
sql, (listing_id, listing_id, limit, offset)).fetchall()
return obj
def get_number_of_users():
sql = "SELECT COUNT(*) as count FROM user"
with db.DatabaseCursor() as cursor:
row = cursor.execute(sql).fetchone()
return row['count']
def get_all_users():
sql = "SELECT u.uid, u.name, u.email FROM user AS u"
with db.DatabaseCursor() as cursor:
rows = cursor.execute(sql).fetchall()
return rows
