From 7fc555e53f0a03c93fe31ad9850b1e5d885c44f6 Mon Sep 17 00:00:00 2001 From: Boris Kolpackov Date: Fri, 19 Oct 2012 11:37:20 +0200 Subject: Document prepared query support --- NEWS | 7 + doc/manual.xhtml | 477 ++++++++++++++++++++++++++++++++++++++++++++++++++++++- 2 files changed, 479 insertions(+), 5 deletions(-) diff --git a/NEWS b/NEWS index 84a6330..e863ffe 100644 --- a/NEWS +++ b/NEWS @@ -1,5 +1,12 @@ Version 2.2.0 + * Support for prepared queries. Prepared queries are a thin wrapper around + the underlying database system's prepared statements functionality. They + provide a way to perform potentially expensive query preparations tasks + only once and then executing the query multiple time. For more information, + refer to Section 4.5, "Prepared Queries" in the ODB manual as well as the + 'prepared' example in the odb-examples package. + * Support for early connection release. Now the database connection is released when commit()/rollback() is called rather than when the transaction instance goes out of scope. 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. 4.2Parameter Binding 4.3Executing a Query 4.4Query Result + 4.5Prepared Queries @@ -683,7 +684,7 @@ for consistency. 17.5.1Query Result Caching 17.5.2Foreign Key Constraints 17.5.3Unique Constraint Violations - 17.5.4Multithreaded Windows Applications + 17.5.4Multi-threaded Windows Applications 17.5.5Affected Row Count and DDL Statements 17.5.6Long Data and Automatically Assigned Object Ids 17.5.7Long Data and By-Value Accessors/Modifiers @@ -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 Section 8.2, "Polymorphism Inheritance".

+

The prepared_already_cached exception is thrown by the + cache_query() function if a prepared query with the + specified name is already cached. The prepared_type_mismatch + exception is thrown by the lookup_query() function if + the specified prepared query object type or parameters type + does not match the one in the cache. Refer to Section + 4.5, "Prepared Queries" for details.

+

The unknown_schema exception is thrown by the odb::schema_catalog class if a schema with the specified name is not found. Refer to Section 3.4, "Database" @@ -4077,7 +4106,7 @@ namespace odb example:

-  query q ("first = 'John'" + (query::age < query::_ref (age)));
+  query q ("first = 'John' AND" + (query::age < query::_ref (age)));

4.1 ODB Query Language

@@ -4312,7 +4341,9 @@ namespace odb

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 + Section 4.5, "Prepared Queries" 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 } + +

4.5 Prepared Queries

+ +

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.

+ +

In ODB all the non-query database operations such as + persist(), load(), update(), + etc., are implemented in terms of prepared statements that are + cached and reused. While the query() 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.

+ +

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 odb::database + 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 database::begin()), + 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 odb::database instance. + For more information on connection factories, refer to + Part II, "Database Systems".

+ +

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.

+ +

To enable the prepared query functionality we need to specify + the --generate-prepared 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 --omit-unprepared option.

+ +

To prepare a query we use the prepare_query() function + template. This function can be called on both the odb::database + and odb::connection instances. The odb::database + version simply obtains the connection used by the currently active + transaction and calls the corresponding odb::connection + version. If no transaction is currently active, then this function + throws the odb::not_in_transaction exception + (Section 3.5, "Transactions"). The + prepare_query() function has the following signature:

+ + 
+  template <typename T>
+  prepared_query<T>
+  prepare_query (const char* name, const odb::query<T>&);
+
+ +

The first argument to the prepare_query() 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 "object_query" (e.g., + "person_query") is reserved for the once-off queries + executed by the database::query() function. Note that + the prepare_query() function makes only a shallow copy + of this argument, which means that the name must be valid for the + lifetime of the returned prepared_query instance.

+ +

The second argument to the prepare_query() function + is the query criteria. It has the same semantics as in the + query() function discussed in Section + 4.3, "Executing a Query". Similar to query(), we + also have to explicitly specify the object type that we will be + querying. For example:

