libdbutil - Step-by-Step Example

by Todd Shadburn <tshadb@attglobal.net>

The idea for the dbutil library originally came out of the need to generate
SQL statements from the GUI of an application I was working on. The
application uses a relational database backend, and the SQL that needed to
be generated made heavy use of table joins. The GUI had many windows with
lots of entry fields that had to be filled from the database. Hand coding
the SQL for each of the operations that could be performed from the UI would
have been a nightmare...not to mention being able to make changes to the UI
later, if the need arose. So, I started to design a small library of functions
which could generate SQL based on a hierarchy of structures resembling the
relational model.
The library had to provide support for the different statement types; select,
insert, update and delete and support them with many joins within the
statement.

Consider the following example...

We will attempt to design a minimal call-tracking application. The purpose of
this application will be to track incoming calls. It should collect caller
information and information about the purpose of the call. The application
should be able to track the call from beginning through to completion. We will
first develop a set of database tables and a relational model, then we will
code the API function calls needed to duplicate this model within the dbutil
library.
 

Database Tables, SQL and the Relational Model

---

create table caller (
callerid integer not null,
callername char(30) not null,
callerphone char(14) not null,
callerfax char(14),
calleremail char(30)
);

The callername, callerphone, callefax and calleremail fields are self
explanatory and the callerid field is an integer type field which will be
used to provide uniqueness. The callerid field will also be used to join the
tables when we define the relationships required. The 'not null' declarations
basically make those fields required, a value must be specified for the fields
during an insert.
Next the application needs to track the call subject information. This
includes callid, date/time call was taken, the priority of the call(low,
medium,high,etc.), who took the call and some area for the call taker to
enter some text pertaining to the call. Also, it would be nice to be able to
change the status of the call. Once the call has been completed, there needs
to be a way to differentiate this call from a call that is still pending. A
field containing the call 'status' is required, which will hold one of the
following values; open, pending, closed. Lastly a field containing the
date and time that the call was closed, or completed, is required. Following
is one way to create a database table to hold the information required(in SQL):

create table call (
callid integer not null,
callcaller integer not null,
callagent char(20) not null,
callpriority integer not null,
callstatus integer not null,
calltaken datetime,
callresolved datetime,
calltext char(255)
);

The callid field will be used to uniquely identify one call from another. The
calltaken and callresolved fields hold the date and time when the call was
created and resolved respectively. The calltext is a character field to hold
misc. text entered by the person taking the call. The callpriority field is an
integer type which will hold the offset into a list of values for the priority
for the call. This field will be used to populate an optionmenu widget, later
in the example. The callstatus field will hold the status for the call, in the
same manner as the callpriority field.
The callcaller field requires some explanation. This field will be used to
hold a reference to the caller record associated with this call. The field is
a 'foreign key', a field whos value references a field in another(foreign)
table. When a call record is inserted into the call table, the value of the
callerid field in the caller table will be inserted into this field. This way
whenever a call record is retrieved, this field provides an easy way to get
the caller record. When a call record is selected to be displayed, a second
SQL statement will be sent to the database to select the associated caller
record. This statement will contain a table join between the call and caller
tables where the callcaller field is equal to the callerid field. This join
implements the 'relationship' between the call and caller tables. For example,
if the call record contains the following values;

callid = 1
callcaller = 2
callagent = 'agent1'
callpriority = 1
callstatus = 1
calltaken = '2001-04-15 20:42:00'
callresolved = NULL
calltext = 'User is unable to logon to workstation.'

Then the SQL statement needed to retrieve the correct caller record is;

select callerid, callername, callerphone, callerfax, calleremail
from caller, call
where callerid = callcaller and callcaller = 2;

This statement will select all fields from the caller record where the
callerid is the same as the value in the call table's callcaller field. This
relation can also be used in the reverse fasion, to find all the call records
associated with a given callerid. This statement looks like;

