<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd"> <html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en"> <head> <meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /> <meta name="generator" content="AsciiDoc 8.4.4" /> <title>Wt::Dbo Tutorial</title> <style type="text/css"> /* Debug borders */ p, li, dt, dd, div, pre, h1, h2, h3, h4, h5, h6 { /* border: 1px solid red; */ } body { margin: 1em 5% 1em 5%; } a { color: blue; text-decoration: underline; } a:visited { color: fuchsia; } em { font-style: italic; color: navy; } strong { font-weight: bold; color: #083194; } tt { color: navy; } h1, h2, h3, h4, h5, h6 { color: #527bbd; font-family: sans-serif; margin-top: 1.2em; margin-bottom: 0.5em; line-height: 1.3; } h1, h2, h3 { border-bottom: 2px solid silver; } h2 { padding-top: 0.5em; } h3 { float: left; } h3 + * { clear: left; } div.sectionbody { font-family: serif; margin-left: 0; } hr { border: 1px solid silver; } p { margin-top: 0.5em; margin-bottom: 0.5em; } ul, ol, li > p { margin-top: 0; } pre { padding: 0; margin: 0; } span#author { color: #527bbd; font-family: sans-serif; font-weight: bold; font-size: 1.1em; } span#email { } span#revision { font-family: sans-serif; } div#footer { font-family: sans-serif; font-size: small; border-top: 2px solid silver; padding-top: 0.5em; margin-top: 4.0em; } div#footer-text { float: left; padding-bottom: 0.5em; } div#footer-badges { float: right; padding-bottom: 0.5em; } div#preamble { margin-top: 1.5em; margin-bottom: 1.5em; } div.tableblock, div.imageblock, div.exampleblock, div.verseblock, div.quoteblock, div.literalblock, div.listingblock, div.sidebarblock, div.admonitionblock { margin-top: 1.5em; margin-bottom: 1.5em; } div.admonitionblock { margin-top: 2.5em; margin-bottom: 2.5em; } div.content { /* Block element content. */ padding: 0; } /* Block element titles. */ div.title, caption.title { color: #527bbd; font-family: sans-serif; font-weight: bold; text-align: left; margin-top: 1.0em; margin-bottom: 0.5em; } div.title + * { margin-top: 0; } td div.title:first-child { margin-top: 0.0em; } div.content div.title:first-child { margin-top: 0.0em; } div.content + div.title { margin-top: 0.0em; } div.sidebarblock > div.content { background: #ffffee; border: 1px solid silver; padding: 0.5em; } div.listingblock > div.content { border: 1px solid silver; background: #f4f4f4; padding: 0.5em; } div.quoteblock { padding-left: 2.0em; margin-right: 10%; } div.quoteblock > div.attribution { padding-top: 0.5em; text-align: right; } div.verseblock { padding-left: 2.0em; margin-right: 10%; } div.verseblock > div.content { white-space: pre; } div.verseblock > div.attribution { padding-top: 0.75em; text-align: left; } /* DEPRECATED: Pre version 8.2.7 verse style literal block. */ div.verseblock + div.attribution { text-align: left; } div.admonitionblock .icon { vertical-align: top; font-size: 1.1em; font-weight: bold; text-decoration: underline; color: #527bbd; padding-right: 0.5em; } div.admonitionblock td.content { padding-left: 0.5em; border-left: 2px solid silver; } div.exampleblock > div.content { border-left: 2px solid silver; padding: 0.5em; } div.imageblock div.content { padding-left: 0; } div.imageblock img { border: 1px solid silver; } span.image img { border-style: none; } dl { margin-top: 0.8em; margin-bottom: 0.8em; } dt { margin-top: 0.5em; margin-bottom: 0; font-style: normal; color: navy; } dd > *:first-child { margin-top: 0.1em; } ul, ol { list-style-position: outside; } ol.arabic { list-style-type: decimal; } ol.loweralpha { list-style-type: lower-alpha; } ol.upperalpha { list-style-type: upper-alpha; } ol.lowerroman { list-style-type: lower-roman; } ol.upperroman { list-style-type: upper-roman; } div.compact ul, div.compact ol, div.compact p, div.compact p, div.compact div, div.compact div { margin-top: 0.1em; margin-bottom: 0.1em; } div.tableblock > table { border: 3px solid #527bbd; } thead { font-family: sans-serif; font-weight: bold; } tfoot { font-weight: bold; } td > div.verse { white-space: pre; } p.table { margin-top: 0; } /* Because the table frame attribute is overriden by CSS in most browsers. */ div.tableblock > table[frame="void"] { border-style: none; } div.tableblock > table[frame="hsides"] { border-left-style: none; border-right-style: none; } div.tableblock > table[frame="vsides"] { border-top-style: none; border-bottom-style: none; } div.hdlist { margin-top: 0.8em; margin-bottom: 0.8em; } div.hdlist tr { padding-bottom: 15px; } dt.hdlist1.strong, td.hdlist1.strong { font-weight: bold; } td.hdlist1 { vertical-align: top; font-style: normal; padding-right: 0.8em; color: navy; } td.hdlist2 { vertical-align: top; } div.hdlist.compact tr { margin: 0; padding-bottom: 0; } .comment { background: yellow; } @media print { div#footer-badges { display: none; } } div#toctitle { color: #527bbd; font-family: sans-serif; font-size: 1.1em; font-weight: bold; margin-top: 1.0em; margin-bottom: 0.1em; } div.toclevel1, div.toclevel2, div.toclevel3, div.toclevel4 { margin-top: 0; margin-bottom: 0; } div.toclevel2 { margin-left: 2em; font-size: 0.9em; } div.toclevel3 { margin-left: 4em; font-size: 0.9em; } div.toclevel4 { margin-left: 6em; font-size: 0.9em; } /* Workarounds for IE6's broken and incomplete CSS2. */ div.sidebar-content { background: #ffffee; border: 1px solid silver; padding: 0.5em; } div.sidebar-title, div.image-title { color: #527bbd; font-family: sans-serif; font-weight: bold; margin-top: 0.0em; margin-bottom: 0.5em; } div.listingblock div.content { border: 1px solid silver; background: #f4f4f4; padding: 0.5em; } div.quoteblock-attribution { padding-top: 0.5em; text-align: right; } div.verseblock-content { white-space: pre; } div.verseblock-attribution { padding-top: 0.75em; text-align: left; } div.exampleblock-content { border-left: 2px solid silver; padding-left: 0.5em; } /* IE6 sets dynamically generated links as visited. */ div#toc a:visited { color: blue; } </style> <script type="text/javascript"> /*<![CDATA[*/ window.onload = function(){generateToc(2)} /* Author: Mihai Bazon, September 2002 * http://students.infoiasi.ro/~mishoo * * Table Of Content generator * Version: 0.4 * * Feel free to use this script under the terms of the GNU General Public * License, as long as you do not remove or alter this notice. */ /* modified by Troy D. Hanson, September 2006. License: GPL */ /* modified by Stuart Rackham, October 2006. License: GPL */ function getText(el) { var text = ""; for (var i = el.firstChild; i != null; i = i.nextSibling) { if (i.nodeType == 3 /* Node.TEXT_NODE */) // IE doesn't speak constants. text += i.data; else if (i.firstChild != null) text += getText(i); } return text; } function TocEntry(el, text, toclevel) { this.element = el; this.text = text; this.toclevel = toclevel; } function tocEntries(el, toclevels) { var result = new Array; var re = new RegExp('[hH]([2-'+(toclevels+1)+'])'); // Function that scans the DOM tree for header elements (the DOM2 // nodeIterator API would be a better technique but not supported by all // browsers). var iterate = function (el) { for (var i = el.firstChild; i != null; i = i.nextSibling) { if (i.nodeType == 1 /* Node.ELEMENT_NODE */) { var mo = re.exec(i.tagName) if (mo) result[result.length] = new TocEntry(i, getText(i), mo[1]-1); iterate(i); } } } iterate(el); return result; } // This function does the work. toclevels = 1..4. function generateToc(toclevels) { var toc = document.getElementById("toc"); var entries = tocEntries(document.getElementsByTagName("body")[0], toclevels); for (var i = 0; i < entries.length; ++i) { var entry = entries[i]; if (entry.element.id == "") entry.element.id = "toc" + i; var a = document.createElement("a"); a.href = "#" + entry.element.id; a.appendChild(document.createTextNode(entry.text)); var div = document.createElement("div"); div.appendChild(a); div.className = "toclevel" + entry.toclevel; toc.appendChild(div); } if (entries.length == 0) document.getElementById("header").removeChild(toc); } /*]]>*/ </script> </head> <body> <div id="header"> <h1>Wt::Dbo Tutorial</h1> <div id="toc"> <div id="toctitle">Table of Contents</div> <noscript><p><b>JavaScript must be enabled in your browser to display the table of contents.</b></p></noscript> </div> </div> <div id="preamble"> <div class="sectionbody"> <div class="paragraph"><p>Koen Deforche <<a href="mailto:koen@emweb.be">koen@emweb.be</a>> 3.1.10, July 14 2011</p></div> </div> </div> <h2 id="_introduction">1. Introduction</h2> <div class="sectionbody"> <div class="paragraph"><p><tt>Wt::Dbo</tt> is a C++ ORM (Object-Relational-Mapping) library.</p></div> <div class="paragraph"><p>The library is distributed as part of <a href="http://www.webtoolkit.eu/wt">Wt</a> for building database-driven web applications, but may be equally well used independently from it.</p></div> <div class="paragraph"><p>The library provides a class-based view on database tables which keeps an object-hiearchy of database object automatically synchronized with a database by inserting, updating and deleting database records. C++ classes map to database tables, class fields to table columns, and pointers and collections of pointers to database relations. An object from a mapped class is called a <strong>database object</strong> (dbo). Query results may be defined in terms of database objects, primitives, or tuples of these.</p></div> <div class="paragraph"><p>A modern C++ approach is used to solve the mapping problem. Rather than resorting to XML-based descriptions of how C++ classes and fields should map onto tables and columns, or using obscure macros, the mapping is defined entirely in C++ code.</p></div> <div class="paragraph"><p>In this tutorial, we will work our way through a blogging example, similar to the one that is distributed with the library.</p></div> <div class="admonitionblock"> <table><tr> <td class="icon"> <div class="title">Tip</div> </td> <td class="content"> <div class="paragraph"><p>The complete source code for the examples used in this tutorial are available as ready-to-run programs in the <tt>examples/feature/dbo/</tt> folder of <a href="http://www.webtoolkit.eu/wt/download">Wt</a>.</p></div> </td> </tr></table> </div> </div> <h2 id="_mapping_a_single_class">2. Mapping a single class</h2> <div class="sectionbody"> <div class="paragraph"><p>We will start off with using <tt>Wt::Dbo</tt> for mapping a single class <tt>User</tt> to a corresponding table <tt>user</tt>.</p></div> <div class="admonitionblock"> <table><tr> <td class="icon"> <div class="title">Warning</div> </td> <td class="content"> <div class="paragraph"><p>In this tutorial and the examples, we alias the namespace <tt>Wt::Dbo</tt> to <tt>dbo</tt>, and in our explanation we will refer to types and methods available in that namespace directly.</p></div> </td> </tr></table> </div> <div class="paragraph"><p>To build the following example, you need to link against the <tt>wtdbo</tt> and <tt>wtdbosqlite3</tt> libraries.</p></div> <div class="listingblock"> <div class="title">Mapping a single class (tutorial1.C)</div> <div class="content"><!-- Generator: GNU source-highlight 3.1 by Lorenzo Bettini http://www.lorenzobettini.it http://www.gnu.org/software/src-highlite --> <pre><tt><span style="font-weight: bold"><span style="color: #000080">#include</span></span> <span style="color: #FF0000"><Wt/Dbo/Dbo></span> <span style="font-weight: bold"><span style="color: #000080">#include</span></span> <span style="color: #FF0000"><string></span> <span style="font-weight: bold"><span style="color: #0000FF">namespace</span></span> dbo <span style="color: #990000">=</span> Wt<span style="color: #990000">::</span>Dbo<span style="color: #990000">;</span> <span style="font-weight: bold"><span style="color: #0000FF">class</span></span> <span style="color: #008080">User</span> <span style="color: #FF0000">{</span> <span style="font-weight: bold"><span style="color: #0000FF">public</span></span><span style="color: #990000">:</span> <span style="font-weight: bold"><span style="color: #0000FF">enum</span></span> Role <span style="color: #FF0000">{</span> Visitor <span style="color: #990000">=</span> <span style="color: #993399">0</span><span style="color: #990000">,</span> Admin <span style="color: #990000">=</span> <span style="color: #993399">1</span><span style="color: #990000">,</span> Alien <span style="color: #990000">=</span> <span style="color: #993399">42</span> <span style="color: #FF0000">}</span><span style="color: #990000">;</span> std<span style="color: #990000">::</span><span style="color: #008080">string</span> name<span style="color: #990000">;</span> std<span style="color: #990000">::</span><span style="color: #008080">string</span> password<span style="color: #990000">;</span> <span style="color: #008080">Role</span> role<span style="color: #990000">;</span> <span style="color: #009900">int</span> karma<span style="color: #990000">;</span> <span style="font-weight: bold"><span style="color: #0000FF">template</span></span><span style="color: #990000"><</span><span style="font-weight: bold"><span style="color: #0000FF">class</span></span> <span style="color: #008080">Action</span><span style="color: #990000">></span> <span style="color: #009900">void</span> <span style="font-weight: bold"><span style="color: #000000">persist</span></span><span style="color: #990000">(</span>Action<span style="color: #990000">&</span> a<span style="color: #990000">)</span> <span style="color: #FF0000">{</span> dbo<span style="color: #990000">::</span><span style="font-weight: bold"><span style="color: #000000">field</span></span><span style="color: #990000">(</span>a<span style="color: #990000">,</span> name<span style="color: #990000">,</span> <span style="color: #FF0000">"name"</span><span style="color: #990000">);</span> dbo<span style="color: #990000">::</span><span style="font-weight: bold"><span style="color: #000000">field</span></span><span style="color: #990000">(</span>a<span style="color: #990000">,</span> password<span style="color: #990000">,</span> <span style="color: #FF0000">"password"</span><span style="color: #990000">);</span> dbo<span style="color: #990000">::</span><span style="font-weight: bold"><span style="color: #000000">field</span></span><span style="color: #990000">(</span>a<span style="color: #990000">,</span> role<span style="color: #990000">,</span> <span style="color: #FF0000">"role"</span><span style="color: #990000">);</span> dbo<span style="color: #990000">::</span><span style="font-weight: bold"><span style="color: #000000">field</span></span><span style="color: #990000">(</span>a<span style="color: #990000">,</span> karma<span style="color: #990000">,</span> <span style="color: #FF0000">"karma"</span><span style="color: #990000">);</span> <span style="color: #FF0000">}</span> <span style="color: #FF0000">}</span><span style="color: #990000">;</span></tt></pre></div></div> <div class="paragraph"><p>This example shows how persistence support is defined for a C++ class. A template member method <tt>persist()</tt> is defined which serves as a persistence definition for the class. For each member in the class, a call to <tt><a href="../../reference/html/group__dbo.html#ga8a2b653ff57f1459dfa2e556badd71d6">Wt::Dbo::field()</a></tt> is used to map the field to a table column name.</p></div> <div class="paragraph"><p>As you may see, standard C++ types such as <tt>int</tt>, <tt>std::string</tt> and <tt>enum</tt> types are readily supported by the library (a full list of supported types can be found in the documentation of <tt><a href="../../reference/html/structWt_1_1Dbo_1_1sql__value__traits.html">Wt::Dbo::sql_value_traits<<em>T</em>></a></tt>). Support for other types can be added by specializing <tt><a href="../../reference/html/structWt_1_1Dbo_1_1sql__value__traits.html">Wt::Dbo::sql_value_traits<<em>T</em>></a></tt>. There is also support for built-in Wt types such as WDate, WDateTime, WTime and WString which can be enabled by including <Wt/Dbo/WtSqlTraits>.</p></div> <div class="paragraph"><p>The library defines a number of actions which will be applied to a database object using its <tt>persist()</tt> method, which applies it in turn to all its members. These actions will then read, update or insert database objects, create the schema, or propagate transaction outcomes.</p></div> <div class="admonitionblock"> <table><tr> <td class="icon"> <div class="title">Note</div> </td> <td class="content"> <div class="paragraph"><p>For brevity, our example uses public members. There is nothing that prevents you to encapsulate your state in private members and provide accessor methods. You may even define the persistence method in terms of accessor methods by differentiating between read and write actions.</p></div> </td> </tr></table> </div> </div> <h2 id="_a_first_session">3. A first session</h2> <div class="sectionbody"> <div class="paragraph"><p>Now that we have a mapping definition for our <tt>User</tt> class, we can start a database session, create our schema (if necessary) and add a user to the database.</p></div> <div class="paragraph"><p>Let us walk through the code for doing this.</p></div> <div class="listingblock"> <div class="title">(tutorial1.C continued)</div> <div class="content"><!-- Generator: GNU source-highlight 3.1 by Lorenzo Bettini http://www.lorenzobettini.it http://www.gnu.org/software/src-highlite --> <pre><tt><span style="color: #009900">void</span> <span style="font-weight: bold"><span style="color: #000000">run</span></span><span style="color: #990000">()</span> <span style="color: #FF0000">{</span> <span style="font-style: italic"><span style="color: #9A1900">/*</span></span> <span style="font-style: italic"><span style="color: #9A1900"> * Setup a session, would typically be done once at application startup.</span></span> <span style="font-style: italic"><span style="color: #9A1900"> */</span></span> dbo<span style="color: #990000">::</span>backend<span style="color: #990000">::</span><span style="color: #008080">Sqlite3</span> <span style="font-weight: bold"><span style="color: #000000">sqlite3</span></span><span style="color: #990000">(</span><span style="color: #FF0000">"blog.db"</span><span style="color: #990000">);</span> dbo<span style="color: #990000">::</span><span style="color: #008080">Session</span> session<span style="color: #990000">;</span> session<span style="color: #990000">.</span><span style="font-weight: bold"><span style="color: #000000">setConnection</span></span><span style="color: #990000">(</span>sqlite3<span style="color: #990000">);</span> <span style="color: #990000">...</span></tt></pre></div></div> <div class="paragraph"><p>The <tt><a href="../../reference/html/classWt_1_1Dbo_1_1Session.html">Session</a></tt> object is a long living object that provides access to our database objects. You will typically create a Session object for the entire lifetime of an application session, and one per user. None of the <tt><a href="../../reference/html/group__dbo.html">Wt::Dbo</a></tt> classes are thread safe (except for the connection pools), and session objects are not shared between sessions.</p></div> <div class="paragraph"><p>The lack of thread-safety is not simply a consequence of laziness on our part. It coincides with the promises made by transactional integrity on the database: you will not want to see the changes made by one session in another session while its transaction has not been committed (Read-Committed transaction isolation level). It might make sense however to implement a copy-on-write strategy in the future, to allow sharing of the bulk of database objects between sessions.</p></div> <div class="paragraph"><p>The session is given a connection which it may use to communicate with the database. A session will use a connection only during a transaction, and thus does not really need a dedicated connection. When you are planning for multiple concurrent sessions, it makes sense to use a connection pool instead, and a session may also be initialized with a reference to a <a href="../../reference/html/classWt_1_1Dbo_1_1Session.html#a63ab17e16c9b721c94e6e9f67c5c255d">connection pool</a>.</p></div> <div class="paragraph"><p><tt>Wt::Dbo</tt> uses an abstraction layer for database access, and currently supports <a href="../../reference/html/classWt_1_1Dbo_1_1backend_1_1Postgres.html">Postgres</a> and <a href="../../reference/html/classWt_1_1Dbo_1_1backend_1_1Sqlite3.html">Sqlite3</a> as backends.</p></div> <div class="listingblock"> <div class="title">(tutorial1.C continued)</div> <div class="content"><!-- Generator: GNU source-highlight 3.1 by Lorenzo Bettini http://www.lorenzobettini.it http://www.gnu.org/software/src-highlite --> <pre><tt> <span style="color: #990000">...</span> session<span style="color: #990000">.</span>mapClass<span style="color: #990000"><</span>User<span style="color: #990000">>(</span><span style="color: #FF0000">"user"</span><span style="color: #990000">);</span> <span style="font-style: italic"><span style="color: #9A1900">/*</span></span> <span style="font-style: italic"><span style="color: #9A1900"> * Try to create the schema (will fail if already exists).</span></span> <span style="font-style: italic"><span style="color: #9A1900"> */</span></span> session<span style="color: #990000">.</span><span style="font-weight: bold"><span style="color: #000000">createTables</span></span><span style="color: #990000">();</span> <span style="color: #990000">...</span></tt></pre></div></div> <div class="paragraph"><p>Next, we use <tt><a href="../../reference/html/classWt_1_1Dbo_1_1Session.html#a5886d450c052ae0ee15ab3c91e439229">mapClass()</a></tt> to register each database class with the session, indicating the database table onto which the class must be mapped.</p></div> <div class="paragraph"><p>Certainly during development, but also for initial deployment, it is convenient to let <tt>Wt::Dbo</tt> create or drop the database schema.</p></div> <div class="paragraph"><p>This generates the following SQL:</p></div> <div class="listingblock"> <div class="content"><!-- Generator: GNU source-highlight 3.1 by Lorenzo Bettini http://www.lorenzobettini.it http://www.gnu.org/software/src-highlite --> <pre><tt>begin transaction <span style="font-weight: bold"><span style="color: #0000FF">create</span></span> <span style="font-weight: bold"><span style="color: #0000FF">table</span></span> <span style="color: #FF0000">"user"</span> <span style="color: #990000">(</span> <span style="color: #FF0000">"id"</span> integer <span style="font-weight: bold"><span style="color: #0000FF">primary</span></span> <span style="font-weight: bold"><span style="color: #0000FF">key</span></span> autoincrement<span style="color: #990000">,</span> <span style="color: #FF0000">"version"</span> integer <span style="font-weight: bold"><span style="color: #0000FF">not</span></span> <span style="font-weight: bold"><span style="color: #0000FF">null</span></span><span style="color: #990000">,</span> <span style="color: #FF0000">"name"</span> <span style="color: #009900">text</span> <span style="font-weight: bold"><span style="color: #0000FF">not</span></span> <span style="font-weight: bold"><span style="color: #0000FF">null</span></span><span style="color: #990000">,</span> <span style="color: #FF0000">"password"</span> <span style="color: #009900">text</span> <span style="font-weight: bold"><span style="color: #0000FF">not</span></span> <span style="font-weight: bold"><span style="color: #0000FF">null</span></span><span style="color: #990000">,</span> <span style="color: #FF0000">"role"</span> integer <span style="font-weight: bold"><span style="color: #0000FF">not</span></span> <span style="font-weight: bold"><span style="color: #0000FF">null</span></span><span style="color: #990000">,</span> <span style="color: #FF0000">"karma"</span> integer <span style="font-weight: bold"><span style="color: #0000FF">not</span></span> <span style="font-weight: bold"><span style="color: #0000FF">null</span></span> <span style="color: #990000">)</span> commit transaction</tt></pre></div></div> <div class="paragraph"><p>As you can see, next to the four columns that map to C++ fields, by default, <tt>Wt::Dbo</tt> adds another two columns: <tt>id</tt> and <tt>version</tt>. The id is a surrogate primary key, and version is used for version-based optimistic locking. Since Wt 3.1.4, Wt::Dbo you can suppress the version field, and provide natural keys of any type instead of the surrogate primary key, see <a href="#customizing">Customizing the mapping</a>.</p></div> <div class="paragraph"><p>Finally, we can add a user to the database. All database operations happen within a transaction.</p></div> <div class="listingblock"> <div class="title">(tutorial1.C continued)</div> <div class="content"><!-- Generator: GNU source-highlight 3.1 by Lorenzo Bettini http://www.lorenzobettini.it http://www.gnu.org/software/src-highlite --> <pre><tt> <span style="color: #990000">...</span> <span style="font-style: italic"><span style="color: #9A1900">/*</span></span> <span style="font-style: italic"><span style="color: #9A1900"> * A unit of work happens always within a transaction.</span></span> <span style="font-style: italic"><span style="color: #9A1900"> */</span></span> dbo<span style="color: #990000">::</span><span style="color: #008080">Transaction</span> <span style="font-weight: bold"><span style="color: #000000">transaction</span></span><span style="color: #990000">(</span>session<span style="color: #990000">);</span> <span style="color: #008080">User</span> <span style="color: #990000">*</span>user <span style="color: #990000">=</span> <span style="font-weight: bold"><span style="color: #0000FF">new</span></span> <span style="font-weight: bold"><span style="color: #000000">User</span></span><span style="color: #990000">();</span> user<span style="color: #990000">-></span>name <span style="color: #990000">=</span> <span style="color: #FF0000">"Joe"</span><span style="color: #990000">;</span> user<span style="color: #990000">-></span>password <span style="color: #990000">=</span> <span style="color: #FF0000">"Secret"</span><span style="color: #990000">;</span> user<span style="color: #990000">-></span>role <span style="color: #990000">=</span> User<span style="color: #990000">::</span>Visitor<span style="color: #990000">;</span> user<span style="color: #990000">-></span>karma <span style="color: #990000">=</span> <span style="color: #993399">13</span><span style="color: #990000">;</span> dbo<span style="color: #990000">::</span><span style="color: #008080">ptr<User></span> userPtr <span style="color: #990000">=</span> session<span style="color: #990000">.</span><span style="font-weight: bold"><span style="color: #000000">add</span></span><span style="color: #990000">(</span>user<span style="color: #990000">);</span> transaction<span style="color: #990000">.</span><span style="font-weight: bold"><span style="color: #000000">commit</span></span><span style="color: #990000">();</span> <span style="color: #FF0000">}</span></tt></pre></div></div> <div class="paragraph"><p>A call to <a href="../../reference/html/classWt_1_1Dbo_1_1Session.html#a1ffa672cd86ff1ce6d59837b05081946">Session::add()</a> adds an object to the database. This call returns a <tt><a href="../../reference/html/classWt_1_1Dbo_1_1ptr.html">ptr<<em>Dbo</em>></a></tt> to reference a database object of type <tt><em>Dbo</em></tt>. This is a shared pointer which also keeps track of the persistence state of the referenced object. Within each session, a database object will be loaded at most once: the session keeps track of loaded database objects and returns an existing object whenever a query to the database requires this. When the last pointer to a database object goes out of scope, the <em>transient</em> (in-memory) copy of the database object is also deleted (unless it was modified, in which case the transient copy will only be be deleted after changes have been successfully committed to the database).</p></div> <div class="paragraph"><p>The session also keeps track of objects that have been modified and which need to be flushed (using SQL statements) to the database. Flushing happens automatically when committing the transaction, or whenever needed to maintain consistency between the transient objects and the database copy (e.g. before doing a query).</p></div> <div class="paragraph"><p>This generates the following SQL:</p></div> <div class="listingblock"> <div class="content"><!-- Generator: GNU source-highlight 3.1 by Lorenzo Bettini http://www.lorenzobettini.it http://www.gnu.org/software/src-highlite --> <pre><tt>begin transaction <span style="font-weight: bold"><span style="color: #0000FF">insert</span></span> <span style="font-weight: bold"><span style="color: #0000FF">into</span></span> <span style="color: #FF0000">"user"</span> <span style="color: #990000">(</span><span style="color: #FF0000">"version"</span><span style="color: #990000">,</span> <span style="color: #FF0000">"name"</span><span style="color: #990000">,</span> <span style="color: #FF0000">"password"</span><span style="color: #990000">,</span> <span style="color: #FF0000">"role"</span><span style="color: #990000">,</span> <span style="color: #FF0000">"karma"</span><span style="color: #990000">)</span> <span style="font-weight: bold"><span style="color: #0000FF">values</span></span> <span style="color: #990000">(?,</span> <span style="color: #990000">?,</span> <span style="color: #990000">?,</span> <span style="color: #990000">?,</span> <span style="color: #990000">?)</span> commit transaction</tt></pre></div></div> <div class="paragraph"><p>All SQL statements are prepared once (per connection) and reused later, which has the benefit of avoiding SQL injection problems, and allows potentially better performance.</p></div> </div> <h2 id="_querying_objects">4. Querying objects</h2> <div class="sectionbody"> <div class="paragraph"><p>There are two ways of querying the database. Database objects of a single <tt><em>Dbo</em></tt> class can be queried using <tt><a href="../../reference/html/classWt_1_1Dbo_1_1Session.html#a637d1031ffddd4bd7bbb55a616192ac7">Session::find<Dbo>(<em>condition</em>)</a></tt>:</p></div> <div class="listingblock"> <div class="title">(tutorial1.C continued)</div> <div class="content"><!-- Generator: GNU source-highlight 3.1 by Lorenzo Bettini http://www.lorenzobettini.it http://www.gnu.org/software/src-highlite --> <pre><tt>dbo<span style="color: #990000">::</span><span style="color: #008080">ptr<User></span> joe <span style="color: #990000">=</span> session<span style="color: #990000">.</span>find<span style="color: #990000"><</span>User<span style="color: #990000">>().</span><span style="font-weight: bold"><span style="color: #000000">where</span></span><span style="color: #990000">(</span><span style="color: #FF0000">"name = ?"</span><span style="color: #990000">).</span><span style="font-weight: bold"><span style="color: #000000">bind</span></span><span style="color: #990000">(</span><span style="color: #FF0000">"Joe"</span><span style="color: #990000">);</span> std<span style="color: #990000">::</span>cerr <span style="color: #990000"><<</span> <span style="color: #FF0000">"Joe has karma: "</span> <span style="color: #990000"><<</span> joe<span style="color: #990000">-></span>karma <span style="color: #990000"><<</span> std<span style="color: #990000">::</span>endl<span style="color: #990000">;</span></tt></pre></div></div> <div class="paragraph"><p>All queries use prepared statements with positional argument binding. The <tt>Session::find<T>()</tt> method returns a <tt><a href="../../reference/html/classWt_1_1Dbo_1_1Query.html">Query< ptr<T> ></a></tt> object. The Query object can be used to refine the search by defining Sql <tt>where</tt>, <tt>order by</tt> and <tt>group by</tt> definitions, and allows binding of parameters using <tt><a href="../../reference/html/classWt_1_1Dbo_1_1Query.html#abe3f72ee7e005c62e4428c399b57d6c5">Query::bind()</a></tt>. In this case the query should expect a single result and is casted directly to a database object pointer.</p></div> <div class="admonitionblock"> <table><tr> <td class="icon"> <div class="title">Note</div> </td> <td class="content"> <div class="paragraph"><p>Since Wt 3.1.3, the <tt>Query</tt> class has a second parameter <em>BindStrategy</em> which has two possible values, corresponding two different query implementations.</p></div> <div class="paragraph"><p>The default strategy is <em>DynamicBinding</em> and allows the query to be a long lived object associated with the session which may be run multiple times. It also allows you to modify the query by changing only the order order the limit/offsets.</p></div> <div class="paragraph"><p>An alternative strategy is <em>DirectBinding</em> which passes bound parameters directly on to an underlying prepared statement. This corresponds to the old behavior of a Query object. Such a query can be run only once, but has the benefit of having less (C++) overhead because the parameters values are directly passed on to the backend instead of stored within the query object.</p></div> </td> </tr></table> </div> <div class="paragraph"><p>The query formulated to the database is:</p></div> <div class="listingblock"> <div class="content"><!-- Generator: GNU source-highlight 3.1 by Lorenzo Bettini http://www.lorenzobettini.it http://www.gnu.org/software/src-highlite --> <pre><tt><span style="font-weight: bold"><span style="color: #0000FF">select</span></span> id<span style="color: #990000">,</span> version<span style="color: #990000">,</span> <span style="color: #FF0000">"name"</span><span style="color: #990000">,</span> <span style="color: #FF0000">"password"</span><span style="color: #990000">,</span> <span style="color: #FF0000">"role"</span><span style="color: #990000">,</span> <span style="color: #FF0000">"karma"</span> <span style="font-weight: bold"><span style="color: #0000FF">from</span></span> <span style="color: #FF0000">"user"</span> <span style="font-weight: bold"><span style="color: #0000FF">where</span></span> <span style="color: #990000">(</span>name <span style="color: #990000">=</span> <span style="color: #990000">?)</span></tt></pre></div></div> <div class="paragraph"><p>The more general way for querying uses <tt><a href="../../reference/html/classWt_1_1Dbo_1_1Session.html#a8712ff0d59f7bb6a4d2986ad9637cc9e">Session::query<Result>(<em>sql</em>)</a></tt>, which supports not only database objects as results. The query of above is equivalent to:</p></div> <div class="listingblock"> <div class="title">(tutorial1.C continued)</div> <div class="content"><!-- Generator: GNU source-highlight 3.1 by Lorenzo Bettini http://www.lorenzobettini.it http://www.gnu.org/software/src-highlite --> <pre><tt>dbo<span style="color: #990000">::</span><span style="color: #008080">ptr<User></span> joe2 <span style="color: #990000">=</span> session<span style="color: #990000">.</span>query<span style="color: #990000"><</span> dbo<span style="color: #990000">::</span>ptr<span style="color: #990000"><</span>User<span style="color: #990000">></span> <span style="color: #990000">>(</span><span style="color: #FF0000">"select u from user u"</span><span style="color: #990000">).</span><span style="font-weight: bold"><span style="color: #000000">where</span></span><span style="color: #990000">(</span><span style="color: #FF0000">"name = ?"</span><span style="color: #990000">).</span><span style="font-weight: bold"><span style="color: #000000">bind</span></span><span style="color: #990000">(</span><span style="color: #FF0000">"Joe"</span><span style="color: #990000">);</span></tt></pre></div></div> <div class="paragraph"><p>And this generates similar SQL:</p></div> <div class="listingblock"> <div class="content"><!-- Generator: GNU source-highlight 3.1 by Lorenzo Bettini http://www.lorenzobettini.it http://www.gnu.org/software/src-highlite --> <pre><tt><span style="font-weight: bold"><span style="color: #0000FF">select</span></span> u<span style="color: #990000">.</span>id<span style="color: #990000">,</span> u<span style="color: #990000">.</span>version<span style="color: #990000">,</span> u<span style="color: #990000">.</span><span style="color: #FF0000">"name"</span><span style="color: #990000">,</span> u<span style="color: #990000">.</span><span style="color: #FF0000">"password"</span><span style="color: #990000">,</span> u<span style="color: #990000">.</span><span style="color: #FF0000">"role"</span><span style="color: #990000">,</span> u<span style="color: #990000">.</span><span style="color: #FF0000">"karma"</span> <span style="font-weight: bold"><span style="color: #0000FF">from</span></span> user u <span style="font-weight: bold"><span style="color: #0000FF">where</span></span> <span style="color: #990000">(</span>name <span style="color: #990000">=</span> <span style="color: #990000">?)</span></tt></pre></div></div> <div class="paragraph"><p>The <tt><em>sql</em></tt> statement passed to the method may be arbitrary sql which returns results that are compatible with the <tt><em>Result</em></tt> type. The <tt><em>select</em></tt> part of the SQL query may be rewritten (as in the example above) to return the individual fields of a queried database object.</p></div> <div class="paragraph"><p>To illustrate that <tt>Session::query<Result>()</tt> may be used to return other types, consider the query below where an <tt>int</tt> result is returned.</p></div> <div class="listingblock"> <div class="title">(tutorial1.C continued)</div> <div class="content"><!-- Generator: GNU source-highlight 3.1 by Lorenzo Bettini http://www.lorenzobettini.it http://www.gnu.org/software/src-highlite --> <pre><tt><span style="color: #009900">int</span> count <span style="color: #990000">=</span> session<span style="color: #990000">.</span>query<span style="color: #990000"><</span><span style="color: #009900">int</span><span style="color: #990000">>(</span><span style="color: #FF0000">"select count(1) from user"</span><span style="color: #990000">).</span><span style="font-weight: bold"><span style="color: #000000">where</span></span><span style="color: #990000">(</span><span style="color: #FF0000">"name = ?"</span><span style="color: #990000">).</span><span style="font-weight: bold"><span style="color: #000000">bind</span></span><span style="color: #990000">(</span><span style="color: #FF0000">"Joe"</span><span style="color: #990000">);</span></tt></pre></div></div> <div class="paragraph"><p>The queries above were expecting unique results, but queries can also have multiple results. A <tt>Session::query<<em>Result</em>>()</tt> may therefore in general return a <tt>dbo::collection< <em>Result</em> ></tt> (for multiple results) and in the examples above they were coerced to a single unique <tt><em>Result</em></tt> for convenience. Similarly, <tt>Session::find<<em>Dbo</em>>()</tt> may return a <tt>collection< ptr<<em>Dbo</em>> ></tt> or a unique <tt><em>ptr<_Dbo</em>></tt>. If a unique result is asked, but the query found multiple results, a <tt><a href="../../reference/html/classWt_1_1Dbo_1_1NoUniqueResultException.html">NoUniqueResultException</a></tt> will be thrown.</p></div> <div class="paragraph"><p><tt><a href="../../reference/html/classWt_1_1Dbo_1_1collection.html">collection<<em>T</em>></a></tt> is an STL-compatible collection which has iterators that implement the <tt>InputIterator</tt> requirements. Thus, you can only iterate through the results of a collection once. After the results have been iterated the <tt>collection</tt> can no longer be used (but the <tt>Query</tt> object can be reused unless a <em>DirectBinding</em> bind strategy was used).</p></div> <div class="paragraph"><p>The following code shows how you may multiple results of a query may be iterated:</p></div> <div class="listingblock"> <div class="title">(tutorial1.C continued)</div> <div class="content"><!-- Generator: GNU source-highlight 3.1 by Lorenzo Bettini http://www.lorenzobettini.it http://www.gnu.org/software/src-highlite --> <pre><tt><span style="font-weight: bold"><span style="color: #0000FF">typedef</span></span> dbo<span style="color: #990000">::</span><span style="color: #008080">collection< dbo::ptr<User> ></span> Users<span style="color: #990000">;</span> <span style="color: #008080">Users</span> users <span style="color: #990000">=</span> session<span style="color: #990000">.</span>find<span style="color: #990000"><</span>User<span style="color: #990000">>();</span> std<span style="color: #990000">::</span>cerr <span style="color: #990000"><<</span> <span style="color: #FF0000">"We have "</span> <span style="color: #990000"><<</span> users<span style="color: #990000">.</span><span style="font-weight: bold"><span style="color: #000000">size</span></span><span style="color: #990000">()</span> <span style="color: #990000"><<</span> <span style="color: #FF0000">" users:"</span> <span style="color: #990000"><<</span> std<span style="color: #990000">::</span>endl<span style="color: #990000">;</span> <span style="font-weight: bold"><span style="color: #0000FF">for</span></span> <span style="color: #990000">(</span>Users<span style="color: #990000">::</span><span style="color: #008080">const_iterator</span> i <span style="color: #990000">=</span> users<span style="color: #990000">.</span><span style="font-weight: bold"><span style="color: #000000">begin</span></span><span style="color: #990000">();</span> i <span style="color: #990000">!=</span> users<span style="color: #990000">.</span><span style="font-weight: bold"><span style="color: #000000">end</span></span><span style="color: #990000">();</span> <span style="color: #990000">++</span>i<span style="color: #990000">)</span> std<span style="color: #990000">::</span>cerr <span style="color: #990000"><<</span> <span style="color: #FF0000">" user "</span> <span style="color: #990000"><<</span> <span style="color: #990000">(*</span>i<span style="color: #990000">)-></span>name <span style="color: #990000"><<</span> <span style="color: #FF0000">" with karma of "</span> <span style="color: #990000"><<</span> <span style="color: #990000">(*</span>i<span style="color: #990000">)-></span>karma <span style="color: #990000"><<</span> std<span style="color: #990000">::</span>endl<span style="color: #990000">;</span></tt></pre></div></div> <div class="paragraph"><p>This code will perform two database queries: one for the call to <tt>collection::size()</tt> and one for iterating the results:</p></div> <div class="listingblock"> <div class="content"><!-- Generator: GNU source-highlight 3.1 by Lorenzo Bettini http://www.lorenzobettini.it http://www.gnu.org/software/src-highlite --> <pre><tt><span style="font-weight: bold"><span style="color: #0000FF">select</span></span> count<span style="color: #990000">(</span><span style="color: #993399">1</span><span style="color: #990000">)</span> <span style="font-weight: bold"><span style="color: #0000FF">from</span></span> <span style="color: #FF0000">"user"</span> <span style="font-weight: bold"><span style="color: #0000FF">select</span></span> id<span style="color: #990000">,</span> version<span style="color: #990000">,</span> <span style="color: #FF0000">"name"</span><span style="color: #990000">,</span> <span style="color: #FF0000">"password"</span><span style="color: #990000">,</span> <span style="color: #FF0000">"role"</span><span style="color: #990000">,</span> <span style="color: #FF0000">"karma"</span> <span style="font-weight: bold"><span style="color: #0000FF">from</span></span> <span style="color: #FF0000">"user"</span></tt></pre></div></div> <div class="admonitionblock"> <table><tr> <td class="icon"> <div class="title">Warning</div> </td> <td class="content"> <div class="paragraph"><p>A query uses a prepared statement to execute, and prepares a new statement if no statement was yet prepared for that query. Because a prepared statement is usually not reentrant and at the same time a query will use an existing statement if one exists, you need to be careful to not have two collections with the same statement busy at the same time. Thus while iterating the results of a query you cannot use that same query again. Therefore it may be necessary to copy the results into a standard container (such as <tt>std::vector</tt>) before iterating them. Since Wt 3.1.3, concurrent use will be detected and an exception will be thrown saying:</p></div> <div class="literalblock"> <div class="content"> <pre><tt>A collection for '...' is already in use. Reentrant statement use is not yet implemented.</tt></pre> </div></div> <div class="paragraph"><p>We plan to remove this restriction in later versions, cloning prepared statements as necessary.</p></div> </td> </tr></table> </div> </div> <h2 id="_updating_objects">5. Updating objects</h2> <div class="sectionbody"> <div class="paragraph"><p>Unlike most other smart pointers, <tt>ptr<<em>Dbo</em>></tt> is read-only by default: it returns a <tt>const <em>Dbo</em>*</tt>. To modify a database object, you need to call the <tt><a href="../../reference/html/classWt_1_1Dbo_1_1ptr.html#a8f9f4ec6ed2bd8febe8b62525c9576da">ptr::modify()</a></tt> method, which returns a non-const object. This mark the object as dirty and the modifications will later be synchronized to the database.</p></div> <div class="listingblock"> <div class="title">(tutorial1.C continued)</div> <div class="content"><!-- Generator: GNU source-highlight 3.1 by Lorenzo Bettini http://www.lorenzobettini.it http://www.gnu.org/software/src-highlite --> <pre><tt>dbo<span style="color: #990000">::</span><span style="color: #008080">ptr<User></span> joe <span style="color: #990000">=</span> session<span style="color: #990000">.</span>find<span style="color: #990000"><</span>User<span style="color: #990000">>().</span><span style="font-weight: bold"><span style="color: #000000">where</span></span><span style="color: #990000">(</span><span style="color: #FF0000">"name = ?"</span><span style="color: #990000">).</span><span style="font-weight: bold"><span style="color: #000000">bind</span></span><span style="color: #990000">(</span><span style="color: #FF0000">"Joe"</span><span style="color: #990000">);</span> joe<span style="color: #990000">.</span><span style="font-weight: bold"><span style="color: #000000">modify</span></span><span style="color: #990000">()-></span>karma<span style="color: #990000">++;</span> joe<span style="color: #990000">.</span><span style="font-weight: bold"><span style="color: #000000">modify</span></span><span style="color: #990000">()-></span>password <span style="color: #990000">=</span> <span style="color: #FF0000">"public"</span><span style="color: #990000">;</span></tt></pre></div></div> <div class="paragraph"><p>Database synchronization does not happen instantaneously, instead, they are delayed until explicitly asked, using <tt><a href="../../reference/html/classWt_1_1Dbo_1_1ptr.html#aaf91f5ef8bcdabfafcd9abb4182625bd">ptr<<em>Dbo</em>>::flush()</a></tt> or <tt><a href="../../reference/html/classWt_1_1Dbo_1_1Session.html#ab896d119f1e0fb79adffe2282b92ef17">Session::flush()</a></tt>, until a query is executed whose results may be affected by the changes made, or until the transaction is committed.</p></div> <div class="paragraph"><p>The previous code will generate the following SQL:</p></div> <div class="listingblock"> <div class="content"><!-- Generator: GNU source-highlight 3.1 by Lorenzo Bettini http://www.lorenzobettini.it http://www.gnu.org/software/src-highlite --> <pre><tt><span style="font-weight: bold"><span style="color: #0000FF">select</span></span> id<span style="color: #990000">,</span> version<span style="color: #990000">,</span> <span style="color: #FF0000">"name"</span><span style="color: #990000">,</span> <span style="color: #FF0000">"password"</span><span style="color: #990000">,</span> <span style="color: #FF0000">"role"</span><span style="color: #990000">,</span> <span style="color: #FF0000">"karma"</span> <span style="font-weight: bold"><span style="color: #0000FF">from</span></span> <span style="color: #FF0000">"user"</span> <span style="font-weight: bold"><span style="color: #0000FF">where</span></span> <span style="color: #990000">(</span>name <span style="color: #990000">=</span> <span style="color: #990000">?)</span> <span style="font-weight: bold"><span style="color: #0000FF">update</span></span> <span style="color: #FF0000">"user"</span> <span style="font-weight: bold"><span style="color: #0000FF">set</span></span> <span style="color: #FF0000">"version"</span> <span style="color: #990000">=</span> <span style="color: #990000">?,</span> <span style="color: #FF0000">"name"</span> <span style="color: #990000">=</span> <span style="color: #990000">?,</span> <span style="color: #FF0000">"password"</span> <span style="color: #990000">=</span> <span style="color: #990000">?,</span> <span style="color: #FF0000">"role"</span> <span style="color: #990000">=</span> <span style="color: #990000">?,</span> <span style="color: #FF0000">"karma"</span> <span style="color: #990000">=</span> <span style="color: #990000">?</span> <span style="font-weight: bold"><span style="color: #0000FF">where</span></span> <span style="color: #FF0000">"id"</span> <span style="color: #990000">=</span> <span style="color: #990000">?</span> <span style="font-weight: bold"><span style="color: #0000FF">and</span></span> <span style="color: #FF0000">"version"</span> <span style="color: #990000">=</span> <span style="color: #990000">?</span></tt></pre></div></div> <div class="paragraph"><p>We already saw how using <tt>Session::add(ptr<<em>Dbo</em>>)</tt>, we added a new object to the database. The opposite operation is <tt><a href="../../reference/html/classWt_1_1Dbo_1_1ptr.html#af4b26afebd56abc42005ef9f954d8fee">ptr<<em>Dbo</em>>::remove()</a></tt>: it deletes the object in the database.</p></div> <div class="listingblock"> <div class="title">(tutorial1.C continued)</div> <div class="content"><!-- Generator: GNU source-highlight 3.1 by Lorenzo Bettini http://www.lorenzobettini.it http://www.gnu.org/software/src-highlite --> <pre><tt>dbo<span style="color: #990000">::</span><span style="color: #008080">ptr<User></span> joe <span style="color: #990000">=</span> session<span style="color: #990000">.</span>find<span style="color: #990000"><</span>User<span style="color: #990000">>().</span><span style="font-weight: bold"><span style="color: #000000">where</span></span><span style="color: #990000">(</span><span style="color: #FF0000">"name = ?"</span><span style="color: #990000">).</span><span style="font-weight: bold"><span style="color: #000000">bind</span></span><span style="color: #990000">(</span><span style="color: #FF0000">"Joe"</span><span style="color: #990000">);</span> joe<span style="color: #990000">.</span><span style="font-weight: bold"><span style="color: #000000">remove</span></span><span style="color: #990000">();</span></tt></pre></div></div> <div class="paragraph"><p>After removing an object, the transient object can still be used, and can even be re-added to the database.</p></div> <div class="admonitionblock"> <table><tr> <td class="icon"> <div class="title">Note</div> </td> <td class="content"> <div class="paragraph"><p>Like <tt>modify()</tt>, also the <tt>add()</tt> and <tt>remove()</tt> operations defer synchronization with the database, and therefore the following code does not actually have any effect on the database:</p></div> <div class="listingblock"> <div class="title">(tutorial1.C continued)</div> <div class="content"><!-- Generator: GNU source-highlight 3.1 by Lorenzo Bettini http://www.lorenzobettini.it http://www.gnu.org/software/src-highlite --> <pre><tt>dbo<span style="color: #990000">::</span><span style="color: #008080">ptr<User></span> silly <span style="color: #990000">=</span> session<span style="color: #990000">.</span><span style="font-weight: bold"><span style="color: #000000">add</span></span><span style="color: #990000">(</span><span style="font-weight: bold"><span style="color: #0000FF">new</span></span> <span style="font-weight: bold"><span style="color: #000000">User</span></span><span style="color: #990000">());</span> silly<span style="color: #990000">.</span><span style="font-weight: bold"><span style="color: #000000">modify</span></span><span style="color: #990000">()-></span>name <span style="color: #990000">=</span> <span style="color: #FF0000">"Silly"</span><span style="color: #990000">;</span> silly<span style="color: #990000">.</span><span style="font-weight: bold"><span style="color: #000000">remove</span></span><span style="color: #990000">();</span></tt></pre></div></div> </td> </tr></table> </div> </div> <h2 id="_mapping_relations">6. Mapping relations</h2> <div class="sectionbody"> <h3 id="_em_many_to_one_em_relations">6.1. <em>Many-to-One</em> relations</h3><div style="clear:left"></div> <div class="paragraph"><p>Let’s add posts to our blogging example, and define a Man-to-One relation between posts and users. In the code below, we limit ourselves to the statements important for defining the relationship.</p></div> <div class="listingblock"> <div class="title">Many-to-One relation (tutorial2.C)</div> <div class="content"><!-- Generator: GNU source-highlight 3.1 by Lorenzo Bettini http://www.lorenzobettini.it http://www.gnu.org/software/src-highlite --> <pre><tt><span style="font-weight: bold"><span style="color: #000080">#include</span></span> <span style="color: #FF0000"><Wt/Dbo/Dbo></span> <span style="font-weight: bold"><span style="color: #000080">#include</span></span> <span style="color: #FF0000"><string></span> <span style="font-weight: bold"><span style="color: #0000FF">namespace</span></span> dbo <span style="color: #990000">=</span> Wt<span style="color: #990000">::</span>Dbo<span style="color: #990000">;</span> <span style="font-weight: bold"><span style="color: #0000FF">class</span></span> <span style="color: #008080">User</span><span style="color: #990000">;</span> <span style="font-weight: bold"><span style="color: #0000FF">class</span></span> <span style="color: #008080">Post</span> <span style="color: #FF0000">{</span> <span style="font-weight: bold"><span style="color: #0000FF">public</span></span><span style="color: #990000">:</span> <span style="color: #990000">...</span> dbo<span style="color: #990000">::</span><span style="color: #008080">ptr<User></span> user<span style="color: #990000">;</span> <span style="font-weight: bold"><span style="color: #0000FF">template</span></span><span style="color: #990000"><</span><span style="font-weight: bold"><span style="color: #0000FF">class</span></span> <span style="color: #008080">Action</span><span style="color: #990000">></span> <span style="color: #009900">void</span> <span style="font-weight: bold"><span style="color: #000000">persist</span></span><span style="color: #990000">(</span>Action<span style="color: #990000">&</span> a<span style="color: #990000">)</span> <span style="color: #FF0000">{</span> <span style="color: #990000">...</span> dbo<span style="color: #990000">::</span><span style="font-weight: bold"><span style="color: #000000">belongsTo</span></span><span style="color: #990000">(</span>a<span style="color: #990000">,</span> user<span style="color: #990000">,</span> <span style="color: #FF0000">"user"</span><span style="color: #990000">);</span> <span style="color: #FF0000">}</span> <span style="color: #FF0000">}</span><span style="color: #990000">;</span> <span style="font-weight: bold"><span style="color: #0000FF">class</span></span> <span style="color: #008080">User</span> <span style="color: #FF0000">{</span> <span style="font-weight: bold"><span style="color: #0000FF">public</span></span><span style="color: #990000">:</span> <span style="color: #990000">...</span> dbo<span style="color: #990000">::</span><span style="color: #008080">collection< dbo::ptr<Post> ></span> posts<span style="color: #990000">;</span> <span style="font-weight: bold"><span style="color: #0000FF">template</span></span><span style="color: #990000"><</span><span style="font-weight: bold"><span style="color: #0000FF">class</span></span> <span style="color: #008080">Action</span><span style="color: #990000">></span> <span style="color: #009900">void</span> <span style="font-weight: bold"><span style="color: #000000">persist</span></span><span style="color: #990000">(</span>Action<span style="color: #990000">&</span> a<span style="color: #990000">)</span> <span style="color: #FF0000">{</span> <span style="color: #990000">...</span> dbo<span style="color: #990000">::</span><span style="font-weight: bold"><span style="color: #000000">hasMany</span></span><span style="color: #990000">(</span>a<span style="color: #990000">,</span> posts<span style="color: #990000">,</span> dbo<span style="color: #990000">::</span>ManyToOne<span style="color: #990000">,</span> <span style="color: #FF0000">"user"</span><span style="color: #990000">);</span> <span style="color: #FF0000">}</span> <span style="color: #FF0000">}</span><span style="color: #990000">;</span></tt></pre></div></div> <div class="paragraph"><p>At the <em>Many</em>-side, we add a reference to a user, and in the <tt>persist()</tt> method we call <tt><a href="../../reference/html/group__dbo.html#gaf1a3f4231d2064b86eb3903003527ddd">belongsTo()</a></tt>. This allows us to reference the user to which this post belongs. The last argument will correspond to the name of the database column which defines the relationship.</p></div> <div class="paragraph"><p>At the <em>One</em>-side, we add a collection of posts, and in the <tt>persist()</tt> method we call <tt><a href="../../reference/html/group__dbo.html#ga1a086b583fb150dbd4c5b4ba92bc177f">hasMany()</a></tt>. The join field must be the same name as in reciproce belongsTo() method call.</p></div> <div class="paragraph"><p>If we add the Post class too to our session using <tt>Session::mapClass()</tt>, and create the schema, the following SQL is generated:</p></div> <div class="listingblock"> <div class="content"><!-- Generator: GNU source-highlight 3.1 by Lorenzo Bettini http://www.lorenzobettini.it http://www.gnu.org/software/src-highlite --> <pre><tt><span style="font-weight: bold"><span style="color: #0000FF">create</span></span> <span style="font-weight: bold"><span style="color: #0000FF">table</span></span> <span style="color: #FF0000">"user"</span> <span style="color: #990000">(</span> <span style="color: #990000">...</span> <span style="font-style: italic"><span style="color: #9A1900">-- table user is unaffected by the relationship</span></span> <span style="color: #990000">);</span> <span style="font-weight: bold"><span style="color: #0000FF">create</span></span> <span style="font-weight: bold"><span style="color: #0000FF">table</span></span> <span style="color: #FF0000">"post"</span> <span style="color: #990000">(</span> <span style="color: #990000">...</span> <span style="color: #FF0000">"user_id"</span> <span style="color: #009900">bigint</span><span style="color: #990000">,</span> constraint <span style="color: #FF0000">"fk_post_user"</span> <span style="font-weight: bold"><span style="color: #0000FF">foreign</span></span> <span style="font-weight: bold"><span style="color: #0000FF">key</span></span> <span style="color: #990000">(</span><span style="color: #FF0000">"user_id"</span><span style="color: #990000">)</span> <span style="font-weight: bold"><span style="color: #0000FF">references</span></span> <span style="color: #FF0000">"user"</span> <span style="color: #990000">(</span><span style="color: #FF0000">"id"</span><span style="color: #990000">)</span> <span style="color: #990000">)</span></tt></pre></div></div> <div class="paragraph"><p>Note the <tt>user_id</tt> field which corresponds to the join name “user”.</p></div> <div class="paragraph"><p>At the <em>Many</em>-side, you may read or write the <tt>ptr</tt> to set a user to which this post belongs.</p></div> <div class="paragraph"><p>The collection at the <em>One</em>-side allows us to retrieve all associated elements, and also insert() and remove() elements, which has the same effect as setting the <tt>ptr</tt> on the <em>Many</em>-side.</p></div> <div class="paragraph"><p>Example:</p></div> <div class="listingblock"> <div class="title">(tutorial2.C continued)</div> <div class="content"><!-- Generator: GNU source-highlight 3.1 by Lorenzo Bettini http://www.lorenzobettini.it http://www.gnu.org/software/src-highlite --> <pre><tt>dbo<span style="color: #990000">::</span><span style="color: #008080">ptr<Post></span> post <span style="color: #990000">=</span> session<span style="color: #990000">.</span><span style="font-weight: bold"><span style="color: #000000">add</span></span><span style="color: #990000">(</span><span style="font-weight: bold"><span style="color: #0000FF">new</span></span> <span style="font-weight: bold"><span style="color: #000000">Post</span></span><span style="color: #990000">());</span> post<span style="color: #990000">.</span><span style="font-weight: bold"><span style="color: #000000">modify</span></span><span style="color: #990000">()-></span>user <span style="color: #990000">=</span> joe<span style="color: #990000">;</span> <span style="font-style: italic"><span style="color: #9A1900">// or joe.modify()->posts.insert(post);</span></span> <span style="font-style: italic"><span style="color: #9A1900">// will print 'Joe has 1 post(s).'</span></span> std<span style="color: #990000">::</span>cerr <span style="color: #990000"><<</span> <span style="color: #FF0000">"Joe has "</span> <span style="color: #990000"><<</span> joe<span style="color: #990000">-></span>posts<span style="color: #990000">.</span><span style="font-weight: bold"><span style="color: #000000">size</span></span><span style="color: #990000">()</span> <span style="color: #990000"><<</span> <span style="color: #FF0000">" post(s)."</span> <span style="color: #990000"><<</span> std<span style="color: #990000">::</span>endl<span style="color: #990000">;</span></tt></pre></div></div> <div class="paragraph"><p>As you can see, as soon as <em>joe</em> is set as <em>user</em> for the new post, the post is reflected in the <em>posts</em> collection of <em>joe</em>, and vice-versa.</p></div> <div class="admonitionblock"> <table><tr> <td class="icon"> <div class="title">Warning</div> </td> <td class="content"> <div class="paragraph"><p>The collection uses a prepared statement to execute. Collections will try to share a single prepared statement, but prepared statements are usually not reentrant. As a result, you need to be careful to not have two collections with the same statement busy at the same time. Thus while iterating a collection, you need to be sure you will not reentrantly iterate the same collection (of the same or another object). Therefore it may be necessary to copy the results into a standard container (such as <tt>std::vector</tt>) before iterating them.</p></div> <div class="paragraph"><p>We plan to remove this restriction in later versions, cloning prepared statements as necessary.</p></div> </td> </tr></table> </div> <h3 id="_em_many_to_many_em_relations">6.2. <em>Many-to-Many</em> relations</h3><div style="clear:left"></div> <div class="paragraph"><p>To illustrate <em>Many-to-Many</em> relations, we will add tags to our blogging example, and define an <em>Many-to-Many</em> relation between posts and tags. In the code below, we again limit ourselves to the statements important for defining the relationship.</p></div> <div class="listingblock"> <div class="title">Many-to-Many relation (tutorial2.C)</div> <div class="content"><!-- Generator: GNU source-highlight 3.1 by Lorenzo Bettini http://www.lorenzobettini.it http://www.gnu.org/software/src-highlite --> <pre><tt><span style="font-weight: bold"><span style="color: #000080">#include</span></span> <span style="color: #FF0000"><Wt/Dbo/Dbo></span> <span style="font-weight: bold"><span style="color: #000080">#include</span></span> <span style="color: #FF0000"><string></span> <span style="font-weight: bold"><span style="color: #0000FF">namespace</span></span> dbo <span style="color: #990000">=</span> Wt<span style="color: #990000">::</span>Dbo<span style="color: #990000">;</span> <span style="font-weight: bold"><span style="color: #0000FF">class</span></span> <span style="color: #008080">Tag</span><span style="color: #990000">;</span> <span style="font-weight: bold"><span style="color: #0000FF">class</span></span> <span style="color: #008080">Post</span> <span style="color: #FF0000">{</span> <span style="font-weight: bold"><span style="color: #0000FF">public</span></span><span style="color: #990000">:</span> <span style="color: #990000">...</span> dbo<span style="color: #990000">::</span><span style="color: #008080">collection< dbo::ptr<Tag> ></span> tags<span style="color: #990000">;</span> <span style="font-weight: bold"><span style="color: #0000FF">template</span></span><span style="color: #990000"><</span><span style="font-weight: bold"><span style="color: #0000FF">class</span></span> <span style="color: #008080">Action</span><span style="color: #990000">></span> <span style="color: #009900">void</span> <span style="font-weight: bold"><span style="color: #000000">persist</span></span><span style="color: #990000">(</span>Action<span style="color: #990000">&</span> a<span style="color: #990000">)</span> <span style="color: #FF0000">{</span> <span style="color: #990000">...</span> dbo<span style="color: #990000">::</span><span style="font-weight: bold"><span style="color: #000000">hasMany</span></span><span style="color: #990000">(</span>a<span style="color: #990000">,</span> tags<span style="color: #990000">,</span> dbo<span style="color: #990000">::</span>ManyToMany<span style="color: #990000">,</span> <span style="color: #FF0000">"post_tags"</span><span style="color: #990000">);</span> <span style="color: #FF0000">}</span> <span style="color: #FF0000">}</span><span style="color: #990000">;</span> <span style="font-weight: bold"><span style="color: #0000FF">class</span></span> <span style="color: #008080">Tag</span> <span style="color: #FF0000">{</span> <span style="font-weight: bold"><span style="color: #0000FF">public</span></span><span style="color: #990000">:</span> <span style="color: #990000">...</span> dbo<span style="color: #990000">::</span><span style="color: #008080">collection< dbo::ptr<Post> ></span> posts<span style="color: #990000">;</span> <span style="font-weight: bold"><span style="color: #0000FF">template</span></span><span style="color: #990000"><</span><span style="font-weight: bold"><span style="color: #0000FF">class</span></span> <span style="color: #008080">Action</span><span style="color: #990000">></span> <span style="color: #009900">void</span> <span style="font-weight: bold"><span style="color: #000000">persist</span></span><span style="color: #990000">(</span>Action<span style="color: #990000">&</span> a<span style="color: #990000">)</span> <span style="color: #FF0000">{</span> <span style="color: #990000">...</span> dbo<span style="color: #990000">::</span><span style="font-weight: bold"><span style="color: #000000">hasMany</span></span><span style="color: #990000">(</span>a<span style="color: #990000">,</span> posts<span style="color: #990000">,</span> dbo<span style="color: #990000">::</span>ManyToMany<span style="color: #990000">,</span> <span style="color: #FF0000">"post_tags"</span><span style="color: #990000">);</span> <span style="color: #FF0000">}</span> <span style="color: #FF0000">}</span><span style="color: #990000">;</span></tt></pre></div></div> <div class="paragraph"><p>As expected, the relationship is reflected in almost the same way in both classes: they both have a <tt>collection</tt> of database objects of the related class, and in the <tt>persist()</tt> method we call <tt>hasMany()</tt>. The join field in this case will correspond to the name of a join-table used to persist the relation.</p></div> <div class="paragraph"><p>Adding the Post class to our session using <tt>Session::mapClass()</tt>, we now get the following SQL for creating the schema:</p></div> <div class="listingblock"> <div class="content"><!-- Generator: GNU source-highlight 3.1 by Lorenzo Bettini http://www.lorenzobettini.it http://www.gnu.org/software/src-highlite --> <pre><tt><span style="font-weight: bold"><span style="color: #0000FF">create</span></span> <span style="font-weight: bold"><span style="color: #0000FF">table</span></span> <span style="color: #FF0000">"post"</span> <span style="color: #990000">(</span> <span style="color: #990000">...</span> <span style="font-style: italic"><span style="color: #9A1900">-- table post is unaffected by the relationship</span></span> <span style="color: #990000">)</span> <span style="font-weight: bold"><span style="color: #0000FF">create</span></span> <span style="font-weight: bold"><span style="color: #0000FF">table</span></span> <span style="color: #FF0000">"tag"</span> <span style="color: #990000">(</span> <span style="color: #990000">...</span> <span style="font-style: italic"><span style="color: #9A1900">-- table tag is unaffected by the relationship</span></span> <span style="color: #990000">)</span> <span style="font-weight: bold"><span style="color: #0000FF">create</span></span> <span style="font-weight: bold"><span style="color: #0000FF">table</span></span> <span style="color: #FF0000">"post_tags"</span> <span style="color: #990000">(</span> <span style="color: #FF0000">"post_id"</span> <span style="color: #009900">bigint</span> <span style="font-weight: bold"><span style="color: #0000FF">not</span></span> <span style="font-weight: bold"><span style="color: #0000FF">null</span></span><span style="color: #990000">,</span> <span style="color: #FF0000">"tag_id"</span> <span style="color: #009900">bigint</span> <span style="font-weight: bold"><span style="color: #0000FF">not</span></span> <span style="font-weight: bold"><span style="color: #0000FF">null</span></span><span style="color: #990000">,</span> <span style="font-weight: bold"><span style="color: #0000FF">primary</span></span> <span style="font-weight: bold"><span style="color: #0000FF">key</span></span> <span style="color: #990000">(</span><span style="color: #FF0000">"post_id"</span><span style="color: #990000">,</span> <span style="color: #FF0000">"tag_id"</span><span style="color: #990000">),</span> constraint <span style="color: #FF0000">"fk_post_tags_key1"</span> <span style="font-weight: bold"><span style="color: #0000FF">foreign</span></span> <span style="font-weight: bold"><span style="color: #0000FF">key</span></span> <span style="color: #990000">(</span><span style="color: #FF0000">"post_id"</span><span style="color: #990000">)</span> <span style="font-weight: bold"><span style="color: #0000FF">references</span></span> <span style="color: #FF0000">"post"</span> <span style="color: #990000">(</span><span style="color: #FF0000">"id"</span><span style="color: #990000">),</span> constraint <span style="color: #FF0000">"fk_post_tags_key2"</span> <span style="font-weight: bold"><span style="color: #0000FF">foreign</span></span> <span style="font-weight: bold"><span style="color: #0000FF">key</span></span> <span style="color: #990000">(</span><span style="color: #FF0000">"tag_id"</span><span style="color: #990000">)</span> <span style="font-weight: bold"><span style="color: #0000FF">references</span></span> <span style="color: #FF0000">"tag"</span> <span style="color: #990000">(</span><span style="color: #FF0000">"id"</span><span style="color: #990000">)</span> <span style="color: #990000">)</span> <span style="font-weight: bold"><span style="color: #0000FF">create</span></span> <span style="font-weight: bold"><span style="color: #0000FF">index</span></span> <span style="color: #FF0000">"post_tags_post"</span> <span style="font-weight: bold"><span style="color: #0000FF">on</span></span> <span style="color: #FF0000">"post_tags"</span> <span style="color: #990000">(</span><span style="color: #FF0000">"post_id"</span><span style="color: #990000">)</span> <span style="font-weight: bold"><span style="color: #0000FF">create</span></span> <span style="font-weight: bold"><span style="color: #0000FF">index</span></span> <span style="color: #FF0000">"post_tags_tag"</span> <span style="font-weight: bold"><span style="color: #0000FF">on</span></span> <span style="color: #FF0000">"post_tags"</span> <span style="color: #990000">(</span><span style="color: #FF0000">"tag_id"</span><span style="color: #990000">)</span></tt></pre></div></div> <div class="paragraph"><p>The collection at either side of the <em>Many-to-Many</em> relation allows us to retrieve all associated elements. Unlike a collection in a <em>Many-to-One</em> relation however, we may now also <tt><a href="../../reference/html/classWt_1_1Dbo_1_1collection.html#7b497add0d6ac2b1e098820f9ee64c93">insert()</a></tt> and <tt><a href="../../reference/html/classWt_1_1Dbo_1_1collection.html#35d046e991b3fec59af85d501fd49e51">erase()</a></tt> items from the collection. To define a relation between a post and a tag, you need to add the post to the tag’s <em>posts</em> collection, or the tag to the post’s <em>tags</em> collection. You may not do both! The change will automatically be reflected in the reciproce collection. Likewise, to undo the relation between a post and a tag, you should remove the tag from the post’s <em>tags</em> collection, or the post from the tag’s <em>posts</em> collection, but not both.</p></div> <div class="paragraph"><p>Example:</p></div> <div class="listingblock"> <div class="title">(tutorial2.C continued)</div> <div class="content"><!-- Generator: GNU source-highlight 3.1 by Lorenzo Bettini http://www.lorenzobettini.it http://www.gnu.org/software/src-highlite --> <pre><tt>dbo<span style="color: #990000">::</span><span style="color: #008080">ptr<Post></span> post <span style="color: #990000">=</span> <span style="color: #990000">...</span> dbo<span style="color: #990000">::</span><span style="color: #008080">ptr<Tag></span> cooking <span style="color: #990000">=</span> session<span style="color: #990000">.</span><span style="font-weight: bold"><span style="color: #000000">add</span></span><span style="color: #990000">(</span><span style="font-weight: bold"><span style="color: #0000FF">new</span></span> <span style="font-weight: bold"><span style="color: #000000">Tag</span></span><span style="color: #990000">());</span> cooking<span style="color: #990000">.</span><span style="font-weight: bold"><span style="color: #000000">modify</span></span><span style="color: #990000">()-></span>name <span style="color: #990000">=</span> <span style="color: #FF0000">"Cooking"</span><span style="color: #990000">;</span> post<span style="color: #990000">.</span><span style="font-weight: bold"><span style="color: #000000">modify</span></span><span style="color: #990000">()-></span>tags<span style="color: #990000">.</span><span style="font-weight: bold"><span style="color: #000000">insert</span></span><span style="color: #990000">(</span>cooking<span style="color: #990000">);</span> <span style="font-style: italic"><span style="color: #9A1900">// will print '1 post(s) tagged with Cooking.'</span></span> std<span style="color: #990000">::</span>cerr <span style="color: #990000"><<</span> cooking<span style="color: #990000">-></span>posts<span style="color: #990000">.</span><span style="font-weight: bold"><span style="color: #000000">size</span></span><span style="color: #990000">()</span> <span style="color: #990000"><<</span> <span style="color: #FF0000">" post(s) tagged with Cooking."</span> <span style="color: #990000"><<</span> std<span style="color: #990000">::</span>endl<span style="color: #990000">;</span></tt></pre></div></div> <div class="admonitionblock"> <table><tr> <td class="icon"> <div class="title">Warning</div> </td> <td class="content"> <div class="paragraph"><p>The same warning as above applies here as well.</p></div> </td> </tr></table> </div> <h3 id="_em_one_to_one_em_relations">6.3. <em>One-to-One</em> relations</h3><div style="clear:left"></div> <div class="paragraph"><p><em>One-to-One</em> relations are currently not supported, but can be simulated using <em>Many-to-One</em> relations as they have the same database schema structure.</p></div> </div> <h2 id="customizing">7. Customizing the mapping</h2> <div class="sectionbody"> <div class="paragraph"><p>By default, <tt>Wt::Dbo</tt> will add an auto-incrementing surrogate primary (<tt>id</tt>) key and a version field (<tt>version</tt>) to each mapped table.</p></div> <div class="paragraph"><p>While these defaults make sense for a new project, you can tailor the mapping so that you can map to virtually any existing database schema.</p></div> <h3 id="_changing_or_disabling_the_surrogate_primary_key_id_field">7.1. Changing or disabling the surrogate primary key "id" field</h3><div style="clear:left"></div> <div class="paragraph"><p>To change the field name used for the surrogate primary key for a mapped class, or to disable the surrogate primary key for a class and use a nautral key instead, you need to specialize <tt><a href="../../reference/html/structWt_1_1Dbo_1_1dbo__traits.html">Wt::Dbo::dbo_traits<C></a></tt>.</p></div> <div class="paragraph"><p>For example, the code below changes the primary key field for class Post from <tt>id</tt> to <tt>post_id</tt>:</p></div> <div class="listingblock"> <div class="title">Changing the "id" field name (tutorial3.C)</div> <div class="content"><!-- Generator: GNU source-highlight 3.1 by Lorenzo Bettini http://www.lorenzobettini.it http://www.gnu.org/software/src-highlite --> <pre><tt><span style="font-weight: bold"><span style="color: #000080">#include</span></span> <span style="color: #FF0000"><Wt/Dbo/Dbo></span> <span style="font-weight: bold"><span style="color: #0000FF">namespace</span></span> dbo <span style="color: #990000">=</span> Wt<span style="color: #990000">::</span>Dbo<span style="color: #990000">;</span> <span style="font-weight: bold"><span style="color: #0000FF">class</span></span> <span style="color: #008080">Post</span> <span style="color: #FF0000">{</span> <span style="font-weight: bold"><span style="color: #0000FF">public</span></span><span style="color: #990000">:</span> <span style="color: #990000">...</span> <span style="color: #FF0000">}</span><span style="color: #990000">;</span> <span style="font-weight: bold"><span style="color: #0000FF">namespace</span></span> Wt <span style="color: #FF0000">{</span> <span style="font-weight: bold"><span style="color: #0000FF">namespace</span></span> Dbo <span style="color: #FF0000">{</span> <span style="font-weight: bold"><span style="color: #0000FF">template</span></span><span style="color: #990000"><></span> <span style="font-weight: bold"><span style="color: #0000FF">struct</span></span> <span style="color: #008080">dbo_traits</span><span style="color: #990000"><</span>Post<span style="color: #990000">></span> <span style="color: #990000">:</span> <span style="font-weight: bold"><span style="color: #0000FF">public</span></span> dbo_default_traits <span style="color: #FF0000">{</span> <span style="font-weight: bold"><span style="color: #0000FF">static</span></span> <span style="font-weight: bold"><span style="color: #0000FF">const</span></span> <span style="color: #009900">char</span> <span style="color: #990000">*</span><span style="font-weight: bold"><span style="color: #000000">surrogateIdField</span></span><span style="color: #990000">()</span> <span style="color: #FF0000">{</span> <span style="font-weight: bold"><span style="color: #0000FF">return</span></span> <span style="color: #FF0000">"post_id"</span><span style="color: #990000">;</span> <span style="color: #FF0000">}</span> <span style="color: #FF0000">}</span><span style="color: #990000">;</span> <span style="color: #FF0000">}</span> <span style="color: #FF0000">}</span></tt></pre></div></div> <h3 id="_changing_or_disabling_the_version_field">7.2. Changing or disabling the "version" field</h3><div style="clear:left"></div> <div class="paragraph"><p>To change the field name used for the optimistic concurrency control version field (<tt>version</tt>), or to disable optimistic concurrency control for a class alltoghether, you need to specialize <tt><a href="../../reference/html/structWt_1_1Dbo_1_1dbo__traits.html">Wt::Dbo::dbo_traits<C></a></tt>.</p></div> <div class="paragraph"><p>For example, the code below disables optimistic concurrency control for class Post:</p></div> <div class="listingblock"> <div class="title">Disabling the "version" field name (tutorial4.C)</div> <div class="content"><!-- Generator: GNU source-highlight 3.1 by Lorenzo Bettini http://www.lorenzobettini.it http://www.gnu.org/software/src-highlite --> <pre><tt><span style="font-weight: bold"><span style="color: #000080">#include</span></span> <span style="color: #FF0000"><Wt/Dbo/Dbo></span> <span style="font-weight: bold"><span style="color: #0000FF">namespace</span></span> dbo <span style="color: #990000">=</span> Wt<span style="color: #990000">::</span>Dbo<span style="color: #990000">;</span> <span style="font-weight: bold"><span style="color: #0000FF">class</span></span> <span style="color: #008080">Post</span> <span style="color: #FF0000">{</span> <span style="font-weight: bold"><span style="color: #0000FF">public</span></span><span style="color: #990000">:</span> <span style="color: #990000">...</span> <span style="color: #FF0000">}</span><span style="color: #990000">;</span> <span style="font-weight: bold"><span style="color: #0000FF">namespace</span></span> Wt <span style="color: #FF0000">{</span> <span style="font-weight: bold"><span style="color: #0000FF">namespace</span></span> Dbo <span style="color: #FF0000">{</span> <span style="font-weight: bold"><span style="color: #0000FF">template</span></span><span style="color: #990000"><></span> <span style="font-weight: bold"><span style="color: #0000FF">struct</span></span> <span style="color: #008080">dbo_traits</span><span style="color: #990000"><</span>Post<span style="color: #990000">></span> <span style="color: #990000">:</span> <span style="font-weight: bold"><span style="color: #0000FF">public</span></span> dbo_default_traits <span style="color: #FF0000">{</span> <span style="font-weight: bold"><span style="color: #0000FF">static</span></span> <span style="font-weight: bold"><span style="color: #0000FF">const</span></span> <span style="color: #009900">char</span> <span style="color: #990000">*</span><span style="font-weight: bold"><span style="color: #000000">versionField</span></span><span style="color: #990000">()</span> <span style="color: #FF0000">{</span> <span style="font-weight: bold"><span style="color: #0000FF">return</span></span> <span style="color: #993399">0</span><span style="color: #990000">;</span> <span style="color: #FF0000">}</span> <span style="color: #FF0000">}</span><span style="color: #990000">;</span> <span style="color: #FF0000">}</span> <span style="color: #FF0000">}</span></tt></pre></div></div> <h3 id="_specifying_a_natural_primary_key">7.3. Specifying a natural primary key</h3><div style="clear:left"></div> <div class="paragraph"><p>Instead of using a auto-incrementing surrogate primary key, you may want to use a different primary key.</p></div> <div class="paragraph"><p>For example, the code below changes the primary key for the User table to a string (his username) which maps onto a <tt>varchar (20)</tt> field <tt>user_name</tt>:</p></div> <div class="listingblock"> <div class="title">Using a natural key (tutorial5.C)</div> <div class="content"><!-- Generator: GNU source-highlight 3.1 by Lorenzo Bettini http://www.lorenzobettini.it http://www.gnu.org/software/src-highlite --> <pre><tt><span style="font-weight: bold"><span style="color: #000080">#include</span></span> <span style="color: #FF0000"><Wt/Dbo/Dbo></span> <span style="font-weight: bold"><span style="color: #0000FF">namespace</span></span> dbo <span style="color: #990000">=</span> Wt<span style="color: #990000">::</span>Dbo<span style="color: #990000">;</span> <span style="font-weight: bold"><span style="color: #0000FF">class</span></span> <span style="color: #008080">User</span> <span style="color: #FF0000">{</span> <span style="font-weight: bold"><span style="color: #0000FF">public</span></span><span style="color: #990000">:</span> std<span style="color: #990000">::</span><span style="color: #008080">string</span> userId<span style="color: #990000">;</span> <span style="font-weight: bold"><span style="color: #0000FF">template</span></span><span style="color: #990000"><</span><span style="font-weight: bold"><span style="color: #0000FF">class</span></span> <span style="color: #008080">Action</span><span style="color: #990000">></span> <span style="color: #009900">void</span> <span style="font-weight: bold"><span style="color: #000000">persist</span></span><span style="color: #990000">(</span>Action<span style="color: #990000">&</span> a<span style="color: #990000">)</span> <span style="color: #FF0000">{</span> dbo<span style="color: #990000">::</span><span style="font-weight: bold"><span style="color: #000000">id</span></span><span style="color: #990000">(</span>a<span style="color: #990000">,</span> userId<span style="color: #990000">,</span> <span style="color: #FF0000">"user_id"</span><span style="color: #990000">,</span> <span style="color: #993399">20</span><span style="color: #990000">);</span> <span style="color: #FF0000">}</span> <span style="color: #FF0000">}</span><span style="color: #990000">;</span> <span style="font-weight: bold"><span style="color: #0000FF">namespace</span></span> Wt <span style="color: #FF0000">{</span> <span style="font-weight: bold"><span style="color: #0000FF">namespace</span></span> Dbo <span style="color: #FF0000">{</span> <span style="font-weight: bold"><span style="color: #0000FF">template</span></span><span style="color: #990000"><></span> <span style="font-weight: bold"><span style="color: #0000FF">struct</span></span> <span style="color: #008080">dbo_traits</span><span style="color: #990000"><</span>User<span style="color: #990000">></span> <span style="color: #990000">:</span> <span style="font-weight: bold"><span style="color: #0000FF">public</span></span> dbo_default_traits <span style="color: #FF0000">{</span> <span style="font-weight: bold"><span style="color: #0000FF">typedef</span></span> std<span style="color: #990000">::</span><span style="color: #008080">string</span> IdType<span style="color: #990000">;</span> <span style="font-weight: bold"><span style="color: #0000FF">static</span></span> <span style="color: #008080">IdType</span> <span style="font-weight: bold"><span style="color: #000000">invalidId</span></span><span style="color: #990000">()</span> <span style="color: #FF0000">{</span> <span style="font-weight: bold"><span style="color: #0000FF">return</span></span> std<span style="color: #990000">::</span><span style="font-weight: bold"><span style="color: #000000">string</span></span><span style="color: #990000">();</span> <span style="color: #FF0000">}</span> <span style="font-weight: bold"><span style="color: #0000FF">static</span></span> <span style="font-weight: bold"><span style="color: #0000FF">const</span></span> <span style="color: #009900">char</span> <span style="color: #990000">*</span><span style="font-weight: bold"><span style="color: #000000">surrogateIdField</span></span><span style="color: #990000">()</span> <span style="color: #FF0000">{</span> <span style="font-weight: bold"><span style="color: #0000FF">return</span></span> <span style="color: #993399">0</span><span style="color: #990000">;</span> <span style="color: #FF0000">}</span> <span style="color: #FF0000">}</span><span style="color: #990000">;</span> <span style="color: #FF0000">}</span> <span style="color: #FF0000">}</span></tt></pre></div></div> <div class="paragraph"><p>The <tt>id()</tt> function has the same syntax as the <tt>field()</tt> function.</p></div> <div class="paragraph"><p>A natural primary key may also be a composite key, a foreign key or a combination.</p></div> <h3 id="_specifying_a_composite_natural_primary_key">7.4. Specifying a composite natural primary key</h3><div style="clear:left"></div> <div class="paragraph"><p>To use a composite type as a natural primary key, i.e. a type which consists of more than one field, you need to have a corresponding C++ type.</p></div> <div class="paragraph"><p>The type has a number of basic requirements, such as default constructor, comparison operators (== and <), and a streaming operator.</p></div> <div class="listingblock"> <div class="title">Using a composite natural primary key (tutorial6.C)</div> <div class="content"><!-- Generator: GNU source-highlight 3.1 by Lorenzo Bettini http://www.lorenzobettini.it http://www.gnu.org/software/src-highlite --> <pre><tt><span style="font-weight: bold"><span style="color: #0000FF">struct</span></span> <span style="color: #008080">Coordinate</span> <span style="color: #FF0000">{</span> <span style="color: #009900">int</span> x<span style="color: #990000">,</span> y<span style="color: #990000">;</span> <span style="font-weight: bold"><span style="color: #000000">Coordinate</span></span><span style="color: #990000">()</span> <span style="color: #990000">:</span> <span style="font-weight: bold"><span style="color: #000000">x</span></span><span style="color: #990000">(-</span><span style="color: #993399">1</span><span style="color: #990000">),</span> <span style="font-weight: bold"><span style="color: #000000">y</span></span><span style="color: #990000">(-</span><span style="color: #993399">1</span><span style="color: #990000">)</span> <span style="color: #FF0000">{</span> <span style="color: #FF0000">}</span> <span style="font-weight: bold"><span style="color: #000000">Coordinate</span></span><span style="color: #990000">(</span><span style="color: #009900">int</span> an_x<span style="color: #990000">,</span> <span style="color: #009900">int</span> an_y<span style="color: #990000">)</span> <span style="color: #990000">:</span> <span style="font-weight: bold"><span style="color: #000000">x</span></span><span style="color: #990000">(</span>an_x<span style="color: #990000">),</span> <span style="font-weight: bold"><span style="color: #000000">y</span></span><span style="color: #990000">(</span>an_y<span style="color: #990000">)</span> <span style="color: #FF0000">{</span> <span style="color: #FF0000">}</span> <span style="color: #009900">bool</span> <span style="font-weight: bold"><span style="color: #0000FF">operator</span></span><span style="color: #990000">==</span> <span style="color: #990000">(</span><span style="font-weight: bold"><span style="color: #0000FF">const</span></span> Coordinate<span style="color: #990000">&</span> other<span style="color: #990000">)</span> <span style="font-weight: bold"><span style="color: #0000FF">const</span></span> <span style="color: #FF0000">{</span> <span style="font-weight: bold"><span style="color: #0000FF">return</span></span> x <span style="color: #990000">==</span> other<span style="color: #990000">.</span>x <span style="color: #990000">&&</span> y <span style="color: #990000">==</span> other<span style="color: #990000">.</span>y<span style="color: #990000">;</span> <span style="color: #FF0000">}</span> <span style="color: #009900">bool</span> <span style="font-weight: bold"><span style="color: #0000FF">operator</span></span><span style="color: #990000"><</span> <span style="color: #990000">(</span><span style="font-weight: bold"><span style="color: #0000FF">const</span></span> Coordinate<span style="color: #990000">&</span> other<span style="color: #990000">)</span> <span style="font-weight: bold"><span style="color: #0000FF">const</span></span> <span style="color: #FF0000">{</span> <span style="font-weight: bold"><span style="color: #0000FF">if</span></span> <span style="color: #990000">(</span>x <span style="color: #990000"><</span> other<span style="color: #990000">.</span>x<span style="color: #990000">)</span> <span style="font-weight: bold"><span style="color: #0000FF">return</span></span> <span style="font-weight: bold"><span style="color: #0000FF">true</span></span><span style="color: #990000">;</span> <span style="font-weight: bold"><span style="color: #0000FF">else</span></span> <span style="font-weight: bold"><span style="color: #0000FF">if</span></span> <span style="color: #990000">(</span>x <span style="color: #990000">==</span> other<span style="color: #990000">.</span>x<span style="color: #990000">)</span> <span style="font-weight: bold"><span style="color: #0000FF">return</span></span> y <span style="color: #990000"><</span> other<span style="color: #990000">.</span>y<span style="color: #990000">;</span> <span style="font-weight: bold"><span style="color: #0000FF">else</span></span> <span style="font-weight: bold"><span style="color: #0000FF">return</span></span> <span style="font-weight: bold"><span style="color: #0000FF">false</span></span><span style="color: #990000">;</span> <span style="color: #FF0000">}</span> <span style="color: #FF0000">}</span><span style="color: #990000">;</span> std<span style="color: #990000">::</span>ostream<span style="color: #990000">&</span> <span style="font-weight: bold"><span style="color: #0000FF">operator</span></span><span style="color: #990000"><<</span> <span style="color: #990000">(</span>std<span style="color: #990000">::</span>ostream<span style="color: #990000">&</span> o<span style="color: #990000">,</span> <span style="font-weight: bold"><span style="color: #0000FF">const</span></span> Coordinate<span style="color: #990000">&</span> c<span style="color: #990000">)</span> <span style="color: #FF0000">{</span> <span style="font-weight: bold"><span style="color: #0000FF">return</span></span> o <span style="color: #990000"><<</span> <span style="color: #FF0000">"("</span> <span style="color: #990000"><<</span> c<span style="color: #990000">.</span>x <span style="color: #990000"><<</span> <span style="color: #FF0000">", "</span> <span style="color: #990000"><<</span> c<span style="color: #990000">.</span>y <span style="color: #990000"><<</span> <span style="color: #FF0000">")"</span><span style="color: #990000">;</span> <span style="color: #FF0000">}</span></tt></pre></div></div> <div class="paragraph"><p>Next, you must indicate how the type is persisted, by overloading Dbo’s <tt>field()</tt> function for it.</p></div> <div class="listingblock"> <div class="title">(tutorial6.C continued)</div> <div class="content"><!-- Generator: GNU source-highlight 3.1 by Lorenzo Bettini http://www.lorenzobettini.it http://www.gnu.org/software/src-highlite --> <pre><tt><span style="font-weight: bold"><span style="color: #0000FF">namespace</span></span> Wt <span style="color: #FF0000">{</span> <span style="font-weight: bold"><span style="color: #0000FF">namespace</span></span> Dbo <span style="color: #FF0000">{</span> <span style="font-weight: bold"><span style="color: #0000FF">template</span></span> <span style="color: #990000"><</span><span style="font-weight: bold"><span style="color: #0000FF">class</span></span> <span style="color: #008080">Action</span><span style="color: #990000">></span> <span style="color: #009900">void</span> <span style="font-weight: bold"><span style="color: #000000">field</span></span><span style="color: #990000">(</span>Action<span style="color: #990000">&</span> action<span style="color: #990000">,</span> Coordinate<span style="color: #990000">&</span> coordinate<span style="color: #990000">,</span> <span style="font-weight: bold"><span style="color: #0000FF">const</span></span> std<span style="color: #990000">::</span>string<span style="color: #990000">&</span> name<span style="color: #990000">,</span> <span style="color: #009900">int</span> size <span style="color: #990000">=</span> <span style="color: #990000">-</span><span style="color: #993399">1</span><span style="color: #990000">)</span> <span style="color: #FF0000">{</span> <span style="font-weight: bold"><span style="color: #000000">field</span></span><span style="color: #990000">(</span>action<span style="color: #990000">,</span> coordinate<span style="color: #990000">.</span>x<span style="color: #990000">,</span> name <span style="color: #990000">+</span> <span style="color: #FF0000">"_x"</span><span style="color: #990000">);</span> <span style="font-weight: bold"><span style="color: #000000">field</span></span><span style="color: #990000">(</span>action<span style="color: #990000">,</span> coordinate<span style="color: #990000">.</span>y<span style="color: #990000">,</span> name <span style="color: #990000">+</span> <span style="color: #FF0000">"_y"</span><span style="color: #990000">);</span> <span style="color: #FF0000">}</span> <span style="color: #FF0000">}</span> <span style="color: #FF0000">}</span></tt></pre></div></div> <div class="paragraph"><p>With this in place, we can use the <tt>Coordinate</tt> type as a natural primary key type:</p></div> <div class="listingblock"> <div class="title">(tutorial6.C continued)</div> <div class="content"><!-- Generator: GNU source-highlight 3.1 by Lorenzo Bettini http://www.lorenzobettini.it http://www.gnu.org/software/src-highlite --> <pre><tt><span style="font-weight: bold"><span style="color: #0000FF">class</span></span> <span style="color: #008080">GeoTag</span><span style="color: #990000">;</span> <span style="font-weight: bold"><span style="color: #0000FF">namespace</span></span> Wt <span style="color: #FF0000">{</span> <span style="font-weight: bold"><span style="color: #0000FF">namespace</span></span> Dbo <span style="color: #FF0000">{</span> <span style="font-weight: bold"><span style="color: #0000FF">template</span></span><span style="color: #990000"><></span> <span style="font-weight: bold"><span style="color: #0000FF">struct</span></span> <span style="color: #008080">dbo_traits</span><span style="color: #990000"><</span>GeoTag<span style="color: #990000">></span> <span style="color: #990000">:</span> <span style="font-weight: bold"><span style="color: #0000FF">public</span></span> dbo_default_traits <span style="color: #FF0000">{</span> <span style="font-weight: bold"><span style="color: #0000FF">typedef</span></span> <span style="color: #008080">Coordinate</span> IdType<span style="color: #990000">;</span> <span style="font-weight: bold"><span style="color: #0000FF">static</span></span> <span style="color: #008080">IdType</span> <span style="font-weight: bold"><span style="color: #000000">invalidId</span></span><span style="color: #990000">()</span> <span style="color: #FF0000">{</span> <span style="font-weight: bold"><span style="color: #0000FF">return</span></span> <span style="font-weight: bold"><span style="color: #000000">Coordinate</span></span><span style="color: #990000">();</span> <span style="color: #FF0000">}</span> <span style="font-weight: bold"><span style="color: #0000FF">static</span></span> <span style="font-weight: bold"><span style="color: #0000FF">const</span></span> <span style="color: #009900">char</span> <span style="color: #990000">*</span><span style="font-weight: bold"><span style="color: #000000">surrogateIdField</span></span><span style="color: #990000">()</span> <span style="color: #FF0000">{</span> <span style="font-weight: bold"><span style="color: #0000FF">return</span></span> <span style="color: #993399">0</span><span style="color: #990000">;</span> <span style="color: #FF0000">}</span> <span style="color: #FF0000">}</span><span style="color: #990000">;</span> <span style="color: #FF0000">}</span> <span style="color: #FF0000">}</span> <span style="font-weight: bold"><span style="color: #0000FF">class</span></span> <span style="color: #008080">GeoTag</span> <span style="color: #FF0000">{</span> <span style="font-weight: bold"><span style="color: #0000FF">public</span></span><span style="color: #990000">:</span> <span style="color: #008080">Coordinate</span> position<span style="color: #990000">;</span> std<span style="color: #990000">::</span><span style="color: #008080">string</span> name<span style="color: #990000">;</span> <span style="font-weight: bold"><span style="color: #0000FF">template</span></span> <span style="color: #990000"><</span><span style="font-weight: bold"><span style="color: #0000FF">class</span></span> <span style="color: #008080">Action</span><span style="color: #990000">></span> <span style="color: #009900">void</span> <span style="font-weight: bold"><span style="color: #000000">persist</span></span><span style="color: #990000">(</span>Action<span style="color: #990000">&</span> a<span style="color: #990000">)</span> <span style="color: #FF0000">{</span> dbo<span style="color: #990000">::</span><span style="font-weight: bold"><span style="color: #000000">id</span></span><span style="color: #990000">(</span>a<span style="color: #990000">,</span> position<span style="color: #990000">,</span> <span style="color: #FF0000">"position"</span><span style="color: #990000">);</span> dbo<span style="color: #990000">::</span><span style="font-weight: bold"><span style="color: #000000">field</span></span><span style="color: #990000">(</span>a<span style="color: #990000">,</span> name<span style="color: #990000">,</span> <span style="color: #FF0000">"name"</span><span style="color: #990000">);</span> <span style="color: #FF0000">}</span> <span style="color: #FF0000">}</span><span style="color: #990000">;</span></tt></pre></div></div> <div class="paragraph"><p>Note that the composite key may also include foreign keys, by storing ptr<> objects in the composite, which you map using a <tt>belongsTo()</tt> declaration. See <tt>tutorial8.C</tt> for a complete example.</p></div> <h3 id="_specifying_foreign_key_constraints">7.5. Specifying foreign key constraints</h3><div style="clear:left"></div> <div class="paragraph"><p>The <tt>belongsTo()</tt> function is overloaded so that you can add foreign key constraints which are enforced by the database, such as:</p></div> <div class="ulist"><ul> <li> <p> <tt>NotNull</tt>: cannot be null </p> </li> <li> <p> <tt>OnUpdateCascade</tt>: cascade an update of the (natural) primary key to the foreign keys that reference it </p> </li> <li> <p> <tt>OnUpdateSetNull</tt>: an update of the (natural) primary key sets referencing foreign keys to null </p> </li> <li> <p> <tt>OnDeleteCascade</tt>: cascade a delete of the object to also delete objects that reference it using a foreign key </p> </li> <li> <p> <tt>OnDeleteSetNull</tt>: when the object is deleted, set the referencing foreign keys to null. </p> </li> </ul></div> <div class="paragraph"><p>In the next chapter we will see how you can specify these foreign key constraints also for foreign keys that double as primary key.</p></div> <h3 id="_specifying_a_natural_primary_key_that_is_also_a_foreign_key">7.6. Specifying a natural primary key that is also a foreign key</h3><div style="clear:left"></div> <div class="paragraph"><p>Let’s define a class UserInfo which provides additional data for a User. We will only allow exactly one UserInfo object per User, and therefore chose as primary key for the UserInfo a reference to the User.</p></div> <div class="listingblock"> <div class="title">Using a foreign key as primary key (tutorial7.C)</div> <div class="content"><!-- Generator: GNU source-highlight 3.1 by Lorenzo Bettini http://www.lorenzobettini.it http://www.gnu.org/software/src-highlite --> <pre><tt><span style="font-weight: bold"><span style="color: #000080">#include</span></span> <span style="color: #FF0000"><Wt/Dbo/Dbo></span> <span style="font-weight: bold"><span style="color: #000080">#include</span></span> <span style="color: #FF0000"><Wt/Dbo/backend/Sqlite3></span> <span style="font-weight: bold"><span style="color: #0000FF">namespace</span></span> dbo <span style="color: #990000">=</span> Wt<span style="color: #990000">::</span>Dbo<span style="color: #990000">;</span> <span style="font-weight: bold"><span style="color: #0000FF">class</span></span> <span style="color: #008080">UserInfo</span><span style="color: #990000">;</span> <span style="font-weight: bold"><span style="color: #0000FF">class</span></span> <span style="color: #008080">User</span><span style="color: #990000">;</span> <span style="font-weight: bold"><span style="color: #0000FF">namespace</span></span> Wt <span style="color: #FF0000">{</span> <span style="font-weight: bold"><span style="color: #0000FF">namespace</span></span> Dbo <span style="color: #FF0000">{</span> <span style="font-weight: bold"><span style="color: #0000FF">template</span></span><span style="color: #990000"><></span> <span style="font-weight: bold"><span style="color: #0000FF">struct</span></span> <span style="color: #008080">dbo_traits</span><span style="color: #990000"><</span>UserInfo<span style="color: #990000">></span> <span style="color: #990000">:</span> <span style="font-weight: bold"><span style="color: #0000FF">public</span></span> dbo_default_traits <span style="color: #FF0000">{</span> <span style="font-weight: bold"><span style="color: #0000FF">typedef</span></span> <span style="color: #008080">ptr<User></span> IdType<span style="color: #990000">;</span> <span style="font-weight: bold"><span style="color: #0000FF">static</span></span> <span style="color: #008080">IdType</span> <span style="font-weight: bold"><span style="color: #000000">invalidId</span></span><span style="color: #990000">()</span> <span style="color: #FF0000">{</span> <span style="font-weight: bold"><span style="color: #0000FF">return</span></span> ptr<span style="color: #990000"><</span>User<span style="color: #990000">>();</span> <span style="color: #FF0000">}</span> <span style="font-weight: bold"><span style="color: #0000FF">static</span></span> <span style="font-weight: bold"><span style="color: #0000FF">const</span></span> <span style="color: #009900">char</span> <span style="color: #990000">*</span><span style="font-weight: bold"><span style="color: #000000">surrogateIdField</span></span><span style="color: #990000">()</span> <span style="color: #FF0000">{</span> <span style="font-weight: bold"><span style="color: #0000FF">return</span></span> <span style="color: #993399">0</span><span style="color: #990000">;</span> <span style="color: #FF0000">}</span> <span style="color: #FF0000">}</span><span style="color: #990000">;</span> <span style="color: #FF0000">}</span> <span style="color: #FF0000">}</span> <span style="font-weight: bold"><span style="color: #0000FF">class</span></span> <span style="color: #008080">User</span> <span style="color: #FF0000">{</span> <span style="font-weight: bold"><span style="color: #0000FF">public</span></span><span style="color: #990000">:</span> std<span style="color: #990000">::</span><span style="color: #008080">string</span> name<span style="color: #990000">;</span> dbo<span style="color: #990000">::</span><span style="color: #008080">collection< dbo::ptr<UserInfo> ></span> infos<span style="color: #990000">;</span> <span style="font-weight: bold"><span style="color: #0000FF">template</span></span><span style="color: #990000"><</span><span style="font-weight: bold"><span style="color: #0000FF">class</span></span> <span style="color: #008080">Action</span><span style="color: #990000">></span> <span style="color: #009900">void</span> <span style="font-weight: bold"><span style="color: #000000">persist</span></span><span style="color: #990000">(</span>Action<span style="color: #990000">&</span> a<span style="color: #990000">)</span> <span style="color: #FF0000">{</span> dbo<span style="color: #990000">::</span><span style="font-weight: bold"><span style="color: #000000">field</span></span><span style="color: #990000">(</span>a<span style="color: #990000">,</span> name<span style="color: #990000">,</span> <span style="color: #FF0000">"name"</span><span style="color: #990000">);</span> <span style="font-style: italic"><span style="color: #9A1900">// In fact, this is really constrained to hasOne() ...</span></span> dbo<span style="color: #990000">::</span><span style="font-weight: bold"><span style="color: #000000">hasMany</span></span><span style="color: #990000">(</span>a<span style="color: #990000">,</span> infos<span style="color: #990000">,</span> dbo<span style="color: #990000">::</span>ManyToOne<span style="color: #990000">,</span> <span style="color: #FF0000">"user"</span><span style="color: #990000">);</span> <span style="color: #FF0000">}</span> <span style="color: #FF0000">}</span><span style="color: #990000">;</span> <span style="font-weight: bold"><span style="color: #0000FF">class</span></span> <span style="color: #008080">UserInfo</span> <span style="color: #FF0000">{</span> <span style="font-weight: bold"><span style="color: #0000FF">public</span></span><span style="color: #990000">:</span> dbo<span style="color: #990000">::</span><span style="color: #008080">ptr<User></span> user<span style="color: #990000">;</span> std<span style="color: #990000">::</span><span style="color: #008080">string</span> info<span style="color: #990000">;</span> <span style="font-weight: bold"><span style="color: #0000FF">template</span></span><span style="color: #990000"><</span><span style="font-weight: bold"><span style="color: #0000FF">class</span></span> <span style="color: #008080">Action</span><span style="color: #990000">></span> <span style="color: #009900">void</span> <span style="font-weight: bold"><span style="color: #000000">persist</span></span><span style="color: #990000">(</span>Action<span style="color: #990000">&</span> a<span style="color: #990000">)</span> <span style="color: #FF0000">{</span> dbo<span style="color: #990000">::</span><span style="font-weight: bold"><span style="color: #000000">id</span></span><span style="color: #990000">(</span>a<span style="color: #990000">,</span> user<span style="color: #990000">,</span> <span style="color: #FF0000">"user"</span><span style="color: #990000">,</span> dbo<span style="color: #990000">::</span>OnDeleteCascade<span style="color: #990000">);</span> dbo<span style="color: #990000">::</span><span style="font-weight: bold"><span style="color: #000000">field</span></span><span style="color: #990000">(</span>a<span style="color: #990000">,</span> info<span style="color: #990000">,</span> <span style="color: #FF0000">"info"</span><span style="color: #990000">);</span> <span style="color: #FF0000">}</span> <span style="color: #FF0000">}</span><span style="color: #990000">;</span> <span style="color: #009900">void</span> <span style="font-weight: bold"><span style="color: #000000">run</span></span><span style="color: #990000">()</span> <span style="color: #FF0000">{</span> <span style="font-style: italic"><span style="color: #9A1900">/*</span></span> <span style="font-style: italic"><span style="color: #9A1900"> * Setup a session, would typically be done once at application startup.</span></span> <span style="font-style: italic"><span style="color: #9A1900"> */</span></span> dbo<span style="color: #990000">::</span>backend<span style="color: #990000">::</span><span style="color: #008080">Sqlite3</span> <span style="font-weight: bold"><span style="color: #000000">sqlite3</span></span><span style="color: #990000">(</span><span style="color: #FF0000">":memory:"</span><span style="color: #990000">);</span> sqlite3<span style="color: #990000">.</span><span style="font-weight: bold"><span style="color: #000000">setProperty</span></span><span style="color: #990000">(</span><span style="color: #FF0000">"show-queries"</span><span style="color: #990000">,</span> <span style="color: #FF0000">"true"</span><span style="color: #990000">);</span> dbo<span style="color: #990000">::</span><span style="color: #008080">Session</span> session<span style="color: #990000">;</span> session<span style="color: #990000">.</span><span style="font-weight: bold"><span style="color: #000000">setConnection</span></span><span style="color: #990000">(</span>sqlite3<span style="color: #990000">);</span> session<span style="color: #990000">.</span>mapClass<span style="color: #990000"><</span>User<span style="color: #990000">>(</span><span style="color: #FF0000">"user"</span><span style="color: #990000">);</span> session<span style="color: #990000">.</span>mapClass<span style="color: #990000"><</span>UserInfo<span style="color: #990000">>(</span><span style="color: #FF0000">"user_info"</span><span style="color: #990000">);</span> <span style="font-style: italic"><span style="color: #9A1900">/*</span></span> <span style="font-style: italic"><span style="color: #9A1900"> * Try to create the schema (will fail if already exists).</span></span> <span style="font-style: italic"><span style="color: #9A1900"> */</span></span> session<span style="color: #990000">.</span><span style="font-weight: bold"><span style="color: #000000">createTables</span></span><span style="color: #990000">();</span> dbo<span style="color: #990000">::</span><span style="color: #008080">Transaction</span> <span style="font-weight: bold"><span style="color: #000000">transaction</span></span><span style="color: #990000">(</span>session<span style="color: #990000">);</span> <span style="color: #FF0000">{</span> <span style="color: #008080">User</span> <span style="color: #990000">*</span>user <span style="color: #990000">=</span> <span style="font-weight: bold"><span style="color: #0000FF">new</span></span> <span style="font-weight: bold"><span style="color: #000000">User</span></span><span style="color: #990000">();</span> user<span style="color: #990000">-></span>name <span style="color: #990000">=</span> <span style="color: #FF0000">"Joe"</span><span style="color: #990000">;</span> dbo<span style="color: #990000">::</span><span style="color: #008080">ptr<User></span> userPtr <span style="color: #990000">=</span> session<span style="color: #990000">.</span><span style="font-weight: bold"><span style="color: #000000">add</span></span><span style="color: #990000">(</span>user<span style="color: #990000">);</span> <span style="color: #008080">UserInfo</span> <span style="color: #990000">*</span>userInfo <span style="color: #990000">=</span> <span style="font-weight: bold"><span style="color: #0000FF">new</span></span> <span style="font-weight: bold"><span style="color: #000000">UserInfo</span></span><span style="color: #990000">();</span> userInfo<span style="color: #990000">-></span>user <span style="color: #990000">=</span> userPtr<span style="color: #990000">;</span> userInfo<span style="color: #990000">-></span>info <span style="color: #990000">=</span> <span style="color: #FF0000">"great guy"</span><span style="color: #990000">;</span> session<span style="color: #990000">.</span><span style="font-weight: bold"><span style="color: #000000">add</span></span><span style="color: #990000">(</span>userInfo<span style="color: #990000">);</span> transaction<span style="color: #990000">.</span><span style="font-weight: bold"><span style="color: #000000">commit</span></span><span style="color: #990000">();</span> <span style="color: #FF0000">}</span> <span style="color: #FF0000">{</span> dbo<span style="color: #990000">::</span><span style="color: #008080">Transaction</span> <span style="font-weight: bold"><span style="color: #000000">transaction</span></span><span style="color: #990000">(</span>session<span style="color: #990000">);</span> dbo<span style="color: #990000">::</span><span style="color: #008080">ptr<UserInfo></span> info <span style="color: #990000">=</span> session<span style="color: #990000">.</span>find<span style="color: #990000"><</span>UserInfo<span style="color: #990000">>();</span> std<span style="color: #990000">::</span>cerr <span style="color: #990000"><<</span> info<span style="color: #990000">-></span>user<span style="color: #990000">-></span>name <span style="color: #990000"><<</span> <span style="color: #FF0000">" is a "</span> <span style="color: #990000"><<</span> info<span style="color: #990000">-></span>info <span style="color: #990000"><<</span> std<span style="color: #990000">::</span>endl<span style="color: #990000">;</span> transaction<span style="color: #990000">.</span><span style="font-weight: bold"><span style="color: #000000">commit</span></span><span style="color: #990000">();</span> <span style="color: #FF0000">}</span> <span style="color: #FF0000">}</span> <span style="color: #009900">int</span> <span style="font-weight: bold"><span style="color: #000000">main</span></span><span style="color: #990000">(</span><span style="color: #009900">int</span> argc<span style="color: #990000">,</span> <span style="color: #009900">char</span> <span style="color: #990000">**</span>argv<span style="color: #990000">)</span> <span style="color: #FF0000">{</span> <span style="font-weight: bold"><span style="color: #000000">run</span></span><span style="color: #990000">();</span> <span style="color: #FF0000">}</span></tt></pre></div></div> <div class="paragraph"><p>As you can see, in this example we would really need a One-to-One relationship, but this currently not yet supported in Dbo and thus we emulate it using a Many-to-One relationship (which has the same representation in SQL).</p></div> <div class="paragraph"><p>When run, this should output:</p></div> <div class="listingblock"> <div class="content"><!-- Generator: GNU source-highlight 3.1 by Lorenzo Bettini http://www.lorenzobettini.it http://www.gnu.org/software/src-highlite --> <pre><tt>begin transaction <span style="font-weight: bold"><span style="color: #0000FF">create</span></span> <span style="font-weight: bold"><span style="color: #0000FF">table</span></span> <span style="color: #FF0000">"user"</span> <span style="color: #990000">(</span> <span style="color: #FF0000">"id"</span> integer <span style="font-weight: bold"><span style="color: #0000FF">primary</span></span> <span style="font-weight: bold"><span style="color: #0000FF">key</span></span> autoincrement<span style="color: #990000">,</span> <span style="color: #FF0000">"version"</span> integer <span style="font-weight: bold"><span style="color: #0000FF">not</span></span> <span style="font-weight: bold"><span style="color: #0000FF">null</span></span><span style="color: #990000">,</span> <span style="color: #FF0000">"name"</span> <span style="color: #009900">text</span> <span style="font-weight: bold"><span style="color: #0000FF">not</span></span> <span style="font-weight: bold"><span style="color: #0000FF">null</span></span> <span style="color: #990000">)</span> <span style="font-weight: bold"><span style="color: #0000FF">create</span></span> <span style="font-weight: bold"><span style="color: #0000FF">table</span></span> <span style="color: #FF0000">"user_info"</span> <span style="color: #990000">(</span> <span style="color: #FF0000">"version"</span> integer <span style="font-weight: bold"><span style="color: #0000FF">not</span></span> <span style="font-weight: bold"><span style="color: #0000FF">null</span></span><span style="color: #990000">,</span> <span style="color: #FF0000">"user_id"</span> <span style="color: #009900">bigint</span><span style="color: #990000">,</span> <span style="color: #FF0000">"info"</span> <span style="color: #009900">text</span> <span style="font-weight: bold"><span style="color: #0000FF">not</span></span> <span style="font-weight: bold"><span style="color: #0000FF">null</span></span><span style="color: #990000">,</span> <span style="font-weight: bold"><span style="color: #0000FF">primary</span></span> <span style="font-weight: bold"><span style="color: #0000FF">key</span></span> <span style="color: #990000">(</span><span style="color: #FF0000">"user_id"</span><span style="color: #990000">),</span> constraint <span style="color: #FF0000">"fk_user_info_user"</span> <span style="font-weight: bold"><span style="color: #0000FF">foreign</span></span> <span style="font-weight: bold"><span style="color: #0000FF">key</span></span> <span style="color: #990000">(</span><span style="color: #FF0000">"user_id"</span><span style="color: #990000">)</span> <span style="font-weight: bold"><span style="color: #0000FF">references</span></span> <span style="color: #FF0000">"user"</span> <span style="color: #990000">(</span><span style="color: #FF0000">"id"</span><span style="color: #990000">)</span> <span style="font-weight: bold"><span style="color: #0000FF">on</span></span> <span style="font-weight: bold"><span style="color: #0000FF">delete</span></span> cascade <span style="color: #990000">)</span> commit transaction begin transaction <span style="font-weight: bold"><span style="color: #0000FF">insert</span></span> <span style="font-weight: bold"><span style="color: #0000FF">into</span></span> <span style="color: #FF0000">"user"</span> <span style="color: #990000">(</span><span style="color: #FF0000">"version"</span><span style="color: #990000">,</span> <span style="color: #FF0000">"name"</span><span style="color: #990000">)</span> <span style="font-weight: bold"><span style="color: #0000FF">values</span></span> <span style="color: #990000">(?,</span> <span style="color: #990000">?)</span> <span style="font-weight: bold"><span style="color: #0000FF">insert</span></span> <span style="font-weight: bold"><span style="color: #0000FF">into</span></span> <span style="color: #FF0000">"user_info"</span> <span style="color: #990000">(</span><span style="color: #FF0000">"version"</span><span style="color: #990000">,</span> <span style="color: #FF0000">"user_id"</span><span style="color: #990000">,</span> <span style="color: #FF0000">"info"</span><span style="color: #990000">)</span> <span style="font-weight: bold"><span style="color: #0000FF">values</span></span> <span style="color: #990000">(?,</span> <span style="color: #990000">?,</span> <span style="color: #990000">?)</span> commit transaction begin transaction <span style="font-weight: bold"><span style="color: #0000FF">select</span></span> version<span style="color: #990000">,</span> <span style="color: #FF0000">"user_id"</span><span style="color: #990000">,</span> <span style="color: #FF0000">"info"</span> <span style="font-weight: bold"><span style="color: #0000FF">from</span></span> <span style="color: #FF0000">"user_info"</span> <span style="font-weight: bold"><span style="color: #0000FF">select</span></span> <span style="color: #FF0000">"version"</span><span style="color: #990000">,</span> <span style="color: #FF0000">"name"</span> <span style="font-weight: bold"><span style="color: #0000FF">from</span></span> <span style="color: #FF0000">"user"</span> <span style="font-weight: bold"><span style="color: #0000FF">where</span></span> <span style="color: #FF0000">"id"</span> <span style="color: #990000">=</span> <span style="color: #990000">?</span> Joe <span style="font-weight: bold"><span style="color: #0000FF">is</span></span> a great guy commit transaction</tt></pre></div></div> </div> <h2 id="_transactions_and_concurrency">8. Transactions and concurrency</h2> <div class="sectionbody"> <div class="paragraph"><p>Reading data from the database or flushing changes to the database require an active transaction. A <tt><a href="../../reference/html/classWt_1_1Dbo_1_1Transaction.html">Transaction</a></tt> is a RIIA (Resource-Initialization-is-Acquisition) class which at the same time provides isolation between concurrent sessions and atomicity for persisting changes to the database.</p></div> <div class="paragraph"><p>The library implements optimistic locking, which allows detection (rather than avoidance) of concurrent modifications. It is a recommended and widely used strategy for dealing with concurrency issues in a scalable manner as no write locks are needed on the database. To detect a concurrent modification, a <tt>version</tt> field is added to each table which is incremented on each modification. When performing a modification (such as updating or removing an object), it is checked that the version of the record in the database is the same as the version of the object that was originally read from the database.</p></div> <div class="admonitionblock"> <table><tr> <td class="icon"> <div class="title">Note</div> </td> <td class="content"> <div class="title">Transaction isolation levels</div>The minimum level of isolation which is required for the library’s <em>optimistic locking</em> strategy is <em>Read Committed</em>: modifications in a transaction are only visible to other sessions as soon as they are committed. This is usually the lowest level of isolation supported by a database.</td> </tr></table> </div> <div class="paragraph"><p>The <tt>Transaction</tt> class is a light-weight proxy that references a <em>logical</em> transaction: multiple (usually nested) Transaction objects may be instantiated simultaneously, which each need to be committed for the logical transaction to be committed. In this way you can easily protect individual methods which require database access with such a transaction object, which will automatically participate in a wider transaction if that is available. A transaction will in fact defer opening a real transaction in the database until needed, and thus there is no penalty for instantiating a transaction to make sure a unit of work is atomic, even if you are not yet sure that there will be actual work done.</p></div> <div class="paragraph"><p>Transactions may fail and dealing with failing transactions is an integral aspect of their usage. When the library detects a concurrent modification, a <tt><a href="../../reference/html/classWt_1_1Dbo_1_1StaleObjectException.html">StaleObjectException</a></tt> is thrown. Other exceptions may be thrown, including exceptions in the backend driver when for example the database schema is not compatible with the mapping. There may also be problems detected by the business logic which may raise an exception and cause the transaction to be rolled back. When a transaction is rolled back, the modified database objects are not successfully synchronized with the database, but may possibly be synchronized later in a new transaction.</p></div> <div class="paragraph"><p>Obviously, many exceptions will be fatal. One notable exception is the <tt>StaleObjectException</tt> however. Different strategies are possible to deal with this exception. Regardless of the approach, you will at least need to <tt><a href="../../reference/html/classWt_1_1Dbo_1_1ptr.html#abb1db71ef910748437d69bf11a04eb6e">reread()</a></tt> the stale database object(s) before being able to commit changes made in a new transaction.</p></div> </div> <h2 id="_installing_tt_wt_dbo_tt">9. Installing <tt>Wt::Dbo</tt></h2> <div class="sectionbody"> <div class="paragraph"><p><tt>Wt::Dbo</tt> is included in Wt, and can thus be installed as part of this library for which there may be standard packages availabe for your operating system.</p></div> <div class="paragraph"><p>The library does however in no way depend on Wt, and can also be built, installed and used separately from it. Starting from a Wt source package (and on in a UNIX-like environment), you would do the following to build and install only <tt>Wt::Dbo</tt>:</p></div> <div class="listingblock"> <div class="title">Installing <tt>Wt::Dbo</tt> from source (UNIX-like)</div> <div class="content"><!-- Generator: GNU source-highlight 3.1 by Lorenzo Bettini http://www.lorenzobettini.it http://www.gnu.org/software/src-highlite --> <pre><tt>$ cd wt-xxx $ mkdir build $ cd build $ cmake <span style="color: #990000">../</span> <span style="font-style: italic"><span style="color: #9A1900"># extra options may be needed for locating boost, postgres, install directory, ...</span></span> $ cd src/Wt/Dbo $ make $ sudo make install</tt></pre></div></div> <div class="paragraph"><p>See also the <a href="http://www.webtoolkit.eu/wt/doc/reference/html/InstallationUnix.html">Wt installation instructions</a>.</p></div> </div> <div id="footer"> <div id="footer-text"> Last updated 2011-08-12 09:26:42 CEST </div> </div> </body> </html>