283 lines
9.8 KiB
HTML
283 lines
9.8 KiB
HTML
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
|
||
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
|
||
<html xmlns="http://www.w3.org/1999/xhtml">
|
||
<head>
|
||
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
|
||
<title>Database Joins</title>
|
||
<link rel="stylesheet" href="gettingStarted.css" type="text/css" />
|
||
<meta name="generator" content="DocBook XSL Stylesheets V1.62.4" />
|
||
<link rel="home" href="index.html" title="Getting Started with Berkeley DB" />
|
||
<link rel="up" href="indexes.html" title="Chapter 5. Secondary Databases" />
|
||
<link rel="previous" href="secondaryCursor.html" title=" Using Cursors with Secondary Databases " />
|
||
<link rel="next" href="coreindexusage.html" title="Secondary Database Example" />
|
||
</head>
|
||
<body>
|
||
<div class="navheader">
|
||
<table width="100%" summary="Navigation header">
|
||
<tr>
|
||
<th colspan="3" align="center">Database Joins</th>
|
||
</tr>
|
||
<tr>
|
||
<td width="20%" align="left"><a accesskey="p" href="secondaryCursor.html">Prev</a> </td>
|
||
<th width="60%" align="center">Chapter 5. Secondary Databases</th>
|
||
<td width="20%" align="right"> <a accesskey="n" href="coreindexusage.html">Next</a></td>
|
||
</tr>
|
||
</table>
|
||
<hr />
|
||
</div>
|
||
<div class="sect1" lang="en" xml:lang="en">
|
||
<div class="titlepage">
|
||
<div>
|
||
<div>
|
||
<h2 class="title" style="clear: both"><a id="joins"></a>Database Joins</h2>
|
||
</div>
|
||
</div>
|
||
<div></div>
|
||
</div>
|
||
<p>
|
||
If you have two or more secondary databases associated with a primary
|
||
database, then you can retrieve primary records based on the intersection of
|
||
multiple secondary entries. You do this using a
|
||
|
||
<span>join cursor.</span>
|
||
</p>
|
||
<p>
|
||
Throughout this document we have presented a
|
||
|
||
<span>structure</span>
|
||
that stores
|
||
|
||
information on grocery
|
||
|
||
<span>vendors.</span>
|
||
That
|
||
|
||
<span>structure</span>
|
||
is fairly simple with a limited
|
||
number of data members, few of which would be interesting from a query
|
||
perspective. But suppose, instead, that we were storing
|
||
information on something with many more characteristics that can be queried, such
|
||
as an automobile. In that case, you may be storing information such as
|
||
color, number of doors, fuel mileage, automobile type, number of
|
||
passengers, make, model, and year, to name just a few.
|
||
</p>
|
||
<p>
|
||
In this case, you would still likely be using some unique value to key your
|
||
primary entries (in the United States, the automobile's VIN would be
|
||
ideal for this purpose). You would then create a
|
||
|
||
<span>structure</span>
|
||
that identifies
|
||
all the characteristics of the automobiles in your inventory.
|
||
|
||
|
||
</p>
|
||
<p>
|
||
To query this data, you might then create multiple secondary databases,
|
||
one for each of the characteristics that you want to query. For
|
||
example, you might create a secondary for color, another for number of
|
||
doors, another for number of passengers, and so forth. Of course, you
|
||
will need a unique
|
||
|
||
<span>key extractor function</span>
|
||
for each such secondary database. You do
|
||
all of this using the concepts and techniques described throughout this
|
||
chapter.
|
||
</p>
|
||
<p>
|
||
Once you have created this primary database and all interesting
|
||
secondaries, what you have is the ability to retrieve automobile records
|
||
based on a single characteristic. You can, for example, find all the
|
||
automobiles that are red. Or you can find all the automobiles that have
|
||
four doors. Or all the automobiles that are minivans.
|
||
</p>
|
||
<p>
|
||
The next most natural step, then, is to form compound queries, or joins.
|
||
For example, you might want to find all the automobiles that are red,
|
||
and that were built by Toyota, and that are minivans. You can do this
|
||
using a
|
||
|
||
<span>join cursor.</span>
|
||
</p>
|
||
<div class="sect2" lang="en" xml:lang="en">
|
||
<div class="titlepage">
|
||
<div>
|
||
<div>
|
||
<h3 class="title"><a id="joinUsage"></a>Using Join Cursors</h3>
|
||
</div>
|
||
</div>
|
||
<div></div>
|
||
</div>
|
||
<p>
|
||
To use a join cursor:
|
||
</p>
|
||
<div class="itemizedlist">
|
||
<ul type="disc">
|
||
<li>
|
||
<p>
|
||
Open two or more
|
||
|
||
cursors
|
||
|
||
<span>for</span>
|
||
secondary databases that are associated with
|
||
the same primary database.
|
||
</p>
|
||
</li>
|
||
<li>
|
||
<p>
|
||
Position each such cursor to the secondary key
|
||
value in which you are interested. For example, to build on
|
||
the previous description, the cursor for the color
|
||
database is positioned to the <tt class="literal">red</tt> records
|
||
while the cursor for the model database is positioned to the
|
||
<tt class="literal">minivan</tt> records, and the cursor for the
|
||
make database is positioned to <tt class="literal">Toyota</tt>.
|
||
</p>
|
||
</li>
|
||
<li>
|
||
<p>
|
||
|
||
Create an array of cursors, and
|
||
place in it each of the cursors that are participating in your join query.
|
||
<span>Note that this array must be null terminated.</span>
|
||
|
||
</p>
|
||
</li>
|
||
<li>
|
||
<p>
|
||
|
||
Obtain a join cursor. You do this using the
|
||
|
||
|
||
<tt class="methodname">Db::join()</tt>
|
||
method. You must pass this method the array of secondary cursors that you
|
||
opened and positioned in the previous steps.
|
||
</p>
|
||
</li>
|
||
<li>
|
||
<p>
|
||
Iterate over the set of matching records
|
||
|
||
until
|
||
|
||
<span>the return code is not <tt class="literal">0</tt>.</span>
|
||
</p>
|
||
</li>
|
||
<li>
|
||
<p>
|
||
Close your cursor.
|
||
</p>
|
||
</li>
|
||
<li>
|
||
<p>
|
||
If you are done with them, close all your cursors.
|
||
</p>
|
||
</li>
|
||
</ul>
|
||
</div>
|
||
<p>
|
||
For example:
|
||
</p>
|
||
<a id="cxx_index9"></a>
|
||
<pre class="programlisting">#include <db_cxx.h>
|
||
#include <string.h>
|
||
|
||
...
|
||
|
||
// Exception handling omitted
|
||
|
||
int ret;
|
||
|
||
Db automotiveDB(NULL, 0);
|
||
Db automotiveColorDB(NULL, 0);
|
||
Db automotiveMakeDB(NULL, 0);
|
||
Db automotiveTypeDB(NULL, 0);
|
||
|
||
// Database and secondary database opens omitted for brevity.
|
||
// Assume a primary database:
|
||
// automotiveDB
|
||
// Assume 3 secondary databases:
|
||
// automotiveColorDB -- secondary database based on automobile color
|
||
// automotiveMakeDB -- secondary database based on the manufacturer
|
||
// automotiveTypeDB -- secondary database based on automobile type
|
||
|
||
// Position the cursors
|
||
Dbc *color_curs;
|
||
automotiveColorDB.cursor(NULL, &color_curs, 0);
|
||
char *the_color = "red";
|
||
Dbt key(the_color, strlen(the_color) + 1);
|
||
Dbt data;
|
||
if ((ret = color_curs->get(&key, &data, DB_SET)) != 0) {
|
||
// Error handling goes here
|
||
}
|
||
|
||
Dbc *make_curs;
|
||
automotiveMakeDB.cursor(NULL, &make_curs, 0);
|
||
char *the_make = "Toyota";
|
||
key.set_data(the_make);
|
||
key.set_size(strlen(the_make) + 1);
|
||
if ((ret = make_curs->get(&key, &data, DB_SET)) != 0) {
|
||
// Error handling goes here
|
||
}
|
||
|
||
Dbc *type_curs;
|
||
automotiveTypeDB.cursor(NULL, &type_curs, 0);
|
||
char *the_type = "minivan";
|
||
key.set_data(the_type);
|
||
key.set_size(strlen(the_type) + 1);
|
||
if ((ret = type_curs->get(&key, &data, DB_SET)) != 0) {
|
||
// Error handling goes here
|
||
}
|
||
|
||
// Set up the cursor array
|
||
Dbc *carray[4];
|
||
carray[0] = color_curs;
|
||
carray[1] = make_curs;
|
||
carray[2] = type_curs;
|
||
carray[3] = NULL;
|
||
|
||
// Create the join
|
||
Dbc *join_curs;
|
||
if ((ret = automotiveDB.join(carray, &join_curs, 0)) != 0) {
|
||
// Error handling goes here
|
||
}
|
||
|
||
// Iterate using the join cursor
|
||
while ((ret = join_curs->get(&key, &data, 0)) == 0) {
|
||
// Do interesting things with the key and data
|
||
}
|
||
|
||
// If we exited the loop because we ran out of records,
|
||
// then it has completed successfully.
|
||
if (ret == DB_NOTFOUND) {
|
||
// Close all our cursors and databases as is appropriate, and
|
||
// then exit with a normal exit status (0).
|
||
} </pre>
|
||
</div>
|
||
</div>
|
||
<div class="navfooter">
|
||
<hr />
|
||
<table width="100%" summary="Navigation footer">
|
||
<tr>
|
||
<td width="40%" align="left"><a accesskey="p" href="secondaryCursor.html">Prev</a> </td>
|
||
<td width="20%" align="center">
|
||
<a accesskey="u" href="indexes.html">Up</a>
|
||
</td>
|
||
<td width="40%" align="right"> <a accesskey="n" href="coreindexusage.html">Next</a></td>
|
||
</tr>
|
||
<tr>
|
||
<td width="40%" align="left" valign="top">
|
||
|
||
Using Cursors with Secondary Databases
|
||
</td>
|
||
<td width="20%" align="center">
|
||
<a accesskey="h" href="index.html">Home</a>
|
||
</td>
|
||
<td width="40%" align="right" valign="top"> Secondary Database Example</td>
|
||
</tr>
|
||
</table>
|
||
</div>
|
||
</body>
|
||
</html>
|