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