diff options
Diffstat (limited to 'tagrss.py')
| -rw-r--r-- | tagrss.py | 125 |
1 files changed, 101 insertions, 24 deletions
@@ -18,24 +18,51 @@ class FeedFetchError(Exception): super().__init__(f"Get {feed_source} returned HTTP {status_code}") +class SqliteMissingForeignKeySupportError(Exception): + pass + + class TagRss: def __init__(self, *, storage_path: str | pathlib.Path): self.connection: sqlite3.Connection = sqlite3.connect(storage_path) + with self.connection: self.connection.executescript( """ -CREATE TABLE IF NOT EXISTS feeds(id INTEGER PRIMARY KEY, source TEXT UNIQUE, title TEXT); -CREATE INDEX IF NOT EXISTS feed_source ON feeds(source); +PRAGMA foreign_keys = ON; -CREATE TABLE IF NOT EXISTS feed_tags(feed_id INTEGER, tag TEXT); -CREATE INDEX IF NOT EXISTS feed_tags_feed_id ON feed_tags(feed_id); +CREATE TABLE IF NOT EXISTS + feeds( + id INTEGER PRIMARY KEY, + source TEXT UNIQUE, + title TEXT + ) STRICT; -CREATE TABLE IF NOT EXISTS entries(id INTEGER PRIMARY KEY, feed_id INTEGER, title TEXT, link TEXT, epoch_published INTEGER, epoch_updated INTEGER, epoch_downloaded INTEGER); -CREATE INDEX IF NOT EXISTS entry_epoch_downloaded ON entries(epoch_downloaded); +CREATE TABLE IF NOT EXISTS + feed_tags( + feed_id INTEGER REFERENCES feeds(id) ON DELETE CASCADE, + tag TEXT + ) STRICT; +CREATE INDEX IF NOT EXISTS idx_feed_tags__feed_id__tag ON feed_tags(feed_id, tag); +CREATE INDEX IF NOT EXISTS idx_feed_tags__tag__feed_id ON feed_tags(tag, feed_id); + +CREATE TABLE IF NOT EXISTS + entries( + id INTEGER PRIMARY KEY, + feed_id INTEGER REFERENCES feeds(id) ON DELETE CASCADE, + title TEXT, + link TEXT, + epoch_published INTEGER, + epoch_updated INTEGER, + epoch_downloaded INTEGER + ) STRICT; +CREATE INDEX IF NOT EXISTS idx_entries__epoch_downloaded ON entries(epoch_downloaded); """ ) + if (1,) not in self.connection.execute("PRAGMA foreign_keys;").fetchmany(1): + raise SqliteMissingForeignKeySupportError - def add_feed(self, *, feed_source: str, tags: tuple[str]): + def add_feed(self, feed_source: str, tags: list[str]) -> None: response = requests.get(feed_source) if response.status_code != requests.codes.ok: raise FeedFetchError(feed_source, response.status_code) @@ -44,7 +71,8 @@ CREATE INDEX IF NOT EXISTS entry_epoch_downloaded ON entries(epoch_downloaded); except KeyError: base: str = feed_source parsed = feedparser.parse( - io.BytesIO(bytes(response.text, encoding="utf-8")), response_headers={"Content-Location": base} + io.BytesIO(bytes(response.text, encoding="utf-8")), + response_headers={"Content-Location": base}, ) with self.connection: feed_title: str = parsed.feed.get("title", "") @@ -52,36 +80,37 @@ CREATE INDEX IF NOT EXISTS entry_epoch_downloaded ON entries(epoch_downloaded); self.connection.execute( "INSERT INTO feeds(source, title) VALUES(?, ?);", (feed_source, feed_title), - ) + ) # Note: ensure no more INSERTs between this and the last_insert_rowid() call except sqlite3.IntegrityError: raise FeedAlreadyAddedError feed_id: int = int( - self.connection.execute( - "SELECT id FROM feeds WHERE source = ?;", (feed_source,) - ).fetchone()[0] + self.connection.execute("SELECT last_insert_rowid();").fetchone()[0] ) self.connection.executemany( - f"INSERT INTO feed_tags(feed_id, tag) VALUES({feed_id}, ?);", tuple(((tag,) for tag in tags)) + "INSERT INTO feed_tags(feed_id, tag) VALUES(?, ?);", + ((feed_id, tag) for tag in tags), ) for entry in reversed(parsed.entries): - link: str = entry.get("link", "") + link: str = entry.get("link", None) + title: str = entry.get("title", None) try: epoch_published: typing.Optional[int] = calendar.timegm( entry.get("published_parsed", None) ) - except ValueError: + except TypeError: epoch_published = None try: epoch_updated: typing.Optional[int] = calendar.timegm( entry.get("updated_parsed", None) ) - except ValueError: + except TypeError: epoch_updated = None self.connection.execute( - "INSERT INTO entries(feed_id, title, link, epoch_published, epoch_updated, epoch_downloaded) VALUES(?, ?, ?, ?, ?, ?);", + "INSERT INTO entries(feed_id, title, link, epoch_published, epoch_updated, epoch_downloaded) \ + VALUES(?, ?, ?, ?, ?, ?);", ( feed_id, - feed_title, + title, link, epoch_published, epoch_updated, @@ -89,15 +118,16 @@ CREATE INDEX IF NOT EXISTS entry_epoch_downloaded ON entries(epoch_downloaded); ), ) - def get_entries(self, *, limit: int): + def get_entries(self, *, limit: int) -> list[dict[str, typing.Any]]: with self.connection: - result = self.connection.execute( - "SELECT feed_id, title, link, epoch_published, epoch_updated FROM entries ORDER BY epoch_downloaded DESC LIMIT ?;", + resp = self.connection.execute( + "SELECT feed_id, title, link, epoch_published, epoch_updated FROM entries \ + ORDER BY epoch_downloaded DESC LIMIT ?;", (limit,), ).fetchall() entries = [] - for entry in result: + for entry in resp: entries.append( { "feed_id": entry[0], @@ -108,6 +138,53 @@ CREATE INDEX IF NOT EXISTS entry_epoch_downloaded ON entries(epoch_downloaded); } ) return entries - def get_feed_tags(self, feed_id: int) -> tuple[str]: + + def get_feed_source(self, feed_id: int) -> str: + with self.connection: + return self.connection.execute( + "SELECT source FROM feeds WHERE id = ?;", (feed_id,) + ).fetchone()[0] + + def get_feed_title(self, feed_id: int) -> str: + with self.connection: + return self.connection.execute( + "SELECT title FROM feeds WHERE id = ?;", (feed_id,) + ).fetchone()[0] + + def get_feed_tags(self, feed_id: int) -> list[str]: + with self.connection: + return [ + t[0] + for t in self.connection.execute( + "SELECT tag FROM feed_tags WHERE feed_id = ?;", (feed_id,) + ).fetchall() + ] + + def set_feed_source(self, feed_id: int, feed_source: str): + with self.connection: + self.connection.execute( + "UPDATE feeds SET source = ? WHERE id = ?;", (feed_source, feed_id) + ) + + def set_feed_title(self, feed_id: int, feed_title: str): + with self.connection: + self.connection.execute( + "UPDATE feeds SET title = ? WHERE id = ?;", (feed_title, feed_id) + ) + + def set_feed_tags(self, feed_id: int, feed_tags: list[str]): + with self.connection: + self.connection.execute("DELETE FROM feed_tags WHERE feed_id = ?;", (feed_id,)) + self.connection.executemany( + "INSERT INTO feed_tags(feed_id, tag) VALUES(?, ?);", + ((feed_id, tag) for tag in feed_tags), + ) + + def delete_feed(self, feed_id: int) -> None: + with self.connection: + self.connection.execute("DELETE FROM feeds WHERE id = ?;", (feed_id,)) + + def close(self) -> None: with self.connection: - return tuple((t[0] for t in self.connection.execute("SELECT tag FROM feed_tags WHERE feed_id = ?;", (feed_id,)).fetchall()))
\ No newline at end of file + self.connection.execute("PRAGMA optimize;") + self.connection.close() |