def _get_playlists(self, unique_identifier: str) -> List[str]:
with tcsql.connect(self.database_path) as manager:
tags = manager.execute(
'SELECT playlist FROM playlist_items WHERE comic = ?',
[unique_identifier]
)
return [t[0] for t in tags]
def _get_tags(self, unique_identifier: str) -> List[str]:
with tcsql.connect(self.database_path) as manager:
tags = manager.execute(
f'SELECT tag FROM comic_tags WHERE comic = ?',
[unique_identifier]
)
return [t[0] for t in tags]
def get_comic(self, unique_identifier: str) -> Optional[Comic]:
with tcsql.connect(self.database_path) as manager:
result = manager.execute(
f'''SELECT path, unique_identifier, title, author, category,
display_title, loved, date_added
FROM comics WHERE unique_identifier = ?''',
[unique_identifier]
)
if len(result) == 0:
return None
return Comic(result[0], self._get_tags(unique_identifier), self._get_playlists(unique_identifier))
def get_all_categories(self) -> Dict[str, int]:
with tcsql.connect(self.database_path) as manager:
return dict(manager.execute(
'SELECT category, COUNT(*) FROM comics GROUP BY category'
))
def get_all_authors(self) -> Dict[str, int]:
with tcsql.connect(self.database_path) as manager:
return dict(manager.execute(
'SELECT author, COUNT(*) FROM comics GROUP BY author'
))
def get_all_tags(self) -> Dict[str, int]:
with tcsql.connect(self.database_path) as manager:
return dict(manager.execute(
'SELECT tag, COUNT(*) FROM comic_tags GROUP BY tag'
))
def search_comics(
self,
search_string: Optional[str] = None,
categories: List[str] = [],
authors: List[str] = [],
tags: List[str] = [],
playlists: List[str] = [],
loved_only=False
) -> SearchResult:
''' returns a 4-tuple ([comics], {category_name: id}, {author_name: id}, {tag_name: id})
Note that authors, tags, categories need to be exact matches, but search string doesn't.'''
with tcsql.connect(self.database_path) as manager:
query = f"""
SELECT
comics.path,
comics.unique_identifier,
comics.title,
comics.author,
comics.category,
comics.display_title,
comics.loved,
comics.date_added,
group_concat(comic_tags.tag),
group_concat(playlist_items.playlist)
FROM
comics
LEFT OUTER JOIN
comic_tags ON comic_tags.comic = comics.unique_identifier
LEFT OUTER JOIN
playlist_items ON playlist_items.comic = comics.unique_identifier
WHERE
{ f'comics.category IN {tcsql.question_marks(len(categories))} AND ' if categories != [] else '' }
{ f'comics.author IN {tcsql.question_marks(len(authors))} AND ' if authors != [] else '' }
{ f'comic_tags.tag IN {tcsql.question_marks(len(tags))} AND ' if tags != [] else ''}
{ f'playlist_items.playlist IN {tcsql.question_marks(len(playlists))} AND ' if playlists != [] else ''}
{ 'loved = 1 AND ' if loved_only else '' }
{ '''(comics.display_title COLLATE UTF8_GENERAL_CI LIKE ? OR
(comics.display_title IS NULL AND comics.title COLLATE UTF8_GENERAL_CI LIKE ?) OR
comics.author COLLATE UTF8_GENERAL_CI LIKE ? OR
comic_tags.tag = ?) AND '''
if search_string is not None else '' }
active = 1
GROUP BY comics.unique_identifier
"""
arguments = categories + authors + tags + playlists
if search_string is not None:
arguments += [f'%{search_string}%'] * 3 + [search_string]
result = manager.execute(query, arguments)
comics = []
category_dict: Counter = Counter()
author_dict: Counter = Counter()
tag_dict: Counter = Counter()
for *data, tag_string, playlist_string in result:
assert len(data) == 8
tags = sorted(set(tag_string.split(','))) if tag_string is not None else []
playlists = sorted(set(playlist_string.split(','))) if playlist_string is not None else []
comic = Comic(data, tags, playlists)
comics.append(comic)
category_dict[comic.category] += 1
author_dict[comic.author] += 1
for tag in comic.tags:
tag_dict[tag] += 1
return comics, category_dict, author_dict, tag_dict