create and use an index for a set of records

[MarcoBoschi]

Hi to all
Given a list of records ( see array aRecord ) ,
does exist a faster way to create a temporary index containing only these records?

Thank in advance?

Marco

#include "fivewin.ch"
ANNOUNCE RDDSYS

FUNCTION MAIN
LOCAL nIni
SET DELETED ON

SET EXCLUSIVE OFF
USE clihs

PUBLIC aRecord := { 985 , 10754 , 22456 , 23677 , 23679 , 32121 , 35109 , 38372 , 43872 }
PUBLIC nRecI := 1
PUBLIC nRecF := 9
PUBLIC nRecC := 1
PUBLIC lWhile := .T.

nIni := SECONDS()

INDEX ON field->clifor TAG ARECORD TO CLIFOR_REC FOR punta_record() WHILE M->lWhile

browse()


RETURN NIL


FUNCTION PUNTA_RECORD()
LOCAL lReturn := .F.
DBGOTO( M->aRecord[ M->nRecC ] )

IF M->nRecC < M->nRecf
M->nRecC ++
lReturn := .T.
ELSE
GO BOTTOM
M->lWhile := .F.
ENDIF

RETURN lReturn

INIT PROCEDURE RddInit
REQUEST DBFFPT
REQUEST DBFCDX
rddSetDefault( "DBFCDX" )
RETURN

[MarcoBoschi]

I'm sorry
This is the source working sample code


#include "fivewin.ch"
ANNOUNCE RDDSYS

REQUEST HB_GT_GUI_DEFAULT

STATIC aRecord
STATIC nRecF
STATIC nRecC
STATIC lWhile

FUNCTION MAIN
SET DELETED ON

SET EXCLUSIVE OFF
USE clihs


aRecord := { 100 , 200 , 300 , 400 , 500 , 600 , 700 , 800, 900 , 1000, 1100 }
nRecF := LEN( aRecord )
nRecC := 1
lWhile := .T.

GOTO aRecord[ 1 ]
INDEX ON field->clifor TAG ARECORD TO CLIFOR_REC WHILE punta_record()

GO TOP
BROWSE()



RETURN NIL


FUNCTION PUNTA_RECORD()
LOCAL lRitorna := .F.

IF nRecC <= nRecF
GOTO aRecord[ nRecC ]
nRecC ++
lRitorna := .T.
ELSE
lWhile := .F.
ENDIF
RETURN lRitorna

INIT PROCEDURE RddInit
REQUEST DBFFPT
REQUEST DBFCDX
rddSetDefault( "DBFCDX" )
RETURN

[James Bott]

Marco,

The simplest solution would be to move all the record data into the array and then browse the array.

The next simplest would be to create a database class that has a modified skip() method that uses the array as an index. The set the oBrw:bSkip codeblock to use this database skip method.

The most complicated way would be to write functions to work with standard DBF commands that use the array as an index.

Regards,
James

[demont frank]

Marco,

The INDEX command has a clause CUSTOM :


CUSTOM specifies that a custom built order will be created for RDDs
that support them. A custom built order is initially empty, giving you
complete control over order maintenance. The system does not
automatically add and delete keys from a custom built order. Instead,
you explicitly add and delete keys using ORDKEYADD() and ORDKEYDEL().
This capability is excellent for generating pick lists of specific
records and other custom applications.

I never used it , but i suppose you have to

Code: Select all

LOCAL aRecord := { 985 , 10754 , 22456 , 23677 , 23679 , 32121 , 35109 , 38372 , 43872 } , nRecord

INDEX ON field->clifor TAG ARECORD CUSTOM//TO CLIFOR_REC FOR punta_record() WHILE M->lWhile
FOR EACH nRecord In aRecord
    GOTO nRecord
    OrdKeyAdd()
NEXT
browse()


 

[MarcoBoschi]

COOL!
Thank you so much Frank

[MarcoBoschi]

James,
your solution is very interesting too
Thank you

[James Bott]

Marco,

Well, Frank's solution looks easier. I was not aware of the CUSTOM clause.

Let us know what solution you use.

Regards,
James

[nageswaragunupudi]

The INDEX command has a clause CUSTOM :

This is the best way to deal with a hand-picked set of records. Along with CUSTOM clause, also if we use TEMPORARY ( or MEMORY ) clause, the index is created locally in the memory or temporary space ( depending on RDD ) and is even faster and is automatically erased when the table is closed. This is what I do in my programs where this is necessary.

Incidentally, if the only intention is to browse the record numbers in an array, xbrowse provides even much simpler way to do this, even without creating any index, custom or otherwise.

Use the normal syntax for creating xbrowse for the DBF and add the clause ROWS aRecArray.
Only the record numbers in the aRecArray are browsed.

Example:

Code: Select all

@ 0,0 XBROWSE oBrw OF oWnd AUTCOLS ALIAS "CUSTOMER" ROWS { 22, 44, 66 }
 

[MarcoBoschi]

Everything comes from the fact that a function like this at_search() free searches in a table is very much faster than traditional methods.
The function returns an array containing the records that I find the string passed
as parameter.
I put this function in a application that run in a server (search32.exe) in polling mode.
Result of every request from clients is an index creation or an Array containing matching record.

#include "fivewin.ch"
#include "FileIO.ch"
ANNOUNCE RDDSYS

#define crlf CHR(13)+CHR(10)

// parameters: dbf file to open and string to search with .AND. clause ( not words but strings! )
FUNCTION MAIN()

LOCAL aRecord := {}
LOCAL i
LOCAL cRecords := ""
LOCAL nRecord

aRecord := at_search( "clifor", "COMPUTER", "PADOVA" )

FOR i := 1 TO LEN( aRecord )
cRecords := cRecords + " " + ALLTRIM( STR(aRecord) )
NEXT i
? cRecords