select callid, callcaller, callagent, callpriority, callstatus,
calltaken, callresolved, calltext
from call, caller
where callerid = callcaller and callerid = 2;

In the above example SQL statements, the joins are accompanied by a value for
the foreign key(callcaller) and the primary key(callerid) of the respective
tables. These field values each come from the table record that will have
already been retrieved, when the initial database table was queried.
 

Mapping the Relational Model to the dbutil API

---

/* calleremail is type CHAR */
fcalleremail = dbutil_field_new(NULL, "calleremail");
dbutil_field_set_flags(fcalleremail, DBF_CHAR);
dbutil_table_add_field(tcaller, fcalleremail);

Then we will create the call table and add the needed fields:

tcall = dbutil_table_new("call");

/* callid is the primary field and type INTEGER */
fcallid = dbutil_field_new(NULL, "callid");
dbutil_field_set_flags(fcallid, DBF_PRIMARYKEY|DBF_INT);
dbutil_table_add_field(tcall, fcallid);

/* callcaller is a foreign key field and type INTEGER */
fcallcaller = dbutil_field_new(NULL, "callcaller");
dbutil_field_set_flags(fcallcaller, DBF_INT);
dbutil_table_add_field(tcall, fcallcaller);

/* callagent is type CHAR */
fcallagent = dbutil_field_new(NULL, "callagent");
dbutil_field_set_flags(fcallagent, DBF_CHAR);
dbutil_table_add_field(tcall, fcallagent);

/* callpriority is type INTEGER */
fcallpriority = dbutil_field_new(NULL, "callpriority");
dbutil_field_set_flags(fcallpriority, DBF_INT);
dbutil_table_add_field(tcall, fcallpriority);

/* callstatus is type INTEGER */
fcallstatus = dbutil_field_new(NULL, "callstatus");
dbutil_field_set_flags(fcallstatus, DBF_INT);
dbutil_table_add_field(tcall, fcallstatus);

/* calltaken is type CHAR */
fcalltaken = dbutil_field_new(NULL, "calltaken");
dbutil_field_set_flags(fcalltaken, DBF_CHAR);
dbutil_table_add_field(tcall, fcalltaken);

/* callresolved is type CHAR */
fcallresolved = dbutil_field_new(NULL, "callresolved");
dbutil_field_set_flags(fcallresolved, DBF_CHAR);
dbutil_table_add_field(tcall, fcallresolved);

/* calltext is type CHAR */
fcalltext = dbutil_field_new(NULL, "calltext");
dbutil_field_set_flags(fcalltext, DBF_CHAR);
dbutil_table_add_field(tcall, fcalltext);

Next we create a tableset and add the two tables:

ts = dbutil_tableset_new("tscallentry");
dbutil_tableset_add_table(ts, tcaller);
dbutil_tableset_add_table(ts, tcall);

Now we need to create the relation between the two tables. The primary field
argument refers to the field in the primary table(caller) for the relation.
The foreign field argument refers to the field in the foreign table(call).

rcallcaller = dbutil_relation_new("callcaller", fcallerid, fcallcaller);

Finally, we must create a relationset using the tableset created above and
add the relation:

rscallentry = dbutil_relationset_new("rscallentry", ts);
dbutil_relationset_add_relation(rscallentry, rcallcaller);

The relationset can now be used to generate SQL statements to be passed to the
database. The statements will be generated from the data set in the various
fields. For example, to generate an INSERT statement from data in the caller
table:

sqlstr = dbutil_relationset_insert(rscallentry, "caller");
printf("The SQL is '%s'\n", sqlstr->str);

The sqlstr->str string should be passed to the prepare or execute function of
the database system you are using.
 

dbutil_gtk_* functions

---

Currently the GTK+ widgets which are supported are those derived from;
GtkEntry, GtkOptionMenu and GtkToggleButton.

See tests/gtkexample.c for an example of using the dbutil API with the GTK+
widgets.