/*************************************************************
 * Berechnung der Vorfahren einer Generation.
 * Um die Anfragen einfach zu halten, wird auf 
 * die Unterscheidung der Geschlechter
 * (Großmütter, Großväter etc.) verzichtet.
 *
 * Um die Namen der Personen ausgeben zu können, wird 
 * die View eltern_2 an Stelle der Beziehungstabelle
 * eltern verwendet.
 *************************************************************/

-- Alle Eltern:
SELECT   p_nr, p_name, e_nr, e_name
FROM     eltern_2
ORDER BY p_name,  p_nr
;

-- Alle Personen sowie deren Eltern, sofern diese bekannt sind:
SELECT   p_nr, p_name, e_nr, e_name
FROM     personen NATURAL LEFT JOIN eltern_2
ORDER BY p_name,  p_nr
;



-- Alle Großeltern:
SELECT   e1.p_nr, e1.p_name, e2.e_nr, e2.e_name
FROM     eltern_2 e1 JOIN eltern_2 e2 ON e1.e_nr = e2.p_nr
ORDER BY p_name,  p_nr
;

-- Alle Personen sowie deren Großeltern, sofern diese bekannt sind:
SELECT   p.p_nr, p.p_name, e2.e_nr, e2.e_name
FROM     personen p
         LEFT JOIN
         (eltern_2 e1 JOIN eltern_2 e2 ON e1.e_nr = e2.p_nr)
         ON p.p_nr = e1.p_nr
ORDER BY p_name,  p_nr
;



-- Alle Urgroßeltern:
SELECT   e1.p_nr, e1.p_name, e3.e_nr, e3.e_name
FROM     eltern_2 e1 JOIN eltern_2 e2 ON e1.e_nr = e2.p_nr
                     JOIN eltern_2 e3 ON e2.e_nr = e3.p_nr
ORDER BY p_name,  p_nr
;


-- Alle Personen sowie deren Urgroßeltern, sofern diese bekannt sind:
SELECT   p.p_nr, p.p_name, e3.e_nr, e3.e_name
FROM     personen p
         LEFT JOIN
         (eltern_2 e1 JOIN eltern_2 e2 ON e1.e_nr = e2.p_nr
                      JOIN eltern_2 e3 ON e2.e_nr = e3.p_nr
         )
         ON p.p_nr = e1.p_nr
ORDER BY p_name,  p_nr
;


/*************************************************************
 * Berechnung "aller" Vorfahren ohne Rekursion.
 *************************************************************/

-- Alle Vorfahren bis zur Urgroßeltern-Generation:

SELECT   e1.p_nr, e1.p_name, e_nr AS v_nr, e_name AS v_name,
         1 AS v_generation
FROM     eltern_2 e1

UNION

SELECT   e1.p_nr, e1.p_name, e2.e_nr AS v_nr, e2.e_name AS v_name,
         2 AS v_generation
FROM     eltern_2 e1 JOIN eltern_2 e2 ON e1.e_nr = e2.p_nr

UNION

SELECT   e1.p_nr, e1.p_name, e3.e_nr AS v_nr, e3.e_name AS v_name,
         3 AS v_generation
FROM     eltern_2 e1 JOIN eltern_2 e2 ON e1.e_nr = e2.p_nr
                     JOIN eltern_2 e3 ON e2.e_nr = e3.p_nr
-- Und so weiter. Für jede weitere Generation folgt
-- eine weitere SELECT-Anweisung mit jeweils einem
-- weitern eltern_2-JOIN.

ORDER BY p_name, p_nr, v_generation
;


-- Alle Personen (Generation 0) sowie deren Vorfahren 
-- (bis zu den Urgroßeltern), die bekannt sind:

SELECT   p.p_nr, p.p_name, p.p_nr AS v_nr, p.p_name AS v_name,
         0 AS v_generation
FROM     personen p

UNION

SELECT   e1.p_nr, e1.p_name, e_nr AS v_nr, e_name AS v_name,
         1 AS v_generation
FROM     eltern_2 e1

UNION

SELECT   e1.p_nr, e1.p_name, e2.e_nr AS v_nr, e2.e_name AS v_name,
         2 AS v_generation
FROM     eltern_2 e1 JOIN eltern_2 e2 ON e1.e_nr = e2.p_nr

