<!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="txttrig.html" title="Text Triggers" />
<link rel="next" href="droptxtindex.html" title="Removing A Text Index" />
<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="tablesandinternals" />
<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="txttrig.html" title="Text Triggers">Prev</a> | <a class="link" href="droptxtindex.html" title="Removing A Text Index">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>
<a href="txttrig.html">Text Triggers</a>
</div>
<div class="selected">
<a href="tablesandinternals.html">Generated Tables and Internals</a>
<div>
<a href="#gentabsaprocs" title="Generated Tables and Procedures ">Generated Tables and Procedures </a>
<a href="#procs" title="The procedures are:">The procedures are:</a>
<a href="#fttrigtblsandprocs" title="Tables and Procedures Created By Text Triggers">Tables and Procedures Created By Text Triggers</a>
</div>
</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="tablesandinternals" />
<h2>18.5. Generated Tables and Internals</h2>
<a name="gentabsaprocs" />
<h3>18.5.1. Generated Tables and Procedures </h3>
<p>
vt_create_text_index makes a separate table for storing the text index information and
separate procedures and triggers for maintaining this data. These are
automatically dropped if the original table is dropped.
</p>
<p>
The updates to the indexed column are recorded in a separate update tracking table. This table, named
VTLOG_<qualifier>_<owner>_<table> contains a row for each row in the indexed table that has been changed
since the text index was last updated.
</p>
<p>
The text index is stored in a table named <table>_<column>_WORDS.
The generated tables are made under the qualifier that is current at the time of their creation.
The owner is the creating user.
</p>
<br />
<a name="procs" />
<h3>18.5.2. The procedures are:</h3>
<div>
<pre class="programlisting">
VT_INDEX_<qualifier>_<owner>_<table> (in flag integer)
</pre>
</div>
<p>
This re-indexes the table. A flag of 0 makes the index, a flag of 1 deletes data found in
the table from the index.
</p>
<div>
<pre class="programlisting">
VT_INC_INDEX_<qualifier>_<owner>_<table> (in flag integer) ()
</pre>
</div>
<p>
This function refreshes the index using the change tracking information in the
VTLOG_ table.
</p>
<br />
<a name="fttrigtblsandprocs" />
<h3>18.5.3. Tables and Procedures Created By Text Triggers</h3>
<div>
<pre class="programlisting">
- [TARGET_TABLE_NAME]_[DATA_COLUMN_NAME]_HIT
columns
TTH_D_ID INTEGER - document ID, references unique id of [TARGET_TABLE]
TTH_T_ID INTEGER - query ID, references QUERY table
TTH_U_ID INTEGER - user ID, references USER table
TTH_TS TIMESTAMP - date and time of retrieval
TTH_NOTIFY VARCHAR - e-mail address of user for notification
TTH_TITLE VARCHAR - not used (can be filled with user-defined trigger)
TTH_URL VARCHAR - not used (can be filled with user-defined trigger)
- [TARGET_TABLE_NAME]_[DATA_COLUMN_NAME]_QUERY
columns
TT_ID INTEGER - Unique ID of query
TT_QUERY VARCHAR - query text
TT_WORD VARCHAR - the best word for query
TT_COMMENT VARCHAR - Description
TT_CD VARCHAR - user data (not used)
TT_PREDICATE VARCHAR - not used
TT_XPATH VARCHAR - XPATH expression. If specified,
the test is this XPATH predicate,
see separate section.
- [TARGET_TABLE_NAME]_[DATA_COLUMN_NAME]_USER
columns
TTU_U_ID INTEGER - unique user ID can reference SYS_USERS.
TTU_T_ID INTEGER - query ID, references QUERY table
TTU_COMMENT VARCHAR - Description
TTU_NOTIFY VARCHAR - e-mail address of user for notification
</pre>
</div>
<p>Note that the queries are available given a table wide query identifier
and an 'entry point' word. Such a word is a word that must occur in the document
in order for the query to have a possibility of matching the document, The
query text and other attributes are denormalized so that the primary key is the
word, id pair when the id itself is unique. Note that in the case of an AND of
words, the least frequent of the words will be used as unique entry point of the
query, so that it will not be tried on documents that do not contain this word.
However, a query with OR'ed terms may have several such words, hence the
possibility of multiple rows in the query table for the same query.</p>
<p>The TT_USER_<xx> table maps from the query to a user. The idea of
this is to allow a single query to have multiple users. Consider an application
which allows creating personalized information filtering profiles. It is to be
expected that multiple users would store the same profiles. Therefore the link
between the user and the query is entitized as this table. The user specific
comment and notification mode are thus stored here, not with the query. The
notification mode itself is application dependent. The user id is an application
dependent id that can be used to reference application user entities. Some
applications may use this whereas other applications will have all queries on
a single user.</p>
<p>When a hit is noticed an entry is made into the TT_HIT_<xx> table.
One row is inserted for each unique document id, query id, user id combination
for which the document matches the query and there is a link to a user from the
query through TT_USER_<xx>. A query with no row in TT_USEER_<xx> is
an integrity error. The number of times the pattern is found in each document
or its free text hit score has no effect on the hit insertion.</p>
<p>One may note that defining application specific triggers on the hit table
can be used to add immediate application reactions to incoming data.</p>
<p>The free text triggers are matched against the new content immediately
before the content is inserted into the free text index. Therefore the batch
mode setting affects the time of matching. In all situations, the matching takes
place after the data is inserted but before the free text index is updated. If
text index maintenance is in synchronous (non-batched) mode, the text trigger
match and hit generation is in the same transaction as the update to the content
being watched.</p>
<a name="fttprocs" />
<h4>18.5.3.1. Procedures</h4>
<p>For queries definition</p>
<div>
<pre class="programlisting">tt_query_[TARGET_TABLE_NAME] (
in [query text] varchar, - query expression
in [user_id] integer, - user id references SYS_USERS or SYS_DAV_USER
in [comment] varchar, - description
in [e-mail or empty] varchar) - e-mail address for user notification</pre>
</div>
<p>or hits registration (used inside text index procedures)</p>
<div>
<pre class="programlisting">vt_hits_[TARGET_TABLE_NAME] (inout [batch] any, inout [words array] any)</pre>
</div>
<br />
<a name="ftttrigs" />
<h4>18.5.3.2. Triggers</h4>
<p>for hits removal after document delete occurred</p>
<div>
<pre class="programlisting">[TARGET_TABLE_NAME]_FTT_D</pre>
</div>
<br />
<a name="fttexamples" />
<h4>18.5.3.3. Examples</h4>
<div>
<pre class="programlisting">
-- create a table
create table T1 (id integer, dt varchar, primary key (id));
-- define text index
create text index on T1 (dt);
-- create text trigger
create text trigger on T1;
-- define an query
tt_query_T1 ('xyz and abc', 1, 'This is a test query', null);
-- do some inserts
insert into T1 (id, dt) values (1, 'xyz');
insert into T1 (id, dt) values (2, 'xyz abc');
insert into T1 (id, dt) values (3, 'abc');
update T1 set dt = 'xyz qwe abc' where id = 2;
select TTH_TS, dt from T1, T1_dt_HIT where id = TTH_D_ID order by TTH_TS desc;
-- produces following
TTH_TS dt
BINARY VARCHAR
_______________________________________________________________________________
2000-10-24 18:25:53 xyz qwe abc
2000-10-24 18:25:53 xyz qwe abc
</pre>
</div>
<br />
<br />
<table border="0" width="90%" id="navbarbottom">
<tr>
<td align="left" width="33%">
<a href="txttrig.html" title="Text Triggers">Previous</a>
<br />Text Triggers</td>
<td align="center" width="34%">
<a href="freetext.html">Chapter Contents</a>
</td>
<td align="right" width="33%">
<a href="droptxtindex.html" title="Removing A Text Index">Next</a>
<br />Removing A Text Index</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
>
892
