From 60a14a9eada33984f8f259e082b5e621270ac8d1 Mon Sep 17 00:00:00 2001 From: Boris Kolpackov Date: Tue, 27 Sep 2011 11:35:29 +0200 Subject: Add view example --- view/driver.cxx | 308 ++++++++++++++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 308 insertions(+) create mode 100644 view/driver.cxx (limited to 'view/driver.cxx') 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 +// copyright : not copyrighted - public domain + +#include // std::auto_ptr +#include + +#include +#include +#include + +#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 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 ca (new country ("CA", "Canada")); + shared_ptr za (new country ("ZA", "South Africa")); + shared_ptr us (new country ("US", "United States")); + shared_ptr se (new country ("SE", "Sweden")); + + shared_ptr st (new employer (1, "Simple Tech Ltd")); + shared_ptr cs (new employer (2, "Complex Systems Inc")); + + shared_ptr e1 ( + new employee (1, "John", "Doe", 29, ca, ca, st)); + + shared_ptr e2 ( + new employee (2, "Jane", "Doe", 30, za, us, cs)); + + shared_ptr e3 ( + new employee (3, "Joe", "Dirt", 31, us, za, st)); + + shared_ptr 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 query; + typedef odb::result result; + + transaction t (db->begin ()); + + result r (db->query (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 r ( + db->query (query::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 query; + typedef odb::result result; + + transaction t (db->begin ()); + + // Note that we need to add the object name after query::. + // + result r (db->query (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 query; + typedef odb::result result; + + transaction t (db->begin ()); + + result r (db->query ()); + + // Some other interesting queries to try: + // + // This one restricts the calculation to a specific employer: + // + // result r (db->query ( + // query::employer::name == "Simple Tech Ltd")); + // + // And this one filters the employees based on certain criteria. + // + // result r (db->query ( + // 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 query; + typedef odb::result result; + + transaction t (db->begin ()); + + // Note that we use the alias given in the object pragma after query::. + // + result r (db->query ( + 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 result; + + transaction t (db->begin ()); + + // With native views we have to use the native query syntax. + // + result r (db->query ("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 result; + + transaction t (db->begin ()); + + result r (db->query ("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 query; + typedef odb::result result; + + transaction t (db->begin ()); + + result r (db->query ()); + + cout << "Previous employees" << endl; + + for (result::iterator i (r.begin ()); i != r.end (); ++i) + { + const nullable& 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; + } +} -- cgit v1.1