Data structures using Interbase SQL

As soon as possible.

hi okemoto,

could you show your datastructure?
it should like

CREATE TABLE People (
  ID NUMBER(9, 0) NOT NULL,
  NAME VARCHAR2(24) NOT NULL,
  REMARK VARCHAR2(48),
  PARENT_ID NUMBER(9, 0)
)

meikl

I do not have a specific data structure in mind but I would like to get the best solution. Can you suggest one?

hi okemoto,

well, i've done a little sample-app,
its independent from the database, therefore you can use interbase or paradox or whatever.

unit db_childs_u;

interface

uses
  Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
  StdCtrls, Grids, DBGrids, Db, DBTables;

type
  TForm1 = class(TForm)
    Table1: TTable;
    DataSource1: TDataSource;
    DBGrid1: TDBGrid;
    Button1: TButton;
    Button2: TButton;
    Button3: TButton;
    procedure Button1Click(Sender: TObject);
    procedure Button2Click(Sender: TObject);
    procedure Button3Click(Sender: TObject);
  private
    function CountChildren(Parent : Integer) : Integer;
    { Private declarations }
  public
    { Public declarations }
  end;

var
  Form1: TForm1;

implementation

{$R *.DFM}

//Table-structure named People
//ID->Integer->Autoincrement
//ParentID->Integer
//Name->Char(WhateverlengthNeeded)
//here can be more fields

//append as child of current selected record
procedure TForm1.Button1Click(Sender: TObject);
var ID : Integer;
begin
  If (Table1.State = dsBrowse) and
      not (Table1.IsEmpty) then
  Begin
    ID := Table1.FieldByName('ID').AsInteger;
    Table1.Append;
    Table1.FieldByName('ParentID').AsInteger := ID;
  end;
end;

//post - save changes
procedure TForm1.Button2Click(Sender: TObject);
begin
  if (Table1.State = dsEdit) or
     (Table1.State = dsInsert) then Table1.Post;
end;

//Count children of current Record
procedure TForm1.Button3Click(Sender: TObject);
begin
  ShowMessage(table1.FieldByName('Name').AsString+' has '+
              inttostr(CountChildren(table1.FieldByName('Id').AsInteger))+
              ' Children and ChildCildren');
end;

//recursive counting
function TForm1.CountChildren(Parent : Integer) : Integer;
var
  q : TQuery;
begin
  Result := 0;
  q := TQuery.Create(Self);
  q.DatabaseName := Table1.DataBaseName;
  q.SQL.Clear;
  q.SQL.Add('Select * from People where ParentId = '+IntToStr(Parent));
  q.Open;
  q.First;
  while not q.Eof do
  Begin
    Result := Result+CountChildren(q.FieldByName('ID').AsInteger)+1;
    q.Next;
  end;
  q.Close;
  q.Free;
end;

//Count children of current Record
procedure TForm1.Button3Click(Sender: TObject);
begin
  ShowMessage(table1.FieldByName('Name').AsString+' has '+
              inttostr(CountChildren(table1.FieldByName('Id').AsInteger))+
              ' Children and ChildChildren');
end;

end.

meikl

hi again,

its a solution to point 3.
other follows
(just a change in the
CountChildren-Function)

meikl

ok, here the change


//recursive counting
function TForm1.CountChildren(Parent,MaxGenarations : Integer) : Integer;
var
  q : TQuery;
begin
  Result := 0;
  q := TQuery.Create(Self);
  q.DatabaseName := Table1.DataBaseName;
  q.SQL.Clear;
  q.SQL.Add('Select * from People where ParentId = '+IntToStr(Parent));
  q.Open;
  q.First;
  while not q.Eof do
  Begin
    if MaxGenarations <> 0 then
      Result := Result+CountChildren(q.FieldByName('ID').AsInteger,MaxGenarations-1);
    Result := Result + 1;
    q.Next;
  end;
  q.Close;
  q.Free;
end;

//Count children of current Record
procedure TForm1.Button3Click(Sender: TObject);
begin
  //All Childs-> MaxGenerations-Param is -1
  ShowMessage(table1.FieldByName('Name').AsString+' has '+
              inttostr(CountChildren(table1.FieldByName('Id').AsInteger,-1))+
              ' Children');
  //Childs of one generation->MaxGenerations-Param is "wanted genetionsdepth" -1 := 0
  ShowMessage(table1.FieldByName('Name').AsString+' has '+
              inttostr(CountChildren(table1.FieldByName('Id').AsInteger,0))+
              ' Children');
  //Childs of two generation->MaxGenerations-Param is "wanted genetionsdepth "-1 := 1
  ShowMessage(table1.FieldByName('Name').AsString+' has '+
              inttostr(CountChildren(table1.FieldByName('Id').AsInteger,1))+
              ' Children');
