44 lines
1.9 KiB
SQL
44 lines
1.9 KiB
SQL
-- =====================================================================
|
|
-- Migration 004: Creates News Table for Municipality Announcements
|
|
-- =====================================================================
|
|
|
|
|
|
-- ---------------------------------------------------------------------
|
|
-- Block 1: Creates Table "news"
|
|
-- ---------------------------------------------------------------------
|
|
CREATE TABLE IF NOT EXISTS news (
|
|
news_id SERIAL PRIMARY KEY,
|
|
municipality_id INTEGER NOT NULL REFERENCES municipalities(municipality_id) ON DELETE CASCADE,
|
|
title VARCHAR(255) NOT NULL,
|
|
content TEXT NOT NULL,
|
|
author_name VARCHAR(100) NOT NULL DEFAULT 'Stadtverwaltung',
|
|
published_at TIMESTAMP NOT NULL DEFAULT NOW(),
|
|
created_at TIMESTAMP NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMP NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
|
|
-- ---------------------------------------------------------------------
|
|
-- Block 2: Trigger Functions
|
|
-- ---------------------------------------------------------------------
|
|
|
|
-- Automatically Refresh updated_at on every UPDATE.
|
|
CREATE TRIGGER set_news_updated_at
|
|
BEFORE UPDATE ON news
|
|
FOR EACH ROW
|
|
EXECUTE FUNCTION set_updated_at();
|
|
|
|
|
|
-- ---------------------------------------------------------------------
|
|
-- Block 3 Indexes for fast Queries
|
|
-- ---------------------------------------------------------------------
|
|
CREATE INDEX idx_news_municipality ON news(municipality_id);
|
|
|
|
|
|
-- ---------------------------------------------------------------------
|
|
-- Block 4: Seed Data — Initial News Article
|
|
-- ---------------------------------------------------------------------
|
|
INSERT INTO news (municipality_id, title, content)
|
|
SELECT municipality_id, 'Mitmachkarte gestartet',
|
|
'Die Mitmachkarte als Bürgerbeteiligungsportal der Stadt Lohne (Oldenburg) wird nun freigeschaltet. Wir freuen uns auf Ihre Hinweise und Vorschläge!'
|
|
FROM municipalities WHERE slug = 'lohne'; |