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/employee.hxx | 287 ++++++++++++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 287 insertions(+) create mode 100644 view/employee.hxx (limited to 'view/employee.hxx') diff --git a/view/employee.hxx b/view/employee.hxx new file mode 100644 index 0000000..4ff4f1b --- /dev/null +++ b/view/employee.hxx @@ -0,0 +1,287 @@ +// file : view/employee.hxx +// author : Boris Kolpackov +// copyright : not copyrighted - public domain + +#ifndef EMPLOYEE_HXX +#define EMPLOYEE_HXX + +#include +#include // std::size_t + +#include +#include + +// Include TR1 header in a compiler-specific fashion. Fall back +// on the Boost implementation if the compiler does not support TR1. +// +#include + +using std::tr1::shared_ptr; + +#pragma db object +class country +{ +public: + country (const std::string& code, std::string const& name) + : code_ (code), name_ (name) + { + } + + const std::string& + name () const + { + return name_; + } + +private: + friend class odb::access; + + country () {} + + #pragma db id + std::string code_; // ISO 2-letter country code. + + std::string name_; +}; + +#pragma db object +class employer +{ +public: + employer (unsigned long id, const std::string& name) + : id_ (id), name_ (name) + { + } + + const std::string& + name () const + { + return name_; + } + +private: + friend class odb::access; + + employer () {} + + #pragma db id + unsigned long id_; + + std::string name_; +}; + +#pragma db object +class employee +{ +public: + employee (unsigned long id, + const std::string& first, + const std::string& last, + unsigned short age, + shared_ptr res, + shared_ptr nat, + shared_ptr e) + : id_ (id), first_ (first), last_ (last), age_ (age), + residence_ (res), + nationality_ (nat), + employed_by_ (e) + { + } + + // Name. + // + const std::string& + first () const + { + return first_; + } + + const std::string& + last () const + { + return last_; + } + + // Employer. + // + shared_ptr + employed_by () const + { + return employed_by_; + } + + // Residence and nationality. + // + shared_ptr + residence () const + { + return residence_; + } + + shared_ptr + nationality () const + { + return nationality_; + } + +private: + friend class odb::access; + + employee () {} + + #pragma db id + unsigned long id_; + + std::string first_; + std::string last_; + + unsigned short age_; + + shared_ptr residence_; + shared_ptr nationality_; + + shared_ptr employed_by_; +}; + +// We also have a "legacy" employee_extra table that is not mapped to any +// object. It has the following columns: +// +// CREATE TABLE employee_extra( +// employee_id INTEGER NOT NULL, +// vacation_days INTEGER NOT NULL, +// previous_employer_id INTEGER) +// + +// A simple view with a single associated object. It allows us to get +// the name of an employee without loading any of the other data, such +// as the country and employer objects. The first and last data members +// in the view are automatically assumed to correspond to the first_ and +// last_ members in the employee object. +// +#pragma db view object(employee) +struct employee_name +{ + std::string first; + std::string last; +}; + +// A simple aggregate view. It allows us to count the number of employees +// matching certain criteria. Here we use a column expression with a +// reference to the id_ data member in the employee object. +// +#pragma db view object(employee) +struct employee_count +{ + #pragma db column("count(" + employee::id_ + ")") + std::size_t count; +}; + +// A simple view with two associated object. It allows us to get the +// name of an employee and its employer without loading any other data. +// Because there is a relationship between the employee and employer +// objects (employee::employed_by_), the ODB compiler automatically +// used this relationship as a join condition. Also, similar to the +// employee_name view, the first and last data members are automatically +// assumed to correspond to the first_ and last_ members in the employee +// object. For the employer_name member we provide an explicit member +// reference. +// +#pragma db view object(employee) object(employer) +struct employee_employer +{ + std::string first; + std::string last; + + #pragma db column(employer::name_) + std::string employer_name; +}; + +// A more interesting aggregate view using GROUP BY. It allows us to +// calculate the min/max age of employees for each employer. Here we +// use the C++-integrated syntax for the query condition template with +// a placeholder (?). +// +#pragma db view object(employee) object(employer) \ + query ((?) + "GROUP BY" + employer::name_) +struct employer_age +{ + #pragma db column(employer::name_) + std::string employer_name; + + #pragma db column("min(" + employee::age_ + ")") + unsigned short min_age; + + #pragma db column("max(" + employee::age_ + ")") + unsigned short max_age; +}; + +// A more complex view with three associated objects, two of which are +// of the same type, which requires us to use aliases and disambiguate +// the relationships used to join each object. +// +#pragma db view object(employee) \ + object(country = res_country: employee::residence_) \ + object(country = nat_country: employee::nationality_) +struct employee_country +{ + std::string first; + std::string last; + + #pragma db column(res_country::name_) + std::string res_country_name; + + #pragma db column(nat_country::name_) + std::string nat_country_name; +}; + +// A native view. A native view provides a complete query and is normally +// based on an ad-hoc table. This view allows us to load the employee +// vacation information from the legacy employee_extra table. +// +#pragma db view query("SELECT employee_id, vacation_days " \ + "FROM view_employee_extra") +struct employee_vacation +{ + #pragma db type("INTEGER") + unsigned long id; + + #pragma db type("INTEGER") + unsigned short days; +}; + +// An improved version of the previous view that extracts the employee +// first and last names instead of the id. To get the names we need to +// add the employee object to this view and use a custom join condition +// to tie it up with our legacy table. +// +#pragma db view table("view_employee_extra") \ + object(employee: "view_employee_extra.employee_id = " + employee::id_) +struct employee_vacation2 +{ + std::string first; + std::string last; + + #pragma db column("view_employee_extra.vacation_days") type("INTEGER") + unsigned short days; +}; + +// An advanced view that joins two objects via a legacy table. It returns +// the previous employer information for each employee. +// +#pragma db view object(employee) \ + table("view_employee_extra" = "extra": \ + "extra.employee_id = " + employee::id_) \ + object(employer: "extra.previous_employer_id = " + employer::id_) +struct employee_prev_employer +{ + std::string first; + std::string last; + + // If previous_employer_id is NULL, then the name will be NULL as well. + // We use the odb::nullable wrapper to handle this. + // + #pragma db column(employer::name_) + odb::nullable prev_employer_name; +}; + +#endif // EMPLOYEE_HXX -- cgit v1.1