(Ebook - Pdf) Kick Ass Delphi Programming

the new level, Form2 (see Figure 11.3) shows two levels at a time. By showing two levels, the data can be arbitrarily nested without changing the visual interface at all.

FIGURE 11.3 A recursive Master/Detail relationship between rows of the same table.

You can change the range of Table1 so it only shows employees with Boss_ID of some chosen value, and Table2 will dutifully show only details of those rows (for example, the employees working for that manager). This “step” type of interface is fine for shallow hierarchy trees, but when there are many paths to follow, it’s easy for a user to get lost. The least you can do is show a label with the lineage, as Figure 11.3 does. The label at the top of the form (Label1) shows the lineage of the current record in Table1.

Hierarchy Navigation

To navigate up and down the hierarchy, you need to offer two additional utilities. In the example we’ve been following, the user goes down the branch when they double-click on a detail row of the right-hand (child) grid. This changes the filter on the table connected to the left-hand (parent) grid so that it shows the same rows as the right-hand grid. This new filter then changes the rows in the right-hand grid: it now shows child rows of the current parent row. This is a difficult business to describe in plain text, so Listing 11.3 shows an event handler for the OnDoubleClick event that demonstrates this action. Read the code closely to make sure you understand how it works.

Listing 11.3 Double-click handler for recursive relationship navigation

procedure TForm2.DBGrid2DblClick(Sender : TObject); var

NewRangeID, SelectedEmployeeID : String;

begin

{show the user the current range }

if Table1.FieldByName('Boss_ID').AsString = '' then Label1.Caption := Table2.FieldByName('Boss_ID').AsString

else

Label1.Caption := Label1.Caption + ':' +

Table2.FieldByName('Boss_ID').AsString;

{Assume that Table1.IndexFieldNames is still Boss_ID;Emp_ID }

SelectedEmployeeID := Table2.FieldByName('Emp_ID').AsString;

NewRangeID := Table2.FieldByName('Boss_ID').AsString; Table1.SetRange([NewRangeID],[NewRangeID]); Table1.FindKey([NewRangeID,SelectedEmployeeID]);

end;

procedure TForm2.UpOneLevelButtonClick(Sender : TObject); var

PrevPos : Integer;

NewRangeID : String;

begin

