Understanding ClientDataSet Indexes

Abstract: A ClientDataSet does not obtain its indexes from the data it loads. Indexes, if you want them, must be explicitly defined. This article shows you how to do this at design-time or runtime.

In many respects, an index on a ClientDataSet is like that on any other TDataSet descendant. Specifically, an index controls the order of records in the DataSet, as well as enables or enhances a variety of other operations, such as searches, ranges, and dataset linking.

In earlier articles in this series I described how the structure of a ClientDataSet is defined. There you learned that, if a ClientDataSet is loaded through a DataSetProvider, the structure is based on the columns that the DataSetProvider obtains from its DataSet. When a DataSetProvider is not involved, the structure is either based on metadata loaded from a file previously saved by a ClientDataSet, or is defined by the ClientDataSet's FieldDefs property or by TFields associated with the ClientDataSet.

Unlike a ClientDataSet's structure, which is normally obtained from existing data, a ClientDataSet's indexes are not. Specifically, when a ClientDataSet is loaded with data obtained from a DataSetProvider, or is loaded from a previously saved ClientDataSet file, the ClientDataSet's structure is largely (and usually entirely) defined by the DataSetProvider, or loaded from the saved file. Indexes, with the exception of two default indexes, are solely the responsibility of the ClientDataSet itself. In other words, even if the DataSet from which a DataSetProvider obtains its data possesses indexes, those are unrelated to any indexes on the ClientDataSet loaded from that DataSetProvider.

Consider the CUSTOMER table found in the example EMPLOYEE.GDB InterBase database that ships with Delphi. There are four customer table-related indexes present in the database, including indexes based on the CUST_NO, COMPANY, and COUNTRY fields. Regardless of how you load the data from that table into a ClientDataSet, those indexes will be all but ignored by the DataSetProvider, and will be absent in the ClientDataSet. With the exception of the two default indexes that a ClientDataSet creates for its own use, if you want additional indexes in a ClientDataSet, you must define them explicitly.

In general, the indexes of a ClientDataSet can be divided into three categories: default indexes, temporary indexes, and persistent indexes. Each of these indexes is discussed in the following sections.

Default Indexes

Most ClientDataSets have two default indexes, as shown in the following image of the Object Inspector. One of these is named DEFAULT_ORDER, and the other is named CHANGEINDEX. DEFAULT_ORDER represents the original order that the records where loaded into the ClientDataSet. If the ClientDataSet is loaded through a DataSetProvider, this order matches that of the DataSet from which the DataSetProvider obtains its data. For example, if the DataSetProvider points to a SQLDataSet that includes a SQL query with an ORDER BY clause, DEFAULT_ORDER will order the records in the same order as that defined by the ORDER BY clause. If the DataSetProvider doesn't specify an order, the default order will match the natural order of the records in the corresponding DataSet.

While DEFAULT_ORDER is associated with the records held in the Data property of the ClientDataSet, CHANGEINDEX is associated with the order of records held in the Delta property, also known as the change log. This index is maintained as changes are posted to a ClientDataSet, and it controls the order in which the changed records will be processed by the DataSetProvider when ApplyUpdates is called.

These default indexes have limited utility in most database applications. For example, DEFAULT_ORDER can be used to return data held in a ClientDataSet to the originally loaded order after having switched to some other index. In most cases, however, a ClientDataSet's natural order is of little interest. Most developers want to based indexes on specific fields, depending on the needs of the application.

CHANGEINDEX, by comparison, can be used to display only those records that appear in the change log, and in the order in which those changes will be applied if ApplyUpdates is called. Again, this order might be interesting, most developers are not concerned with the order in which changes are applied. One reason is that there is another mechanism that a ClientDataSet provides for this purpose: the StatusFilter property. StatusFilter permits you to display specific kinds of changes contained in the change log. These changes can be displayed using any ClientDataSet index, not just the order in which the changes where applied. CHANGEINDEX is really only useful when the order that the records where placed in the change log is of interest.

