diff options
Diffstat (limited to 'odb-examples/view/driver.cxx')
-rw-r--r-- | odb-examples/view/driver.cxx | 416 |
1 files changed, 416 insertions, 0 deletions
diff --git a/odb-examples/view/driver.cxx b/odb-examples/view/driver.cxx new file mode 100644 index 0000000..eed0d4b --- /dev/null +++ b/odb-examples/view/driver.cxx @@ -0,0 +1,416 @@ +// file : view/driver.cxx +// copyright : not copyrighted - public domain + +#include <memory> // std::unique_ptr, std::shared_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 + { + unique_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 + { +#ifndef DATABASE_ORACLE + db->execute ("DROP TABLE view_employee_extra"); +#else + db->execute ("DROP TABLE \"view_employee_extra\""); +#endif + t.commit (); + } + catch (const odb::exception&) + { + } + } + + { + transaction t (db->begin ()); + +#ifndef DATABASE_ORACLE + db->execute ( + "CREATE TABLE view_employee_extra (" + "employee_id INTEGER NOT NULL," + "vacation_days INTEGER NOT NULL," + "previous_employer_id INTEGER)"); +#else + db->execute ( + "CREATE TABLE \"view_employee_extra\" (" + "\"employee_id\" INTEGER NOT NULL," + "\"vacation_days\" INTEGER NOT NULL," + "\"previous_employer_id\" INTEGER)"); +#endif + + 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. + // +#ifndef DATABASE_ORACLE + 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)"); +#else + 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)"); +#endif + + 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 of an aggregate query contains only one element so let's + // use the query_value() shortcut. + // + employee_count ec ( + db->query_value<employee_count> ( + query<employee_count>::last == "Doe")); + + cout << ec.count << " employees with the Doe last name" << endl + << endl; + + t.commit (); + } + + // Load the employee-employer information for all the employees 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 min/max employee ages for each employer. + // + { + 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: + // + // typedef odb::query<employer_age> query; + // + // 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 << "Min/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 for employees with 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 db 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 (); + } + + // The same but using the object loading view. + // + { + typedef odb::query<employee_country_objects> query; + typedef odb::result<employee_country_objects> result; + + transaction t (db->begin ()); + + // We have to use a session in order for the object pointers + // in our view and object pointers inside objects that we load + // to point to the same instances, where appropriate. + // + session s; + + result r (db->query<employee_country_objects> ( + query::res::name == query::nat::name)); + + cout << "Employees residing inside the country of nationality" << endl; + + for (result::iterator i (r.begin ()); i != r.end (); ++i) + { + assert (i->e->nationality () == i->nat); + assert (i->e->residence () == i->res); + + const employee& e (*i->e); + const country& r (*i->res); + const country& n (*i->nat); + + cout << " " << e.first () << " " << e.last () << " " + << r.name () << " " << n.name () << endl; + } + + cout << endl; + + t.commit (); + } + + // Get the list of employers that have any employees. + // + { + typedef odb::result<employer_with_employees> result; + + shared_ptr<employer> es (new employer (3, "Empty Shell LLC")); + + transaction t (db->begin ()); + db->persist (es); + + result r (db->query<employer_with_employees> ()); + + cout << "Employers with employees" << endl; + + for (result::iterator i (r.begin ()); i != r.end (); ++i) + cout << " " << i->e->name () << endl; + + cout << endl; + + db->erase (es); + 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 SQL query syntax. + // +#ifndef DATABASE_ORACLE + result r (db->query<employee_vacation> ("vacation_days <> 0")); +#else + result r (db->query<employee_vacation> ("\"vacation_days\" <> 0")); +#endif + + 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 ()); + +#ifndef DATABASE_ORACLE + result r (db->query<employee_vacation2> ("vacation_days <> 0")); +#else + result r (db->query<employee_vacation2> ("\"vacation_days\" <> 0")); +#endif + + 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::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; + } +} |