<!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.subject" content="SQL" />
<meta name="dc.subject" content="SQL Reference" />
<meta name="dc.subject" content="Select" />
<meta name="dc.subject" content="Update" />
<meta name="dc.subject" content="delete" />
<meta name="dc.subject" content="Select Statement" />
<meta name="dc.subject" content="SQL Syntax" />
<meta name="dc.subject" content="Syntax" />
<meta name="dc.title" content="8. SQL Reference" />
<meta name="dc.subject" content="8. SQL Reference" />
<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="sqlreference.html" title="Chapter Contents" />
<link rel="prev" href="transitivityinsQL.html" title="Transitivity in SQL" />
<link rel="next" href="contents.html" title="Contents" />
<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>8. SQL Reference</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="SQL; SQL Reference; Select; Update; delete; Select Statement; SQL Syntax; Syntax; " />
<meta name="GENERATOR" content="OpenLink XSLT Team" />
</head>
<body>
<div id="header">
<a name="sqlreffastphrasematch" />
<img src="../images/misc/logo.jpg" alt="" />
<h1>8. SQL Reference</h1>
</div>
<div id="navbartop">
<div>
<a class="link" href="sqlreference.html">Chapter Contents</a> | <a class="link" href="transitivityinsQL.html" title="Transitivity in SQL">Prev</a> | <a class="link" href="sqlprocedures.html" title="SQL Procedure Language Guide">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="sqlreference.html">SQL Reference</a>
</div>
<br />
<div>
<a href="sqlrefDATATYPES.html">Datatypes</a>
</div>
<div>
<a href="udt.html">User Defined Types</a>
</div>
<div>
<a href="sqlrefxmldatatype.html">XML Column Type</a>
</div>
<div>
<a href="catidentifiers.html">Identifier Case & Quoting</a>
</div>
<div>
<a href="wideidentifiers.html">Wide Character Identifiers</a>
</div>
<div>
<a href="QUALIFIEDNAMES.html">Qualified Names</a>
</div>
<div>
<a href="litsbraceescs.html">Literals, Brace Escapes</a>
</div>
<div>
<a href="CREATETABLE.html">CREATE TABLE Statement</a>
</div>
<div>
<a href="DROPTABLE.html">DROP TABLE Statement</a>
</div>
<div>
<a href="CREATEINDEX.html">CREATE INDEX Statement</a>
</div>
<div>
<a href="DROPINDEX.html">DROP INDEX Statement</a>
</div>
<div>
<a href="ALTERTABLE.html">ALTER TABLE Statement</a>
</div>
<div>
<a href="CREATEVIEW.html">CREATE VIEW Statement</a>
</div>
<div>
<a href="CREATEXMLSCHEMA.html">CREATE XML SCHEMA Statement</a>
</div>
<div>
<a href="DROPXMLSCHEMA.html">DROP XML SCHEMA Statement</a>
</div>
<div>
<a href="sequenceobjects.html">Sequence Objects</a>
</div>
<div>
<a href="insertSTMT.html">INSERT Statement</a>
</div>
<div>
<a href="updatestmt.html">UPDATE Statement</a>
</div>
<div>
<a href="SELECTSTMT.html">SELECT Statement</a>
</div>
<div>
<a href="COMMIT_ROLLBACK.html">COMMIT WORK, ROLLBACK WORK Statement</a>
</div>
<div>
<a href="CHECKPOINT.html">CHECKPOINT, SHUTDOWN Statement</a>
</div>
<div>
<a href="spasviewsandtables.html">Stored Procedures as Views & Derived Tables</a>
</div>
<div>
<a href="GRANT.html">GRANT, REVOKE Statement</a>
</div>
<div>
<a href="SETstmt.html">SET Statement</a>
</div>
<div>
<a href="anytimequeries.html">Anytime Queries</a>
</div>
<div>
<a href="besteffortunion.html">Best Effort Union</a>
</div>
<div>
<a href="aggregates.html">Standard and User-Defined Aggregate Functions</a>
</div>
<div>
<a href="sqloptimizer.html">Virtuoso SQL Optimization</a>
</div>
<div>
<a href="sqlinverse.html">SQL Inverse Functions</a>
</div>
<div>
<a href="GRAMMAR.html">SQL Grammar</a>
</div>
<div>
<a href="BITMAPINDICES.html">Bitmap Indices</a>
</div>
<div>
<a href="transitivityinsQL.html">Transitivity in SQL</a>
</div>
<div class="selected">
<a href="sqlreffastphrasematch.html">Fast Phrase Match Processor</a>
<div>
<a href="#sqlreffastphrasematchscl" title="Phrases, Phrase Sets and Phrase Classes">Phrases, Phrase Sets and Phrase Classes</a>
<a href="#sqlreffastphrasematchapi" title="Phrase Set Configuration API">Phrase Set Configuration API</a>
<a href="#sqlreffastphrasematchsadv" title="Advertisers and Advertisement Rules">Advertisers and Advertisement Rules</a>
<a href="#sqlreffastphraseexample" title="Example">Example</a>
</div>
</div>
<br />
</div>
<div id="text">
<a name="sqlreffastphrasematch" />
<h2>8.33. Fast Phrase Match Processor</h2>
<p>An "annotation phrase" is a keyword or key phrase associated with some application specific data
and a uniform "annotation phrase set" may map dictionary words to their descriptions or ICAO codes
to airport names and co-ordinates or geographical names to maps or Wikipedia topics to links to
Wikipedia pages. An application may need to scan a given text and find all occurrences of phrases
from given phrase set, for various purposes, e.g., to improve the resource with additional hyperlinks,
to replace technical codes with user-friendly names, or to suggest appropriate tags and keywords.
Virtuoso has built-in phrase match processor that is fast enough to deal with long documents and big
phrase sets in interactive applications.</p>
<a name="sqlreffastphrasematchscl" />
<h3>8.33.1. Phrases, Phrase Sets and Phrase Classes</h3>
<p>An annotation phrase is a pair of a key (a string that is supposed to be a word or phrase of some
natural language) and a value (of any type if its serialization is shorter than 2 kilobytes or a
string shorter than 10 megabytes). The key phrase will be divided into words and normalized by
language-specific functions used by free text search; after normalization it should contain from one
to four words. The associated value may be very long in principle, but it will add noticeable
overheads related to memory allocation and copying so it is recommended to keep it short if
performance is somehow important. It is usual to make the value as short as an ID in some external
"detailed" table, not only for speed but to support multiple synonyms for one thing or names of a
thing in different languages.</p>
<p>Annotation phrases are grouped in "phrase sets" and every "phrase set" belongs to
some "phrase class".</p>
<p>Phrase classes are enumerated in a DB.DBA.SYS_ANN_PHRASE_CLASS table.</p>
<div>
<pre class="programlisting">
create table DB.DBA.SYS_ANN_PHRASE_CLASS
(
APC_ID integer not null primary key,
APC_NAME varchar(255) unique, -- unique name for use in API/UI
APC_OWNER_UID integer, -- references SYS_USERS (U_ID), NULL if the record writable for any reader
APC_READER_GID integer, -- references SYS_USERS (U_ID), NULL if the record is readable for public
APC_CALLBACK varchar,
APC_APP_ENV any
)
;
</pre>
</div>
<p>Phrase sets and phrase classes may contain confidential information. E.g., a phrase set may be used
to check that a given outgoing document does not mention titles of confidential projects or names of
persons that should act anonymously for public. So any application may specify access restrictions
when a phrase class is created. The application usually sets APC_CALLBACK to name of some of its
functions (but it may be any string) and APC_APP_ENV to value of any type (application may pass it
to the APC_CALLBACK function as one of arguments). Phrase match processor only keeps these data in
memory, unchanged, and returns to the application as a part of text processing report, so they can
be used for any purpose.
</p>
<p>Phrase class describes an access to some application-specific callback, but it does not mention any
phrases at all. Individual phrases are grouped into phrase sets. Each phrase set belongs to exactly
one phrase class and is restricted to one language handler. It does not necessarily mean that it should
consist of phrases of one natural language, because many language handlers support mix of languages,
but it may apply some restrictions.</p>
<p>Phrase sets are enumerated in a DB.DBA.SYS_ANN_PHRASE_SET table.</p>
<div>
<pre class="programlisting">
create table DB.DBA.SYS_ANN_PHRASE_SET
(
APS_ID integer not null primary key,
APS_NAME varchar(255) unique, -- unique name for use in API/UI
APS_OWNER_UID integer, -- references SYS_USERS (U_ID), NULL if the record writable for any reader
APS_READER_GID integer, -- references SYS_USERS (U_ID), NULL if the record is readable for public
APS_APC_ID integer not null, -- references SYS_ANN_PHRASE_CLASS (APC_ID)
APS_LANG_NAME varchar not null, -- name of language handler that is used to split texts of phrases
APS_APP_ENV any,
APS_SIZE any, -- approximate number of phrases inserted in the set (actual or estimate for future)
APS_LOAD_AT_BOOT integer not null -- flags whether phrases should be loaded at boot time.
)
;
</pre>
</div>
<p>APS_APP_ENV can be of any type (application may pass it to the APC_CALLBACK function as one
of arguments). Like APC_APP_ENV, phrase match processor only keeps it in memory and returns in a text
processing report.</p>
<p>The APS_SIZE tweaks the amount of memory consumed by a phrase set when it's loaded. The average "price"
of placing a phrase to a set is 2 bytes per phrase. Memory amounts are less than a kilobyte while APS_SIZE
is below 256, less than 64 kilobytes while APS_SIZE grow up to 32 thousands of phrases and reaches its
maximum of 64 megabytes when APS_SIZE reaches its practical limit of 32 million phrases. Only rough
similarity to the actual number of inserts is needed, say, same order of magnitude. The exaggerated value
of APS_SIZE will allocate a bit more memory and may slightly improve the speed. It is not practical to
set APS_SIZE much smaller than it should be because it will cause frequent table lookups and disk buffers
in use will overweight any memory savings in the phrase match processor. If there are numerous phrase sets
that are used only occasionally, do not decrease APS_SIZE, instead set APS_LOAD_AT_BOOT to zero for them.</p>
<p>Note that if phrase set is edited intensively during server run then the number of inserted phrases is
important, an effect of phrase removal is visible only after server restart. E.g. if a phrase set is
frequently cleaned and refilled with new phrases then it is much better to delete an obsolete set and
create a new one.</p>
<p>Individual annotation phrases are stored in a table DB.DBA.SYS_ANN_PHRASE, that should not be modified
by applications directly by data manipulation statements. The content of the table is used to build
special search structures in memory and SQL operations like INSERT and DELETE can not keep in-memory
structures in sync with the content of the table. DBA may read the table but should not update; in
case of occasional update all phrase sets that contain edited phrases may become unusable until server
restart.</p>
<div>
<pre class="programlisting">
create table DB.DBA.SYS_ANN_PHRASE
(
AP_APS_ID integer not null, -- references SYS_ANN_PHRASE_SET (APS_ID),
AP_CHKSUM integer, -- phrase check-sum
AP_TEXT varchar, -- original text
AP_LINK_DATA any, -- Associated data about links etc.
AP_LINK_DATA_LONG long varchar, -- Same as AP_LINK_DATA but for long content, one of two is always NULL
primary key (AP_APS_ID, AP_CHKSUM, AP_TEXT)
)
;
</pre>
</div>
<p>The "almost direct" way of writing to the table is via BIF
<a href="fn_ann_phrase_class_add.html">ap_add_phrases</a>.
It gets two arguments, the
integer ID of annotation phrase set and a vector of descriptions of phrases that should be edited in that
phrase set. Every item of vector of descriptions is in turn vector of one or two values; first value is
the text of the phrase, second value is associated application specific data, the absence of second value
indicates that the phrase should be removed. If same text of phrase appears in the vector of description
more than once, and associated data differ then any version of data can be stored for future use; it is
the roll of dice because the vector is reordered for faster processing.</p>
<br />
<a name="sqlreffastphrasematchapi" />
<h3>8.33.2. Phrase Set Configuration API</h3>
<ul>
<li>
<a href="fn_ann_phrase_class_add.html">DB.DBA.ANN_PHRASE_CLASS_ADD</a>
</li>
<li>
<a href="fn_ann_phrase_class_del.html">DB.DBA.ANN_PHRASE_CLASS_DEL</a>
</li>
<li>
<a href="fn_ap_build_match_list.html">AP_BUILD_MATCH_LIST</a>:
The report R is a vector of 6 elements:
<ol>
<li>R[0] vector of all distinct phrase classes for phrase sets of found phrases; every pair of
items represents one phrase class: first item is an integer APC_ID of a class, second item is a description
of phrase class as vector of APC_NAME, APC_CALLBACK and APC_APP_ENV;</li>
<li>R[1] vector of all distinct phrase sets of found phrases; every pair of items represents
one phrase set: first item is an integer APS_ID of a class, second item is a vector of APS_NAME, APS_APC_ID,
index of phrase class description in R[0] and APS_APP_ENV;</li>
<li>R[2] vector of all distinct found phrases; every item represents a phrase as a vector of
AP_APS_ID, index of phrase set description in R[1], AP_TEXT and application-specific data from
AP_LINK_DATA or AP_LINK_DATA_LONG;</li>
<li>R[3] vector of all composed arrows for the text; every item represents one place in a text, as an "arrow" described below;</li>
<li>R[4] vector of indexes of arrows that point to words in the text; every item is an integer that is index in R[3];</li>
<li>R[5] vector of descriptions of occurrences of annotation phrases in text; every item
represents one occurrence as vector of index of first word in R[3], index of last word in R[3], index of
found phrase in R[2], index of previous occurrence of same phrase in R[5].</li>
</ol>
</li>
</ul>
<br />
<a name="sqlreffastphrasematchsadv" />
<h3>8.33.3. Advertisers and Advertisement Rules</h3>
<p>Phrase sets are sufficient for many purposes but advertisement-specific applications need more built-in
functionality for maximum speed. This functionality can be reused by any application that adds hyperlinks
to texts depending on content of the text.</p>
<p>Consider advertisers that want to show links to their resources near phrases they choose as relevant, and
one phrase can be chosen by many advertisers. To make the processing easier, there exists special support
for phrase sets that store lists of links for phrases.</p>
<p>An advertiser is some very abstract "source" of annotation phrases.
The exact nature of an advertiser is application-specific, but each advertiser has an identifiable account.
All accounts are in one table:</p>
<div>
<pre class="programlisting">
create table DB.DBA.SYS_ANN_AD_ACCOUNT (
AAA_ID integer not null primary key,
AAA_NAME varchar(255) unique, -- unique name for use in API/UI
AAA_OWNER_UID integer, -- references SYS_USERS (U_ID), NULL if the record writable for any reader
AAA_READER_GID integer, -- references SYS_USERS (U_ID), NULL if the record is readable for public
AAA_DETAILS long xml, -- any details, e.g., in RDF
AAA_APP_ENV any
)
;
</pre>
</div>
<p>Advertisement links are stored in a separate table:</p>
<div>
<pre class="programlisting">
create table DB.DBA.SYS_ANN_LINK (
AL_ID integer primary key,
AL_OWNER_UID integer, -- references SYS_USERS (U_ID), NULL if the record writable for any reader; always readable for public
AL_URI varchar, -- URI template for A HREF
AL_TEXT varchar, -- text template for body of <A>
AL_NOTE varchar, -- text after the link (or around it)
AL_TAGS any, -- tags to add or remove
AL_CALLBACK varchar,
AL_APP_ENV any
)
;
</pre>
</div>
<p>Advertisers, links and phrases are linked together by advertisement rules.
Each rule says that if a given phrase is found in some text then a given link should be shown and a
specified advertiser's account should be charged.
</p>
<div>
<pre class="programlisting">
create table DB.DBA.SYS_ANN_AD_RULE (
AAR_AAA_ID integer not null, -- advertiser who pays for the ad
AAR_APS_ID integer not null, -- phrase set
AAR_AP_CHKSUM integer not null, -- phrase check-sum
AAR_TEXT varchar not null, -- original text
AAR_AL_ID integer not null, -- references SYS_ANN_LINK (AL_ID)
AAR_APP_ENV any,
primary key (AAR_AAA_ID, AAR_APS_ID, AAR_AP_CHKSUM, AAR_TEXT, AAR_AL_ID)
)
;
</pre>
</div>
<p>When an annotation phrase is stored in its phrase set via advertisement API, its application-specific data
is always a vector, and vector's length is divisible by three. When a new ad rule is added to the vector, it
gets three new items: value of AL_ID of a link, value of advertiser's AAA_ID and AAR_APP_ENV of the ad rule.
</p>
<p>Anyone can describe a link but can not edit other's descriptions:</p>
<div>
<pre class="programlisting">
create function DB.DBA.ANN_LINK_ADD (
in _owner_uid integer,
in _uri varchar, -- value for AL_URI
in _text varchar, -- value for AL_TEXT
in _note varchar, -- value for AL_NOTE
in _tags any, -- value for AL_TAGS
in _callback varchar,
in _app_env any) returns integer
</pre>
</div>
<p>So one user may create links in favor of other user even without permission, but can not edit other's links.
To edit or delete, one should be an owner and pass authentication:</p>
<div>
<pre class="programlisting">
create function DB.DBA.ANN_LINK_MODIFY (
in _id integer,
in _owner_uid integer,
in _uri varchar,
in _text varchar,
in _note varchar,
in _tags any,
in _callback varchar,
in _app_env any,
in auth_uname varchar,
in auth_pwd varchar) returns integer
create function DB.DBA.ANN_LINK_DEL (
in _id integer,
in auth_uname varchar,
in auth_pwd varchar) returns integer
</pre>
</div>
<p>A link can not be dropped even by its owner if it is used by some advertiser.
</p>
<div>
<pre class="programlisting">
create function DB.DBA.ANN_AD_RULE_ADD (
in aaa_name varchar, -- Advertiser's name (or its integer AAA_ID key), to store as AAR_AAA_ID
in aps_name varchar, -- Name of a phrase set (or its integer APS_ID key), to store as AAR_APS_ID
in _text varchar, -- Text of the phrase, will be stored as AAR_TEXT and duplicated in an AP_TEXT if the phrase is new
in _al_id integer, -- ID of a link (AL_ID in DB.DBA.SYS_ANN_LINK), to store as AAR_AL_ID
in _app_env any, -- Application-specific data, will be stored in AAR_APP_ENV and become a part of AP_LINK_DATA of the phrase
in _lang_name varchar, -- Language name, for verification only; an error will be signalled if the value of argument is not equal to language name of the phrase set
in auth_uname varchar,
in auth_pwd varchar) returns integer
</pre>
</div>
<p>Application-specific data should be short, because for any given phrase, sum of data from all advertisers
should not exceed 2 kilobytes. Fortunately, most of needed data are in DB.DBA.SYS_ANN_LINK row already so
AAR_APP_ENV is frequently a NULL.</p>
<div>
<pre class="programlisting">
create function DB.DBA.ANN_AD_RULE_DEL (
in aaa_name varchar,
in aps_name varchar,
in _text varchar,
in _al_id integer,
in _lang_name varchar,
in auth_uname varchar,
in auth_pwd varchar) returns integer
</pre>
</div>
<p>If a phrase set is edited by DB.DBA.ANN_AD_RULE_ADD / DB.DBA.ANN_AD_RULE_DEL and by ap_add_phrases() then
errors are almost guaranteed (and not detected or recovered automatically in current version). The function
ap_build_match_list() works fine with mix of phrase sets managed by both methods because it is not sensitive
to the format of data in phrases.</p>
<br />
<a name="sqlreffastphraseexample" />
<h3>8.33.4. Example</h3>
<p>The following example demonstrates managing phrases sets and data associated with them:</p>
<div>
<pre class="programlisting">
SQL>insert replacing DB.DBA.SYS_ANN_PHRASE_CLASS (APC_ID, APC_NAME, APC_OWNER_UID, APC_READER_GID, APC_CALLBACK, APC_APP_ENV)
values (3, 'Debug apc #3', http_dav_uid(), http_admin_gid(), 'DB.DBA.AP_DEBUG_CALLBACK', 'Debug apc #3 env')
;
Done. -- 0 msec.
SQL>insert replacing DB.DBA.SYS_ANN_PHRASE_SET (APS_ID, APS_NAME, APS_OWNER_UID, APS_READER_GID, APS_APC_ID, APS_LANG_NAME, APS_APP_ENV, APS_SIZE, APS_LOAD_AT_BOOT)
values (5, 'Debug aps #5', http_dav_uid(), http_admin_gid(), 3, 'x-any', 'Debug aps #5 env', 10000, 0)
;
Done. -- 0 msec.
SQL>insert replacing DB.DBA.SYS_ANN_PHRASE_SET (APS_ID, APS_NAME, APS_OWNER_UID, APS_READER_GID, APS_APC_ID, APS_LANG_NAME, APS_APP_ENV, APS_SIZE, APS_LOAD_AT_BOOT)
values (7, 'Debug aps #7', http_dav_uid(), http_admin_gid(), 3, 'x-any', 'Debug aps #7 env', 10000, 0)
;
Done. -- 0 msec.
SQL>ap_add_phrases (5,
vector (
vector ('Debug5', 'Debug5 env'),
vector ('Debug5 one', 'Debug5 one env'),
vector ('Debug5 two', 'Debug5 two env'),
vector ('Debug5 three', 'Debug5 three env'),
vector ('Debug5 twenty one', 'Debug5 twenty one env'),
vector ('Debug5 twenty two', 'Debug5 twenty two env'),
vector ('Debug5 twenty three', 'Debug5 twenty three env')
) )
;
Done. -- 0 msec.
SQL>ap_add_phrases (7,
vector (
vector ('Debug7', 'Debug7 env'),
vector ('Debug7 one', 'Debug7 one env'),
vector ('Debug7 two', 'Debug7 two env'),
vector ('Debug7 three', 'Debug7 three env'),
vector ('Debug7 twenty one', 'Debug7 twenty one env'),
vector ('Debug7 twenty two', 'Debug7 twenty two env'),
vector ('Debug7 twenty three', 'Debug7 twenty three env')
) )
;
Done. -- 0 msec.
SQL>ap_add_phrases (5,
vector (
vector ('Debug5 thirty one', 'Debug5 thirty one BAD env'),
vector ('Debug5 thirty two', 'Debug5 thirty two BAD env'),
vector ('Debug5 thirty three', 'Debug5 thirty three BAD env'),
vector ('Debug5 one hundred', 'Debug5 one hundred BAD env')
) )
;
Done. -- 0 msec.
SQL>ap_add_phrases (5,
vector (
vector ('Debug5', 'Debug5 UPDATED env'),
vector ('Debug5 thirty one', 'Debug5 thirty one NEW env'),
vector ('Debug5 thirty two', 'Debug5 thirty two NEW env'),
vector ('Debug5 thirty three', 'Debug5 thirty three NEW env'),
vector ('Debug5 one hundred')
) )
;
Done. -- 0 msec.
SQL>create function ptext_1 ()
{
return '
vector ''Debug5'', ''Debug5 env'',
vector ''Debug5 one'', ''Debug5 one env'',
vector ''Debug5 two'', ''Debug5 two env'',
vector ''Debug5 three'', ''Debug5 three env'',
vector ''Debug5 twenty one'', ''Debug5 twenty one env'',
vector ''Debug5 twenty two'', ''Debug5 twenty two env'',
vector ''Debug5 twenty three'', ''Debug5 twenty three env''
ap_add_phrases 7,
vector
vector ''Debug7'', ''Debug7 env'',
vector ''Debug7 one'', ''Debug7 one env'',
vector ''Debug7 two'', ''Debug7 two env'',
vector ''Debug7 three'', ''Debug7 three env'',
vector ''Debug7 twenty one'', ''Debug7 twenty one env'',
vector ''Debug7 twenty two'', ''Debug7 twenty two env'',
vector ''Debug7 twenty three'', ''Debug7 twenty three env''
ap_add_phrases 5,
vector
vector ''Debug5 thirty one'', ''Debug5 thirty one BAD env'',
vector ''Debug5 thirty two'', ''Debug5 thirty two BAD env'',
vector ''Debug5 thirty three'', ''Debug5 thirty three BAD env'',
vector ''Debug5 one hundred'', ''Debug5 one hundred BAD env''
ap_add_phrases 5,
vector
vector ''Debug5'', ''Debug5 UPDATED env'',
vector ''Debug5 thirty one'', ''Debug5 thirty one NEW env'',
vector ''Debug5 thirty two'', ''Debug5 thirty two NEW env'',
vector ''Debug5 thirty three'', ''Debug5 thirty three NEW env'',
vector ''Debug5 one hundred''
';
}
;
Done. -- 20 msec.
SQL>create function ptext_2 ()
{
return '
vector ''Debug5 twenty one''
vector ''Debug7 twenty one''
vector ''Debug5 thirty one''
vector ''Debug5 thirty one''
vector ''Debug5 one hundred''
';
}
;
Done. -- 10 msec.
SQL>create function test_ptext_1()
{
declare ses any;
ses := string_output();
ap_debug_langhandler (ptext_1 (), 'x-any', vector (5, 7), ses );
return string_output_string (ses);
}
;
Done. -- 10 msec.
SQL>create procedure dump_match_list (in vect any, in path varchar := null)
{
declare VDATA varchar;
if (path is null)
{
result_names (VDATA);
path := '';
}
if (vect is null)
{
result (path || ' NULL');
return;
}
if (193 <> __tag (vect))
{
result (path || ' ' || (cast (vect as varchar)));
return;
}
declare l, ctr integer;
l := length (vect);
if (0 = l)
{
result (path || ' (empty vector)');
return;
}
if (isinteger (vect [l - 1]))
{
declare ses any;
ses := string_output ();
for (ctr := 0; ctr < l; ctr := ctr + 1)
{
http (sprintf (' [%d]=%s', ctr, cast (vect[ctr] as varchar)), ses);
}
result (path || string_output_string (ses));
return;
}
for (ctr := 0; ctr < l; ctr := ctr + 1)
{
dump_match_list (vect[ctr], sprintf ('%s[%d]', path, ctr));
}
}
;
Done. -- 10 msec.
SQL>select test_ptext_1();
callret
VARCHAR
_______________________________________________________________________________
(vector) '[[Debug5 UPDATED env]Debug5]]', '[[Debug5 UPDATED env]Debug5]] (env)', (vector) '[[Debug5 UPDATED env ; Debug5 one env]Debug5]] [[Debug5 one env]one]]', '[[Debug5 UPDATED env ; Debug5 one env]Debug5]] [[Debug5 one env ]one]] (env)',
(vector) '[[Debug5 UPDATED env ; Debug5 two env]Debug5]] [[Debug5 two env]two]]', '[[Debug5 UPDATED env ; Debug5 two env]Debug5]] [[Debug5 two env ]two]] (env)', (vector) '[[Debug5 UPDATED env ; Debug5 three env]Debug5]] [[Debug5 three env]three]]', '[[Debug5 UPDATED env ; Debug5 three env]Debug5]] [[Debug5 three env]three]] (env)',
(vector) '[[Debug5 UPDATED env ; Debug5 twenty one env]Debug5]] [[Debug5 twenty one env]twenty]] [[Debug5 twenty one env]one]]', '[[Debug5 UPDATED env ; Debug5 twenty one env]Debug5]] [[Debug5 twenty one env]twenty]] [[Debug5 twenty one env]one]] (env)',
(vector) '[[Debug5 UPDATED env ; Debug5 twenty two env]Debug5]] [[Debug5 twenty two env]twenty]] [[Debug5 twenty two env]two]]', '[[Debug5 UPDATED env ; Debug5 twenty two env]Debug5]] [[Debug5 twenty two env]twenty]] [[Debug5 twenty two env]two]] (env)',
(vector) '[[Debug5 UPDATED env ; Debug5 twenty three env]Debug5]] [[Debug5 twenty three env]twenty]] [[Debug5 twenty three env]three]]', '[[Debug5 UPDATED env ; Debug5 twenty three env]Debug5]] [[Debug5 twenty three env]twenty]] [[Debug5 twenty three env]three]] (env)' (ap)_(add)_(phrases) (7), (vector)
(vector) '[[Debug7 env]Debug7]]', '[[Debug7 env]Debug7]] (env)', (vector) '[[Debug7 one env ; Debug7 env]Debug7]] [[Debug7 one env]one]]', '[[Debug7 one env ; Debug7 env]Debug7]] [[Debug7 one env]one]] (env)',
(vector) '[[Debug7 two env ; Debug7 env]Debug7]] [[Debug7 two env]two]]', '[[Debug7 two env ; Debug7 env]Debug7]] [[Debug7 two env]two]] (env)',
(vector) '[[Debug7 three env ; Debug7 env]Debug7]] [[Debug7 three env]three]]', '[[Debug7 three env ; Debug7 env]Debug7]] [[Debug7 three env]three ]] (env)',
(vector) '[[Debug7 twenty one env ; Debug7 env
1 Rows. -- 50 msec.
SQL>dump_match_list (ap_build_match_list (vector (5, 7), ptext_2 (), 'x-any', 0, 0 ) );
VDATA
VARCHAR
_______________________________________________________________________________
[0][0] 3
[0][1][0] Debug apc #3
[0][1][1] DB.DBA.AP_DEBUG_CALLBACK
[0][1][2] Debug apc #3 env
[1][0] 5
[1][1][0] Debug aps #5
[1][1][1] 3
[1][1][2] 1
[1][1][3] Debug aps #5 env
[1][2] 7
.....
139 Rows. -- 231 msec.
SQL>dump_match_list (ap_build_match_list (vector (5, 7), ptext_2 (), 'x-any', 0, 3 ) );
VDATA
VARCHAR
_______________________________________________________________________________
[0][0] 3
[0][1][0] Debug apc #3
[0][1][1] DB.DBA.AP_DEBUG_CALLBACK
[0][1][2] Debug apc #3 env
[1][0] 5
[1][1][0] Debug aps #5
[1][1][1] 3
[1][1][2] 1
.....
133 Rows. -- 231 msec.
SQL>create procedure ap_make_js_menus (
in ap_set_ids any, in source_UTF8 varchar, in lang_name varchar, in is_html integer)
{
declare res_out, script_out, match_list any;
declare m_apc, m_aps, m_app, m_apa, m_apa_w, m_aph any;
declare apa_w_ctr, apa_w_count integer;
declare app_ctr, app_count integer;
declare prev_end integer;
match_list := ap_build_match_list ( ap_set_ids, source_UTF8, lang_name, is_html, 3); -- 3 is to have less garbage, hence better speed.
m_apc := aref_set_0 (match_list, 0);
m_aps := aref_set_0 (match_list, 1);
m_app := aref_set_0 (match_list, 2);
m_apa := aref_set_0 (match_list, 3);
m_apa_w := aref_set_0 (match_list, 4);
m_aph := aref_set_0 (match_list, 5);
apa_w_count := length (m_apa_w);
app_count := length (m_app);
if (0 = app_count)
{
return source_UTF8;
}
res_out := string_output ();
script_out := string_output ();
http ('<script DEFER language="javescript"><!--', script_out);
http ('\n var v_descs = [', script_out);
for (apa_w_ctr := 0; apa_w_ctr < apa_w_count; apa_w_ctr := apa_w_ctr + 1)
{
declare apa_idx integer;
declare apa any;
apa_idx := m_apa_w [apa_w_ctr];
apa := aref_set_0 (m_apa, apa_idx);
if (5 = length (apa))
{
declare apa_beg, apa_end, apa_hpctr, apa_hpcount integer;
apa_beg := apa [1];
apa_end := apa [2];
apa_hpcount := length (apa[4]);
http (subseq (source_UTF8, prev_end, apa_beg), res_out);
http (sprintf ('<span id="apjsm%d">', apa_idx), res_out);
http (subseq (source_UTF8, apa_beg, apa_end), res_out);
http ('</span>', res_out);
prev_end := apa_end;
if (apa_w_ctr > 0)
http(',', script_out);
http (sprintf ('\n\t["apjsm%d"\t, [', apa_idx), script_out);
for (apa_hpctr := 0; apa_hpctr < apa_hpcount; apa_hpctr := apa_hpctr + 1)
{
if (apa_hpctr > 0)
http (',', script_out);
http (cast (apa[4][apa_hpctr] as varchar), script_out);
}
http (']\t, [', script_out);
-- todo: list service indexes. Do we need this in such a form?
http ('\t]', script_out);
}
}
http (subseq (source_UTF8, prev_end), res_out);
http (' ];', script_out);
http ('\n var v_descs = [', script_out);
-- todo list services. Do we need this in such a form?
http (' ];', script_out);
http ('\n var v_links = [', script_out);
for (app_ctr := 0; app_ctr < app_count; app_ctr := app_ctr + 1)
{
declare app any;
app := m_app [app_ctr];
if (app_ctr > 0)
http(',', script_out);
http (sprintf ('\n\t["%s"\t, "%s"\t, "%s"\t, "%s"\t]', app[3][0], app[3][1], app[3][2], app[3][3]), script_out);
}
http (' ];', script_out);
http ('\n--></script><script DEFER language="javascript" src="lookup.js"></script>', script_out);
return
replace (
replace (
string_output_string (res_out),
'<body', '<body onload="makePopupDivs (v_descs, v_services, v_links)"' ),
'</html>', string_output_string (script_out) || '</html>' );
}
;
Done. -- 0 msec.
SQL>create function ptext_3 ()
{
return '<html>
<head>
<link href="gogo.css" rel="stylesheet" type="text/css" />
</head>
<body>
<p>The OpenLink Virtuoso team has developers based in Bulgaria</p>
</body>
</html>';
}
;
Done. -- 10 msec.
SQL>create function ptext_3 ()
{
return '<html>
<head>
<link href="gogo.css" rel="stylesheet" type="text/css" />
</head>
<body>
<p>The OpenLink <strong>Virtuoso</strong> team has developers based in Bulgaria</p>
</body>
</html>';
}
;
Done. -- 0 msec.
SQL>insert replacing DB.DBA.SYS_ANN_PHRASE_SET (APS_ID, APS_NAME, APS_OWNER_UID, APS_READER_GID, APS_APC_ID, APS_LANG_NAME, APS_APP_ENV, APS_SIZE, APS_LOAD_AT_BOOT)
values (9, 'Gogo aps', http_dav_uid(), http_admin_gid(), 3, 'x-any', 'Gogo aps env', 10000, 0);
Done. -- 0 msec.
SQL>ap_add_phrases (9,
vector (
vector ('OpenLink' , vector ('OpenLink' , 'http://www.openlinksw.com' , 'OpenLink Software' , ' The industry leading provider of High Performance drivers' )),
vector ('OpenLink Virtuoso' , vector ('OpenLink Virtuoso' , 'http://www.openlinksw.com/virtuoso' , 'OpenLink Virtuoso' , ' OpenLink Universal Integration Middleware' )),
vector ('Bulgaria' , vector ('Bulgaria' , 'http://www.government.bg/English/' , 'Bulgaria' , ' Republic of Bulgaria' ))
) )
;
Done. -- 0 msec.
SQL>select ap_make_js_menus (vector (9), ptext_3(), 'x-any', 1);
callret
VARCHAR
_______________________________________________________________________________
<html>
<head>
<link href="gogo.css" rel="stylesheet" type="text/css" />
</head>
<body onload="makePopupDivs (v_descs, v_services, v_links)">
<p>The OpenLink <strong>Virtuoso</strong> team has developers based in Bulgaria</p>
</body>
<script DEFER language="javescript"><!--
var v_descs = [ ];
var v_descs = [ ];
var v_links = [
["Bulgaria" , "http://www.government.bg/English/" , "Bulgaria" , " Republic of Bulgaria" ],
["OpenLink Virtuoso" , "http://www.openlinksw.com/virtuoso" , "OpenLink Virtuoso" , " OpenLink Universal Integration Middleware" ],
["OpenLink" , "http://www.openlinksw.com" , "OpenLink Software" , " The industry leading provider of High Performance drivers" ] ];
--></script><script DEFER language="javascript" src="lookup.js"></script></html>
1 Rows. -- 0 msec.
</pre>
</div>
<br />
<table border="0" width="90%" id="navbarbottom">
<tr>
<td align="left" width="33%">
<a href="transitivityinsQL.html" title="Transitivity in SQL">Previous</a>
<br />Transitivity in SQL</td>
<td align="center" width="34%">
<a href="sqlreference.html">Chapter Contents</a>
</td>
<td align="right" width="33%">
<a href="sqlprocedures.html" title="SQL Procedure Language Guide">Next</a>
<br />Contents of SQL Procedure Language Guide</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
>
886