Creating Indexes

There are two types of indexes that you explicitly create: temporary indexes and persistent indexes. Each of these index types play an important role in applications, permitting you to control the order that records appear in the ClientDataSet, as well as to enable index-based operations, including searches, ranges, and dataset linking. Each of these index types is discussed in the following sections.

Temporary Indexes

Temporary indexes are created with the IndexFieldNames property. To create a temporary index, set the IndexFieldNames property to the name of the field or fields you want to base the index on. When you need a multi-field index, separate the field names with semicolons. For example, imagine that you have a ClientDataSet that contains customer records, including account number, first name, last name, city, state, and so on. If you want to sort this data by last name and first name (and assuming that these fields are named FirstName and LastName, respectively), you can create a temporary index by setting the client dataset's IndexFieldNames property to the following string:

LastName;FirstName

As with all published properties, this can be done at design time, or it can be done in code at runtime using a statement similar to the following:

ClientDataSet1.IndexFieldNames := 'LastName;FirstName';

When you assign a value to the ClientDataSet's IndexFieldNames property, the ClientDataSet immediately generates the index. If the contents of the ClientDataSet are being displayed, those records will appear sorted in ascending order by the fields of the index, with the first field in the index sorted first, followed the second (if present), and so on.

Indexes create this way are temporary in that when you change the value of the IndexFieldNames property, the previous index is discarded, and a new one is created. For example, imagine that if after you created the last name/first name index, you then execute the following statement:

ClientDataSet1.IndexFieldNames := 'FirstName'

This statement will cause the existing temporary index to be discarded and a new index to be generated. If the new index defines a sort order different from the previous index, the record display order is also updated. If you later set the IndexFieldNames property back to 'LastName;FirstName', the first name index will be discarded, and a new last name/first name index will be created.

Temporary indexes are extremely useful under a number of situations, such as when you want to permit your users to sort the data based on any field or field combination. There are, however, some drawbacks to temporary indexes. One of these is that indexes take some time to build, and temporary indexes must be re-built more often than persistent indexes. The time it takes a ClientDataSet to build an index is based on the number of records being indexed, the field types being indexes, and number of fields in the index. Since these indexes are built in memory, even a complicated temporary index can be built in a fraction of a second, so long as there are less than 10,000 records or so in the ClientDataSet. Even with more than 100,000, most indexes can be built in less than 10 seconds on a typical workstation.

A more important concern when deciding between temporary and persistent indexes involves index features. Specifically, you can only build ascending temporary indexes. In addition, temporary indexes do not support more advanced index options, such as unique indexes. If you need a more complicated index, you will need to create persistent indexes.

Persistent Indexes

Persistent indexes are index definitions that can be used to build indexes at runtime. Once a persistent index has been built, it remains available to the ClientDataSet so long as the ClientDataSet remains open. For example, if there is a persistent index based on a field named FirstName, setting the ClientDataSet to use this index causes the index to be built. If you then set the ClientDataSet to use another persistent index based on the last name/first name field combination, that index is built, but the first name-based index is not discarded. If you then set the ClientDataSet to use the first name index once again, it immediately switches to that previously created index. Unlike temporary indexes, persistent indexes are not discarded until the ClientDataSet against which they were built is closed.

You create IndexDefs at design-time using the IndexDefs collection property editor, shown in the following figure. To display this collection editor, select the IndexDefs property of a ClientDataSet in the Object Inspector and click the ellipsis button that appears.

Note that the IndexDefs collection property editor may not include default indexes. Whether or not default indexes appear depends on whether or not you have loaded data into the ClientDataSet at design-time, and where you loaded that data from.

