def isBookmarked(conn, sid, uid):
'''checks if a work is bookmarked'''
curs = dbi.cursor(conn)
curs.execute(
'''select * from bookmarks
where sid=%s and uid=%s''', [sid, uid])
return curs.fetchone()
def uploadBook(dept, course_num, prof, price, condition, title, description):
curs = dbi.cursor(CONN)
# finds the course the book is for
curs.execute(
'''select id from courses
where department = %s
and number = %s
and professor = %s''', [dept, course_num, prof])
course_id = curs.fetchone()
# finds the A_book the book is for
if course_id:
curs.execute(
'''select book_id from A_book_course where
course_id = %s''', [course_id[0]])
book_id = curs.fetchone()
else:
book_id = 1
# insert the S_book into the database
curs.execute(
'''insert into S_books(price,
sold_status,
`condition`,
title,
description,
seller,
buyer,
book
)
values (%s,%s,%s,%s,%s,%s,%s,%s)''',
[price, 0, condition, title, description, 'jzhao2', None, book_id])
def updateReport(conn, infoDict, reportID, changed):
'''Updates the report of the given reportID. infoDict contains the new values
for the report, and changed is a boolean that determines whether or not
any of the identifying information of the report has been changed (such
as the name, dining hall, or date served).'''
curs = dbi.cursor(conn)
# we don't want anyone else changing anything about this report while we're
# changing it, and concurrent changes to other reports could affect whether
# this update results in a duplicate entry or not
curs.execute('''LOCK TABLES report WRITE,label WRITE''')
if changed: # duplication is only possible if identifying information has changed
print('checking duplicate')
if isDuplicate(curs, infoDict):
curs.execute('''UNLOCK TABLES''') # don't forget to unlock
return False
curs.execute(
'''UPDATE report SET
name=%s,meal=%s,served=%s,hall=%s,imagefile=%s,
notes=%s,owner=%s
WHERE id=%s''', [
infoDict['name'], infoDict['meal'], infoDict['served'],
infoDict['hall'], infoDict['imagefile'], infoDict['notes'],
infoDict['owner'], reportID
])
# delete and re-insert labels
curs.execute('''DELETE FROM label WHERE id=%s''', [reportID])
insertLabels(conn, infoDict['allergens'], reportID, 'allergen')
insertLabels(conn, infoDict['diets'], reportID, 'diet')
curs.execute('''UNLOCK TABLES''') # don't forget to unlock
return True
def getAuthorId(conn, sid):
'''given an sid, gets the uid'''
curs = dbi.cursor(conn)
curs.execute(
'''select uid from works inner join users using (uid)
where sid=%s''', [sid])
return curs.fetchone()
def uploadBook(dept, course_num, price, condition, title, author, description,
seller, filename, professor, year):
CONN = getConn('textbooks_db')
curs = dbi.cursor(CONN)
success = 1
# finds the course the book is for
curs.execute(
'''select id from courses
where department = %s
and number = %s''', [dept, course_num])
course_id = curs.fetchone()
if course_id is None:
# if the user is trying to submit for an non-existing course
return course_id
else:
# insert the book into the database
curs.execute(
'''insert into books(price, sold_status,`condition`, title, author,
`description`,seller,course,pic, professor, `year`)
values (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)''', [
price, 0, condition, title, author, description, seller,
course_id[0], filename, professor, year
])
return 1
def insertReport(conn, infoDict):
'''Inserts a report into the report table given a database connection
and a dictionary of values. Returns the unique ID of the just-inserted
report.'''
curs = dbi.cursor(conn)
curs.execute('''LOCK TABLES report WRITE,label WRITE''')
if isDuplicate(curs, infoDict):
curs.execute('''UNLOCK TABLES''')
return -1
else:
curs.execute(
'''
INSERT INTO report(name,meal,served,hall,imagefile,notes,owner)
VALUES(%s, %s, %s, %s, %s, %s, %s)
''', [
infoDict['name'], infoDict['meal'], infoDict['served'],
infoDict['hall'], infoDict['imagefile'], infoDict['notes'],
infoDict['owner']
])
curs.execute('SELECT LAST_INSERT_ID()')
reportID = curs.fetchone()[0]
insertLabels(conn, infoDict['allergens'], reportID, 'allergen')
insertLabels(conn, infoDict['diets'], reportID, 'diet')
curs.execute('''UNLOCK TABLES''')
return reportID
def getUserPic(username):
CONN = getConn('textbooks_db')
curs = dbi.cursor(CONN)
numrows = curs.execute('''select pic from users where username = %s''',
[username])
filename = curs.fetchone()
return filename
def insertRequest(conn, bnumber, city, country, guestnum, start, end):
'''Inserts a request into the database'''
curs = dbi.cursor(conn)
curs.execute(
'''insert into request(bnumber, guestnum, city, country,
start,end) values(%s, %s, %s, %s, %s, %s)''',
[bnumber, guestnum, city, country, start, end])
def getAllNums():
CONN = getConn('textbooks_db')
curs = dbi.cursor(CONN)
curs.execute('''select distinct number from courses
order by number asc''')
return curs.fetchall()
def getBookPic(bid):
CONN = getConn('textbooks_db')
curs = dbi.cursor(CONN)
numrows = curs.execute('''select pic from books where id = %s''', [bid])
filename = curs.fetchone()
return filename
def lastUpdated(conn, sid):
'''changes updated to current date whenever a work is updated '''
curs = dbi.cursor(conn)
curs.execute('lock tables works write')
curs.execute('''update works set updated = %s where sid = %s''',
[datetime.now(), sid])
curs.execute('unlock tables')
def getUIDFirst(conn):
'''gets last inserted uid'''
curs = dbi.cursor(conn)
curs.execute('select LAST_INSERT_ID()')
row = curs.fetchone()
print(row)
uid = row[0]
return uid
def getCourseNumbers(dept):
CONN = getConn('textbooks_db')
curs = dbi.cursor(CONN)
curs.execute(
'''select distinct number from courses
where department = %s''', [dept])
return curs.fetchall()
def addStory(conn, uid, title, summary):
'''given a uid, title, summary, adds the story'''
curs = dbi.cursor(conn)
curs.execute(
'''insert into works(uid, title, summary)
values (%s, %s, %s)''', [uid, title, summary])
curs.execute('select last_insert_id()')
return curs.fetchone()
def updateMovie(conn, tt, newtt, title, release, addedby, director):
'''Updates movie with the given tt from the database with given values'''
curs = dbi.cursor(conn)
curs.execute(
'''update movie
set tt=%s, title=%s, `release`=%s, addedby=%s, director=%s
where tt=%s''',
[newtt, title, release, addedby, director, tt])
def updateUser(conn, new_bnum, email, name, phonenum, bnumber):
'''update the user with the given bnumbers information in the database'''
curs = dbi.cursor(conn)
curs.execute(
'''update user
set bnumber=%s, email=%s, name=%s, phonenum=%s
where bnumber = %s''',
[new_bnum, email, name, phonenum, bnumber])
def insertMovie(conn, tt, title, year):
'''Given the movie's tt, title, and release year,
it inserts the movie to the database'''
curs = dbi.cursor(conn)
addedby = 1380
curs.execute(
'''insert into movies(tt, title, `release`, addedby) values (%s, %s, %s, %s)''',
[tt, title, year, addedby])
def addTags(conn, sid, genre, warnings, audience, isFin):
'''adds tags to a story'''
curs = dbi.cursor(conn)
tagslist = [*genre, *warnings, *audience, *isFin]
for i in tagslist:
curs.execute(
'''insert into taglink(tid, sid)
values (%s, %s)''', [i, sid])
def insertPass(conn, username, hashed_str):
'''inserts user into database when they make an account'''
curs = dbi.cursor(conn)
curs.execute('lock tables users')
curs.execute(
'''INSERT INTO users(uid,username,passhash)
VALUES(null,%s,%s)''', [username, hashed_str])
curs.execute('unlock tables')
def addComment(conn, commentText, uid, cid):
'''adds a comment to a chapter'''
curs = dbi.cursor(conn)
curs.execute(
'''insert into reviews(commenter, reviewText) values(%s, %s)''',
[uid, commentText])
curs.execute('select LAST_INSERT_ID()')
row = curs.fetchone()
rid = row[0]
curs.execute('''insert into reviewCredits values(%s, %s)''', [rid, cid])
def getSellingNums():
CONN = getConn('textbooks_db')
curs = dbi.cursor(CONN)
curs.execute('''select distinct number
from courses
inner join books
where (courses.id = books.course)
and books.sold_status = 0''')
return curs.fetchall()
def checkMovie(conn, tt):
'''Given the tt of a movie, it checks if it's in the database
and returns null or the title'''
curs = dbi.cursor(conn)
curs.execute('''select title from movie where tt=%s''', [tt])
s = curs.fetchone()
if s is None:
return s
else:
return s[0]
def editListing(conn, pid, newListing):
'''Updates a listing with the new information provided in dictionary form'''
curs = dbi.cursor(conn)
curs.execute(
'''update place set city=%s, country=%s, street1=%s, street2=%s,
state=%s, maxguest=%s, postalcode=%s where pid=%s''', [
newListing['city'], newListing['country'], newListing['street1'],
newListing['street2'], newListing['state'], newListing['maxguest'],
newListing['zip'], pid
])
def searchMovie(conn, ask):
'''Returns the tt of the first movie that matches the search query'''
curs = dbi.cursor(conn)
curs.execute('''select tt from movie where title like %s''',
['%' + ask + '%'])
s = curs.fetchone()
if s is None:
return s
else:
return s[0]
def addStory(conn, uid, title, summary, isFin):
'''given a uid, title, summary, adds the story'''
curs = dbi.cursor(conn)
curs.execute('lock tables works write')
curs.execute(
'''insert into works(uid, title, updated, summary, wip, avgRating)
values (%s, %s, %s, %s, %s, 0)''',
[uid, title, datetime.now(), summary, isFin])
curs.execute('select last_insert_id()')
curs.execute('unlock tables')
return curs.fetchone()
def insertListing(conn, bnumber, street1, street2, city, state, zipcode,
country, maxguest, start, end):
'''Inserts a listing and the corresponding availability'''
curs = dbi.cursor(conn)
curs.execute(
'''insert into place(bnumber, city, country, street1, street2,
state, maxguest, postalcode) values(%s, %s, %s, %s, %s, %s, %s, %s)''',
[bnumber, city, country, street1, street2, state, maxguest, zipcode])
pid = curs.lastrowid
insertAvailability(conn, pid, start, end)
return pid
def insertLabels(conn, infoDict, reportID, kind):
'''Inserts the listed and present/followed allergens or diets (depending
on kind) given by the provided dictionary and associates them with the
given ID. Kind can be one of "allergen" or "diet".'''
curs = dbi.cursor(conn)
for key in iter(infoDict):
for entry in infoDict[key]:
curs.execute('''
INSERT INTO label(id,code,labeled,kind)
VALUES(%s, %s, %s, %s)
''', \
[reportID, entry, key, kind])
def setSoldStatus(book_id, status):
CONN = getConn('textbooks_db')
curs = dbi.cursor(CONN)
if status == '1':
curs.execute('''UPDATE books SET sold_status = 1 WHERE id=%s''',
[book_id])
return 1
else:
curs.execute('''UPDATE books SET sold_status = 0 WHERE id=%s''',
[book_id])
return 0
def signUp():
''' gets the info from the sign up form and inserts info into the Users table, otherwise if a field is
not filled in, will reupload the page for them to try again '''
if request.method == 'GET':
return redirect(url_for('index'))
else:
try:
fullname = request.form['fName']
email = request.form['email']
username = request.form['username']
passwd1 = request.form['password1']
passwd2 = request.form['password2']
profpicPath = 'img/default_profilepic.jpeg'
if passwd1 != passwd2:
flash('passwords do not match')
return redirect(url_for('index'))
hashed = bcrypt.hashpw(passwd1.encode('utf-8'), bcrypt.gensalt())
hashed_str = hashed.decode('utf-8')
conn = db.getConn(DB)
curs = dbi.cursor(conn)
try:
curs.execute(
'''INSERT INTO Users(uid,fullname,email,username,hashed, biotxt, profpicPath)
VALUES(null,%s,%s,%s,%s, null, %s)''',
[fullname, email, username, hashed_str, profpicPath])
except Exception as err:
flash('That username is taken: {}'.format(repr(err)))
return redirect(url_for('index'))
curs.execute('select last_insert_id()')
row = curs.fetchone()
uid = row[0]
session['username'] = username
session['uid'] = uid
session['logged_in'] = True
session['fullname'] = fullname
os.mkdir('static/img/{}'.format(uid))
return redirect(url_for('user', username=username))
except Exception as err:
flash('form submission error: field(s) not filled in ' + str(err))
return redirect(url_for('index'))
def updateReport(conn, infoDict, reportID, changed):
curs = dbi.cursor(conn)
curs.execute('''LOCK TABLES report WRITE,label WRITE''')
if changed:
print('checking duplicate')
if isDuplicate(curs, infoDict):
curs.execute('''UNLOCK TABLES''')
return False
curs.execute(
'''UPDATE report SET
name=%s,meal=%s,served=%s,hall=%s,imagefile=%s,
notes=%s,owner=%s
WHERE id=%s''', [
infoDict['name'], infoDict['meal'], infoDict['served'],
infoDict['hall'], infoDict['imagefile'], infoDict['notes'],
infoDict['owner'], reportID
])
curs.execute('''DELETE FROM label WHERE id=%s''', [reportID])
insertLabels(conn, infoDict['allergens'], reportID, 'allergen')
insertLabels(conn, infoDict['diets'], reportID, 'diet')
curs.execute('''UNLOCK TABLES''')
return True
