db.ts•4.68 kB
import Database from 'better-sqlite3';
import path from 'path';
import os from 'os';
// Database file path - using the same path as the main application
const DB_DIR = process.env.DB_DIR || path.join(os.homedir(), '.mcp-rss-crawler');
const DB_FILE = process.env.DB_FILE || path.join(DB_DIR, 'feeds.db');
// Create singleton database connections
let readOnlyDb: Database.Database | null = null;
let writeDb: Database.Database | null = null;
export function getDb(): Database.Database {
if (!readOnlyDb) {
try {
readOnlyDb = new Database(DB_FILE, { readonly: true });
console.log(`Connected to database at ${DB_FILE}`);
} catch (error) {
console.error('Error connecting to database:', error);
throw new Error('Failed to connect to the database');
}
}
return readOnlyDb;
}
export function getWriteDb(): Database.Database {
if (!writeDb) {
try {
writeDb = new Database(DB_FILE);
console.log(`Connected to database for writing at ${DB_FILE}`);
} catch (error) {
console.error('Error connecting to database for writing:', error);
throw new Error('Failed to connect to the database for writing');
}
}
return writeDb;
}
// Feed types
export interface Feed {
id: string;
url: string;
name: string;
category: string | null;
last_updated: number;
}
// Item types
export interface FeedItem {
id: string;
feed_id: string;
title: string;
link: string;
summary: string | null;
content: string | null;
published: number;
author: string | null;
feed_title?: string;
feed_url?: string;
categories?: string[];
}
// Get all feeds
export function getAllFeeds(): Feed[] {
const db = getDb();
return db.prepare('SELECT * FROM feeds ORDER BY category, name').all() as Feed[];
}
// Get feeds by category
export function getFeedsByCategory(category: string): Feed[] {
const db = getDb();
return db.prepare('SELECT * FROM feeds WHERE category = ? ORDER BY name').all(category) as Feed[];
}
// Get all categories
export function getAllCategories(): string[] {
const db = getDb();
const categories = db.prepare('SELECT DISTINCT category FROM feeds WHERE category IS NOT NULL').all() as { category: string }[];
return categories.map(c => c.category);
}
// Get items from a specific feed
export function getItemsByFeed(feedId: string, limit: number = 50): FeedItem[] {
const db = getDb();
return db.prepare(`
SELECT i.*, f.name as feed_title, f.url as feed_url
FROM items i
JOIN feeds f ON i.feed_id = f.id
WHERE i.feed_id = ?
ORDER BY i.published DESC
LIMIT ?
`).all(feedId, limit) as FeedItem[];
}
// Get recent items
export function getRecentItems(limit: number = 50): FeedItem[] {
const db = getDb();
return db.prepare(`
SELECT i.*, f.name as feed_title, f.url as feed_url
FROM items i
JOIN feeds f ON i.feed_id = f.id
ORDER BY i.published DESC
LIMIT ?
`).all(limit) as FeedItem[];
}
// Get items by category
export function getItemsByCategory(category: string, limit: number = 50): FeedItem[] {
const db = getDb();
return db.prepare(`
SELECT i.*, f.name as feed_title, f.url as feed_url
FROM items i
JOIN feeds f ON i.feed_id = f.id
WHERE f.category = ?
ORDER BY i.published DESC
LIMIT ?
`).all(category, limit) as FeedItem[];
}
// Search items
export function searchItems(query: string, limit: number = 50): FeedItem[] {
const db = getDb();
const searchPattern = `%${query}%`;
return db.prepare(`
SELECT i.*, f.name as feed_title, f.url as feed_url
FROM items i
JOIN feeds f ON i.feed_id = f.id
WHERE i.title LIKE ? OR i.summary LIKE ? OR i.content LIKE ?
ORDER BY i.published DESC
LIMIT ?
`).all(searchPattern, searchPattern, searchPattern, limit) as FeedItem[];
}
// Get categories for an item
export function getItemCategories(itemId: string): string[] {
const db = getDb();
const rows = db.prepare('SELECT category FROM categories WHERE item_id = ?').all(itemId) as { category: string }[];
return rows.map(row => row.category);
}
// Get a single item by ID
export function getItemById(itemId: string): FeedItem | null {
const db = getDb();
const item = db.prepare(`
SELECT i.*, f.name as feed_title, f.url as feed_url
FROM items i
JOIN feeds f ON i.feed_id = f.id
WHERE i.id = ?
`).get(itemId) as FeedItem | null;
if (item) {
item.categories = getItemCategories(itemId);
}
return item;
}
// Close the database connections
export function closeDb(): void {
if (readOnlyDb) {
readOnlyDb.close();
readOnlyDb = null;
}
if (writeDb) {
writeDb.close();
writeDb = null;
}
}