Chapter 6. Advanced techniques

Table of Contents

Restricting values

Sometimes it makes sense to restrict the possible field values that the user can enter into a form or grid. Suppose you have a table that contains orders for some customers. For that customers field, it might makes sense to only ever contain the name of an existing customer rather than any possible freeform text.

To achieve this, we should firstly create a Gnome::Gda::DataModel holding the possible values for the customers field, by creating a Gnome::Gda::DataModelArray.

const Glib::ustring customers[] = { "Ed Lamton", "Lew Bonito", "Mark Lawrenscep", "Grep Popoff", "Vladimir Zirkov" };
Glib::RefPtr<Gnome::Gda::DataModelArray> restrict_model = Gnome::Gda::DataModelArray::create(1 /* number of columns */);
for(int i = 0; i < 5 /* number of rows */; ++ i)
{
  // Vector containing the values for each column in this row. Since we only
  // have one column, the vector contains only one element.
  std::vector<Gnome::Gda::Value> row_values;
  row_values.push_back(Gnome::Gda::Value(customers[i]));
  restrict_model->append_values(row_values);
}

The first line defines the customer names to which we would like to restrict the values. Then a new Gnome::Gda::DataModelArray is created with one single column. This column will contain these customer names. It is also possible to create a model with more columns, to assign additional information to each value. Next, each string is converted into a Gnome::Gda::Value. Gnome::Gda::Value is a generic value object that can hold a value of any type that is commonly used in database management systems. The value is then packed into a vector that is given to the previously-created model. The number of elements in the vector must match the number of columns of the model - this is also why we create a vector with only one single element in it.

To actually perform the restriction on a grid or form, the following steps are required, assuming that grid is a Gnome::Db::Grid):

Glib::RefPtr<Gnome::Gda::DataModelIter> iter = grid.get_raw_grid()->get_current_data();
iter->restrict_param_by_name("customer", restrict_model, 0);

Every field in the table is represented by a Gnome::Gda::Parameter object in libgda. It might be looked up by its field name from a Gnome::Gda::DataModelIter which we in turn get from the grid (or form respectively). Such an iterator may be used to traverse row-wise through a Gnome::Gda::DataModel and it also manages the parameters for each column. The restrict_param_by_name() function does first look up a parameter by its field name and then restrict the values for this parameter to those contained in the first column (index 0) of the model created above.

While this basically works, a much more elegant way to achieve the same functionality is to read the restriction values from another table in the database. Suppose there is another table called "customers" that, in addition to a unique ID, stores the available customer names. The "orders" table then just holds an integer ID referring to the corresponding entry in the customers table instead of the customer name itself.

Instead of the Gnome::Gda::DataModelArray, another Gnome::Gda::DataModelQuery is now required that reads the customer names from the corresponding table. The assignment of that model to the Gnome::Gda::Parameter works as above.

Glib::RefPtr<Gnome::Gda::DataModelQuery> model = Gnome::Gda::DataModelQuery::create(dict);
model->set_sql_text("SELECT id, name FROM customers");

Glib::RefPtr<Gnome::Gda::DataModelIter> iter = grid.get_raw_grid()->get_current_data();
iter->restrict_param_by_name("customer", model, 0);

Note that libgnomedb is smart enough to not show the numerical ID which is contained in the 'customer' field of the 'orders' table. Instead, it takes the 'name' of the customer that is stored in the second column of model (because the name was mentioned in the SELECT query when using set_sql_text() in our example). If our SELECT command had requested only the 'id' field, then libgnomedb would have shown only the ID field. model.

Example

Figure 6.1. Restricted fields

Restricted fields

Source Code

File: examplewindow.h

#include <memory>

#include <gtkmm.h>
#include <libgdamm.h>
#include <libgnomedbmm.h>

class ExampleWindow : public Gtk::Window
{
public:
  ExampleWindow(const Glib::RefPtr<Gnome::Gda::Dict>& dict);
    
private:
#ifdef GLIBMM_EXCEPTIONS_ENABLED
  void create_grid(const Glib::RefPtr<Gnome::Gda::Dict>& dict);
#else
  void create_grid(const Glib::RefPtr<Gnome::Gda::Dict>& dict, std::auto_ptr<Glib::Error>& error);
#endif // GLIBMM_EXCEPTIONS_ENABLED

  Glib::RefPtr<Gnome::Gda::DataModelQuery> m_model;
    
  Gtk::VBox m_box;
  Gtk::Label m_label;
  Gnome::Db::Grid* m_grid;
};

File: main.cc

#include <libgnomedbmm.h>
#include <libgdamm.h>
#include <gtkmm.h>

#include <iostream>

#include "examplewindow.h"

#ifdef GLIBMM_EXCEPTIONS_ENABLED
Glib::RefPtr<Gnome::Gda::Dict> create_dict()
#else
Glib::RefPtr<Gnome::Gda::Dict> create_dict(std::auto_ptr<Glib::Error>& error)
#endif
{
  Glib::RefPtr<Gnome::Gda::Dict> dict = Gnome::Gda::Dict::create();
  Glib::RefPtr<Gnome::Gda::Client> client = Gnome::Gda::Client::create();
  Glib::ustring connection_string = "DB_DIR=" LIBGNOMEDB_DATADIR ";DB_NAME=demo_db";

#ifdef GLIBMM_EXCEPTIONS_ENABLED
  Glib::RefPtr<Gnome::Gda::Connection> cnc = client->open_connection_from_string("SQLite", connection_string, "" /* username */, "" /* password */);
  dict->set_connection(cnc);
  dict->update_dbms_meta_data();
#else
  Glib::RefPtr<Gnome::Gda::Connection> cnc = client->open_connection_from_string("SQLite", connection_string, "", "", Gnome::Gda::ConnectionOptions(0), error);
  if(error.get() == NULL)
  {
    dict->set_connection(cnc);
    dict->update_dbms_meta_data(error);
  }
#endif

  return dict;
}

