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>Database Joins</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="Getting Started with Berkeley DB" /> 10 <link rel="up" href="indexes.html" title="Chapter 5. Secondary Databases" /> 11 <link rel="prev" href="secondaryCursor.html" title="Using Cursors with Secondary Databases" /> 12 <link rel="next" href="coreindexusage.html" title="Secondary Database Example" /> 13 </head> 14 <body> 15 <div class="navheader"> 16 <table width="100%" summary="Navigation header"> 17 <tr> 18 <th colspan="3" align="center">Database Joins</th> 19 </tr> 20 <tr> 21 <td width="20%" align="left"><a accesskey="p" href="secondaryCursor.html">Prev</a> </td> 22 <th width="60%" align="center">Chapter 5. Secondary Databases</th> 23 <td width="20%" align="right"> <a accesskey="n" href="coreindexusage.html">Next</a></td> 24 </tr> 25 </table> 26 <hr /> 27 </div> 28 <div class="sect1" lang="en" xml:lang="en"> 29 <div class="titlepage"> 30 <div> 31 <div> 32 <h2 class="title" style="clear: both"><a id="joins"></a>Database Joins</h2> 33 </div> 34 </div> 35 </div> 36 <div class="toc"> 37 <dl> 38 <dt> 39 <span class="sect2"> 40 <a href="joins.html#joinUsage">Using Join Cursors</a> 41 </span> 42 </dt> 43 </dl> 44 </div> 45 <p> 46 If you have two or more secondary databases associated with a primary 47 database, then you can retrieve primary records based on the intersection of 48 multiple secondary entries. You do this using a 49 50 <span>join cursor.</span> 51 </p> 52 <p> 53 Throughout this document we have presented a 54 55 <span>structure</span> 56 that stores 57 58 information on grocery 59 60 <span>vendors.</span> 61 That 62 63 <span>structure</span> 64 is fairly simple with a limited 65 number of data members, few of which would be interesting from a query 66 perspective. But suppose, instead, that we were storing 67 information on something with many more characteristics that can be queried, such 68 as an automobile. In that case, you may be storing information such as 69 color, number of doors, fuel mileage, automobile type, number of 70 passengers, make, model, and year, to name just a few. 71 </p> 72 <p> 73 In this case, you would still likely be using some unique value to key your 74 primary entries (in the United States, the automobile's VIN would be 75 ideal for this purpose). You would then create a 76 77 <span>structure</span> 78 that identifies 79 all the characteristics of the automobiles in your inventory. 80 81 82 </p> 83 <p> 84 To query this data, you might then create multiple secondary databases, 85 one for each of the characteristics that you want to query. For 86 example, you might create a secondary for color, another for number of 87 doors, another for number of passengers, and so forth. Of course, you 88 will need a unique 89 90 <span>key extractor function</span> 91 for each such secondary database. You do 92 all of this using the concepts and techniques described throughout this 93 chapter. 94 </p> 95 <p> 96 Once you have created this primary database and all interesting 97 secondaries, what you have is the ability to retrieve automobile records 98 based on a single characteristic. You can, for example, find all the 99 automobiles that are red. Or you can find all the automobiles that have 100 four doors. Or all the automobiles that are minivans. 101 </p> 102 <p> 103 The next most natural step, then, is to form compound queries, or joins. 104 For example, you might want to find all the automobiles that are red, 105 and that were built by Toyota, and that are minivans. You can do this 106 using a 107 108 <span>join cursor.</span> 109 </p> 110 <div class="sect2" lang="en" xml:lang="en"> 111 <div class="titlepage"> 112 <div> 113 <div> 114 <h3 class="title"><a id="joinUsage"></a>Using Join Cursors</h3> 115 </div> 116 </div> 117 </div> 118 <p> 119 To use a join cursor: 120 </p> 121 <div class="itemizedlist"> 122 <ul type="disc"> 123 <li> 124 <p> 125 Open two or more 126 127 cursors 128 129 <span>for</span> 130 secondary databases that are associated with 131 the same primary database. 132 </p> 133 </li> 134 <li> 135 <p> 136 Position each such cursor to the secondary key 137 value in which you are interested. For example, to build on 138 the previous description, the cursor for the color 139 database is positioned to the <code class="literal">red</code> records 140 while the cursor for the model database is positioned to the 141 <code class="literal">minivan</code> records, and the cursor for the 142 make database is positioned to <code class="literal">Toyota</code>. 143 </p> 144 </li> 145 <li> 146 <p> 147 148 Create an array of cursors, and 149 place in it each of the cursors that are participating in your join query. 150 <span>Note that this array must be null terminated.</span> 151 152 </p> 153 </li> 154 <li> 155 <p> 156 157 Obtain a join cursor. You do this using the 158 159 <code class="methodname">DB->join()</code> 160 161 method. You must pass this method the array of secondary cursors that you 162 opened and positioned in the previous steps. 163 </p> 164 </li> 165 <li> 166 <p> 167 Iterate over the set of matching records 168 169 until 170 171 <span>the return code is not <code class="literal">0</code>.</span> 172 </p> 173 </li> 174 <li> 175 <p> 176 Close your cursor. 177 </p> 178 </li> 179 <li> 180 <p> 181 If you are done with them, close all your cursors. 182 </p> 183 </li> 184 </ul> 185 </div> 186 <p> 187 For example: 188 </p> 189 <a id="c_index9"></a> 190 <pre class="programlisting">#include <db.h> 191#include <string.h> 192 193... 194 195DB *automotiveDB; 196DB *automotiveColorDB; 197DB *automotiveMakeDB; 198DB *automotiveTypeDB; 199DBC *color_curs, *make_curs, *type_curs, *join_curs; 200DBC *carray[4]; 201DBT key, data; 202int ret; 203 204char *the_color = "red"; 205char *the_type = "minivan"; 206char *the_make = "Toyota"; 207 208/* Database and secondary database opens omitted for brevity. 209 * Assume a primary database handle: 210 * automotiveDB 211 * Assume 3 secondary database handles: 212 * automotiveColorDB -- secondary database based on automobile color 213 * automotiveMakeDB -- secondary database based on the manufacturer 214 * automotiveTypeDB -- secondary database based on automobile type 215 */ 216 217/* initialize pointers and structures */ 218color_curs = NULL; 219make_curs = NULL; 220type_curs = NULL; 221join_curs = NULL; 222 223memset(&key, 0, sizeof(DBT)); 224memset(&data, 0, sizeof(DBT)); 225 226/* open the cursors */ 227if (( ret = 228 automotiveColorDB->cursor(automotiveColorDB, NULL, 229 &color_curs, 0)) != 0) { 230 /* Error handling goes here */ 231} 232 233if (( ret = 234 automotiveMakeDB->cursor(automotiveMakeDB, NULL, 235 &make_curs, 0)) != 0) { 236 /* Error handling goes here */ 237} 238 239if (( ret = 240 automotiveTypeDB->cursor(automotiveTypeDB, NULL, 241 &type_curs, 0)) != 0) { 242 /* Error handling goes here */ 243} 244 245/* Position the cursors */ 246key.data = the_color; 247key.size = strlen(the_color) + 1; 248if ((ret = color_curs->get(color_curs, &key, &data, DB_SET)) != 0) 249 /* Error handling goes here */ 250 251key.data = the_make; 252key.size = strlen(the_make) + 1; 253if ((ret = make_curs->get(make_curs, &key, &data, DB_SET)) != 0) 254 /* Error handling goes here */ 255 256key.data = the_type; 257key.size = strlen(the_type) + 1; 258if ((ret = type_curs->get(type_curs, &key, &data, DB_SET)) != 0) 259 /* Error handling goes here */ 260 261/* Set up the cursor array */ 262carray[0] = color_curs; 263carray[1] = make_curs; 264carray[2] = type_curs; 265carray[3] = NULL; 266 267/* Create the join */ 268if ((ret = automotiveDB->join(automotiveDB, carray, &join_curs, 0)) != 0) 269 /* Error handling goes here */ 270 271/* Iterate using the join cursor */ 272while ((ret = join_curs->get(join_curs, &key, &data, 0)) == 0) { 273 /* Do interesting things with the key and data */ 274} 275 276/* 277 * If we exited the loop because we ran out of records, 278 * then it has completed successfully. 279 */ 280if (ret == DB_NOTFOUND) { 281 /* 282 * Close all our cursors and databases as is appropriate, and 283 * then exit with a normal exit status (0). 284 */ 285} </pre> 286 </div> 287 </div> 288 <div class="navfooter"> 289 <hr /> 290 <table width="100%" summary="Navigation footer"> 291 <tr> 292 <td width="40%" align="left"><a accesskey="p" href="secondaryCursor.html">Prev</a> </td> 293 <td width="20%" align="center"> 294 <a accesskey="u" href="indexes.html">Up</a> 295 </td> 296 <td width="40%" align="right"> <a accesskey="n" href="coreindexusage.html">Next</a></td> 297 </tr> 298 <tr> 299 <td width="40%" align="left" valign="top"> 300 301 <span>Using Cursors with Secondary Databases</span> 302 </td> 303 <td width="20%" align="center"> 304 <a accesskey="h" href="index.html">Home</a> 305 </td> 306 <td width="40%" align="right" valign="top"> Secondary Database Example</td> 307 </tr> 308 </table> 309 </div> 310 </body> 311</html> 312