Index Oddity

[Old Dog]

I have a database of states with two indexes, abbreviation and name.  when the active index is abrev the records numbers are sequential.  when the active index is name the record numbers skip.  

Code: [Select]

procedure TFormStates.CreateStatesTable(AFileName : String);

begin
  With DbfStates do
    begin
      Close;
      with FieldDefs do
        begin
          Clear;
          Add('Abrev',ftString,2);
          Add('Name',ftString,25);
          Add('Country',ftString,6);
          Add('Neighbors',ftString,80);
        end;
      TableName:=aFileName;
      CreateTable;
      Exclusive := true;
      Open;
      AddIndex('ndxAbrev', 'Abrev', []);
      AddIndex('ndxName', 'Country + Name', []);
    end;
  MessageDlg('File created',mtWarning,[mbOK],0);
end;

I have tried regenerating the indexes, deleting the index file and re-adding  the indexes, and copying the data into a new file.  Same problem.

I will also create a bug report.

[sorinr]

Hi,

Can you be more specific on what kind of database do you use and in what circumstances do you use it.

From the code you have posted I presume u'r using dbf files.
Is your "name" index unique or one or many records can have the same value?

Regards.

[Old Dog]

yes it is a dbf file created by this program.

the data is unique, there is only one state/province with a given name.

[sorinr]

Hi,

Are all your records in the country field exactly the same? Like "USA"? or some of them are "U.S.A" BECAUSE this is changing the composed index completely.
Or somewhere in the dbf data you have "nil" Country or Name fields.

[Old Dog]

All the same, either 'USA' or 'Canada'.  They are selected from combo box.

[SteveF]

I just want to clarify: when you use "abrev" as your index the record numbers are sequential, but when you use "name" the record numbers are not sequential?

You do realize that DBF files have this oddity: when an index is applied to a table, the records are automagically sorted by the field referenced by that index?  That in itself would explain the differing record number order.

Steve

[Old Dog]

You miss understand.  I am not referring to the physical record number (Dbf.PhysicalRecNo) that the record has in the file, but to the virtual record number (Dbf.RecNo) that the record has, as an example, in a grid.  The data was not entered in order of either index.  There are 59 records.  When I use the abbreviation  index RecNo = {1,2,3,4,5,6,.....55,56,57,58,59}.  But when I use the name index RecNo = {1,2,3,4,5.....38,39,45,46,.....56,57,61,62....71,72,86,87,...}.  These number are not precise, but do illustrate the oddity.

[sorinr]

Yes, you are right. With no index the dbf.recno returns seqvential numbers from 1 to 59.
When you use the abrev index recno returns: 1,2,3.....,24,25,43,44....75,76
When you use the name index recno returns: 1,2...,19,20,25,26,...30,37,...

I will try to import the same data in a firebird table to see if the oddity persist when using zeos' s Ztable.

[sorinr]

Tested it with Zeos components on an FB table containing the same data and the oddity is gone.
The only explanation I think of is that TDbf.RecNo has a bug when using indexes.

[SteveF]

You miss understand.  I am not referring to the physical record number (Dbf.PhysicalRecNo) that the record has in the file, but to the virtual record number (Dbf.RecNo) that the record has, as an example, in a grid.  The data was not entered in order of either index.  There are 59 records.  When I use the abbreviation  index RecNo = {1,2,3,4,5,6,.....55,56,57,58,59}.  But when I use the name index RecNo = {1,2,3,4,5.....38,39,45,46,.....56,57,61,62....71,72,86,87,...}.  These number are not precise, but do illustrate the oddity.

As a non-Dbf user -- I strongly lean toward SQL databases in preference -- is there a reason this change is important?

In the SQL world, I would design the table to hold this data with three columns:
StateID (Numeric, Auto-Increment)
StateName (Text)
StateAbbrev (Text)

Then I don't have to worry about some administrative "record number" that may be arbitrarily assigned by the database engine -- I use the StateID number *held as data in my table* as the foreign key in the other tables that reference the State-data table.

Steve

[Old Dog]

There are actually a few reasons the oddity is interfering with performance.  This is only one of several similar forms.  The other data bases have considerably more records.

1)  I had be using RecNo to navigate the table but came across MoveBy which will work in its place.

2)  I have a group of navigation buttons with differing jumps that I wanted to turn on and off depending on effectiveness.  For example a first button it useless when looking at the first record.

3)  I want to show record of total.  Not very useful if it says I am at record 67 of 59 records.  Assigning a record number to a record, even using auto increment, would only be useful when the records are in the order of entry, not when sort by some other criteria.

[SteveF]

There are actually a few reasons the oddity is interfering with performance.  This is only one of several similar forms.  The other data bases have considerably more records.

1)  I had be using RecNo to navigate the table but came across MoveBy which will work in its place.

2)  I have a group of navigation buttons with differing jumps that I wanted to turn on and off depending on effectiveness.  For example a first button it useless when looking at the first record.

3)  I want to show record of total.  Not very useful if it says I am at record 67 of 59 records.  Assigning a record number to a record, even using auto increment, would only be useful when the records are in the order of entry, not when sort by some other criteria.

As I've just been discovering, working on similar things, Lazarus has some built-in features that deal very nicely with items 2 & 3.

For item 2, use the TDBNavigator control.  If you want to have different jumps, take a look at this topic: http://delphi.about.com/od/usedbvcl/l/aa090203a.htm, which covers custom jumps among others.

For item 3:  The components I'm using for my data connection (ZEOSlib TZQuery) have the following properties:
RecordCount
RecNo

For the total record count, I execute a .Last on the query component & then ask for a .RecordCount.  For the current record (28 of...), grabbing the .RecNo in the TDBNavigator's Click event works quite well...

Steve