`cypressaward/scraper/create-news-table.sql`

40 lines
-- News table for cy pres related articles
CREATE TABLE IF NOT EXISTS scraped_news (
  id SERIAL PRIMARY KEY,
  title TEXT NOT NULL,
  description TEXT,
  url TEXT NOT NULL UNIQUE,
  published_date DATE NOT NULL,
  author TEXT,
  category TEXT NOT NULL,
  source TEXT NOT NULL,
  keywords TEXT[],
  cy_pres_related BOOLEAN DEFAULT true,
  verified BOOLEAN DEFAULT true,
  hash TEXT UNIQUE,
  created_at TIMESTAMP DEFAULT NOW(),
  last_update TIMESTAMP DEFAULT NOW()
);

-- Index for faster searches
CREATE INDEX IF NOT EXISTS idx_scraped_news_category ON scraped_news(category);
CREATE INDEX IF NOT EXISTS idx_scraped_news_published_date ON scraped_news(published_date);
CREATE INDEX IF NOT EXISTS idx_scraped_news_source ON scraped_news(source);
CREATE INDEX IF NOT EXISTS idx_scraped_news_cy_pres ON scraped_news(cy_pres_related);

-- Add full text search capability
ALTER TABLE scraped_news ADD COLUMN IF NOT EXISTS search_vector tsvector;
CREATE INDEX IF NOT EXISTS idx_scraped_news_search ON scraped_news USING gin(search_vector);

-- Update search vector automatically
CREATE OR REPLACE FUNCTION update_news_search_vector() RETURNS trigger AS $$
BEGIN
  NEW.search_vector = to_tsvector('english', COALESCE(NEW.title, '') || ' ' || COALESCE(NEW.description, '') || ' ' || COALESCE(NEW.author, '') || ' ' || COALESCE(NEW.source, ''));
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS update_news_search_trigger ON scraped_news;
CREATE TRIGGER update_news_search_trigger
  BEFORE INSERT OR UPDATE ON scraped_news
  FOR EACH ROW EXECUTE FUNCTION update_news_search_vector();