Firebird: Volltextindex

4 - Beispieldatenbank

Der Volltextindex liegt im Archiv in zwei Versionen vor. Einmal in FullTextIndexTest.sql als Beispiel für eine Implementierung in ein Projekt. Die zweite Variante in FullTextIndexStatistic.sql speichert in der Tabelle "texts" die Zeiten, die zur Ausführung der einzelnen Trigger-Teile gebraucht wurden und eventuell aufgetretene Fehler. Zusätzlich gibt es einige Views, die diese statistischen Informationen auswerten.

Views

"view_counts"Zählt die Datensätze der einzelnen Tabelle.
"view_text_stats"Berechnet die Statistik für die Textindizierung.
"view_word_types"Zählt die Verwendung der einzelnen Worttypen in den vorhandenen Texten.
"view_words"Zeigt die Wörter in den Texten.

Suche nach einem ganze Wort

In diesem Beispiel wird in den Texten nach dem ganzen Wort 'firebird' gesucht. Als Ergebnis erhält man alle Texte, die dieses Wort enthalten und dazu zwei Listen mit den Textindizes und Textpositionen des Wortes.
    WITH
    words_1 AS
    (
          /* Join für zu suchendes Wort */
          SELECT "FK_tlw_txt",      /* FK auf Texttabelle          */
                 "CL_tlw_index",    /* Index des Wortes im Text    */
                 "CL_tlw_position"  /* Position des Wortes im Text */
            FROM "words"
                 /* Verküpfung des Wortes mit dem Volltextindex */
      INNER JOIN "text_link_words" ON "FK_tlw_wrd" = "PK_wrd"
                 /* zu suchendes Wort mit Lower() wg. berechnetem Index */
           WHERE LOWER("CL_wrd_word") = 'firebird'
                 /* in diesem Fall nur ganze Wörter */
             AND "FK_tlw_wty" = 0
    )
    SELECT "PK_txt",      /* PK des Textes */
           "CL_txt_text", /* der Text      */
           /* Liste der gefundenen Indizes im Text */
           LIST(w1."CL_tlw_index"   , ';') AS "CL_tlw_index_1",
           /* Liste der gefundenen Positionen im Text */
           LIST(w1."CL_tlw_position", ';') AS "CL_tlw_position_1"
      FROM "texts"
           /* Text mit gesuchtem Wort verküpfen */
INNER JOIN words_1 w1 ON "PK_txt" = w1."FK_tlw_txt"
  GROUP BY "PK_txt",
           "CL_txt_text"

Suche nach zwei ganzen Wörtern

In diesem Beispiel wird in den Texten nach dem ganzen Wort 'datenbank' und dem ganzen Wort 'firebird' gesucht. Als Ergebnis erhält man alle Texte, die diese Wörter enthalten und dazu die Listen mit den Textindizes und Textpositionen der Wörter.
    WITH
    words_1 AS
    (
          /* JOIN für zu suchendes Wort 1 */
          SELECT "FK_tlw_txt",      /* FK auf Texttabelle          */
                 "CL_tlw_index",    /* Index des Wortes im Text    */
                 "CL_tlw_position"  /* Position des Wortes im Text */
            FROM "words"
                 /* Verküpfung des Wortes mit dem Volltextindex */
      INNER JOIN "text_link_words" ON "FK_tlw_wrd" = "PK_wrd"
                 /* zu suchendes Wort mit LOWER wg. berechnetem Index */
           WHERE LOWER("CL_wrd_word") = 'datenbank'
                 /* in diesem Fall nur ganze Wörter */
             AND "FK_tlw_wty" = 0
    ),
    words_2 AS
    (
          /* JOIN für zu suchendes Wort 2 */
          SELECT "FK_tlw_txt",      /* FK auf Texttabelle          */
                 "CL_tlw_index",    /* Index des Wortes im Text    */
                 "CL_tlw_position"  /* Position des Wortes im Text */
            FROM "words"
                 /* Verküpfung des Wortes mit dem Volltextindex */
      INNER JOIN "text_link_words" ON "FK_tlw_wrd" = "PK_wrd"
                 /* zu suchendes Wort mit LOWER wg. berechnetem Index */
           WHERE LOWER("CL_wrd_word") = 'firebird'
                 /* in diesem Fall nur ganze Wörter */
             AND "FK_tlw_wty" = 0
    )
    SELECT "PK_txt",      /* PK des Textes */
           "CL_txt_text", /* der Text      */
           /* Liste der gefundenen Indizes für 'datenbank' im Text */
           LIST(w1."CL_tlw_index"   , ';') AS "CL_tlw_index_1",
           /* Liste der gefundenen Positionen für 'datenbank' im Text */
           LIST(w1."CL_tlw_position", ';') AS "CL_tlw_position_1",
           /* Liste der gefundenen Indizes für 'firebird' im Text */
           LIST(w2."CL_tlw_index"   , ';') AS "CL_tlw_index_2",
           /* Liste der gefundenen Positionen 'firebird' im Text */
           LIST(w2."CL_tlw_position", ';') AS "CL_tlw_position_2"
      FROM "texts"
           /* Text mit gesuchtem Wort verküpfen */
