DROP TABLE IF EXISTS dokument          CASCADE;
DROP TABLE IF EXISTS fi_replace_letter CASCADE;

CREATE TABLE dokument
( id     INTEGER NOT NULL,
  autor  VARCHAR NOT NULL,
  titel  VARCHAR NOT NULL,
  inhalt TEXT,
  
  trigramm_autor TEXT, -- NULLABLE, da der Index erst später erzeugt wird
  trigramm       TEXT, -- NULLABLE, da der Index erst später erzeugt wird

  PRIMARY KEY (id)
);

INSERT INTO dokument(id, autor, titel, inhalt)
VALUES
(1, 'Wolfgang Kowarschick',
    'Skript zur Vorlesung MMDB', 
    'Vorlesung MM-Datenbanksysteme im WiSe 2009/2010'
),
(2, 'Wolfgang Kowarschick',
    'Skript zur Vorlesung MMProg',
    'Vorlesung MM-Programmierung im WiSe 2009/2010'
),
(3, 'Christian Märtin, Rolf Socher, Michael Lutz',
    'Theoretische Grundlagen der Informatik',
    'Theoretische Grundlagen der Informatik, 3. Auflage'
),
(4, 'Wolfgang Kowarschick',
    'Skript zur Vorlesung PM',
    NULL
)
;

CREATE TABLE fi_replace_letter
( letter_from CHAR(1)    NOT NULL,
  letters_to  VARCHAR(3) NOT NULL,
  
  PRIMARY KEY (letter_from),
  UNIQUE      (letters_to)
)
;

INSERT INTO fi_replace_letter(letter_from, letters_to)
VALUES
 ('ä', 'ae'),
 ('ö', 'oe'),
 ('ü', 'ue'),
 ('ß', 'ss'),
 ('f', 'ph')
;

/******************************************************************************
 * Hilfsfunktionen
 ******************************************************************************/

CREATE OR REPLACE FUNCTION delimiters_regexp() RETURNS TEXT
LANGUAGE SQL
IMMUTABLE
AS 
$$
  SELECT '[^\w_+]+'
$$ 
;

CREATE OR REPLACE FUNCTION transform_to(c CHAR) RETURNS TEXT
LANGUAGE SQL
IMMUTABLE
AS 
$$
  SELECT letters_to
  FROM   fi_replace_letter
  WHERE  letter_from = c
$$ 
;

CREATE OR REPLACE FUNCTION normalizeText(p_text text) RETURNS text
RETURNS NULL ON NULL INPUT
LANGUAGE SQL
IMMUTABLE
AS 
$$
  SELECT STRING_AGG(COALESCE(transform_to(c),c),'')
  FROM   REGEXP_SPLIT_TO_TABLE(LOWER(p_text), '') dummy(c)
$$ 
;

/******************************************************************************
 * Erzeuge einen Trigramm-Index für das Text-Attribut 'autor'.
 ******************************************************************************/

/* Aufräumen */
DROP TRIGGER IF EXISTS trigramm_autor_update_trigger ON dokument;
DROP FUNCTION IF EXISTS trigramm_autor_update_function();
DROP INDEX IF EXISTS trigramm_autor_index;

-- Erzeuge den zugehörigen Index
CREATE INDEX trigramm_autor_index ON dokument USING GIN(trigramm_autor gin_trgm_ops);

-- Passe den Index bei jedem UPDATE und jedem INSERT an.
CREATE FUNCTION trigramm_autor_update_function() RETURNS TRIGGER
LANGUAGE plpgsql
AS
$$
  BEGIN
    NEW.trigramm_autor = normalizeText(NEW.autor);
    RETURN NEW;
  END
$$
;

CREATE TRIGGER trigramm_autor_update_trigger BEFORE INSERT OR UPDATE
ON dokument
FOR EACH ROW 
EXECUTE PROCEDURE
   trigramm_autor_update_function(); 
;

-- Aktualisiere alle trigramme, indem für jedes Dokumente
-- der Trigger ausgelöst wird. Dies ist nur dann notwendig,
-- wenn der Trigramm-Index NACHTRÄGLICH eingefügt wurde.

UPDATE dokument
SET trigramm_autor = NULL
;

/******************************************************************************
 * Erzeuge einen trigramm-Index
 * für die Text-Attribute 'autor', 'titel' und 'inhalt'.
 ******************************************************************************/

-- Aufräumen
DROP TRIGGER IF EXISTS trigramm_update_trigger ON dokument;
DROP FUNCTION IF EXISTS trigramm_update_function();
DROP INDEX IF EXISTS trigramm_index;

-- Erzeuge den zugehörigen Index
CREATE INDEX trigramm_index ON dokument USING GIN(trigramm gin_trgm_ops);

