diff options
author | Boris Kolpackov <boris@codesynthesis.com> | 2012-10-19 11:37:20 +0200 |
---|---|---|
committer | Boris Kolpackov <boris@codesynthesis.com> | 2012-10-19 11:40:38 +0200 |
commit | 7fc555e53f0a03c93fe31ad9850b1e5d885c44f6 (patch) | |
tree | c86d0ef70543333230396eaafcc909f32a29dacc /doc/manual.xhtml | |
parent | 8097adf760d8cdf769cd7c50455f3b76d57ce35f (diff) |
Document prepared query support
Diffstat (limited to 'doc/manual.xhtml')
-rw-r--r-- | doc/manual.xhtml | 477 |
1 files changed, 472 insertions, 5 deletions
diff --git a/doc/manual.xhtml b/doc/manual.xhtml index 5286d9d..c4d4afb 100644 --- a/doc/manual.xhtml +++ b/doc/manual.xhtml @@ -344,6 +344,7 @@ for consistency. <tr><th>4.2</th><td><a href="#4.2">Parameter Binding</a></td></tr> <tr><th>4.3</th><td><a href="#4.3">Executing a Query</a></td></tr> <tr><th>4.4</th><td><a href="#4.4">Query Result</a></td></tr> + <tr><th>4.5</th><td><a href="#4.5">Prepared Queries</a></td></tr> </table> </td> </tr> @@ -683,7 +684,7 @@ for consistency. <tr><th>17.5.1</th><td><a href="#17.5.1">Query Result Caching</a></td></tr> <tr><th>17.5.2</th><td><a href="#17.5.2">Foreign Key Constraints</a></td></tr> <tr><th>17.5.3</th><td><a href="#17.5.3">Unique Constraint Violations</a></td></tr> - <tr><th>17.5.4</th><td><a href="#17.5.4">Multithreaded Windows Applications</a></td></tr> + <tr><th>17.5.4</th><td><a href="#17.5.4">Multi-threaded Windows Applications</a></td></tr> <tr><th>17.5.5</th><td><a href="#17.5.5">Affected Row Count and DDL Statements</a></td></tr> <tr><th>17.5.6</th><td><a href="#17.5.6">Long Data and Automatically Assigned Object Ids</a></td></tr> <tr><th>17.5.7</th><td><a href="#17.5.7">Long Data and By-Value Accessors/Modifiers</a></td></tr> @@ -3897,6 +3898,26 @@ namespace odb what () const throw (); }; + // Prepared query support exceptions. + // + struct prepared_already_cached: exception + { + const char* + name () const; + + virtual const char* + what () const throw (); + }; + + struct prepared_type_mismatch: exception + { + const char* + name () const; + + virtual const char* + what () const throw (); + }; + // Schema catalog exceptions. // struct unknown_schema: exception @@ -3988,6 +4009,14 @@ namespace odb class. For more information on polymorphism support, refer to <a href="#8.2">Section 8.2, "Polymorphism Inheritance"</a>.</p> + <p>The <code>prepared_already_cached</code> exception is thrown by the + <code>cache_query()</code> function if a prepared query with the + specified name is already cached. The <code>prepared_type_mismatch</code> + exception is thrown by the <code>lookup_query()</code> function if + the specified prepared query object type or parameters type + does not match the one in the cache. Refer to <a href="#4.5">Section + 4.5, "Prepared Queries"</a> for details.</p> + <p>The <code>unknown_schema</code> exception is thrown by the <code>odb::schema_catalog</code> class if a schema with the specified name is not found. Refer to <a href="#3.4">Section 3.4, "Database"</a> @@ -4077,7 +4106,7 @@ namespace odb example:</p> <pre class="cxx"> - query q ("first = 'John'" + (query::age < query::_ref (age))); + query q ("first = 'John' AND" + (query::age < query::_ref (age))); </pre> <h2><a name="4.1">4.1 ODB Query Language</a></h2> @@ -4312,7 +4341,9 @@ namespace odb <p>Normally, we would create a named query instance if we are planning to run the same query multiple times and would use the - in-line version for those that are executed only once. A named + in-line version for those that are executed only once (see also + <a href="#4.5">Section 4.5, "Prepared Queries"</a> for a more + optimal way to re-execute the same query multiple times). A named query instance that does not have any by-reference parameters is immutable and can be shared between multiple threads without synchronization. On the other hand, a query instance with @@ -4594,6 +4625,442 @@ namespace odb } </pre> + + <h2><a name="4.5">4.5 Prepared Queries</a></h2> + + <p>Most modern relational database systems have the notion of a prepared + statement. Prepared statements allow us to perform the potentially + expensive tasks of parsing SQL, preparing the query execution + plan, etc., once and then executing the same query multiple + times, potentially using different values for parameters in + each execution.</p> + + <p>In ODB all the non-query database operations such as + <code>persist()</code>, <code>load()</code>, <code>update()</code>, + etc., are implemented in terms of prepared statements that are + cached and reused. While the <code>query()</code> database + operation also uses the prepared statement, this statement + is not cached or reused by default since ODB has no knowledge + of whether a query will be executed multiple times or only + once. Instead, ODB provides a mechanism, called prepared queries, + that allows us to prepare a query once and execute it multiple + times. In other words, ODB prepared queries are a thin wrapper + around the underlying database's prepared statement functionality.</p> + + <p>In most cases ODB shields the application developer from database + connection management and multi-threading issues. However, when it + comes to prepared queries, a basic understanding of how ODB manages + these aspects is required. Conceptually, the <code>odb::database</code> + class represents a specific database, that is, a data store. However, + underneath, it maintains one or more connections to this database. + A connection can be used only by a single thread at a time. When + we start a transaction (by calling <code>database::begin()</code>), + the transaction instance obtains a connection and holds on to it + until the transaction is committed or rolled back. During this time + no other thread can use this connection. When the transaction + releases the connection, it may be closed or reused by another + transaction in this or another thread. What exactly happens to + a connection after it has been released depends on the connection + factory that is used by the <code>odb::database</code> instance. + For more information on connection factories, refer to + <a href="#II">Part II, "Database Systems"</a>.</p> + + <p>A query prepared on one connection cannot be executed on another. + In other words, a prepared query is associated with the connection. + One important implication of this restriction is that we cannot + prepare a query in one transaction and then try to execute it + in another without making sure that both transactions use the + same connection.</p> + + <p>To enable the prepared query functionality we need to specify + the <code>--generate-prepared</code> ODB compiler option. If + we are planning to always prepare our queries, then we can + disable the once-off query execution support by also specifying + the <code>--omit-unprepared</code> option.</p> + + <p>To prepare a query we use the <code>prepare_query()</code> function + template. This function can be called on both the <code>odb::database</code> + and <code>odb::connection</code> instances. The <code>odb::database</code> + version simply obtains the connection used by the currently active + transaction and calls the corresponding <code>odb::connection</code> + version. If no transaction is currently active, then this function + throws the <code>odb::not_in_transaction</code> exception + (<a href="#3.5">Section 3.5, "Transactions"</a>). The + <code>prepare_query()</code> function has the following signature:</p> + + <pre class="cxx"> + template <typename T> + prepared_query<T> + prepare_query (const char* name, const odb::query<T>&); + </pre> + + <p>The first argument to the <code>prepare_query()</code> function is + the prepared query name. This name is used as a key for prepared + query caching (discussed later) and must be unique. For some databases, + notably PostgreSQL, it is also used as a name of the underlying prepared + statement. The name <code>"<i>object</i>_query"</code> (e.g., + <code>"person_query"</code>) is reserved for the once-off queries + executed by the <code>database::query()</code> function. Note that + the <code>prepare_query()</code> function makes only a shallow copy + of this argument, which means that the name must be valid for the + lifetime of the returned <code>prepared_query</code> instance.</p> + + <p>The second argument to the <code>prepare_query()</code> function + is the query criteria. It has the same semantics as in the + <code>query()</code> function discussed in <a href="#4.3">Section + 4.3, "Executing a Query"</a>. Similar to <code>query()</code>, we + also have to explicitly specify the object type that we will be + querying. For example:</p> + + <pre class="cxx"> +typedef odb::query<person> query; +typedef odb::prepared_query<person> prep_query; + +prep_query pq ( + db.prepare_query<person> ("person-age-query", query::age > 50)); + </pre> + + <p>The result of executing the <code>prepare_query()</code> function is + the <code>prepared_query</code> instance that represent the prepared + query. It is best to view <code>prepared_query</code> as a handle to + the underlying prepared statement. While we can make a copy of it or + assign one <code>prepared_query</code> to another, the two instances + will refer to the same prepared statement. Once the last instance of + <code>prepared_query</code> referencing a specific prepared statement + is destroyed, this statement is released. The <code>prepared_query</code> + class template has the following interface:</p> + + <pre class="cxx"> +namespace odb +{ + template <typename T> + struct prepared_query + { + prepared_query (); + + prepared_query (const prepared_query&) + prepared_query& operator= (const prepared_query&) + + result<T> + execute (bool cache = true); + + const char* + name () const; + + statement& + statement () const; + + operator unspecified_bool_type () const; + }; +} + </pre> + + <p>The default constructor creates an empty <code>prepared_query</code> + instance, that is, an instance that does not reference a prepared + statement and therefore cannot be executed. The only way to create + a non-empty prepared query is by calling the <code>prepare_query()</code> + function discussed above. To test whether the prepared query is empty, + we can use the implicit conversion operator to a boolean type. For + example:</p> + + <pre class="cxx"> + prepared_query<person> pq; + + if (pq) + { + // Not empty. + ... + } + </pre> + + <p>The <code>execute()</code> function executes the query and returns + the result instance. The <code>cache</code> argument indicates + whether the result should be cached and has the same semantics + as in the <code>query()</code> function. In fact, conceptually, + <code>prepare_query()</code> and <code>execute()</code> are just + the <code>query()</code> function split into two: + <code>prepare_query()</code> takes the first + <code>query()</code> argument (the query condition) while + <code>execute()</code> takes the second (the cache flag). Note + also that re-executing a prepared query invalidates the + previous execution result, whether cached or uncached. </p> + + <p>The <code>name()</code> function returns the prepared query name. + This is the same name as was passed as the first argument in the + <code>prepare_query()</code> call. The <code>statement()</code> + function returns a reference to the underlying prepared statement. + Note also that calling any of these functions on an empty + <code>prepared_query</code> instance results in undefined behavior.</p> + + <p>The simplest use-case for a prepared query is the need to + execute the same query multiple times within a single transaction. + Consider the following example that queries for people that are older + than a number of different ages. This and subsequent code fragments + are taken from the <code>prepared</code> example in the + <code>odb-examples</code> package.</p> + + <pre class="cxx"> +typedef odb::query<person> query; +typedef odb::prepared_query<person> prep_query; +typedef odb::result<person> result; + +transaction t (db.begin ()); + +unsigned short age; +query q (query::age > query::_ref (age)); +prep_query pq (db.prepare_query<person> ("person-age-query", q)); + +for (age = 90; age > 40; age -= 10) +{ + result r (pq.execute ()); + ... +} + +t.commit (); + </pre> + + <p>Another scenario is the need to reuse the same query in multiple + transactions that are executed at once. As was mentioned above, + in this case we need to make sure that the prepared query and + all the transactions use the same connection. Consider an + alternative version of the above example that executes each + query in a separate transaction:</p> + + <pre class="cxx"> +connection_ptr conn (db.connection ()); + +unsigned short age; +query q (query::age > query::_ref (age)); +prep_query pq (conn->prepare_query<person> ("person-age-query", q)); + +for (age = 90; age > 40; age -= 10) +{ + transaction t (conn->begin ()); + + result r (pq.execute ()); + ... + + t.commit (); +} + </pre> + + + <p>Note that with this approach we hold on to the database connection + until all the transactions involving the prepared query are + executed. In particular, this means that while we are busy, the + connection cannot be reused by another thread. Therefore, this + approach is only recommended if all the transactions are executed + close to each other. Also note that an uncached (see below) + prepared query is invalidated once we release the connection + on which it was prepared.</p> + + <p>If we need to reuse a prepared query in transactions that are + executed at various times, potentially in different threads, then + the recommended approach is to cache the prepared query on the + connection. To support this functionality the <code>odb::database</code> + and <code>odb::connection</code> classes provide the following + function templates. Similar to <code>prepare_query()</code>, + the <code>odb::database</code> versions of the below + functions call the corresponding <code>odb::connection</code> + versions using the currently active transaction to resolve + the connection.</p> + + <pre class="cxx"> + template <typename T> + void + cache_query (const prepared_query<T>&); + + template <typename T, typename P> + void + cache_query (const prepared_query<T>&, + std::[auto|unique]_ptr<P> params); + + template <typename T> + prepared_query<T> + lookup_query (const char* name) const; + + template <typename T, typename P> + prepared_query<T> + lookup_query (const char* name, P*& params) const; + </pre> + + <p>The <code>cache_query()</code> function caches the passed prepared + query on the connection. The second overloaded version of + <code>cache_query()</code> also takes a pointer to the + by-reference query parameters. In C++98 it should be + <code>std::auto_ptr</code> while in C++11 <code>std::auto_ptr</code> + or <code>std::unique_ptr</code> can be used. The + <code>cache_query()</code> function assumes ownership of the + passed <code>params</code> argument. If a prepared query + with the same name is already cached on this connection, + then the <code>odb::prepared_already_cached</code> exception + is thrown.</p> + + <p>The <code>lookup_query()</code> function looks up a previously + cached prepared query given its name. The second overloaded + version of <code>lookup_query()</code> also returns a pointer + to the by-reference query parameters. If a prepared query + with this name has not been cached, then an empty + <code>prepared_query</code> instance is returned. If a + prepared query with this name has been cached but either + the object type or the parameters type does not match + that which was cached, then the <code>odb::prepared_type_mismatch</code> + exception is thrown.</p> + + <p>As a first example of the prepared query cache functionality, + consider the case that does not use any by-reference parameters:</p> + + <pre class="cxx"> +for (unsigned short i (0); i < 5; ++i) +{ + transaction t (db.begin ()); + + prep_query pq (db.lookup_query<person> ("person-age-query")); + + if (!pq) + { + pq = db.prepare_query<person> ( + "person-val-age-query", query::age > 50); + db.cache_query (pq); + } + + result r (pq.execute ()); + ... + + t.commit (); + + // Do some other work. + // + ... +} + </pre> + + <p>The following example shows how to do the same but for a query that + includes by-reference parameters. In this case the parameters are + cached together with the prepared query.</p> + + <pre class="cxx"> +for (unsigned short age (90); age > 40; age -= 10) +{ + transaction t (db.begin ()); + + unsigned short* age_param; + prep_query pq ( + db.lookup_query<person> ("person-age-query", age_param)); + + if (!pq) + { + auto_ptr<unsigned short> p (new unsigned short); + age_param = p.get (); + query q (query::age > query::_ref (*age_param)); + pq = db.prepare_query<person> ("person-age-query", q); + db.cache_query (pq, p); // Assumes ownership of p. + } + + *age_param = age; // Initialize the parameter. + result r (pq.execute ()); + ... + + t.commit (); + + // Do some other work. + // + ... +} + </pre> + + <p>As is evident from the above examples, when we use a prepared + query cache, each transaction that executes a query must also + include code that prepares and caches this query if it hasn't already + been done. If a prepared query is used in a single place in the + application, then this is normally not an issue since all the + relevant code is kept in one place. However, if the same query + is used in several different places in the application, then + we may end up duplicating the same preparation and caching + code, which makes it hard to maintain.</p> + + <p>To resolve this issue ODB allows us to register a prepared + query factory that will be called to prepare and cache a + query during the call to <code>lookup_query()</code>. To + register a factory we use the <code>database::query_factory()</code> + function. In C++98 it has the following signature:</p> + + <pre class="cxx"> + void + query_factory (const char* name, + void (*factory) (const char* name, connection&)); + </pre> + + <p>While in C++11 it uses the <code>std::function</code> class + template:</p> + + <pre class="cxx"> + void + query_factory (const char* name, + std::function<void (const char* name, connection&)>); + </pre> + + <p>The first argument to the <code>query_factory()</code> function is + the prepared query name that this factory will be called to prepare + and cache. An empty name is treated as a fallback wildcard factory + that is capable of preparing any query. The second argument is the + factory function or, in C++11, function object or lambda.</p> + + <p>The example fragment shows how we can use the prepared query + factory:</p> + + <pre class="cxx"> +struct params +{ + unsigned short age; + string first; +}; + +static void +query_factory (const char* name, connection& c) +{ + auto_ptr<params> p (new params); + query q (query::age > query::_ref (p->age) && + query::first == query::_ref (p->first)); + prep_query pq (c.prepare_query<person> (name, q)); + c.cache_query (pq, p); +} + +db.query_factory ("person-age-name-query", &query_factory); + +for (unsigned short age (90); age > 40; age -= 10) +{ + transaction t (db.begin ()); + + params* p; + prep_query pq (db.lookup_query<person> ("person-age-name-query", p)); + assert (pq); + + p->age = age; + p->first = "John"; + result r (pq.execute ()); + ... + + t.commit (); +} + </pre> + + <p>In C++11 we could have instead used a lambda function as well as + <code>unique_ptr</code> rather than <code>auto_ptr</code>:</p> + + <pre> +db.query_factory ( + "person-age-name-query", + [] (const char* name, connection& c) + { + unique_ptr<params> p (new params); + query q (query::age > query::_ref (p->age) && + query::first == query::_ref (p->first)); + prep_query pq (c.prepare_query<person> (name, q)); + c.cache_query (pq, std::move (p)); + }); + </pre> + <!-- CHAPTER --> <hr class="page-break"/> @@ -17153,9 +17620,9 @@ namespace odb errors to the <code>object_already_persistent</code> exception (<a href="#3.14">Section 3.14, "ODB Exceptions"</a>).</p> - <h3><a name="17.5.4">17.5.4 Multithreaded Windows Applications</a></h3> + <h3><a name="17.5.4">17.5.4 Multi-threaded Windows Applications</a></h3> - <p>Multithreaded Windows applications must use the + <p>Multi-threaded Windows applications must use the <code>_beginthread()</code>/<code>_beginthreadex()</code> and <code>_endthread()</code>/<code>_endthreadex()</code> CRT functions instead of the <code>CreateThread()</code> and <code>EndThread()</code> |