def get_listings_under_owner(owner_id):
with DatabaseCursor() as cursor:
current_app.logger.info(
"Getting listings under user with ID {}".format(owner_id))
cursor.execute('select * from listing where owner_id = %s',
(owner_id, ))
return cursor.fetchall()
def get_loans_under_bidder(bidder_id):
with DatabaseCursor() as cursor:
current_app.logger.info(
"Getting bid with bid ID {} from database".format(bidder_id))
cursor.execute('select * from loan where bidder_id = %s;',
(bidder_id, ))
return cursor.fetchall()
def get_user_by_username(username):
with DatabaseCursor() as cursor:
current_app.logger.info(
"Getting user with username {} from database".format(username))
cursor.execute('select * from users where username = %s;',
(username, ))
return cursor.fetchone()
def delete_listing(listing_name, owner_id):
with DatabaseCursor() as cursor:
current_app.logger.info("Deleting listing {} {} from database".format(
listing_name, owner_id))
cursor.execute(
"delete from listing where listing_name = %s and owner_id = %s",
(listing_name, owner_id))
def get_listings_owner_id():
with DatabaseCursor() as cursor:
cursor.execute('''select *
from listing l
order by l.owner_id''')
result=cursor.fetchall()
return result
def get_listings_by_all(all_entries):
with DatabaseCursor() as cursor:
current_app.logger.info(
"Getting listings under all entries with query {}".format(
all_entries))
cursor.execute(
"SELECT l.listing_name, u.name AS user_name, l.description, l.listed_date, "
"tag.name AS tag_name, l.owner_id, u.username FROM listing l "
"LEFT JOIN listing_tag lt ON l.listing_name = lt.listing_name AND l.owner_id = lt.owner_id "
"LEFT JOIN tag AS tag ON lt.tag_id = tag.tag_id "
"LEFT JOIN users AS u ON l.owner_id = u.id "
"WHERE l.owner_id IN (SELECT u2.id FROM users u2 WHERE LOWER(name) "
"LIKE LOWER(%s) OR LOWER(username) LIKE LOWER(%s)) "
"OR l.listing_name IN (SELECT lt2.listing_name FROM listing_tag lt2 "
"WHERE tag_id IN (SELECT tag2.tag_id FROM tag tag2 WHERE LOWER(name) LIKE LOWER(%s))) "
"AND l.owner_id IN (SELECT lt2.owner_id FROM listing_tag lt2 "
"WHERE tag_id IN (SELECT tag2.tag_id FROM tag tag2 WHERE LOWER(name) LIKE LOWER(%s))) "
"OR LOWER(l.listing_name) LIKE LOWER(%s) OR LOWER(l.description) LIKE LOWER(%s) "
"GROUP BY l.listing_name, u.name, l.description, l.listed_date,"
" tag.name, l.owner_id, u.username "
"ORDER BY listed_date DESC;", (
all_entries,
all_entries,
all_entries,
all_entries,
all_entries,
all_entries,
))
return cursor.fetchall()
def insert_listing_tag(tag_id, listing_name, owner_id):
try:
with DatabaseCursor() as cursor:
cursor.execute('INSERT INTO listing_tag VALUES(%s, %s, %s);', (tag_id, listing_name, owner_id))
return True
except psycopg2.IntegrityError:
current_app.logger.error("listing_tag insertion failed: [{}]".format(listing_name))
return False
def get_listings_by_owner_name(owner_name):
with DatabaseCursor() as cursor:
current_app.logger.info(
"Getting listings under user with name {}".format(owner_name))
cursor.execute(
'SELECT * FROM listing WHERE owner_id IN (SELECT id FROM users WHERE name = %s)',
(owner_name, ))
return cursor.fetchall()
def delete_bid(bidder_id, listing_name, owner_id):
with DatabaseCursor() as cursor:
current_app.logger.info(
"Deleting bid {} of listing {} and owner {} from database".format(
bidder_id, listing_name, owner_id))
cursor.execute(
"delete from bid where bidder_id = %s and listing_name = %s and owner_id = %s",
(bidder_id, listing_name, owner_id))
def get_popular_listings():
with DatabaseCursor() as cursor:
cursor.execute('''select l.listing_name, l.owner_id, count(*)
from listing l, bid b
where l.listing_name = b.listing_name and l.owner_id = b.owner_id and l.is_available = 'true'
group by l.listing_name, l.owner_id
order by count(*) desc
limit 5''')
return cursor.fetchall()
def get_expensive_listings():
with DatabaseCursor() as cursor:
cursor.execute('''select l.listing_name, l.owner_id , max(price)
from listing l , bid b
where l.listing_name = b.listing_name and l.owner_id = b.owner_id and l.is_available = 'true'
group by l.listing_name, l.owner_id
order by max(price) desc
limit 5''')
return cursor.fetchall()
def get_most_common_tag_of_owner(owner_name):
with DatabaseCursor() as cursor:
current_app.logger.info("Getting most common tag name of owner name {} from database".format(owner_name))
cursor.execute("SELECT tag.name AS tag_name, COUNT(lt.tag_id) AS count FROM listing_tag lt "
"LEFT JOIN tag AS tag ON lt.tag_id = tag.tag_id "
"WHERE lt.owner_id = (SELECT u.id FROM users u WHERE u.username = %s) "
"GROUP BY tag.name ORDER BY count DESC LIMIT 1;",
(owner_name,))
return cursor.fetchone()
def get_listing(listing_name, owner_id):
with DatabaseCursor() as cursor:
current_app.logger.info(
"Getting listing {} from owner {} from database".format(
listing_name, owner_id))
cursor.execute(
'select * from listing where listing_name = %s and owner_id = %s;',
(listing_name, owner_id))
return cursor.fetchone()
def get_bid_under_listing_and_bidder(bidder_id, listing_name, owner_id):
with DatabaseCursor() as cursor:
current_app.logger.info(
"Getting bid with listing {}, owner {} and bidder {} from database"
.format(listing_name, owner_id, bidder_id))
cursor.execute(
'select * from bid where listing_name = %s and owner_id = %s and bidder_id = %s;',
(listing_name, owner_id, bidder_id))
return cursor.fetchone()
def insert_tag(name):
try:
with DatabaseCursor() as cursor:
cursor.execute('INSERT INTO tag VALUES(DEFAULT, %s);', name)
current_app.logger.info("Tag added to database: [{}]"
.format(name))
return True
except psycopg2.IntegrityError:
current_app.logger.error("INSERTION FAILED: [{}]".format(name))
return False
def get_listings_with_tags(owner_id):
with DatabaseCursor() as cursor:
cursor.execute(
'''select l.listing_name, l.owner_id, l.description, lt.tag_id, tag.name AS tag_name,
l.is_available, l.listed_date from listing l left join listing_tag lt on l.listing_name = lt.listing_name
and l.owner_id = lt.owner_id left join tag tag on lt.tag_id = tag.tag_id where l.owner_id = %s
group by l.listing_name, l.owner_id, lt.tag_id, tag.name
order by l.listing_name asc, l.owner_id asc''',
(owner_id, ))
return cursor.fetchall()
def update_user_password(username, password):
try:
with DatabaseCursor() as cursor:
cursor.execute(
'UPDATE users SET password_hash = %s WHERE username = %s',
(password, username))
return True
except (Exception, psycopg2.DatabaseError) as error:
current_app.logger.error(
"User password change failed: [{}]".format(error))
return False
def update_user_info(username, name, email, phone_no=None):
try:
with DatabaseCursor() as cursor:
cursor.execute(
'UPDATE users SET email = %s, name = %s, phone_no = %s WHERE username = %s',
(email, name, phone_no, username))
return True
except psycopg2.IntegrityError:
current_app.logger.info("UPDATE FAILED: [{}, {}, {}, {}, {}]".format(
username, email, name, password, phone_no))
return False
def update_tag(tag_id, name):
try:
with DatabaseCursor() as cursor:
cursor.execute('UPDATE tag SET name = %s'
'where tag_id = %s;',
(name, tag_id))
current_app.logger.info("Tag {} updated: [{}]"
.format(tag_id, name))
return True
except psycopg2.Error:
current_app.logger.error("UPDATE FAILED: [{}, {}]".format(tag_id, name))
return False
def insert_user(username, email, name, password, phone_no=None):
try:
with DatabaseCursor() as cursor:
cursor.execute('INSERT INTO users VALUES(DEFAULT, %s, %s, %s, %s, %s);', (username, email, name, password,
phone_no))
current_app.logger.info("User added to database: [{}, {}, {}, {}, {}]".format(username, email, name,
password, phone_no))
return True
except psycopg2.IntegrityError:
current_app.logger.info("INSERTION FAILED: [{}, {}, {}, {}, {}]".format(username, email, name, password,
phone_no))
return False
def update_listing(listing_name, owner_id, description):
try:
with DatabaseCursor() as cursor:
cursor.execute(
'UPDATE listing SET description = %s where listing_name = %s and owner_id = %s;',
(description, listing_name, owner_id))
current_app.logger.info(
"Listing ('{}') updated.".format(listing_name))
return True
except (Exception, psycopg2.DatabaseError) as error:
current_app.logger.error("Listing update failed: [{}]".format(error))
return False
def update_bid(bidder_id, listing_name, owner_id, bid_date, price):
try:
with DatabaseCursor() as cursor:
cursor.execute(
'UPDATE bid SET price = %s '
'where bidder_id = %s and listing_name = %s and owner_id = %s and bid_date = %s;',
(price, bidder_id, listing_name, owner_id, bid_date))
current_app.logger.info("Bid {} updated: [{}, {}, {}, {}]".format(
bidder_id, listing_name, owner_id, bid_date, price))
return True
except psycopg2.Error:
current_app.logger.error("UPDATE FAILED: [{}, {}, {}, {}, {}]".format(
bidder_id, listing_name, owner_id, bid_date, price))
return False
def get_listings_by_listing_name(listing_name):
with DatabaseCursor() as cursor:
current_app.logger.info(
"Getting listings under listing with name {}".format(listing_name))
cursor.execute(
"SELECT l.listing_name, u.name AS user_name, l.description, l.listed_date, "
"tag.name AS tag_name, l.owner_id, u.username FROM listing l "
"LEFT JOIN listing_tag lt ON l.listing_name = lt.listing_name AND l.owner_id = lt.owner_id "
"LEFT JOIN tag AS tag ON lt.tag_id = tag.tag_id "
"LEFT JOIN users AS u ON l.owner_id = u.id "
"WHERE LOWER(l.listing_name) LIKE LOWER(%s) "
"GROUP BY l.listing_name, u.name, l.description, l.listed_date,"
" tag.name, l.owner_id, u.username "
"ORDER BY listed_date DESC;", (listing_name, ))
return cursor.fetchall()
def insert_bid(bidder_id, listing_name, owner_id, bid_date, price):
try:
with DatabaseCursor() as cursor:
cursor.execute(
'INSERT INTO bid VALUES(%s, %s, %s, %s, %s);',
(bidder_id, listing_name, owner_id, bid_date, price))
current_app.logger.info(
"Bid added to database: [{}, {}, {}, {}, {}]".format(
bidder_id, listing_name, owner_id, bid_date, price))
return True
except psycopg2.IntegrityError:
current_app.logger.error(
"INSERTION FAILED: [{}, {}, {}, {}, {}]".format(
bidder_id, listing_name, owner_id, bid_date, price))
return False
def insert_loan(bidder_id, listing_name, owner_id, bid_date, borrow_date,
return_date, return_loc, pickup_loc):
try:
with DatabaseCursor() as cursor:
cursor.execute(
'INSERT INTO loan VALUES(%s, %s, %s, %s, %s, %s, %s, %s);',
(bidder_id, listing_name, owner_id, bid_date, borrow_date,
return_date, return_loc, pickup_loc))
current_app.logger.info(
"Loan added to database: [{}, {}, {}, {}, {}, {}, {}, {}]".
format(bidder_id, listing_name, owner_id, bid_date,
borrow_date, return_date, return_loc, pickup_loc))
return True
except psycopg2.IntegrityError:
current_app.logger.error(
"INSERTION FAILED: [{}, {}, {}, {}, {}, {}, {}, {}]".format(
bidder_id, listing_name, owner_id, bid_date, borrow_date,
return_date, return_loc, pickup_loc))
return False
def update_listing(listing_name,
owner_id,
description,
listed_date,
is_available=True):
try:
with DatabaseCursor() as cursor:
cursor.execute(
'UPDATE listing SET listed_date = %s, description = %s, is_available = %s '
'where listing_name = %s and owner_id = %s;',
(listed_date, description, is_available, listing_name,
owner_id))
current_app.logger.info(
"Listing {} updated: [{}, {}, {}, {}]".format(
listing_name, description, is_available, listed_date,
owner_id))
return True
except psycopg2.Error:
current_app.logger.error("UPDATE FAILED: [{}, {}, {}, {}, {}]".format(
listed_date, description, is_available, listing_name, owner_id))
return False
def insert_listing(listing_name,
owner_id,
description,
listed_date,
is_available=True):
try:
with DatabaseCursor() as cursor:
cursor.execute('INSERT INTO listing VALUES(%s, %s, %s, %s, %s);',
(listing_name, owner_id, description, listed_date,
is_available))
current_app.logger.info(
"Listing {} added to database: [{}, {}, {}, {}]".format(
listing_name, owner_id, description, listed_date,
is_available))
return True
except psycopg2.IntegrityError:
current_app.logger.error(
"INSERTION of {} FAILED: [{}, {}, {}, {}]".format(
listing_name, owner_id, description, listed_date,
is_available))
return False
def get_tag_by_id(tag_id):
with DatabaseCursor() as cursor:
current_app.logger.info("Getting tag with tag ID {} from database".format(tag_id))
cursor.execute('select * from tag where tag_id = %s;', (tag_id,))
return cursor.fetchone()
def get_all_listing():
with DatabaseCursor() as cursor:
current_app.logger.info("Getting all listing from database".format())
cursor.execute('SELECT * FROM LISTING;')
return cursor.fetchall()
def retrieve_users():
with DatabaseCursor() as cursor:
cursor.execute('select * from users')
result = cursor.fetchall()
return result