From 8338bb049db44d9b470b2a76acc5853b92915238 Mon Sep 17 00:00:00 2001 From: Boris Kolpackov Date: Wed, 17 Oct 2012 09:31:54 +0200 Subject: Add example for prepared query usage --- prepared/driver.cxx | 232 ++++++++++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 232 insertions(+) create mode 100644 prepared/driver.cxx (limited to 'prepared/driver.cxx') diff --git a/prepared/driver.cxx b/prepared/driver.cxx new file mode 100644 index 0000000..470d0b8 --- /dev/null +++ b/prepared/driver.cxx @@ -0,0 +1,232 @@ +// file : prepared/driver.cxx +// copyright : not copyrighted - public domain + +#include // std::auto_ptr +#include +#include + +#include +#include +#include + +#include "database.hxx" // create_database + +#include "person.hxx" +#include "person-odb.hxx" + +using namespace std; +using namespace odb::core; + +struct params +{ + unsigned short age; + string first; +}; + +static void +query_factory (const char* name, connection& c) +{ + typedef odb::query query; + + auto_ptr p (new params); + query q (query::age > query::_ref (p->age) && + query::first == query::_ref (p->first)); + prepared_query pq (c.prepare_query (name, q)); + c.cache_query (pq, p); +} + +static void +print_ages (unsigned short age, odb::result r) +{ + cout << "over " << age << ':'; + + for (odb::result::iterator i (r.begin ()); i != r.end (); ++i) + cout << ' ' << i->age (); + + cout << endl; +} + +int +main (int argc, char* argv[]) +{ + try + { + auto_ptr db (create_database (argc, argv)); + + // Create a few persistent person objects. + // + { + person p1 ("John", "First", 91); + person p2 ("John", "Second", 81); + person p3 ("John", "Third", 71); + person p4 ("John", "Fourth", 61); + person p5 ("John", "Fifth", 51); + + transaction t (db->begin ()); + db->persist (p1); + db->persist (p2); + db->persist (p3); + db->persist (p4); + db->persist (p5); + t.commit (); + } + + typedef odb::query query; + typedef odb::prepared_query prep_query; + typedef odb::result result; + + // Example of an uncached prepared query in the same transaction. + // + { + transaction t (db->begin ()); + + unsigned short age; + query q (query::age > query::_ref (age)); + prep_query pq (db->prepare_query ("person-age-query", q)); + + for (age = 90; age > 40; age -= 10) + { + result r (pq.execute ()); + print_ages (age, r); + } + + t.commit (); + } + + // Example of an uncached prepared query in multiple transactions. + // + // Note that here we have to first obtain a connection, then prepare + // the query using this connection, and finally start each transaction + // that uses the prepared query on this connection. + // + { + connection_ptr conn (db->connection ()); + + unsigned short age; + query q (query::age > query::_ref (age)); + prep_query pq (conn->prepare_query ("person-age-query", q)); + + for (age = 90; age > 40; age -= 10) + { + transaction t (conn->begin ()); + + result r (pq.execute ()); + print_ages (age, r); + + t.commit (); + } + } + + // Example of a cached prepared query without by-reference parameters. + // + for (unsigned short i (0); i < 5; ++i) + { + transaction t (db->begin ()); + + prep_query pq (db->lookup_query ("person-val-age-query")); + + if (!pq) + { + pq = db->prepare_query ( + "person-val-age-query", query::age > 50); + db->cache_query (pq); + } + + result r (pq.execute ()); + print_ages (50, r); + + t.commit (); + } + + // Example of a cached prepared query with by-reference parameters. + // + for (unsigned short age (90); age > 40; age -= 10) + { + transaction t (db->begin ()); + + unsigned short* age_param; + prep_query pq ( + db->lookup_query ("person-ref-age-query", age_param)); + + if (!pq) + { + auto_ptr p (new unsigned short); + age_param = p.get (); + query q (query::age > query::_ref (*age_param)); + pq = db->prepare_query ("person-ref-age-query", q); + db->cache_query (pq, p); // Assumes ownership of p. + } + + *age_param = age; // Initialize the parameter. + result r (pq.execute ()); + print_ages (age, r); + + t.commit (); + } + + // Example of a cached prepared query that uses a query factory. + // + 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-age-name-query", p)); + assert (pq); + + p->age = age; + p->first = "John"; + result r (pq.execute ()); + print_ages (age, r); + + t.commit (); + } + + // In C++11 the above call to query_factory() can be re-written to + // use a lambda function, for example: + // + /* + db->query_factory ( + "person-age-name-query", + [] (const char* name, connection& c) + { + auto_ptr p (new params); + query q (query::age > query::_ref (p->age) && + query::first == query::_ref (p->first)); + prepared_query pq (c.prepare_query (name, q)); + c.cache_query (pq, p); + }); + */ + + // Prepared queries can also be used with views, as shown in the + // following example. + // + { + typedef odb::query query; + typedef odb::prepared_query prep_query; + typedef odb::result result; + + transaction t (db->begin ()); + + unsigned short age; + query q (query::age > query::_ref (age)); + prep_query pq ( + db->prepare_query ("person-count-age-query", q)); + + for (age = 90; age > 40; age -= 10) + { + result r (pq.execute ()); + cout << "over " << age << ": " << r.begin ()->count << endl; + } + + t.commit (); + } + } + catch (const odb::exception& e) + { + cerr << e.what () << endl; + return 1; + } +} -- cgit v1.1