Storing Documents in a Fulltext System

Fulltext in 602SQL

Document Fulltext Search

Fulltext Systems - Indexing

Automatic Document Indexing

Automatic document indexing ensures that the fulltext index is updated immediately when documents are created, deleted or updated. Therefore, the fulltext search is always based on the current document set.

Automatic index execution is based on the fulltext triggers specifications of the fulltext system. You can change the automatic indexing using the ALTER FULLTEXT statement.

Each automatic index request contains the document table name and the names of two columns: the document column and the column containing the document number. It must also contain the information on how the document is stored (in a table or external file) and may contain information about the internal document format. The fulltext system definition can be controlled from the fulltext designer interface.

If a document is stored in an external file, the 602SQL Server has no way of determining when the file is modified. It is therefore necessary to execute the UPDATE SQL statement on the column with this document or manually call the index function described below.

Example:

A definition of a fulltext object with fulltext triggers for automatic indexing:

CREATE FULLTEXT FT_TEST LANGUAGE 0 LEMMATIZED NOT SUBSTRUCTURED
(DOCTAB ID_DOC DOC,'TXT')

Manual Document Indexing

You must be able to control when the fulltext index is updated using manual document indexing (e.g. schedule index updates after business hours).

The Fulltext_index_doc function is used to explicitly index a specific document.

If the document is stored in an external file, it is necessary to call the index function on the computer with access to this file. Therefore, the SQL procedure executed on a database server usually cannot index a document stored on a client computer. The Fulltext_index_doc client API function serves this purpose.

If a document or the document content is deleted, it is necessary to remove it from the index with the Fulltext_remove_doc SQL function.

The Fulltext_remove_doc function does not need to be called after a document change if the Fulltext_index_doc function will be called (the old content will be removed automatically).

You can automate document indexing by placing the manual index functions in SQL triggers on the document table.

Example:

CREATE FULLTEXT FT_TEST2 LANGUAGE 0 LEMMATIZED NOT SUBSTRUCTURED
()

An example of an SQL trigger:

TRIGGER `Doctab_UPD_ftx` AFTER UPDATE OF doc
ON `Doctab`
REFERENCING NEW AS newrec FOR EACH ROW
BEGIN
  CALL Fulltext_index_doc('.ft_test2', newrec.id_doc, newrec.doc, 'txt', 0);
END

An example of an SQL procedure for deferred indexing:

PROCEDURE `deferred_indexing`( );
BEGIN
  DECLARE i INTEGER DEFAULT 0;
  FOR row AS cx CURSOR FOR
    SELECT id_doc, doc, indexed 
    FROM Doctab
    WHERE NOT indexed
  DO
    CALL Fulltext_index_doc('.ft_test_deferred', row.id_doc, row.doc, 'txt', 0);
    UPDATE SET indexed = TRUE WHERE CURRENT OF cx;
    SET i = i+1;
  END FOR;
  CALL Log_write(Int2str(i)+' document(s) indexed');
END

Storing Documents in a Fulltext System

Fulltext in 602SQL

Document Fulltext Search