1<?xml version="1.0" encoding="UTF-8" standalone="no"?> 2<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> 3<html xmlns="http://www.w3.org/1999/xhtml"> 4 <head> 5 <meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /> 6 <title>Secondary indexes</title> 7 <link rel="stylesheet" href="gettingStarted.css" type="text/css" /> 8 <meta name="generator" content="DocBook XSL Stylesheets V1.73.2" /> 9 <link rel="start" href="index.html" title="Berkeley DB Programmer's Reference Guide" /> 10 <link rel="up" href="am.html" title="Chapter 3. Access Method Operations" /> 11 <link rel="prev" href="am_close.html" title="Database close" /> 12 <link rel="next" href="am_foreign.html" title="Foreign key indexes" /> 13 </head> 14 <body> 15 <div class="navheader"> 16 <table width="100%" summary="Navigation header"> 17 <tr> 18 <th colspan="3" align="center">Secondary indexes</th> 19 </tr> 20 <tr> 21 <td width="20%" align="left"><a accesskey="p" href="am_close.html">Prev</a> </td> 22 <th width="60%" align="center">Chapter 3. 23 Access Method Operations 24 </th> 25 <td width="20%" align="right"> <a accesskey="n" href="am_foreign.html">Next</a></td> 26 </tr> 27 </table> 28 <hr /> 29 </div> 30 <div class="sect1" lang="en" xml:lang="en"> 31 <div class="titlepage"> 32 <div> 33 <div> 34 <h2 class="title" style="clear: both"><a id="am_second"></a>Secondary indexes</h2> 35 </div> 36 </div> 37 </div> 38 <p>A secondary index, put simply, is a way to efficiently access records 39in a database (the primary) by means of some piece of information other 40than the usual (primary) key. In Berkeley DB, this index is simply another 41database whose keys are these pieces of information (the secondary 42keys), and whose data are the primary keys. Secondary indexes can be 43created manually by the application; there is no disadvantage, other 44than complexity, to doing so. However, when the secondary key can be 45mechanically derived from the primary key and datum that it points to, 46as is frequently the case, Berkeley DB can automatically and transparently 47manage secondary indexes.</p> 48 <p>As an example of how secondary indexes might be used, consider a 49database containing a list of students at a college, each of whom has 50a unique student ID number. A typical database would use the student 51ID number as the key; however, one might also reasonably want to be 52able to look up students by last name. To do this, one would construct 53a secondary index in which the secondary key was this last name.</p> 54 <p>In SQL, this would be done by executing something like the following:</p> 55 <pre class="programlisting">CREATE TABLE students(student_id CHAR(4) NOT NULL, 56 lastname CHAR(15), firstname CHAR(15), PRIMARY KEY(student_id)); 57CREATE INDEX lname ON students(lastname);</pre> 58 <p>In Berkeley DB, this would work as follows (a 59<a class="ulink" href="second.javas" target="_top">Java API example is also available</a>):</p> 60 <pre class="programlisting">struct student_record { 61 char student_id[4]; 62 char last_name[15]; 63 char first_name[15]; 64}; 65 66void 67second() 68{ 69 DB *dbp, *sdbp; 70 int ret; 71 72 /* Open/create primary */ 73 if ((ret = db_create(&dbp, dbenv, 0)) != 0) 74 handle_error(ret); 75 if ((ret = dbp->open(dbp, NULL, 76 "students.db", NULL, DB_BTREE, DB_CREATE, 0600)) != 0) 77 handle_error(ret); 78 79 /* 80 * Open/create secondary. Note that it supports duplicate data 81 * items, since last names might not be unique. 82 */ 83 if ((ret = db_create(&sdbp, dbenv, 0)) != 0) 84 handle_error(ret); 85 if ((ret = sdbp->set_flags(sdbp, DB_DUP | DB_DUPSORT)) != 0) 86 handle_error(ret); 87 if ((ret = sdbp->open(sdbp, NULL, 88 "lastname.db", NULL, DB_BTREE, DB_CREATE, 0600)) != 0) 89 handle_error(ret); 90 91 /* Associate the secondary with the primary. */ 92 if ((ret = dbp->associate(dbp, NULL, sdbp, getname, 0)) != 0) 93 handle_error(ret); 94} 95 96/* 97 * getname -- extracts a secondary key (the last name) from a primary 98 * key/data pair 99 */ 100int 101getname(secondary, pkey, pdata, skey) 102 DB *secondary; 103 const DBT *pkey, *pdata; 104 DBT *skey; 105{ 106 /* 107 * Since the secondary key is a simple structure member of the 108 * record, we don't have to do anything fancy to return it. If 109 * we have composite keys that need to be constructed from the 110 * record, rather than simply pointing into it, then the user's 111 * function might need to allocate space and copy data. In 112 * this case, the DB_DBT_APPMALLOC flag should be set in the 113 * secondary key DBT. 114 */ 115 memset(skey, 0, sizeof(DBT)); 116 skey->data = ((struct student_record *)pdata->data)->last_name; 117 skey->size = sizeof((struct student_record *)pdata->data)->last_name; 118 return (0); 119}</pre> 120 <p>From the application's perspective, putting things into the database 121works exactly as it does without a secondary index; one can simply 122insert records into the primary database. In SQL one would do the 123following:</p> 124 <pre class="programlisting">INSERT INTO student 125 VALUES ("WC42", "Churchill ", "Winston ");</pre> 126 <p>and in Berkeley DB, one does:</p> 127 <pre class="programlisting">struct student_record s; 128DBT data, key; 129 130memset(&key, 0, sizeof(DBT)); 131memset(&data, 0, sizeof(DBT)); 132memset(&s, 0, sizeof(struct student_record)); 133key.data = "WC42"; 134key.size = 4; 135memcpy(&s.student_id, "WC42", sizeof(s.student_id)); 136memcpy(&s.last_name, "Churchill ", sizeof(s.last_name)); 137memcpy(&s.first_name, "Winston ", sizeof(s.first_name)); 138data.data = &s; 139data.size = sizeof(s); 140if ((ret = dbp->put(dbp, txn, &key, &data, 0)) != 0) 141 handle_error(ret);</pre> 142 <p>Internally, a record with secondary key "Churchill" is inserted into 143the secondary database (in addition to the insertion of "WC42" into the 144primary, of course).</p> 145 <p>Deletes are similar. The SQL clause:</p> 146 <pre class="programlisting">DELETE FROM student WHERE (student_id = "WC42");</pre> 147 <p>looks like:</p> 148 <pre class="programlisting">DBT key; 149 150memset(&key, 0, sizeof(DBT)); 151key.data = "WC42"; 152key.size = 4; 153if ((ret = dbp->del(dbp, txn, &key, 0)) != 0) 154 handle_error(ret);</pre> 155 <p>Deletes can also be performed on the secondary index directly; a delete 156done this way will delete the "real" record in the primary as well. If 157the secondary supports duplicates and there are duplicate occurrences of 158the secondary key, then all records with that secondary key are removed 159from both the secondary index and the primary database. In 160SQL:</p> 161 <pre class="programlisting">DELETE FROM lname WHERE (lastname = "Churchill ");</pre> 162 <p>In Berkeley DB:</p> 163 <pre class="programlisting">DBT skey; 164 165memset(&skey, 0, sizeof(DBT)); 166skey.data = "Churchill "; 167skey.size = 15; 168if ((ret = sdbp->del(sdbp, txn, &skey, 0)) != 0) 169 handle_error(ret);</pre> 170 <p>Gets on a secondary automatically return the primary datum. If 171<a href="../api_reference/C/dbget.html" class="olink">DB->pget()</a> or <a href="../api_reference/C/dbcget.html" class="olink">DBC->pget()</a> is used in lieu of <a href="../api_reference/C/dbget.html" class="olink">DB->get()</a> or <a href="../api_reference/C/dbcget.html" class="olink">DBC->get()</a>, the primary key is returned as well. Thus, the 172equivalent of:</p> 173 <pre class="programlisting">SELECT * from lname WHERE (lastname = "Churchill ");</pre> 174 <p>would be:</p> 175 <pre class="programlisting">DBT data, pkey, skey; 176<para /> 177memset(&skey, 0, sizeof(DBT)); 178memset(&pkey, 0, sizeof(DBT)); 179memset(&data, 0, sizeof(DBT)); 180skey.data = "Churchill "; 181skey.size = 15; 182if ((ret = sdbp->pget(sdbp, txn, &skey, &pkey, &data, 0)) != 0) 183 handle_error(ret); 184/* 185 * Now pkey contains "WC42" and data contains Winston's record. 186 */</pre> 187 <p>To create a secondary index to a Berkeley DB database, open the database that 188is to become a secondary index normally, then pass it as the "secondary" 189argument to the <a href="../api_reference/C/dbassociate.html" class="olink">DB->associate()</a> method for some primary database.</p> 190 <p>After a <a href="../api_reference/C/dbassociate.html" class="olink">DB->associate()</a> call is made, the secondary indexes become 191alternate interfaces to the primary database. All updates to the 192primary will be automatically reflected in each secondary index that has 193been associated with it. All get operations using the <a href="../api_reference/C/dbget.html" class="olink">DB->get()</a> 194or <a href="../api_reference/C/dbcget.html" class="olink">DBC->get()</a> methods on the secondary index return the primary datum 195associated with the specified (or otherwise current, in the case of 196cursor operations) secondary key. The <a href="../api_reference/C/dbget.html" class="olink">DB->pget()</a> and 197<a href="../api_reference/C/dbcget.html" class="olink">DBC->pget()</a> methods also become usable; these behave just like 198<a href="../api_reference/C/dbget.html" class="olink">DB->get()</a> and <a href="../api_reference/C/dbcget.html" class="olink">DBC->get()</a>, but return the primary key in 199addition to the primary datum, for those applications that need it as 200well.</p> 201 <p>Cursor get operations on a secondary index perform as expected; although 202the data returned will by default be those of the primary database, a 203position in the secondary index is maintained normally, and records will 204appear in the order determined by the secondary key and the comparison 205function or other structure of the secondary database.</p> 206 <p>Delete operations on a secondary index delete the item from the primary 207database and all relevant secondaries, including the current one.</p> 208 <p>Put operations of any kind are forbidden on secondary indexes, as there 209is no way to specify a primary key for a newly put item. Instead, the 210application should use the <a href="../api_reference/C/dbput.html" class="olink">DB->put()</a> or <a href="../api_reference/C/dbcput.html" class="olink">DBC->put()</a> methods 211on the primary database.</p> 212 <p>Any number of secondary indexes may be associated with a given primary 213database, up to limitations on available memory and the number of open 214file descriptors.</p> 215 <p>Note that although Berkeley DB guarantees that updates made using any 216<a href="../api_reference/C/db.html" class="olink">DB</a> handle with an associated secondary will be reflected in the 217that secondary, associating each primary handle with all the appropriate 218secondaries is the responsibility of the application and is not enforced 219by Berkeley DB. It is generally unsafe, but not forbidden by Berkeley DB, to modify 220a database that has secondary indexes without having those indexes open 221and associated. Similarly, it is generally unsafe, but not forbidden, 222to modify a secondary index directly. Applications that violate these 223rules face the possibility of outdated or incorrect results if the 224secondary indexes are later used.</p> 225 <p>If a secondary index becomes outdated for any reason, it should be 226discarded using the <a href="../api_reference/C/dbremove.html" class="olink">DB->remove()</a> method and a new one created 227using the <a href="../api_reference/C/dbassociate.html" class="olink">DB->associate()</a> method. If a secondary index is no 228longer needed, all of its handles should be closed using the 229<a href="../api_reference/C/dbclose.html" class="olink">DB->close()</a> method, and then the database should be removed using 230a new database handle and the <a href="../api_reference/C/dbremove.html" class="olink">DB->remove()</a> method.</p> 231 <p>Closing a primary database handle automatically dis-associates all 232secondary database handles associated with it.</p> 233 </div> 234 <div class="navfooter"> 235 <hr /> 236 <table width="100%" summary="Navigation footer"> 237 <tr> 238 <td width="40%" align="left"><a accesskey="p" href="am_close.html">Prev</a> </td> 239 <td width="20%" align="center"> 240 <a accesskey="u" href="am.html">Up</a> 241 </td> 242 <td width="40%" align="right"> <a accesskey="n" href="am_foreign.html">Next</a></td> 243 </tr> 244 <tr> 245 <td width="40%" align="left" valign="top">Database close </td> 246 <td width="20%" align="center"> 247 <a accesskey="h" href="index.html">Home</a> 248 </td> 249 <td width="40%" align="right" valign="top"> Foreign key indexes</td> 250 </tr> 251 </table> 252 </div> 253 </body> 254</html> 255