Deutsch English
Home
About tdbengine
Newsletter
Download
Helpware
Forum
Chat
Documentation
Installation
Configuration
First Steps
Runtime Switches
Text files
System functions
Database functions
Index functions
Full-text Indexing
Memos and BLOBs
Semaphore concept
Error codes
Hints for PHP developers
Basic Course
Function reference
HOWTO...?
Projects
Links
Benchmarks
Bug Reporting
Support request
 
Home    Overview    Search    Impressum    Contact    Members
Index functions
General information about indices
An index is in its way a subject-heading-list of a table, which is arranged in a special order and thereby it allows to make a quick search for certain records. For generating such a subject-heading-list all field types can be basically used here with the exception of memo, BLOB und relation fields.

Indices are necessary for:
  • quick search for certain (indexed) fields,
  • (relational) linking of tables,
  • arranged (ranked) access to the rows of a table.

For example, if an index is generated in an address table over the "name" field then a new table is made with two fields. The first field has the contents of the corresponding name field, the second - the physical record number of  the related record. This table is created in a very special format what is called a B-tree (a high-performance order and search structure). Because the indexes are placed in external files, we are also speaking about external indexes when referring to the indexes of the tdbengine.

A couple of indexes is generated automatically: If the table contains an AUTO field, then an index (extension .inr) is created over this field.
If the table contains an AUTO field, then an ID index (extension .id) is created in accordance with the structure.
Each link field contained in a table without U-specification leads to an index (extension .in1 to .in9) over this field. Additional indexes can be given in the structure definition as well: [INDEX]
index_1=ind-name:ind-def
index_2=...

ind-name = name of index file with extension .ind
ind-def = index description (see below) Note: The names of indexes must be of no more than 8 characters (without extension) and have letters (without diaeresis) only.

The Indexes of the tdbengine


Of those indexes as many as one likes may be generated (in the current version 6.2.6 up to 15) of each table. The tdbengine offers hierarchical indexes with up to 10 hierarchical layers and calculated indexes.

The definition of an index is performed through an index description. This has the following format: Hierarchical index::=Field-Def{","Field-Def}.
Field-Def::=["$"]Field[":"Number]
Field::=FieldIdentifier|FieldNumber Calculated index::="("Expression")"[":"Number] Examples: Hierarchical index: first name,last name:1,ZIP
Calculated index: (first name+'/'+last name):40 The optional data :Number after a field (or after an expression) is rated only on string fields (or on string expressions) and it specifies how many characters (maximum) of the string are adopted in the index information. At present time (version 6.2.5) restrictions for index descriptions are as follows:
  • The expression of a calculated index presently must not exceed 40 characters.
  • The index information (Sum of all hierarchical layers) may cover a maximum of 256 bytes.

Functions for creating, updating and removing indexes
Note: The following functions are executed only if the table was opened with the right of  "indexing" (=8).

Generating index

GenIndex(db : INTEGER; IndDef : STRING; IndName : STRING) : INTEGER