+ + 
+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));
+
+ +

The result of executing the prepare_query() function is + the prepared_query instance that represent the prepared + query. It is best to view prepared_query as a handle to + the underlying prepared statement. While we can make a copy of it or + assign one prepared_query to another, the two instances + will refer to the same prepared statement. Once the last instance of + prepared_query referencing a specific prepared statement + is destroyed, this statement is released. The prepared_query + class template has the following interface:

+ + 
+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;
+  };
+}
+
+ +

The default constructor creates an empty prepared_query + 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 prepare_query() + function discussed above. To test whether the prepared query is empty, + we can use the implicit conversion operator to a boolean type. For + example:

+ + 
+  prepared_query<person> pq;
+
+  if (pq)
+  {
+    // Not empty.
+    ...
+  }
+
+ +

The execute() function executes the query and returns + the result instance. The cache argument indicates + whether the result should be cached and has the same semantics + as in the query() function. In fact, conceptually, + prepare_query() and execute() are just + the query() function split into two: + prepare_query() takes the first + query() argument (the query condition) while + execute() takes the second (the cache flag). Note + also that re-executing a prepared query invalidates the + previous execution result, whether cached or uncached.

+ +

The name() function returns the prepared query name. + This is the same name as was passed as the first argument in the + prepare_query() call. The statement() + function returns a reference to the underlying prepared statement. + Note also that calling any of these functions on an empty + prepared_query instance results in undefined behavior.

+ +

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 prepared example in the + odb-examples package.

+ + 
+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 ();
+
+ +

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:

+ + 
+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 ();
+}
+
+ + +

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.

+ +

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 odb::database + and odb::connection classes provide the following + function templates. Similar to prepare_query(), + the odb::database versions of the below + functions call the corresponding odb::connection + versions using the currently active transaction to resolve + the connection.

+ + 
+  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;
+
+ +

The cache_query() function caches the passed prepared + query on the connection. The second overloaded version of + cache_query() also takes a pointer to the + by-reference query parameters. In C++98 it should be + std::auto_ptr while in C++11 std::auto_ptr + or std::unique_ptr can be used. The + cache_query() function assumes ownership of the + passed params argument. If a prepared query + with the same name is already cached on this connection, + then the odb::prepared_already_cached exception + is thrown.

+ +

The lookup_query() function looks up a previously + cached prepared query given its name. The second overloaded + version of lookup_query() also returns a pointer + to the by-reference query parameters. If a prepared query + with this name has not been cached, then an empty + prepared_query 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 odb::prepared_type_mismatch + exception is thrown.

+ +

As a first example of the prepared query cache functionality, + consider the case that does not use any by-reference parameters:

+ + 
+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.
+  //
+  ...
+}
+
+ +

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.

+ + 
+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.
+  //
+  ...
+}
+
+ +

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.

+ +

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 lookup_query(). To + register a factory we use the database::query_factory() + function. In C++98 it has the following signature:

+ + 
+  void
+  query_factory (const char* name,
+                 void (*factory) (const char* name, connection&));
+
+ +

While in C++11 it uses the std::function class + template:

+ + 
+  void
+  query_factory (const char* name,
+                 std::function<void (const char* name, connection&)>);
+
+ +

The first argument to the query_factory() 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.

+ +

The example fragment shows how we can use the prepared query + factory:

+ + 
+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 ();
+}
+
+ +

In C++11 we could have instead used a lambda function as well as + unique_ptr rather than auto_ptr:

+ + 
+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));
+  });
+
+
@@ -17153,9 +17620,9 @@ namespace odb errors to the object_already_persistent exception (Section 3.14, "ODB Exceptions").

-

17.5.4 Multithreaded Windows Applications

+

17.5.4 Multi-threaded Windows Applications

-

Multithreaded Windows applications must use the +

Multi-threaded Windows applications must use the _beginthread()/_beginthreadex() and _endthread()/_endthreadex() CRT functions instead of the CreateThread() and EndThread() -- cgit v1.1