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.
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.
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'.
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) )
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 |