dbutil - Example by Todd Shadburn ---------------------------------------------------------- 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 --------------------------------------------- A minimal call-tracking application first needs to track callers, so our first table will contain caller information; name, phone#, fax# and email address. So this table could possibly be structured as follows(in SQL): 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 ---------------------------------------------- Now that we have the database structure in place, we can begin writing the code to duplicate this structure with the API functions. First we will create the caller table and add the needed fields: tcaller = dbutil_table_new("caller"); /* callerid is the primary field and type INTEGER */ fcallerid = dbutil_field_new(NULL, "callerid"); dbutil_field_set_flags(fcallerid, DBF_PRIMARYKEY|DBF_INT); dbutil_table_add_field(tcaller, fcallerid); /* callername is type CHAR and SELECTs should use LIKE */ fcallername = dbutil_field_new(NULL, "callername"); dbutil_field_set_flags(fcallername, DBF_CHAR|DBF_SQLUSELIKE); dbutil_table_add_field(tcaller, fcallername); /* callerphone is type CHAR */ fcallerphone = dbutil_field_new(NULL, "callerphone"); dbutil_field_set_flags(fcallerphone, DBF_CHAR); dbutil_table_add_field(tcaller, fcallerphone); /* callerfax is type CHAR */ fcallerfax = dbutil_field_new(NULL, "callerfax"); dbutil_field_set_flags(fcallerfax, DBF_CHAR); dbutil_table_add_field(tcaller, fcallerfax); /* 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 ---------------------- This set of functions integrates calls to functions within the GTK+ widget set. As GTK+ widgets(GtkEntry, GtkOptionMenu, etc) are created, the widget should be passed as the first argument to the dbutil_field_new() function calls. Before calling one of the dbutil_relationset_select/insert/update/delete functions, make a call to dbutil_gtk_table_sync_with_gui() to syncronize one dbtable with the data in the GTK+ widgets. Making a call to dbutil_gtk_relationset_sync_with_gui() will syncronize all the tables, which are contained in the relationset's tableset, with the data in the associated GTK+ widgets. 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.