<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html>
<head profile="http://internetalchemy.org/2003/02/profile">
<link rel="foaf" type="application/rdf+xml" title="FOAF" href="http://www.openlinksw.com/dataspace/uda/about.rdf" />
<link rel="schema.dc" href="http://purl.org/dc/elements/1.1/" />
<meta name="dc.title" content="18. Free Text Search" />
<meta name="dc.subject" content="18. Free Text Search" />
<meta name="dc.creator" content="OpenLink Software Documentation Team ; " />
<meta name="dc.copyright" content="OpenLink Software, 1999 - 2009" />
<link rel="top" href="index.html" title="OpenLink Virtuoso Universal Server: Documentation" />
<link rel="search" href="/doc/adv_search.vspx" title="Search OpenLink Virtuoso Universal Server: Documentation" />
<link rel="parent" href="freetext.html" title="Chapter Contents" />
<link rel="prev" href="queryingftcols.html" title="Querying Free Text Indexes" />
<link rel="next" href="tablesandinternals.html" title="Generated Tables and Internals" />
<link rel="shortcut icon" href="../images/misc/favicon.ico" type="image/x-icon" />
<link rel="stylesheet" type="text/css" href="doc.css" />
<link rel="stylesheet" type="text/css" href="/doc/translation.css" />
<title>18. Free Text Search</title>
<meta http-equiv="Content-Type" content="text/xhtml; charset=UTF-8" />
<meta name="author" content="OpenLink Software Documentation Team ; " />
<meta name="copyright" content="OpenLink Software, 1999 - 2009" />
<meta name="keywords" content="" />
<meta name="GENERATOR" content="OpenLink XSLT Team" />
</head>
<body>
<div id="header">
<a name="txttrig" />
<img src="../images/misc/logo.jpg" alt="" />
<h1>18. Free Text Search</h1>
</div>
<div id="navbartop">
<div>
<a class="link" href="freetext.html">Chapter Contents</a> | <a class="link" href="queryingftcols.html" title="Querying Free Text Indexes">Prev</a> | <a class="link" href="tablesandinternals.html" title="Generated Tables and Internals">Next</a>
</div>
</div>
<div id="currenttoc">
<form method="post" action="/doc/adv_search.vspx">
<div class="search">Keyword Search: <br />
<input type="text" name="q" /> <input type="submit" name="go" value="Go" />
</div>
</form>
<div>
<a href="http://www.openlinksw.com/">www.openlinksw.com</a>
</div>
<div>
<a href="http://docs.openlinksw.com/">docs.openlinksw.com</a>
</div>
<br />
<div>
<a href="index.html">Book Home</a>
</div>
<br />
<div>
<a href="contents.html">Contents</a>
</div>
<div>
<a href="preface.html">Preface</a>
</div>
<br />
<div class="selected">
<a href="freetext.html">Free Text Search</a>
</div>
<br />
<div>
<a href="txtidxquickstart.html">Basic Concepts</a>
</div>
<div>
<a href="creatingtxtidxs.html">Creating Free Text Indexes</a>
</div>
<div>
<a href="queryingftcols.html">Querying Free Text Indexes</a>
</div>
<div class="selected">
<a href="txttrig.html">Text Triggers</a>
<div>
<a href="#createtxttrg" title="Creating Text Triggers">Creating Text Triggers</a>
<a href="#createddbobjs" title="Created Database Objects">Created Database Objects</a>
</div>
</div>
<div>
<a href="tablesandinternals.html">Generated Tables and Internals</a>
</div>
<div>
<a href="droptxtindex.html">Removing A Text Index</a>
</div>
<div>
<a href="droptxttrig.html">Removing A Text Trigger</a>
</div>
<div>
<a href="ftinternationalization.html">Internationalization & Unicode</a>
</div>
<div>
<a href="ftperformance.html">Performance</a>
</div>
<div>
<a href="fttfuncs.html">Free Text Functions</a>
</div>
<br />
</div>
<div id="text">
<a name="txttrig" />
<h2>18.4. Text Triggers</h2>
<p>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.</p>
<p>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.</p>
<a name="createtxttrg" />
<h3>18.4.1. Creating Text Triggers</h3>
<p>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.</p>
<p>
Syntax:
</p>
<div>
<pre class="programlisting">
CREATE TEXT TRIGGER ON <table> [(<data_column>)]
DROP TEXT TRIGGER ON <table> [(<data_column>)]
</pre>
</div>
<p>
The <data_column> is optional and must be a text indexed column.
</p>
<p>The table to be watched by a text trigger should have a free text indexed column. This
may or may not be XML data.</p>
<p>
The <table> must be text indexed before creating text trigger on it.
</p>
<br />
<a name="ex_txttrg" />
<div class="example">
<div class="exampletitle">Creating a Text Trigger</div>
<div>
<pre class="programlisting">
create table ftt (id integer not null primary key, dt long varchar);
create text xml index on ftt (dt);
create text trigger on ftt;
</pre>
</div>
<p>Adding queries to the text trigger</p>
<div>
<pre class="programlisting">
TT_QUERY_ftt ('virtuoso and server and international',
1, 'Virtuoso international support', 'iam@foo.bar');
</pre>
</div>
<p>
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.
</p>
<div>
<pre class="programlisting">
TT_XPATH_QUERY_ftt ('/chapter[@label = ''XI'']',
2, 'Chapter XI changes', '');
</pre>
</div>
<p>
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.
</p>
<div>
<pre class="programlisting">
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;
</pre>
</div>
<p>
will produce
</p>
<div>
<pre class="screen">
TTH_U_ID TTH_D_ID TTH_T_ID TTH_TS
_____________________________________________
1 1 1 2001-01-17 12:35:30
</pre>
</div>
<p>
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'.
</p>
</div>
<a name="createddbobjs" />
<h3>18.4.2. Created Database Objects</h3>
<p>
Text trigger hits table. Text trigger stores hits on documents matching a condition within this table.
</p>
<div>
<pre class="programlisting">
<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)
)
</pre>
</div>
<p>
Text trigger queries table, where the query definition procedure stores the user specific queries.
</p>
<div>
<pre class="programlisting">
<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)
)
</pre>
</div>
<p>
One query can add one or more rows to the queries table.
</p>
<p>
Text trigger users table, in it query definition procedure add a reference between query and user.
</p>
<div>
<pre class="programlisting">
<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)
)
</pre>
</div>
<div class="note">
<div class="notetitle">Note:</div>
<p>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>'.
</p>
</div>
<p>
Procedures that are used for adding text queries.
</p>
<div>
<pre class="programlisting">
TT_QUERY_<table_name> (in query_text varchar, in user_id integer,
in label varchar, in notification_address varchar)
</pre>
</div>
<p>
Used for adding XPATH queries.
</p>
<div>
<pre class="programlisting">
TT_XPATH_QUERY_<table_name> (in xpath_query_text varchar , in user_id integer,
in label varchar, in notification_address varchar)
</pre>
</div>
<p>
Used to send notifications to the users.
This procedure is usually called by the server event scheduler.
</p>
<div>
<pre class="programlisting">
TT_NOTIFY_<table_name> ();
</pre>
</div>
<br />
<table border="0" width="90%" id="navbarbottom">
<tr>
<td align="left" width="33%">
<a href="queryingftcols.html" title="Querying Free Text Indexes">Previous</a>
<br />Querying Free Text Indexes</td>
<td align="center" width="34%">
<a href="freetext.html">Chapter Contents</a>
</td>
<td align="right" width="33%">
<a href="tablesandinternals.html" title="Generated Tables and Internals">Next</a>
<br />Generated Tables and Internals</td>
</tr>
</table>
</div>
<div id="footer">
<div>Copyright© 1999 - 2009 OpenLink Software All rights reserved.</div>
<div id="validation">
<a href="http://validator.w3.org/check/referer">
<img src="http://www.w3.org/Icons/valid-xhtml10" alt="Valid XHTML 1.0!" height="31" width="88" />
</a>
<a href="http://jigsaw.w3.org/css-validator/">
<img src="http://jigsaw.w3.org/css-validator/images/vcss" alt="Valid CSS!" height="31" width="88" />
</a>
</div>
</div>
</body>
</html>
distrib
>
Fedora
>
14
>
x86_64
>
media
>
updates
>
by-pkgid
>
71d40963b505df4524269198e237b3e3
>
files
>
903