Click the Add New button on the IndexDefs collection editor toolbar (or press the INS key) once for each persistent index that you want to define for a ClientDataSet. Each time you click the Add New button (or press INS), a new IndexDef is created. Complete the index definitions by selecting each IndexDef in the IndexDefs collection editor, one at a time, and configuring it using the Object Inspector. The Object Inspector, with an IndexDef selected, is shown in the following figure. Note that the Options property has been expanded to show its various flags.

At a minimum, you must set the Fields property of an IndexDef to the name of the field or fields to be indexed. If you are building a multi-field index, separate the field names with semicolons. You cannot include virtual fields, such as calculated or aggregate fields, in an index.

By default, indexes created using IndexDefs are ascending indexes. If you want the index to be a descending index, set the ixDesccending flag in the Options property. Alternatively, you can set the DescFields property to a semicolon-separated list of the fields that you want sorted in descending order. Using DescFields, you can define an index in which one or more, but not necessarily all fields, are sorted in descending order.

Indexed string fields normally are case sensitive. If you want string fields to be indexes without regards to the case of the strings, you can set the ixCaseInsensitive flag in the Options property. Or, you can include a semicolon-separated list of fields whose contents you want sorted case insensitive in the CaseInsFields property. Use the CaseInsFields property when you want to sort some, but not all, string fields without regards to case.

If you want the ClientDataSet to maintain information about groups, set the GroupingLevel property. Groups refer to the unique values on one or more fields of an index. Setting GroupingLevel to 0 maintains no grouping information, treating all records in a ClientDataSet as belonging to a single group. A GroupingLevel of 1 treats all records that contains the same value in the first field of the index as a group. Setting GroupingLevel to 2 treats all records with the combination of vlaues on the first two fields of the index as a group, and so on. GroupingLevel is typically only useful if you are using aggregate fields, or want to call the GetGroupState method. Grouping will be discussed in greater detail in a future article in this series.

In addition to sorting records, indexes can ensure the uniqueness of records. If you want to ensure that no two records contain the same data in the field or fields of an index, set the ixUnique flag in the IndexDef's Option property.

The remaining properties of the TIndexDef class do not apply to ClientDataSets. For example, ClientDataSets do not support expression, primary, or non-maintained indexes. As a result, do not set the Expression property or add the ixNonMaintained or ixPrimary flags to the Options property when defining an IndexDef for a ClientDataSet. Likewise, Source only applies to DataSets that refer to dBASE tables. Do not set the Source property when defining an index for ClientDataSets.

Using Persistent Indexes

A persistent index is created when a ClientDataSet's IndexName property is set to the name of an IndexDef. If IndexName is set at design-time, or is set prior to opening a ClientDataSet, that index is built immediately after the ClientDataSet is opened. Note that a ClientDataSet does not build an index until it needs it. Specifically, even if you have fifty different persistent indexes defined for a ClientDataSet, no index is actually built until the ClientDataSet is opened, and then the only index that will be built will be the one whose name is assigned to the IndexName property. If IndexName is not set to the name of an index, the DEFAULT_ORDER index is used.

Creating Persistent Indexes at Runtime

To create IndexDefs at runtime, you use either the Add or AddIndexDef methods of the object assigned to the ClientDataSet's IndexDefs property, or you can call the ClientDataSet's AddIndex method. Like the related AddFieldDef, AddIndexDef is more flexible than AddIndex, which makes it the recommended method for adding a persistent index at runtime.

AddIndexDef returns an IndexDef instance, which you use to set the properties of the index. For example, the following statement creates an IndexDef for the data in the ClientDataSet, and then makes this the active index:

with ClientDataSet1.IndexDefs.AddIndexDef do 
begin 
  Name := 'LastFirstIdx'; 
  Fields := 'LastName;FirstName';
  Options := [ixDescending, ixCaseInsensitive]; 
end; 
ClientDataSet1.IndexName := 'LastFirstIdx';

Unlike AddFieldDefs, the AddIndex method is a method of the TCustomClientDataSet class. The following is the syntax of AddIndex:

