Indexes in Virtual table

[FCS]

Hello,

Is it possible to create two indexes in the VirtualTable and use both of them ?

For example, I have a table with 2 fields:
1- ID = longint
2- Tekst = string[50];

I need to quick find a record at the first time by ID, and at the next time by Tekst.
I know that I can set the indexed fields, but in this case changing this property of virtual table will probably recreate the index, which will take a lot of time (for table with 5000 records).

Regards
Michal

[AlexP]

Hello,

Yes, you can use several fields by specifying them separated by semicolons.

[FCS]

Hello,

As I understand you, I should use the property IndexFieldNames and set it on ID; Tekst,Tekst2 and this will create two indexes: first on ID, second on Tekst and Tekst2.

But how can I search a value using ID or Tekst+Tekst2 index ?

Type
TX_rec record
ID : longint;
Tekst :string[10];
Tekst2 :string[50];
end;

procedure Search_Value(ID:longint; Tekst, Tekst2:string; var X_rec:TX_rec);
begin
fillChar(X_rec, sizeOf(X_rec), 0);

if ID<>0 then <Search_VT_by_ID(ID, X_rec)>
else if Tekst<>'' then <Search_VT_by_Tekst(Tekst+Tekst2, X_rec)>;
end;

How to implement Search_VT_by_ID and Search_VT_by_Tekst functions ?

Regards
Michal

[AlexP]

You can use the LocateEx method for search.

[FCS]

Hello,

Thanks Alex, but reading your documentation of TVirtualTables I see this description:
"Used to get or set the list of fields on which the recordset is sorted."
It looks like only one index is possible to define.

I need two indexes:
First on ID
Second on Tekst+Tekst2

When the Search_Value procedure is called I want to search by one of these indexes.

In the standard TTable, the default behaviour of changing the IndexFieldNames property value leads to recreate the index.

http://edn.embarcadero.com/article/29056

In this way it will take a time. I need something like "Persistent Indexes" describes in this article.

If I am wrong, please more information.

Regards
Michal

[AlexP]

The below code demonstrates work with two fields as indexfiednames

Code: Select all

program Project1;

{$APPTYPE CONSOLE}

{$R *.res}

uses
  System.SysUtils,
  Data.DB,
  VirtualTable;

var
  VT: TVirtualTable;

  procedure fill(id: integer; const text: string);
  begin
    VT.Append;
    VT.Fields[0].AsInteger := id;
    VT.Fields[1].AsString := text;
    VT.Post;
  end;

  procedure show;
  var
    i: integer;
  begin
    VT.First;
    while not VT.Eof do begin
      for i := 0 to VT.FieldCount - 1 do
        Write(VT.Fields[i].AsString + #9);
      Write(#13#10);
      VT.Next;
    end;
    WriteLn('----------------------')
  end;

  procedure SetIndex(const IndexFieldNames: string);
  begin
    Vt.IndexFieldNames := IndexFieldNames;
  end;

begin
  VT := TVirtualTable.Create(nil);
  try
    VT.AddField('ID', ftInteger);
    VT.AddField('TEXT', ftString, 10);
    VT.Open;
    fill(1, 'b');
    fill(2, 'c');
    fill(1, 'a');
    fill(2, 'a');
    fill(1, 'c');
    fill(2, 'b');
    show;
    SetIndex('ID');
    show;
    SetIndex('');
    SetIndex('TEXT');
    show;
    SetIndex('');
    SetIndex('ID;TEXT');
    show;
    SetIndex('');
    SetIndex('TEXT;ID');
    show;
    VT.Close;
  finally
    VT.Free;
    readln;
  end;
end.

[FCS]

Hello,

Thanks for this example.

But my question is still actual, and I see that there is not possible to have more then one index for the VT.

As I understand this example:
SetIndex('') - drops existing index
SetIndex('ID') - sorts and recreates index on ID field

SetIndex('') - drops existing index
SetIndex('TEXT') - sorts and recreates index on Text field

If I have hundreds of searching by ID or/and Text field the switching of indexes will slow down this process.

I see that I should have two VT, first with index set to ID, and second with index set to Text field.

This way my procedure may be like this:

Code: Select all

procedure Search_Value(ID:longint; Tekst, Tekst2:string; var X_rec:TX_rec);
begin
  fillChar(X_rec, sizeOf(X_rec), 0);

  if ID<>0 then  begin
    if  VT_Indexed_by_ID.Locate('ID', ID) then begin
       X_rec.ID   :=VT_Indexed_by_ID.FieldByName('ID').AsInteger;
       X_rec.Text:=VT_Indexed_by_ID.FieldByName('Text').AsString;
       X_rec.Text2:=VT_Indexed_by_ID.FieldByName('Text2').AsString;
    end;
  end else begin
    if (Tekst<>'') and (VT_Indexed_by_Text_Text2.Locate('Tekst;Tekst2', Text+Text2)) 
    then begin
       X_rec.ID   :=VT_Indexed_by_Text_Text2.FieldByName('ID').AsInteger;
       X_rec.Text:=VT_Indexed_by_Text_Text2.FieldByName('Text').AsString;
       X_rec.Text2:=VT_Indexed_by_Text_Text2.FieldByName('Text2').AsString;
    end;
  end;
end;

Regards
Michal

[AlexP]

When modifying the index, re-sorting will occur in any case. Therefore one of the solutions is specifying all the possible fields in the index.

[FCS]

Hello,

Thanks for the answer.

Could you consider to add the 'Persistent Indexes' to the TVirtualTable ?

In this case the table could be have a few indexes. Inserting/deleting records will modify all indexes. The searching data will use existing indexes without recreating them. Something like in DBF files with several *.ndx files. Before searching the presistent index will be chose by index name and searching will use this index.

Regards
Michal

[AlexP]

You can leave your suggestion on our uservoice page . If it gets enough user votes, we will implement it.