Index Oddity
lazarus / 16-Databases

----------------------------------------------------------------------
Old Dog · 2011-02-20 21:24

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 · 2011-02-21 13:28

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 · 2011-02-21 21:57

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 · 2011-02-22 09:27

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 · 2011-02-24 03:42

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

----------------------------------------------------------------------
SteveF · 2011-02-24 17:32

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 · 2011-02-27 20:18

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 · 2011-02-28 06:19

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 · 2011-02-28 07:54

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 · 2011-02-28 16:47

Quote from: Old Dog on February 27, 2011, 09:18:04 pmYou 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 · 2011-03-04 22:29

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 · 2011-03-14 16:30

Quote from: Old Dog on March 04, 2011, 11:29:33 pmThere 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