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 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 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 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 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 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 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_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 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 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 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 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
}