Up to now, we've discussed specific techniques related to InterBase programming, but we haven't delved into the development of an application and the problems this presents in practice. In the following subsections, I'll discuss a few practical techniques, in no specific order.
Nando Dessena (who knows InterBase much better than I do) and I have used all of these techniques in a seminar discussing the porting of an internal Paradox application to InterBase. The application we discussed in the seminar was large and complex, and I've trimmed it down to only a few tables to make it fit into the space I have for this chapter.
The database discussed in this section is called mastering.gdb. You can find it in the data subfolder of the code folder for this chapter. You can examine it using InterBase Console, possibly after making a copy to a writable drive so that you can fully interact with it.
I mentioned in Chapter 13 that I'm a fan of using IDs extensively to identify the records in each table of a database.
I tend to use a single sequence of IDs for an entire system, something often called an Object ID (OID) and discussed in a sidebar earlier in this chapter. In such a case, however, the IDs of the two tables must be unique. Because you might not know in advance which objects could be used in place of others, adopting a global OID allows you more freedom later. The drawback is that if you have lots of data, using a 32-bit integer as the ID (that is, having only 4 billion objects) might not be sufficient. For this reason, InterBase 6 supports 64-bit generators.
How do you generate the unique values for these IDs when multiple clients are running? Keeping a table with a latest value will create troubles, because multiple concurrent transactions (from different users) will see the same values. If you don't use tables, you can use a database-independent mechanism, including the rather large Windows GUIDs or the so-called high-low technique (the assignment of a base number to each client at startup—the high number—that is combined with a consecutive number—the low number—determined by the client).
Another approach, bound to the database, is the use of internal mechanisms for sequences, indicated with different names in each SQL server. In InterBase they are called generators. These sequences operate and are incremented outside of transactions, so that they provide unique numbers even to concurrent users (remember that InterBase forces you to open a transaction to read data).
You've already seen how to create a generator. Here is the definition for the one in my demo database, followed by the definition of the view you can use to query for a new value:
create generator g_master; create view v_next_id ( next_id ) as select gen_id(g_master, 1) from rdb$database ;
Inside the RWBlocks application, I've added an IBQuery component to a data module (because I don't need it to be an editable dataset) with the following SQL:
select next_id from v_next_id;
The advantage, compared to using the direct statement, is that this code is easier to write and maintain, even if the underlying generator changes (or you switch to a different approach behind the scenes). Moreover, in the same data module, I've added a function that returns a new value for the generator:
function TDmMain.GetNewId: Integer; begin // return the next value of the generator QueryId.Open; try Result := QueryId.Fields.AsInteger; finally QueryId.Close; end; end;
This method can be called in the AfterInsert event of any dataset to fill in the value for the ID:
mydataset.FieldByName ('ID').AsInteger := data.GetNewId;
As I've mentioned, the IBX datasets can be tied directly to a generator, thus simplifying the overall picture. Thanks to the specific property editor (shown in Figure 14.17), connecting a field of the dataset to the generator becomes trivial.
Notice that both these approaches are much better than the approach based on a server-side trigger, discussed earlier in this chapter. In that case, the Delphi application didn't know the ID of the record sent to the database and so was unable to refresh it. Not having the record ID (which is also the only key field) on the Delphi side means it is almost impossible to insert such a value directly inside a DBGrid. If you try, you'll see that the value you insert gets lost, only to reappear in case of a full refresh.
Using client-side techniques based on the manual code or the GeneratorField property causes no trouble. The Delphi application knows the ID (the record key) before posting it, so it can easily place it in a grid and refresh it properly.
An interesting issue with SQL servers in general, not specifically InterBase, has to do with case-insensitive searches. Suppose you don't want to show a large amount of data in a grid (which is a bad idea for a client/server application). You instead choose to let the user type the initial portion of a name and then filter a query on this input, displaying only the smaller resulting record set in a grid. I've done this for a table of companies.
This search by company name will be executed frequently and will take place on a large table. However, if you search using the starting with or like operator, the search will be case sensitive, as in the following SQL statement:
select * from companies where name starting with 'win';
To make a case-insensitive search, you can use the upper function on both sides of the comparison to test the uppercase values of each string, but a similar query will be very slow, because it won't be based on an index. On the other hand, saving the company names (or any other name) in uppercase letters would be silly, because when you print those names, the result will be unnatural (even if common in old information systems).
If you can trade off some disk space and memory for the extra speed, you can use a trick: Add an extra field to the table to store the uppercase value of the company name, and use a server-side trigger to generate it and update it. You can then ask the database to maintain an index on the uppercase version of the name, to speed the search operation even further.
In practice, the table definition looks like this:
create domain d_uid as integer; create table companies ( id d_uid not null, name varchar(50), tax_code varchar(16), name_upper varchar(50), constraint companies_pk primary key (id) );
To copy the uppercase name of each company into the related field, you cannot rely on client-side code, because an inconsistency would cause problems. In a case like this, it is better to use a trigger on the server, so that each time the company name changes, its uppercase version is updated accordingly. Another trigger is used to insert a new company:
create trigger companies_bi for companies active before insert position 0 as begin new.name_upper = upper(new.name); end; create trigger companies_bu for companies active before update position 0 as begin if (new.name <> old.name) then new.name_upper = upper(new.name); end;
Finally, I've added an index to the table with this DDL statement:
create index i_companies_name_upper on companies(name_upper);
With this structure behind the scenes, you can now select all the companies starting with the text of an edit box (edSearch) by writing the following code in a Delphi application:
dm.DataCompanies.Close; dm.DataCompanies.SelectSQL.Text := 'select c.id, c.name, c.tax_code,' + ' from companies c ' + ' where name_upper starting with ''' + UpperCase (edSearch.Text) + ''''; dm.DataCompanies.Open;
Using a prepared parametric query, you might be able to make this code even faster.
As an alternative, you could create a server-side calculated field in the table definition, but doing so would prevent you from having an index on the field, which speeds up your queries considerably:
name_upper varchar(50) computed by (upper(name))
You might notice that the table describing companies is quite bare. It has no company address, nor any contact information. The reason is that I want to be able to handle companies that have multiple offices (or locations) and list contact information about multiple employees of those companies.
Every location is bound to a company. Notice, though, that I've decided not to use a location identifier related to the company (such as a progressive location number for each company), but rather a global ID for all the locations. This way, I can refer to a location ID (let's say, for shipping goods) without having to also refer to the company ID. This is the definition of the table that stores company locations:
create table locations ( id d_uid not null, id_company d_uid not null, address varchar(40), town varchar(30), zip varchar(10), state varchar(4), phone varchar(15), fax varchar(15), constraint locations_pk primary key (id), constraint locations_uc unique (id_company, id) ); alter table locations add constraint locations_fk_companies foreign key (id_company) references companies (id) on update no action on delete no action;
The final definition of a foreign key relates the id_company field of the locations table with the ID field of the companies table. The other table lists names and contact information for people at specific company locations. To follow the database normalization rules, I should have added to this table only a reference to the location, because each location relates to a company. However, to make it simpler to change the location of a person within a company and to make my queries much more efficient (avoiding an extra step), I've added to the people table both a reference to the location and a reference to the company.
The table has another unusual feature: One of the people working for a company can be set as the key contact. You obtain this functionality with a Boolean field (defined with a domain, because the Boolean type is not supported by InterBase) and by adding triggers to the table so that only one employee of each company can have this flag active:
create domain d_boolean as char(1) default 'F' check (value in ('T', 'F')) not null create table people ( id d_uid not null, id_company d_uid not null, id_location d_uid not null, name varchar(50) not null, phone varchar(15), fax varchar(15), email varchar(50), key_contact d_boolean, constraint people_pk primary key (id), constraint people_uc unique (id_company, name) ); alter table people add constraint people_fk_companies foreign key (id_company) references companies (id) on update no action on delete cascade; alter table people add constraint people_fk_locations foreign key (id_company, id_location) references locations (id_company, id); create trigger people_ai for people active after insert position 0 as begin /* if a person is the key contact, remove the flag from all others (of the same company) */ if (new.key_contact = 'T') then update people set key_contact = 'F' where id_company = new.id_company and id <> new.id; end; create trigger people_au for people active after update position 0 as begin /* if a person is the key contact, remove the flag from all others (of the same company) */ if (new.key_contact = 'T' and old.key_contact = 'F') then update people set key_contact = 'F' where id_company = new.id_company and id <> new.id; end;
The three tables discussed so far have a clear master/detail relation. For this reason, the RWBlocks example uses three IBDataSet components to access the data, hooking up the two secondary tables to the main one. The code for the master/detail support is that of a standard database example based on queries, so I won't discuss it further (but I suggest you study the example's source code).
Each of the datasets has a full set of SQL statements, to make the data editable. Whenever you enter a new detail element, the program hooks it to its master tables, as in the two following methods:
procedure TDmCompanies.DataLocationsAfterInsert(DataSet: TDataSet); begin // initialize the data of the detail record // with a reference to the master record DataLocationsID_COMPANY.AsInteger := DataCompaniesID.AsInteger; end; procedure TDmCompanies.DataPeopleAfterInsert(DataSet: TDataSet); begin // initialize the data of the detail record // with a reference to the master record DataPeopleID_COMPANY.AsInteger := DataCompaniesID.AsInteger; // the suggested location is the active one, if available if not DataLocations.IsEmpty then DataPeopleID_LOCATION.AsInteger := DataLocationsID.AsInteger; // the first person added becomes the key contact // (checks whether the filtered dataset of people is empty) DataPeopleKEY_CONTACT.AsBoolean := DataPeople.IsEmpty; end;
As this code suggests, a data module hosts the dataset components. The program has a data module for every form (hooked up dynamically, because you can create multiple instances of each form). Each data module has a separate transaction so that the various operations performed in different pages are totally independent. The database connection, however, is centralized. A main data module hosts the corresponding component, which is referenced by all the datasets. Each of the data modules is created dynamically by the form referring to it, and its value is stored in the form's dm private field:
procedure TFormCompanies.FormCreate(Sender: TObject); begin dm := TDmCompanies.Create (Self); dsCompanies.Dataset := dm.DataCompanies; dsLocations.Dataset := dm.DataLocations; dsPeople.Dataset := dm.DataPeople; end;
This way, you can easily create multiple instances of a form, with an instance of the data module connected to each of them. The form connected to the data module has three DBGrid controls, each tied to a data module and one of the corresponding datasets. You can see this form at run time, with some data, in Figure 14.18.
The form is hosted by a main form, which in turn is based on a page control, with the other forms embedded. Only the form connected with the first page is created when the program starts. The ShowForm method I've written takes care of parenting the form to the tab sheet of the page control, after removing the form border:
procedure TFormMain.FormCreate(Sender: TObject); begin ShortDateFormat := 'dd/mm/yyyy'; ShowForm (TFormCompanies.Create (Self), TabCompanies); end; procedure TFormMain.ShowForm (Form: TForm; Tab: TTabSheet); begin Form.BorderStyle := bsNone; Form.Align := alClient; Form.Parent := Tab; Form.Show; end;
The other two pages are populated at run time:
procedure TFormMain.PageControl1Change(Sender: TObject); begin if PageControl1.ActivePage.ControlCount = 0 then if PageControl1.ActivePage = TabFreeQ then ShowForm (TFormFreeQuery.Create (self), TabFreeQ) else if PageControl1.ActivePage = TabClasses then ShowForm (TFormClasses.Create (self), TabClasses); end;
The companies form hosts the search by company name (discussed in the previous section) plus a search by location. You enter the name of a town and get back a list of companies having an office in that town:
procedure TFormCompanies.btnTownClick(Sender: TObject); begin with dm.DataCompanies do begin Close; SelectSQL.Text := 'select c.id, c.name, c.tax_code' + ' from companies c ' + ' where exists (select loc.id from locations loc ' + ' where loc.id_company = c.id and upper(loc.town) = ''' + UpperCase(edTown.Text) + ''' )'; Open; dm.DataLocations.Open; dm.DataPeople.Open; end; end;
The form includes a lot more source code. Some of it is related to closing permission (as a user cannot close the form while there are pending edits not posted to the database), and quite a bit relates to the use of the form as a lookup dialog, as described later.
Part of the program and the database involves booking training classes and courses. (Although I built this program as a showcase, it also helps me run my own business.) The database includes a classes table that lists all the training courses, each with a title and the planned date. Another table hosts registration by company, including the classes registered for, the ID of the company, and some notes. Finally, a third table lists people who've signed up, each hooked to a registration for his or her company, with the amount paid.
The rationale behind this company-based registration is that invoices are sent to companies, which book the classes for programmers and can receive specific discounts. In this case the database is more normalized, because the people registration doesn't refer directly to a class, but only to the company registration for that class. Here are the definitions of the tables involved (I've omitted foreign key constraints and other elements):
create table classes ( id d_uid not null, description varchar(50), starts_on timestamp not null, constraint classes_pk primary key (id) ); create table classes_reg ( id d_uid not null, id_company d_uid not null, id_class d_uid not null, notes varchar(255), constraint classes_reg_pk primary key (id), constraint classes_reg_uc unique (id_company, id_class) ); create domain d_amount as numeric(15, 2); create table people_reg ( id d_uid not null, id_classes_reg d_uid not null, id_person d_uid not null, amount d_amount, constraint people_reg_pk primary key (id) );
The data module for this group of tables uses a master/detail/detail relationship, and has code to set the connection with the active master record when a new detail record is created. Each dataset has a generator field for its ID, and each has the proper update and insert SQL statements. These statements are generated by the corresponding component editor using only the ID field to identify existing records and updating only the fields in the original table. Each of the two secondary datasets retrieves data from a lookup table (either the list of companies or the list of people). I had to edit the RefreshSQL statements manually to repeat the proper inner join. Here is an example:
object IBClassReg: TIBDataSet Database = DmMain.IBDatabase1 Transaction = IBTransaction1 AfterInsert = IBClassRegAfterInsert DeleteSQL.Strings = ( 'delete from classes_reg' 'where id = :old_id') InsertSQL.Strings = ( 'insert into classes_reg (id, id_class, id_company, notes)' 'values (:id, :id_class, :id_company, :notes)') RefreshSQL.Strings = ( 'select reg.id, reg.id_class, reg.id_company, reg.notes, c.name ' 'from classes_reg reg' 'join companies c on reg.id_company = c.id' 'where id = :id') SelectSQL.Strings = ( 'select reg.id, reg.id_class, reg.id_company, reg.notes, c.name ' 'from classes_reg reg' 'join companies c on reg.id_company = c.id' 'where id_class = :id') ModifySQL.Strings = ( 'update classes_reg' 'set' ' id = :id,' ' id_class = :id_class,' ' id_company = :id_company,' ' notes = :notes' 'where id = :old_id') GeneratorField.Field = 'id' GeneratorField.Generator = 'g_master' DataSource = dsClasses end
To complete the discussion of IBClassReg, here is its only event handler:
procedure TDmClasses.IBClassRegAfterInsert(DataSet: TDataSet); begin IBClassReg.FieldByName ('id_class').AsString := IBClasses.FieldByName ('id').AsString; end;
The IBPeopleReg dataset has similar settings, but the IBClasses dataset is simpler at design time. At run time, this dataset's SQL code is dynamically modified, using three alternatives to display scheduled classes (whenever the date is after today's date), classes already started or finished in the current year, and classes from past years. A user selects one of the three groups of records for the table with a tab control, which hosts the DBGrid for the main table (see Figure 14.19).
The three alternative SQL statements are created when the program starts, or when the class registrations form is created and displayed. The program stores the final portion of the three alternative instructions (the where clause) in a string list and selects one of the strings when the tab changes:
procedure TFormClasses.FormCreate(Sender: TObject); begin dm := TDmClasses.Create (Self); // connect the datasets to the data sources dsClasses.Dataset := dm.IBClasses; dsClassReg.DataSet := dm.IBClassReg; dsPeopleReg.DataSet := dm.IBPeopleReg; // open the datasets dm.IBClasses.Active := True; dm.IBClassReg.Active := True; dm.IBPeopleReg.Active := True; // prepare the SQL for the three tabs SqlCommands := TStringList.Create; SqlCommands.Add (' where Starts_On > ''now'''); SqlCommands.Add (' where Starts_On <= ''now'' and ' + ' extract (year from Starts_On ) >= extract(year from current_timestamp)'); SqlCommands.Add (' where extract (year from Starts_On) < ' + ' extract(year from current_timestamp)'); end; procedure TFormClasses.TabChange(Sender: TObject); begin dm.IBClasses.Active := False; dm.IBClasses.SelectSQL  := SqlCommands [Tab.TabIndex]; dm.IBClasses.Active := True; end;
The two detail datasets of this class registration form display lookup fields. Instead of showing the ID of the company that booked the class, for example, the form shows the company name. You obtain this functionality with an inner join in the SQL statement and by configuring the DBGrid columns so they don't display the company ID. In a local application, or one with a limited amount of data, you could use a lookup field. However, copying the entire lookup dataset locally or opening it for browsing should be limited to tables with about 100 records at most, embedding some search capabilities.
If you have a large table, such as a table of companies, an alternative solution is to use a secondary dialog box to perform the lookup selection. For example, you can choose a company by using the form you've already built and taking advantage of its search capabilities. To display this form as a dialog box, the program creates a new instance of it, shows some hidden buttons already there at design time, and lets the user select a company to refer to from the other table.
To simplify the use of this lookup, which can happen multiple times in a large program, I've added to the companies form a class function that has as output parameters the name and ID of the selected company. An initial ID can be passed to the function to determine its initial selection. Here is the complete code of this class function, which creates an object of its class, selects the initial record if requested, shows the dialog box, and finally extracts the return values:
class function TFormCompanies.SelectCompany ( var CompanyName: string; var CompanyId: Integer): Boolean; var FormComp: TFormCompanies; begin Result := False; FormComp := TFormCompanies.Create (Application); FormComp.Caption := 'Select Company'; try // activate dialog buttons FormComp.btnCancel.Visible := True; FormComp.btnOK.Visible := True; // select company if CompanyId > 0 then FormComp.dm.DataCompanies.SelectSQL.Text := 'select c.id, c.name, c.tax_code' + ' from companies c ' + ' where c.id = ' + IntToStr (CompanyId) else FormComp.dm.DataCompanies.SelectSQL.Text := 'select c.id, c.name, c.tax_code' + ' from companies c ' + ' where name_upper starting with ''a'''; FormComp.dm.DataCompanies.Open; FormComp.dm.DataLocations.Open; FormComp.dm.DataPeople.Open; if FormComp.ShowModal = mrOK then begin Result := True; CompanyId := FormComp.dm.DataCompanies.FieldByName ('id').AsInteger; CompanyName := FormComp.dm.DataCompanies.FieldByName ('name').AsString; end; finally FormComp.Free; end; end;
Another slightly more complex class function (available with the example's source code, but not listed here) lets you select a person from a given company to register people for classes. In this case, the form is displayed after disallowing searching another company or modifying the company's data.
In both cases, you trigger the lookup by adding an ellipsis button to the column of the DBGrid—for example, the grid column listing the names of companies registered for classes. When this button is clicked, the program calls the class function to display the dialog box and uses its result to update the hidden ID field and the visible name field:
procedure TFormClasses.DBGridClassRegEditButtonClick(Sender: TObject); var CompanyName: string; CompanyId: Integer; begin CompanyId := dm.IBClassReg.FieldByName ('id_Company').AsInteger; if TFormCompanies.SelectCompany (CompanyName, CompanyId) then begin dm.IBClassReg.Edit; dm.IBClassReg.FieldByName ('Name').AsString := CompanyName; dm.IBClassReg.FieldByName ('id_Company').AsInteger := CompanyId; end; end;
The program's final feature is a form where a user can directly type in and run a SQL statement. As a helper, the form lists in a combo box the available tables of the database, obtained when the form is created by calling
Selecting an item from the combo box generates a generic SQL query:
MemoSql.Lines.Text := 'select * from ' + ComboTables.Text;
The user (if an expert) can then edit the SQL, possibly introducing restrictive clauses, and then run the query:
procedure TFormFreeQuery.ButtonRunClick(Sender: TObject); begin QueryFree.Close; QueryFree.SQL := MemoSql.Lines; QueryFree.Open; end;
You can see this third form of the RWBlocks program in Figure 14.20. Of course, I'm not suggesting that you add SQL editing to programs intended for all your users—this feature is intended for power users or programmers. I basically wrote it for myself!