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
|
<!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: ex_call_center.c</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="navtreedata.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 3.2.1</span>
</div>
<div id="projectbrief"><!-- 3.2.1 --></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.13 -->
<script type="text/javascript" src="menudata.js"></script>
<script type="text/javascript" src="menu.js"></script>
<script type="text/javascript">
$(function() {
initMenu('',false,false,'search.php','Search');
});
</script>
<div id="main-nav"></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('ex_call_center_8c-example.html','');});
</script>
<div id="doc-content">
<div class="header">
<div class="headertitle">
<div class="title">ex_call_center.c</div> </div>
</div><!--header-->
<div class="contents">
<p>A more complex schema based on a call center example, showing how to map some SQL constructs onto the WiredTiger API.</p>
<div class="fragment"><div class="line"><span class="comment">/*-</span></div><div class="line"><span class="comment"> * Public Domain 2014-2019 MongoDB, Inc.</span></div><div class="line"><span class="comment"> * Public Domain 2008-2014 WiredTiger, Inc.</span></div><div class="line"><span class="comment"> *</span></div><div class="line"><span class="comment"> * This is free and unencumbered software released into the public domain.</span></div><div class="line"><span class="comment"> *</span></div><div class="line"><span class="comment"> * Anyone is free to copy, modify, publish, use, compile, sell, or</span></div><div class="line"><span class="comment"> * distribute this software, either in source code form or as a compiled</span></div><div class="line"><span class="comment"> * binary, for any purpose, commercial or non-commercial, and by any</span></div><div class="line"><span class="comment"> * means.</span></div><div class="line"><span class="comment"> *</span></div><div class="line"><span class="comment"> * In jurisdictions that recognize copyright laws, the author or authors</span></div><div class="line"><span class="comment"> * of this software dedicate any and all copyright interest in the</span></div><div class="line"><span class="comment"> * software to the public domain. We make this dedication for the benefit</span></div><div class="line"><span class="comment"> * of the public at large and to the detriment of our heirs and</span></div><div class="line"><span class="comment"> * successors. We intend this dedication to be an overt act of</span></div><div class="line"><span class="comment"> * relinquishment in perpetuity of all present and future rights to this</span></div><div class="line"><span class="comment"> * software under copyright law.</span></div><div class="line"><span class="comment"> *</span></div><div class="line"><span class="comment"> * THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND,</span></div><div class="line"><span class="comment"> * EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF</span></div><div class="line"><span class="comment"> * MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT.</span></div><div class="line"><span class="comment"> * IN NO EVENT SHALL THE AUTHORS BE LIABLE FOR ANY CLAIM, DAMAGES OR</span></div><div class="line"><span class="comment"> * OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE,</span></div><div class="line"><span class="comment"> * ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR</span></div><div class="line"><span class="comment"> * OTHER DEALINGS IN THE SOFTWARE.</span></div><div class="line"><span class="comment"> *</span></div><div class="line"><span class="comment"> * ex_call_center.c</span></div><div class="line"><span class="comment"> * This is an example application that demonstrates how to map a</span></div><div class="line"><span class="comment"> * moderately complex SQL application into WiredTiger.</span></div><div class="line"><span class="comment"> */</span></div><div class="line"><span class="preprocessor">#include <test_util.h></span></div><div class="line"></div><div class="line"><span class="keyword">static</span> <span class="keyword">const</span> <span class="keywordtype">char</span> *home;</div><div class="line"></div><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 class="line"><span class="keywordtype">int</span></div><div class="line">main(<span class="keywordtype">int</span> argc, <span class="keywordtype">char</span> *argv[])</div><div class="line">{</div><div class="line"> <span class="keywordtype">int</span> count, exact;</div><div class="line"> <a name="_a0"></a><a class="code" href="struct_w_t___c_o_n_n_e_c_t_i_o_n.html">WT_CONNECTION</a> *conn;</div><div class="line"> <a name="_a1"></a><a class="code" href="struct_w_t___s_e_s_s_i_o_n.html">WT_SESSION</a> *session;</div><div class="line"> <a name="_a2"></a><a class="code" href="struct_w_t___c_u_r_s_o_r.html">WT_CURSOR</a> *cursor;</div><div class="line"> CUSTOMER cust, *custp,</div><div class="line"> cust_sample[] = {{0, <span class="stringliteral">"Professor Oak"</span>, <span class="stringliteral">"LeafGreen Avenue"</span>, <span class="stringliteral">"123-456-7890"</span>},</div><div class="line"> {0, <span class="stringliteral">"Lorelei"</span>, <span class="stringliteral">"Sevii Islands"</span>, <span class="stringliteral">"098-765-4321"</span>}, {0, NULL, NULL, NULL}};</div><div class="line"> CALL call, *callp,</div><div class="line"> call_sample[] = {{0, 32, 1, 2, <span class="stringliteral">"billing"</span>, <span class="stringliteral">"unavailable"</span>},</div><div class="line"> {0, 33, 1, 2, <span class="stringliteral">"billing"</span>, <span class="stringliteral">"available"</span>}, {0, 34, 1, 2, <span class="stringliteral">"reminder"</span>, <span class="stringliteral">"unavailable"</span>},</div><div class="line"> {0, 35, 1, 2, <span class="stringliteral">"reminder"</span>, <span class="stringliteral">"available"</span>}, {0, 0, 0, 0, NULL, NULL}};</div><div class="line"></div><div class="line"> home = example_setup(argc, argv);</div><div class="line"> error_check(<a name="a3"></a><a class="code" href="group__wt.html#gacbe8d118f978f5bfc8ccb4c77c9e8813">wiredtiger_open</a>(home, NULL, <span class="stringliteral">"create"</span>, &conn));</div><div class="line"></div><div class="line"> error_check(conn-><a name="a4"></a><a class="code" href="struct_w_t___c_o_n_n_e_c_t_i_o_n.html#adad5965cd4a60f65b5ac01f7ca6d1fc0">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. The columns will be stored</span></div><div class="line"><span class="comment"> * in two groups: "main" and "address", created below.</span></div><div class="line"><span class="comment"> */</span></div><div class="line"> error_check(session-><a name="a5"></a><a class="code" href="struct_w_t___s_e_s_s_i_o_n.html#a358ca4141d59c345f401c58501276bbb">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"> error_check(session-><a class="code" href="struct_w_t___s_e_s_s_i_o_n.html#a358ca4141d59c345f401c58501276bbb">create</a>(session, <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"> error_check(session-><a class="code" href="struct_w_t___s_e_s_s_i_o_n.html#a358ca4141d59c345f401c58501276bbb">create</a>(session, <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"> error_check(session-><a class="code" href="struct_w_t___s_e_s_s_i_o_n.html#a358ca4141d59c345f401c58501276bbb">create</a>(session, <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"> error_check(session-><a name="a6"></a><a class="code" href="struct_w_t___s_e_s_s_i_o_n.html#afb5b4a69c2c5cafe411b2b04fdc1c75d">open_cursor</a>(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 name="a7"></a><a class="code" href="struct_w_t___c_u_r_s_o_r.html#a27f7cbd0cd3e561f6a145704813ad64c">set_value</a>(cursor, custp->name, custp->address, custp->phone);</div><div class="line"> error_check(cursor-><a name="a8"></a><a class="code" href="struct_w_t___c_u_r_s_o_r.html#aac90d9fbcc031570f924db55f8a1cee3">insert</a>(cursor));</div><div class="line"> }</div><div class="line"> error_check(cursor-><a name="a9"></a><a class="code" href="struct_w_t___c_u_r_s_o_r.html#aeea071f192cab12245a50fbe71c3460b">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 columns will be stored</span></div><div class="line"><span class="comment"> * together, so no column groups are declared.</span></div><div class="line"><span class="comment"> */</span></div><div class="line"> error_check(session-><a class="code" href="struct_w_t___s_e_s_s_i_o_n.html#a358ca4141d59c345f401c58501276bbb">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 and call_date.</span></div><div class="line"><span class="comment"> */</span></div><div class="line"> error_check(session-><a class="code" href="struct_w_t___s_e_s_s_i_o_n.html#a358ca4141d59c345f401c58501276bbb">create</a>(session, <span class="stringliteral">"index:calls:cust_date"</span>, <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"> error_check(session-><a class="code" href="struct_w_t___s_e_s_s_i_o_n.html#afb5b4a69c2c5cafe411b2b04fdc1c75d">open_cursor</a>(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">set_value</a>(</div><div class="line"> cursor, callp->call_date, callp->cust_id, callp->emp_id, callp->call_type, callp->notes);</div><div class="line"> error_check(cursor-><a class="code" href="struct_w_t___c_u_r_s_o_r.html#aac90d9fbcc031570f924db55f8a1cee3">insert</a>(cursor));</div><div class="line"> }</div><div class="line"> error_check(cursor-><a class="code" href="struct_w_t___c_u_r_s_o_r.html#aeea071f192cab12245a50fbe71c3460b">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"> error_check(</div><div class="line"> session-><a class="code" href="struct_w_t___s_e_s_s_i_o_n.html#afb5b4a69c2c5cafe411b2b04fdc1c75d">open_cursor</a>(session, <span class="stringliteral">"index:customers:phone(id,name)"</span>, NULL, NULL, &cursor));</div><div class="line"> cursor-><a name="a10"></a><a class="code" href="struct_w_t___c_u_r_s_o_r.html#ad1088d719df40babc1f57d086691ebdc">set_key</a>(cursor, <span class="stringliteral">"123-456-7890"</span>);</div><div class="line"> error_check(cursor-><a name="a11"></a><a class="code" href="struct_w_t___c_u_r_s_o_r.html#a7e25b2ced2cf3ec68bd5429bf921c79f">search</a>(cursor));</div><div class="line"> error_check(cursor-><a name="a12"></a><a class="code" href="struct_w_t___c_u_r_s_o_r.html#af85364a5af50b95bbc46c82e72f75c01">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>, cust.name, cust.id);</div><div class="line"> error_check(cursor-><a class="code" href="struct_w_t___c_u_r_s_o_r.html#aeea071f192cab12245a50fbe71c3460b">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"> error_check(session-><a class="code" href="struct_w_t___s_e_s_s_i_o_n.html#afb5b4a69c2c5cafe411b2b04fdc1c75d">open_cursor</a>(</div><div class="line"> session, <span class="stringliteral">"index:calls:cust_date(cust_id,call_type,notes)"</span>, 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 call date for a given</span></div><div class="line"><span class="comment"> * cust_id. Search for (cust_id+1,0), then work 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">set_key</a>(cursor, cust.id + 1, 0);</div><div class="line"> error_check(cursor-><a name="a13"></a><a class="code" href="struct_w_t___c_u_r_s_o_r.html#a8068ddce20d0775f26f6dac6e5eb209c">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 cursor will be</span></div><div class="line"><span class="comment"> * positioned on a matching key if one exists, or an adjacent key if one does not. If the</span></div><div class="line"><span class="comment"> * positioned key is equal to or 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> (exact >= 0)</div><div class="line"> error_check(cursor-><a name="a14"></a><a class="code" href="struct_w_t___c_u_r_s_o_r.html#a43d6664d2f68902aa63f933864242e76">prev</a>(cursor));</div><div class="line"> <span class="keywordflow">for</span> (count = 0; count < 3; ++count) {</div><div class="line"> error_check(cursor-><a class="code" href="struct_w_t___c_u_r_s_o_r.html#af85364a5af50b95bbc46c82e72f75c01">get_value</a>(cursor, &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(</div><div class="line"> <span class="stringliteral">"Call record: customer %"</span> PRIu64 <span class="stringliteral">" (%s: %s)\n"</span>, call.cust_id, call.call_type, call.notes);</div><div class="line"> error_check(cursor-><a class="code" href="struct_w_t___c_u_r_s_o_r.html#a43d6664d2f68902aa63f933864242e76">prev</a>(cursor));</div><div class="line"> }</div><div class="line"> error_check(conn-><a name="a15"></a><a class="code" href="struct_w_t___c_o_n_n_e_c_t_i_o_n.html#af535c517df851eeac8ebf3594d40b545">close</a>(conn, NULL));</div><div class="line"></div><div class="line"> <span class="keywordflow">return</span> (EXIT_SUCCESS);</div><div class="line">}</div></div><!-- fragment --> </div><!-- contents -->
</div><!-- doc-content -->
<!-- start footer part -->
<div id="nav-path" class="navpath"><!-- id is needed for treeview function! -->
<ul>
<li class="footer">Copyright (c) 2008-2019 MongoDB, Inc. All rights reserved. Contact <a href="mailto:info@wiredtiger.com">info@wiredtiger.com</a> for more information.</li>
</ul>
</div>
</body>
</html>
|
