import Database from 'better-sqlite3';
import path from 'path';
import os from 'os';
import { DbFeed, DbFeedItem } from 'shared-types';
// Database configuration
const DEFAULT_DB_DIR = path.join(os.homedir(), '.mcp-rss-crawler');
const DEFAULT_DB_FILE = path.join(DEFAULT_DB_DIR, 'feeds.db');
// Database connection options
export interface DbOptions {
dbPath?: string;
readonly?: boolean;
}
// Create singleton database connections
let readOnlyDb: Database.Database | null = null;
let writeDb: Database.Database | null = null;
/**
* Get a read-only database connection
*/
export function getDb(options: DbOptions = {}): Database.Database {
const dbPath = options.dbPath || process.env.DB_FILE || DEFAULT_DB_FILE;
if (!readOnlyDb) {
try {
readOnlyDb = new Database(dbPath, { readonly: true });
console.log(`Connected to database at ${dbPath}`);
} catch (error) {
console.error('Error connecting to database:', error);
throw new Error('Failed to connect to the database');
}
}
return readOnlyDb;
}
/**
* Get a writable database connection
*/
export function getWriteDb(options: DbOptions = {}): Database.Database {
const dbPath = options.dbPath || process.env.DB_FILE || DEFAULT_DB_FILE;
if (!writeDb) {
try {
writeDb = new Database(dbPath);
console.log(`Connected to database for writing at ${dbPath}`);
} catch (error) {
console.error('Error connecting to database for writing:', error);
throw new Error('Failed to connect to the database for writing');
}
}
return writeDb;
}
/**
* Close all database connections
*/
export function closeDb(): void {
if (readOnlyDb) {
readOnlyDb.close();
readOnlyDb = null;
}
if (writeDb) {
writeDb.close();
writeDb = null;
}
}
// Feed operations
export const feedOperations = {
/**
* Get all feeds
*/
getAllFeeds(options: DbOptions = {}): DbFeed[] {
const db = getDb(options);
return db.prepare('SELECT * FROM feeds ORDER BY category, name').all() as DbFeed[];
},
/**
* Get feeds by category
*/
getFeedsByCategory(category: string, options: DbOptions = {}): DbFeed[] {
const db = getDb(options);
return db.prepare('SELECT * FROM feeds WHERE category = ? ORDER BY name').all(category) as DbFeed[];
},
/**
* Get a feed by ID
*/
getFeedById(id: string, options: DbOptions = {}): DbFeed | null {
const db = getDb(options);
return db.prepare('SELECT * FROM feeds WHERE id = ?').get(id) as DbFeed | null;
},
/**
* Add a new feed
*/
addFeed(feed: Omit<DbFeed, 'id'>, options: DbOptions = {}): string {
const db = getWriteDb(options);
const id = crypto.randomUUID();
db.prepare(`
INSERT INTO feeds (id, url, name, category, last_updated)
VALUES (?, ?, ?, ?, ?)
`).run(id, feed.url, feed.name, feed.category || null, feed.last_updated || Date.now());
return id;
},
/**
* Update a feed
*/
updateFeed(feed: DbFeed, options: DbOptions = {}): void {
const db = getWriteDb(options);
db.prepare(`
UPDATE feeds
SET url = ?, name = ?, category = ?, last_updated = ?
WHERE id = ?
`).run(feed.url, feed.name, feed.category || null, feed.last_updated, feed.id);
},
/**
* Delete a feed
*/
deleteFeed(id: string, options: DbOptions = {}): void {
const db = getWriteDb(options);
// Start a transaction to delete feed and its items
const transaction = db.transaction(() => {
// Delete items first (foreign key constraint)
db.prepare('DELETE FROM items WHERE feed_id = ?').run(id);
// Delete the feed
db.prepare('DELETE FROM feeds WHERE id = ?').run(id);
});
transaction();
}
};
// Category operations
export const categoryOperations = {
/**
* Get all categories
*/
getAllCategories(options: DbOptions = {}): string[] {
const db = getDb(options);
const categories = db.prepare('SELECT DISTINCT category FROM feeds WHERE category IS NOT NULL').all() as { category: string }[];
return categories.map(c => c.category);
}
};
// Item operations
export const itemOperations = {
/**
* Get items from a specific feed
*/
getItemsByFeed(feedId: string, limit: number = 50, options: DbOptions = {}): DbFeedItem[] {
const db = getDb(options);
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 DbFeedItem[];
},
/**
* Get recent items
*/
getRecentItems(limit: number = 50, options: DbOptions = {}): DbFeedItem[] {
const db = getDb(options);
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 DbFeedItem[];
},
/**
* Get items by category
*/
getItemsByCategory(category: string, limit: number = 50, options: DbOptions = {}): DbFeedItem[] {
const db = getDb(options);
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 DbFeedItem[];
},
/**
* Search items
*/
searchItems(query: string, limit: number = 50, options: DbOptions = {}): DbFeedItem[] {
const db = getDb(options);
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 DbFeedItem[];
},
/**
* Get categories for an item
*/
getItemCategories(itemId: string, options: DbOptions = {}): string[] {
const db = getDb(options);
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
*/
getItemById(itemId: string, options: DbOptions = {}): DbFeedItem | null {
const db = getDb(options);
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 DbFeedItem | null;
if (item) {
item.categories = this.getItemCategories(itemId, options);
}
return item;
},
/**
* Add a new item
*/
addItem(item: Omit<DbFeedItem, 'id'>, options: DbOptions = {}): string {
const db = getWriteDb(options);
const id = item.id || crypto.randomUUID();
// Start a transaction
const transaction = db.transaction(() => {
// Insert the item
db.prepare(`
INSERT INTO items (id, feed_id, title, link, summary, content, published, author)
VALUES (?, ?, ?, ?, ?, ?, ?, ?)
`).run(
id,
item.feed_id,
item.title,
item.link || '',
item.summary || null,
item.content || null,
item.published,
item.author || null
);
// Insert categories if any
if (item.categories && item.categories.length > 0) {
const insertCategory = db.prepare('INSERT INTO categories (item_id, category) VALUES (?, ?)');
for (const category of item.categories) {
insertCategory.run(id, category);
}
}
});
transaction();
return id;
}
};
// Export all operations
export default {
getDb,
getWriteDb,
closeDb,
feeds: feedOperations,
categories: categoryOperations,
items: itemOperations
};