end;

meikl

Listening...

meikl,

I provided three questions. You answered the second one. What about the first and third? I am not trying to be picky but I just want a complete solution.
Thanks.



 

 
 

hi okemoto,

i thought i had answered all three
based on the last version of the
function TForm1.CountChildren(Parent,MaxGenarations : Integer) : Integer;

 
//Count children of current Record
procedure TForm1.Button3Click(Sender: TObject);
begin

//that call to 3.
//All Childs-> MaxGenerations-Param is -1
ShowMessage(table1.FieldByName('Name').AsString+' has '+
inttostr(CountChildren(table1.FieldByName('Id').AsInteger,-1))+
' Children');

//that call to 2.
//Childs of one generation->MaxGenerations-Param is "wanted genetionsdepth" -1 := 0
ShowMessage(table1.FieldByName('Name').AsString+' has '+
inttostr(CountChildren(table1.FieldByName('Id').AsInteger,0))+
' Children');

//that call to 1.
//Childs of two generation->MaxGenerations-Param is "wanted
genetionsdepth "-1 := 1
ShowMessage(table1.FieldByName('Name').AsString+' has '+
inttostr(CountChildren(table1.FieldByName('Id').AsInteger,1))+
' Children');

end;

because you can pass now a MaxGenerationdepth-paramater:
is the paramater -1 -> all childs are counted of current record
is the paramater 0 -> one child-generation is counted of current record
is the paramater 1 -> two child-generations are counted of current record
is the paramater 2 -> three child-generations are counted of current record
is the paramater 3 -> four child-generations are counted of current record
...and so on

did i missed something ?

meikl

I will try the solution and if it meets the criteria, you got it!
Will let you know.
Thanks!.

meikl,

I tried your solution and I have a comment regarding the performance. I used it with the data I posted for the question and even with such little data I noticed that the performance was an issue. I am concerned if for example I am working with 4~5,000 records. This will be evidently noticeable. Any improvements on your code?

hi okemoto,

first, yes, it will take time as every counting or calculation (just go into the explorer->rightclick on a dir->select propertys and look how the explorer counted the byteamount of the dir)

well have done a test to all three parts with your given data and get the result in <0.5 sec for each.

yes,
there are performance-improvements possible by code:

- generally to minimize the datatransfer, the query can changed into:
q.SQL.Add('Select Id from People where ParentId = '+IntToStr(Parent));
- if the maxgenarations parameter is 0, then a codebranch can be added like
If MaxGenarations = 0 then
begin
  q.SQL.Add('Select count(Id) as CId from People where ParentId = '+IntToStr(Parent));
  q.Open;
  if q.eof then Result := 0
  else Result := q.FieldByName(CId).AsInteger;
end
else . . .

and yes, there also performance-improvements (code-independent) in configuration-settings and os-settings (caching) also in the used hardware (highend)

and yes, there are also improvements on sql-based serversystems->let the server count->stored procedure/function

as you see, are there some performance influences, which are code independent, used hardware, used databasesystem, OS-Settings, used databasedriver, concurrent tasks on server/workstation, network and so on.

meikl

hi again,

this will be the changes for code:

//recursive counting
function TForm1.CountChildren(Parent,MaxGenarations : Integer) : Integer;
var
  q : TQuery;
begin
  Result := 0;
  q := TQuery.Create(Self);
  q.DatabaseName := Table1.DataBaseName;
  q.SQL.Clear;
  if MaxGenarations = 0 then
  begin
    q.SQL.Add('Select Count(Id) as CId from People where ParentId = '+IntToStr(Parent));
    q.Open;
    If q.Eof then Result := 0
    else Result := q.FieldByName('CId').AsInteger;
  end
  else
  begin
    q.SQL.Add('Select Id from People where ParentId = '+IntToStr(Parent));
    q.Open;
    q.First;
    while not q.Eof do
    Begin
      // if MaxGenarations <> 0 then  //never used
      Result := Result+CountChildren(q.FieldByName('ID').AsInteger,MaxGenarations-1);
      Result := Result + 1;
      q.Next;
    end;
  end;
  q.Close;
  q.Free;
end;

meikl

Good! You deserve the points!!!