async def add_anime(self, payload: PayloadData):
fut = self.app.pool.fetchrow(
"""
INSERT INTO api_anime_data (
id,
title,
title_english,
title_japanese,
description,
rating,
img_url,
link,
genres,
crunchyroll
) VALUES (random_string(18), $1, $2, $3, $4, $5, $6, $7, $8, $9)
ON CONFLICT (title)
DO UPDATE SET
title_english = excluded.title_english,
title_japanese = excluded.title_japanese,
rating = excluded.rating,
description = excluded.description,
img_url = excluded.img_url,
link = excluded.link,
genres = excluded.genres,
crunchyroll = excluded.crunchyroll
RETURNING *;
""", payload.title, payload.title_english, payload.title_japanese,
payload.description, payload.rating, payload.img_url, payload.link,
payload.genres, payload.crunchyroll)
try:
row = await fut
except asyncpg.UniqueViolationError:
return StandardResponse(
status=400,
data=f"anime already exists with title: {payload.title!r}",
)
row = dict(row)
g_rows = await self.app.pool.fetch(
"""
SELECT name FROM api_genres WHERE id & $1 != 0;
""", row['genres'])
row['genres'] = [g_row['name'] for g_row in g_rows]
asyncio.get_running_loop().run_in_executor(
None, self.app.meili.anime.add_documents, [row])
return StandardResponse(status=200,
data={
"id":
row['id'],
"message":
f"anime added with id: {row['id']!r}",
})
async def get_flags_from_genres(self, genres: List[str] = Query(...)):
if len(genres) == 0:
return StandardResponse(status=200, data='0')
rows = await self.app.pool.fetch(
"""
SELECT id FROM api_genres WHERE name = any($1::text[]);
""", genres)
mapper = [row['id'] for row in rows]
flags = reduce(or_, mapper) if genres else 0
return StandardResponse(status=200, data=str(flags))
async def add_command(self, payload: Command):
# todo auth
await self.app.pool.execute(
"""
INSERT INTO bot_commands (
command_id,
name,
category,
about,
running,
user_required_permissions,
bot_required_permissions
) VALUES ($1, $2, $3, $4, $5, $6, $7);
""",
payload.command_id,
payload.name,
payload.category,
payload.about,
payload.running,
payload.user_required_permissions,
payload.bot_required_permissions,
)
return StandardResponse(
status=200,
data=f"successfully inserted command {payload.command_id}",
)
async def get_anime_with_id(self, anime_id: str):
row = await self.app.pool.fetchrow(
"""
SELECT
id,
title,
title_english,
title_japanese,
description,
rating,
img_url,
link,
array(SELECT name FROM api_genres WHERE id & api_anime_data.genres != 0) as genres,
crunchyroll
FROM api_anime_data
WHERE id = $1;
""", anime_id)
if row is None:
return StandardResponse(
status=404,
data=f"no anime found with id: {anime_id!r}",
).into_response()
return DataResponse(status=200, data=dict(row)) # noqa
async def add_item(
self,
user_id: int,
tag_id: str,
payload: TagItem,
):
""" Adds an item to the given tag for the given user. """
# todo auth
count = await self.app.pool.fetchrow(
"""
SELECT COUNT(id) AS total
FROM user_tracking_items
WHERE user_id = $1 AND tag_id = $2;
""", user_id, tag_id)
if count['total'] >= 20:
return ORJSONResponse(
StandardResponse(status=400, data="Max items already exists"))
fut = self.app.pool.fetchrow(
"""
INSERT INTO user_tracking_items (
id,
user_id,
tag_id,
title,
url,
referer,
description
) VALUES ($1, $2, $3, $4, $5, $6, $7)
RETURNING id;
""", uuid.uuid4(), user_id, tag_id, payload.title, payload.url,
payload.referer, payload.description)
try:
res = await fut
except asyncpg.ForeignKeyViolationError:
msg = StandardResponse(
status=404,
data=f"no tag exists with id: {tag_id} for user: {user_id}",
)
return ORJSONResponse(msg.dict(), status_code=404)
return ItemInsertResponse(status=200, data=str(res['id']))
async def remove_news_hook(self, guild_id: int):
# todo auth
await self.app.pool.execute("""
DELETE FROM guild_events_hooks_news
WHERE guild_id = $1;
""", guild_id)
return StandardResponse(status=200, data="successfully removed hook")
async def remove_aliases(
self,
guild_id: int,
alias: VarChar32 = None,
command_id: VarChar32 = None,
):
""" Removes an alias from the given guild. """
# todo auth
if command_id is not None and alias is not None:
return StandardResponse(
status=422,
data="query must contain either alias or command_id not both",
).into_response()
if command_id is not None:
await self.app.pool.execute(
"""
DELETE FROM guild_command_aliases
WHERE guild_id = $1 AND command_id = $2;
""", guild_id, command_id)
return StandardResponse(
status=200,
data=f"removed all aliases for command: {command_id} if exists",
)
if alias is not None:
await self.app.pool.execute(
"""
DELETE FROM guild_command_aliases
WHERE guild_id = $1 AND alias = $2;
""", guild_id, alias)
return StandardResponse(
status=200,
data=f"removed alias: {alias} if exists",
)
return StandardResponse(
status=422,
data="missing one query out of alias or command_id",
).into_response()
async def delete_tag(self, user_id: int, tag_id: str):
""" Deletes a given tag for a given user. """
# todo auth
await self.app.pool.execute(
"""
DELETE FROM user_tracking_tags
WHERE user_id = $1 AND tag_id = $2;
""", user_id, tag_id)
return StandardResponse(status=200, data="successfully deleted tag")
async def remove_command(self, command_id: VarChar32):
# todo auth
await self.app.pool.execute(
"""
DELETE FROM bot_commands WHERE command_id = $1;
""", command_id)
return StandardResponse(
status=200,
data=f"successfully deleted command if exists {command_id}",
)
async def remove_item(self, user_id: int, tag_id: str, tracking_id: UUID4):
""" Remove an item from a given tag for the given user. """
await self.app.pool.execute(
"""
DELETE FROM user_tracking_items
WHERE
user_id = $1 AND
tag_id = $2 AND
id = $3;
""", user_id, tag_id, tracking_id)
return StandardResponse(status=200, data="item deleted if exists")
async def get_genre_with_name(self, genre_name: str):
row = await self.app.pool.fetchrow(
"""
SELECT id, name FROM api_genres WHERE name = $1;
""", genre_name)
if row is None:
return StandardResponse(
status=404,
data=f"genre does not exist with name {genre_name!r}",
).into_response()
return GenreResponse(status=200, data=dict(row)) # noqa
async def add_aliases(self, user_id: int, payload: CommandAlias):
""" Adds an alias for the given user. """
# todo auth
fut = self.app.pool.execute(
"""
INSERT INTO user_command_aliases (user_id, command_id, alias)
VALUES ($1, $2, $3);
""", user_id, payload.command_id, payload.alias)
try:
await fut
except asyncpg.ForeignKeyViolationError:
return StandardResponse(
status=422,
data=
f"command with command id {payload.command_id} does not exist",
).into_response()
return StandardResponse(
status=200,
data=f"alias added for command id {payload.command_id}",
)
async def get_news_hook(self, guild_id: int):
row = await self.app.pool.fetchrow("""
SELECT
webhook_url
FROM guild_events_hooks_news
WHERE guild_id = $1;
""", guild_id)
if row is None:
return StandardResponse(
status=404,
data=f"no news hooks exist for {guild_id}",
).into_response()
return EventHook(**row)
async def update_release_hook(self, payload: EventHook):
# todo auth
row = await self.app.pool.fetchrow(
"""
INSERT INTO guild_events_hooks_release (
guild_id,
webhook_url
) VALUES ($1, $2)
ON CONFLICT (guild_id)
DO UPDATE
SET webhook_url = excluded.webhook_url
RETURNING guild_id;
""",
int(payload.guild_id), payload.webhook_url
)
return StandardResponse(status=200, data=f"successfully added hook for {dict(row)}")
async def create_tag(
self,
user_id: int,
tag_id: str,
payload: TagCreationPayload,
):
""" Creates a tag for a given user. """
# todo auth
await self.app.pool.execute(
"""
INSERT INTO user_tracking_tags (user_id, tag_id, tag_name, description)
VALUES ($1, $2, $3, $4)
ON CONFLICT (user_id, tag_id)
DO UPDATE SET description = EXCLUDED.description;
""", user_id, tag_id, payload.tag_name, payload.description)
return StandardResponse(status=200,
data="successfully updated / created tag")
async def get_manga_with_id(self, manga_id: str):
row = await self.app.pool.fetchrow(
"""
SELECT
id,
title,
description,
rating,
img_url,
link,
array(SELECT name FROM api_genres WHERE id & api_manga_data.genres != 0) as genres
FROM api_manga_data
WHERE id = $1;
""", manga_id)
if row is None:
return StandardResponse(
status=404,
data=f"no manga found with id: {manga_id!r}",
).into_response()
return DataResponse(status=200, data=dict(row)) # noqa