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 153 <tt class="methodname">Db::join()</tt> 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="cxx_index9"></a> 183 <pre class="programlisting">#include <db_cxx.h> 184#include <string.h> 185 186... 187 188// Exception handling omitted 189 190int ret; 191 192Db automotiveDB(NULL, 0); 193Db automotiveColorDB(NULL, 0); 194Db automotiveMakeDB(NULL, 0); 195Db automotiveTypeDB(NULL, 0); 196 197// Database and secondary database opens omitted for brevity. 198// Assume a primary database: 199// automotiveDB 200// Assume 3 secondary databases: 201// automotiveColorDB -- secondary database based on automobile color 202// automotiveMakeDB -- secondary database based on the manufacturer 203// automotiveTypeDB -- secondary database based on automobile type 204 205// Position the cursors 206Dbc *color_curs; 207automotiveColorDB.cursor(NULL, &color_curs, 0); 208char *the_color = "red"; 209Dbt key(the_color, strlen(the_color) + 1); 210Dbt data; 211if ((ret = color_curs->get(&key, &data, DB_SET)) != 0) { 212 // Error handling goes here 213} 214 215Dbc *make_curs; 216automotiveMakeDB.cursor(NULL, &make_curs, 0); 217char *the_make = "Toyota"; 218key.set_data(the_make); 219key.set_size(strlen(the_make) + 1); 220if ((ret = make_curs->get(&key, &data, DB_SET)) != 0) { 221 // Error handling goes here 222} 223 224Dbc *type_curs; 225automotiveTypeDB.cursor(NULL, &type_curs, 0); 226char *the_type = "minivan"; 227key.set_data(the_type); 228key.set_size(strlen(the_type) + 1); 229if ((ret = type_curs->get(&key, &data, DB_SET)) != 0) { 230 // Error handling goes here 231} 232 233// Set up the cursor array 234Dbc *carray[4]; 235carray[0] = color_curs; 236carray[1] = make_curs; 237carray[2] = type_curs; 238carray[3] = NULL; 239 240// Create the join 241Dbc *join_curs; 242if ((ret = automotiveDB.join(carray, &join_curs, 0)) != 0) { 243 // Error handling goes here 244} 245 246// Iterate using the join cursor 247while ((ret = join_curs->get(&key, &data, 0)) == 0) { 248 // Do interesting things with the key and data 249} 250 251// If we exited the loop because we ran out of records, 252// then it has completed successfully. 253if (ret == DB_NOTFOUND) { 254 // Close all our cursors and databases as is appropriate, and 255 // then exit with a normal exit status (0). 256} </pre> 257 </div> 258 </div> 259 <div class="navfooter"> 260 <hr /> 261 <table width="100%" summary="Navigation footer"> 262 <tr> 263 <td width="40%" align="left"><a accesskey="p" href="secondaryCursor.html">Prev</a> </td> 264 <td width="20%" align="center"> 265 <a accesskey="u" href="indexes.html">Up</a> 266 </td> 267 <td width="40%" align="right"> <a accesskey="n" href="coreindexusage.html">Next</a></td> 268 </tr> 269 <tr> 270 <td width="40%" align="left" valign="top"> 271 272 Using Cursors with Secondary Databases 273 </td> 274 <td width="20%" align="center"> 275 <a accesskey="h" href="index.html">Home</a> 276 </td> 277 <td width="40%" align="right" valign="top"> Secondary Database Example</td> 278 </tr> 279 </table> 280 </div> 281 </body> 282</html> 283