34 lines
1.3 KiB
SQL
34 lines
1.3 KiB
SQL
|
-- +goose Up
|
||
|
|
-- +goose StatementBegin
|
||
|
|
-- RSS-feed metadata for subscribed podcasts. One row per podcast
|
||
|
|
-- media_items row; the feed refresher polls feed_url every
|
||
|
|
-- refresh_interval_seconds and upserts new episodes into the existing
|
||
|
|
-- episodes table.
|
||
|
|
|
||
|
|
CREATE TABLE IF NOT EXISTS public.podcast_feeds (
|
||
|
|
media_item_id text PRIMARY KEY
|
||
|
|
REFERENCES public.media_items(content_id) ON DELETE CASCADE,
|
||
|
|
feed_url text NOT NULL,
|
||
|
|
etag text,
|
||
|
|
last_modified text,
|
||
|
|
last_refreshed_at timestamp with time zone,
|
||
|
|
last_refresh_error text,
|
||
|
|
refresh_interval_seconds integer NOT NULL DEFAULT 600,
|
||
|
|
created_at timestamp with time zone NOT NULL DEFAULT now(),
|
||
|
|
updated_at timestamp with time zone NOT NULL DEFAULT now()
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE UNIQUE INDEX IF NOT EXISTS idx_podcast_feeds_feed_url
|
||
|
|
ON public.podcast_feeds (feed_url);
|
||
|
|
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_podcast_feeds_due_for_refresh
|
||
|
|
ON public.podcast_feeds (last_refreshed_at);
|
||
|
|
-- +goose StatementEnd
|
||
|
|
|
||
|
|
-- +goose Down
|
||
|
|
-- +goose StatementBegin
|
||
|
|
DROP INDEX IF EXISTS public.idx_podcast_feeds_due_for_refresh;
|
||
|
|
DROP INDEX IF EXISTS public.idx_podcast_feeds_feed_url;
|
||
|
|
DROP TABLE IF EXISTS public.podcast_feeds;
|
||
|
|
-- +goose StatementEnd
|