UNION

SELECT   e1.p_nr, e1.p_name, e3.e_nr AS v_nr, e3.e_name AS v_name,
         3 AS v_generation
FROM     eltern_2 e1 JOIN eltern_2 e2 ON e1.e_nr = e2.p_nr
                     JOIN eltern_2 e3 ON e2.e_nr = e3.p_nr
-- Und so weiter. Für jede weitere Generation folgt
-- eine weitere SELECT-Anweisung mit jeweils einem
-- weitern eltern_2-JOIN.

ORDER BY p_name, p_nr, v_generation
;

/*************************************************************
 * Berechnung "aller" Vorfahren mit Rekursion.
 *************************************************************/

-- Alle Vorfahren:
WITH RECURSIVE vorfahren (p_nr, p_name, v_nr, v_name, v_generation)
AS
(
  -- Die Eltern sind Vorfahren der ersten Generation
  -- (nicht-rekursiver Zweig).
  SELECT   v.p_nr, v.p_name, e_nr AS v_nr, e_name AS v_name,
           1 AS v_generation
  FROM     eltern_2 v
  UNION
  -- Die Eltern der Vorfahren der n-ten Generation
  -- sind Vorfahren der n-plus-ersten Generation
  -- (rekursiver Zweig).
  SELECT   v.p_nr, v.p_name, e.e_nr AS v_nr, e.e_name AS v_name,
           v.v_generation+1 AS v_generation
  FROM     vorfahren v JOIN eltern_2 e ON v.v_nr = e.p_nr
)
SELECT   p_nr, p_name, v_nr, v_name, v_generation
FROM     vorfahren
ORDER BY p_name, p_nr, v_generation
;


-- Alle Personen (Generation 0) sowie deren Vorfahren 
-- (bis zu den Urgroßeltern), die bekannt sind:
WITH RECURSIVE vorfahren (p_nr, p_name, v_nr, v_name, v_generation)
AS
(
  -- Die Personen selbst sind Vorfahren der nullten Generation
  -- (nicht-rekursiver Zweig).
  SELECT   v.p_nr, v.p_name, v.p_nr AS v_nr, v.p_name AS v_name,
           0 AS v_generation
  FROM     personen v
  UNION
  -- Die Eltern der Vorfahren der n-ten Generation
  -- sind Vorfahren der n-plus-ersten Generation
  -- (rekursiver Zweig).
  SELECT   v.p_nr, v.p_name, e.e_nr AS v_nr, e.e_name AS v_name,
           v.v_generation+1 AS v_generation
  FROM     vorfahren v JOIN eltern_2 e ON v.v_nr = e.p_nr
)
SELECT   p_nr, p_name, v_nr, v_name, v_generation
FROM     vorfahren
ORDER BY p_name, p_nr, v_generation
;

-- Alle Personen (Generation 0) sowie deren Vorfahren
-- (bis zu den Urgroßeltern), die bekannt sind:
-- mit Bezeichnung der Generation
WITH RECURSIVE vorfahren
 (p_nr, p_name, p_geschlecht, v_nr, v_name, v_geschlecht, v_generation)
AS
(
  -- Die Personen selbst sind Vorfahren der nullten Generation
  -- (nicht-rekursiver Zweig).
  SELECT   v.p_nr, v.p_name, v.p_geschlecht,  
           v.p_nr, v.p_name, v.p_geschlecht,
           0
  FROM     personen v
  UNION
  -- Die Eltern der Vorfahren der n-ten Generation
  -- sind Vorfahren der n-plus-ersten Generation
  -- (rekursiver Zweig).
  SELECT   v.p_nr, v.p_name, v.p_geschlecht,
           e.e_nr, e.e_name, e.e_geschlecht,
           v.v_generation+1
  FROM     vorfahren v JOIN eltern_2 e ON v.v_nr = e.p_nr
)
SELECT   p_nr, p_name, p_geschlecht,
         v_nr, v_name, v_geschlecht,
         v_generation,
         g_bezeichnung,
         CASE
          WHEN v_geschlecht = 'Frau' 
            THEN g_bezeichnung_frau
            ELSE g_bezeichnung_mann
         END AS g_bezeichnung_geschlecht,
         2^v_generation AS max_anzahl
