169 lines
7.6 KiB
PL/PgSQL
169 lines
7.6 KiB
PL/PgSQL
-- =====================================================================
|
|
-- WebGIS Citizen Participation Portal — Initial Schema
|
|
-- Migration: 001_initial_schema.sql
|
|
-- Description: Creates Core Tables for a multi-tenant Citizen
|
|
-- Participation Platform with Point/Line/Polygon
|
|
-- Contributions, Voting, and Moderation Workflow.
|
|
-- =====================================================================
|
|
|
|
|
|
-- ---------------------------------------------------------------------
|
|
-- Block 1: Checks PostGIS Extension
|
|
-- ---------------------------------------------------------------------
|
|
CREATE EXTENSION IF NOT EXISTS postgis;
|
|
|
|
|
|
-- ---------------------------------------------------------------------
|
|
-- Block 2: Creates Table "municipalities"
|
|
-- One Row per Municipalitiy using the Portal (multi-tenant setup).
|
|
-- ---------------------------------------------------------------------
|
|
CREATE TABLE municipalities (
|
|
municipality_id SERIAL PRIMARY KEY,
|
|
name VARCHAR(100) NOT NULL UNIQUE, -- Municipalitiy Name
|
|
slug VARCHAR(50) NOT NULL UNIQUE, -- URL-safe Identifier, e.g. "lohne"
|
|
center_lat DOUBLE PRECISION NOT NULL, -- Map Center Latitude
|
|
center_lng DOUBLE PRECISION NOT NULL, -- Map Center Longitude
|
|
default_zoom SMALLINT NOT NULL DEFAULT 13, -- Map Default Zoom Level
|
|
logo_path VARCHAR(255), -- Relative Path to Municipality Logo
|
|
primary_color VARCHAR(7) DEFAULT '#6a6a6a', -- HexColor for UI Theme
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
COMMENT ON TABLE municipalities IS 'Configuration Per Municipality (Tenant) using the Citizen Participation Portal.';
|
|
|
|
|
|
-- ---------------------------------------------------------------------
|
|
-- Block 3: Table "contributions"
|
|
-- Aitizen and Administration Contributions as Points, Lines, and
|
|
-- Polygons stored together in one mixed-geometry Column.
|
|
-- ---------------------------------------------------------------------
|
|
CREATE TABLE contributions (
|
|
contribution_id SERIAL PRIMARY KEY,
|
|
municipality_id INTEGER NOT NULL REFERENCES municipalities(municipality_id) ON DELETE CASCADE,
|
|
geom GEOMETRY(Geometry, 4326) NOT NULL, -- Mixed Geometry: Point, Line, Polygon, ... (WGS84)
|
|
geom_type VARCHAR(20) NOT NULL, -- 'point' | 'line' | 'polygon'
|
|
category VARCHAR(50) NOT NULL, -- Contribution Category
|
|
title VARCHAR(200) NOT NULL,
|
|
description TEXT,
|
|
author_name VARCHAR(100) NOT NULL,
|
|
status VARCHAR(20) NOT NULL DEFAULT 'pending',
|
|
likes_count INTEGER NOT NULL DEFAULT 0,
|
|
dislikes_count INTEGER NOT NULL DEFAULT 0,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
|
|
CONSTRAINT contributions_geom_type_check
|
|
CHECK (geom_type IN ('point', 'line', 'polygon')),
|
|
CONSTRAINT contributions_status_check
|
|
CHECK (status IN ('pending', 'approved', 'rejected', 'in_progress', 'done'))
|
|
);
|
|
|
|
COMMENT ON TABLE contributions IS 'Citizen and Administration Contributions with mixed Geometry Types.';
|
|
|
|
|
|
-- ---------------------------------------------------------------------
|
|
-- Block 4: Indexes for fast Queries
|
|
-- ---------------------------------------------------------------------
|
|
CREATE INDEX contributions_geom_idx ON contributions USING GIST (geom);
|
|
CREATE INDEX contributions_municipality_idx ON contributions (municipality_id);
|
|
CREATE INDEX contributions_status_idx ON contributions (status);
|
|
CREATE INDEX contributions_category_idx ON contributions (category);
|
|
|
|
|
|
-- ---------------------------------------------------------------------
|
|
-- Block 5: Table "votes"
|
|
-- Individual like and dislike Records. UNIQUE Constraint prevents the
|
|
-- same voter from liking or disliking the same contribution multiple times.
|
|
-- ---------------------------------------------------------------------
|
|
CREATE TABLE votes (
|
|
vote_id SERIAL PRIMARY KEY,
|
|
contribution_id INTEGER NOT NULL REFERENCES contributions(contribution_id) ON DELETE CASCADE,
|
|
voter_name VARCHAR(100) NOT NULL, -- ToDo: Replace with user_id once Authentification exists
|
|
vote_type VARCHAR(10) NOT NULL,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
|
|
CONSTRAINT votes_unique_per_voter UNIQUE (contribution_id, voter_name),
|
|
CONSTRAINT votes_vote_type_check CHECK (vote_type IN ('like', 'dislike'))
|
|
);
|
|
|
|
COMMENT ON TABLE votes IS 'Individual Votes to prevent duplicate Likes and Dislikes.';
|
|
|
|
|
|
-- ---------------------------------------------------------------------
|
|
-- Block 6: Trigger Functions
|
|
-- ---------------------------------------------------------------------
|
|
|
|
-- Automatically Refresh updated_at on every UPDATE.
|
|
CREATE OR REPLACE FUNCTION set_updated_at()
|
|
RETURNS TRIGGER AS $$
|
|
BEGIN
|
|
NEW.updated_at = NOW();
|
|
RETURN NEW;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
CREATE TRIGGER contributions_updated_at
|
|
BEFORE UPDATE ON contributions
|
|
FOR EACH ROW EXECUTE FUNCTION set_updated_at();
|
|
|
|
CREATE TRIGGER municipalities_updated_at
|
|
BEFORE UPDATE ON municipalities
|
|
FOR EACH ROW EXECUTE FUNCTION set_updated_at();
|
|
|
|
|
|
-- Keeps likes_count / dislikes_count synchronized with the votes Table.
|
|
CREATE OR REPLACE FUNCTION update_vote_counts()
|
|
RETURNS TRIGGER AS $$
|
|
BEGIN
|
|
IF TG_OP = 'INSERT' THEN
|
|
IF NEW.vote_type = 'like' THEN
|
|
UPDATE contributions SET likes_count = likes_count + 1
|
|
WHERE contribution_id = NEW.contribution_id;
|
|
ELSE
|
|
UPDATE contributions SET dislikes_count = dislikes_count + 1
|
|
WHERE contribution_id = NEW.contribution_id;
|
|
END IF;
|
|
ELSIF TG_OP = 'DELETE' THEN
|
|
IF OLD.vote_type = 'like' THEN
|
|
UPDATE contributions SET likes_count = GREATEST(likes_count - 1, 0)
|
|
WHERE contribution_id = OLD.contribution_id;
|
|
ELSE
|
|
UPDATE contributions SET dislikes_count = GREATEST(dislikes_count - 1, 0)
|
|
WHERE contribution_id = OLD.contribution_id;
|
|
END IF;
|
|
END IF;
|
|
RETURN NULL;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
CREATE TRIGGER votes_count_sync
|
|
AFTER INSERT OR DELETE ON votes
|
|
FOR EACH ROW EXECUTE FUNCTION update_vote_counts();
|
|
|
|
|
|
-- ---------------------------------------------------------------------
|
|
-- Block 7: Typed Geometry Views for QGIS
|
|
-- QGIS handles mixed-geometry Tables awkwardly, so one View per
|
|
-- Geometry Type is created. Reflects live Data from the Contributions Table.
|
|
-- ---------------------------------------------------------------------
|
|
CREATE VIEW contributions_points AS
|
|
SELECT * FROM contributions WHERE geom_type = 'point';
|
|
|
|
CREATE VIEW contributions_lines AS
|
|
SELECT * FROM contributions WHERE geom_type = 'line';
|
|
|
|
CREATE VIEW contributions_polygons AS
|
|
SELECT * FROM contributions WHERE geom_type = 'polygon';
|
|
|
|
|
|
-- ---------------------------------------------------------------------
|
|
-- Block 8: Seed Data — Initial Municipality
|
|
-- ---------------------------------------------------------------------
|
|
INSERT INTO municipalities (name, slug, center_lat, center_lng, default_zoom, primary_color)
|
|
VALUES ('Lohne (Oldenburg)', 'lohne', 52.66639, 8.23306, 14, '#00376D');
|
|
|
|
|
|
-- =====================================================================
|
|
-- End of migration 001_initial_schema.sql
|
|
-- ===================================================================== |