procedure AddIndex(const Name, Fields: string; Options: TIndexOptions;  
  const DescFields: string = ''; const CaseInsFields: string = '';  
  const GroupingLevel: Integer = 0);

As you can see from this syntax, this method requires at least three parameters. The first parameter is the name of the index you are creating, the second is the semicolon-separated list of the index fields, and the third is the index options. Note, however, that only the ixCaseInsensitive, ixDescending, and ixUnique TIndexOptions are valid when you invoke AddIndex. Using any of the other TIndexOptions flags raises an exception.

The fourth parameter, DescFields, is an optional parameter that you can use to list the fields of the index that you want to sort in descending order. You use this parameter when you want some of the index fields to be sorted in ascending order and others in descending order. When you use DescFields, do not include the ixDescending flag in Options.

Like DescFields, CaseInsFields is an optional String property that you can use to select which fields of the index should be sorted without respect to uppercase or lowercase characters. When you use CaseInsFields, do not include the ixCaseInsensitive flag in Options.

The final parameter, GroupingLevel, is an optional parameter that you use to define the default grouping level to use when the index is selected.

An Example: Creating Indexes On-the-fly

One of the most requested features in a database application is the ability to sort the data displayed in a DBGrid by clicking on the column title. The CDSSort project demonstrates how you can add this feature to any DBGrid that displays data from a ClientDataSet. (Click here to download this project.) This project makes use of a generic procedure named SortCustomClientDataSet. This procedure is designed to work with any TCustomClientDataSet descendant, including ClientDataSet, SQLClientDataSet, BDEClientDataSet, and IBClientDataSet. However, some of the properties used in this code are not visible to the TCustomClientDataSet class. Specifically, the IndexDefs and IndexName properties are declared protected in TCustomClientDataSet. As a result, this code relies on runtime type information (RTTI) to work with these properties. This means that any unit implementing this procedure must use the TypInfo unit.

The following is the SortCustomClientDataSet procedure:

uses TypInfo; //TypInfo needed for RTTI GetObjectProp
              //IsPublishedProp, and SetStrProp methods

function SortCustomClientDataSet(DataSet: TCustomClientDataSet;
  const FieldName: String): Boolean;
var
  i: Integer;
  IndexDefs: TIndexDefs;
  IndexName: String;
  IndexOptions: TIndexOptions;
  Field: TField;
begin
Result := False;
Field := DataSet.Fields.FindField(FieldName);
//If invalid field name, exit.
if Field = nil then Exit;
//if invalid field type, exit.
if (Field is TObjectField) or (Field is TBlobField) or
  (Field is TAggregateField) or (Field is TVariantField)
   or (Field is TBinaryField) then Exit;
//Get IndexDefs and IndexName using RTTI
if IsPublishedProp(DataSet, 'IndexDefs') then
  IndexDefs := GetObjectProp(DataSet, 'IndexDefs') as TIndexDefs
else
  Exit;
if IsPublishedProp(DataSet, 'IndexName') then
  IndexName := GetStrProp(DataSet, 'IndexName')
else
  Exit;
//Ensure IndexDefs is up-to-date
IndexDefs.Update;
//If an ascending index is already in use,
//switch to a descending index
if IndexName = FieldName + '__IdxA'
then
  begin
    IndexName := FieldName + '__IdxD';
    IndexOptions := [ixDescending];
  end
else
  begin
    IndexName := FieldName + '__IdxA';
    IndexOptions := [];
  end;
//Look for existing index
for i := 0 to Pred(IndexDefs.Count) do
begin
  if IndexDefs[i].Name = IndexName then
    begin
      Result := True;
      Break
    end;  //if
end; // for
//If existing index not found, create one
if not Result then
    begin
      DataSet.AddIndex(IndexName, FieldName, IndexOptions);
      Result := True;
    end; // if not
//Set the index
SetStrProp(DataSet, 'IndexName', IndexName);
end;

