mirror of
https://github.com/yasuhirokimura/db18
synced 2026-06-08 18:29:23 +00:00
Yasuhiro KIMURA
983 lines
38 KiB
HTML
983 lines
38 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>Cursor operations</title>
|
||
<link rel="stylesheet" href="gettingStarted.css" type="text/css" />
|
||
<meta name="generator" content="DocBook XSL Stylesheets V1.73.2" />
|
||
<link rel="start" href="index.html" title="Berkeley DB Programmer's Reference Guide" />
|
||
<link rel="up" href="am.html" title="Chapter 3. Access Method Operations" />
|
||
<link rel="prev" href="am_foreign.html" title="Foreign key indexes" />
|
||
<link rel="next" href="am_misc.html" title="Chapter 4. Access Method Wrapup" />
|
||
</head>
|
||
<body>
|
||
<div xmlns="" class="navheader">
|
||
<div class="libver">
|
||
<p>Library Version 18.1.40</p>
|
||
</div>
|
||
<table width="100%" summary="Navigation header">
|
||
<tr>
|
||
<th colspan="3" align="center">Cursor operations</th>
|
||
</tr>
|
||
<tr>
|
||
<td width="20%" align="left"><a accesskey="p" href="am_foreign.html">Prev</a> </td>
|
||
<th width="60%" align="center">Chapter 3. Access Method Operations </th>
|
||
<td width="20%" align="right"> <a accesskey="n" href="am_misc.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="am_cursor"></a>Cursor operations</h2>
|
||
</div>
|
||
</div>
|
||
</div>
|
||
<div class="toc">
|
||
<dl>
|
||
<dt>
|
||
<span class="sect2">
|
||
<a href="am_cursor.html#am_curget">Retrieving records with a
|
||
cursor</a>
|
||
</span>
|
||
</dt>
|
||
<dt>
|
||
<span class="sect2">
|
||
<a href="am_cursor.html#am_curput">Storing records with a cursor</a>
|
||
</span>
|
||
</dt>
|
||
<dt>
|
||
<span class="sect2">
|
||
<a href="am_cursor.html#am_curdel">Deleting records with a cursor</a>
|
||
</span>
|
||
</dt>
|
||
<dt>
|
||
<span class="sect2">
|
||
<a href="am_cursor.html#am_curdup">Duplicating a cursor</a>
|
||
</span>
|
||
</dt>
|
||
<dt>
|
||
<span class="sect2">
|
||
<a href="am_cursor.html#am_join">Equality Join</a>
|
||
</span>
|
||
</dt>
|
||
<dt>
|
||
<span class="sect2">
|
||
<a href="am_cursor.html#am_count">Data item count</a>
|
||
</span>
|
||
</dt>
|
||
<dt>
|
||
<span class="sect2">
|
||
<a href="am_cursor.html#am_curclose">Cursor close</a>
|
||
</span>
|
||
</dt>
|
||
</dl>
|
||
</div>
|
||
<p>
|
||
A database cursor refers to a single key/data pair in the
|
||
database. It supports traversal of the database and is the
|
||
only way to access individual duplicate data items. Cursors
|
||
are used for operating on collections of records, for
|
||
iterating over a database, and for saving handles to
|
||
individual records, so that they can be modified after they
|
||
have been read.
|
||
</p>
|
||
<p>
|
||
The <a href="../api_reference/C/dbcursor.html" class="olink">DB->cursor()</a> method opens a cursor into a database. Upon
|
||
return the cursor is uninitialized, cursor positioning occurs
|
||
as part of the first cursor operation.
|
||
</p>
|
||
<p>
|
||
Once a database cursor has been opened, records may be
|
||
retrieved (<a href="../api_reference/C/dbcget.html" class="olink">DBC->get()</a>), stored (<a href="../api_reference/C/dbcput.html" class="olink">DBC->put()</a>), and deleted
|
||
(<a href="../api_reference/C/dbcdel.html" class="olink">DBC->del()</a>).
|
||
</p>
|
||
<p>
|
||
Additional operations supported by the cursor handle
|
||
include duplication (<a href="../api_reference/C/dbcdup.html" class="olink">DBC->dup()</a>), equality join (<a href="../api_reference/C/dbjoin.html" class="olink">DB->join()</a>), and
|
||
a count of duplicate data items (<a href="../api_reference/C/dbccount.html" class="olink">DBC->count()</a>). Cursors are
|
||
eventually closed using <a href="../api_reference/C/dbcclose.html" class="olink">DBC->close()</a>.
|
||
</p>
|
||
<p>
|
||
For more information on the operations supported by the
|
||
cursor handle, see the <a href="../api_reference/C/dbc.html#dbclist" class="olink">Database Cursors and Related
|
||
Methods</a> section in the
|
||
<em class="citetitle">Berkeley DB C API Reference Guide.</em>
|
||
</p>
|
||
<div class="sect2" lang="en" xml:lang="en">
|
||
<div class="titlepage">
|
||
<div>
|
||
<div>
|
||
<h3 class="title"><a id="am_curget"></a>Retrieving records with a
|
||
cursor</h3>
|
||
</div>
|
||
</div>
|
||
</div>
|
||
<p>
|
||
The <a href="../api_reference/C/dbcget.html" class="olink">DBC->get()</a> method retrieves records from the database
|
||
using a cursor. The <a href="../api_reference/C/dbcget.html" class="olink">DBC->get()</a> method takes a flag which
|
||
controls how the cursor is positioned within the database and
|
||
returns the key/data item associated with that positioning.
|
||
Similar to <a href="../api_reference/C/dbget.html" class="olink">DB->get()</a>, <a href="../api_reference/C/dbcget.html" class="olink">DBC->get()</a> may also take a supplied key and
|
||
retrieve the data associated with that key from the database.
|
||
There are several flags that you can set to customize
|
||
retrieval.
|
||
</p>
|
||
<div class="sect3" lang="en" xml:lang="en">
|
||
<div class="titlepage">
|
||
<div>
|
||
<div>
|
||
<h4 class="title"><a id="idm140654540096272"></a>Cursor position flags</h4>
|
||
</div>
|
||
</div>
|
||
</div>
|
||
<div class="variablelist">
|
||
<dl>
|
||
<dt>
|
||
<span class="term"><a href="../api_reference/C/dbcget.html#dbcget_DB_FIRST" class="olink">DB_FIRST</a>, <a href="../api_reference/C/dbcget.html#dbcget_DB_LAST" class="olink">DB_LAST</a></span>
|
||
</dt>
|
||
<dd>
|
||
Return the first (last) record in the
|
||
database.
|
||
</dd>
|
||
<dt>
|
||
<span class="term"><a href="../api_reference/C/dbcget.html#dbcget_DB_NEXT" class="olink">DB_NEXT</a>, <a href="../api_reference/C/dbcget.html#dbcget_DB_PREV" class="olink">DB_PREV</a></span>
|
||
</dt>
|
||
<dd>
|
||
Return the next (previous) record in the
|
||
database.
|
||
</dd>
|
||
<dt>
|
||
<span class="term">
|
||
<a href="../api_reference/C/dbcget.html#dbcget_DB_NEXT_DUP" class="olink">DB_NEXT_DUP</a>
|
||
</span>
|
||
</dt>
|
||
<dd>
|
||
Return the next record in the database, if
|
||
it is a duplicate data item for the current key.
|
||
For Heap databases, this flag always results in
|
||
the cursor returning the
|
||
<code class="literal">DB_NOTFOUND</code> error.
|
||
</dd>
|
||
<dt>
|
||
<span class="term"><a href="../api_reference/C/dbcget.html#dbcget_DB_NEXT_NODUP" class="olink">DB_NEXT_NODUP</a>, <a href="../api_reference/C/dbcget.html#dbcget_DB_PREV_NODUP" class="olink">DB_PREV_NODUP</a></span>
|
||
</dt>
|
||
<dd>
|
||
Return the next (previous) record in the
|
||
database that is not a duplicate data item for the
|
||
current key.
|
||
</dd>
|
||
<dt>
|
||
<span class="term">
|
||
<a href="../api_reference/C/dbcget.html#dbcget_DB_CURRENT" class="olink">DB_CURRENT</a>
|
||
</span>
|
||
</dt>
|
||
<dd>
|
||
Return the record from the database to
|
||
which the cursor currently refers.
|
||
</dd>
|
||
</dl>
|
||
</div>
|
||
</div>
|
||
<div class="sect3" lang="en" xml:lang="en">
|
||
<div class="titlepage">
|
||
<div>
|
||
<div>
|
||
<h4 class="title"><a id="idm140654540085536"></a>Retrieving specific key/data pairs</h4>
|
||
</div>
|
||
</div>
|
||
</div>
|
||
<div class="variablelist">
|
||
<dl>
|
||
<dt>
|
||
<span class="term">
|
||
<a href="../api_reference/C/dbcget.html#dbcget_DB_SET" class="olink">DB_SET</a>
|
||
</span>
|
||
</dt>
|
||
<dd>
|
||
Return the record from the database that
|
||
matches the supplied key. In the case of
|
||
duplicates the first duplicate is returned and the
|
||
cursor is positioned at the beginning of the
|
||
duplicate list. The user can then traverse the
|
||
duplicate entries for the key.
|
||
</dd>
|
||
<dt>
|
||
<span class="term">
|
||
<a href="../api_reference/C/dbcget.html#dbcget_DB_SET_RANGE" class="olink">DB_SET_RANGE</a>
|
||
</span>
|
||
</dt>
|
||
<dd>
|
||
Return the smallest record in the database
|
||
greater than or equal to the supplied key. This
|
||
functionality permits partial key matches and
|
||
range searches in the Btree access method.
|
||
</dd>
|
||
<dt>
|
||
<span class="term">
|
||
<a href="../api_reference/C/dbcget.html#dbcget_DB_GET_BOTH" class="olink">DB_GET_BOTH</a>
|
||
</span>
|
||
</dt>
|
||
<dd>
|
||
Return the record from the database that
|
||
matches both the supplied key and data items. This
|
||
is particularly useful when there are large
|
||
numbers of duplicate records for a key, as it
|
||
allows the cursor to easily be positioned at the
|
||
correct place for traversal of some part of a
|
||
large set of duplicate records.
|
||
</dd>
|
||
<dt>
|
||
<span class="term">
|
||
<a href="../api_reference/C/dbcget.html#dbcget_DB_GET_BOTH_RANGE" class="olink">DB_GET_BOTH_RANGE</a>
|
||
</span>
|
||
</dt>
|
||
<dd>
|
||
If used on a database configured for sorted
|
||
duplicates, this returns the smallest record in
|
||
the database greater than or equal to the supplied
|
||
key and data items. If used on a database that is
|
||
<span class="emphasis"><em>not</em></span> configured for sorted
|
||
duplicates, this flag behaves identically to
|
||
<code class="literal">DB_GET_BOTH</code>.
|
||
</dd>
|
||
</dl>
|
||
</div>
|
||
</div>
|
||
<div class="sect3" lang="en" xml:lang="en">
|
||
<div class="titlepage">
|
||
<div>
|
||
<div>
|
||
<h4 class="title"><a id="idm140654540104192"></a>Retrieving based on record numbers</h4>
|
||
</div>
|
||
</div>
|
||
</div>
|
||
<div class="variablelist">
|
||
<dl>
|
||
<dt>
|
||
<span class="term">
|
||
<a href="../api_reference/C/dbcget.html#dbcget_DB_SET_RECNO" class="olink">DB_SET_RECNO</a>
|
||
</span>
|
||
</dt>
|
||
<dd>
|
||
If the underlying database is a Btree, and
|
||
was configured so that it is possible to search it
|
||
by logical record number, retrieve a specific
|
||
record based on a record number argument.
|
||
</dd>
|
||
<dt>
|
||
<span class="term">
|
||
<a href="../api_reference/C/dbcget.html#dbcget_DB_GET_RECNO" class="olink">DB_GET_RECNO</a>
|
||
</span>
|
||
</dt>
|
||
<dd>
|
||
If the underlying database is a Btree, and
|
||
was configured so that it is possible to search it
|
||
by logical record number, return the record number
|
||
for the record to which the cursor refers.
|
||
</dd>
|
||
</dl>
|
||
</div>
|
||
</div>
|
||
<div class="sect3" lang="en" xml:lang="en">
|
||
<div class="titlepage">
|
||
<div>
|
||
<div>
|
||
<h4 class="title"><a id="idm140654540087200"></a>Special-purpose flags</h4>
|
||
</div>
|
||
</div>
|
||
</div>
|
||
<div class="variablelist">
|
||
<dl>
|
||
<dt>
|
||
<span class="term">
|
||
<a href="../api_reference/C/dbget.html#dbget_DB_CONSUME" class="olink">DB_CONSUME</a>
|
||
</span>
|
||
</dt>
|
||
<dd>
|
||
Read-and-delete: the first record (the
|
||
head) of the queue is returned and deleted. The
|
||
underlying database must be a Queue.
|
||
</dd>
|
||
<dt>
|
||
<span class="term">
|
||
<a href="../api_reference/C/dbcget.html#dbcget_DB_RMW" class="olink">DB_RMW</a>
|
||
</span>
|
||
</dt>
|
||
<dd>
|
||
Read-modify-write: acquire write locks
|
||
instead of read locks during retrieval. This can
|
||
enhance performance in threaded applications by
|
||
reducing the chance of deadlock.
|
||
</dd>
|
||
</dl>
|
||
</div>
|
||
<p>
|
||
In all cases, the cursor is repositioned by a <a href="../api_reference/C/dbcget.html" class="olink">DBC->get()</a>
|
||
operation to point to the newly-returned key/data pair in
|
||
the database.
|
||
</p>
|
||
<p>
|
||
The following is a code example showing a cursor
|
||
walking through a database and displaying the records it
|
||
contains to the standard output:
|
||
</p>
|
||
<a id="prog_am19"></a>
|
||
<pre class="programlisting">int
|
||
display(char *database)
|
||
|
||
{
|
||
DB *dbp;
|
||
DBC *dbcp;
|
||
DBT key, data;
|
||
int close_db, close_dbc, ret;
|
||
|
||
close_db = close_dbc = 0;
|
||
|
||
/* Open the database. */
|
||
if ((ret = db_create(&dbp, NULL, 0)) != 0) {
|
||
fprintf(stderr,
|
||
"%s: db_create: %s\n", progname, db_strerror(ret));
|
||
return (1);
|
||
}
|
||
close_db = 1;
|
||
|
||
/* Turn on additional error output. */
|
||
dbp->set_errfile(dbp, stderr);
|
||
dbp->set_errpfx(dbp, progname);
|
||
|
||
/* Open the database. */
|
||
if ((ret = dbp->open(dbp, NULL, database, NULL,
|
||
DB_UNKNOWN, DB_RDONLY, 0)) != 0) {
|
||
dbp->err(dbp, ret, "%s: DB->open", database);
|
||
goto err;
|
||
}
|
||
|
||
/* Acquire a cursor for the database. */
|
||
if ((ret = dbp->cursor(dbp, NULL, &dbcp, 0)) != 0) {
|
||
dbp->err(dbp, ret, "DB->cursor");
|
||
goto err;
|
||
}
|
||
close_dbc = 1;
|
||
|
||
/* Initialize the key/data return pair. */
|
||
memset(&key, 0, sizeof(key));
|
||
memset(&data, 0, sizeof(data));
|
||
|
||
/* Walk through the database and print out the key/data pairs. */
|
||
while ((ret = dbcp->get(dbcp, &key, &data, DB_NEXT)) == 0)
|
||
printf("%.*s : %.*s\n",
|
||
(int)key.size, (char *)key.data,
|
||
(int)data.size, (char *)data.data);
|
||
if (ret != DB_NOTFOUND) {
|
||
dbp->err(dbp, ret, "DBcursor->get");
|
||
goto err;
|
||
}
|
||
|
||
err: if (close_dbc && (ret = dbcp->close(dbcp)) != 0)
|
||
dbp->err(dbp, ret, "DBcursor->close");
|
||
if (close_db && (ret = dbp->close(dbp, 0)) != 0)
|
||
fprintf(stderr,
|
||
"%s: DB->close: %s\n", progname, db_strerror(ret));
|
||
return (0);
|
||
}</pre>
|
||
</div>
|
||
</div>
|
||
<div class="sect2" lang="en" xml:lang="en">
|
||
<div class="titlepage">
|
||
<div>
|
||
<div>
|
||
<h3 class="title"><a id="am_curput"></a>Storing records with a cursor</h3>
|
||
</div>
|
||
</div>
|
||
</div>
|
||
<p>
|
||
The <a href="../api_reference/C/dbcput.html" class="olink">DBC->put()</a> method stores records into the database using
|
||
a cursor. In general, <a href="../api_reference/C/dbcput.html" class="olink">DBC->put()</a> takes a key and inserts the
|
||
associated data into the database, at a location controlled by
|
||
a specified flag.
|
||
</p>
|
||
<p>
|
||
There are several flags that you can set to customize
|
||
storage:
|
||
</p>
|
||
<div class="variablelist">
|
||
<dl>
|
||
<dt>
|
||
<span class="term">
|
||
<a href="../api_reference/C/dbcput.html#dbcput_DB_AFTER" class="olink">DB_AFTER</a>
|
||
</span>
|
||
</dt>
|
||
<dd>
|
||
Create a new record, immediately after the
|
||
record to which the cursor refers.
|
||
</dd>
|
||
<dt>
|
||
<span class="term">
|
||
<a href="../api_reference/C/dbcput.html#dbcput_DB_BEFORE" class="olink">DB_BEFORE</a>
|
||
</span>
|
||
</dt>
|
||
<dd>
|
||
Create a new record, immediately before the
|
||
record to which the cursor refers.
|
||
</dd>
|
||
<dt>
|
||
<span class="term">
|
||
<a href="../api_reference/C/dbcget.html#dbcget_DB_CURRENT" class="olink">DB_CURRENT</a>
|
||
</span>
|
||
</dt>
|
||
<dd>
|
||
Replace the data part of the record to which
|
||
the cursor refers.
|
||
</dd>
|
||
<dt>
|
||
<span class="term">
|
||
<a href="../api_reference/C/dbcput.html#dbcput_DB_KEYFIRST" class="olink">DB_KEYFIRST</a>
|
||
</span>
|
||
</dt>
|
||
<dd>
|
||
Create a new record as the first of the
|
||
duplicate records for the supplied key.
|
||
</dd>
|
||
<dt>
|
||
<span class="term">
|
||
<a href="../api_reference/C/dbcput.html#dbcput_DB_KEYLAST" class="olink">DB_KEYLAST</a>
|
||
</span>
|
||
</dt>
|
||
<dd>
|
||
Create a new record, as the last of the
|
||
duplicate records for the supplied key.
|
||
</dd>
|
||
</dl>
|
||
</div>
|
||
<p>
|
||
In all cases, the cursor is repositioned by a <a href="../api_reference/C/dbcput.html" class="olink">DBC->put()</a>
|
||
operation to point to the newly inserted key/data pair in the
|
||
database.
|
||
</p>
|
||
<p>
|
||
The following is a code example showing a cursor storing
|
||
two data items in a database that supports duplicate data
|
||
items:
|
||
</p>
|
||
<a id="prog_am20"></a>
|
||
<pre class="programlisting">int
|
||
store(DB *dbp)
|
||
|
||
{
|
||
DBC *dbcp;
|
||
DBT key, data;
|
||
int ret;
|
||
|
||
/*
|
||
* The DB handle for a Btree database supporting duplicate data
|
||
* items is the argument; acquire a cursor for the database.
|
||
*/
|
||
if ((ret = dbp->cursor(dbp, NULL, &dbcp, 0)) != 0) {
|
||
dbp->err(dbp, ret, "DB->cursor");
|
||
goto err;
|
||
}
|
||
|
||
/* Initialize the key. */
|
||
memset(&key, 0, sizeof(key));
|
||
key.data = "new key";
|
||
key.size = strlen(key.data) + 1;
|
||
|
||
/* Initialize the data to be the first of two duplicate records. */
|
||
memset(&data, 0, sizeof(data));
|
||
data.data = "new key's data: entry #1";
|
||
data.size = strlen(data.data) + 1;
|
||
|
||
/* Store the first of the two duplicate records. */
|
||
if ((ret = dbcp->put(dbcp, &key, &data, DB_KEYFIRST)) != 0)
|
||
dbp->err(dbp, ret, "DB->cursor");
|
||
|
||
/* Initialize the data to be the second of two duplicate records. */
|
||
data.data = "new key's data: entry #2";
|
||
data.size = strlen(data.data) + 1;
|
||
|
||
/*
|
||
* Store the second of the two duplicate records. No duplicate
|
||
* record sort function has been specified, so we explicitly
|
||
* store the record as the last of the duplicate set.
|
||
*/
|
||
if ((ret = dbcp->put(dbcp, &key, &data, DB_KEYLAST)) != 0)
|
||
dbp->err(dbp, ret, "DB->cursor");
|
||
|
||
err: if ((ret = dbcp->close(dbcp)) != 0)
|
||
dbp->err(dbp, ret, "DBcursor->close");
|
||
|
||
return (0);
|
||
}</pre>
|
||
<div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">
|
||
<h3 class="title">Note</h3>
|
||
<p>
|
||
If you are using the Heap access method and you are
|
||
creating a new record in the database, then the key that
|
||
you provide to the <a href="../api_reference/C/dbcput.html" class="olink">DBC->put()</a> method should be empty. The
|
||
<a href="../api_reference/C/dbcput.html" class="olink">DBC->put()</a> method will return the record's ID (RID) in the
|
||
key. The RID is automatically created for you when Heap
|
||
database records are created.
|
||
</p>
|
||
</div>
|
||
</div>
|
||
<div class="sect2" lang="en" xml:lang="en">
|
||
<div class="titlepage">
|
||
<div>
|
||
<div>
|
||
<h3 class="title"><a id="am_curdel"></a>Deleting records with a cursor</h3>
|
||
</div>
|
||
</div>
|
||
</div>
|
||
<p>
|
||
The <a href="../api_reference/C/dbcdel.html" class="olink">DBC->del()</a> method deletes records from the database using
|
||
a cursor. The <a href="../api_reference/C/dbcdel.html" class="olink">DBC->del()</a> method deletes the record to which the
|
||
cursor currently refers. In all cases, the cursor position is
|
||
unchanged after a delete.
|
||
</p>
|
||
</div>
|
||
<div class="sect2" lang="en" xml:lang="en">
|
||
<div class="titlepage">
|
||
<div>
|
||
<div>
|
||
<h3 class="title"><a id="am_curdup"></a>Duplicating a cursor</h3>
|
||
</div>
|
||
</div>
|
||
</div>
|
||
<p>
|
||
Once a cursor has been initialized (for example, by a call
|
||
to <a href="../api_reference/C/dbcget.html" class="olink">DBC->get()</a>), it can be thought of as identifying a particular
|
||
location in a database. The <a href="../api_reference/C/dbcdup.html" class="olink">DBC->dup()</a> method permits an
|
||
application to create a new cursor that has the same locking
|
||
and transactional information as the cursor from which it is
|
||
copied, and which optionally refers to the same position in
|
||
the database.
|
||
</p>
|
||
<p>
|
||
In order to maintain a cursor position when an application
|
||
is using locking, locks are maintained on behalf of the cursor
|
||
until the cursor is closed. In cases when an application is
|
||
using locking without transactions, cursor duplication is
|
||
often required to avoid self-deadlocks. For further details,
|
||
refer to <a class="xref" href="lock_am_conv.html" title="Berkeley DB Transactional Data Store locking conventions">Berkeley DB Transactional Data
|
||
Store locking conventions</a>.
|
||
</p>
|
||
</div>
|
||
<div class="sect2" lang="en" xml:lang="en">
|
||
<div class="titlepage">
|
||
<div>
|
||
<div>
|
||
<h3 class="title"><a id="am_join"></a>Equality Join</h3>
|
||
</div>
|
||
</div>
|
||
</div>
|
||
<p>
|
||
Berkeley DB supports "equality" (also known as "natural"),
|
||
joins on secondary indices. An equality join is a method of
|
||
retrieving data from a primary database using criteria stored
|
||
in a set of secondary indices. It requires the data be
|
||
organized as a primary database which contains the primary key
|
||
and primary data field, and a set of secondary indices. Each
|
||
of the secondary indices is indexed by a different secondary
|
||
key, and, for each key in a secondary index, there is a set of
|
||
duplicate data items that match the primary keys in the
|
||
primary database.
|
||
</p>
|
||
<p>
|
||
For example, let's assume the need for an application that
|
||
will return the names of stores in which one can buy fruit of
|
||
a given color. We would first construct a primary database
|
||
that lists types of fruit as the key item, and the store where
|
||
you can buy them as the data item:
|
||
</p>
|
||
<div class="informaltable">
|
||
<table border="1" width="80%">
|
||
<colgroup>
|
||
<col />
|
||
<col />
|
||
</colgroup>
|
||
<thead>
|
||
<tr>
|
||
<th>Primary key:</th>
|
||
<th>Primary data:</th>
|
||
</tr>
|
||
</thead>
|
||
<tbody>
|
||
<tr>
|
||
<td align="left">apple</td>
|
||
<td align="left">Convenience Store</td>
|
||
</tr>
|
||
<tr>
|
||
<td align="left">blueberry</td>
|
||
<td align="left">Farmer's Market</td>
|
||
</tr>
|
||
<tr>
|
||
<td align="left">peach</td>
|
||
<td align="left">Shopway</td>
|
||
</tr>
|
||
<tr>
|
||
<td align="left">pear</td>
|
||
<td align="left">Farmer's Market</td>
|
||
</tr>
|
||
<tr>
|
||
<td align="left">raspberry</td>
|
||
<td align="left">Shopway</td>
|
||
</tr>
|
||
<tr>
|
||
<td align="left">strawberry</td>
|
||
<td align="left">Farmer's Market</td>
|
||
</tr>
|
||
</tbody>
|
||
</table>
|
||
</div>
|
||
<p>
|
||
We would then create a secondary index with the key
|
||
<span class="bold"><strong>color</strong></span>, and, as the data
|
||
items, the names of fruits of different colors.
|
||
</p>
|
||
<div class="informaltable">
|
||
<table border="1" width="80%">
|
||
<colgroup>
|
||
<col />
|
||
<col />
|
||
</colgroup>
|
||
<thead>
|
||
<tr>
|
||
<th>Secondary key:</th>
|
||
<th>Secondary data:</th>
|
||
</tr>
|
||
</thead>
|
||
<tbody>
|
||
<tr>
|
||
<td align="left">blue</td>
|
||
<td align="left">blueberry</td>
|
||
</tr>
|
||
<tr>
|
||
<td align="left">red</td>
|
||
<td align="left">apple</td>
|
||
</tr>
|
||
<tr>
|
||
<td align="left">red</td>
|
||
<td align="left">raspberry</td>
|
||
</tr>
|
||
<tr>
|
||
<td align="left">red</td>
|
||
<td align="left">strawberry</td>
|
||
</tr>
|
||
<tr>
|
||
<td align="left">yellow</td>
|
||
<td align="left">peach</td>
|
||
</tr>
|
||
<tr>
|
||
<td align="left">yellow</td>
|
||
<td align="left">pear</td>
|
||
</tr>
|
||
</tbody>
|
||
</table>
|
||
</div>
|
||
<p>
|
||
This secondary index would allow an application to look up a
|
||
color, and then use the data items to look up the stores where
|
||
the colored fruit could be purchased. For example, by first
|
||
looking up <span class="bold"><strong>blue</strong></span>, the data
|
||
item <span class="bold"><strong>blueberry</strong></span> could be used
|
||
as the lookup key in the primary database, returning <span class="bold"><strong>Farmer's Market</strong></span>.
|
||
</p>
|
||
<p>
|
||
Your data must be organized in the following manner in order
|
||
to use the <a href="../api_reference/C/dbjoin.html" class="olink">DB->join()</a> method:
|
||
</p>
|
||
<div class="orderedlist">
|
||
<ol type="1">
|
||
<li>
|
||
The actual data should be stored in the database
|
||
represented by the <a href="../api_reference/C/db.html" class="olink">DB</a> object used to invoke this method.
|
||
Generally, this <a href="../api_reference/C/db.html" class="olink">DB</a> object is called the
|
||
<span class="emphasis"><em>primary</em></span>.
|
||
</li>
|
||
<li>
|
||
Secondary indices should be stored in separate
|
||
databases, whose keys are the values of the secondary
|
||
indices and whose data items are the primary keys
|
||
corresponding to the records having the designated
|
||
secondary key value. It is acceptable (and expected) that
|
||
there may be duplicate entries in the secondary indices.
|
||
<p>
|
||
These duplicate entries should be sorted for
|
||
performance reasons, although it is not required. For
|
||
more information see the <a href="../api_reference/C/dbset_flags.html#dbset_flags_DB_DUPSORT" class="olink">DB_DUPSORT</a> flag to the
|
||
<a href="../api_reference/C/dbset_flags.html" class="olink">DB->set_flags()</a> method.
|
||
</p></li>
|
||
</ol>
|
||
</div>
|
||
<p>
|
||
What the <a href="../api_reference/C/dbjoin.html" class="olink">DB->join()</a> method does is review a list of secondary
|
||
keys, and, when it finds a data item that appears as a data
|
||
item for all of the secondary keys, it uses that data item as
|
||
a lookup into the primary database, and returns the associated
|
||
data item.
|
||
</p>
|
||
<p>
|
||
If there were another secondary index that had as its key
|
||
the <span class="bold"><strong>cost</strong></span> of the fruit, a
|
||
similar lookup could be done on stores where inexpensive fruit
|
||
could be purchased:
|
||
</p>
|
||
<div class="informaltable">
|
||
<table border="1" width="80%">
|
||
<colgroup>
|
||
<col />
|
||
<col />
|
||
</colgroup>
|
||
<thead>
|
||
<tr>
|
||
<th>Secondary key:</th>
|
||
<th>Secondary data:</th>
|
||
</tr>
|
||
</thead>
|
||
<tbody>
|
||
<tr>
|
||
<td align="left">expensive</td>
|
||
<td align="left">blueberry</td>
|
||
</tr>
|
||
<tr>
|
||
<td align="left">expensive</td>
|
||
<td align="left">peach</td>
|
||
</tr>
|
||
<tr>
|
||
<td align="left">expensive</td>
|
||
<td align="left">pear</td>
|
||
</tr>
|
||
<tr>
|
||
<td align="left">expensive</td>
|
||
<td align="left">strawberry</td>
|
||
</tr>
|
||
<tr>
|
||
<td align="left">inexpensive</td>
|
||
<td align="left">apple</td>
|
||
</tr>
|
||
<tr>
|
||
<td align="left">inexpensive</td>
|
||
<td align="left">pear</td>
|
||
</tr>
|
||
<tr>
|
||
<td align="left">inexpensive</td>
|
||
<td align="left">raspberry</td>
|
||
</tr>
|
||
</tbody>
|
||
</table>
|
||
</div>
|
||
<p>
|
||
The <a href="../api_reference/C/dbjoin.html" class="olink">DB->join()</a> method provides equality join functionality.
|
||
While not strictly cursor functionality, in that it is not a
|
||
method off a cursor handle, it is more closely related to the
|
||
cursor operations than to the standard <a href="../api_reference/C/db.html" class="olink">DB</a> operations.
|
||
</p>
|
||
<p>
|
||
It is also possible to do lookups based on multiple criteria
|
||
in a single operation. For example, it is possible to look up
|
||
fruits that are both red and expensive in a single operation.
|
||
If the same fruit appeared as a data item in both the color
|
||
and expense indices, then that fruit name would be used as the
|
||
key for retrieval from the primary index, and would then
|
||
return the store where expensive, red fruit could be
|
||
purchased.
|
||
</p>
|
||
<div class="sect3" lang="en" xml:lang="en">
|
||
<div class="titlepage">
|
||
<div>
|
||
<div>
|
||
<h4 class="title"><a id="idm140654539992848"></a>Example</h4>
|
||
</div>
|
||
</div>
|
||
</div>
|
||
<p>
|
||
Consider the following three databases:
|
||
</p>
|
||
<div class="variablelist">
|
||
<dl>
|
||
<dt>
|
||
<span class="term">personnel</span>
|
||
</dt>
|
||
<dd>
|
||
<div class="itemizedlist">
|
||
<ul type="disc">
|
||
<li>key = SSN</li>
|
||
<li>data = record containing name,
|
||
address, phone number, job
|
||
title</li>
|
||
</ul>
|
||
</div>
|
||
</dd>
|
||
<dt>
|
||
<span class="term">lastname</span>
|
||
</dt>
|
||
<dd>
|
||
<div class="itemizedlist">
|
||
<ul type="disc">
|
||
<li>key = lastname</li>
|
||
<li>data = SSN</li>
|
||
</ul>
|
||
</div>
|
||
</dd>
|
||
<dt>
|
||
<span class="term">jobs</span>
|
||
</dt>
|
||
<dd>
|
||
<div class="itemizedlist">
|
||
<ul type="disc">
|
||
<li>key = job title</li>
|
||
<li>data = SSN</li>
|
||
</ul>
|
||
</div>
|
||
</dd>
|
||
</dl>
|
||
</div>
|
||
<p>
|
||
Consider the following query:
|
||
</p>
|
||
<pre class="programlisting">Return the personnel records of all people named smith with the job
|
||
title manager.</pre>
|
||
<p>
|
||
This query finds are all the records in the primary
|
||
database (personnel) for whom the criteria <span class="bold"><strong>lastname=smith and job
|
||
title=manager</strong></span> is true.
|
||
</p>
|
||
<p>
|
||
Assume that all databases have been properly opened and
|
||
have the handles: pers_db, name_db, job_db. We also assume
|
||
that we have an active transaction to which the handle txn
|
||
refers.
|
||
</p>
|
||
<a id="prog_am21"></a>
|
||
<pre class="programlisting">DBC *name_curs, *job_curs, *join_curs;
|
||
DBC *carray[3];
|
||
DBT key, data;
|
||
int ret, tret;
|
||
|
||
name_curs = NULL;
|
||
job_curs = NULL;
|
||
memset(&key, 0, sizeof(key));
|
||
memset(&data, 0, sizeof(data));
|
||
|
||
if ((ret =
|
||
name_db->cursor(name_db, txn, &name_curs, 0)) != 0)
|
||
goto err;
|
||
key.data = "smith";
|
||
key.size = sizeof("smith");
|
||
if ((ret =
|
||
name_curs->get(name_curs, &key, &data, DB_SET)) != 0)
|
||
goto err;
|
||
|
||
if ((ret = job_db->cursor(job_db, txn, &job_curs, 0)) != 0)
|
||
goto err;
|
||
key.data = "manager";
|
||
key.size = sizeof("manager");
|
||
if ((ret =
|
||
job_curs->get(job_curs, &key, &data, DB_SET)) != 0)
|
||
goto err;
|
||
|
||
carray[0] = name_curs;
|
||
carray[1] = job_curs;
|
||
carray[2] = NULL;
|
||
|
||
if ((ret =
|
||
pers_db->join(pers_db, carray, &join_curs, 0)) != 0)
|
||
goto err;
|
||
while ((ret =
|
||
join_curs->get(join_curs, &key, &data, 0)) == 0) {
|
||
/* Process record returned in key/data. */
|
||
}
|
||
|
||
/*
|
||
* If we exited the loop because we ran out of records,
|
||
* then it has completed successfully.
|
||
*/
|
||
if (ret == DB_NOTFOUND)
|
||
ret = 0;
|
||
|
||
err:
|
||
if (join_curs != NULL &&
|
||
(tret = join_curs->close(join_curs)) != 0 && ret == 0)
|
||
ret = tret;
|
||
if (name_curs != NULL &&
|
||
(tret = name_curs->close(name_curs)) != 0 && ret == 0)
|
||
ret = tret;
|
||
if (job_curs != NULL &&
|
||
(tret = job_curs->close(job_curs)) != 0 && ret == 0)
|
||
ret = tret;
|
||
|
||
return (ret);</pre>
|
||
<p>
|
||
The name cursor is positioned at the beginning of the
|
||
duplicate list for <span class="bold"><strong>smith</strong></span>
|
||
and the job cursor is placed at the beginning of the
|
||
duplicate list for <span class="bold"><strong>manager</strong></span>.
|
||
The join cursor is returned from the join method. This code then
|
||
loops over the join cursor getting the personnel records of each one
|
||
until there are no more.
|
||
</p>
|
||
</div>
|
||
</div>
|
||
<div class="sect2" lang="en" xml:lang="en">
|
||
<div class="titlepage">
|
||
<div>
|
||
<div>
|
||
<h3 class="title"><a id="am_count"></a>Data item count</h3>
|
||
</div>
|
||
</div>
|
||
</div>
|
||
<p>
|
||
Once a cursor has been initialized to refer to a particular
|
||
key in the database, it can be used to determine the number of
|
||
data items that are stored for any particular key. The
|
||
<a href="../api_reference/C/dbccount.html" class="olink">DBC->count()</a> method returns this number of data items. The
|
||
returned value is always one, unless the database supports
|
||
duplicate data items, in which case it may be any number of
|
||
items.
|
||
</p>
|
||
</div>
|
||
<div class="sect2" lang="en" xml:lang="en">
|
||
<div class="titlepage">
|
||
<div>
|
||
<div>
|
||
<h3 class="title"><a id="am_curclose"></a>Cursor close</h3>
|
||
</div>
|
||
</div>
|
||
</div>
|
||
<p>
|
||
The <a href="../api_reference/C/dbcclose.html" class="olink">DBC->close()</a> method closes the <a href="../api_reference/C/dbc.html" class="olink">DBC</a> cursor, after which
|
||
the cursor may no longer be used. Although cursors are
|
||
implicitly closed when the database they point to are closed,
|
||
it is good programming practice to explicitly close cursors.
|
||
In addition, in transactional systems, cursors may not exist
|
||
outside of a transaction and so must be explicitly
|
||
closed.
|
||
</p>
|
||
</div>
|
||
</div>
|
||
<div class="navfooter">
|
||
<hr />
|
||
<table width="100%" summary="Navigation footer">
|
||
<tr>
|
||
<td width="40%" align="left"><a accesskey="p" href="am_foreign.html">Prev</a> </td>
|
||
<td width="20%" align="center">
|
||
<a accesskey="u" href="am.html">Up</a>
|
||
</td>
|
||
<td width="40%" align="right"> <a accesskey="n" href="am_misc.html">Next</a></td>
|
||
</tr>
|
||
<tr>
|
||
<td width="40%" align="left" valign="top">Foreign key indexes </td>
|
||
<td width="20%" align="center">
|
||
<a accesskey="h" href="index.html">Home</a>
|
||
</td>
|
||
<td width="40%" align="right" valign="top"> Chapter 4. Access Method Wrapup </td>
|
||
</tr>
|
||
</table>
|
||
</div>
|
||
</body>
|
||
</html>
|