-
Notifications
You must be signed in to change notification settings - Fork 5
/
Copy pathschema.html
513 lines (513 loc) · 61.3 KB
/
schema.html
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
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
<!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/xhtml;charset=UTF-8"/>
<meta http-equiv="X-UA-Compatible" content="IE=9"/>
<title>WiredTiger: Schemas</title>
<link href="tabs.css" rel="stylesheet" type="text/css"/>
<script type="text/javascript" src="jquery.js"></script>
<script type="text/javascript" src="dynsections.js"></script>
<link href="navtree.css" rel="stylesheet" type="text/css"/>
<script type="text/javascript" src="resize.js"></script>
<script type="text/javascript" src="navtree.js"></script>
<script type="text/javascript">
$(document).ready(initResizable);
</script>
<link href="doxygen.css" rel="stylesheet" type="text/css" />
<link href="wiredtiger.css" rel="stylesheet" type="text/css"/>
</head>
<body>
<div id="top"><!-- do not remove this div, it is closed by doxygen! -->
<div id="titlearea">
<table cellspacing="0" cellpadding="0">
<tbody>
<tr style="height: 56px;">
<td id="projectlogo"><a href="http://wiredtiger.com/"><img alt="Logo" src="LogoFinal-header.png" alt="WiredTiger" /></a></td>
<td style="padding-left: 0.5em;">
<div id="projectname">
 <span id="projectnumber">Version 1.4.2</span>
