import sqlite3 from sqlite3 import Error import os import functools import operator import re from Render import Article import Utils class Cache: def __init__(self, api): self.conn = None self.api = api file_path = os.path.expanduser("~") + '/.config/inomnibus/cache.db' create_categories = """create table if not exists categories ( id text primary key, name text not null, timestamp integer, date text ) """ create_feeds = """create table if not exists feeds ( id text primary key, name text not null, timestamp integer, date text, category_id text, foreign key (category_id) references categories (id) ) """ create_articles = """create table if not exists articles ( id text primary key, title text not null, timestamp integer, date text, content text, url text, is_read boolean, origin text, category_id text, foreign key (category_id) references categories (id), foreign key (origin) references feeds (id) ) """ create_favorites = """create table if not exists favorites ( id text primary key, title text not null, timestamp integer, date text, content text, url text, origin text, foreign key (origin) references feeds (id) ) """ create_links = """create table if not exists links ( id text, url text not null, foreign key (id) references articles (id) ) """ try: self.conn = sqlite3.connect(file_path) self.conn.cursor().execute(create_categories) self.conn.cursor().execute(create_feeds) self.conn.cursor().execute(create_articles) self.conn.cursor().execute(create_links) self.conn.cursor().execute(create_favorites) except Error as e: Utils.writeLog(e) def markStreamAsRead(self, streamId): cur = self.conn.cursor() if re.search("^feed/[0-9]+", streamId): table = "feeds" else: table = "categories" cur.execute("""select timestamp from """ + table + """ where id = ?""", (streamId,)) ts = cur.fetchone()[0] if self.api.markStreamAsRead(streamId, ts): if table == "feeds": cur.execute("""update articles set is_read = 1 where origin = ?""", (streamId,)) else: cur.execute("""update articles set is_read = 1 where category_id = ?""", (streamId,)) self.conn.commit() def getArticle(self, id, is_fav): cur = self.conn.cursor() if is_fav: cur.execute("""select title,content,url from favorites where id = ?""", (id,)) else: cur.execute("""select title,content,url from articles where id = ?""", (id,)) return cur.fetchone() def toggleArticleStatus(self, id): cur = self.conn.cursor() cur.execute("""update articles set is_read = not is_read where id = ?""", (id,)) cur.execute("""select is_read from articles where id = ?""", (id,)) is_read = cur.fetchone() if self.api.toggleArticleStatus(id, is_read): self.conn.commit() else: self.conn.rollback() def toggleArticleStarred(self, id): cur = self.conn.cursor() is_favorite = 1 cur.execute("""select id from favorites where id = ?""", (id,)) if bool(cur.fetchone()): cur.execute("""delete from favorites where id = ?""", (id,)) is_favorite = 0 else: cur.execute("""select title, timestamp, date, content, url, origin from articles where id = ?""", (id,)) title, timestamp, date, content, url, origin = cur.fetchone() cur.execute("""insert into favorites(id, title, timestamp, date, content, url, origin) values(:id, :title, :timestamp, :date ,:content, :url, :origin)""", {"id": id, "title": title, "timestamp": timestamp, "date": date, "content": content, "url": url, "origin": origin}) if self.api.toggleArticleStarred(id, is_favorite): self.conn.commit() else: self.conn.rollback() def getArticleLinks(self, id): cur = self.conn.cursor() cur.execute("""select url from links where id = ?""", (id,)) links = functools.reduce(operator.iconcat, cur.fetchall(), []) return links def getArticlesFromFeed(self, feed_id, is_read): cur = self.conn.cursor() cur.execute("""select * from articles where origin = ? and is_read = ? order by timestamp desc""", (feed_id, is_read,)) return cur.fetchall() def getArticlesFromCategory(self, category_id, is_read): cur = self.conn.cursor() if category_id == "Favorites": cur.execute("""select *, (select name from feeds where id = origin) as origin_name from favorites order by timestamp desc""") else: cur.execute("""select *, (select name from feeds where id = origin) as origin_name from articles where category_id = ? and is_read = ? order by timestamp desc""", (category_id, is_read,)) return cur.fetchall() def getCategories(self, show_read): cur = self.conn.cursor() # statement = """select * from categories where id = 'Favorites'""" statement = """select id, name, (select count(*) from favorites) as unread_count, timestamp, date from categories where id = 'Favorites' order by timestamp desc""" cur.execute(statement) favorites = cur.fetchone() statement = """select id, name, (select count(*) from articles where category_id = categories.id and is_read = ?) as unread_count, timestamp, date from categories where unread_count != 0 and id != 'Favorites' order by timestamp desc""" cur.execute(statement, (show_read,)) return [favorites, *cur.fetchall()] def getFeeds(self, category_id, show_read): statement = """select id, name, (select count(*) from articles where origin = feeds.id and is_read = ?) as unread_count, timestamp, date, category_id from feeds where category_id = ? and unread_count != 0 order by timestamp desc""" cur = self.conn.cursor() cur.execute(statement, (show_read, category_id)) return cur.fetchall() def refresh(self): # noqa timestamps = {} cur = self.conn.cursor() cur.execute("""select id, timestamp from categories""") for row in cur.fetchall(): timestamps[row[0]] = row[1] self.api.refresh() for item in self.api.unreadCounts.keys(): if item[0:13] != "user/-/label/": continue data = self.api.unreadCounts[item] cur.execute("""insert into categories(id,name,timestamp,date) values(:id,:name,:ts,:d) on conflict(id) do update set timestamp = :ts, date = :d; """, {"id": item, "name": item[13:], "ts": data[1], "d": data[2]}) self.conn.commit() for item in self.api.feeds: data = self.api.unreadCounts[item["id"]] cur.execute("""insert into feeds(id,name,timestamp,date,category_id) values(:id,:name,:ts,:d,:c_id) on conflict(id) do update set timestamp = :ts, date = :d,category_id = :c_id; """, {"id": item["id"], "name": item["title"], "ts": data[1], "d": data[2], "c_id": item["categories"][0]["id"]}) self.conn.commit() yield "Fetching Favorites" cur.execute("""select timestamp from categories where id = 'Favorites'""") favoritesTimestampTuple = cur.fetchone() if favoritesTimestampTuple is None: cur.execute("""insert into categories(id,name,timestamp,date) values('Favorites','Favorites',0,'')""") self.conn.commit() last_updated, last_updated_date, favorites = self.api.getFavorites() cur.execute("""update categories set timestamp = :ts, date = :d where id = 'Favorites'""", {"ts": last_updated, "d": last_updated_date}) self.conn.commit() for favorite in favorites: favObj = Article(favorite) cur.execute("""insert or ignore into favorites(id,title,timestamp,date,content,url,origin) values(:id,:t,:ts,:d,:c,:u,:o)""", {"id": favObj.id, "t": favObj.title, "ts": favObj.timestamp, "d": favObj.date, "c": favObj.text, "u": favObj.url, "o": favObj.origin}) for link in favObj.links: cur.execute("""insert or ignore into links(id,url) values(?,?)""", (favObj.id, link)) self.conn.commit() cur.execute("""select id, name from categories where id != 'Favorites'""") for row in cur.fetchall(): yield "Fetching category " + row[1] articles = self.api.articlesFromCategory(row[0]) for article in articles: articleObj = Article(article) cur.execute("""insert into articles(id,title,timestamp,date,content,url,is_read,origin,category_id) values(:id,:t,:ts,:d,:c,:u,:r,:o,:c_id) on conflict(id) do update set id = :id, title = :t, timestamp = :ts, date = :d, content = :c, url = :u, is_read = :r, category_id = :c_id, origin = :o; """, {"id": articleObj.id, "t": articleObj.title, "ts": articleObj.timestamp, "d": articleObj.date, "c": articleObj.text, "u": articleObj.url, "r": articleObj.is_read, "o": articleObj.origin, "c_id": row[0]}) for link in articleObj.links: cur.execute("""insert into links(id,url) values(?,?)""", (articleObj.id, link)) self.conn.commit()