/*****************************************************************
 * Lösche bereits existierende Views, Tabellen und Domänen.
 *****************************************************************/

DROP AGGREGATE IF EXISTS array_accumulate(anyelement) CASCADE;
DROP AGGREGATE IF EXISTS string_array_accumulate(anyelement) CASCADE;

DROP VIEW IF EXISTS vorfahren_1 CASCADE;
DROP VIEW IF EXISTS vorfahren_2 CASCADE;

DROP VIEW IF EXISTS eltern_2 CASCADE;


DROP TYPE IF EXISTS D_GESCHLECHT;

/*****************************************************************
 * Domänen
 *****************************************************************/

CREATE TYPE D_GESCHLECHT 
AS 
ENUM('Frau', 'Mann');

/*****************************************************************
 * Tabellen
 *****************************************************************/

-- Generationen:
--     g_nr (PK): Generationsstude
--     g_bezeichnung:      Name der Generationstufe (geschlechtsneutral, plural)
--     g_bezeichnung_frau: Name der Generationstufe (weiblich, singular)
--     g_bezeichnung:      Name der Generationstufe (männlich, singular)
CREATE TABLE generation
(g_nr               INTEGER NOT NULL,
 g_bezeichnung      VARCHAR NOT NULL,
 g_bezeichnung_frau VARCHAR NOT NULL,
 g_bezeichnung_mann VARCHAR NOT NULL,

 CONSTRAINT pk_generation
   PRIMARY KEY (g_nr)
);

-- Personen: 
--    p_nr (PK) 
--    p_name 
--    p_geschlecht
--    ...        (beliebig viele weitere Informationen)
CREATE TABLE personen
(p_nr         INTEGER      NOT NULL,
 p_name       VARCHAR      NOT NULL,
 p_geschlecht D_GESCHLECHT NOT NULL,

 CONSTRAINT pk_personen
   PRIMARY KEY (p_nr)
);

-- Eltern:
--  p_nr (PK, FK => personen (p_nr))
--  e_nr (PK, FK => personen (p_nr))
--
-- Beziehung: Eine Person p_nr hat eine Person e_nr als Elter.
CREATE TABLE eltern
(p_nr INTEGER NOT NULL,
 e_nr INTEGER NOT NULL,

 CONSTRAINT pk_eltern
   PRIMARY KEY (p_nr, e_nr),

 CONSTRAINT fk_p_nr
  FOREIGN KEY (p_nr) REFERENCES personen (p_nr),

 CONSTRAINT fk_e_nr
  FOREIGN KEY (e_nr) REFERENCES personen (p_nr)
);

/*****************************************************************
 * Views
 *****************************************************************/

-- Eltern 2: = Eltern mit Namen und Geschlecht der Personen,
--             die in der jeweiligen Eltern-/Kind-Beziehung stehen.
--  p_nr:         Identifikator einer Person
--  p_name:       Name der Person p_nr
--  p_geschlecht: Geschlecht der Person p_nr
--  e_nr:         Identifikator eines Elters der Person p_nr
--  e_name:       Name der Person e_nr
--  e_geschlecht: Geschlecht der Person e_nr
CREATE VIEW eltern_2 (p_nr, p_name, p_geschlecht,
                      e_nr, e_name, e_geschlecht
                     )
AS
SELECT p.p_nr, p.p_name, p.p_geschlecht,
       e.p_nr, e.p_name, e.p_geschlecht
FROM        personen p 
       JOIN eltern      USING (p_nr)
       JOIN personen e  ON    (e_nr = e.p_nr)           
;

-- Inhalt der View "Eltern 2"
SELECT   p_nr, p_name, p_geschlecht,
         e_nr, e_name, e_geschlecht
FROM     eltern_2
ORDER BY p_name, p_nr, e_name, e_nr
;

/*****************************************************************
 * Rekursive Views
 *****************************************************************/

-- Vorfahren 1:
--  p_nr: Identifikator einer Person
--  v_nr: Identifikator eines Vorfahren der Person p_nr 
--
-- Beziehung:  Eine Person p_nr hat eine Person v_nr als Vorfahre.
-- Berechnung: Durch Rekursion über die Beziehungstabelle Eltern.
CREATE VIEW vorfahren_1 (p_nr, v_nr)
AS
-- Temporäre rekursive View 
-- (da PostgreSQL rekursive Views noch nicht unterstützt)
WITH RECURSIVE vorfahren (p_nr, v_nr)
AS
(
  -- Die Eltern sind Vorfahren (nicht-rekrusiver Zweig).
  SELECT p_nr, e_nr FROM eltern

  UNION

  -- Die Vorfahren der Eltern sind auch Vorfahren (rekursiver Zweig).
  SELECT e.p_nr, v.v_nr
  FROM   eltern e, vorfahren v
  WHERE  e.e_nr = v.p_nr
)
-- Selektiere alles von der temporären View.
SELECT p_nr, v_nr FROM vorfahren
;

-- Inhalt der View "Vorfahren 1"
SELECT   p_nr, v_nr 
FROM     vorfahren_1
ORDER BY p_nr, v_nr
;


