def delete_artist_engine(artist_id: int) -> (bool, str):
"""
Delete an artist in ENGINE mode
:param artist_id: id of the artist to delete
"""
success = False
artist_name = None
exists = False
try:
artist = execute(
f'SELECT name from "{ARTIST_TABLE}" WHERE id = {artist_id};')
if artist.rowcount != 0:
exists = True
artist_name = artist.mappings().first().get('name')
# when an artist is deleted, need to delete availability, genres & shows as well to keep the db consistent
execute_transaction([
f'DELETE FROM "{AVAILABILITY_TABLE}" WHERE artist_id = {artist_id};',
f'DELETE FROM "{SHOWS_TABLE}" WHERE artist_id = {artist_id};',
f'DELETE FROM "{ARTIST_GENRES_TABLE}" WHERE artist_id = {artist_id};',
f'DELETE FROM "{ARTIST_TABLE}" WHERE id = {artist_id};'
])
success = True
except:
print_exc_info()
if not exists:
abort(HTTPStatus.NOT_FOUND.value)
return success, artist_name
def delete_venue_engine(venue_id: int) -> (bool, str):
"""
Delete a venue in ENGINE mode
:param venue_id: id of the venue to delete
"""
success = False
venue_name = None
exists = False
try:
venue = execute(f'SELECT name from "{VENUE_TABLE}" WHERE id = {venue_id};')
if venue.rowcount != 0:
exists = True
venue_name = venue.mappings().first().get('name')
# when an venue is deleted, need to delete genres & shows as well to keep the db consistent
execute_transaction([
f'DELETE FROM "{SHOWS_TABLE}" WHERE venue_id = {venue_id};',
f'DELETE FROM "{VENUE_GENRES_TABLE}" WHERE venue_id = {venue_id};',
f'DELETE FROM "{VENUE_TABLE}" WHERE id = {venue_id};'
])
success = True
except:
print_exc_info()
if not exists:
abort(HTTPStatus.NOT_FOUND.value)
return success, venue_name
def create_artist_orm(artist: Artist, availability: Availability) -> (bool, str):
"""
Create an artist in ORM mode
:param artist: artist to create
:param availability: artist availability
"""
artist_name = artist.name
try:
db.session.add(artist)
db.session.commit()
if availability.is_available():
availability.artist_id = artist.id
db.session.add(availability)
db.session.commit()
success = True
except:
db.session.rollback()
print_exc_info()
success = False
finally:
db.session.close()
return success, artist_name
def create_artist_engine(artist: dict, availability: dict):
"""
Create an artist in ENGINE mode
:param artist: artist to create
:param availability: artist availability
"""
success = False
artist_name = artist["name"]
try:
new_artist = execute(artist_insert_sql(artist))
if new_artist.rowcount > 0:
# using raw sql so need to query to get new id
new_artist = execute(
id_name_by_unique_properties_sql(
*extract_unique_properties_engine(artist)))
if new_artist.rowcount > 0:
new_artist = new_artist.fetchone()
artist_id = new_artist["id"]
stmts = []
# add genres
for stmt in genre_changes_engine([], artist["genres"],
artist_id, _ARTIST_):
stmts.append(stmt)
if is_available(availability):
stmts.append(
availability_insert_sql(artist_id, availability))
execute_transaction(stmts)
success = True
except:
print_exc_info()
return success, artist_name
def delete_artist_orm(artist_id: int) -> (bool, str):
"""
Delete an artist in ORM mode
:param artist_id: id of the artist to delete
"""
artist = Artist.query.filter(Artist.id == artist_id).first_or_404()
artist_name = artist.name
try:
# when an artist is deleted, need to delete availability & shows as well to keep the db consistent
availability = Availability.query.filter(Availability.artist_id == artist_id).all()
shows = Show.query.filter(Show.artist_id == artist_id).all()
for available in availability:
db.session.delete(available)
for show in shows:
db.session.delete(show)
db.session.delete(artist)
db.session.commit()
success = True
except:
db.session.rollback()
print_exc_info()
success = False
finally:
db.session.close()
return success, artist_name
def existing_artist_orm(name: str, city: str, state: str):
"""
Check for existing artist
:param name: artist name
:param city: artist city
:param state: artist state
:return: existing artist id and name, or None
"""
artist_id = None
artist_name = None
try:
existing = Artist.query \
.with_entities(Artist.id, Artist.name) \
.filter(and_(func.lower(Artist.name) == func.lower(name),
func.lower(Artist.city) == func.lower(city),
func.upper(Artist.state) == func.upper(state))) \
.first()
if existing is not None:
artist_id = existing.id
artist_name = existing.name
except:
print_exc_info()
return artist_id, artist_name
def availability_by_artist_engine(
artist_id: int,
from_date=datetime,
as_type=EntityResult.DICT) -> Union[dict, None]:
"""
Search for an artist's latest availability
:param artist_id: id of artist
:param from_date: filtering criterion
:param as_type: result
"""
availability = None
try:
properties = [
'"' + AVAILABILITY_TABLE + '".' + p
for p in get_model_property_list(AVAILABILITY_TABLE)
]
properties = ', '.join(properties)
result = execute(
f'SELECT {properties} FROM "{AVAILABILITY_TABLE}" '
f'INNER JOIN "{ARTIST_TABLE}" '
f'ON {_AVAILABILITY_.fq_column("artist_id")} = {_ARTIST_.fq_column("id")} '
f'WHERE {_AVAILABILITY_.fq_column("artist_id")} = {artist_id} '
f'AND {_AVAILABILITY_.fq_column("from_date")} < TIMESTAMP \'{from_date}\' '
f'ORDER BY {_AVAILABILITY_.fq_column("from_date")} DESC, '
f'{_AVAILABILITY_.fq_column("id")} DESC;')
if result.rowcount == 0:
availability = None
else:
entry = result.mappings().first()
availability = {k: entry.get(k) for k in entry.keys()}
except:
print_exc_info()
abort(HTTPStatus.INTERNAL_SERVER_ERROR.value)
return availability
def update_venue_orm(venue_id: int, form: FlaskForm) -> (bool, str):
"""
Update a venue in ORM mode
:param venue_id: id of the venue to update
:param form: form to populate from
"""
commit_change = False
venue = Venue.query.filter(Venue.id == venue_id).first_or_404()
venue_name = venue.name
updated_venue = populate_venue_orm(Venue(), form)
if not updated_venue.equal(venue, IGNORE_ID):
# change has occurred update venue
populate_venue_orm(venue, form)
commit_change = True
try:
if commit_change:
db.session.commit()
success = True
else:
success = None
except:
db.session.rollback()
print_exc_info()
success = False
finally:
db.session.close()
return success, venue_name
def bookings_by_venue_orm(venue_id: int, query_date: datetime) -> list:
"""
Search for a venue's bookings
:param venue_id: id of venue
:param query_date: date filtering criterion
"""
bookings = []
try:
query = Show.query.join(Venue, Show.venue_id == Venue.id) \
.join(Artist, Show.artist_id == Artist.id) \
.with_entities(Show.start_time, Show.duration, Artist.name)
if query_date is not None:
query = query.filter(
and_(Show.venue_id == venue_id,
Show.start_date == cast(query_date, Date)))
else:
query = query.filter(Show.venue_id == venue_id) \
.order_by(Show.start_date)
bookings = query.all()
except:
print_exc_info()
abort(HTTPStatus.INTERNAL_SERVER_ERROR.value)
# [{'start_time': ?, 'duration' ?, ...}, {}, ...] }
return [{
k: show[BOOKING_BY_VENUE_DICT[k]]
for k, v in BOOKING_BY_VENUE_DICT.items()
} for show in bookings]
def existing_venue_orm(name: str, address: str, city: str, state: str):
"""
Check for existing venue
:param name: artist name
:param address: artist address
:param city: artist city
:param state: artist state
:return: existing venue id and name, or None
"""
venue_id = None
venue_name = None
try:
existing = Venue.query \
.with_entities(Venue.id, Venue.name) \
.filter(and_(func.lower(Venue.name) == func.lower(name),
func.lower(Venue.city) == func.lower(city),
func.lower(Venue.address) == func.lower(address),
func.upper(Venue.state) == func.upper(state))) \
.first()
if existing is not None:
venue_id = existing.id
venue_name = existing.name
except:
print_exc_info()
return venue_id, venue_name
def bookings_by_venue_engine(venue_id: int, query_date: datetime):
"""
Search for a venue's bookings
:param venue_id: id of venue
:param query_date: date filtering criterion
"""
bookings = []
try:
sql = f'SELECT "{SHOWS_TABLE}".start_time, "{SHOWS_TABLE}".duration, "{ARTIST_TABLE}".name ' \
f'FROM (("{SHOWS_TABLE}" ' \
f'INNER JOIN "{VENUE_TABLE}" ON "{SHOWS_TABLE}".venue_id = "{VENUE_TABLE}".id) ' \
f'INNER JOIN "{ARTIST_TABLE}" ON "{SHOWS_TABLE}".artist_id = "{ARTIST_TABLE}".id) ' \
f'WHERE "{SHOWS_TABLE}".venue_id = {venue_id}'
if query_date is not None:
sql = f'{sql} AND DATE("{SHOWS_TABLE}".start_time) = \'{date_to_str(query_date)}\''
else:
sql = f'{sql} ORDER BY "{SHOWS_TABLE}".start_time'
sql = sql + ';'
bookings = execute(sql).fetchall()
except:
print_exc_info()
abort(HTTPStatus.INTERNAL_SERVER_ERROR.value)
# [{'start_time': ?, 'duration' ?, ...}, {}, ...] }
return [{k: show[BOOKING_BY_VENUE_DICT[k]] for k, v in BOOKING_BY_VENUE_DICT.items()} for show in bookings]
def create_venue_engine(venue: dict):
"""
Create an venue in ENGINE mode
:param venue: venue to create
"""
success = False
venue_name = venue["name"]
try:
new_venue = execute(venue_insert_sql(venue))
if new_venue.rowcount > 0:
# using raw sql so need to query to get new id
new_venue = execute(
id_name_by_unique_properties_sql(*extract_unique_properties_engine(venue))
)
if new_venue.rowcount > 0:
new_venue = new_venue.fetchone()
venue_id = new_venue["id"]
stmts = []
# add genres
for stmt in genre_changes_engine([], venue["genres"], venue_id, _VENUE_):
stmts.append(stmt)
execute_transaction(stmts)
success = True
except:
print_exc_info()
return success, venue_name
def shows_orm(page: int, filterby: str, mode: str, form: FlaskForm,
search_term: str) -> dict:
"""
List all shows
:param page: requested page of search results
:param filterby: results filter; one of 'all', 'previous' or 'upcoming'
:param mode: one of 'basic', 'advanced' or 'all'
:param form: form data for advanced search
:param search_term: search_term for basic search
"""
shows_list = []
pagination = Pagination(None, page, SHOWS_PER_PAGE, 0, shows_list)
# advanced search on Venue & Artist, joining class clauses with 'and' and the result of those with 'or'
# e.g. if have 'name' do same search on Venue & Artist and 'or' their results
search = SearchParams([_ARTIST_, _VENUE_],
conjunction=[OR_CONJUNC,
AND_CONJUNC]).load_form(form)
search.simple_search_term = search_term
try:
shows_list = Show.query\
.join(Venue, Show.venue_id == Venue.id) \
.join(Artist, Show.artist_id == Artist.id) \
.with_entities(Show.venue_id, Show.artist_id, Show.start_time,
Venue.name, Artist.name, Artist.image_link)
if filterby == FILTER_PREVIOUS:
shows_list = shows_list.filter(Show.start_time < datetime.today())
elif filterby == FILTER_UPCOMING:
shows_list = shows_list.filter(Show.start_time > datetime.today())
# get search terms and clauses for both Venue & Artist
ncsg_search_clauses(mode, search)
if len(search.clauses) > 0:
shows_list = entity_search_clauses(shows_list, search,
entity_search_expression)
pagination = shows_list \
.order_by(Show.start_time) \
.paginate(page=page, per_page=SHOWS_PER_PAGE)
shows_list = pagination.items
except:
print_exc_info()
abort(HTTPStatus.INTERNAL_SERVER_ERROR.value)
# [{'venue_id': ?, 'artist_id' ?, ...}, {}, ...] }
data = [{
k: show[v] if k != 'start_time' else show[v].isoformat()
for k, v in SHOWS_DICT.items()
} for show in shows_list]
return {
"count": pagination.total,
"data": data,
"search_term": ', '.join(search.search_terms),
"mode": mode,
"pagination": pagination
}
def create_show_engine(show: dict):
"""
Create an show in ENGINE mode
:param show: show to create
"""
success = False
try:
new_show = execute(show_insert_sql(show))
success = new_show.rowcount > 0
except:
print_exc_info()
return success
def index():
latest_artists = []
latest_venues = []
try:
latest_artists, latest_venues = latest_lists()
except:
print_exc_info()
abort(HTTPStatus.INTERNAL_SERVER_ERROR.value)
artist_list = [{'id': a.id, 'name': a.name} for a in latest_artists]
venue_list = [{'id': v.id, 'name': v.name} for v in latest_venues]
return render_template('pages/home.html',
artists=artist_list,
venues=venue_list)
def exists_or_404(entity: Union[Model, AnyStr], entity_id: int):
"""
Check if entity exists, or abort with 404
:param entity: entity model or name of table to search
:param entity_id: id of entity to check
"""
exists = False
try:
exists = exists_impl(entity, entity_id)
except:
print_exc_info()
abort(HTTPStatus.INTERNAL_SERVER_ERROR.value)
if not exists:
abort(HTTPStatus.NOT_FOUND.value)
def artists_and_venues_orm():
"""
Get artists and venues for show listing
"""
artists = []
venues = []
try:
artists = Artist.query.with_entities(Artist.id, Artist.name).order_by(
Artist.name).all()
venues = Venue.query.with_entities(Venue.id, Venue.name).order_by(
Venue.name).all()
except:
print_exc_info()
return artists, venues
def artists_and_venues_engine():
"""
Get artists and venues for show listing
"""
artists = []
venues = []
try:
artists = execute(f'SELECT id, name FROM "{ARTIST_TABLE}" ORDER BY name;') \
.fetchall()
artists = [(a["id"], a["name"]) for a in artists]
venues = execute(f'SELECT id, name FROM "{VENUE_TABLE}" ORDER BY name;') \
.fetchall()
venues = [(a["id"], a["name"]) for a in venues]
except:
print_exc_info()
return artists, venues
def create_show_orm(show: Show):
"""
Create a show in ORM mode
:param show: show to create
"""
try:
db.session.add(show)
db.session.commit()
success = True
except:
db.session.rollback()
print_exc_info()
success = False
finally:
db.session.close()
return success
def create_venue_orm(venue: Venue):
"""
Create an venue in ORM mode
:param venue: venue to create
"""
venue_name = venue.name
try:
db.session.add(venue)
db.session.commit()
success = True
except:
db.session.rollback()
print_exc_info()
success = False
finally:
db.session.close()
return success, venue_name
def update_artist_orm(artist_id: int, form: FlaskForm, availability: Availability) -> (bool, str):
"""
Update an artist in ORM mode
:param artist_id: id of the artist to update
:param form: form to populate from
:param availability: artist availability
"""
commit_change = False
artist = Artist.query.filter(Artist.id == artist_id).first_or_404()
artist_name = artist.name
updated_artist = populate_artist_orm(Artist(), form)
if not updated_artist.equal(artist, IGNORE_ID):
# change has occurred update artist
populate_artist_orm(artist, form)
artist_name = artist.name
commit_change = True
new_availability = populate_availability_orm(Availability(), form)
new_availability.artist_id = artist_id
try:
if is_available(availability) != is_available(new_availability) or \
not availability.equal(new_availability, IGNORE_ID_DATE):
# availability has changed, add new setting
db.session.add(new_availability)
commit_change = True
if commit_change:
db.session.commit()
success = True
else:
success = None
except:
db.session.rollback()
print_exc_info()
success = False
finally:
db.session.close()
return success, artist_name
def existing_artist_engine(name: str, city: str, state: str):
"""
Check for existing artist
:param name: artist name
:param city: artist city
:param state: artist state
:return: existing artist id and name, or None
"""
artist_id = None
artist_name = None
try:
existing = execute(id_name_by_unique_properties_sql(name, city, state))
if existing.rowcount > 0:
hit = existing.mappings().first()
artist_id = hit.get('id')
artist_name = hit.get('name')
except:
print_exc_info()
return artist_id, artist_name
def availability_by_artist_orm(artist_id: int,
from_date: datetime, as_type=EntityResult.DICT) -> Union[dict, Availability, None]:
"""
Search for an artist's latest availability
:param artist_id: id of artist
:param from_date: filtering criterion
:param as_type: result
"""
availability = None
try:
availability = Availability.query.join(Artist, Availability.artist_id == Artist.id) \
.filter(and_(Availability.artist_id == artist_id), Availability.from_date < from_date) \
.order_by(Availability.from_date.desc(), Availability.id.desc()) \
.first()
if availability is not None and as_type != EntityResult.MODEL:
availability = availability.get_dict()
except:
print_exc_info()
abort(HTTPStatus.INTERNAL_SERVER_ERROR.value)
return availability
def venues_engine():
"""
List all venues
"""
venues = []
try:
cities_states = execute(f'SELECT DISTINCT state, city from "{VENUE_TABLE}";')
for city_state in cities_states:
city = city_state["city"]
state = city_state["state"]
venue_list = execute(
f'SELECT DISTINCT id, name from "{VENUE_TABLE}" WHERE state = \'{state}\' AND city = \'{city}\';')
venues.append({
"state": state,
"city": city,
"venues": entity_shows_count(venue_list, _VENUE_)
})
except:
print_exc_info()
abort(HTTPStatus.INTERNAL_SERVER_ERROR.value)
return venues
def venues_orm() -> list:
"""
List all venues
"""
venues = []
try:
cities_states = Venue.query.with_entities(Venue.state,
Venue.city).distinct().all()
for city_state in cities_states:
venue_list = Venue.query.with_entities(Venue.id, Venue.name) \
.filter(and_(Venue.state == city_state.state, Venue.city == city_state.city)) \
.all()
venues.append({
"state": city_state[0],
"city": city_state[1],
"venues": entity_shows_count(venue_list, _VENUE_)
})
except:
print_exc_info()
abort(HTTPStatus.INTERNAL_SERVER_ERROR.value)
return venues
def existing_venue_engine(name: str, address: str, city: str, state: str):
"""
Check for existing venue
:param name: venue name
:param address: venue address
:param city: venue city
:param state: venue state
:return: existing venue id and name, or None
"""
venue_id = None
venue_name = None
try:
existing = execute(
id_name_by_unique_properties_sql(name, address, city, state)
)
if existing.rowcount > 0:
hit = existing.mappings().first()
venue_id = hit.get('id')
venue_name = hit.get('name')
except:
print_exc_info()
return venue_id, venue_name
def delete_venue_orm(venue_id: int):
"""
Delete an venue in ORM mode
:param venue_id: id of the venue to delete
"""
venue = Venue.query.filter(Venue.id == venue_id).first_or_404()
venue_name = venue.name
try:
# when an venue is deleted, need to delete shows as well to keep the db consistent
shows = Show.query.filter(Show.venue_id == venue_id).all()
for show in shows:
db.session.delete(show)
db.session.delete(venue)
db.session.commit()
success = True
except:
db.session.rollback()
print_exc_info()
success = False
finally:
db.session.close()
return success, venue_name
def shows_engine(page: int, filterby: str, mode: str, form: FlaskForm,
search_term: str) -> dict:
"""
List all shows
:param page: requested page of search results
:param filterby: results filter; one of 'all', 'previous' or 'upcoming'
:param mode: one of 'basic', 'advanced' or 'all'
:param form: form data for advanced search
:param search_term: search_term for basic search
"""
shows_list = []
pagination = Pagination(None, page, SHOWS_PER_PAGE, 0, shows_list)
# advanced search on Venue & Artist, joining class clauses with 'and' and the result of those with 'or'
# e.g. if have 'name' do same search on Venue & Artist and 'or' their results
search = \
SearchParams([_VENUE_, _ARTIST_], conjunction=[OR_CONJUNC, AND_CONJUNC],
genre_aliases=[None, _ALIAS_GENRE_TABLE_]).load_form(form)
search.simple_search_term = search_term
try:
if filterby == FILTER_PREVIOUS:
time_filter = f'"{SHOWS_TABLE}".start_time < \'{datetime_to_str(datetime.today())}\''
elif filterby == FILTER_UPCOMING:
time_filter = f'"{SHOWS_TABLE}".start_time > \'{datetime_to_str(datetime.today())}\''
else:
time_filter = None
# get search terms and clauses for both Venue & Artist
ncsg_search_clauses(mode, search)
from_term = _GENRE_FROM_JOIN_ if search.searching_on[
SP_GENRES] else _BASIC_FROM_JOIN_
if len(search.clauses) > 0:
search_filter = entity_search_clauses('', search,
entity_search_expression)
else:
search_filter = None
filters = _combine_filters(search_filter, time_filter)
# get total count
sql = f'SELECT COUNT("{SHOWS_TABLE}".venue_id) FROM {from_term}{filters};'
total = execute(sql).scalar()
if total > 0:
offset = SHOWS_PER_PAGE * (page - 1)
if offset >= total:
abort(HTTPStatus.BAD_REQUEST.value)
else:
offset = 0
# get items for this request
sql = f'SELECT {_FIELDS_LIST_} FROM {from_term}{filters} ' \
f'ORDER BY "{SHOWS_TABLE}".start_time LIMIT {SHOWS_PER_PAGE} OFFSET {offset};'
shows_list = execute(sql).fetchall()
total = len(shows_list)
pagination = Pagination(None, page, SHOWS_PER_PAGE, total, shows_list)
shows_list = pagination.items
except:
print_exc_info()
abort(HTTPStatus.INTERNAL_SERVER_ERROR.value)
# [{'venue_id': ?, 'artist_id' ?, ...}, {}, ...] }
data = [{
k: show[v] if k != 'start_time' else show[v].isoformat()
for k, v in SHOWS_DICT.items()
} for show in shows_list]
return {
"count": pagination.total,
"data": data,
"search_term": ', '.join(search.search_terms),
"mode": mode,
"pagination": pagination
}