This code begins by verifying that the field passed in the second parameter exists, and that it is of the correct type. Next, the code verifies that the client dataset passed in the first formal parameter has an IndexDefs property. If so, it assigns the value of this property to a local variable. It then calculates an index name by appending the characters "__IdxA" or "__IdxD" to the name of the field to index on, with __IdxA being used for an ascending index, and __IdxD for a descending index.

Next, the IndexDefs property is scanned for an existing index with the calculated name. If one is found (because it was already created in response to a previous header click), that index is set to the IndexName property. If the index name is not found, a new index with that name is created, and then the dataset is instructed to use it.

In the CDSSort project, this code is called from within the DBGrid's OnTitleClick event handler. The following is how this event handler is implemented in the CDSSortGrid project:

procedure TForm1.DBGrid1TitleClick(Column: TColumn);
begin
  SortCustomClientDataSet(ClientDataSet1, Column.FieldName);
end;

As pointed out above, this code has the drawback of requiring RTTI, which is necessary since the IndexDefs and IndexName properties of the TCustomClientDataSet class are protected properties. The CDSSort project also includes a function named SortClientDataSet. This function, shown in the following code segment, is significantly simpler, in that it does not require RTTI. However, it can only be passed an instance of the TClientDataSet class, meaning that it cannot be used with other TCustomerClientDataSet provided by Delphi, such as BDEClientDataSets and SQLClientDataSets.

function SortClientDataSet(ClientDataSet: TClientDataSet;
  const FieldName: String): Boolean;
var
  i: Integer;
  NewIndexName: String;
  IndexOptions: TIndexOptions;
  Field: TField;
begin
Result := False;
Field := ClientDataSet.Fields.FindField(FieldName);
//If invalid field name, exit.
if Field = nil then Exit;
//if invalid field type, exit.
if (Field is TObjectField) or (Field is TBlobField) or
  (Field is TAggregateField) or (Field is TVariantField)
   or (Field is TBinaryField) then Exit;
//Get IndexDefs and IndexName using RTTI
//Ensure IndexDefs is up-to-date
ClientDataSet.IndexDefs.Update;
//If an ascending index is already in use,
//switch to a descending index
if ClientDataSet.IndexName = FieldName + '__IdxA'
then
  begin
    NewIndexName := FieldName + '__IdxD';
    IndexOptions := [ixDescending];
  end
else
  begin
    NewIndexName := FieldName + '__IdxA';
    IndexOptions := [];
  end;
//Look for existing index
for i := 0 to Pred(ClientDataSet.IndexDefs.Count) do
begin
  if ClientDataSet.IndexDefs[i].Name = NewIndexName then
    begin
      Result := True;
      Break
    end;  //if
end; // for
//If existing index not found, create one
if not Result then
    begin
      ClientDataSet.AddIndex(NewIndexName,
        FieldName, IndexOptions);
      Result := True;
    end; // if not
//Set the index
ClientDataSet.IndexName := NewIndexName;
end;

About the Author

Cary Jensen is President of Jensen Data Systems, Inc., a Texas-based training and consulting company that won the 2002 Delphi Informant Magazine Readers Choice award for Best Training. He is the author and presenter for Delphi Developer Days (www.DelphiDeveloperDays.com), an information-packed Delphi (TM) seminar series that tours North America and Europe. Cary is also an award-winning, best-selling co-author of eighteen books, including Building Kylix Applications (2001, Osborne/McGraw-Hill), Oracle JDeveloper (1999, Oracle Press), JBuilder Essentials (1998, Osborne/McGraw-Hill), and Delphi In Depth (1996, Osborne/McGraw-Hill). For information about onsite training and consulting you can contact Cary at cjensen@jensendatasystems.com, or visit his Web site at www.JensenDataSystems.com.

Click here for a listing of upcoming seminars, workshops, and conferences where Cary Jensen is presenting.

Server Response from: BDN9A