<!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="sqloptimizer.html" title="Virtuoso SQL Optimization" />
<link rel="next" href="GRAMMAR.html" title="SQL Grammar" />
<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="sqlinverse" />
<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="sqloptimizer.html" title="Virtuoso SQL Optimization">Prev</a> | <a class="link" href="GRAMMAR.html" title="SQL Grammar">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 class="selected">
<a href="sqlinverse.html">SQL Inverse Functions</a>
<div>
<a href="#updthoughinverses" title="Updating through Inverses">Updating through Inverses</a>
</div>
</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>
<a href="sqlreffastphrasematch.html">Fast Phrase Match Processor</a>
</div>
<br />
</div>
<div id="text">
<a name="sqlinverse" />
<h2>8.29. SQL Inverse Functions</h2>
<p>Many virtual database application scenarios require performing various
conversions on data in legacy tables. An example of this is currency conversions,
conversions between British and metric units, replacing complex composite keys with single numeric row id's and so forth.</p>
<p>SQL views are the normal way of implementing such conversions. However,
when making queries with selection criteria referencing the results of these
conversions we often need to first convert and then test, possibly having to
bring data from the source system to the virtual database. Further, we
will not be able to use indices that may exist on the legacy system if
we first have to convert and only then test the value of a column.</p>
<p>Virtuoso introduces an SQL extension for dealing with these issues.
Virtuoso allows the definition of two SQL functions that are inverses of each other,
for example <span class="computeroutput">dollar_to_euro</span> and <span class="computeroutput">euro_to_dollar</span> would be examples of inverse functions.</p>
<p>Thus, when we define a view like:</p>
<div>
<pre class="programlisting">create view euro_item as Select I_id, dollar_to_euro (I_price) as I_price, I_name from item;</pre>
</div>
<p>We can further declare:</p>
<div>
<pre class="programlisting">db..sinv_create_inverse ('euro_to_dollar', 'dollar_to_euro', 1);</pre>
</div>
<p>and perform the query:</p>
<div>
<pre class="programlisting">select * from euro_item where I_price > 100;</pre>
</div>
<p>Normally, this would internally perform the query:</p>
<div>
<pre class="programlisting">select * from item where dollar_to_euro (I_price) > 100;</pre>
</div>
<p>Now however it will make use of the inverse function information and we get:</p>
<div>
<pre class="programlisting">select * from item where I_price > euro_to_dollar (100)</pre>
</div>
<p>The second form is substantially more efficient since only one function
call needs to be done for the whole query, not to mention the fact that if the
currency conversion function were only defined on the vdb and the item table
were linked from another server, all data would have to be brought over to
the vdb for the conversion.</p>
<p>Now if the conversion function were defined only on the vdb, the amount
of euros would be converted to dollars and then passed as a parameter to</p>
<div>
<pre class="programlisting">Select * from item where I_price > ?</pre>
</div>
<p>Many simple conversions can be efficiently handled by this method.
For most unit and data type conversions, the conversion preserves collation
order. Amount a will be greater than amount b whether a and b be expressed
in euros or dollars, as long as both a and b are in the same currency.
Some other conversions such as compression, encryption, hashes and mappings
of arbitrary unique ids to another set of ids do not preserve collation.
Whether a particular mapping preserves collation is indicated by the third
argument of <a href="fn_sinv_create_inverse.html">sinv_create_inverse</a>. In the above example we see a value of 1,
meaning that collation is preserved. Zero specifies that collation is not preserved.</p>
<p>Sometimes it will be desirable to map multi-part keys into a single key for
convenience of querying or for compatibility with another database schema.</p>
<p>We will use the orders and customer tables from the TPC C benchmark schema
to illustrate the feature. Both tables have a primary key consisting of three parts,
a warehouse id, a district id and then a customer or order id. The order references
the customer with a foreign key consisting of its own warehouse and district id
and of a customer id.</p>
<p>Now we may wish to simplify the situation and make a one-column order id and a
one column foreign key reference to customer for presenting the system to an outside
reporting application. We will do this completely without touching the original database,
all logic taking place on the virtual database.</p>
<p>In this case, the original keys consist of three numbers: n digits of warehouse
id, 2 digits of district id and 9 digits of order or customer id. We can simply concatenate
these numbers into a single decimal. To do this, we define the following functions:</p>
<div>
<pre class="programlisting">create function num_truncate (in n numeric)
{
return cast (cast (n as numeric) - 0.5 as numeric (40, 0));
}
create function num_mod (in x numeric, in y numeric)
{
return (x - num_truncate (cast (x as numeric) / y) * y);
}
/* This function takes the three key parts and returns a numeric where each occupies a fixed range of digits. */
create function o_iid (in w_id int, in d_id int, in o_id int) returns numeric
{
return (w_id * 100000000000 + d_id * 1000000000 + o_id);
}
/* The below three functions retrieve each of the fields encoded in the numeric produced by o_iid */
create function o_iid_w_id (in iid numeric) returns int
{
return cast (num_truncate (iid / 100000000000) as int);
}
create function o_iid_d_id (in iid numeric) returns int
{
return cast (num_mod (num_truncate (iid / 1000000000), 100) as int);
}
create function o_iid_o_id (in iid numeric)
{
return cast (num_mod (iid, 1000000000) as int);
}
db..sinv_create_inverse ('O_IID', VECTOR ('O_IID_W_ID', 'O_IID_D_ID', 'O_IID_O_ID'), 0);</pre>
</div>
<p>The <a href="fn_sinv_create_inverse.html">sinv_create_inverse</a> now defines an accessory for each of the arguments
of o_iid. The first function in the vector retrieves the first argument of a call
to o_iid, the second the second and so forth. Since we are talking about mapping
a set of values into one value it does not make sense to speak of preserving a collation,
thus the last argument is 0. When making this declaration we also assert that for each
distinct combination of arguments of o_iid, we get a distinct value from which all the
original arguments can be retrieved using the functions mentioned.</p>
<p>Now we define the same functions for constructing a synthetic one-part customer id.
The functions are exactly the same as for the order id.</p>
<div>
<pre class="programlisting">create function c_iid (in w_id int, in d_id int, in c_id int) returns numeric
{
return (w_id * 100000000000 + d_id * 1000000000 + c_id);
}
create function c_iid_w_id (in iid numeric) returns int
{
return cast (num_truncate (iid / 100000000000) as int);
}
create function c_iid_d_id (in iid numeric) returns int
{
return cast (num_mod (num_truncate (iid / 1000000000), 100) as int);
}
create function c_iid_c_id (in iid numeric)
{
return cast (num_mod (iid, 1000000000) as int);
}
db..sinv_create_inverse ('C_IID', VECTOR ('C_IID_W_ID', 'C_IID_D_ID', 'C_IID_C_ID'), 0);</pre>
</div>
<p>Now we have defined functions for producing the synthetic keys we wish to use.
Now we bind these functions to the original database using views. We assume that the
original orders and customer tables are attached from a remote database under the
names orders and customer.</p>
<div>
<pre class="programlisting">create view orders_2 as select o_iid (o_w_id, o_d_id, o_id) as o_iid numeric, c_iid (o_w_id, o_d_id, o_c_id) as o_c_iid numeric, * from orders;
create view customer_2 as select c_iid (c_w_id, c_d_id, c_id) as c_iid numeric, * from customer;</pre>
</div>
<p>As a simple test, let us get the synthetic o_c_iid from an order using the synthetic o_iid key.</p>
<div>
<pre class="screen">
SQL> select o_c_iid from orders_2 where o_iid = 102000000002;
returns________
102000000935
</pre>
</div>
<p>The order #2 in district #2 of warehouse #1 refers to customer #935 or district #2 of warehouse #1.</p>
<div>
<pre class="screen">
SQL> explain ('select o_c_iid from orders_2 where o_iid = 102000000002');
returns
{
Precode:
0: $25 "callret" := Call O_IID_O_ID (<constant (102000000002)>)
7: $26 "callret" := Call O_IID_D_ID (<constant (102000000002)>)
14: $27 "callret" := Call O_IID_W_ID (<constant (102000000002)>)
21: BReturn 0
Remote SELECT "t2"."O_C_ID", "t2"."O_D_ID", "t2"."O_W_ID" FROM "DBA"."ORDERS" "t2" where "t2"."O_ID" = ? and "t2"."O_D_ID" = ? and "t2"."O_W_ID" = ?
Params ($25 "callret", $26 "callret", $27 "callret")
Output ($29 "t1.O_C_ID", $30 "t1.O_D_ID", $31 "t1.O_W_ID")
After code:
0: $40 "O_C_IID" := Call C_IID ($31 "t1.O_W_ID", $30 "t1.O_D_ID", $29 "t1.O_C_ID")
7: BReturn 0
Select ($40 "O_C_IID")
}
</pre>
</div>
<p>In this query execution plan we see that the virtual database first decomposes
the synthetic o_iid into its component parts, passes these to a query run on the
remote database, retrieves the components of the synthetic o_c_iid and finally
assembles this and returns it to the client.</p>
<p>We may also use this mapping in more complex queries:</p>
<div>
<pre class="programlisting">select count (*) from orders_2, customer_2 where o_c_iid = c_iid;</pre>
</div>
<p>This uses the synthetic foreign and primary keys for joining. Normally,
since the functions for making these keys are only defined on the vdb side,
each order would have to be retrieved and then the corresponding customer fetched.
Now however we can take advantage of the inverse declaration and decompose the
comparison of the synthetic keys into comparisons of each of their arguments:</p>
<div>
<pre class="screen">
SQL> explain ('select count (*) from orders_2, customer_2 where o_c_iid = c_iid');
returns
{
Remote SELECT COUNT ( *) FROM "DBA"."ORDERS" "t2" , "DBA"."CUSTOMER" "t4" where "t4"."C_ID" = "t2"."O_C_ID" and "t4"."C_D_ID" = "t2"."O_D_ID" and "t4"."C_W_ID" = "t2"."O_W_ID"
Output ($26 "aggregate")
Select ($26 "aggregate")
}
</pre>
</div>
<p>We see that the SQL compiler decomposed the comparison of the c_iid's into
a comparison of the original arguments of the function. This can be done because
we earlier asserted that each distinct argument combination would produce a
unique result of the function.</p>
<p>The examples covered thus far are simple in that there is a clear way of mapping
the data back and forth using SQL functions. This is not always possible, for example
if we wish to generate unique numeric id's from keys consisting of arbitrary strings.</p>
<p>To this effect, Virtuoso provides a mechanism for automatically generating mapping
functions, which take a combination of n arguments of an arbitrary searchable data type
and generate a unique id for each distinct combination. The ids are assigned as needed
and the mapping is persisted in an automatically generated table.</p>
<p>We could have generated the o_iid, o_iid_w_id, o_iid_d_id and o_iid_o_id functions with the following single call:</p>
<div>
<pre class="programlisting">db..sinv_create_key_mapping ('O_IID', vector ('W_ID', 'int', 'D_ID', 'int', 'O_ID', 'int'));</pre>
</div>
<p>The first argument is the name of the mapping function to generate. The following
vector lists the arguments of this function and their data types. The name of each argument
is appended to the name of the mapping function, separated by an underscore for forming the
inverse functions, one for each argument. Thus the functions are named exactly as in the
previous example where we defined them manually.</p>
<p>Internally, this generates a table for keeping the w_id, d_id and o_id for each
allocated o_iid. The o_iid's come from a sequence object. The functions manage this
table automatically, without requiring developer intervention. The name of the
sequence is the same as that of the mapping, thus sequence_set can be used for
setting the sequence counter. Note that sequence names are case sensitive.</p>
<p>When a new combination of w_id, d_id, o_id is seen by o_iid, it inserts a
row in the mapping table and assigns this a new unique number. This update of
the mapping table is committed at the same time the enclosing transaction commits.
The table is normally read with read committed isolation and updated with serializable
isolation. This guarantees that when a single w_id, d_id, o_id combination is seen at
the same time on two threads the result will be the same id being given on both threads.</p>
<p>Any value returned by the mapping function may at all times be used for the inverse
functions. The inverse functions will return NULL if given a value that was at no time
returned by the mapping function.</p>
<p>The table created for the mapping is named MAP_<name of the mapping function>.
The system does not define an automatic cleanup function for removing established
mappings because the efficient way of implementing this is quite application dependent.
Such a function can however be easily written by the developer if one is needed.</p>
<p>Because comparison of mapping functions is reduced into pair-wise comparison of their arguments, the query</p>
<div>
<pre class="programlisting">select count (*) from orders, customer where o_c_iid = c_iid</pre>
</div>
<p>Will work even if all orders or customers have not been assigned a mapping on the vdb
side. If a virtual database application wishes to retrieve rows from a remote database using
a synthetic key made by functions defined with <a href="fn_sinv_create_key_mapping.html">sinv_create_key_mapping</a>, any value returned by
the mapping function in a transaction that was successfully committed is valid and will
retrieve the data intended.</p>
<a name="updthoughinverses" />
<h3>8.29.1. Updating through Inverses</h3>
<p>A view that selects calls to functions, which have inverses, is updateable.
This means that the SQL compiler will use the inverse function on the value being
assigned before assigning the column. This is done for both insert and update
operations. No special declaration is needed. If the function with inverse
has multiple arguments, all of which are columns, then assigning the function
will assign all the argument columns, having called the appropriate inverse for
getting each of the separate argument values.</p>
<p>For example:</p>
<div>
<pre class="screen">
SQL> insert into orders_2 (o_iid) values (1102000000003);
Done. -- 1 msec.
SQL> select * from orders_2 where o_w_id = 11;
O_IID O_C_IID O_ID O_D_ID O_W_ID O_C_ID O_ENTRY_D O_CARRIER_ID O_OL_CNT O_ALL_LOCAL
DECIMAL DECIMAL INTEGER NOT NULL INTEGER NOT NULL INTEGER NOT NULL INTEGER DATE INTEGER INTEGER INTEGER
_______________________________________________________________________________
1102000000003 NULL 3 2 11 NULL NULL NULL NULL NULL
1 Rows. -- 1 msec.
SQL> update item_euro set i_price = 120 where i_id = 1234;
</pre>
</div>
<br />
<div class="note">
<div class="notetitle">Conclusions</div>
<p>The examples in this document used tables attached from remote databases
because the features discussed here are most likely to be useful in such contexts.
Also the query execution plans clearly show which operations take place where. The
inverse function mechanism is however in no way limited to virtual database applications.
All the examples will work equally well with local tables.</p>
</div>
<table border="0" width="90%" id="navbarbottom">
<tr>
<td align="left" width="33%">
<a href="sqloptimizer.html" title="Virtuoso SQL Optimization">Previous</a>
<br />Virtuoso SQL Optimization</td>
<td align="center" width="34%">
<a href="sqlreference.html">Chapter Contents</a>
</td>
<td align="right" width="33%">
<a href="GRAMMAR.html" title="SQL Grammar">Next</a>
<br />SQL Grammar</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
>
879
