SQL-Beispiele aus dem Miro-Board (08 Benutzerverwaltung)

SQL-Dateien

hsaweb-create.sql(Daten­bank erstellen)
hsaweb-data.sql(Daten ein­fü­gen)
hsaweb-query.sql(Prak­ti­kums­auf­ga­be HSA-Web)
hsaweb-trigger.sql(Prak­ti­kums­auf­ga­be HSA-Web-Trigger)

Einführung

X'AF' || X'FE' = X'AFFE'
SUBSTRING(X'543210' FROM 3) = X'10'
SUBSTRING('Wolfgang' FROM 3) = 'lfgang'

Relationales Modell und Schema

volltext.sql(Erzeu­gung der Datenbank)
volltext-query.sql(Bei­spiels­an­fra­gen) 
SELECT d.did, autor, titel, inhalt --*
FROM   woerterbuch w JOIN volltextindex v  ON w.wid = v.wid
                     JOIN dokument d       ON v.did = d.did
WHERE  wort LIKE 'datenbank%'

Beispielsanfragen

SELECT id, attribute, pos, author, title, content
FROM document_extended
WHERE word = 'maertin'
;
SELECT id, attribute, pos, author, title, content
FROM document_extended
WHERE word = fi_normalize('Märtin')
;
SELECT id, attribute, pos, author, title, content
FROM document_extended
WHERE word = fi_normalize('Märtin') AND attribute = 'author'
;

/******************************************************************************
* Find all documents containing 'Vorlesung' or 'Datenbank%':
******************************************************************************/
SELECT id, attribute, pos, author, title, content
FROM   document_extended
WHERE  word = fi_normalize('Vorlesung') OR word LIKE fi_normalize('Datenbank%')
;
/******************************************************************************
* Find all documents containing '%systeme' (ending auf 'systeme'):
******************************************************************************/
SELECT id, attribute, pos, author, title, content
FROM document_extended

-- Pfui! Durchgefallen!
SELECT id
FROM fi_dictionary
WHERE word LIKE '%systeme'
;

-- Entrunkierung ist OK!
SELECT id
FROM fi_dictionary
WHERE word LIKE '@emetsys%'
;

-- Endtrunkierung mit Normalisierung ist besser
SELECT id, attribute, pos, author, title, content
FROM document_extended
WHERE word LIKE fi_normalize('@emetsys%')
;

Beispielsanfragen 2

/******************************************************************************
* Find all documents containing 'Vorlesung', 'Kowa%' and 'WiSe':
******************************************************************************/

SELECT DISTINCT d1.id, d1.author, d1.title, d1.content
FROM document_extended d1 JOIN document_extended d2 ON d1.id = d2.id
                          JOIN document_extended d3 ON d1.id = d3.id
WHERE     d1.word = 'vorlesung'
      AND d2.word LIKE 'kowa%'
      AND d3.word = fi_normalize('WiSe')
;

Postgres-Volltextsuche

CREATE TABLE dokument
( id             INTEGER NOT NULL,
  autor          VARCHAR NOT NULL,
  titel          VARCHAR NOT NULL,
  inhalt         TEXT,
  volltext_autor TSVECTOR,
  volltext       TSVECTOR,

  PRIMARY KEY (id)
);

CREATE INDEX volltext_autor_index ON dokument USING GIN(volltext_autor);

DROP TRIGGER IF EXISTS volltext_autor_update_trigger ON dokument;
DROP FUNCTION IF EXISTS volltext_autor_update_function();
DROP INDEX IF EXISTS volltext_autor_index;

-- Erzeuge den zugehörigen Index
CREATE INDEX volltext_autor_index ON dokument USING gin(volltext_autor);

-- Passe den Index bei jedem UPDATE und jedem INSERT an.
CREATE FUNCTION volltext_autor_update_function() RETURNS TRIGGER
LANGUAGE plpgsql
AS
$$
  BEGIN
    NEW.volltext_autor = TO_TSVECTOR('simple', NEW.autor);
    RETURN NEW;
  END
$$
;

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

Beispielsanfragen (Postgres)

SELECT id, autor, titel, inhalt
FROM   dokument
WHERE  volltext_autor @@ TO_TSQUERY('simple', 'Kowarschick')

Endtrunkierung

SELECT d.did, autor, titel, inhalt --*
FROM   woerterbuch w JOIN volltextindex v  ON w.wid = v.wid
                     JOIN dokument d       ON v.did = d.did
WHERE  wort LIKE '%gang'
;

SELECT d.did, autor, titel, inhalt --*
FROM   woerterbuch w JOIN volltextindex v  ON w.wid = v.wid
                     JOIN dokument d       ON v.did = d.did
WHERE  wort LIKE '@gnag%'
;