Add view example

1 files changed, 308 insertions, 0 deletions

diff --git a/view/driver.cxx b/view/driver.cxx
new file mode 100644
index 0000000..ccd8d2d
--- /dev/null
+++ b/view/driver.cxx
@@ -0,0 +1,308 @@
+// file      : view/driver.cxx
+// author    : Boris Kolpackov <boris@codesynthesis.com>
+// copyright : not copyrighted - public domain
+
+#include <memory>   // std::auto_ptr
+#include <iostream>
+
+#include <odb/database.hxx>
+#include <odb/session.hxx>
+#include <odb/transaction.hxx>
+
+#include "database.hxx" // create_database
+
+#include "employee.hxx"
+#include "employee-odb.hxx"
+
+using namespace std;
+using namespace odb::core;
+
+int
+main (int argc, char* argv[])
+{
+  try
+  {
+    auto_ptr<database> db (create_database (argc, argv));
+
+    // Create the legacy employee_extra table.
+    //
+    {
+      // First try to drop the table if it exists.
+      //
+      {
+        transaction t (db->begin ());
+        try
+        {
+          db->execute ("DROP TABLE view_employee_extra");
+          t.commit ();
+        }
+        catch (const odb::exception&)
+        {
+        }
+      }
+
+      {
+        transaction t (db->begin ());
+
+        db->execute (
+          "CREATE TABLE view_employee_extra ("
+          "employee_id INTEGER NOT NULL,"
+          "vacation_days INTEGER NOT NULL,"
+          "previous_employer_id INTEGER)");
+
+        t.commit ();
+      }
+    }
+
+    // Create a few persistent objects.
+    //
+    {
+      shared_ptr<country> ca (new country ("CA", "Canada"));
+      shared_ptr<country> za (new country ("ZA", "South Africa"));
+      shared_ptr<country> us (new country ("US", "United States"));
+      shared_ptr<country> se (new country ("SE", "Sweden"));
+
+      shared_ptr<employer> st (new employer (1, "Simple Tech Ltd"));
+      shared_ptr<employer> cs (new employer (2, "Complex Systems Inc"));
+
+      shared_ptr<employee> e1 (
+        new employee (1, "John", "Doe", 29, ca, ca, st));
+
+      shared_ptr<employee> e2 (
+        new employee (2, "Jane", "Doe", 30, za, us, cs));
+
+      shared_ptr<employee> e3 (
+        new employee (3, "Joe", "Dirt", 31, us, za, st));
+
+      shared_ptr<employee> e4 (
+        new employee (4, "Johan", "Johansen", 32, se, se, cs));
+
+      transaction t (db->begin ());
+
+      db->persist (ca);
+      db->persist (za);
+      db->persist (us);
+      db->persist (se);
+
+      db->persist (st);
+      db->persist (cs);
+
+      db->persist (e1);
+      db->persist (e2);
+      db->persist (e3);
+      db->persist (e4);
+
+      // Populate the legacy table.
+      //
+      db->execute ("INSERT INTO view_employee_extra ("
+                   "employee_id, vacation_days, previous_employer_id)"
+                   "VALUES (1, 5, 2)");
+
+      db->execute ("INSERT INTO view_employee_extra ("
+                   "employee_id, vacation_days, previous_employer_id)"
+                   "VALUES (2, 10, NULL)");
+
+      db->execute ("INSERT INTO view_employee_extra ("
+                   "employee_id, vacation_days, previous_employer_id)"
+                   "VALUES (3, 0, NULL)");
+
+      db->execute ("INSERT INTO view_employee_extra ("
+                   "employee_id, vacation_days, previous_employer_id)"
+                   "VALUES (4, 15, 1)");
+
+      t.commit ();
+    }
+
+    // Load names of the employees that are under 31 using the employee_name
+    // view.
+    //
+    {
+      typedef odb::query<employee_name> query;
+      typedef odb::result<employee_name> result;
+
+      transaction t (db->begin ());
+
+      result r (db->query<employee_name> (query::age < 31));
+
+      cout << "Employees under 31" << endl;
+
+      for (result::iterator i (r.begin ()); i != r.end (); ++i)
+        cout << "  " << i->first << " " << i->last << endl;
+
+      cout << endl;
+
+      t.commit ();
+    }
+
+    // Count the number of employees which has the Doe last name using the
+    // employee_count view.
+    //
+    {
+      transaction t (db->begin ());
+
+      result<employee_count> r (
+        db->query<employee_count> (query<employee_count>::last == "Doe"));
+
+      // Results of this aggregate query contains only one element.
+      //
+      cout << r.begin ()->count << " employees with the Doe last name" << endl
+           << endl;
+
+      t.commit ();
+    }
+
+    // Load the employee-employer information for all the employess with the
+    // Doe last name using the employee_employer view.
+    //
+    {
+      typedef odb::query<employee_employer> query;
+      typedef odb::result<employee_employer> result;
+
+      transaction t (db->begin ());
+
+      // Note that we need to add the object name after query::.
+      //
+      result r (db->query<employee_employer> (query::employee::last == "Doe"));
+
+      cout << "Employees with the Doe last name" << endl;
+
+      for (result::iterator i (r.begin ()); i != r.end (); ++i)
+        cout << "  " << i->first << " " << i->last <<  " "
+             << i->employer_name << endl;
+
+      cout << endl;
+
+      t.commit ();
+    }
+
+    // Calculate average ages of all employees for each employer.
+    //
+    {
+      typedef odb::query<employer_age> query;
+      typedef odb::result<employer_age> result;
+
+      transaction t (db->begin ());
+
+      result r (db->query<employer_age> ());
+
+      // Some other interesting queries to try:
+      //
+      // This one restricts the calculation to a specific employer:
+      //
+      // result r (db->query<employer_age> (
+      //             query::employer::name == "Simple Tech Ltd"));
+      //
+      // And this one filters the employees based on certain criteria.
+      //
+      // result r (db->query<employer_age> (
+      //             query::employee::last == "Doe"));
+      //
+
+      cout << "Man/max employee ages" << endl;
+
+      for (result::iterator i (r.begin ()); i != r.end (); ++i)
+        cout << "  " << i->employer_name << " "
+             << i->min_age << '/' << i->max_age << endl;
+
+      cout << endl;
+
+      t.commit ();
+    }
+
+    // Load the country information employees different residence and
+    // nationality.
+    //
+    {
+      typedef odb::query<employee_country> query;
+      typedef odb::result<employee_country> result;
+
+      transaction t (db->begin ());
+
+      // Note that we use the alias given in the object pragma after query::.
+      //
+      result r (db->query<employee_country> (
+                  query::res_country::name != query::nat_country::name));
+
+      cout << "Employees residing outside of country of nationality" << endl;
+
+      for (result::iterator i (r.begin ()); i != r.end (); ++i)
+        cout << "  " << i->first << " " << i->last << " "
+             << i->res_country_name << " " << i->nat_country_name << endl;
+
+      cout << endl;
+
+      t.commit ();
+    }
+
+    // Get the list of employees that have accumulated vacation days.
+    //
+    {
+      typedef odb::result<employee_vacation> result;
+
+      transaction t (db->begin ());
+
+      // With native views we have to use the native query syntax.
+      //
+      result r (db->query<employee_vacation> ("vacation_days != 0"));
+
+      cout << "Employees with accumulated vacation days" << endl;
+
+      for (result::iterator i (r.begin ()); i != r.end (); ++i)
+        cout << "  " << i->id << " " << i->days << endl;
+
+      cout << endl;
+
+      t.commit ();
+    }
+
+    // Get the list of employees that have accumulated vacation days,
+    // this time using the improved employee_vacation2 view.
+    //
+    {
+      typedef odb::result<employee_vacation2> result;
+
+      transaction t (db->begin ());
+
+      result r (db->query<employee_vacation2> ("vacation_days != 0"));
+
+      cout << "Employees with accumulated vacation days (take 2)" << endl;
+
+      for (result::iterator i (r.begin ()); i != r.end (); ++i)
+        cout << "  " << i->first << " " << i->last << " " << i->days << endl;
+
+      cout << endl;
+
+      t.commit ();
+    }
+
+    // Show the previous employers using the employee_prev_employer view.
+    //
+    {
+      typedef odb::query<employee_prev_employer> query;
+      typedef odb::result<employee_prev_employer> result;
+
+      transaction t (db->begin ());
+
+      result r (db->query<employee_prev_employer> ());
+
+      cout << "Previous employees" << endl;
+
+      for (result::iterator i (r.begin ()); i != r.end (); ++i)
+      {
+        const nullable<string>& pe (i->prev_employer_name);
+
+        cout << "  " << i->first << " " << i->last << " "
+             << (pe.null () ? string ("N/A") : *pe) << endl;
+      }
+
+      cout << endl;
+
+      t.commit ();
+    }
+  }
+  catch (const odb::exception& e)
+  {
+    cerr << e.what () << endl;
+    return 1;
+  }
+}