## Introduction to the library

The libmergeant library's aim is to make it easy to manage data within databases. To achieve its purpose, it provides the following features:

- A dynamically managed structure of objects to represent the database structure and the server features (data types, functions, aggregates, tables, constraints on tables, etc). This structure can be used to obtain information on the database structure, it is the data dictionnary; a dictionnary is represented by a single MgConf;

- Some high level widgets to be used in applications (MgSelector and MgWorkForm for instance);

- Some specific objects (MgDataHandler) and widgets (MgDataEntry)to interact with data of most the most common type (string, number, dates, etc) and plugins can be used to add support for other database specific data types;

- A dynamic way to build queries of any kind (even complex ones) with the MgQuery object and encapsulates sending queries to the DBMS and receiving data from it; Each query can have some (optional or not) parameters (MgParameter objects) required for their execution.

- Parameters are grouped together within a MgContext object, which manages them. Parameters can be declared as "shadows" of other parameters (alias parameters) which allow easy dynamic and automatic update of widgets.

- The XML format is used to store all the meta-data (using the MgXmlStorage interface) and mechanisms to retreive references to objects from their XML id (the id stored in the XML file).

Look at the examples in the source distribution of the library, or at the small example below.

## Simple example

This section presents a small example of how to use some powerfull widgets provided with the Libmergeant library (the usage of the data dictionnary is better seen in the source of the 'mg-db-browser' application located in the extra/ directory). This small application will allow direct manipulation of data stored in a database representing a small company's shipments. Each shipment (order) is represented by an entry in the 'orders' table, and the corresponding's contents are listed as entries in the 'order_contents' table. Moreover, the customer the order is for is expressed as a link from the 'orders' table to the 'customers' table.

The sample application will allow to select a customer, will provide a tabular view of all the orders for that customers, and in another tabular view whill display the selected order's contents. The orders and the order's contents can be directly edited from the tabular views.

The following picture is a screenshot of the final sample application to be built. The resulting window allows to select a customer from a drop-down menu at the top ('Mark Lawrence' from Madrid in the example); the middle part shows the orders for the selected customer, and the bottom part shows the selected order's contents (two screens in the example). Even though they are used, all the primary and foreign keys are hidden from the user.

This sample example is built using Glade, and avoids concentrating on the GTK specific layout code.

### Database structure

