gemini-antenna/antennaDB.py

#!/usr/bin/env python3
# vim: tabstop=4 shiftwidth=4 expandtab

import sqlite3
import os
from multiFeedParsing import FeedEntry,TwtxtEntry

# There's a lot of opening and closing going on here,
# because several processes will be sharing one sqlite3
# db, which is just a single file. We want to hog it
# as little as possible to minimize the risk of
# collisions. Some errors are tolerable; this is a
# good enough effort.
class AntennaDB():

    def __init__(self, dbPath="antenna.sqlite"):
        self.dbPath = dbPath

    def createDB(self):
        connection = sqlite3.connect(self.dbPath)
        cursor = connection.cursor()
        cursor.execute("CREATE TABLE IF NOT EXISTS entries (feedurl text, author text, updated datetime, title text, link text primary key)")
        cursor.execute("CREATE TABLE IF NOT EXISTS twtxt (feedurl text, author text, posted datetime, twt text)")
        connection.close()

    def getEntries(self):
        connection = sqlite3.connect(self.dbPath)
        cursor = connection.cursor()
        cursor.execute("SELECT feedurl, author, updated, title, link FROM entries ORDER BY updated DESC")
        results = []
        for result in cursor.fetchall():
            results.append(FeedEntry(feedurl = result[0], author = result[1], updated = result[2], title = result[3], link = result[4]))
        connection.close()
        return results

    def getTwts(self):
        connection = sqlite3.connect(self.dbPath)
        cursor = connection.cursor()
        cursor.execute("SELECT feedurl, author, posted, twt FROM twtxt ORDER BY posted DESC")
        results = []
        for result in cursor.fetchall():
            results.append(TwtxtEntry(feedurl = result[0], author = result[1], posted = result[2], twt = result[3]))
        connection.close()
        return results

    def deleteFeeds(self, urls):
        urlTuples = []
        for url in urls:
            urlTuples.append((url,))
        connection = sqlite3.connect(self.dbPath)
        cursor = connection.cursor()
        cursor.executemany("DELETE FROM entries WHERE feedurl LIKE ?", urlTuples)
        cursor.executemany("DELETE FROM twtxt WHERE feedurl LIKE ?", urlTuples)
        connection.commit()
        connection.close()

    # UPSERTs entries into the DB, if they're not too old. Returns how many entries were upserted.
    def insertFeedEntries(self, entries, limit=0):
        entries = [e for e in entries if e.updated > limit]
        entrytuples = []
        for entry in entries:
            entrytuples.append((entry.feedurl, entry.author, entry.updated, entry.title, entry.link))
        connection = sqlite3.connect(self.dbPath)
        cursor = connection.cursor()
        cursor.executemany("INSERT INTO entries (feedurl, author, updated, title, link) VALUES (?,?,?,?,?) ON CONFLICT (link) DO UPDATE SET author = excluded.author, updated = excluded.updated, title = excluded.title", entrytuples)
        connection.commit()
        connection.close()
        return len(entries)

    def insertTwtxtEntries(self, entries, limit=0):
        entries = [e for e in entries if e.posted > limit]
        entrytuples = []
        for entry in entries:
            entrytuples.append((entry.feedurl, entry.author, entry.posted, entry.twt))
        connection = sqlite3.connect(self.dbPath)
        cursor = connection.cursor()
        cursor.executemany("INSERT INTO twtxt (feedurl, author, posted, twt) VALUES (?,?,?,?)", entrytuples)
        connection.commit()
        connection.close()
        return len(entries)

    def pruneDB(self, limit):
        connection = sqlite3.connect(self.dbPath)
        cursor = connection.cursor()
        cursor.execute("DELETE FROM entries WHERE updated < ?", (limit,))
        cursor.execute("DELETE FROM twtxt WHERE posted < ?", (limit,))
        connection.commit()
        connection.close()