</div>
</td>
</tr>
</tbody>
</table>
</div>
<div class="banner">
<a href="https://github.com/wiredtiger/wiredtiger">Fork me on GitHub</a>
<a class="last" href="http://groups.google.com/group/wiredtiger-users">Join my user group</a>
</div>
<!-- end header part -->
<!-- Generated by Doxygen 1.8.2 -->
<div id="navrow1" class="tabs">
<ul class="tablist">
<li><a href="index.html"><span>Main Page</span></a></li>
<li class="current"><a href="pages.html"><span>Related Pages</span></a></li>
<li><a href="modules.html"><span>Modules</span></a></li>
<li><a href="examples.html"><span>Examples</span></a></li>
<li><a href="license.html"><span>License</span></a></li>
</ul>
</div>
</div><!-- top -->
<div id="side-nav" class="ui-resizable side-nav-resizable">
<div id="nav-tree">
<div id="nav-tree-contents">
<div id="nav-sync" class="sync"></div>
</div>
</div>
<div id="splitbar" style="-moz-user-select:none;"
class="ui-resizable-handle">
</div>
</div>
<script type="text/javascript">
$(document).ready(function(){initNavTree('schema.html','');});
</script>
<div id="doc-content">
<div class="header">
<div class="headertitle">
<div class="title">Schemas </div> </div>
</div><!--header-->
<div class="contents">
<div class="textblock"><p>While many tables have simple key/value pairs for records, WiredTiger also supports more complex data patterns.</p>
<h1><a class="anchor" id="schema_intro"></a>
Tables, rows and columns</h1>
<p>A table is a logical representation of data consisting of cells in rows and columns. For example, a database might have a simple table including an employee identifier, last name and first name, and a salary:</p>
<table class="doxtable">
<tr>
<th>Employee ID</th><th>Last Name</th><th>First Name</th><th>Salary </th></tr>
<tr>
<td>1</td><td>Smith</td><td>Joe</td><td>40000 </td></tr>
<tr>
<td>2</td><td>Jones</td><td>Mary</td><td>50000 </td></tr>
<tr>
<td>3</td><td>Johnson</td><td>Cathy</td><td>44000 </td></tr>
</table>
<p>A row-oriented database would store all of the values for the first employee in the first row, then the next employee's values in the next row, and so on:</p>
<pre>
1,Smith,Joe,40000
2,Jones,Mary,50000
3,Johnson,Cathy,44000
</pre><p>A column-oriented database would store all of the values of a column together, then the values of the next column, and so on:</p>
<pre>
1,2,3
Smith,Jones,Johnson
Joe,Mary,Cathy
40000,50000,44000
</pre><p>WiredTiger supports both storage formats, and can mix and match the storage of columns within a logical table.</p>
<p>A <em>table</em> in WiredTiger consist of one or more <em>"column groups"</em> that together hold all of the columns in primary key order; and zero or more <em>indices</em> that enable fast lookup of records by columns in orders other than the primary key.</p>
<p>Applications describe the format of their data by supplying a schema to <a class="el" href="struct_w_t___s_e_s_s_i_o_n.html#a358ca4141d59c345f401c58501276bbb" title="Create a table, column group, index or file.">WT_SESSION::create</a>. This specifies how the application's data can be split into fields and mapped onto rows and columns.</p>
<h1><a class="anchor" id="schema_column_types"></a>
Column types</h1>
<p>By default, WiredTiger works as a traditional key/value store, where the keys and values are raw byte arrays accessed using a <a class="el" href="group__wt.html#struct_w_t___i_t_e_m" title="A raw item of data to be managed.">WT_ITEM</a> structure. Key and value types may also be chosen from a list, or composed of multiple columns with any combination of types. Keys and values may be up to (<code>4GB - 512B</code>) bytes in size.</p>
<p>See <a class="el" href="keyvalue.html">Key/Value pairs</a> for more details on raw key / value items.</p>
<h1><a class="anchor" id="schema_format_types"></a>
Format types</h1>
<p>WiredTiger's uses format strings similar to those specified in the Python struct module to describe the types of columns in a table: <a href="http://docs.python.org/library/struct">http://docs.python.org/library/struct</a></p>
<table class="doxtable">
<tr>
<th>Format</th><th>C Type</th><th>Java type</th><th>Python type</th><th>Notes </th></tr>
<tr>
<td><code>x</code> </td><td>N/A</td><td>N/A</td><td>N/A</td><td>pad byte, no associated value </td></tr>
<tr>
<td><code>b</code> </td><td><code>int8_t</code> </td><td><code>byte</code> </td><td><code>int</code> </td><td>signed byte </td></tr>
<tr>
<td><code>B</code> </td><td><code>uint8_t</code> </td><td><code>byte</code> </td><td><code>int</code> </td><td>unsigned byte </td></tr>
<tr>
<td><code>h</code> </td><td><code>int16_t</code> </td><td><code>short</code> </td><td><code>int</code> </td><td>signed 16-bit </td></tr>
<tr>
<td><code>H</code> </td><td><code>uint16_t</code> </td><td><code>short</code> </td><td><code>int</code> </td><td>unsigned 16-bit </td></tr>
<tr>
<td><code>i</code> </td><td><code>int32_t</code> </td><td><code>int</code> </td><td><code>int</code> </td><td>signed 32-bit </td></tr>
<tr>
<td><code>I</code> </td><td><code>uint32_t</code> </td><td><code>int</code> </td><td><code>int</code> </td><td>unsigned 32-bit </td></tr>
<tr>
<td><code>l</code> </td><td><code>int32_t</code> </td><td><code>int</code> </td><td><code>int</code> </td><td>signed 32-bit </td></tr>
<tr>
<td><code>L</code> </td><td><code>uint32_t</code> </td><td><code>int</code> </td><td><code>int</code> </td><td>unsigned 32-bit </td></tr>
<tr>
<td><code>q</code> </td><td><code>int64_t</code> </td><td><code>long</code> </td><td><code>int</code> </td><td>signed 64-bit </td></tr>
<tr>
<td><code>Q</code> </td><td><code>uint64_t</code> </td><td><code>long</code> </td><td><code>int</code> </td><td>unsigned 64-bit </td></tr>
<tr>
<td><code>r</code> </td><td><code>uint64_t</code> </td><td><code>long</code> </td><td><code>int</code> </td><td>record number </td></tr>
<tr>
<td><code>s</code> </td><td><code>char</code>[]</td><td><code>String</code> </td><td><code>str</code> </td><td>fixed-length string </td></tr>
<tr>
<td><code>S</code> </td><td><code>char</code>[]</td><td><code>String</code> </td><td><code>str</code> </td><td>NUL-terminated string </td></tr>
<tr>
<td><code>t</code> </td><td><code>uint8_t</code> </td><td><code>byte</code> </td><td><code>int</code> </td><td>fixed-length bit field </td></tr>
<tr>
<td><code>u</code> </td><td><code><a class="el" href="group__wt.html#struct_w_t___i_t_e_m" title="A raw item of data to be managed.">WT_ITEM</a> *</code></td><td><code>byte[]</code></td><td><code>str</code> </td><td>raw byte array </td></tr>
</table>
<p>The <code>'r'</code> type is used for record number keys in column stores. It is otherwise identical to the <code>'Q'</code> type.</p>
<p>The <code>'S'</code> type is encoded as a C language string terminated by a NUL character.</p>
<p>The <code>'t'</code> type is used for fixed-length bit field values. If it is preceded by a size, that indicates the number of bits to store, between 1 and 8. That number of low-order bits will be stored in the table. The default is a size of 1 bit: that is, a boolean. C applications must always use a <code>uint8_t</code> type (or equivalently, <code>unsigned char</code>) for calls to <a class="el" href="struct_w_t___c_u_r_s_o_r.html#a27f7cbd0cd3e561f6a145704813ad64c" title="Set the value for the next operation.">WT_CURSOR::set_value</a>, and a pointer to the same for calls to <a class="el" href="struct_w_t___c_u_r_s_o_r.html#af85364a5af50b95bbc46c82e72f75c01" title="Get the value for the current record.">WT_CURSOR::get_value</a>. If a bit field value is combined with other types in a packing format, it is equivalent to <code>'B'</code>, and a full byte is used to store it.</p>
<p>When referenced by a record number (that is, a key format of <code>'r'</code>), the <code>'t'</code> type will be stored in a fixed-length column-store, and will not have an out-of-band value to indicate the record does not exist. In this case, a 0 byte value is used to indicate the record does not exist. This means removing a record with <a class="el" href="struct_w_t___c_u_r_s_o_r.html#abbba24fe607fee519c4c9c4669cd4455" title="Remove a record.">WT_CURSOR::remove</a> is equivalent to storing a value of 0 in the record with <a class="el" href="struct_w_t___c_u_r_s_o_r.html#a444cdc0952e7f8d55d23173516c7037f" title="Update a record.">WT_CURSOR::update</a> (and storing a value of 0 in the record will cause cursor scans to skip the record). Additionally, creating a record past the end of an object implies the creation of any missing intermediate records, with byte values of 0.</p>
<p>The <code>'u'</code> type is for raw byte arrays: if it appears at the end of a format string (including in the default <code>"u"</code> format for untyped tables), the size is not stored explicitly. When <code>'u'</code> appears within a format string, the size is stored as a 32-bit integer in the same byte order as the rest of the format string, followed by the data.</p>
<p>There is a default collator that gives lexicographic (byte-wise) comparisons, and the default encoding is designed so that lexicographic ordering of encoded keys is usually the expected ordering. For example, the variable-length encoding of integers is designed so that they have the natural integer ordering under the default collator.</p>
<p>See <a class="el" href="packing.html">Packing and Unpacking Data</a> for details of WiredTiger's packing format.</p>
<p>WiredTiger can also be extended with custom collators by implementing the <a class="el" href="struct_w_t___c_o_l_l_a_t_o_r.html" title="The interface implemented by applications to provide custom ordering of records.">WT_COLLATOR</a> interface.</p>
<h1><a class="anchor" id="schema_key_and_value_formats"></a>
Key and value formats</h1>
<p>Every table has a key format and a value format as describe in <a class="el" href="schema.html#schema_column_types">Column types</a>. These types are configured when the table is created by passing <code>key_format</code> and <code>value_format</code> keys to <a class="el" href="struct_w_t___s_e_s_s_i_o_n.html#a358ca4141d59c345f401c58501276bbb" title="Create a table, column group, index or file.">WT_SESSION::create</a>.</p>
<p>For example, a simple row-store table with strings as both keys and values would be created as follows:</p>
<div class="fragment"><div class="line"> ret = session-><a class="code" href="struct_w_t___s_e_s_s_i_o_n.html#a358ca4141d59c345f401c58501276bbb" title="Create a table, column group, index or file.">create</a>(session,</div>
<div class="line"> <span class="stringliteral">"table:mytable"</span>, <span class="stringliteral">"key_format=S,value_format=S"</span>);</div>
</div><!-- fragment --><p> A simple column-store table with strings for values would be created as follows:</p>
<div class="fragment"><div class="line"> ret = session-><a class="code" href="struct_w_t___s_e_s_s_i_o_n.html#a358ca4141d59c345f401c58501276bbb" title="Create a table, column group, index or file.">create</a>(session,</div>
<div class="line"> <span class="stringliteral">"table:mytable"</span>, <span class="stringliteral">"key_format=r,value_format=S"</span>);</div>
</div><!-- fragment --> <h1><a class="anchor" id="schema_cursor_formats"></a>
Cursor formats</h1>
<p>Cursors for a table have the same key format as the table itself. The key columns of a cursor are set with <a class="el" href="struct_w_t___c_u_r_s_o_r.html#ad1088d719df40babc1f57d086691ebdc" title="Set the key for the next operation.">WT_CURSOR::set_key</a> and accessed with <a class="el" href="struct_w_t___c_u_r_s_o_r.html#af19f6f9d9c7fc248ab38879032620b2f" title="Get the key for the current record.">WT_CURSOR::get_key</a>. <a class="el" href="struct_w_t___c_u_r_s_o_r.html#ad1088d719df40babc1f57d086691ebdc" title="Set the key for the next operation.">WT_CURSOR::set_key</a> is analogous to <code>printf</code>, and takes a list of values in the order the key columns are configured in <code>key_format</code>.</p>
<p>For example, setting the key for a row-store table with strings as keys would be done as follows:</p>
<div class="fragment"><div class="line"> <span class="comment">/* Set the cursor's string key. */</span></div>
<div class="line"> <span class="keyword">const</span> <span class="keywordtype">char</span> *key = <span class="stringliteral">"another key"</span>;</div>
<div class="line"> cursor-><a class="code" href="struct_w_t___c_u_r_s_o_r.html#ad1088d719df40babc1f57d086691ebdc" title="Set the key for the next operation.">set_key</a>(cursor, key);</div>
</div><!-- fragment --><p> For example, setting the key for a column-store table would be done as follows:</p>
<div class="fragment"><div class="line"> uint64_t recno = 37; <span class="comment">/* Set the cursor's record number key. */</span></div>
<div class="line"> cursor-><a class="code" href="struct_w_t___c_u_r_s_o_r.html#ad1088d719df40babc1f57d086691ebdc" title="Set the key for the next operation.">set_key</a>(cursor, recno);</div>
</div><!-- fragment --><p> A more complex example, setting a composite key for a row-store table where the key_format was <code>"SiS"</code>, would be done as follows:</p>
<div class="fragment"><div class="line"> <span class="comment">/* Set the cursor's "SiH" format composite key. */</span></div>
<div class="line"> cursor-><a class="code" href="struct_w_t___c_u_r_s_o_r.html#ad1088d719df40babc1f57d086691ebdc" title="Set the key for the next operation.">set_key</a>(cursor, <span class="stringliteral">"first"</span>, (int32_t)5, (uint16_t)7);</div>
</div><!-- fragment --><p> The key's values are accessed with <a class="el" href="struct_w_t___c_u_r_s_o_r.html#af19f6f9d9c7fc248ab38879032620b2f" title="Get the key for the current record.">WT_CURSOR::get_key</a>, which is analogous to <code>scanf</code>, and takes a list of pointers to values in the same order:</p>
<div class="fragment"><div class="line"> <span class="keyword">const</span> <span class="keywordtype">char</span> *key; <span class="comment">/* Get the cursor's string key. */</span></div>
<div class="line"> ret = cursor-><a class="code" href="struct_w_t___c_u_r_s_o_r.html#af19f6f9d9c7fc248ab38879032620b2f" title="Get the key for the current record.">get_key</a>(cursor, &key);</div>
</div><!-- fragment --><div class="fragment"><div class="line"> uint64_t recno; <span class="comment">/* Get the cursor's record number key. */</span></div>
<div class="line"> ret = cursor-><a class="code" href="struct_w_t___c_u_r_s_o_r.html#af19f6f9d9c7fc248ab38879032620b2f" title="Get the key for the current record.">get_key</a>(cursor, &recno);</div>
</div><!-- fragment --><div class="fragment"><div class="line"> <span class="comment">/* Get the cursor's "SiH" format composite key. */</span></div>
<div class="line"> <span class="keyword">const</span> <span class="keywordtype">char</span> *first;</div>
<div class="line"> int32_t second;</div>
<div class="line"> uint16_t third;</div>
<div class="line"> cursor-><a class="code" href="struct_w_t___c_u_r_s_o_r.html#af19f6f9d9c7fc248ab38879032620b2f" title="Get the key for the current record.">get_key</a>(cursor, &first, &second, &third);</div>
</div><!-- fragment --><p> Cursors for a table have the same value format as the table, unless a projection is specified to <a class="el" href="struct_w_t___s_e_s_s_i_o_n.html#afb5b4a69c2c5cafe411b2b04fdc1c75d" title="Open a new cursor on a data source or duplicate an existing cursor.">WT_SESSION::open_cursor</a>.</p>
<p><a class="el" href="struct_w_t___c_u_r_s_o_r.html#a27f7cbd0cd3e561f6a145704813ad64c" title="Set the value for the next operation.">WT_CURSOR::set_value</a> is used to set value columns, and <a class="el" href="struct_w_t___c_u_r_s_o_r.html#af85364a5af50b95bbc46c82e72f75c01" title="Get the value for the current record.">WT_CURSOR::get_value</a> is used to get value columns, in the same way as described for <a class="el" href="struct_w_t___c_u_r_s_o_r.html#ad1088d719df40babc1f57d086691ebdc" title="Set the key for the next operation.">WT_CURSOR::set_key</a> and <a class="el" href="struct_w_t___c_u_r_s_o_r.html#af19f6f9d9c7fc248ab38879032620b2f" title="Get the key for the current record.">WT_CURSOR::get_key</a>.</p>
<h1><a class="anchor" id="schema_columns"></a>
Columns</h1>
<p>The columns in a table can be assigned names by passing a <code>columns</code> key to <a class="el" href="struct_w_t___s_e_s_s_i_o_n.html#a358ca4141d59c345f401c58501276bbb" title="Create a table, column group, index or file.">WT_SESSION::create</a>. The column names are assigned first to the columns in the <code>key_format</code>, and then to the columns in <code>value_format</code>. There must be a name for every column, and no column names may be repeated.</p>
<p>For example, a column-store table with an employee ID as the key and three columns (department, salary and first year of employment), might be created as follows:</p>
<div class="fragment"><div class="line"> <span class="comment">/*</span></div>
<div class="line"><span class="comment"> * Create a table with columns: keys are record numbers, values are</span></div>
<div class="line"><span class="comment"> * (string, signed 32-bit integer, unsigned 16-bit integer).</span></div>
<div class="line"><span class="comment"> */</span></div>
<div class="line"> ret = session-><a class="code" href="struct_w_t___s_e_s_s_i_o_n.html#a358ca4141d59c345f401c58501276bbb" title="Create a table, column group, index or file.">create</a>(session, <span class="stringliteral">"table:mytable"</span>,</div>
<div class="line"> <span class="stringliteral">"key_format=r,value_format=SiH"</span></div>
<div class="line"> <span class="stringliteral">"columns=(id,department,salary,year-started)"</span>);</div>
</div><!-- fragment --><p> In this example, the key's column name is <code>id</code>, and the value's column names are <code>department</code>, <code>salary</code>, and <code>year-started</code> (where <code>id</code> maps to the column format <code>r</code>, <code>department</code> maps to the column value format <code>S</code>, <code>salary</code> maps to the value format <code>i</code> and <code>year-started</code> maps to the value format <code>H</code>).</p>
<p>Once the table is created, there is no need to call <a class="el" href="struct_w_t___s_e_s_s_i_o_n.html#a358ca4141d59c345f401c58501276bbb" title="Create a table, column group, index or file.">WT_SESSION::create</a> during subsequent runs of the application. However, it's worthwhile making the call anyway as it both verifies the table exists and the table schema matches the schema expected by the application.</p>
<h1><a class="anchor" id="schema_column_groups"></a>
Column groups</h1>
<p>Once column names are assigned, they can be used to configure column groups. Column groups are primarily used to define storage in order to tune cache behavior, as each column group is stored in a separate file.</p>
<p>There are two steps involved in setting up column groups: first, pass a list of names for the column groups in the <code>colgroups</code> configuration key to <a class="el" href="struct_w_t___s_e_s_s_i_o_n.html#a358ca4141d59c345f401c58501276bbb" title="Create a table, column group, index or file.">WT_SESSION::create</a>. Then make a call to <a class="el" href="struct_w_t___s_e_s_s_i_o_n.html#a358ca4141d59c345f401c58501276bbb" title="Create a table, column group, index or file.">WT_SESSION::create</a> for each column group, using the URI <code>colgroup:<table>:<colgroup name></code> and a <code>columns</code> key in the configuration. Every column must appear in at least one column group; columns can be listed in multiple column groups, causing the column to be stored in multiple files.</p>
<p>For example, consider the following data being stored in a WiredTiger table:</p>
<div class="fragment"><div class="line"><span class="comment">/* The C struct for the data we are storing in a WiredTiger table. */</span></div>
<div class="line"><span class="keyword">typedef</span> <span class="keyword">struct </span>{</div>
<div class="line"> <span class="keywordtype">char</span> country[5];</div>
<div class="line"> uint16_t year;</div>
<div class="line"> uint64_t population;</div>
<div class="line">} POP_RECORD;</div>
<div class="line"></div>
<div class="line">POP_RECORD pop_data[] = {</div>
<div class="line"> { <span class="stringliteral">"AU"</span>, 1900, 4000000 },</div>
<div class="line"> { <span class="stringliteral">"AU"</span>, 2000, 19053186 },</div>
<div class="line"> { <span class="stringliteral">"CAN"</span>, 1900, 5500000 },</div>
<div class="line"> { <span class="stringliteral">"CAN"</span>, 2000, 31099561 },</div>
<div class="line"> { <span class="stringliteral">"UK"</span>, 1900, 369000000 },</div>
<div class="line"> { <span class="stringliteral">"UK"</span>, 2000, 59522468 },</div>
<div class="line"> { <span class="stringliteral">"USA"</span>, 1900, 76212168 },</div>
<div class="line"> { <span class="stringliteral">"USA"</span>, 2000, 301279593 },</div>
<div class="line"> { <span class="stringliteral">""</span>, 0, 0 }</div>
<div class="line">};</div>
</div><!-- fragment --><p> If we primarily wanted to access the population information by itself, but still wanted population information included when accessing other information, we might store all of the columns in one file, and store an additional copy of the population column in another file:</p>
<div class="fragment"><div class="line"> <span class="comment">/*</span></div>
<div class="line"><span class="comment"> * Create the population table.</span></div>
<div class="line"><span class="comment"> * Keys are record numbers, the format for values is (5-byte string,</span></div>
<div class="line"><span class="comment"> * uint16_t, uint64_t).</span></div>
<div class="line"><span class="comment"> * See ::wiredtiger_struct_pack for details of the format strings.</span></div>
<div class="line"><span class="comment"> */</span></div>
<div class="line"> ret = session-><a class="code" href="struct_w_t___s_e_s_s_i_o_n.html#a358ca4141d59c345f401c58501276bbb" title="Create a table, column group, index or file.">create</a>(session, <span class="stringliteral">"table:poptable"</span>,</div>
<div class="line"> <span class="stringliteral">"key_format=r,"</span></div>
<div class="line"> <span class="stringliteral">"value_format=5sHQ,"</span></div>
<div class="line"> <span class="stringliteral">"columns=(id,country,year,population),"</span></div>
<div class="line"> <span class="stringliteral">"colgroups=(main,population)"</span>);</div>
<div class="line"></div>
<div class="line"> <span class="comment">/*</span></div>
<div class="line"><span class="comment"> * Create two column groups: a primary column group with the country</span></div>
<div class="line"><span class="comment"> * code, year and population (named "main"), and a population column</span></div>
<div class="line"><span class="comment"> * group with the population by itself (named "population").</span></div>
<div class="line"><span class="comment"> */</span></div>
<div class="line"> ret = session-><a class="code" href="struct_w_t___s_e_s_s_i_o_n.html#a358ca4141d59c345f401c58501276bbb" title="Create a table, column group, index or file.">create</a>(session,</div>
<div class="line"> <span class="stringliteral">"colgroup:poptable:main"</span>, <span class="stringliteral">"columns=(country,year,population)"</span>);</div>
<div class="line"> ret = session-><a class="code" href="struct_w_t___s_e_s_s_i_o_n.html#a358ca4141d59c345f401c58501276bbb" title="Create a table, column group, index or file.">create</a>(session,</div>
<div class="line"> <span class="stringliteral">"colgroup:poptable:population"</span>, <span class="stringliteral">"columns=(population)"</span>);</div>
</div><!-- fragment --><p> Column groups always have the same key as the table. This is particularly useful for column stores, because record numbers are not stored explicitly on disk, so there is no repetition of keys across multiple files. Keys will be replicated in multiple files in the case of row-store column groups.</p>
<p>A cursor can be opened on a column group by passing the column group's URI to the <a class="el" href="struct_w_t___s_e_s_s_i_o_n.html#afb5b4a69c2c5cafe411b2b04fdc1c75d" title="Open a new cursor on a data source or duplicate an existing cursor.">WT_SESSION::open_cursor</a> method. For example, the population can be retrieved from both of the column groups we created:</p>
<div class="fragment"><div class="line"> <span class="comment">/*</span></div>
<div class="line"><span class="comment"> * Open a cursor on the main column group, and return the information</span></div>
<div class="line"><span class="comment"> * for a particular country.</span></div>
<div class="line"><span class="comment"> */</span></div>
<div class="line"> ret = session-><a class="code" href="struct_w_t___s_e_s_s_i_o_n.html#afb5b4a69c2c5cafe411b2b04fdc1c75d" title="Open a new cursor on a data source or duplicate an existing cursor.">open_cursor</a>(</div>
<div class="line"> session, <span class="stringliteral">"colgroup:poptable:main"</span>, NULL, NULL, &cursor);</div>
<div class="line"> cursor-><a class="code" href="struct_w_t___c_u_r_s_o_r.html#ad1088d719df40babc1f57d086691ebdc" title="Set the key for the next operation.">set_key</a>(cursor, 2);</div>
<div class="line"> <span class="keywordflow">if</span> ((ret = cursor-><a class="code" href="struct_w_t___c_u_r_s_o_r.html#a7e25b2ced2cf3ec68bd5429bf921c79f" title="Move to the record matching the key.">search</a>(cursor)) == 0) {</div>
<div class="line"> ret = cursor-><a class="code" href="struct_w_t___c_u_r_s_o_r.html#af85364a5af50b95bbc46c82e72f75c01" title="Get the value for the current record.">get_value</a>(cursor, &country, &year, &population);</div>
<div class="line"> printf(<span class="stringliteral">"ID 2: country %s, year %u, population %"</span> PRIu64 <span class="stringliteral">"\n"</span>,</div>
<div class="line"> country, year, population);</div>
<div class="line"> }</div>
</div><!-- fragment --><div class="fragment"><div class="line"> <span class="comment">/*</span></div>
<div class="line"><span class="comment"> * Open a cursor on the population column group, and return the</span></div>
<div class="line"><span class="comment"> * population of a particular country.</span></div>
<div class="line"><span class="comment"> */</span></div>
<div class="line"> ret = session-><a class="code" href="struct_w_t___s_e_s_s_i_o_n.html#afb5b4a69c2c5cafe411b2b04fdc1c75d" title="Open a new cursor on a data source or duplicate an existing cursor.">open_cursor</a>(session,</div>
<div class="line"> <span class="stringliteral">"colgroup:poptable:population"</span>, NULL, NULL, &cursor);</div>
<div class="line"> cursor-><a class="code" href="struct_w_t___c_u_r_s_o_r.html#ad1088d719df40babc1f57d086691ebdc" title="Set the key for the next operation.">set_key</a>(cursor, 2);</div>
<div class="line"> <span class="keywordflow">if</span> ((ret = cursor-><a class="code" href="struct_w_t___c_u_r_s_o_r.html#a7e25b2ced2cf3ec68bd5429bf921c79f" title="Move to the record matching the key.">search</a>(cursor)) == 0) {</div>
<div class="line"> ret = cursor-><a class="code" href="struct_w_t___c_u_r_s_o_r.html#af85364a5af50b95bbc46c82e72f75c01" title="Get the value for the current record.">get_value</a>(cursor, &population);</div>
<div class="line"> printf(<span class="stringliteral">"ID 2: population %"</span> PRIu64 <span class="stringliteral">"\n"</span>, population);</div>
<div class="line"> }</div>
</div><!-- fragment --><p> Key columns may not be included in the list of columns for a column group. Because column groups always have the same key as the table, key columns for column groups are retrieved using <a class="el" href="struct_w_t___c_u_r_s_o_r.html#af19f6f9d9c7fc248ab38879032620b2f" title="Get the key for the current record.">WT_CURSOR::get_key</a>, not <a class="el" href="struct_w_t___c_u_r_s_o_r.html#af85364a5af50b95bbc46c82e72f75c01" title="Get the value for the current record.">WT_CURSOR::get_value</a>.</p>
<h1><a class="anchor" id="schema_indices"></a>
Indices</h1>
<p>Columns are also used to create and configure indices on tables.</p>
<p>Table indices are automatically updated whenever the table is modified.</p>
<p>Table index cursors are read-only and cannot be used for update operations.</p>
<p>To create a table index, call <a class="el" href="struct_w_t___s_e_s_s_i_o_n.html#a358ca4141d59c345f401c58501276bbb" title="Create a table, column group, index or file.">WT_SESSION::create</a> using the URI <code>index:<table>:<index name></code>, listing a column in the configuration.</p>
<p>Continuing the example, we might open an index on the <code>country</code> column:</p>
<div class="fragment"><div class="line"> <span class="comment">/* Create an index with a simple key. */</span></div>
<div class="line"> ret = session-><a class="code" href="struct_w_t___s_e_s_s_i_o_n.html#a358ca4141d59c345f401c58501276bbb" title="Create a table, column group, index or file.">create</a>(session,</div>
<div class="line"> <span class="stringliteral">"index:poptable:country"</span>, <span class="stringliteral">"columns=(country)"</span>);</div>
</div><!-- fragment --><p> Cursors are opened on indices by passing the index's URI to the <a class="el" href="struct_w_t___s_e_s_s_i_o_n.html#afb5b4a69c2c5cafe411b2b04fdc1c75d" title="Open a new cursor on a data source or duplicate an existing cursor.">WT_SESSION::open_cursor</a> method.</p>
<p>Index cursors use the specified index key columns for <a class="el" href="struct_w_t___c_u_r_s_o_r.html#af19f6f9d9c7fc248ab38879032620b2f" title="Get the key for the current record.">WT_CURSOR::get_key</a> and <a class="el" href="struct_w_t___c_u_r_s_o_r.html#ad1088d719df40babc1f57d086691ebdc" title="Set the key for the next operation.">WT_CURSOR::set_key</a>. For example, we can retrieve information from the <code>country</code> index as follows:</p>
<div class="fragment"><div class="line"> <span class="comment">/* Search in a simple index. */</span></div>
<div class="line"> ret = session-><a class="code" href="struct_w_t___s_e_s_s_i_o_n.html#afb5b4a69c2c5cafe411b2b04fdc1c75d" title="Open a new cursor on a data source or duplicate an existing cursor.">open_cursor</a>(session,</div>
<div class="line"> <span class="stringliteral">"index:poptable:country"</span>, NULL, NULL, &cursor);</div>
<div class="line"> cursor-><a class="code" href="struct_w_t___c_u_r_s_o_r.html#ad1088d719df40babc1f57d086691ebdc" title="Set the key for the next operation.">set_key</a>(cursor, <span class="stringliteral">"AU\0\0\0"</span>);</div>
<div class="line"> ret = cursor-><a class="code" href="struct_w_t___c_u_r_s_o_r.html#a7e25b2ced2cf3ec68bd5429bf921c79f" title="Move to the record matching the key.">search</a>(cursor);</div>
<div class="line"> ret = cursor-><a class="code" href="struct_w_t___c_u_r_s_o_r.html#af85364a5af50b95bbc46c82e72f75c01" title="Get the value for the current record.">get_value</a>(cursor, &country, &year, &population);</div>
<div class="line"> printf(<span class="stringliteral">"AU: country %s, year %u, population %"</span> PRIu64 <span class="stringliteral">"\n"</span>,</div>
<div class="line"> country, (<span class="keywordtype">unsigned</span> <span class="keywordtype">int</span>)year, population);</div>
</div><!-- fragment --><p> To create an index with a composite key, specify more than one column to the <a class="el" href="struct_w_t___s_e_s_s_i_o_n.html#a358ca4141d59c345f401c58501276bbb" title="Create a table, column group, index or file.">WT_SESSION::create</a> call:</p>
<div class="fragment"><div class="line"> <span class="comment">/* Create an index with a composite key (country,year). */</span></div>
<div class="line"> ret = session-><a class="code" href="struct_w_t___s_e_s_s_i_o_n.html#a358ca4141d59c345f401c58501276bbb" title="Create a table, column group, index or file.">create</a>(session,</div>
<div class="line"> <span class="stringliteral">"index:poptable:country_plus_year"</span>, <span class="stringliteral">"columns=(country,year)"</span>);</div>
</div><!-- fragment --><p> To retrieve information from a composite index requires a more complicated <a class="el" href="struct_w_t___c_u_r_s_o_r.html#ad1088d719df40babc1f57d086691ebdc" title="Set the key for the next operation.">WT_CURSOR::set_key</a> call, but is otherwise the same:</p>
<div class="fragment"><div class="line"> <span class="comment">/* Search in a composite index. */</span></div>
<div class="line"> ret = session-><a class="code" href="struct_w_t___s_e_s_s_i_o_n.html#afb5b4a69c2c5cafe411b2b04fdc1c75d" title="Open a new cursor on a data source or duplicate an existing cursor.">open_cursor</a>(session,</div>
<div class="line"> <span class="stringliteral">"index:poptable:country_plus_year"</span>, NULL, NULL, &cursor);</div>
<div class="line"> cursor-><a class="code" href="struct_w_t___c_u_r_s_o_r.html#ad1088d719df40babc1f57d086691ebdc" title="Set the key for the next operation.">set_key</a>(cursor, <span class="stringliteral">"USA\0\0"</span>, (uint16_t)1900);</div>
<div class="line"> ret = cursor-><a class="code" href="struct_w_t___c_u_r_s_o_r.html#a7e25b2ced2cf3ec68bd5429bf921c79f" title="Move to the record matching the key.">search</a>(cursor);</div>
<div class="line"> ret = cursor-><a class="code" href="struct_w_t___c_u_r_s_o_r.html#af85364a5af50b95bbc46c82e72f75c01" title="Get the value for the current record.">get_value</a>(cursor, &country, &year, &population);</div>
<div class="line"> printf(<span class="stringliteral">"US 1900: country %s, year %u, population %"</span> PRIu64 <span class="stringliteral">"\n"</span>,</div>
<div class="line"> country, (<span class="keywordtype">unsigned</span> <span class="keywordtype">int</span>)year, population);</div>
</div><!-- fragment --> <h1><a class="anchor" id="schema_index_projections"></a>
Index cursor projections</h1>
<p>By default, index cursors return all of the table's value columns from <a class="el" href="struct_w_t___c_u_r_s_o_r.html#af85364a5af50b95bbc46c82e72f75c01" title="Get the value for the current record.">WT_CURSOR::get_value</a>. The application can specify that a subset of the usual columns should be returned in calls to <a class="el" href="struct_w_t___c_u_r_s_o_r.html#af85364a5af50b95bbc46c82e72f75c01" title="Get the value for the current record.">WT_CURSOR::get_value</a> by appending a list of columns to the <code>uri</code> parameter of the <a class="el" href="struct_w_t___s_e_s_s_i_o_n.html#afb5b4a69c2c5cafe411b2b04fdc1c75d" title="Open a new cursor on a data source or duplicate an existing cursor.">WT_SESSION::open_cursor</a> call. This is called a <em>projection</em>, see <a class="el" href="cursors.html#cursor_projections">Projections</a> for more details.</p>
<p>In the case of index cursors, a projection can be used to avoid lookups in column groups that do not hold columns relevant to the operation.</p>
<p>The following example will return just the table's primary key (a record number, in this case) from the index:</p>
<div class="fragment"><div class="line"> <span class="comment">/* Return the table's record number key using an index. */</span></div>
<div class="line"> ret = session-><a class="code" href="struct_w_t___s_e_s_s_i_o_n.html#afb5b4a69c2c5cafe411b2b04fdc1c75d" title="Open a new cursor on a data source or duplicate an existing cursor.">open_cursor</a>(session,</div>
<div class="line"> <span class="stringliteral">"index:poptable:country_plus_year(id)"</span>, NULL, NULL, &cursor);</div>
<div class="line"> <span class="keywordflow">while</span> ((ret = cursor-><a class="code" href="struct_w_t___c_u_r_s_o_r.html#a0503f16bd8f3d05aa3552f229b3a8e1b" title="Return the next record.">next</a>(cursor)) == 0) {</div>
<div class="line"> ret = cursor-><a class="code" href="struct_w_t___c_u_r_s_o_r.html#af19f6f9d9c7fc248ab38879032620b2f" title="Get the key for the current record.">get_key</a>(cursor, &country, &year);</div>
<div class="line"> ret = cursor-><a class="code" href="struct_w_t___c_u_r_s_o_r.html#af85364a5af50b95bbc46c82e72f75c01" title="Get the value for the current record.">get_value</a>(cursor, &recno);</div>
<div class="line"> printf(</div>
<div class="line"> <span class="stringliteral">"row ID %"</span> PRIu64 <span class="stringliteral">": country %s, year %u\n"</span>,</div>
<div class="line"> recno, country, year);</div>
<div class="line"> }</div>
</div><!-- fragment --><p> Here is an example of a projection that return a subset of columns from the index:</p>
<div class="fragment"><div class="line"> <span class="comment">/* Return just the population column using an index. */</span></div>
<div class="line"> ret = session-><a class="code" href="struct_w_t___s_e_s_s_i_o_n.html#afb5b4a69c2c5cafe411b2b04fdc1c75d" title="Open a new cursor on a data source or duplicate an existing cursor.">open_cursor</a>(session,</div>
<div class="line"> <span class="stringliteral">"index:poptable:country_plus_year(population)"</span>,</div>
<div class="line"> NULL, NULL, &cursor);</div>
<div class="line"> <span class="keywordflow">while</span> ((ret = cursor-><a class="code" href="struct_w_t___c_u_r_s_o_r.html#a0503f16bd8f3d05aa3552f229b3a8e1b" title="Return the next record.">next</a>(cursor)) == 0) {</div>
<div class="line"> ret = cursor-><a class="code" href="struct_w_t___c_u_r_s_o_r.html#af19f6f9d9c7fc248ab38879032620b2f" title="Get the key for the current record.">get_key</a>(cursor, &country, &year);</div>
<div class="line"> ret = cursor-><a class="code" href="struct_w_t___c_u_r_s_o_r.html#af85364a5af50b95bbc46c82e72f75c01" title="Get the value for the current record.">get_value</a>(cursor, &population);</div>
<div class="line"> printf(<span class="stringliteral">"population %"</span> PRIu64 <span class="stringliteral">": country %s, year %u\n"</span>,</div>
<div class="line"> population, country, year);</div>
<div class="line"> }</div>
</div><!-- fragment --><p> For performance reasons, it may be desirable to include all columns for a performance-critical operation in an index, so that it is possible to perform index-only lookups where no column group from the table is accessed. In this case, all of the "hot" columns should be included in the index (always list the "real" index key columns first, so they will determine the sort order). Then, open a cursor on the index that doesn't return any value columns, and no column group will be accessed.</p>
<div class="fragment"><div class="line"> <span class="comment">/*</span></div>
<div class="line"><span class="comment"> * Avoid accessing any other column groups when using an index: supply</span></div>
<div class="line"><span class="comment"> * an empty list of value columns.</span></div>
<div class="line"><span class="comment"> */</span></div>
<div class="line"> ret = session-><a class="code" href="struct_w_t___s_e_s_s_i_o_n.html#afb5b4a69c2c5cafe411b2b04fdc1c75d" title="Open a new cursor on a data source or duplicate an existing cursor.">open_cursor</a>(session,</div>
<div class="line"> <span class="stringliteral">"index:poptable:country_plus_year()"</span>, NULL, NULL, &cursor);</div>
<div class="line"> <span class="keywordflow">while</span> ((ret = cursor-><a class="code" href="struct_w_t___c_u_r_s_o_r.html#a0503f16bd8f3d05aa3552f229b3a8e1b" title="Return the next record.">next</a>(cursor)) == 0) {</div>
<div class="line"> ret = cursor-><a class="code" href="struct_w_t___c_u_r_s_o_r.html#af19f6f9d9c7fc248ab38879032620b2f" title="Get the key for the current record.">get_key</a>(cursor, &country, &year);</div>
<div class="line"> printf(<span class="stringliteral">"country %s, year %u\n"</span>, country, year);</div>
<div class="line"> }</div>
</div><!-- fragment --><p> Index cursors for column-store objects may not be created using the record number as the index key (there is no use for a secondary index on a column-store where the index key is the record number).</p>
<h1><a class="anchor" id="schema_examples"></a>
Code samples</h1>
<p>The code included above was taken from the complete example program <a class="el" href="ex_schema_8c-example.html">ex_schema.c</a>.</p>
<p>Here is another example program, <a class="el" href="ex_call_center_8c-example.html">ex_call_center.c</a>.</p>
<div class="fragment"><div class="line"><span class="comment">/*</span></div>
<div class="line"><span class="comment"> * In SQL, the tables are described as follows:</span></div>
<div class="line"><span class="comment"> *</span></div>
<div class="line"><span class="comment"> * CREATE TABLE Customers(id INTEGER PRIMARY KEY,</span></div>
<div class="line"><span class="comment"> * name VARCHAR(30), address VARCHAR(50), phone VARCHAR(15))</span></div>
<div class="line"><span class="comment"> * CREATE INDEX CustomersPhone ON Customers(phone)</span></div>
<div class="line"><span class="comment"> *</span></div>
<div class="line"><span class="comment"> * CREATE TABLE Calls(id INTEGER PRIMARY KEY, call_date DATE,</span></div>
<div class="line"><span class="comment"> * cust_id INTEGER, emp_id INTEGER, call_type VARCHAR(12),</span></div>
<div class="line"><span class="comment"> * notes VARCHAR(25))</span></div>
<div class="line"><span class="comment"> * CREATE INDEX CallsCustDate ON Calls(cust_id, call_date)</span></div>
<div class="line"><span class="comment"> *</span></div>
<div class="line"><span class="comment"> * In this example, both tables will use record numbers for their IDs, which</span></div>
<div class="line"><span class="comment"> * will be the key. The C structs for the records are as follows.</span></div>
<div class="line"><span class="comment"> */</span></div>
<div class="line"></div>
<div class="line"><span class="comment">/* Customer records. */</span></div>
<div class="line"><span class="keyword">typedef</span> <span class="keyword">struct </span>{</div>
<div class="line"> uint64_t id;</div>
<div class="line"> <span class="keyword">const</span> <span class="keywordtype">char</span> *name;</div>
<div class="line"> <span class="keyword">const</span> <span class="keywordtype">char</span> *address;</div>
<div class="line"> <span class="keyword">const</span> <span class="keywordtype">char</span> *phone;</div>
<div class="line">} CUSTOMER;</div>
<div class="line"></div>
<div class="line"><span class="comment">/* Call records. */</span></div>
<div class="line"><span class="keyword">typedef</span> <span class="keyword">struct </span>{</div>
<div class="line"> uint64_t id;</div>
<div class="line"> uint64_t call_date;</div>
<div class="line"> uint64_t cust_id;</div>
<div class="line"> uint64_t emp_id;</div>
<div class="line"> <span class="keyword">const</span> <span class="keywordtype">char</span> *call_type;</div>
<div class="line"> <span class="keyword">const</span> <span class="keywordtype">char</span> *notes;</div>
<div class="line">} CALL;</div>
</div><!-- fragment --><div class="fragment"><div class="line"> ret = conn-><a class="code" href="struct_w_t___c_o_n_n_e_c_t_i_o_n.html#afc75c44ff4497627c59d9b6aaa64c9d8" title="Open a session.">open_session</a>(conn, NULL, NULL, &session);</div>
<div class="line"></div>
<div class="line"> <span class="comment">/*</span></div>
<div class="line"><span class="comment"> * Create the customers table, give names and types to the columns.</span></div>
<div class="line"><span class="comment"> * The columns will be stored in two groups: "main" and "address",</span></div>
<div class="line"><span class="comment"> * created below.</span></div>
<div class="line"><span class="comment"> */</span></div>
<div class="line"> ret = session-><a class="code" href="struct_w_t___s_e_s_s_i_o_n.html#a358ca4141d59c345f401c58501276bbb" title="Create a table, column group, index or file.">create</a>(session, <span class="stringliteral">"table:customers"</span>,</div>
<div class="line"> <span class="stringliteral">"key_format=r,"</span></div>
<div class="line"> <span class="stringliteral">"value_format=SSS,"</span></div>
<div class="line"> <span class="stringliteral">"columns=(id,name,address,phone),"</span></div>
<div class="line"> <span class="stringliteral">"colgroups=(main,address)"</span>);</div>
<div class="line"></div>
<div class="line"> <span class="comment">/* Create the main column group with value columns except address. */</span></div>
<div class="line"> ret = session-><a class="code" href="struct_w_t___s_e_s_s_i_o_n.html#a358ca4141d59c345f401c58501276bbb" title="Create a table, column group, index or file.">create</a>(session,</div>
<div class="line"> <span class="stringliteral">"colgroup:customers:main"</span>, <span class="stringliteral">"columns=(name,phone)"</span>);</div>
<div class="line"></div>
<div class="line"> <span class="comment">/* Create the address column group with just the address. */</span></div>
<div class="line"> ret = session-><a class="code" href="struct_w_t___s_e_s_s_i_o_n.html#a358ca4141d59c345f401c58501276bbb" title="Create a table, column group, index or file.">create</a>(session,</div>
<div class="line"> <span class="stringliteral">"colgroup:customers:address"</span>, <span class="stringliteral">"columns=(address)"</span>);</div>
<div class="line"></div>
<div class="line"> <span class="comment">/* Create an index on the customer table by phone number. */</span></div>
<div class="line"> ret = session-><a class="code" href="struct_w_t___s_e_s_s_i_o_n.html#a358ca4141d59c345f401c58501276bbb" title="Create a table, column group, index or file.">create</a>(session,</div>
<div class="line"> <span class="stringliteral">"index:customers:phone"</span>, <span class="stringliteral">"columns=(phone)"</span>);</div>
<div class="line"></div>
<div class="line"> <span class="comment">/* Populate the customers table with some data. */</span></div>
<div class="line"> ret = session-><a class="code" href="struct_w_t___s_e_s_s_i_o_n.html#afb5b4a69c2c5cafe411b2b04fdc1c75d" title="Open a new cursor on a data source or duplicate an existing cursor.">open_cursor</a>(</div>
<div class="line"> session, <span class="stringliteral">"table:customers"</span>, NULL, <span class="stringliteral">"append"</span>, &cursor);</div>
<div class="line"> <span class="keywordflow">for</span> (custp = cust_sample; custp->name != NULL; custp++) {</div>
<div class="line"> cursor-><a class="code" href="struct_w_t___c_u_r_s_o_r.html#a27f7cbd0cd3e561f6a145704813ad64c" title="Set the value for the next operation.">set_value</a>(cursor,</div>
<div class="line"> custp->name, custp->address, custp->phone);</div>
<div class="line"> ret = cursor-><a class="code" href="struct_w_t___c_u_r_s_o_r.html#aac90d9fbcc031570f924db55f8a1cee3" title="Insert a record, and optionally overwrite an existing record.">insert</a>(cursor);</div>
<div class="line"> }</div>
<div class="line"> ret = cursor-><a class="code" href="struct_w_t___c_u_r_s_o_r.html#aeea071f192cab12245a50fbe71c3460b" title="Close the cursor.">close</a>(cursor);</div>
<div class="line"></div>
<div class="line"> <span class="comment">/*</span></div>
<div class="line"><span class="comment"> * Create the calls table, give names and types to the columns. All the</span></div>
<div class="line"><span class="comment"> * columns will be stored together, so no column groups are declared.</span></div>
<div class="line"><span class="comment"> */</span></div>
<div class="line"> ret = session-><a class="code" href="struct_w_t___s_e_s_s_i_o_n.html#a358ca4141d59c345f401c58501276bbb" title="Create a table, column group, index or file.">create</a>(session, <span class="stringliteral">"table:calls"</span>,</div>
<div class="line"> <span class="stringliteral">"key_format=r,"</span></div>
<div class="line"> <span class="stringliteral">"value_format=qrrSS,"</span></div>
<div class="line"> <span class="stringliteral">"columns=(id,call_date,cust_id,emp_id,call_type,notes)"</span>);</div>
<div class="line"></div>
<div class="line"> <span class="comment">/*</span></div>
<div class="line"><span class="comment"> * Create an index on the calls table with a composite key of cust_id</span></div>
<div class="line"><span class="comment"> * and call_date.</span></div>
<div class="line"><span class="comment"> */</span></div>
<div class="line"> ret = session-><a class="code" href="struct_w_t___s_e_s_s_i_o_n.html#a358ca4141d59c345f401c58501276bbb" title="Create a table, column group, index or file.">create</a>(session, <span class="stringliteral">"index:calls:cust_date"</span>,</div>
<div class="line"> <span class="stringliteral">"columns=(cust_id,call_date)"</span>);</div>
<div class="line"></div>
<div class="line"> <span class="comment">/* Populate the calls table with some data. */</span></div>
<div class="line"> ret = session-><a class="code" href="struct_w_t___s_e_s_s_i_o_n.html#afb5b4a69c2c5cafe411b2b04fdc1c75d" title="Open a new cursor on a data source or duplicate an existing cursor.">open_cursor</a>(</div>
<div class="line"> session, <span class="stringliteral">"table:calls"</span>, NULL, <span class="stringliteral">"append"</span>, &cursor);</div>
<div class="line"> <span class="keywordflow">for</span> (callp = call_sample; callp->call_type != NULL; callp++) {</div>
<div class="line"> cursor-><a class="code" href="struct_w_t___c_u_r_s_o_r.html#a27f7cbd0cd3e561f6a145704813ad64c" title="Set the value for the next operation.">set_value</a>(cursor, callp->call_date, callp->cust_id,</div>
<div class="line"> callp->emp_id, callp->call_type, callp->notes);</div>
<div class="line"> ret = cursor-><a class="code" href="struct_w_t___c_u_r_s_o_r.html#aac90d9fbcc031570f924db55f8a1cee3" title="Insert a record, and optionally overwrite an existing record.">insert</a>(cursor);</div>
<div class="line"> }</div>
<div class="line"> ret = cursor-><a class="code" href="struct_w_t___c_u_r_s_o_r.html#aeea071f192cab12245a50fbe71c3460b" title="Close the cursor.">close</a>(cursor);</div>
<div class="line"></div>
<div class="line"> <span class="comment">/*</span></div>
<div class="line"><span class="comment"> * First query: a call arrives. In SQL:</span></div>
<div class="line"><span class="comment"> *</span></div>
<div class="line"><span class="comment"> * SELECT id, name FROM Customers WHERE phone=?</span></div>
<div class="line"><span class="comment"> *</span></div>
<div class="line"><span class="comment"> * Use the cust_phone index, lookup by phone number to fill the</span></div>
<div class="line"><span class="comment"> * customer record. The cursor will have a key format of "S" for a</span></div>
<div class="line"><span class="comment"> * string because the cust_phone index has a single column ("phone"),</span></div>
<div class="line"><span class="comment"> * which is of type "S".</span></div>
<div class="line"><span class="comment"> *</span></div>
<div class="line"><span class="comment"> * Specify the columns we want: the customer ID and the name. This</span></div>
<div class="line"><span class="comment"> * means the cursor's value format will be "rS".</span></div>
<div class="line"><span class="comment"> */</span></div>
<div class="line"> ret = session-><a class="code" href="struct_w_t___s_e_s_s_i_o_n.html#afb5b4a69c2c5cafe411b2b04fdc1c75d" title="Open a new cursor on a data source or duplicate an existing cursor.">open_cursor</a>(session,</div>
<div class="line"> <span class="stringliteral">"index:customers:phone(id,name)"</span>, NULL, NULL, &cursor);</div>
<div class="line"> cursor-><a class="code" href="struct_w_t___c_u_r_s_o_r.html#ad1088d719df40babc1f57d086691ebdc" title="Set the key for the next operation.">set_key</a>(cursor, <span class="stringliteral">"123-456-7890"</span>);</div>
<div class="line"> ret = cursor-><a class="code" href="struct_w_t___c_u_r_s_o_r.html#a7e25b2ced2cf3ec68bd5429bf921c79f" title="Move to the record matching the key.">search</a>(cursor);</div>
<div class="line"> <span class="keywordflow">if</span> (ret == 0) {</div>
<div class="line"> ret = cursor-><a class="code" href="struct_w_t___c_u_r_s_o_r.html#af85364a5af50b95bbc46c82e72f75c01" title="Get the value for the current record.">get_value</a>(cursor, &cust.id, &cust.name);</div>
<div class="line"> printf(<span class="stringliteral">"Read customer record for %s (ID %"</span> PRIu64 <span class="stringliteral">")\n"</span>,</div>
<div class="line"> cust.name, cust.id);</div>
<div class="line"> }</div>
<div class="line"> ret = cursor-><a class="code" href="struct_w_t___c_u_r_s_o_r.html#aeea071f192cab12245a50fbe71c3460b" title="Close the cursor.">close</a>(cursor);</div>
<div class="line"></div>
<div class="line"> <span class="comment">/*</span></div>
<div class="line"><span class="comment"> * Next query: get the recent order history. In SQL:</span></div>
<div class="line"><span class="comment"> *</span></div>
<div class="line"><span class="comment"> * SELECT * FROM Calls WHERE cust_id=? ORDER BY call_date DESC LIMIT 3</span></div>
<div class="line"><span class="comment"> *</span></div>
<div class="line"><span class="comment"> * Use the call_cust_date index to find the matching calls. Since it is</span></div>
<div class="line"><span class="comment"> * is in increasing order by date for a given customer, we want to start</span></div>
<div class="line"><span class="comment"> * with the last record for the customer and work backwards.</span></div>
<div class="line"><span class="comment"> *</span></div>
<div class="line"><span class="comment"> * Specify a subset of columns to be returned. (Note that if these were</span></div>
<div class="line"><span class="comment"> * all covered by the index, the primary would not have to be accessed.)</span></div>
<div class="line"><span class="comment"> * Stop after getting 3 records.</span></div>
<div class="line"><span class="comment"> */</span></div>
<div class="line"> ret = session-><a class="code" href="struct_w_t___s_e_s_s_i_o_n.html#afb5b4a69c2c5cafe411b2b04fdc1c75d" title="Open a new cursor on a data source or duplicate an existing cursor.">open_cursor</a>(session,</div>
<div class="line"> <span class="stringliteral">"index:calls:cust_date(cust_id,call_type,notes)"</span>,</div>
<div class="line"> NULL, NULL, &cursor);</div>
<div class="line"></div>
<div class="line"> <span class="comment">/*</span></div>
<div class="line"><span class="comment"> * The keys in the index are (cust_id,call_date) -- we want the largest</span></div>
<div class="line"><span class="comment"> * call date for a given cust_id. Search for (cust_id+1,0), then work</span></div>
<div class="line"><span class="comment"> * backwards.</span></div>
<div class="line"><span class="comment"> */</span></div>
<div class="line"> cust.id = 1;</div>
<div class="line"> cursor-><a class="code" href="struct_w_t___c_u_r_s_o_r.html#ad1088d719df40babc1f57d086691ebdc" title="Set the key for the next operation.">set_key</a>(cursor, cust.id + 1, 0);</div>
<div class="line"> ret = cursor-><a class="code" href="struct_w_t___c_u_r_s_o_r.html#a8068ddce20d0775f26f6dac6e5eb209c" title="Move to the record matching the key if it exists, or a record that would be adjacent.">search_near</a>(cursor, &exact);</div>
<div class="line"></div>
<div class="line"> <span class="comment">/*</span></div>
<div class="line"><span class="comment"> * If the table is empty, search_near will return WT_NOTFOUND, else the</span></div>
<div class="line"><span class="comment"> * cursor will be positioned on a matching key if one exists, or an</span></div>
<div class="line"><span class="comment"> * adjacent key if one does not. If the positioned key is equal to or</span></div>
<div class="line"><span class="comment"> * larger than the search key, go back one.</span></div>
<div class="line"><span class="comment"> */</span></div>
<div class="line"> <span class="keywordflow">if</span> (ret == 0 && exact >= 0)</div>
<div class="line"> ret = cursor-><a class="code" href="struct_w_t___c_u_r_s_o_r.html#a43d6664d2f68902aa63f933864242e76" title="Return the previous record.">prev</a>(cursor);</div>
<div class="line"> <span class="keywordflow">for</span> (count = 0; ret == 0 && count < 3; ++count) {</div>
<div class="line"> ret = cursor-><a class="code" href="struct_w_t___c_u_r_s_o_r.html#af85364a5af50b95bbc46c82e72f75c01" title="Get the value for the current record.">get_value</a>(cursor,</div>
<div class="line"> &call.cust_id, &call.call_type, &call.notes);</div>
<div class="line"> <span class="keywordflow">if</span> (call.cust_id != cust.id)</div>
<div class="line"> <span class="keywordflow">break</span>;</div>
<div class="line"> printf(<span class="stringliteral">"Call record: customer %"</span> PRIu64 <span class="stringliteral">" (%s: %s)\n"</span>,</div>
<div class="line"> call.cust_id, call.call_type, call.notes);</div>
<div class="line"> ret = cursor-><a class="code" href="struct_w_t___c_u_r_s_o_r.html#a43d6664d2f68902aa63f933864242e76" title="Return the previous record.">prev</a>(cursor);</div>
<div class="line"> }</div>
</div><!-- fragment --> </div></div><!-- contents -->
</div><!-- doc-content -->
<!-- start footer part -->
<div id="nav-path" class="navpath"><!-- id is needed for treeview function! -->
<ul>
<li class="navelem"><a class="el" href="index.html">Reference Guide</a></li><li class="navelem"><a class="el" href="programming.html">Writing WiredTiger applications</a></li>
<li class="footer">Copyright (c) 2008-2013 WiredTiger, Inc. All rights reserved. Contact <a href="mailto:[email protected]">[email protected]</a> for more information.</li>
</ul>
</div>
</body>
</html>