c79eb6d76d
Features: - Add CLI with commands: start, add, remove, list, fetch, status, items - Auto-detect RSS format when adding feeds - Auto-run database migrations on startup - Extract full HTML content from RSS description field (NOS-style feeds) - Extract image URLs from RSS enclosure tags - Display images in terminal output with emoji - Include imageUrl in JSON formatter output Database: - Add image_url column to feed_items table - Update storage layer to persist imageUrl field Tests: - Add 10 CLI integration tests - Add 3 RSS parser tests for image/content extraction - Add 2 storage tests for imageUrl persistence Dependencies: - Add commander for CLI framework All 144 tests passing
83 lines
3.0 KiB
TypeScript
83 lines
3.0 KiB
TypeScript
/**
|
|
* Database schema migrations.
|
|
* Creates tables idempotently.
|
|
*/
|
|
|
|
import type { Kysely } from 'kysely';
|
|
import type { Database } from './database.js';
|
|
|
|
export async function migrate(db: Kysely<Database>): Promise<void> {
|
|
// Create feed_items table
|
|
await db.schema
|
|
.createTable('feed_items')
|
|
.ifNotExists()
|
|
.addColumn('id', 'varchar(64)', (col) => col.primaryKey())
|
|
.addColumn('source', 'varchar(2048)', (col) => col.notNull())
|
|
.addColumn('title', 'varchar(512)', (col) => col.notNull())
|
|
.addColumn('url', 'varchar(2048)', (col) => col.notNull())
|
|
.addColumn('published_at', 'varchar(32)', (col) => col.notNull())
|
|
.addColumn('content', 'text')
|
|
.addColumn('summary', 'text')
|
|
.addColumn('image_url', 'varchar(2048)')
|
|
.addColumn('created_at', 'varchar(32)', (col) => col.notNull().defaultTo('CURRENT_TIMESTAMP'))
|
|
.execute();
|
|
|
|
// Create indexes for feed_items
|
|
await db.schema
|
|
.createIndex('idx_feed_items_source')
|
|
.ifNotExists()
|
|
.on('feed_items')
|
|
.column('source')
|
|
.execute();
|
|
|
|
await db.schema
|
|
.createIndex('idx_feed_items_published')
|
|
.ifNotExists()
|
|
.on('feed_items')
|
|
.column('published_at')
|
|
.execute();
|
|
|
|
// Create seen_ids table
|
|
await db.schema
|
|
.createTable('seen_ids')
|
|
.ifNotExists()
|
|
.addColumn('id', 'varchar(64)', (col) => col.primaryKey())
|
|
.addColumn('seen_at', 'varchar(32)', (col) => col.notNull().defaultTo('CURRENT_TIMESTAMP'))
|
|
.execute();
|
|
|
|
// Create feed_sources table
|
|
await db.schema
|
|
.createTable('feed_sources')
|
|
.ifNotExists()
|
|
.addColumn('id', 'varchar(64)', (col) => col.primaryKey())
|
|
.addColumn('url', 'varchar(2048)', (col) => col.notNull().unique())
|
|
.addColumn('name', 'varchar(256)')
|
|
.addColumn('format', 'varchar(10)', (col) => col.notNull())
|
|
.addColumn('poll_interval_ms', 'integer', (col) => col.notNull())
|
|
.addColumn('is_active', 'boolean', (col) => col.notNull().defaultTo(true))
|
|
.addColumn('last_fetched_at', 'varchar(32)')
|
|
.addColumn('last_success_at', 'varchar(32)')
|
|
.addColumn('consecutive_failures', 'integer', (col) => col.notNull().defaultTo(0))
|
|
.addColumn('created_at', 'varchar(32)', (col) => col.notNull().defaultTo('CURRENT_TIMESTAMP'))
|
|
.addColumn('updated_at', 'varchar(32)', (col) => col.notNull().defaultTo('CURRENT_TIMESTAMP'))
|
|
.execute();
|
|
|
|
// Create index on is_active for quick filtering
|
|
await db.schema
|
|
.createIndex('idx_feed_sources_active')
|
|
.ifNotExists()
|
|
.on('feed_sources')
|
|
.column('is_active')
|
|
.execute();
|
|
}
|
|
|
|
export async function reset(db: Kysely<Database>): Promise<void> {
|
|
// Drop tables (for testing)
|
|
await db.schema.dropIndex('idx_feed_sources_active').ifExists().execute();
|
|
await db.schema.dropTable('feed_sources').ifExists().execute();
|
|
await db.schema.dropTable('seen_ids').ifExists().execute();
|
|
await db.schema.dropIndex('idx_feed_items_published').ifExists().execute();
|
|
await db.schema.dropIndex('idx_feed_items_source').ifExists().execute();
|
|
await db.schema.dropTable('feed_items').ifExists().execute();
|
|
}
|