1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
|
<! "@(#)join.so 10.2 (Sleepycat) 10/29/98">
<!Copyright 1997, 1998 by Sleepycat Software, Inc. All rights reserved.>
<html>
<body bgcolor=white>
<head>
<title>Berkeley DB Reference Guide: Access Methods</title>
<meta name="description" content="Berkeley DB: An embedded database programmatic toolkit.">
<meta name="keywords" content="embedded,database,programmatic,toolkit,b+tree,btr
ee,hash,hashing,transaction,transactions,locking,logging,access method,access me
thods,java,C,C++">
</head>
<h3>Berkeley DB Reference Guide: Access Methods</h3>
<p>
<h1 align=center>Logical join</h1>
<p>
The <a href="../../api_c/Db/join.html">DB->join</a> function provides logical join functionality. While
not strictly a cursor function, in that it is not a method off a
cursor handle, it is more related to the cursor functions than to
the standard DB functions.
<p>
The <a href="../../api_c/Db/join.html">DB->join</a> function requires that your data be organized as a primary
database which contains the primary key and primary data field, and a set
of secondary databases. Each of the secondary databases is indexed by a
different secondary key, and, for each key in a secondary database, there
is a set of duplicate data items that match the primary keys in the
primary database.
<p>
What the <a href="../../api_c/Db/join.html">DB->join</a> function 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 items as a lookup into the primary
database, and returns the associated data item.
<p>
For example, consider a database that lists types of fruit as the key item,
and the store where you can buy them as the data item:
<p><ul><pre>
<b>Key:</b> <b>Data:</b>
apple Convenience Store
peach Shopway
pear Farmer's Market
raspberry Shopway
strawberry Farmer's Market
blueberry Farmer's Market
</pre></ul><p>
<p>
A secondary index might have a key <b>color</b>, and, as the data
items, the list of fruits of different colors.
<p><ul><pre>
<b>Key:</b> <b>Data:</b>
red apple, raspberry, strawberry
yellow peach, pear
blue blueberry
</pre></ul><p>
<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, e.g., by first looking up <b>blue</b>, the data
item <b>blueberry</b> could be used as the lookup key in the primary
database, returning <b>Farmer's Market</b>.
<p>
If there were a another secondary index that had as its key the
<b>cost</b> of the fruit, a similar lookup could be done on stores
where inexpensive fruit could be purchased:
<p><ul><pre>
<b>Key:</b> <b>Data:</b>
expensive blueberry, peach, pear, strawberry
inexpensive apple, pear, raspberry
</pre></ul><p>
<p>
The <a href="../../api_c/Db/join.html">DB->join</a> function, makes it possible to do lookups based on multiple
criteria in a single operation, e.g., it would be possible to look up
fruits that were 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.
<h1>Example</h1>
<p>
Consider the following three databases:
<p>
<dl compact>
<p><dt>personnel<dd>
<ul type=disc>
<li>key = SSN
<li>data = record containing name, address, phone number, job title
</ul>
<dt>lastname<dd>
<ul type=disc>
<li>key = lastname
<li>data = SSN
</ul>
<dt>jobs<dd>
<ul type=disc>
<li>key = job title
<li>data = SSN
</ul>
</dl>
<p>
Consider the following query:
<p>
<p><ul><pre>Return all smiths who are managers.</pre></ul><p>
<p>
What is desired are all the records in the primary database (personnel)
for whom the criteria <b>lastname=smith, job title=manager</b> is
true.
<p>
Assume that all databases have been properly opened and have the
handles: pers_db, name_db, job_db. Assume that we have an active
transaction referenced by the handle txn.
<p>
<p><ul><pre>
DBC *name_curs, *job_curs, *join_curs;
DBC *carray[3];
DBT key, data;
int ret, tret;
<p>
name_curs = NULL;
job_curs = NULL;
memset(&key, 0, sizeof(key));
memset(&data, 0, sizeof(data));
<p>
if ((ret = name_db->cursor(name_db, txn, &name_curs)) != 0)
goto err;
key.data = "smith";
key.size = sizeof("smith");
if ((ret = name_curs->c_get(name_curs, &key, &data, DB_SET)) != 0)
goto err;
<p>
if ((ret = job_db->cursor(job_db, txn, &job_curs)) != 0)
goto err;
key.data = "manager";
key.size = sizeof("manager");
if ((ret = job_curs->c_get(job_curs, &key, &data, DB_SET)) != 0)
goto err;
<p>
carray[0] = name_curs;
carray[1] = job_curs;
carray[2] = NULL;
<p>
if ((ret = pers_db->join(pers_db, carray, 0, &join_curs)) != 0)
goto err;
<p>
while ((ret = join_curs->c_get(join_curs, &key, &data, 0)) == 0) {
/* Process record returned in key/data. */
}
<p>
/*
* If we exited the loop because we ran out of records,
* then it has completed successfully.
*/
if (ret == DB_NOTFOUND)
ret = 0;
<p>
err:
if (join_curs != NULL &&
(tret = join_curs->c_close(join_curs)) != 0 && ret == 0)
ret = tret;
if (name_curs != NULL &&
(tret = name_curs->c_close(name_curs)) != 0 && ret == 0)
ret = tret;
if (job_curs != NULL &&
(tret = job_curs->c_close(job_curs)) != 0 && ret == 0)
ret = tret;
return (ret);
<p>
</pre></ul><p>
<p>
<a href="../../ref/am/curdel.html"><img src="../../images/prev.gif"></a>
<a href="../../ref/toc.html"><img src="../../images/toc.gif"></a>
<a href="../../ref/am/curclose.html"><img src="../../images/next.gif"></a>
</tt>
</body>
</html>
|