INNER JOIN words_1 w1 ON "PK_txt" = w1."FK_tlw_txt"
INNER JOIN words_2 w2 ON "PK_txt" = w2."FK_tlw_txt"
  GROUP BY "PK_txt",
           "CL_txt_text"

Suche nach einer Phrase

Um nach einer Phrase zu suchen muß nur überprüft werden, ob sich die Wortindizes im Text in einer Reihenfolge befinden. Das kann einfach durch eine Subtraktion im WHERE-Teil erfolgen. In diesem Beispiel wird nach 'datenbank firebird' gesucht.
    WITH
    words_1 AS
    (
          /* JOIN für zu suchendes Wort 1 */
          SELECT "FK_tlw_txt",      /* FK auf Texttabelle          */
                 "CL_tlw_index",    /* Index des Wortes im Text    */
                 "CL_tlw_position"  /* Position des Wortes im Text */
            FROM "words"
                 /* Verküpfung des Wortes mit dem Volltextindex */
      INNER JOIN "text_link_words" ON "FK_tlw_wrd" = "PK_wrd"
                 /* zu suchendes Wort mit LOWER wg. berechnetem Index */
           WHERE LOWER("CL_wrd_word") = 'datenbank'
                 /* in diesem Fall nur ganze Wörter */
             AND "FK_tlw_wty" = 0
    ),
    words_2 AS
    (
          /* JOIN für zu suchendes Wort 2 */
          SELECT "FK_tlw_txt",      /* FK auf Texttabelle          */
                 "CL_tlw_index",    /* Index des Wortes im Text    */
                 "CL_tlw_position"  /* Position des Wortes im Text */
            FROM "words"
                 /* Verküpfung des Wortes mit dem Volltextindex */
      INNER JOIN "text_link_words" ON "FK_tlw_wrd" = "PK_wrd"
                 /* zu suchendes Wort mit LOWER wg. berechnetem Index */
           WHERE LOWER("CL_wrd_word") = 'firebird'
                 /* in diesem Fall nur ganze Wörter */
             AND "FK_tlw_wty" = 0
    )
    SELECT "PK_txt",      /* PK des Textes */
           "CL_txt_text", /* der Text      */
           /* Liste der gefundenen Indizes für 'datenbank' im Text */
           LIST(w1."CL_tlw_index"   , ';') AS "CL_tlw_index_1",
           /* Liste der gefundenen Positionen für 'datenbank' im Text */
           LIST(w1."CL_tlw_position", ';') AS "CL_tlw_position_1",
           /* Liste der gefundenen Indizes für 'firebird' im Text */
           LIST(w2."CL_tlw_index"   , ';') AS "CL_tlw_index_2",
           /* Liste der gefundenen Positionen 'firebird' im Text */
           LIST(w2."CL_tlw_position", ';') AS "CL_tlw_position_2"
      FROM "texts"
           /* Text mit gesuchtem Wort verküpfen */
INNER JOIN words_1 w1 ON "PK_txt" = w1."FK_tlw_txt"
INNER JOIN words_2 w2 ON "PK_txt" = w2."FK_tlw_txt"
           /* Einschränkung auf eine Phrase */
     WHERE w1."CL_tlw_index" = w2."CL_tlw_index" - 1
  GROUP BY "PK_txt",
           "CL_txt_text"