From cd5836ded746ba67159ec9014b75c3dc07ddc48c Mon Sep 17 00:00:00 2001 From: Pinapelz Date: Wed, 3 Dec 2025 15:33:14 -0800 Subject: change format to use 573-updates middleware as permalink --- database.py | 168 +++++++++++++++--------------------------------------------- 1 file changed, 42 insertions(+), 126 deletions(-) (limited to 'database.py') diff --git a/database.py b/database.py index 35d239f..8ab2ab0 100644 --- a/database.py +++ b/database.py @@ -1,8 +1,6 @@ import sqlite3 -import json from datetime import datetime, timedelta -from typing import List, Dict, Optional -import os +from typing import Optional class DatabaseManager: def __init__(self, db_path: str = "rasis.db"): @@ -10,160 +8,78 @@ class DatabaseManager: self.init_database() def init_database(self): - """Initialize the database with required tables""" + """Initialize the database with a simple posted_posts table""" with sqlite3.connect(self.db_path) as conn: cursor = conn.cursor() cursor.execute(""" - CREATE TABLE IF NOT EXISTS processed_hashes ( + CREATE TABLE IF NOT EXISTS posted_posts ( id INTEGER PRIMARY KEY AUTOINCREMENT, - hash TEXT UNIQUE NOT NULL, - created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP + archive_hash TEXT UNIQUE NOT NULL, + posted_at TIMESTAMP NOT NULL ) """) cursor.execute(""" - CREATE TABLE IF NOT EXISTS post_queue ( - id INTEGER PRIMARY KEY AUTOINCREMENT, - post_data TEXT NOT NULL, - content TEXT NOT NULL, - created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, - posted_at TIMESTAMP NULL, - status TEXT DEFAULT 'pending' - ) - """) - cursor.execute(""" - CREATE TABLE IF NOT EXISTS posting_log ( - id INTEGER PRIMARY KEY AUTOINCREMENT, - posted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, - queue_id INTEGER, - FOREIGN KEY (queue_id) REFERENCES post_queue (id) - ) + CREATE INDEX IF NOT EXISTS idx_posted_at + ON posted_posts(posted_at) """) conn.commit() - def is_hash_processed(self, hash_value: str) -> bool: - """Check if a news post hash has already been processed""" + def is_posted(self, archive_hash: str) -> bool: + """Check if we've already posted this hash""" with sqlite3.connect(self.db_path) as conn: cursor = conn.cursor() - cursor.execute("SELECT 1 FROM processed_hashes WHERE hash = ?", (hash_value,)) + cursor.execute("SELECT 1 FROM posted_posts WHERE archive_hash = ?", (archive_hash,)) return cursor.fetchone() is not None - def add_processed_hash(self, hash_value: str): - """Add a hash to the processed hashes table""" - with sqlite3.connect(self.db_path) as conn: - cursor = conn.cursor() - cursor.execute( - "INSERT OR IGNORE INTO processed_hashes (hash) VALUES (?)", - (hash_value,) - ) - conn.commit() - - def add_to_queue(self, post_data: Dict, content: str) -> int: - """Add a post to the queue and return the queue ID""" - with sqlite3.connect(self.db_path) as conn: - cursor = conn.cursor() - cursor.execute( - "INSERT INTO post_queue (post_data, content) VALUES (?, ?)", - (json.dumps(post_data), content) - ) - conn.commit() - return cursor.lastrowid - - def get_pending_posts(self, limit: Optional[int] = None) -> List[Dict]: - """Get pending posts from the queue""" - with sqlite3.connect(self.db_path) as conn: - cursor = conn.cursor() - query = """ - SELECT id, post_data, content, created_at - FROM post_queue - WHERE status = 'pending' - ORDER BY created_at ASC - """ - if limit: - query += f" LIMIT {limit}" - - cursor.execute(query) - rows = cursor.fetchall() - - return [ - { - 'id': row[0], - 'post_data': json.loads(row[1]), - 'content': row[2], - 'created_at': row[3] - } - for row in rows - ] - - def mark_post_as_posted(self, queue_id: int): - """Mark a queued post as posted""" + def mark_as_posted(self, archive_hash: str): + """Mark a post as posted""" with sqlite3.connect(self.db_path) as conn: cursor = conn.cursor() - now = datetime.now().isoformat() - cursor.execute( - "UPDATE post_queue SET status = 'posted', posted_at = ? WHERE id = ?", - (now, queue_id) - ) cursor.execute( - "INSERT INTO posting_log (queue_id) VALUES (?)", - (queue_id,) + "INSERT OR IGNORE INTO posted_posts (archive_hash, posted_at) VALUES (?, ?)", + (archive_hash, datetime.now().isoformat()) ) conn.commit() - def get_posts_in_last_hour(self) -> int: - """Get the number of posts made in the last hour""" - one_hour_ago = (datetime.now() - timedelta(hours=1)).isoformat() - + def get_posts_count_last_hour(self) -> int: + """How many posts did we make in the last hour?""" + one_hour_ago = datetime.now() - timedelta(hours=1) with sqlite3.connect(self.db_path) as conn: cursor = conn.cursor() cursor.execute( - "SELECT COUNT(*) FROM posting_log WHERE posted_at >= ?", - (one_hour_ago,) + "SELECT COUNT(*) FROM posted_posts WHERE posted_at >= ?", + (one_hour_ago.isoformat(),) ) return cursor.fetchone()[0] def can_post_more(self, max_per_hour: int) -> bool: - """Check if we can post more based on rate limit""" - return self.get_posts_in_last_hour() < max_per_hour - - def get_queue_stats(self) -> Dict: - """Get statistics about the queue""" - with sqlite3.connect(self.db_path) as conn: - cursor = conn.cursor() - - # Get pending count - cursor.execute("SELECT COUNT(*) FROM post_queue WHERE status = 'pending'") - pending = cursor.fetchone()[0] - - # Get posted count - cursor.execute("SELECT COUNT(*) FROM post_queue WHERE status = 'posted'") - posted = cursor.fetchone()[0] - - # Get posts in last hour - posts_last_hour = self.get_posts_in_last_hour() - - return { - 'pending': pending, - 'posted': posted, - 'posts_last_hour': posts_last_hour - } - - def cleanup_old_data(self, days_to_keep: int = 30): - """Clean up old data to keep database size manageable""" - cutoff_date = (datetime.now() - timedelta(days=days_to_keep)).isoformat() - + """Can we post more within the rate limit?""" + return self.get_posts_count_last_hour() < max_per_hour + + def get_next_post_time(self, max_per_hour: int) -> Optional[datetime]: + """Get the time when the next post can be made""" + if self.can_post_more(max_per_hour): + return None # Can post now + one_hour_ago = datetime.now() - timedelta(hours=1) with sqlite3.connect(self.db_path) as conn: cursor = conn.cursor() - - # Clean up old posted posts cursor.execute( - "DELETE FROM post_queue WHERE status = 'posted' AND posted_at < ?", - (cutoff_date,) + "SELECT posted_at FROM posted_posts WHERE posted_at >= ? ORDER BY posted_at ASC LIMIT 1", + (one_hour_ago.isoformat(),) ) - - # Clean up old posting logs + result = cursor.fetchone() + if result: + oldest_post_time = datetime.fromisoformat(result[0]) + return oldest_post_time + timedelta(hours=1) + return None + + def cleanup_old_data(self, days_to_keep: int = 90): + """Optional: Clean up very old entries to keep DB small""" + cutoff_date = datetime.now() - timedelta(days=days_to_keep) + with sqlite3.connect(self.db_path) as conn: + cursor = conn.cursor() cursor.execute( - "DELETE FROM posting_log WHERE posted_at < ?", - (cutoff_date,) + "DELETE FROM posted_posts WHERE posted_at < ?", + (cutoff_date.isoformat(),) ) - conn.commit() -- cgit v1.2.3