76 lines
3.1 KiB
Python
76 lines
3.1 KiB
Python
"""Add briefing tables: rss_feeds, rss_items, weather_cache, conversation briefing columns."""
|
|
|
|
from alembic import op
|
|
|
|
revision = "0026"
|
|
down_revision = "0025"
|
|
|
|
|
|
def upgrade() -> None:
|
|
# Extend conversations table
|
|
op.execute("""
|
|
ALTER TABLE conversations
|
|
ADD COLUMN IF NOT EXISTS conversation_type TEXT NOT NULL DEFAULT 'chat',
|
|
ADD COLUMN IF NOT EXISTS briefing_date DATE
|
|
""")
|
|
op.execute("CREATE INDEX IF NOT EXISTS ix_conversations_type ON conversations(conversation_type)")
|
|
|
|
# RSS feeds
|
|
op.execute("""
|
|
CREATE TABLE IF NOT EXISTS rss_feeds (
|
|
id SERIAL PRIMARY KEY,
|
|
user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
url TEXT NOT NULL,
|
|
title TEXT NOT NULL DEFAULT '',
|
|
category TEXT,
|
|
last_fetched_at TIMESTAMPTZ,
|
|
CONSTRAINT uq_rss_feeds_user_url UNIQUE (user_id, url)
|
|
)
|
|
""")
|
|
op.execute("CREATE INDEX IF NOT EXISTS ix_rss_feeds_user_id ON rss_feeds(user_id)")
|
|
|
|
# RSS items
|
|
op.execute("""
|
|
CREATE TABLE IF NOT EXISTS rss_items (
|
|
id SERIAL PRIMARY KEY,
|
|
feed_id INTEGER NOT NULL REFERENCES rss_feeds(id) ON DELETE CASCADE,
|
|
guid TEXT NOT NULL,
|
|
title TEXT NOT NULL DEFAULT '',
|
|
url TEXT NOT NULL DEFAULT '',
|
|
published_at TIMESTAMPTZ,
|
|
content TEXT NOT NULL DEFAULT '',
|
|
fetched_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
CONSTRAINT uq_rss_items_feed_guid UNIQUE (feed_id, guid)
|
|
)
|
|
""")
|
|
op.execute("CREATE INDEX IF NOT EXISTS ix_rss_items_feed_id ON rss_items(feed_id)")
|
|
op.execute("CREATE INDEX IF NOT EXISTS ix_rss_items_published_at ON rss_items(published_at)")
|
|
|
|
# Weather cache
|
|
op.execute("""
|
|
CREATE TABLE IF NOT EXISTS weather_cache (
|
|
id SERIAL PRIMARY KEY,
|
|
user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
location_key TEXT NOT NULL,
|
|
location_label TEXT NOT NULL DEFAULT '',
|
|
forecast_json JSONB,
|
|
previous_json JSONB,
|
|
fetched_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
CONSTRAINT uq_weather_cache_user_location UNIQUE (user_id, location_key)
|
|
)
|
|
""")
|
|
op.execute("CREATE INDEX IF NOT EXISTS ix_weather_cache_user_id ON weather_cache(user_id)")
|
|
|
|
|
|
def downgrade() -> None:
|
|
op.execute("DROP INDEX IF EXISTS ix_weather_cache_user_id")
|
|
op.execute("DROP TABLE IF EXISTS weather_cache")
|
|
op.execute("DROP INDEX IF EXISTS ix_rss_items_published_at")
|
|
op.execute("DROP INDEX IF EXISTS ix_rss_items_feed_id")
|
|
op.execute("DROP TABLE IF EXISTS rss_items")
|
|
op.execute("DROP INDEX IF EXISTS ix_rss_feeds_user_id")
|
|
op.execute("DROP TABLE IF EXISTS rss_feeds")
|
|
op.execute("DROP INDEX IF EXISTS ix_conversations_type")
|
|
op.execute("ALTER TABLE conversations DROP COLUMN IF EXISTS briefing_date")
|
|
op.execute("ALTER TABLE conversations DROP COLUMN IF EXISTS conversation_type")
|