USE clifor
INDEX ON recno() TAG COD_CLI CUSTOM TO clifor_rec
FOR EACH nRecord In aRecord
GOTO nRecord
OrdKeyAdd()
NEXT
// open browse to monitor
GO TOP
browse()

RETURN NIL

FUNCTION AT_SEARCH( cFile , cString1 , cString2, cString3, cString4 )

LOCAL iW1, iW2
LOCAL cStringa
LOCAL cIndRec := cFile + "_rec"

LOCAL nHandle
LOCAL nSize
LOCAL nRead
LOCAL nPosRec
LOCAL nHead, nRecSize

LOCAL nPosStart := 0
LOCAL nPosRecord := 0
LOCAL aPosRec1 := {}
LOCAL aPosRec2 := {}
LOCAL aString := {}
LOCAL nAndOr := 1
LOCAL aRecord := {}
LOCAL cTrova := ""
LOCAL nFound
LOCAL cRecord
LOCAL nPos2
LOCAL nIni := SECONDS()
LOCAL lDeleted

SET DELETED OFF
SET EXCLUSIVE OFF

// load an array with strings to search
IF cString1 <> NIL ; AADD( aString , cString1 ) ; ENDIF
IF cString2 <> NIL ; AADD( aString , cString2 ) ; ENDIF
IF cString3 <> NIL ; AADD( aString , cString3 ) ; ENDIF
IF cString4 <> NIL ; AADD( aString , cString4 ) ; ENDIF


// open and read all dbf file into cStringa variable
nHandle := FOPEN( cFile + ".dbf" )
nSize := FSeek( nHandle, 0, FS_END )
FSeek( nHandle, -nSize, FS_RELATIVE )
cStringa := SPACE( nSize )
nRead := FRead( nHandle, @cStringa, nSize )
FCLOSE( nHandle )

// open with use commands to obtain header and recsize
USE &cFile
nHead := header()
nRecSize := recsize()
USE
nPosRec := 1
nFound := 0
iW1 := 1
iW2 := 1
nPosStart := 1

// enter in a loop to begin search operations
DO WHILE .T.

// search counter
nFound := 0

// aPosrec1 contain in position 1 the absolute position in cStringa and in position
// 2 the calculated record
aPosRec1 := ATREC( aString[ 1 ] , cStringa , nPosStart, nHead, nRecSize )

IF aPosRec1[ 1 ] > 0 // if the first string to search is founded...

// if a * is present record is deleted
lDeleted := IIF(SUBSTR( cStringa , nHead + nRecSize * ( aPosrec1[ 2 ] -1) +1 , 1 ) = "*", .T., .F. )
IF .NOT. lDeleted
// start position for next search is the beginning of record
nPosStart := aPosRec1[1] + 1

// IF there are other strings to search
IF LEN( aString ) > 1

// first string has been founded
nFound ++

// create a string containing only the actual record
nPosRecord := nHead + nRecSize * ( aPosrec1[ 2 ] - 1 ) + 2
cRecord := SUBSTR( cStringa , nPosRecord , nRecSize )

// pointer for second string to search
iW2 := 2
DO WHILE .T.

// I search the second string only in the record string (more little)
nPos2 := AT( aString[ iW2 ] , cRecord )

IF nPos2 > 0
// second string is founded
nFound ++
ELSE
// if not found exit from loop
EXIT
ENDIF

// if there are other strings to search increment pointer of 1
IF iW2 < LEN( aString )
iW2 ++
ELSE
// otherwise exit
EXIT
ENDIF

ENDDO

// at the exit of loop IF all strings are founded I add record number to aRecord Array
IF nFound = LEN( aString )
AADD( aRecord , aPosRec1[2] )
ENDIF
nPosStart ++

ELSE // there is only one string to search

AADD( aRecord , aPosRec1[2] )
ENDIF
ELSE

nPosStart := nPosStart + nHead + nRecSize

ENDIF
ELSE
EXIT
ENDIF
ENDDO

? SECONDS() - nIni



RETURN aRecord

FUNCTION ATREC( cTesto , cStringa , nStart, nHead, nRecSize )
LOCAL nPos := 0
LOCAL nRecord := 0
nPos := AT( cTesto , cStringa , nStart )

IF nPos > 0
nRecord := INT( ( nPos - nHead ) / nRecSize ) + 1
ENDIF
RETURN { nPos, nRecord }

FUNCTION DbfSize()
RETURN ( (RecSize() * LastRec()) + Header() + 1 )




INIT PROCEDURE RddInit
REQUEST DBFFPT
REQUEST DBFCDX
rddSetDefault( "DBFCDX" )
RETURN

[Dorneles]

Is to do a search in a field
specific database?

Thank
Dorneles

[MarcoBoschi]

No this is a wild search in all fields of database.
For instance if You considere cutomer.dbf from fwh\samples change Main function on this way

even if the table is not the best example to appreciate the potential of this function.
Customer table is too little.
I search customer name "Tom" that live in a "Street"
aRecord := at_search( "customer", "Tom", "Street" )
Please try this function in a your big big table

Thanks

Marco


FUNCTION MAIN()

LOCAL aRecord := {}
LOCAL i
LOCAL cRecords := ""
LOCAL nRecord

aRecord := at_search( "customer", "Tom", "Street" )

FOR i := 1 TO LEN( aRecord )
cRecords := cRecords + " " + ALLTRIM( STR(aRecord) )
NEXT i
? cRecords

USE customer
INDEX ON recno() TAG COD_CLI CUSTOM TO cust_rec
FOR EACH nRecord In aRecord
GOTO nRecord
OrdKeyAdd()
NEXT
// open browse to monitor
GO TOP
browse()

RETURN NIL