def get_from_venue(venueid):
"""
Get the address of a given venue.
"""
with dbCursor() as cursor:
cursor.execute("SELECT * FROM Addresses WHERE venueid=?", venueid)
return cursor.fetchone()
def get(id):
"""
Return an address with the matching id.
"""
with dbCursor() as cursor:
cursor.execute("SELECT * FROM Addresses WHERE aid=?", id)
return cursor.fetchone()
def get(id):
"""
Geth the path and venue information for a given image id.
"""
with dbCursor() as cursor:
cursor.execute("SELECT * FROM images WHERE id = ?", id)
return cursor.fetchone()
def get_from_uname(userName):
"""
Return an owner matching the given userName.
"""
with dbCursor() as cursor:
cursor.execute("SELECT * FROM Owners WHERE userName=?", userName)
return cursor.fetchone()
def insert(name, userName, password, email=None, phone=None, description=None):
"""
Insert an owner into the database with the given details.
Give the raw text password and the SHA2_512 hash will be stored,
calculated with HASHBYTES('SHA2_512', '<password>').
Attempting to add duplicate usernames raises a pyodbc.IntegrityError.
Returns the id of the inserted owner.
"""
with dbCursor() as cursor:
try:
cursor.execute(
"INSERT INTO Owners (name, userName, email, phone, description, pwdhash) \
OUTPUT INSERTED.ownerid VALUES (?, ?, ?, ?, ?, HASHBYTES('SHA2_512', ?))",
(name, userName, email, phone, description, password)
)
except pyodbc.IntegrityError as e:
raise InsertionError(
"SQL Integrity Error, likely a duplicate username on insert.", col='userName', _type="duplicate")
res = cursor.fetchone()
if res is not None:
return int(res[0])
else:
return None
def get_from_uname(userName):
"""
Return a single user with the matching username, or None if it doesn't exist.
"""
with dbCursor() as cursor:
cursor.execute("SELECT * FROM Users WHERE userName=?", userName)
return cursor.fetchone()
def get(id):
"""
Get an availability matching the given id.
"""
with dbCursor() as cursor:
cursor.execute("SELECT * FROM Availabilities WHERE avId=?", id)
return cursor.fetchone()
def get(id):
"""
Return a venue with the matching id.
"""
with dbCursor() as cursor:
cursor.execute("SELECT * FROM Venues WHERE venueid=?", id)
return cursor.fetchone()
def get_for_owner(ownerid):
"""
Return all venues associated with the given owner.
"""
with dbCursor() as cursor:
cursor.execute("SELECT * FROM Venues WHERE ownerid=?", ownerid)
return cursor.fetchall()
def update(bookid, **fields):
"""
Update a booking record. Takes the id of the booking to be updated (bookid)
and values for available fields:
fields:
venueid, userid, startDate, endDate
Similar usage to update_owner()
"""
valid_fields = ("venueid", "userid", "startDate", "endDate")
if 'bookid' in fields:
raise ArgumentException("Unable to change a booking's id.")
for f in fields:
if f not in valid_fields:
raise ArgumentException(f"Invalid field name: {f}")
query = "UPDATE Bookings SET "
keys = [f for f in fields]
vals = [fields[f] for f in keys]
s = [f"{f} = ?" for f in keys]
s = ' , '.join(s)
query += s
query += " WHERE bookid=?"
with dbCursor() as cursor:
cursor.execute(query, (*vals, bookid))
def get_all():
"""
Return a venue with the matching id.
"""
with dbCursor() as cursor:
cursor.execute("SELECT * FROM Venues")
return cursor.fetchall()
def get_for_venue(uid):
"""
Return all bookings for the given venue.
"""
with dbCursor() as cursor:
cursor.execute("SELECT * FROM Bookings WHERE venueid=?", uid)
return cursor.fetchall()
def insert(venueid, userid, startDate, endDate):
"""
Add a booking record. Must use an existing venueid and userid.
If either given id's don't exist in their tables an error is raised.
Returns the id of the new record.
** Date should be of type datetime.date
"""
## Fields:
# id int Identity PRIMARY KEY
# venueid int not null FK -> Venues(id)
# userid int not null FK -> Users(id)
# startDate date not null
# endDate date not null
with dbCursor() as cursor:
try:
cursor.execute(
"INSERT INTO Bookings (venueid, userid, startDate, endDate) \
OUTPUT INSERTED.bookid VALUES (?, ?, ?, ?)",
(venueid, userid, startDate, endDate))
except pyodbc.IntegrityError as e:
print("Invalid venueid or userid on insert.")
raise e
res = cursor.fetchone()
if res is not None:
return int(res[0])
else:
return None
def get_for_venue(venueid):
"""
Fetch all image rows for a given venue.
"""
with dbCursor() as cursor:
cursor.execute("SELECT * FROM images WHERE venueid = ?", venueid)
return cursor.fetchall()
def search(**patterns):
"""
Provide patterns of the form {"column":"pattern"}
referring to the following fields,
and a query will be constructed like:
SELECT * FROM Bookings
WHERE
column1 LIKE pattern1 AND
column2 LIKE pattern2 ...
Returns a list of matching rows.
See https://www.w3schools.com/sql/sql_like.asp for
information on patterns.
Available fields:
bookid int
venueid int
userid int
startDate date
endDate date
"""
query = "SELECT * FROM Bookings WHERE "
cols = [c for c in patterns]
query += f"{cols[0]} LIKE ?"
for c in cols[1:]:
query += f" AND {c} LIKE ?"
with dbCursor() as cursor:
cursor.execute(query, tuple(patterns[c] for c in cols))
return cursor.fetchall()
def get(id):
"""
Return a booking with the matching id.
"""
with dbCursor() as cursor:
cursor.execute("SELECT * FROM Bookings WHERE bookid=?", id)
return cursor.fetchone()
def update(aid, location=None):
"""
Update the location of an address row.
"""
if location is not None:
with dbCursor() as cursor:
cursor.execute('UPDATE Addresses SET location=? WHERE aid=?',
(location, aid))
def get_for_venue(venueid):
"""
Fetch all reviews for a given venue.
Return a list of
[ (revid, venueid, userid, postTime, postDate, rating, review), ... ]
"""
with dbCursor() as cursor:
cursor.execute("SELECT * FROM Reviews WHERE venueid = ?", (venueid))
return cursor.fetchall()
def get(id):
"""
Return an owner with the matching id.
Return None if the owner does not exist.
"""
with dbCursor() as cursor:
cursor.execute("SELECT * FROM Owners WHERE ownerid=?", id)
return cursor.fetchone()
def get(id):
"""
Return a single user with the matching id from the database
Returns None if the user does not exist.
"""
with dbCursor() as cursor:
cursor.execute("SELECT * FROM Users WHERE userid=?", id)
return cursor.fetchone()
def get_availabilities(venueid):
"""
Return all availabilities for a venue with the
given venueid.
"""
with dbCursor() as cursor:
cursor.execute(
"SELECT * FROM Availabilities \
WHERE venueid=?", venueid)
return cursor.fetchall()
def get_for_user(uid):
"""
Return all bookings for the given user.
Orders the output by start date.
"""
with dbCursor() as cursor:
cursor.execute(
"SELECT * FROM Bookings WHERE userid=? ORDER BY startDate", uid)
return cursor.fetchall()
def get_overlapping(startDate, endDate):
"""
Get all availabilities that contain the given period in their
start and end dates.
Returns a list of availability rows.
"""
with dbCursor() as cursor:
cursor.execute(
"SELECT * FROM Availabilities WHERE \
startDate<? AND endDate>?", (startDate, endDate))
return cursor.fetchall()
def get_for_venue_overlapping(venueid, startDate, endDate):
"""
Return bookings where the give date range overlaps even partially
with the existing booking date range.
"""
with dbCursor() as cursor:
cursor.execute(
"""SELECT * FROM Bookings WHERE
endDate >= ? AND startDate <= ? AND venueid=?""",
(startDate, endDate, venueid))
return cursor.fetchall()
def check_user_pass(username, password_text):
"""
Search owners for an owner with the matching username and password.
If username and password match, return that row.
Otherwise return None.
"""
with dbCursor() as cursor:
cursor.execute("SELECT * FROM Owners \
WHERE userName=? AND pwdhash=HASHBYTES('SHA2_512', ?)", (username, password_text))
return cursor.fetchone()
def update(ownerid, **fields):
"""
Update a owner record. Takes the id of the owner to be updated (ownerid)
and keyword arguments corresponding to the table's schema.
pwdhash and pwdplain must not be supplied at the same time, as both
affect the pwdhash field.
** To update the password: **
Changing the pwdhash is not recommended. Instead, provide a plain text
password for the keyword pwdplain and the hash will be calculated and
stored.
valid fields are:
name, userName, email, phone, description, pwdhash, pwdplain
Usage:
update_owner(1, email='*****@*****.**') # Change the owner's email.
Or
kwargs = {'email': '*****@*****.**', 'phone': '12345'}
update_owner(2, **kwargs) # Change the owner's phone number and email.
"""
valid_fields = (
"name", "userName", "email", "phone", "description", "pwdhash", "pwdplain"
)
if 'ownerid' in fields:
raise ArgumentException("Unable to change a owner's id.")
if 'pwdplain' in fields and 'pwdhash' in fields:
raise ArgumentException(
"Can not change plain text password and password hash fields simultaneously.")
query = "UPDATE Owners SET "
for f in fields:
if f not in valid_fields:
raise ArgumentException("Invalid field name: " + f)
# Build the rest of the query
keys = [f for f in fields]
# Do this to ensure dict ordering is irrelevant
vals = [fields[k] for k in keys if k != 'pwdplain']
s = [f"{f} = ?" for f in keys if f != 'pwdplain']
if 'pwdplain' in keys:
s.append("pwdhash = HASHBYTES('SHA2_512', ?)")
vals.append(fields['pwdplain'])
s = ' , '.join(s)
query += s
query += " WHERE ownerid=?"
with dbCursor() as cursor:
cursor.execute(query, (*vals, ownerid))
def insert(venueid, startDate, endDate):
"""
Insert an availability and return the id of the new availability.
"""
with dbCursor() as cursor:
cursor.execute(
"INSERT INTO Availabilities (venueid, startDate, endDate) OUTPUT INSERTED.avId VALUES (?, ?, ?)",
(venueid, startDate, endDate))
res = cursor.fetchone()
if res is not None:
return int(res[0])
else:
return None
def get_overlapping_availability(venueid, startDate, endDate):
"""
Get availabilities that contain the given period in their
start and end dates for a given venue id.
Returns a list of availability rows.
Edge cases are included.
"""
with dbCursor() as cursor:
cursor.execute(
"SELECT * FROM Availabilities WHERE \
venueid=? AND startDate<=? AND endDate>=?",
(venueid, startDate, endDate))
return cursor.fetchall()
def insert(ownerid,
addressid,
name,
bedCount,
bathCount,
carCount,
description,
rate,
minStay,
maxStay,
details,
source=None):
"""
Insert a venue.
** Dates should be type datetime.date
Returns the id of the inserted venue.
"""
# Fields:
# id int Identity PRIMARY KEY
# ownerid int not null FK -> Owners(id)
# addressid int not null FK -> Addresses(id)
# name varchar(200) not null
# bedCount tinyint
# bathCount tinyint
# carCount tinyint
# description text
# rate smallmoney
# minStay int DEFAULT 1
# maxStay int
# details text
# ExtSource url
with dbCursor() as cursor:
try:
cursor.execute(
"INSERT INTO Venues (ownerid, addressid, name, \
bedCount, bathCount, carCount, description, rate, minStay, \
maxStay, details, ExtSource) OUTPUT INSERTED.venueid \
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)",
(ownerid, addressid, name, bedCount, bathCount, carCount,
description, rate, minStay, maxStay, details, source))
except pyodbc.IntegrityError as e:
print("Invalid venueid or userid on insert.")
raise e
res = cursor.fetchone()
if res is not None:
return int(res[0])
else:
return None
def insert(venueid, userid, postDateTime, recommends, reviewBad, reviewGood):
"""
Insert a review into the database.
`venueid`, `userid` should be ints,
`postDateTime` should be type `datetime.datetime`,
`recommends` should be True/False, and all others should be strings.
"""
with dbCursor() as cursor:
cursor.execute(
"""INSERT INTO Reviews
(venueid, userid, postDateTime, recommends, reviewBad, reviewGood)
OUTPUT INSERTED.revid
VALUES (?, ?, ?, ?, ?, ?)""",
(venueid, userid, postDateTime, recommends, reviewBad, reviewGood))
return cursor.fetchone()
