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
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
|
<?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>Chapter 3. Using Secondary Indices</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 Collections Tutorial" />
<link rel="up" href="index.html" title="Berkeley DB Collections Tutorial" />
<link rel="prev" href="handlingexceptions.html" title="Handling Exceptions" />
<link rel="next" href="openingforeignkeys.html" title="More Secondary Key Indices" />
</head>
<body>
<div xmlns="" class="navheader">
<div class="libver">
<p>Library Version 11.2.5.3</p>
</div>
<table width="100%" summary="Navigation header">
<tr>
<th colspan="3" align="center">Chapter 3.
Using Secondary Indices
</th>
</tr>
<tr>
<td width="20%" align="left"><a accesskey="p" href="handlingexceptions.html">Prev</a> </td>
<th width="60%" align="center"> </th>
<td width="20%" align="right"> <a accesskey="n" href="openingforeignkeys.html">Next</a></td>
</tr>
</table>
<hr />
</div>
<div class="chapter" lang="en" xml:lang="en">
<div class="titlepage">
<div>
<div>
<h2 class="title"><a id="UsingSecondaries"></a>Chapter 3.
Using Secondary Indices
</h2>
</div>
</div>
</div>
<div class="toc">
<p>
<b>Table of Contents</b>
</p>
<dl>
<dt>
<span class="sect1">
<a href="UsingSecondaries.html#opensecondaryindices">
Opening Secondary Key Indices
</a>
</span>
</dt>
<dt>
<span class="sect1">
<a href="openingforeignkeys.html">
<span>More Secondary Key Indices</span>
</a>
</span>
</dt>
<dt>
<span class="sect1">
<a href="indexedcollections.html">
Creating Indexed Collections
</a>
</span>
</dt>
<dt>
<span class="sect1">
<a href="retrievingbyindexkey.html">
Retrieving Items by Index Key
</a>
</span>
</dt>
</dl>
</div>
<p>
In the Basic example, each store has a single <span class="emphasis"><em>primary
key</em></span>. The Index example extends the Basic example to add the use of
<span class="emphasis"><em>secondary keys</em></span>.
</p>
<p>
The complete source of the final version of the example program
is included in the Berkeley DB distribution.
</p>
<div class="sect1" lang="en" xml:lang="en">
<div class="titlepage">
<div>
<div>
<h2 class="title" style="clear: both"><a id="opensecondaryindices"></a>
Opening Secondary Key Indices
</h2>
</div>
</div>
</div>
<p>
<span class="emphasis"><em>Secondary indices</em></span> or <span class="emphasis"><em>secondary databases</em></span> are used
to access a primary database by a key other than the primary key.
Recall that the Supplier Number field is the primary key of the
Supplier database. In this section, the Supplier City field will be
used as a secondary lookup key. Given a city value, we would like
to be able to find the Suppliers in that city. Note that more than
one Supplier may be in the same city.
</p>
<p>
Both primary and secondary databases contain key-value records.
The key of an index record is the secondary key, and its value is
the key of the associated record in the primary database. When lookups by
secondary key are performed, the associated record in the primary
database is transparently retrieved by its primary key and returned
to the caller.
</p>
<p>
Secondary indices are maintained automatically when index key
fields (the City field in this case) are added, modified or removed
in the records of the primary database. However, the application
must implement a
<a class="ulink" href="../../java/com/sleepycat/db/SecondaryKeyCreator.html" target="_top">SecondaryKeyCreator</a>
that extracts the index key from the database record.
</p>
<p>
It is useful to contrast opening an secondary index with opening
a primary database (as described earlier in
<a class="xref" href="opendatabases.html" title="Opening and Closing Databases">
Opening and Closing Databases
</a>.
</p>
<div class="itemizedlist">
<ul type="disc">
<li>
<p>
A primary database may be associated with one or more secondary
indices. A secondary index is always associated with exactly one
primary database.
</p>
</li>
<li>
<p>
For a secondary index, a
<a class="ulink" href="../../java/com/sleepycat/db/SecondaryKeyCreator.html" target="_top">SecondaryKeyCreator</a>
must be implemented by the application to extract the index key
from the record of its associated primary database.
</p>
</li>
<li>
<p>
A primary database is represented by a
<a class="ulink" href="../../java/com/sleepycat/db/Database.html" target="_top">Database</a>
object and a secondary index is represented by a
<a class="ulink" href="../../java/com/sleepycat/db/SecondaryDatabase.html" target="_top">SecondaryDatabase</a>
object. The
<a class="ulink" href="../../java/com/sleepycat/db/SecondaryDatabase.html" target="_top">SecondaryDatabase</a>
class extends the
<a class="ulink" href="../../java/com/sleepycat/db/Database.html" target="_top">Database</a>
class.
</p>
</li>
<li>
<p>
When a
<a class="ulink" href="../../java/com/sleepycat/db/SecondaryDatabase.html" target="_top">SecondaryDatabase</a>
is created it is associated with a primary
<a class="ulink" href="../../java/com/sleepycat/db/Database.html" target="_top">Database</a>
object and a
<span>
<a class="ulink" href="../../java/com/sleepycat/db/SecondaryKeyCreator.html" target="_top">SecondaryKeyCreator</a>.
</span>
</p>
</li>
</ul>
</div>
<p>
The <code class="classname">SampleDatabase</code> class is extended to open the
Supplier-by-City secondary key index.
</p>
<a id="index_java_sampledatabase"></a>
<pre class="programlisting"><strong class="userinput"><code>import com.sleepycat.bind.serial.SerialSerialKeyCreator;
import com.sleepycat.db.SecondaryConfig;
import com.sleepycat.db.SecondaryDatabase;</code></strong>
...
public class SampleDatabase
{
...
<strong class="userinput"><code> private static final String SUPPLIER_CITY_INDEX =
"supplier_city_index";
...
private SecondaryDatabase supplierByCityDb;
...</code></strong>
public SampleDatabase(String homeDirectory)
throws DatabaseException, FileNotFoundException
{
...
<strong class="userinput"><code> SecondaryConfig secConfig = new SecondaryConfig();
secConfig.setTransactional(true);
secConfig.setAllowCreate(true);
secConfig.setType(DatabaseType.BTREE);
secConfig.setSortedDuplicates(true);
secConfig.setKeyCreator(
new SupplierByCityKeyCreator(javaCatalog,
SupplierKey.class,
SupplierData.class,
String.class));
supplierByCityDb = env.openSecondaryDatabase(null,
SUPPLIER_CITY_INDEX,
null,
supplierDb,
secConfig);</code></strong>
...
}
} </pre>
<p>
A
<a class="ulink" href="../../java/com/sleepycat/db/SecondaryConfig.html" target="_top">SecondaryConfig</a>
object is used to configure the secondary database. The
<a class="ulink" href="../../java/com/sleepycat/db/SecondaryConfig.html" target="_top">SecondaryConfig</a>
class extends the
<a class="ulink" href="../../java/com/sleepycat/db/DatabaseConfig.html" target="_top">DatabaseConfig</a>
class, and most steps for configuring a secondary database are the
same as for configuring a primary database. The main difference in
the example above is that the
<code class="methodname">SecondaryConfig.setSortedDuplicates()</code> method is called to
allow duplicate index keys. This is how more than one Supplier may
be in the same City. If this property is not specified, the default is
that the index keys of all records must be unique.
</p>
<p>
For a primary database, duplicate keys are not normally used
since a primary database with duplicate keys may not have any
associated secondary indices. If primary database keys are not
unique, there is no way for a secondary key to reference a specific
record in the primary database.
</p>
<p>
Note that <code class="methodname">setSortedDuplicates()</code> and not
<code class="methodname">setUnsortedDuplicates()</code> was called. Sorted
duplicates are always used for indices rather than unsorted duplicates,
since sorting enables optimized equality joins.
</p>
<p>
Opening a secondary key index requires creating a
<span>
<a class="ulink" href="../../java/com/sleepycat/db/SecondaryKeyCreator.html" target="_top">SecondaryKeyCreator</a>.
</span>
The <code class="classname">SupplierByCityKeyCreator</code> class implements the
<a class="ulink" href="../../java/com/sleepycat/db/SecondaryKeyCreator.html" target="_top">SecondaryKeyCreator</a>
interface and will be defined below.
</p>
<p>
The
<a class="ulink" href="../../java/com/sleepycat/db/SecondaryDatabase.html" target="_top">SecondaryDatabase</a>
object is opened last. If you compare the
<code class="methodname">openSecondaryDatabase()</code> and <code class="methodname">openDatabase()</code> methods you'll
notice only two differences:
</p>
<div class="itemizedlist">
<ul type="disc">
<li>
<p>
<code class="methodname">openSecondaryDatabase()</code> has an extra parameter for
specifying the associated primary database. The primary database is
<code class="literal">supplierDb</code> in this case.
</p>
</li>
<li>
<p>
The last parameter of <code class="methodname">openSecondaryDatabase()</code> is a
<code class="literal">SecondaryConfig</code> instead of a <code class="literal">DatabaseConfig</code>.
</p>
</li>
</ul>
</div>
<p>
How to use the secondary index to access records will be shown
in a later section.
</p>
<p>
The application-defined <code class="classname">SupplierByCityKeyCreator</code> class is
shown below. It was used above to configure the secondary
database.
</p>
<a id="index_supplierbycitykeycreator"></a>
<pre class="programlisting">public class SampleDatabase
{
...
<strong class="userinput"><code> private static class SupplierByCityKeyCreator
extends SerialSerialKeyCreator
{
private SupplierByCityKeyCreator(ClassCatalog catalog,
Class primaryKeyClass,
Class valueClass,
Class indexKeyClass)
{
super(catalog, primaryKeyClass, valueClass, indexKeyClass);
}
public Object createSecondaryKey(Object primaryKeyInput,
Object valueInput)
{
SupplierData supplierData = (SupplierData) valueInput;
return supplierData.getCity();
}
}</code></strong>
...
} </pre>
<p>
In general, a key creator class must implement the
<a class="ulink" href="../../java/com/sleepycat/db/SecondaryKeyCreator.html" target="_top">SecondaryKeyCreator</a>
interface. This interface has methods that operate on the record
data as raw bytes. In practice, it is easiest to use an abstract
base class that performs the conversion of record data to and from
the format defined for the database's key and value. The base class
implements the
<a class="ulink" href="../../java/com/sleepycat/db/SecondaryKeyCreator.html" target="_top">SecondaryKeyCreator</a>
interface and has abstract methods that must be implemented in turn
by the application.
</p>
<p>
In this example the
<a class="ulink" href="../../java/com/sleepycat/bind/serial/SerialSerialKeyCreator.html" target="_top">SerialSerialKeyCreator</a>
base class is used because the database record uses the serial
format for both its key and its value. The abstract methods of this
class have key and value parameters of type
<a class="ulink" href="http://download.oracle.com/javase/1.5.0/docs/api/java/lang/Object.html" target="_top">Object</a>
which are automatically converted to and from the raw record data
by the base class.
</p>
<p>
To perform the conversions properly, the key creator must be
aware of all three formats involved: the key format of the primary
database record, the value format of the primary database record,
and the key format of the index record. The
<a class="ulink" href="../../java/com/sleepycat/bind/serial/SerialSerialKeyCreator.html" target="_top">SerialSerialKeyCreator</a>
constructor is given the base classes for these three formats as
parameters.
</p>
<p>
The <code class="methodname">SerialSerialKeyCreator.createSecondaryKey</code> method is
given the key and value of the primary database record as
parameters, and it returns the key of the index record. In this
example, the index key is a field in the primary database record
value. Since the record value is known to be a <code class="classname">SupplierData</code>
object, it is cast to that class and the city field is
returned.
</p>
<p>
Note that the <code class="literal">primaryKeyInput</code> parameter is not used in
the example. This parameter is needed only when an index key is
derived from the key of the primary database record. Normally an
index key is derived only from the primary database record value,
but it may be derived from the key, value or both.
</p>
<p>
The following getter methods return the secondary database
object for use by other classes in the example program. The
secondary database object is used to create Java collections for
accessing records via their secondary keys.
</p>
<a id="index_getsupplierbycitydatabase"></a>
<pre class="programlisting">public class SampleDatabase
{
...
<strong class="userinput"><code> public final SecondaryDatabase getSupplierByCityDatabase()
{
return supplierByCityDb;
}</code></strong>
...
} </pre>
<p>
The following statement closes the secondary database.
</p>
<a id="index_close"></a>
<pre class="programlisting">public class SampleDatabase
{
...
public void close()
throws DatabaseException {
<strong class="userinput"><code> supplierByCityDb.close();</code></strong>
partDb.close();
supplierDb.close();
shipmentDb.close();
javaCatalog.close();
env.close();
}
...
} </pre>
<p>
Secondary databases must be closed before closing their
associated primary database.
</p>
</div>
</div>
<div class="navfooter">
<hr />
<table width="100%" summary="Navigation footer">
<tr>
<td width="40%" align="left"><a accesskey="p" href="handlingexceptions.html">Prev</a> </td>
<td width="20%" align="center"> </td>
<td width="40%" align="right"> <a accesskey="n" href="openingforeignkeys.html">Next</a></td>
</tr>
<tr>
<td width="40%" align="left" valign="top">
Handling Exceptions
</td>
<td width="20%" align="center">
<a accesskey="h" href="index.html">Home</a>
</td>
<td width="40%" align="right" valign="top">
<span>More Secondary Key Indices</span>
</td>
</tr>
</table>
</div>
</body>
</html>
|
