Cut Off All Excess

Like the sculptor Rodin, programmers regularly take a block of program code and cut off everything superfluous from it. At the same time, they have to think a lot, since each “cutting” makes the code more elegant, but increases the complexity of the entire composition.

The first two versions of ClassExplorer look a little heavy because for some entities I wanted to have two views: a table view and a tree view. The tree is good because the records can be grouped in an arbitrary way, which improves the perception as a whole, allowing you to see the entire structure at once. And a traditional table displays records in sorted form, which can make it easier to visually search. Perhaps, with an advanced search system, this argument looks weak, but I still decided to leave the ability to display the tree as a linear list, while removing the components that duplicate this functionality.

Transformation theory

At least three fields are used to build the tree:

• ID – record ID
• ParentID – parent record ID
• Name – display data

Field names ParentID and Name are usually specified when setting component properties visually (in ClassExplorer they are assigned programmatically). But the ID field is implicitly used in MVDB components to display tables, while this field is not even displayed in the database designer, but plays an important role in the operation of the tree.

When requesting data, the tree executes a query like this:

SELECT ID,ParentID,Name FROM tableName ORDER BY ParentIDCode language: SQL (Structured Query Language) (sql)

Sorting is set implicitly and the first element in it must be ParentID – the identifier of the parent record. This is due to the internal algorithm for building a tree, which occurs as follows. First, all entries that have the value ParentID = NULL are added to the tree, these are the root elements (the top nodes of the tree), then for each node, child elements are added recursively. This build process continues until all data from the sample set is in the visual component.

Please note that if the initial request for some reason does not contain elements with ParentID = NULL or these elements do not go at the beginning of the selected data, then the tree will remain empty: the data in it will not be loaded.

To turn a tree into a linear list, it is enough to slip in as ParentID a field that always contains the value NULL. To do this, in the table structure, we add such a field – nullID – optional, which will never be edited and will always have the value NULL.

Now we need some scripting magic to turn a tree into a list and back in an instant.

Scripts

First, let’s create a small field naming convention that will allow us to perform further manipulations with the tree.

const
  // field naming conventions for switching table/list mode
  TREE_LIST_MODE_FIELD_NAME = 'nullID'; // list mode
  TREE_TREE_MODE_FIELD_NAME = 'parentID'; // tree mode
  // headers for the menu
  TREE_LIST_MODE_MENU_CAPTION = 'List'; // list mode
  TREE_TREE_MODE_MENU_CAPTION = 'Tree'; // tree mode
Code language: Delphi (delphi)

Now you can add three procedures that will be responsible for switching the mode and controlling it.

procedure Tree_SwitchToList( ATree:TdbTreeView; AFilter:string; ACustomOrderBy:string );
// switch to list mode
begin
  // set the parent element field, filter and sort
  ATree.dbFieldParentID := TREE_LIST_MODE_FIELD_NAME;
  ATree.dbFilter := AFilter;
  ATree.dbCustomOrderBy := ACustomOrderBy;
  ATree.dbUpdate; // update the data
  // hide tree graphic elements
  TNXTreeColumn(ATree.Columns[0]).ShowLines := False;
  TNXTreeColumn(ATree.Columns[0]).ShowButtons := False;
end;

procedure Tree_SwitchToTree( ATree:TdbTreeView; AFilter:string; ACustomOrderBy:string );
// switch to tree mode
begin
  // set the parent element field, filter and sort
  ATree.dbFieldParentID := TREE_TREE_MODE_FIELD_NAME;
  ATree.dbFilter := AFilter;
  ATree.dbCustomOrderBy := ACustomOrderBy;
  ATree.dbUpdate; // update the data
  // show tree graphic elements
  TNXTreeColumn(ATree.Columns[0]).ShowLines := True;
  TNXTreeColumn(ATree.Columns[0]).ShowButtons := True;
end;

function Tree_isListMode( ATree:TObject;):boolean;
// define tree operation mode
begin
  Result := TdbTreeView(ATree).dbFieldParentID = TREE_LIST_MODE_FIELD_NAME;
end;Code language: Delphi (delphi)

Another procedure will add elements to the popup menu to switch the display mode of the tree: Tree_AddSwitchToPopup() allows you to add two menu items and connect click handlers to them. This scheme is made for maximum flexibility of use: in addition to changing the dbFieldParentID property, mode control procedures need sorting and filtering parameters (dbFilter and dbCustomOrderBy parameters), and these may depend on the data being displayed.

procedure Tree_AddSwitchToPopup( ATree:TdbTreeView; ASwitchToListProc:string; ASwitchToTreeProc:string; );
// add a display mode switch to the popup menu
// ASwitchToListProc - name of the handler procedure for switching to list mode
// ASwitchToTreeProc - name of the handler procedure for switching to tree mode
var
  tmpMenu:TPopupMenu;
  tmpItem:TMenuItem;
  tmpForm: TForm;
