async def delete(self, request, event_id, session_id, tag_id, connection): """Removes tag from event session.""" query = (models.session_tag.t.delete().where( models.session_tag.t.c.session_id == session_id).where( models.session_tag.t.c.tag_id == tag_id)) # Compile query and execute query, params = asyncpgsa.compile_query(query) try: rows = await connection.execute(query, *params) except PostgresError: raise exceptions.NotUpdatedError # Return the HTTP 204 return response.text("", content_type="application/json", status=204)
async def query_fetchval(self, query, column=0): """ return a value in the first row. """ query_string, params = compile_query(query) async with (await self.get_pool()).acquire() as conn: return await conn.fetchval(query_string, *params, column=column)
async def query_fetch(self, query): query_string, params = compile_query(query) async with (await self.get_pool()).acquire() as conn: return await conn.fetch(query_string, *params)
async def post(self, request, event_id, connection): """Creates a new event location.""" # Location form location = request.json # Check, is event exists try: query_event = (select([models.event.t]) .select_from(models.event.t) .where(models.event.t.c.id == event_id) .apply_labels()) query_event, params = asyncpgsa.compile_query(query_event) try: row = await connection.fetchrow(query_event, *params) except PostgresError: raise exceptions.NotFetchedError if not row: raise exceptions.NotFoundError event = models.event.t.parse(row, prefix="events_") except exceptions.NotFoundError: return response.json( response_wrapper.error("Event not found"), status=404) # If event exists, create a transaction # We need to 1) save location, 2) fetch location from a database async with connection.transaction(): try: query = (models.location.t .insert() .values(event_id=event["id"], name=location["name"]) .returning(models.location.t.c.id)) query, params = asyncpgsa.compile_query(query) id = await connection.fetchval(query, *params) query = (select([models.location.t]) .select_from(models.location.t) .where(models.location.t.c.id == id) .apply_labels()) query, params = asyncpgsa.compile_query(query) try: row = await connection.fetchrow(query, *params) except PostgresError: raise exceptions.NotFetchedError if not row: raise exceptions.NotFoundError location = models.location.json_format( models.location.t.parse(row, prefix="locations_")) except (PostgresError, exceptions.DatabaseError): raise exceptions.NotCreatedError return response.json(response_wrapper.ok(location), status=201)
async def get(self, request, connection): """Returns a list of platforms.""" # Validate listing try: pivot, limit, direction = \ self.default_listing.validate_from_request(request) except ValueError: return response.json( response_wrapper.error("Listing arguments error"), status=400) # Prepare query query = select([models.platform.t]).select_from(models.platform.t) # Adjust query according to listing options if pivot is not None: try: query_pivot = query.where(models.platform.t.c.id == pivot) query_pivot, params = asyncpgsa.compile_query(query_pivot) try: row = await connection.fetchrow(query_pivot, *params) except PostgresError: raise exceptions.NotFetchedError if not row: raise exceptions.NotFoundError pivot = models.platform.t.parse(row) except exceptions.NotFoundError: return response.json( response_wrapper.error("Pivot platform not found"), status=400) if direction == listing.Direction.BEFORE: query = ( query.where((models.platform.t.c.slug < pivot["slug"]) | ((models.platform.t.c.slug == pivot["slug"]) & (models.platform.t.c.id > pivot["id"])))) elif direction == listing.Direction.AFTER: query = ( query.where((models.platform.t.c.slug > pivot["slug"]) | ((models.platform.t.c.slug == pivot["slug"]) & (models.platform.t.c.id < pivot["id"])))) # Apply sorting and limit if direction == listing.Direction.BEFORE: query = (query.order_by(models.platform.t.c.slug.desc()).order_by( models.platform.t.c.id.asc()).limit(limit)) elif direction == listing.Direction.AFTER: query = (query.order_by(models.platform.t.c.slug.asc()).order_by( models.platform.t.c.id.desc()).limit(limit)) # Compile query, execute and parse query, params = asyncpgsa.compile_query(query) try: rows = await connection.fetch(query, *params) except PostgresError: raise exceptions.NotFetchedError platforms = [ models.platform.json_format(models.platform.t.parse(row)) for row in rows ] if direction == listing.Direction.BEFORE: platforms.reverse() # Return the list return response.json(response_wrapper.ok(platforms))
async def get(self, request, event_id, connection): """Returns a list of event sessions aka schedule. This endpoint returns complete list, w/o support of listing. """ # Check, is event exists try: query_event = (select([models.event.t]).select_from( models.event.t).where( models.event.t.c.id == event_id).apply_labels()) query_event, params = asyncpgsa.compile_query(query_event) try: row = await connection.fetchrow(query_event, *params) except PostgresError: raise exceptions.NotFetchedError if not row: raise exceptions.NotFoundError event = models.event.t.parse(row, prefix="events_") except exceptions.NotFoundError: return response.json(response_wrapper.error("Event not found"), status=404) # If event exists, query all sessions query = (select([models.session.t]).select_from( models.session.t).where( models.session.t.c.event_id == event["id"]).order_by( models.session.t.c.start_time.asc()).order_by( models.session.t.c.end_time.asc()).order_by( models.session.t.c.created_at.asc()).order_by( models.session.t.c.id.desc()).apply_labels()) # Compile query, execute and parse query, params = asyncpgsa.compile_query(query) try: rows = await connection.fetch(query, *params) except PostgresError: raise exceptions.NotFetchedError sessions = [ models.session.json_format( models.session.t.parse(row, prefix="sessions_")) for row in rows ] # Construct sessions map for fetching persons, locations and tags smap = {session["id"]: session for session in sessions} sids = list(smap.keys()) # Query all persons, locations and tags ids for sessions # TODO: Optimizations for name, field, model in [ ("persons", "person_id", models.session_person), ("locations", "location_id", models.session_location), ("tags", "tag_id", models.session_tag) ]: for session in sessions: session[name] = [] query = (select([model.t]).select_from(model.t).where( model.t.c.session_id.in_(sids))) # Probably, hot spot query, params = asyncpgsa.compile_query(query) try: rows = await connection.fetch(query, *params) except PostgresError: raise exceptions.NotFetchedError rows = [model.t.parse(row) for row in rows] for row in rows: smap[str(row["session_id"])][name].append(str(row[field])) # Return the list return response.json(response_wrapper.ok(sessions))
async def post(self, request, event_id, connection): """Creates a new event session.""" # Session form session = request.json # Check, is event exists try: query_event = (select([models.event.t]).select_from( models.event.t).where( models.event.t.c.id == event_id).apply_labels()) query_event, params = asyncpgsa.compile_query(query_event) try: row = await connection.fetchrow(query_event, *params) except PostgresError: raise exceptions.NotFetchedError if not row: raise exceptions.NotFoundError event = models.event.t.parse(row, prefix="events_") except exceptions.NotFoundError: return response.json(response_wrapper.error("Event not found"), status=404) # If event exists, create a transaction # We need to 1) save session, 2) fetch session from a database async with connection.transaction(): try: query = (models.session.t.insert().values( event_id=event["id"], title=session["title"], description=session["description"], start_time=pendulum.parse(session["start_time"]), end_time=pendulum.parse(session["end_time"])).returning( models.session.t.c.id)) query, params = asyncpgsa.compile_query(query) id = await connection.fetchval(query, *params) query = (select([models.session.t]).select_from( models.session.t).where( models.session.t.c.id == id).apply_labels()) query, params = asyncpgsa.compile_query(query) try: row = await connection.fetchrow(query, *params) except PostgresError: raise exceptions.NotFetchedError if not row: raise exceptions.NotFoundError session = models.session.json_format( models.session.t.parse(row, prefix="sessions_")) except (PostgresError, exceptions.DatabaseError): raise exceptions.NotCreatedError for name in ["persons", "locations", "tags"]: session[name] = [] return response.json(response_wrapper.ok(session), status=201)
async def general_query(self, query, *args, **kwargs): q, a = compile_query(query) return self.connection.general_query(q, *args, **kwargs)
def compile_q(self, q): return asyncpgsa.compile_query(q)