# 18. Free Text Search

Chapter Contents | Prev | Next
Keyword Search:
www.openlinksw.com
docs.openlinksw.com
Book Home
Contents
Preface
Free Text Search
Basic Concepts
Creating Free Text Indexes
Querying Free Text Indexes
Text Triggers
Creating Text Triggers Created Database Objects
Generated Tables and Internals
Removing A Text Index
Removing A Text Trigger
Internationalization & Unicode
Performance
Free Text Functions

## 18.4. Text Triggers

The text trigger mechanism allows implementing a broad range of content tracking functionality. The idea is storing free text or XPATH queries in association to a text indexed column of a table. When the content of the table changes through inserts or updates, the new data is matched against a base of stored queries and hits are marked into a separate table. The data being tracked may either be plain text or XML. In the event of XML, both free text and XPATH queries can be stored.

The benefit of the text trigger system as opposed to other forms of periodic content tracking is that the incoming data itself indexes a base of stored queries instead of a base of stored queries repeatedly indexing the database. This means that only the changes are compared to the stored queries and that queries that could not even in principle match will not be tried. This results in a qualitatively better performance and scalability than repeatedly running a batch of queries over updated data and thus makes possible personalized information filtering applications that would be impractical with other approaches.

### 18.4.1. Creating Text Triggers

The CREATE TEXT TRIGGER statement creates a set of tables and procedures named after the table and column being watched. The TT_QUERY_<xx> table contains the set of queries, the TT_HIT_<xx> table records the matches and the TT_USER_<xx> table can be used to map stored queries to specific users that should be notified.

Syntax:

    CREATE TEXT TRIGGER ON <table> [(<data_column>)]
    DROP TEXT TRIGGER ON <table> [(<data_column>)]

The <data_column> is optional and must be a text indexed column.

The table to be watched by a text trigger should have a free text indexed column. This may or may not be XML data.

The <table> must be text indexed before creating text trigger on it.

Creating a Text Trigger

    create table ftt (id integer not null primary key, dt long varchar);
    create text xml index on ftt (dt);
    create text trigger on ftt;

Adding queries to the text trigger

    TT_QUERY_ftt ('virtuoso and server and international',
      1, 'Virtuoso international support', 'iam@foo.bar');

this adds a query which will filter only documents matching words 'virtuoso', 'server' and 'international', named 'Virtuoso international support' of the user with Id equal to 1 with e-mail notification.

    TT_XPATH_QUERY_ftt ('/chapter[@label = ''XI'']',
      2, 'Chapter XI changes', '');

this will add an XPATH query which will filter only XML documents matching Chapter XI, named 'Chapter XI changes' of the user with Id equal to 2 without e-mail notification.

    insert into ftt values (1, 'virtuoso server international');
    insert into ftt values (2, 'virtuoso international');

    select TTH_D_ID, TTH_T_ID, TTH_U_ID, TTH_TS from ftt_dt_HIT;

will produce

    TTH_U_ID    TTH_D_ID         TTH_T_ID    TTH_TS
    _____________________________________________

    1           1                1           2001-01-17 12:35:30

Meaning that row from 'ftt' with id equal to 1 matches query with TT_ID equal to 1 defined for user with ID equal to 1. Also the hit is registered on '2001-01-17 12:35:30'.

### 18.4.2. Created Database Objects

Text trigger hits table. Text trigger stores hits on documents matching a condition within this table.

    <table_name>_<data_column>_HIT (
      TTH_U_ID INTEGER,   -- references User table TTU_U_ID
      TTH_D_ID ANY,       -- references matching document id
      TTH_T_ID INTEGER,   -- references matching query TT_ID
      TTH_TITLE VARCHAR,  -- user application specific
      TTH_URL VARCHAR,    -- user application specific
      TTH_TS TIMESTAMP,   -- time of registering a hit
      TTH_NOTIFY VARCHAR, -- e-mail address for notification
      PRIMARY KEY (TTH_U_ID, TTH_TS, TTH_D_ID, TTH_T_ID)
    )

Text trigger queries table, where the query definition procedure stores the user specific queries.

    <table_name>_<data_column>_QUERY (
      TT_WORD VARCHAR,  -- the most effective word for searching
      TT_ID INTEGER,    -- id of query
      TT_QUERY VARCHAR, -- text of the query, in case of XPATH query this
    				-- column contains a serialized value
      TT_CD VARCHAR,    -- user data
      TT_COMMENT VARCHAR, -- Human readable label with general purpose
      TT_XPATH VARCHAR,   -- text of XPATH query
      TT_PREDICATE VARCHAR, -- Virtuoso/PL function hook
      PRIMARY KEY (TT_WORD, TT_ID)
    )

One query can add one or more rows to the queries table.

Text trigger users table, in it query definition procedure add a reference between query and user.

    <table_name>_<data_column>_USER (
      TTU_T_ID INTEGER, -- references text query TT_ID
      TTU_U_ID INTEGER, -- references application specific users table ID
      TTU_NOTIFY VARCHAR, -- e-mail address list for notification
      TTU_COMMENT VARCHAR, -- Human readable label of query definition.
      PRIMARY KEY (TTU_T_ID, TTU_U_ID)
    )

Note:

In the case of e-mail notification to the main addresses the string passed as address list must be in the following format: '<u1@foo.com>, <u2@foo.bar>'.

Procedures that are used for adding text queries.

    TT_QUERY_<table_name> (in query_text varchar, in user_id integer,
      in label varchar, in notification_address varchar)

Used for adding XPATH queries.

    TT_XPATH_QUERY_<table_name> (in xpath_query_text varchar , in user_id integer,
    in label varchar, in notification_address varchar)

Used to send notifications to the users. This procedure is usually called by the server event scheduler.

    TT_NOTIFY_<table_name> ();

Previous Querying Free Text Indexes | Chapter Contents | Next Generated Tables and Internals