begin
  CForm(ATree,tmpForm);
  tmpMenu := ATree.PopupMenu;
  // add a visual separator
  tmpItem := TMenuItem.Create(tmpForm);
  tmpItem.Caption := '-';
  tmpMenu.Items.Add(tmpItem);
  // add switch to tree mode, default
  tmpItem := TMenuItem.Create(tmpForm);
  tmpItem.Caption := TREE_TREE_MODE_MENU_CAPTION;
  tmpItem.GroupIndex := 1; // items are linked into a group
  tmpItem.RadioItem := True; // radio button
  tmpItem.Checked := True; // checked
  tmpItem.AutoCheck := True; // automatic switch on click
  tmpItem.onClick := ASwitchToTreeProc;
  tmpMenu.Items.Add(tmpItem);
  // add switch to list mode
  tmpItem := TMenuItem.Create(tmpForm);
  tmpItem.Caption := TREE_LIST_MODE_MENU_CAPTION;
  tmpItem.GroupIndex := 1;
  tmpItem.RadioItem := True;
  tmpItem.AutoCheck := True;
  tmpItem.onClick := ASwitchToListProc;
  tmpMenu.Items.Add(tmpItem);
end;
Code language: Delphi (delphi)

The following two procedures contain project conventions, which deprive them of the status of universality. Perhaps in the future I will solve this problem, but for now, in order to not freeze for a long time in the Thinker’s pose, I added the filter and sort settings directly to the DTF_SwitchToList_OnClick() and DTF_SwitchToTree_OnClick() procedures.

procedure DTF_SwitchToList_OnClick(Sender:TObject);
// switch to list mode
var
  tmpMenu:TPopupMenu;
  tmpTree:TdbTreeView;
  tmpFilter:string;
  tmpForm: TForm;
begin
  tmpMenu := TPopupMenu( TMenuItem(Sender).GetParentMenu );
  tmpTree := TdbTreeView( tmpMenu.PopupComponent );
  tmpFilter := '';
  CForm(tmpTree,tmpForm);
  case tmpForm.Name of
  'dtfTask_Tree': tmpFilter := 'isGroup = 0';
  'dtfClassType_Tree': tmpFilter := 'isType = 0';
  'dtfFuncProc_Tree': tmpFilter := '( id_classType is NULL ) and (isGroup = 0)';
  end;
  Tree_SwitchToList( tmpTree, tmpFilter, 'name' );
end;

procedure DTF_SwitchToTree_OnClick(Sender:TObject);
// switch to tree mode
var
  tmpMenu:TPopupMenu;
  tmpTree:TdbTreeView;
  tmpFilter:string;
  tmpForm: TForm;
begin
  tmpMenu := TPopupMenu( TMenuItem(Sender).GetParentMenu );
  tmpTree := TdbTreeView( tmpMenu.PopupComponent );
  tmpFilter := '';
  CForm(tmpTree,tmpForm);
  case tmpForm.Name of
  'dtfTask_Tree': tmpFilter := '';
  'dtfClassType_Tree': tmpFilter := 'isType = 0';
  'dtfFuncProc_Tree': tmpFilter := 'id_classType is NULL';
  end;
  Tree_SwitchToTree( tmpTree, tmpFilter, 'parentID,name');
end;Code language: Delphi (delphi)

It remains to run the Tree_AddSwitchToPopup () procedure when creating a dynamic form, and everything will work. Below is a fragment of the DTF_Create() procedure, where the required call is added on line 31.

function DTF_Create(AName:string; ATable:string; AListFields:string; AListFieldsNames:string; ASort:string; AParentField:string; AFilter:string; AIsDetail:boolean = False):TForm;
// creating a dynamic form with a table view
// AName - form name; if it contains the _Tree suffix, then the tree form
// ATable - dbGeneralTable for tables and filter buttons; or dbForeignKey for tree
// AListFields - list of displayed fields, you can include calculated and fields from related tables; separator - comma; format: <table>.<field>
// AListFieldsNames - list of field labels
// ASort - dbSortField for tables; dbCustomOredrBy for trees
// AParentField - dbFieldParentID for trees and dbField for dependent tables
// AFilter - string for filtering data
// AIsDetail - a sign that the form is dependent
var
  tmpGrid: TdbStringGridEx;
  tmpButton: TdbButton;
  tmpEdit: TdbEdit;
begin
  // create a form
  Result := TForm.Create(Application);
  with Result do
  begin
    Name := T_DYNAMIC_TABLE_FORM + AName;
    Width := DTF_WIDTH_DEF;
    Height := DTF_HEIGHT_DEF;
    Position := poScreenCenter;
    Scaled := False;
  end;
  // create a table or tree
  if GetSuffix(AName) = SX_TREE then
  begin
    tmpGrid := TdbTreeView.Create(Result);
    tmpGrid.Name := T_TREE_VIEW+GRID_DEFAULT_NAME;
    Tree_AddSwitchToPopup( TdbTreeView(tmpGrid), 'DTF_SwitchToList_OnClick','DTF_SwitchToTree_OnClick' );
  end
  else
  begin
    tmpGrid := TdbStringGridEx.Create(Result);
    tmpGrid.Name := T_TABLE_GRID+GRID_DEFAULT_NAME;
  end;
  //

Code language: Delphi (delphi)

Now it remains to “cut off all unnecessary” visual components on the main form of the application. I decided to get rid of the clutter of multi-pages, leaving only the necessary ones. Where dividers adjoined them, they had to be replaced on panels.

It took some more time to clean up the code from the now unnecessary logic of calculating which of the options (tree or list) is displayed. Then I corrected UserApp_InitForm() – reduced the list of created forms. As a result, the number of forms and components has decreased, while the functionality of the program has been preserved.

If you take a close look at the screenshots, you will see that the “Commands” and “Keywords” tabs have been added. They are supposed to display information about the ObjectPascal language used in My Visual Database scripts. But I do not have full confidence in their need, so I would be grateful for comments and suggestions on the development of the functionality of this program.