-- Passe den Index bei jedem UPDATE und jedem INSERT an.
CREATE FUNCTION trigramm_update_function() RETURNS TRIGGER
LANGUAGE plpgsql
AS
$$
  BEGIN
    NEW.trigramm =
      normalizeText(NEW.autor || ' ' || NEW.titel || ' ' || COALESCE(NEW.inhalt, ''))
    ;
    RETURN NEW;
  END
$$
;

CREATE TRIGGER trigramm_update_trigger BEFORE INSERT OR UPDATE
ON dokument
FOR EACH ROW 
EXECUTE PROCEDURE
  trigramm_update_function();
;

-- Aktualisiere alle trigramme, indem für jedes Dokumente
-- der Trigger ausgelöst wird. Dies ist nur dann notwendig,
-- wenn der Trigramm-Index NACHTRÄGLICH eingefügt wurde.
UPDATE dokument
SET trigramm = NULL
;

/******************************************************************************
 * Infoqueries:
 ******************************************************************************/

SELECT show_trgm('Busbahnhof')        AS busbahnhof,
       show_trgm('Bahnhof für Busse') AS bahnhof_fuer_busse
;
       
SELECT *, show_trgm(trigramm_autor) FROM dokument;

/******************************************************************************
 * Finde alle Dokumente vom Autor 'Kowarschick':
 ******************************************************************************/

SELECT id, autor, titel, inhalt
FROM   dokument
WHERE  normalizeText('Kowarschick') <<% trigramm_autor 
;

SELECT id, autor, titel, inhalt
FROM   dokument
WHERE  normalizeText('Kowarschik') <<% trigramm_autor 
;

SELECT id, autor, titel, inhalt
FROM   dokument
WHERE  normalizeText('Kowarshcick') <<% trigramm_autor 
;

-- zu viele Schreibfehler
SELECT id, autor, titel, inhalt
FROM   dokument
WHERE  normalizeText('Kowarshcik') <<% trigramm_autor 
;

/******************************************************************************
 * Finde alle Dokumente vom Autor 'Märtin':
 ******************************************************************************/

SELECT id, autor, titel, inhalt
FROM   dokument
WHERE  normalizeText('Märtin') <<% trigramm_autor 
;

SELECT id, autor, titel, inhalt
FROM   dokument
WHERE  normalizeText('Martin') <<% trigramm_autor 
;

/******************************************************************************
 * Finde alle Dokumente die 'PM' oder 'Datenbank%' enthalten.
 ******************************************************************************/

SELECT id, autor, titel, inhalt
FROM   dokument
WHERE  normalizeText('PM') <<% trigramm OR normalizeText('Datenbank') <<% trigramm
;

/******************************************************************************
 * Finde alle Dokumente die 'Vorlesung' und 'Datenbank%' enthalten.
 ******************************************************************************/

SELECT id, autor, titel, inhalt
FROM   dokument
WHERE  normalizeText('Vorlesung') <<% trigramm AND normalizeText('Datenbank') <<% trigramm
;

/******************************************************************************
 * Finde alle Dokumente die 'Vorlesung' und 'Kowa%' enthalten.
 ******************************************************************************/

-- zu unähnlich
SELECT id, autor, titel, inhalt
FROM   dokument
WHERE  normalizeText('Vorlesung') <<% trigramm AND normalizeText('Kowa') <<% trigramm
;

-- ähnlich genug
SELECT id, autor, titel, inhalt
FROM   dokument
WHERE  normalizeText('Vorlesung') <<% trigramm AND normalizeText('Kowarsc') <<% trigramm
;

/******************************************************************************
 * Finde alle Dokumente die 'Vorlesung' und 'Kowa%', aber nicht 'MMProg' enthalten.
 ******************************************************************************/

SELECT id, autor, titel, inhalt
FROM   dokument
WHERE  trigramm @@ to_tsquery('german', 'Vorlesung & Kowarsc:* & !MMProg')
;

SELECT id, autor, titel, inhalt
FROM   dokument
WHERE           normalizeText('Vorlesung') <<% trigramm 
       AND      normalizeText('Kowarsc')   <<% trigramm
       AND NOT (normalizeText('MMProg')    <<% trigramm)
;

/******************************************************************************
 * Finde alle Dokumente die 'Michael Lutz' (als Phrase) enthalten.
 ******************************************************************************/

SELECT id, autor, titel, inhalt
FROM   dokument
WHERE  normalizeText('Michael Lutz') <<% trigramm
;

/******************************************************************************
 * Finde alle Dokumente die 'MM % % WiSe' (als Phrase) enthalten.
 ******************************************************************************/

-- Funktioniert nicht