Firebird: Volltextindex

2 - Texttabellen

Die Generierung des Volltextindex der Datenbank wird durch Trigger in den Texttabellen angestoßen und in einigen STORED PROCEDURES erstellt.

Beispieltexttabelle

CREATE TABLE "texts_test" (
    "PK_txt"               INTEGER NOT NULL,
    "CL_txt_text"          BLOB SUB_TYPE 1 SEGMENT SIZE 80,
    "CL_txt_length"        INTEGER NOT NULL,
    "CL_txt_bytes"         INTEGER NOT NULL,
    "CL_txt_hash"          BIGINT NOT NULL,
    "CL_txt_update_index"  SMALLINT DEFAULT 0 NOT NULL,
    "CL_txt_update_delay"  SMALLINT DEFAULT 0 NOT NULL,
    "CL_txt_inserted"      TIMESTAMP DEFAULT 'NOW' NOT NULL,
    "CL_txt_updated"       TIMESTAMP
);

ALTER TABLE "texts" ADD CONSTRAINT "PK_texts"
    PRIMARY KEY ("PK_txt");

Trigger der Texttabellen

In den Texttabellen gibt es typischerweise zwei Trigger für den Volltextindex. Der erste Trigger wird vor einem Update oder Insert gefeuert und prüft Änderungen am Textfeld. Wurde der Datensatz neu angelegt oder hat sich der Text geändert, wird ein Flag gesetzt (im Beispiel "CL_txt_update_index"), das im zweiten Trigger ausgewertet wird.

Erster Trigger

CREATE OR ALTER TRIGGER "texts_BIU0" FOR "texts"
ACTIVE BEFORE INSERT OR UPDATE POSITION 0
AS
DECLARE VARIABLE TEXT_BUF BLOB SUB_TYPE 1 SEGMENT SIZE 80;
BEGIN

  /* PK */
  IF (NEW."PK_txt" IS NULL)
  THEN
    NEW."PK_txt" = GEN_ID("gen_PK", 1);

  /* Zeitpunkt */
  IF (INSERTING)
  THEN
  BEGIN
    NEW."CL_txt_inserted" = 'NOW';
  END
  ELSE
  BEGIN
    IF (UPDATING)
    THEN
    BEGIN
      NEW."CL_txt_inserted" = OLD."CL_txt_inserted";
      NEW."CL_txt_updated"  = 'NOW';
    END
  END

  /* Texteigenschaften */
  TEXT_BUF = COALESCE(NEW."CL_txt_text", '');
  NEW."CL_txt_length" = CHAR_LENGTH (TEXT_BUF);
  NEW."CL_txt_bytes"  = OCTET_LENGTH(TEXT_BUF);
  NEW."CL_txt_hash"   = HASH        (TEXT_BUF);

  /* Flag zur Berechnung des Volltextindex */
  IF (COALESCE(NEW."CL_txt_update_index", 0) = 0)
  THEN
  BEGIN
    IF (
         (NEW."CL_txt_length" <> COALESCE(OLD."CL_txt_length", 0))
         OR
         (NEW."CL_txt_bytes"  <> COALESCE(OLD."CL_txt_bytes" , 0))
         OR
         (NEW."CL_txt_hash"   <> COALESCE(OLD."CL_txt_hash"  , 0))
       )
    THEN
      NEW."CL_txt_update_index" = 1;
    ELSE
      NEW."CL_txt_update_index" = 0;
  END

END

Zweiter Trigger

Der zweite Trigger wird gefeuert, nachdem der Datensatz geschrieben wurde. Hier wird das Flag aus dem ersten Trigger ausgewertet, und gegebenenfalls der Volltextindex für das geänderte Textfeld erzeugt. Das hat den Vorteil, daß das Flag auch manuell gesetzt werden kann, um den Volltextindex manuell zu erzeugen.

Es gibt noch ein zweites Flag (im Beispiel "CL_txt_update_delay"), das dazu führt, daß der Trigger den Volltextindex nicht sofort erzeugt. Das kann z.B. dazu verwendet werden, die Wartezeit bei der Indizierung in einen eigenen Programm-Thread auszulagern. Dazu wird beim ändern des Textes das Flag auf 1 gesetzt. Dadurch wird die Indizierung nicht ausgeführt und die Speicherung des Datensatzes läuft normal schnell. In einem eigenen Thread wird dann das Flag mit einem UPDATE auf 0 gesetzt und die Indizierung wird gestartet.

CREATE OR ALTER TRIGGER "texts_AIU0" FOR "texts"
ACTIVE AFTER INSERT OR UPDATE POSITION 0
AS

  DECLARE VARIABLE BLOCK_SIZE INTEGER;
  DECLARE VARIABLE ID         INTEGER;
  DECLARE VARIABLE WORD_INDEX INTEGER;

BEGIN

  IF (  
       (NEW."CL_txt_update_index" = 1)
       AND
       (NEW."CL_txt_update_delay" = 0)
     )
  THEN 
  BEGIN

    /* Text locken, damit kein anderer Prozess daran arbeitet. */
         SELECT "PK_txt"
           FROM "texts"
          WHERE "PK_txt" = NEW."PK_txt"
     FOR UPDATE OF "CL_txt_text"
      WITH LOCK
           INTO :ID;

    /* Blockgröße bestimmen */
    EXECUTE PROCEDURE "prc_domain_text_length"('dom_TextCache')
      RETURNING_VALUES BLOCK_SIZE;

    /* Aufruf je nach Textlänge */
    IF (CHAR_LENGTH(NEW."CL_txt_text") <= BLOCK_SIZE)
    THEN
      EXECUTE PROCEDURE "prc_ftx_index_char"(NEW."CL_txt_text", 1, 1, 1, 1, 4)
        RETURNING_VALUES WORD_INDEX;
    ELSE
      EXECUTE PROCEDURE "prc_ftx_index_text"(NEW."CL_txt_text", 1, 1, 1, 1, 4)
        RETURNING_VALUES WORD_INDEX;

    /* alte Verknüpfungen löschen */
    IF (UPDATING)
    THEN
      DELETE FROM
        "text_link_words"
       WHERE
         "FK_tlw_txt" = :ID;

    /* temporäre Daten kopieren */
    INSERT INTO
      "text_link_words"
      ("FK_tlw_txt", "FK_tlw_wrd", "CL_tlw_index", "CL_tlw_position", "FK_tlw_wty")
    SELECT
      :ID, "FK_ttw_wrd", "CL_ttw_index", "CL_ttw_position", "FK_ttw_wty"
    FROM
      "_temp_text_link_words";

    /* temporäre Daten löschen */
    DELETE FROM
      "_temp_text_link_words";

    /* Update-Flag zurücksetzen */
    UPDATE
      "texts"
    SET
      "CL_txt_update_index" = 0
    WHERE
      "PK_txt" = :ID;

  END

  WHEN ANY
  DO
  BEGIN
    /* temporäre Daten löschen */
    DELETE FROM
      "_temp_text_link_words";
  END

END
zurückweiter

Index