int main(int argc, char* argv[])
{
  Gtk::Main kit(argc, argv);
  Gnome::Db::init("Grid example", "1.0", argc, argv);

  Glib::RefPtr<Gnome::Gda::Dict> dict;

#ifdef GLIBMM_EXCEPTIONS_ENABLED
  try
  {
    dict = create_dict();
  }
  catch(const Glib::Error& err)
  {
    std::cerr << "Exception caught: " << err.what() << std::endl;
    return 1;
  }
#else
  std::auto_ptr<Glib::Error> error;
  dict = create_dict(error);

  if(error.get() != NULL)
  {
    std::cerr << "Exception caught: " << error->what() << std::endl;
    return 1;
  }
#endif

  ExampleWindow window(dict);

  kit.run(window);
  return 0;
}

File: examplewindow.cc

#include "examplewindow.h" 

#include <libgdamm.h>
#include <iostream>

#ifdef GLIBMM_EXCEPTIONS_ENABLED
void ExampleWindow::create_grid(const Glib::RefPtr<Gnome::Gda::Dict>& dict)
#else
void ExampleWindow::create_grid(const Glib::RefPtr<Gnome::Gda::Dict>& dict, std::auto_ptr<Glib::Error>& error)
#endif // GLIBMM_EXCEPTIONS_ENABLED
{
  // Select relevant order fields from database
  Glib::RefPtr<Gnome::Gda::Query> query = Gnome::Gda::Query::create(dict);
#ifdef GLIBMM_EXCEPTIONS_ENABLED
  query->set_sql_text("SELECT id, customer, creation_date FROM orders");
#else
  query->set_sql_text("SELECT id, customer, creation_date FROM orders", error);
  if(error.get() != NULL) return;
#endif // GLIBMM_EXCEPTIONS_ENABLED
  m_model = Gnome::Gda::DataModelQuery::create(query);

  // This selects fields from the customers table to restrict the customer
  // field in the datamodel above to the values that are in the customers
  // table. We also select name to show the user the name of the customer
  // in the grid instead of just its ID (as it would appear in the orders
  // table).
  Glib::RefPtr<Gnome::Gda::Query> restr_query = Gnome::Gda::Query::create(dict);

#ifdef GLIBMM_EXCEPTIONS_ENABLED
  restr_query->set_sql_text("SELECT id, name FROM customers");
#else
  restr_query->set_sql_text("SELECT id, name FROM customers", error);
  if(error.get() != NULL) return;
#endif // GLIBMM_EXCEPTIONS_ENABLED

  Glib::RefPtr<Gnome::Gda::DataModel> restr = Gnome::Gda::DataModelQuery::create(restr_query);

  const Glib::ustring update_query = 
                 "UPDATE orders set "
                 "customer=##/*name:'+1' type:gint*/ "
                 "WHERE id=##/*name:'-0' type:gint*/";

  const Glib::ustring delete_query =
                 "DELETE FROM orders WHERE id=##/*name:'-0' type:gint*/";

  const Glib::ustring insert_query =
                 "INSERT INTO orders (customer) "
                 "VALUES (##/*name:'+1' type:gint*/)";

#ifdef GLIBMM_EXCEPTIONS_ENABLED
  m_model->set_modification_query(update_query);
  m_model->set_modification_query(delete_query);
  m_model->set_modification_query(insert_query);
#else
  m_model->set_modification_query(update_query, error);
  m_model->set_modification_query(delete_query, error);
  m_model->set_modification_query(insert_query, error);
  if(error.get() != NULL) return;
#endif // GLIBMM_EXCEPTIONS_ENABLED

  m_grid = Gtk::manage(new Gnome::Db::Grid(m_model));
  Glib::RefPtr<Gnome::Gda::DataModelIter> iter = m_grid->get_raw_grid()->get_current_data();

  // This function first looks up the parameter by its field name "customer"
  // and then restricts the values it can hold to those in the first
  // column (index 0) of the customers table.
  //
  // There is also some gda magic involved so that the grid actually
  // shows the name of the customer of the ID. I am not absolutely sure
  // how this works.
#ifdef GLIBMM_EXCEPTIONS_ENABLED
  iter->restrict_param_by_name("customer", restr, 0);
#else
  iter->restrict_param_by_name("customer", restr, 0, error);
#endif // GLIBMM_EXCEPTIONS_ENABLED
}

ExampleWindow::ExampleWindow(const Glib::RefPtr<Gnome::Gda::Dict>& dict)
{    
  m_box.set_border_width(6);
  m_box.pack_start(m_label, Gtk::PACK_SHRINK);

#ifdef GLIBMM_EXCEPTIONS_ENABLED
  try
  {
    create_grid(dict);
  }
  catch(const Glib::Error& err)
  {
    std::cerr << "Exception caught: " << err.what() << std::endl;
    exit(1);
  }
#else
  std::auto_ptr<Glib::Error> error;
  create_grid(dict, error);
  if(error.get() != NULL)
  {
    std::cerr << "Exception caught: " << error->what() << std::endl;
    exit(1);
  }
#endif

  m_box.pack_start(*m_grid);
  add(m_box);
  set_default_size(400, 400);
  show_all();
}