{We want to filter on the selected employee's Boss_ID } NewRangeID := Table1.FieldByName('Boss_ID').AsString; Table1.CancelRange;

Table1.IndexFieldNames := 'Emp_ID'; Table1.FindKey([NewRangeID]);

NewRangeID := Table1.FieldByName('Boss_ID').AsString; Table1.IndexFieldNames := 'Boss_ID';

{This will resynchronize Table2 } Table1.SetRange([NewRangeID],[NewRangeID]);

if Table1.FieldByName('Boss_ID').AsString = '' then Label1.Caption := '<Top level>'

else begin

PrevPos := 0;

while Pos(':',Copy(Label1.Caption,PrevPos + 1,999))<>0 do

PrevPos :=

Pos(':',Copy(Label1.Caption,PrevPos + 1,999)) + PrevPos;

Label1.Caption := Copy(Label1.Caption,1,(PrevPos - 1)); end;

end;

The left-hand grid’s table is filtered on the current filter’s Boss_ID value when the user clicks the Up One Level button. While this allows infinite recursion, it does not allow any easy way to get a list of all members of a boss’ staff’s staff, and so on down the hierarchy. It also gives you no way to get a staff member’s boss’ boss. You could iterate through the linking values for each level in either direction, but the problem is that it is arbitrary: You don’t know how many levels of ownership you will have to go through to get to either end. (In this, of course, it’s just like any other tree-shaped data structure.)

But hierarchies like this are useful in allowing a user to pick an item from any level of detail while giving an application the right level of data. For example, you could have geographical regions broken down into smaller and smaller areas, but the application would still want to know what region those areas are in. You might also want to have general categories broken into specialties, but still allow the choice of a general category to include all the specialized ones. For example, choosing all Painters automatically includes Painters:Decorative, Painters:Commercial, Painters:Marine, and so on. This way, you don’t have to separately list members of every specialty in a general category to get all of the members of the general category.

Products | Contact Us | About Us | Privacy | Ad Info | Home

Use of this site is subject to certain Terms & Conditions, Copyright © 1996-2000 EarthWeb Inc.

All rights reserved. Reproduction whole or in part in any form or medium without express written permission of EarthWeb is prohibited. Read EarthWeb's privacy statement.

To access the contents, click the chapter and section titles.

Kick Ass Delphi Programming

Go!

Keyword

(Publisher: The Coriolis Group)

Author(s): Don Taylor, Jim Mischel, John Penman, Terence Goggin

ISBN: 1576100448

Publication Date: 09/01/96

Search this book:

Go!

-----------

Displaying the Data

While there is no clever way to avoid walking up and down the hierarchy tree, you can use tools that do all that walking for you. Think about how your users want to use the data. They may not care if it is hierarchical or not, but they may want to find an item by looking for its ancestor. They may want to search for items by name wherever they appear in the hierarchy, or only in the descendents of the current item. They may want the ID of the item they choose, or the entire lineage or progeny of that item.

For example, a basic question you will confront in building applications is what to do when the user asks for the “next” item. This could be the next sibling of the current item’s parent; it could be the first child; it could be the next parent if there are no siblings; it could even be the first child of the next sibling. With a visual interface, the user’s expectations are going to be based on position, display, and their own actions, not necessarily on a logical protocol based on the application’s abstract data design.

Besides the one-level relationship described earlier involving two DBGrid components, the obvious controls to use with hierarchical data are the Outline and TreeView components, because they were specifically created to show trees of items rather than simple linear lists. They tend to take up a lot of screen area, so you cannot use them everywhere you want the user to choose an item from a hierarchy. They also encourage you to load the entire structure into memory at once (which can be memory wasteful). You can configure them to load branches as the user opens those branches, but that flexibility comes with a performance penalty.

An example of how such controls may be loaded is shown in Listing 11.4. You should have a working familiarity with the generation operation of the Outline component before trying to follow this code.

Listing 11.4 Loading an Outline component from a list of items

procedure LoadItemStringsFromTop(ListOfItems : TListOfItems); var

Counter : Integer;

procedure LoadOutline(StartIndex : Integer; StartItem : TItem); var

NewIndex : Integer; begin

NewIndex := MyOutline.AddChildObject(StartIndex, StartItem.Description, StartItem);

if StartItem.FirstChildItem <> nil then LoadOutline(NewIndex,StartItem.FirstChildItem);

if StartItem.FirstSiblingItem <> nil then LoadOutline(StartIndex,StartItem.FirstSiblingItem);

end;

begin MyOutline.Clear;

for Counter := 0 to ListOfItems.Count - 1 do if ListOfItems[Counter].Level = 1 then

LoadOutline(0,ListOfItems[Counter]);

end;

To load an Outline, you can go from the top to the bottom adding children to each node, assuming that each node knows whether it is a top-level node and what its children are. TListOfItems and TItem (present in Listing 11.4) are fictitious classes that contain this information. (See the TreeData Components below.)

Unfortunately the standard hierarchical model does not keep an explicit list of children, but rather determines the set of children as those items that have the same item as a parent. Unless you pre-load the entire set into memory (like a TListOfItems) and establish the parent-to-child links, you have to load from the bottom to the top. That is, as you add each item’s parent item, you have to check if a sibling has added the parent already, and if the parent is loaded, tell the control that your new item is part of that parent’s branch.

Using the Data

The point of any user interface should be to communicate effectively with the user. The user needs to see enough of the data to make a choice (or see enough to know that a choice cannot be made), and then somehow, needs to indicate that choice to the application. With graphical trees, it’s easy enough to use the double-click or spacebar keypress to indicate that the current item is the chosen one.

Once the user makes a choice, your application somehow has to identify the item chosen. The text loaded in the control for an item may not uniquely identify an item and may be duplicated in different branches, so most strategies incorporate additional unique ID code columns that contain no intrinsic information (like dates) in them. These IDs should be short and are usually numerical. This makes it easy to ensure that they are unique by adding one (1) to the highest existing value.

NOTE:

Just because you use the ID value to set up the hierarchy in the control does not mean that you can automatically get the ID value back out again. The Index property of the TOutline class changes for each item as you modify the Outline’s contents; it is a relative value and not specifically associated with any particular item.

To be sure, you must somehow link the ID with the item itself. An ID class is one way to do this.

type

TMyIDClass = class(TObject) public

ID : Integer; end;

begin

…

NewIDObject := TMyIDClass.Create;

NewIDObject.ID := ItemTable.FieldByName('ID').AsInteger; MyOutline.AddChildItem(0, Item

Table.FieldByName('Description').AsString;

To link the ID directly to the item, you can use the Object pointer that is already associated with most Windows controls that store lists of items. The TOutline component offers this pointer as Items[Index].Data instead of calling it Object (another anomaly: the Index value starts from one (1) instead of zero (0), as most lists do). This pointer will link an object descended from TObject (that is to say, any class instance) to an item. You have to define a new class that holds your ID value, create an instance of it for every item you load, put the ID in that instance, and set the pointer.

To get your ID back, your code might look like this:

with MyOutline do

ThisID := (Items[SelectedItem].Data as TMyIDClass).ID;

Your application may need more information than just the ID. You can use the ID to look up that other information in a table, and you can also store that information in the same structure that holds the ID associated with the item simply by expanding the TMyIDClass definition.

Products | Contact Us | About Us | Privacy | Ad Info | Home

Use of this site is subject to certain Terms & Conditions, Copyright © 1996-2000 EarthWeb Inc.

All rights reserved. Reproduction whole or in part in any form or medium without express written permission of EarthWeb is prohibited. Read EarthWeb's privacy statement.

To access the contents, click the chapter and section titles.

Kick Ass Delphi Programming

Go!

Keyword

(Publisher: The Coriolis Group)

Author(s): Don Taylor, Jim Mischel, John Penman, Terence Goggin

ISBN: 1576100448

Publication Date: 09/01/96

Search this book:

Go!

-----------

Finding Rows

You need to identify which item in a control the user chooses, but you will also need to locate an item within a control if the control is going to interact with other controls and components. Depending on your control, you may have to iterate through the entire structure until you find the item you are looking for. If your hierarchy subdivides a sorted list into sorted groups (for example, a parent item for each letter of the alphabet and all the items with descriptions starting with that letter as its children), you can take advantage of the grouping to narrow down your search.

One pitfall to avoid if possible is examining the same items more than once during the search. As with the problem of loading items, if you search starting from an item up through its parent, you will examine the same parent item for every child it has. Unless the contents of the items create some groupings that narrow your search, checking the list of items linearly is always the safest way to find an item.

Using Hierarchical Data in Queries

A hard place to reconcile the hierarchical and relational models is in the area of queries. The relational model is good at identifying rows by their attributes (columns) or common values in other tables (joins). Such a query is often a short, self-evident string in SQL.

SQL, however, is not good at expressing concepts like “somewhere in your lineage, there is a green box.” It is good at finding green boxes, but has no idea what a “lineage” is. The section discussing SQL later on in this chapter has some solutions for finding rows iteratively, but if you have the hierarchy in memory, you can get the lineage as a set of IDs and use it in a query with the IN criteria operator. It looks for a field’s value in a delimited list of alternatives. Listing 11.5 demonstrates how this is done by creating a SQL query programatically in a TQuery’s SQL property. Notice here that SQL does not do all the work itself; the hierarchy object first identifies the lineage using its own machinery.

Listing 11.5 Using SQL to search a lineage

procedure TForm1.FindColoredBoxes(ColorName : String; StartingID : Integer);

var

DescendantList : String;

begin

DescendantString := HierarchyObject.GetDescendants(StartingID); with Query1 do

begin DisableControls; Close;

with SQL do begin

Clear; Add('SELECT *');

Add('FROM BoxList T1');

Add('WHERE T1.BoxColor = "' + ColorName + '"'); { This assumes that the IDs in DescendantString

are delimited with commas } if DescendantString <> '' then

Add('AND T1.BoxID IN (' + DescendantString ')'); end;

Open;

EnableControls;

end;

end;

As an example: If your query is looking for all painters, you can go to your hierarchy, find the parent of all painters, programmatically extract all the descendent IDs of that item, and use those ID values as criteria. This allows you to use a single ID to identify a row, allow that ID to be very specialized, and still retrieve that row when you ask for the general category. Because you use the hierarchy, you don’t have to know how many descendants the Painter item has—you will get them all.

If you have a hierarchy in an Outline or TreeView component, you can use the navigational properties of those components to iterate through the descendants of any given item. Otherwise, you have to load it into memory and set pointers between parents and children, or use the iterative or recursive query techniques described below.

Referential Integrity and Circular References

Ironically, a recursive hierarchy in a single table makes referential integrity easy. Within the table, every parent ID must be another row’s item ID or else null (indicating a top-level item). This protects all of the descendants of an item because no item ID can change if any other row depends upon it.

When the hierarchy table contains lookup values, the dependent column needs only to relate to the Item_ID column in the single table. If the lookup values are in several columns or tables to allow multi-level groupings or relationships, referential integrity becomes more complicated.

The most dangerous thing for code that controls a hierarchy is a circular reference. If one item refers to a non-existent ancestor item, that is a detectable problem; however, if an item refers to an ancestor item that is also a descendent item (which is hard to discern if the items are separated by several generations), the code will loop endlessly, looking for an end to the succession of linked items.

The answer? Apart from checking each candidate ancestor item to see if any of its ancestors are

already in the family (which can be expensive in performance terms), your code can have a burn-out counter that throws an exception if the code processes through an excessive number of search cycles. One comfort is that if you use a graphical control to manage the hierarchy on a form, there is no way the end user can set up a circular reference.

Using SQL

If your hierarchies are huge and cannot operate entirely within memory, a SQL implementation may be a better solution. If you know how many levels of recursion are involved, you can use SQL subqueries to bridge generations, as shown here in Listing 11.6.

Listing 11.6 Using SQL to bridge a known number of generations

SELECT *

FROM Items T1

WHERE T1.Parent_ID IN

(SELECT T2.Item_ID

FROM Items T2

WHERE T2.Parent_ID IN

{SELECT T3.Item_ID

FROM Items T3

WHERE T3.Parent_ID = 'Fred'))

This requires exactly three generations: The only rows returned are the grandchildren of Fred. To get children and grandchildren, run two queries, and either use UNION or add the result sets together using INSERT INTO or temporary tables.

To find an item’s parent item, simply query for the one row where the Item_ID is equal to the Parent_ID. To find all the children of an item, query for all the rows that have the Item_ID value as their Parent_ID. To find all the siblings, find all the items that have the same Parent_ID. (Note here that the original item will also be a part of the set unless you specifically exclude it.) Once you have a child or sibling set, to find all the descendants, you have to iterate through each row of that set and run a query to find its children. Iterate through that set to find any children of the children, and so on.

Products | Contact Us | About Us | Privacy | Ad Info | Home

Use of this site is subject to certain Terms & Conditions, Copyright © 1996-2000 EarthWeb Inc.

All rights reserved. Reproduction whole or in part in any form or medium without express written permission of EarthWeb is prohibited. Read EarthWeb's privacy statement.

To access the contents, click the chapter and section titles.

Kick Ass Delphi Programming

Go!

Keyword

(Publisher: The Coriolis Group)

Author(s): Don Taylor, Jim Mischel, John Penman, Terence Goggin

ISBN: 1576100448

Publication Date: 09/01/96

Search this book:

Go!

-----------

Solving the Problem of Arbitrary Nesting

The big problem comes in when the nesting is arbitrary and you don’t know how many levels a lineage might have. There is no conditional branching in SQL; either a subquery is there or it’s not. Joe Celko devotes two chapters of his book SQL for Smarties1 to Trees and Graphs; that is, the data that goes into visual graphs. His techniques are advanced and demonstrate what you have to do to correctly associate one item (or node) to another.

1SQL for Smarties, Advanced SQL Programming, Morgan Kaufmann, 1995, 1-55860-323-9

If you want a simpler, though possibly inefficient and certainly inelegant solution, consider using a temporary table to hold the cumulative results of many queries (the Final table), and another temporary table (the Working table) to hold the results of the most recent one. You may have to use two Working tables and alternate between them, depending on which SQL server you are using. The algorithm looks like this:

1.Query for children of the starting item;

2.Copy the item IDs to the Working table;

3.Query for children of any of the item IDs in the Working table;

4.If there are no child rows, exit;

5.Add the Working table to the Final table;

6.Erase the Working table and add all the query’s item IDs to it;

7.Go back to step 3.

Each cycle retrieves the next generation, and the Final table will contain all the child rows in generation sequence.

Using Stored Procedures

Stored procedures resemble SQL with conditional and looping logic, which SQL itself