db : Tablehandle returned by OpenDB()
IndDef : Index description as string
IndName : File name of the index (without path, with .ind' as extension) Return value: 0 : Function can not be executed (Error code with TDB_LastError)
1..15 : Number of the newly created index The most often occurring error is "Index exists already". The tdbengine does not generate any index, when an (index) file of the same name is in the directory of the table to be indexed. Note: The index is always placed in the same directory as the related table. The name of the index file must always have the extension .ind. The file names must correspond to the 8.3 format convention, namely xxxxxxxx.ind. Also directory data is not allowed! In the following functions the parameter index can be set both by either the index names (with extension) or by the the index numbers (is valid for version 6.2.6 and higher, previous versions - only index number or only index name by FindRec() ).


Deleting Index

DelIndex(db : INTEGER; Index) : INTEGER

db : TableHandle returned by OpenDB()
Index: Index name (without path, but with extension) or index number Return value: 0 = Function is successfully executed, otherwise error code is generated The most often occurring error is "Index still in use". As long as the index is in active access of an opened table, it can't be removed. Note: The specified index is definitively deleted and removed from the table. The function must be applied with a great deal of caution. While tables may be related to several projects, one project is often not aware of the importance of an index to other project. After DelIndex() all higher index numbers are decremented by 1.


Updating an index

RegenInd(db : INTEGER; Index) : INTEGER

db : TableHandle returned by OpenDB()
Index: Index name (without path, but with extension) or index number Return value: 0 = Function successfully executed, otherwise error code is generated All indexes are automatically refreshed with the alteration of the related table, so normally there is no necessity for regenerating. However, the performance of the B-tree is going down over time (after several modifications on the table), so that updating leads to the following facts:
  • the search time is a little shorter (hardly measurable though),
  • the hard disk place demand is noticeably decreased.


Updating all Indexes of a table

Refresh(db : INTEGER) : INTEGER

db : TableHandle returned by OpenDB()
Return value: 0 = Function is successfully executed, otherwise error code is generated In doing so all indices of a table are updated.
Note: The functions for index updating must not be applied to tables, which were created by ScanRecs() in minimum mode.

Obtaining index description

IndexDef(db : INTEGER; Index) : STRING

Obtaining and setting access sequence


For each opened table an access sequence can be set, which is related to following functions:

The access sequence is also relevant within a SUB ... ENDSUB statement, when no sorting sequence is given here.

Access(db : INTEGER [; Index]) : INTEGER

Without index parameter the current access sequence is returned (version 6.2.6 and higher only):
-2 : Marking list
-1 : Physical order
0 : AUTO (if available)
1: ID index (if available)
1(2)..15 : corresponding to index number

In addition to the index names (or numbers) the following may be entered as index:
-2 or "Markierung" (= "marks")
-1 or "Nummer"  (="number")

IndexNo(db : INTEGER) : INTEGER

This function returns the access sequence (old version):
-2 : Marking list
-1 : Physical order
0 : AUTO (if available)
1: ID index (if available)
1(2)..15 : corresponding to index number

IndName(db : INTEGER; Index) : STRING

This function returns the name of the specified index.

Searching records using an index

FindRec(db : INTEGER; SearchStr : STRING [;Index[;Mode : INTEGER]]) : INTEGER

db : TableHandle returned by OpenDB()
SearchStr : searched information
index : Index name or index number(>=0)
Mode :
  0 (default value) -> Search for entry >= SearchStr;
  1 -> Search for entry = SearchStr
Return: 0 -> no record is found, otherwise RecNo(db)

Notes: The function SearchStr must be put adequately to index description. If a component contains a comma, the expression must be set in brackets.

Examples:
Index description ="first name,last name,place"
SearchStr = "Schwalm,Till,München", "Lichtenberg,Franz,(München,Moosach)"

If the information is obtained from the user, the bracketing is advisable for all components: SearchStr:='('+s_ first name+'),('+s_ last name+'),('+s_ place+')'
If no index is stated, the search queries the current index (set by Access()). If the access is not available to an index (but to number or marking), the error message "illegal access" is returned.
The smallest entry (regarding the index order) is sought in the mode 0, which is equal to or greater than the searched one. If no entry is available here, the greatest entry is sought, which is equal to or smaller than the searched one. In this mode 0 is returned only, if the table is completely empty.
On the contrary if in mode 1 an entry is found, which corresponds to the search results in all components, the return value is unequal to 0.

Example:

Table db:

RecNo(db)

First name

Last name

Place

1

Meier

Franz

München

2

Asbacher

Edeltraud

Frankfurt

3

Grünwalder

Antonia

Erfurt

4

Meier

Hans

Hamburg

5

Vogelsang

Ottfriede

Gelsenkirchen


Index 1 Description: "first name,last name,place"
Index 2 Description: "( first name+'/'+last name)" Index 1 (internal)

Index Information

RecNo(db)

Asbacher,Edeltraud,Frankfurt

2

Grünwalder,Antonia,Erfurt

3

Meier,Franz,München

1

Meier,Hans,Hamburg

4

Vogelsang,Ottfriede,Gelsenkirchen

5


FindRec(db,"Meier,Franz,München",1,0) -> 1
FindRec(db,"Meier,Franz,München",1,1) -> 1
FindRec(db,"Meier,Franz",1,0) -> 1
FindRec(db,"Meier,Franz",1,1) -> 0

Index 2 (internal)

Index Information

RecNo(db)

Asbacher/Edeltraud

2

Grünwalder/Antonia

3

Meier/Franz

1

Meier/Hans

4

Vogelsang/Ottfriede

5


FindRec(db,"Meier/Hans",2,0) -> 4
FindRec(db,"Meier/Hans",2,1) -> 4
FindRec(db,"Meier",2,0) -> 1
FindRec(db,"Meier",2,1) -> 0

Setting a filter


With the help of an index a table is put into order. Regarding this order a corresponding part (an extract) is defined from the table by a filter.

SetFilter(db : REAL; FROM [, to] : STRING) : REAL

db : TableHandle returned by OpenDB()
from, to : Index information according to index description
Return value: always 0

The function SetFilter is effective if the access was set to an index only, that is after Access (db,...).

A filter is active until
  • it is switched off by function SetFilter(db,''), or
  • the primary table is changed.

If a filter is active, the function FirstRec() executes a function FindRec(db,FROM), but returns a value only, if the found record is equal to or greater than the contents of "from".
The same is valid for the function LastRec(). The function NextRec() or PrevRec() return results only if the found values are available in the specified range. The function SUB returns records from this range only as well.

The parameter to may be left out only if the related index description contains string fields only: Then SetFilter(db,FROM) is used for abbreviation for SetFilter(db,from,from+chr(255)). On a hierarchical index non-given components are filled with the smallest possible value of "from" and with the greatest possible one of "to".

The filter automatics of the tdbengine


During the selection processing the tdbengine makes an effort to minimize the amount of data to be read and thereby the selection costs by means of investigating all available indices for the fact, if a proper filter can be set. This automatics operates independently of the sequence of the logic operations and also independently of the shortened logic analysis.

The automatics will be turned off, if in the moment the selection resolves
  • the access takes place via the marking list, or
  • the access is established to another index instead of  the index .inr (via the auto-numbers).

Example for the operating mode of the filter automatics:

[STRUCTURE]
field_1=FirstName,STRING,40
field_2=LastName,STRING,30
field_3=Street,STRING,40
field_4=ZIP,STRING,5
field_5=Place,STRING,30
[INDEX]
index_1=addr_name.ind:FirstName,LastName
index_2=addr_zip.ind:ZIP,place

Selection

Index

Filter from ... to

$FirstName like "Meier"

addr_name.ind

meier

$ FirstName like "Meier*" and $LastName like "Hans"

addr_name.ind

meier,hans

$ZIP from "70000" to "79999"

addr_zip.ind

70000 ... 79999,chr(255)+chr(255)...

$FirstName like "*gruber" and $ZIP like "8*"

addr_zip.ind

8
( "*gruber" does not allow filter)

$FirstName like "after*meier*" and $ZIP like "8*"

addr_name.ind

after
(4 characters by "after" in comparison with 1 character by "8")


The filter automatics makes sure that the function SetFilter() must be used in few special cases only.



tdbengine Anwendungen im Web:

Open-Source Web CMS


Open-Source Bug-Tracking


Free wiki hosting

Open-Source Wiki-System

Kostenloses Foren-Hosting

Diät mit tdbengine 8-)

tdbengine chat
irc.tdbengine.org
#tdbengine

   Copyright © 2003-2004 tdb Software Service GmbH
   Alle rechte vorbehalten. / All rights reserved
   Last changed: 05.05.2004


ranking-charts.de

Programmers Heaven - Where programmers go!