The database structure is described in the next paragraphs (in PostgreSQL's syntax in this example):

#### The customers table

The 'id' field is the primary key of this table.

    CREATE TABLE customers (
        id serial NOT NULL,
        name character varying(35) NOT NULL,
        default_served_by integer,
        country character varying(20),
        city character varying(30)
    );

#### The orders table

Each row in the table represents an order passed by a customer. The customer is identified through a foreign key (the 'customer' field). The 'id' field is the primary key of this table.

    CREATE TABLE orders (
        id serial NOT NULL,
        customer integer NOT NULL,
        creation_date date DEFAULT now() NOT NULL,
        delivery_before date,
        delivery_date date
    );

#### The order_contents table

Each row in the table represents an item in an order. The referenced order is identified through a foreign key (the 'order_id' field); the referenced item is identified through the 'product_ref' foreign key. This table has no primary key.

    CREATE TABLE order_contents (
        order_id integer NOT NULL,
        product_ref character varying(15) NOT NULL,
        quantity integer DEFAULT 1 NOT NULL,
        discount double precision DEFAULT 0 NOT NULL
    );

#### The products table

Each row in the table represents an item which is sold and can be ordered. The 'ref field is the primary key of this table.

    CREATE TABLE products (
        ref character varying(15) NOT NULL,
        name character varying(20) NOT NULL,
        price double precision,
        wh_stored integer
    );

### Coding the application

#### Creating the data dictionnary

The data dictionnary is necessary for Libmergeant to operate. The generation of a data dictionnary can be done through the library itself, but it's easier to use the 'mg-db-browser' utility provided in the 'extra/' directory.

To create the data dictionnary, simply run 'mg-db-browser', select a data source, and then save the resulting dictionnary into the file of your choice (of course the example code must then be modified to make it load the right XML file).

#### Definition of the queries

Libmergeant's widget work from SELECT queries which need to be provided. The MgWorkGrid widget uses that SELECT query to display data and to let the user modify that data. The first query to use is the query to get the orders when a customer is chosen; this is a query with a single parameter which need to be assigned a value before the query can be executed. The SELECT query is:

    SELECT t2.name, t1.creation_date, t1.delivery_before, t1.delivery_date
    FROM orders AS t1 INNER JOIN customers AS t2 ON (t1.customer=t2.id)
    WHERE t2.id = <VALUE>;

That query needs to be inserted into the data dictionnary, at the end of it, below the <MG_QUERIES> tag. The previous query can be translated into XML, using libmergeant's DTD (a complete description of the DTD is not the point here) as the following:

        <MG_QUERY id="QU2" name="Orders" descr="List of all the orders for one customer" query_type="SEL">
          <MG_TARGET id="QU2:T1" entity_ref="TVorders"/>
          <MG_TARGET id="QU2:T2" entity_ref="TVcustomers"/>
          <MG_QF id="QU2:QF2" type="FIELD" name="Customer" target="QU2:T2" object="TVcustomers:FIname"/>
          <MG_QF id="QU2:QF1" type="FIELD" name="Creation Date" target="QU2:T1" object="TVorders:FIcreation_date"/>
          <MG_QF id="QU2:QF3" type="FIELD" name="Deliver before" target="QU2:T1" object="TVorders:FIdelivery_before"/>
          <MG_QF id="QU2:QF5" type="FIELD" name="Delivery date" target="QU2:T1" object="TVorders:FIdelivery_date"/>
          <MG_QF id="QU2:QF20" type="VAL" name="Customer Id Val" srv_type="DTint4" is_param="t" value_prov="QU3:QF4" is_visible="f"/>
          <MG_QF id="QU2:QF21" type="FIELD" name="Customer Id" target="QU2:T2" object="TVcustomers:FIid" is_visible="f"/>
          <MG_QF id="QU2:QF4" type="FIELD" name="Order Id" target="QU2:T1" object="TVorders:FIid" is_visible="f"/>
          <MG_JOIN target1="QU2:T1" target2="QU2:T2" join_type="INNER"/>
          <MG_COND id="QU2:C1" type="EQ" l_op="QU2:QF21" r_op="QU2:QF20"/>
        </MG_QUERY>

Most of the tags contain an "id" attribute (the XML ID of the represented object); the choice of the IDs is arbitrary with a few rules to resect. Here that query is identified as "QU2", and the fields as "QU2:QFx". The joins don't have any ID. Notice that the parameter is created using a field which is a value and which has a "is_param" attribute set to TRUE (its XML ID is "QU2:QF20").

The problem with that query is that the query's parameter is not chosen among the existing customers, but is an integer value to be entered manually. The would be resulting application is shown in the next screenshot where the drop down choice has been replaced by a simple numreical value to be entered.

This problem can be solved by giving a hint to the library, within the query, to tell that the parameter must be chosen among the existing customers. The modifications from the previous query are:

- Addition of a SELECT query (QU3) which lists the customers we want to choose from, in the <MG_PARAM_SOURCES> tag of the query

- a new "value_provider" property to the "QU2:QF20" parameter pointing to the "QU3:QF4" field in the QU3 query we want to choose from

The new query is now the following:

        <MG_QUERY id="QU2" name="Orders" descr="List of all the orders for one customer" query_type="SEL">
          <MG_PARAM_SOURCES>
            <MG_QUERY id="QU3" name="Customers" descr="Choose a customer to list orders for" query_type="SEL">
              <MG_TARGET id="QU3:T1" entity_ref="TVcustomers"/>
              <MG_TARGET id="QU3:T2" entity_ref="TVlocations"/>
              <MG_QF id="QU3:QF1" type="FIELD" name="Name" target="QU3:T1" object="TVcustomers:FIname"/>
              <MG_QF id="QU3:QF3" type="FIELD" name="Location" target="QU3:T2" object="TVlocations:FIshortcut"/>
              <MG_QF id="QU3:QF4" type="FIELD" name="Id" target="QU3:T1" object="TVcustomers:FIid" is_visible="t"/>
              <MG_JOIN target1="QU3:T1" target2="QU3:T2" join_type="LEFT"/>
            </MG_QUERY>
          </MG_PARAM_SOURCES>
          <MG_TARGET id="QU2:T1" entity_ref="TVorders"/>
          <MG_TARGET id="QU2:T2" entity_ref="TVcustomers"/>
          <MG_QF id="QU2:QF2" type="FIELD" name="Customer" target="QU2:T2" object="TVcustomers:FIname"/>
          <MG_QF id="QU2:QF1" type="FIELD" name="Creation Date" target="QU2:T1" object="TVorders:FIcreation_date"/>
          <MG_QF id="QU2:QF3" type="FIELD" name="Deliver before" target="QU2:T1" object="TVorders:FIdelivery_before"/>
          <MG_QF id="QU2:QF5" type="FIELD" name="Delivery date" target="QU2:T1" object="TVorders:FIdelivery_date"/>
          <MG_QF id="QU2:QF20" type="VAL" name="Customer Id Val" srv_type="DTint4" is_param="t" value_prov="QU3:QF4" is_visible="f"/>
          <MG_QF id="QU2:QF21" type="FIELD" name="Customer Id" target="QU2:T2" object="TVcustomers:FIid" is_visible="f"/>
          <MG_QF id="QU2:QF4" type="FIELD" name="Order Id" target="QU2:T1" object="TVorders:FIid" is_visible="f"/>
          <MG_JOIN target1="QU2:T1" target2="QU2:T2" join_type="INNER"/>
          <MG_COND id="QU2:C1" type="EQ" l_op="QU2:QF21" r_op="QU2:QF20"/>
        </MG_QUERY>

The SELECT query used to build a widget to modify the contents of an order is the following one:

    SELECT t2.ref, t2.name, t1.quantity, t1.discount, t1.order_id
    FROM order_contents AS t1 INNER JOIN products AS t2  ON (t1.product_ref=t2.ref)
    WHERE t1.order_id = <VALUE>;

which translates into:

        <MG_QUERY id="QU4" name="Orders contents" descr="List the contents of an order" query_type="SEL">
          <MG_TARGET id="QU4:T1" entity_ref="TVorder_contents"/>
          <MG_TARGET id="QU4:T2" entity_ref="TVproducts"/>
          <MG_QF id="QU4:QF1" type="FIELD" name="Item ref#" target="QU4:T2" object="TVproducts:FIref"/>
          <MG_QF id="QU4:QF2" type="FIELD" name="Item name" target="QU4:T2" object="TVproducts:FIname"/>
          <MG_QF id="QU4:QF3" type="FIELD" name="Quantity" target="QU4:T1" object="TVorder_contents:FIquantity"/>
          <MG_QF id="QU4:QF4" type="FIELD" name="Discount" target="QU4:T1" object="TVorder_contents:FIdiscount"/>
          <MG_QF id="QU4:QF20" type="VAL" name="Order Id Val" srv_type="DTint4" is_param="t" is_visible="f"/>
          <MG_QF id="QU4:QF21" type="FIELD" name="AAA" target="QU4:T1" object="TVorder_contents:FIorder_id" is_internal="t"/>
          <MG_JOIN target1="QU4:T1" target2="QU4:T2" join_type="INNER"/>
          <MG_COND id="QU4:C1" type="EQ" l_op="QU4:QF21" r_op="QU4:QF20"/>
        </MG_QUERY>

#### Code parts

Here is the part of the code using the libmergeant library. The complete code is available in the example/ directory. The main() function contains the following code, and basically loads the dictionnary XML file (the DICT_FILE macro) and opens the connection to the DBMS; the main window is built using the create_app1() (generated by Glade itself), and in the prepare_main_window() function.

    MgConf *dict;

    int
    main (int argc, char *argv[])
    {
    	GtkWidget *app1;
    	MgServer *server;
    	GError *error = NULL;

            (...)

    	/* loading XML file into dictionnary */
    	dict = MG_CONF (mg_conf_new ());
    	if (! mg_conf_load_xml_file (dict, DICT_FILE, &error)) {
    		gchar *str;

    		str = g_strdup_printf (_("Error loading file '%s':\n%s\n"), DICT_FILE,
    				       error->message);
    		display_error_and_quit (str);
    	}
    	server = mg_conf_get_server (dict);
    	if (! mg_server_open_connect (server, &error)) {
    		gchar *str;

    		str = g_strdup_printf (_("Can't open connection:\n%s\n"), error->message);
    		display_error_and_quit (str);
    	}

    	/* main window */
    	app1 = create_app1 ();
    	prepare_main_window (app1);
    	gtk_widget_show (app1);

            (...)
    }

The prepare_main_window() function replaces some widgets built by Glade (as there is not yet any support in Glade for the libmergeant's widgets, any volunteers?) by some widgets provided by libmergeant (this is why there are some gtk_table_attach() calls).

    static void
    prepare_main_window (GtkWidget *main_window)
    {
    	MgQuery *qu_orders, *qu_contents;
    	MgContext *context;
    	GtkWidget *form;
    	GtkWidget *table = lookup_widget (main_window, "table1");
    	GtkWidget *grid_orders, *grid_contents;
    	MgTarget *target;
    	MgParameter *param1, *param2;

    	/* Getting pointers to the right queries, see point (1) */
    	qu_orders = mg_conf_get_query_by_xml_id (dict, "QU2");
    	if (!qu_orders) {
    		gchar *str;

    		str = g_strdup_printf (_("Can't find query \"QU2\" in '%s' file\n"), DICT_FILE);
    		display_error_and_quit (str);
    	}

    	qu_contents = mg_conf_get_query_by_xml_id (dict, "QU4");
    	if (!qu_orders) {
    		gchar *str;

    		str = g_strdup_printf (_("Can't find query \"QU4\" in '%s' file\n"), DICT_FILE);
    		display_error_and_quit (str);
    	}

    	/* Form for the customer's choice, see point (2) */
    	context = mg_entity_get_exec_context (MG_ENTITY (qu_orders));
    	form = mg_form_new (dict, context);
    	mg_form_show_entries_actions (MG_FORM (form), FALSE);
    	mg_form_set_entries_auto_default (MG_FORM (form), TRUE);
    	gtk_table_attach (GTK_TABLE (table), form, 0, 1, 1, 2, GTK_FILL | GTK_EXPAND | GTK_SHRINK , 0, 0, 0);
    	gtk_widget_show (form);

    	/* grid for the orders, see point (3) */
    	target = mg_query_get_target_by_xml_id (qu_orders, "QU2:T1");
    	grid_orders = mg_work_grid_new (qu_orders,  target);
    	g_object_set (G_OBJECT (grid_orders), "title-visible", FALSE, NULL);
    	gtk_table_attach_defaults (GTK_TABLE (table), grid_orders, 0, 1, 3, 4);
    	gtk_widget_show (grid_orders);

    	mg_work_widget_bind_to_context (MG_WORK_WIDGET (grid_orders), "QU2:QF20", context, "QU2:QF20");
    	mg_work_widget_run (MG_WORK_WIDGET (grid_orders), MG_ACTION_ASK_CONFIRM_UPDATE | MG_ACTION_ASK_CONFIRM_DELETE |
    			    MG_ACTION_ASK_CONFIRM_INSERT);
    	g_object_unref (context);

    	/* grid for the order's contents, see point (4) */
    	target = mg_query_get_target_by_xml_id (qu_contents, "QU4:T1");
    	grid_contents = mg_work_grid_new (qu_contents,  target);
    	g_object_set (G_OBJECT (grid_contents), "title-visible", FALSE, NULL);
    	gtk_table_attach_defaults (GTK_TABLE (table), grid_contents, 0, 1, 5, 6);
    	gtk_widget_show (grid_contents);

    	mg_work_widget_bind_to_work_widget (MG_WORK_WIDGET (grid_contents), "QU4:QF20",
    					    MG_WORK_WIDGET (grid_orders), "QU2:QF4");
    	mg_work_widget_run (MG_WORK_WIDGET (grid_contents), MG_ACTION_ASK_CONFIRM_UPDATE | MG_ACTION_ASK_CONFIRM_DELETE |
    			    MG_ACTION_ASK_CONFIRM_INSERT);
    }

Here are a few explanations of the different portions of code:

1. In these lines, pointers to the MgQuery objects (which are part of the dictionnary) are obtained, and errors are returned if the queries are not described in the dictionnary. The queries are identified by the ID they have in the XML file from which the dictionnary has been loaded.

2. We ask for a new MgContext object to manage the parameter (there is only one: the customer Id but there could be several ones) required to execute the query to list the customer's orders, and from that context we create a MgForm widget where the user will fill the parameter.

3. We first get the MgTarget representing the table we want to modify through the MgWorkGrid which we then create. The mg_work_widget_bind_to_context() function makes sure any change in the customer's ID parameter will make the grid widget update itself taking into account the new customer ID; and the mg_work_widget_run() actually makes the grid widget display some data (taking the currently selected customer ID as parameter).

4. The same processing is applied for the order's contents, but with the query to list the order's contents, from a chosen order, except that the order is selected from the previously created grid: this is done using the mg_work_widget_bind_to_work_widget() function.

#### Compiling

To compile an application using libmergeant, proceed as with other Gnome libraries, using the pkg-config program and get the CFLAGS and LDFLAGS for the 'libmergeant' library.

For applications using the auto* packages (as for this example), simply add the libmergeant library in the configure.in file along with the other required libraries for the package.