FROM     vorfahren JOIN generation ON v_generation = g_nr
ORDER BY p_name, p_nr, v_generation
;

SELECT g_bezeichnung, 2^g_nr AS max_anzahl
FROM   generation
;

-- Pfade zu allen Vorfahren einer Person.
WITH RECURSIVE vorfahren (p_nr, p_name, v_nummern, v_namen)
AS
(
  -- Für Personen, für die keine Eltern bekannt sind,
  -- besteht der Vorfahrenpfad nur aus der Person selbst
  -- (nicht-rekursiver Zweig).
  SELECT   p.p_nr, p.p_name,
           -- Der Pfad enthält nur die Person selbst.
           ARRAY[p.p_nr]   AS v_nummern,
           ARRAY[p.p_name] AS v_namen
  FROM     personen p
  WHERE    NOT EXISTS -- Es gibt für p.p_nr keinen
                      -- Eintrag in der Eltern-Relation.
           (SELECT * FROM eltern e WHERE e.p_nr = p.p_nr) 

  UNION ALL -- UNION = UNION DISTINCT funktioniert nicht,
            -- da Duplikat-Arrays nicht erkannt werden

  -- Für Personen e.p_nr, für deren Eltern bereits Personenpfade
  -- bekannt sind, berechnet man die Personenpfade, 
  -- indem man ihre Namen den Personenpfaden der
  -- Eltern voarnstellt.
  SELECT   e.p_nr, e.p_name,
           ARRAY[e.p_nr]   || v_nummern AS v_nummern,
           ARRAY[e.p_name] || v_namen   AS v_namen
  FROM     vorfahren v JOIN eltern_2 e ON v.p_nr = e.e_nr
)
SELECT p_nr, p_name, v_nummern, v_namen
FROM vorfahren
ORDER BY p_name, p_nr
;

/********* FUNKTIONIERT LEIDER (NOCH?) NICHT *************/
-- Die vorherige Loesung hat noch den Nachteil, dass für
-- eine Person nicht ein Vorfahrensbaum, sondern diverse unabhängige
-- Pfade generiert werden.
-- Dieses Problem könnte mit Hilfe der Listen-Aggregation 'string_array_accumulate'
-- gelöst werden, wenn rekursive Anfragen Aggregation enhalten dürften. :-((
--
-- Die Funktion array_accumulate kann nicht verwendet werden, weil
-- bei Arrays alle Elemente vom selben Typ sein müssen. Ein
-- Vorfahrenbaum enthält jedoch Namen sowie Bäume als Elemente.
/*
WITH RECURSIVE vorfahren (p_nr, p_name, v_nummern, v_namen)
AS
(
  -- Für Personen, für die keine Eltern bekannt sind,
  -- besteht der Vorfahrenbaum nur aus der Person selbst
  -- (nicht-rekursiver Zweig).
  SELECT   p.p_nr, p.p_name,
           -- Der Pfad enthält nur die Person selbst.
           '[' || p.p_nr || ']'   AS v_nummern,
           '[' || p.p_name || ']' AS v_namen
  FROM     personen p
  WHERE    NOT EXISTS -- Es gibt für p.p_nr keinen
                      -- Eintrag in der Eltern-Relation.
           (SELECT * FROM eltern e WHERE e.p_nr = p.p_nr)

  UNION ALL 

  -- Für Personen e.p_nr, für deren Eltern bereits Personenbäume
  -- bekannt sind, berechnet man den Personenbaum, 
  -- indem man ihren jeweilgen Namen der Vereigigung (Aggregation) der
  -- Personenbäumen der Eltern voarnstellt.
  SELECT   e.p_nr, e.p_name,
           '[' || e.p_nr   || ',' ||
           SUBSTRING(string_array_accumulate(v_nummern) FROM 2) AS v_nummern,
           '[' || e.p_name || ',' ||
           SUBSTRING(string_array_accumulate(v_namen) FROM 2)   AS v_namen
  FROM     vorfahren v JOIN eltern_2 e ON v.p_nr = e.e_nr
  GROUP BY e.p_nr, e.p_name
)
SELECT p_nr, p_name, v_nummern, v_namen
FROM vorfahren
ORDER BY p_name, p_nr
;
*/


