Table of Contents
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
.
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(); }