-- Vorfahren 2:
--  p_nr:          Identifikator einer Person
--  p_name:        Name der Person p_nr
--  p_geschlecht:  Geschlecht der Person p_nr
--  v_nr:          Identifikator eines Vorfahren der Person p_nr
--  v_name:        Name der Person e_nr
--  v_geschlecht:  Geschlecht der Person e_nr
--  g_nr:          Abstand der Generationen zwischen Person und Vorfahre
--  g_bezeichnung: Bezeichnung der Vorfahrengeneration: 
--                 Eltern, Großeltern ... (plural) 
--  g_bezeichnung_geschlecht: 
--                 Bezeichnung der Vorfahrengeneration 
--                 abhängig vom Geschlecht des Vorfahren:
--                 Mutter, Vater, Großmutter, Großvater ... (singular) 
--
-- Beziehung:  Eine Person p_nr hat eine Person v_nr als Vorfahre.
-- Berechnung: Durch Rekursion über die Beziehungstabelle Eltern.
CREATE VIEW vorfahren_2 (p_nr, p_name, p_geschlecht,
                         v_nr, v_name, v_geschlecht,
                         g_nr, g_bezeichnung, g_bezeichnung_geschlecht
                        )
AS
-- Temporäre rekursive View 
-- (da PostgreSQL rekursive Views noch nicht unterstützt)
WITH RECURSIVE vorfahren (p_nr, p_name, p_geschlecht,
                          v_nr, v_name, v_geschlecht,
                          g_nr, g_bezeichnung, g_bezeichnung_geschlecht
                         )
AS
(
  -- Die Eltern sind Vorfahren (nicht-rekrusiver Zweig).
  SELECT p_nr, p_name, p_geschlecht,
         e_nr, e_name, e_geschlecht,
         g.g_nr, g.g_bezeichnung,
         -- Berechnung der geschlechtsspezifischen Bezeichnung der Generation
         CASE WHEN e_geschlecht='Frau' 
           THEN g.g_bezeichnung_frau
           ELSE g.g_bezeichnung_mann
         END
  FROM   eltern_2, generation g -- Kartesisches Produkt
  WHERE  g.g_nr = 1               -- mit einer ein-elementigen Menge!
         -- Elterngeneration = eine Generation Abstand (g.g_nr = 1)
  UNION

  -- Die Eltern der Vorfahren sind auch Vorfahren (rekursiver Zweig).
  SELECT v.p_nr, v.p_name, v.p_geschlecht,
         e.e_nr, e.e_name, e.e_geschlecht,
         g.g_nr, g.g_bezeichnung,
         -- Berechnung der geschlechtsspezifischen Bezeichnung der Generation
         CASE WHEN e_geschlecht='Frau'
           THEN g.g_bezeichnung_frau
           ELSE g.g_bezeichnung_mann
         END
  FROM   vorfahren v, eltern_2 e, generation g
  WHERE  v.v_nr = e.p_nr AND g.g_nr = v.g_nr+1
          -- Elterngeneration = eine Generation mehr Abstand (g.g_nr = v.g_nr+1)
)
-- Selektiere alles von der temporären View.
SELECT p_nr, p_name, p_geschlecht,
       v_nr, v_name, v_geschlecht,
       g_nr, g_bezeichnung, g_bezeichnung_geschlecht
FROM   vorfahren
;

-- Inhalt der View "Vorfahren 2"
SELECT   p_nr, p_name, p_geschlecht,
         v_nr, v_name, v_geschlecht,
         g_nr, g_bezeichnung, g_bezeichnung_geschlecht
FROM     vorfahren_2
ORDER BY p_name, p_nr, g_nr, g_bezeichnung_geschlecht, v_name
;

/*****************************************************************
 * Listen-Aggregation
 *****************************************************************/

-- http://www.postgresql.org/docs/9.1/interactive/sql-createfunction.html
CREATE OR REPLACE FUNCTION array_append_distinct(anyarray, anyelement) RETURNS anyarray AS 
$_SQL_$
SELECT
CASE
   WHEN $1 IS NULL AND $2 IS NULL
       THEN '{}'
   WHEN $1 IS NULL
       THEN ARRAY[$2]
   WHEN $2 IS NULL
       THEN $1
-- WHEN $2 = ANY($1) -- Duplikat-Elimination, kostet Zeit!
--     THEN $1
   ELSE
       array_append($1,$2)
END;
$_SQL_$
LANGUAGE SQL IMMUTABLE;

-- http://www.postgresql.org/docs/9.1/interactive/sql-createaggregate.html
CREATE AGGREGATE array_accumulate(anyelement)
(
   SFUNC = array_append_distinct,
   STYPE = anyarray
); 

/*****************************************************************
 * String-Listen-Aggregation
 *****************************************************************/

-- http://www.postgresql.org/docs/9.1/interactive/sql-createfunction.html
CREATE OR REPLACE FUNCTION string_array_append_distinct(VARCHAR, anyelement) RETURNS VARCHAR AS
$_SQL_$
SELECT
CASE
   WHEN $1 IS NULL AND $2 IS NULL
       THEN '[]'
   WHEN $1 IS NULL
       THEN '[' || CAST($2 AS VARCHAR) || ']'
   WHEN $2 IS NULL
       THEN $1
   ELSE
          SUBSTRING(CAST($1 AS VARCHAR) FROM 1 FOR CHAR_LENGTH(CAST($1 AS VARCHAR))-1)
       || ',' || CAST($2 AS VARCHAR) || ']'
END;
$_SQL_$
LANGUAGE SQL IMMUTABLE;

-- http://www.postgresql.org/docs/9.1/interactive/sql-createaggregate.html
CREATE AGGREGATE string_array_accumulate(anyelement)
(
   SFUNC = string_array_append_distinct,
   STYPE = VARCHAR
);


/*****************************************************************
 * EOF
 *****************************************************************/
