Table Of Contents 1.0 SPEED SEARCH. . . . . . . . . . . . . . . . . . . 2 1.1 OPENSS . . . . . . . . . . . . . . . . . . . 8 1.2 CGHLINE. . . . . . . . . . . . . . . . . . . 9 1.3 RECREATE . . . . . . . . . . . . . . . . . . 11 1.4 DELETIT. . . . . . . . . . . . . . . . . . . 12 1.5 SETLENGTH. . . . . . . . . . . . . . . . . . 13 1.6 SETFLENGTH . . . . . . . . . . . . . . . . . 14 1.7 INITSEEK . . . . . . . . . . . . . . . . . . 15 1.8 SPEEKSEEK. . . . . . . . . . . . . . . . . . 16 Page 2 1.0 SPEED SEARCH The speed search routines allow you to search on any word within a field. It is especially useful on large databases, since the '$' operator and at() function could be used but becomes unacceptably slow the larger the database becomes. Speed search allows multiple fields to be added to the index. So, for example, if you have 3 address fields and you want to search on any element in these fields, you can pass all three field names. An example of the speed search routine, working on a 'real' inventory database with 11,500 items. The database had a 30 character description field, which has on average 4-5 words. A search database containing 46,000 elements was created using space and the / character as delimiters. The combined speed search element databases and corresponding indexes took over 1MB of space. Performing a basic search using a 486SX-25 standalone machine took on average less than 1/2 seconds using the speed search, and over 10 seconds using the '$' operator. The example program FINDIT.PRG creates a database and corresponding search databases of a size specified by the user. The user can then test the speed search and perform basic database manipulation, such as deleting, adding and editing records. When the program is run for the first time, select the create option to create a new search database. Pros/Cons The pros of using the speed search routine is speed, which is important in any application. It has been of great use with retail applications where an item must be found quickly based on a partial description. The search routine will only find values based on the first X characters, so if what you're searching for is contained in the end of the string, then it won't find it. Page 3 The search files can become large, so if you're really tight on space, it may become an issue. Differences from version 1.00 Compressed indexes. The PNT indexes are converted to character strings, giving a increased level of compression. Field Delimiter The delimiter you specify can be important depending on what intend to search on. Example: If you have fields containing 386DX-25, 386DX-33, 486DX2-33 then you might want to use the characters " " and "-" as delimiters, then you could enter the CPU speed (ie. 25) and it would find it. The example program FINDIT.PRG included gives you a pretty good idea of the performance gains available. It is also a useful guideline for implementing the speed search routines into your own code. Page 4 The SCBFND library is copyright of the author, Stein Borge, 1995 and is released as Shareware. Program authors may incorporate it freely into their applications for testing and inspection. You may use SCBFND.LIB free for a 30 day evaluation period. SCBFND.LIB is Shareware, which means each user must register his or her copy for use beyond the evaluation period. Use of non-registered copies of SCBFND.LIB is prohibited. If it is used beyond the 30 day evaluation period a once only contribution of $US 15 Upon receipt of payment copies of the source code will be made available. The registered version performs record locking, which allows it to be used in a network environment. Since I currently don't have a permanent fixed address, please E-Mail me at my Compuserve account 76720,3454 for current address information, or any queries or questions you may have about the library. If your sending mail from the Internet address it to: 76720,3454@compuserve.com You are encouraged to make copies of SCBFND.LIB to give to your friends or to upload to your favourite BBS. These copies should be the original SCBFND.ZIP file. This library has been a great help to me and I hope that it will be to you, too. A great deal of time has gone into this library, and I am constantly trying to improve it. Please feel free to send comments on enhancements you would like to see in the library, or to report on any bugs or questions you may have about the library. And finally, the legal bit... Disclaimer The user of this library takes full responsibility for any loss or damaged data that might occur as a result of using this library. I have made every attempt to ensure that this library will respond as described. Stein Borge will take no responsibility if an error occurs and data is lost or data becomes damaged and the cause has been found to be the functions operations. Stein Borge Page 5 Technical Details The search database is created using up to the first five characters of the source database together with characters PNT. It also creates two indexes, using upto the first five characters and ending with PNT and SND. The PNT index contains record pointers pointing to the appropriate record in the source database, while the SND index contains the actual text. When the search database is opened, the SND becomes the primary index and the PNT index becomes secondary. Example: The following example would attempt to open the search database INVENPNT and the corresponding indexes INVENSND and INVENPNT, if it doesn't exist, it will create it using the desciptin and compatibil fields. openss("inventory"," /",{"descriptin","compatibil"}) The SNDEX field in the search database contains the text, which is the first X characters of a word converted to upper case. The X characters is by default 4 characters, but this can be changed. Page 7 When a record is deleted in the source database or a field within a record is modified to contain few words, then values are flagged as deleted in the search database. So when you search for values in the search database, make sure you ignore deleted ones. This can be done by simply setting SET DELETED ON. When the source database is PACKed then the search databases must be regenerated, since it points to specific record numbers in the source. Example: use test pack recreate("test",{" "},{"name"}) This shareware version does not perform record locking when updating using the CGHLINE() function. The registered version performs record locking. Page 8 1.1 OPENSS Description Opens speed search database. If source database is not open, it will open it. If search database does not exist, then it will create the .DBF files. If indexes do not exist for the search database it will to create them. Regenerating indexes on large databases can take some time. Syntax openss(cDbf,cDel,aFl) Arguments: cDbf This is the name of the source database. If not open, opens it. acDel Delimiters to create index on. This is an array of characters. So if you want to generate an index on the space character and the '/' character, pass {" ","/"} aFl Array of field names to generate index on. You can generate the speed search on a combination of more than one field. The field types must be of the character type Example openss("msd"," /",{"descriptin","compatibil"}) This will attempt to open the MSD files together with it's corresponding MSDSND.DBF files and a MSDSND and MSDPNT indexes. If the MSDSND.DBF file doesn't exist, it will recreate the pointer file and indexes using the descriptin and compatibil fields and "/" and space as delimiters. Related Functions Recreate(). Page 9 1.2 CGHLINE Description Updates speed search database and indexes for changes made in a record. The record in the source database that has been modified must remain at the record location before the function is called. Syntax Cghline(cDbf,aSepList,cFname) Arguments: cDbf This is the name of the source database. aSeplist Delimitiers to create index on. This is an array of characters. So if you want to generate an index on the space character and the '/' character, pass {" ","/"}. aFname Array of field names to generate index on. You can generate the speed search on a combination of more than one field. The field types must be of the character type. Example: The following example updates the search database using the current record in the source database TEST. It uses the fields search and search2 and uses the space character as the delimiter. @ 1,1 say "Edit field: " get search picture "@!" @ 2,1 say "Edit field 2: " get search2 picture "@!" read if lastkey()!=27 Cghline("test",{" "},{"search","search2"}) endif Page 10 Adding a record example append blank @ 1,1 say "Edit field: " get search picture "@!" @ 2,1 say "Edit field 2: " get search2 picture "@!" read Cghline("test",{" "},{"search","search2"}) Related Functions None. Page 11 1.3 RECREATE Description Recreates speed search database. If database does not exist, then it will try to create the .DBF files. If indexes do not exist it will try to create them. Regenerating indexes on large databases can take some time. Syntax Recreate(acDel,aFl,cDbf) Arguments: cDbf This is the name of the source database. If not open, opens it. acDel Delimitiers to create index on. This is an array of characters. So if you want to generate an index on the space character and the '/' character, pass {" ","/"} aFl Array of field names to generate index on. You can generate the speed search on a combination of more than one field. The field types must be of the character type Example Recreate("msd"," /",{"descriptin","compatibil"}) This with attempt to recreate the MSDSND.DBF files and a MSDSND and MSDPNT indexes . Related Functions None. Page 12 1.4 DELETIT Description Deletes records from speed search database for specified record in source database. It is best to delete record in source and then delete in speed search since speed search contains a relation to the source database. Syntax DeleteIt(cDbf,nRecno) Arguments: cDbf This is the name of the source database. If not open, opens it. nRecno This is the record number of the record that is to be deleted in the source database. Example // Get the current record number in source database nDr:=recno() // Delete record in source database delete // Delete record in search database DeleteIt(cSource,nDr) Related Functions None. Page 13 1.5 SETLENGTH Description Sets the length of the string to be parsed from the search database. The default is 4. This value must be set BEFORE the search databases are created. Syntax Setlength(nLength) Arguments: nLength Number of characters to use for SNDEX field in search database. Default is 4. Returns Current value. Example ? SetLength() // This would print the current value. SetLength(6) // Sets length to 6 characters /* This would create the search database for the MSD database using the first 6 characters from each field*/ recreate("msd"," /",{"descriptin","compatibil"}) Page 14 1.6 SETFLENGTH Description Sets the length of the POINTER field in the search database. The POINTER field contains a pointer to the record in the source database. The pointer field is encoded. The default is 2, which would allow up to 256^2, or 65536 records in the source database. If you new you were only going to have up to 256 records in the source database, this value could be changed to 1, saving space in the search database. If you change the value for an existing search database, the database must be recreated. This value should be set before the database is recreated. The maximum value would be four, which would give 256^4 or over four billion records. Syntax Setflength(nLen) Arguments: nLen Pointer field measured in number of digits. Allows 256^nLen records. Returns Current setting. Example ? Setflength() // This would print the current value. /* Sets pointer field to 3 characters. This would allow over 16 million records in the source database */ Setflength(3) Related Functions None. Page 15 1.7 INITSEEK Description Required before a speed search is started to initialize interal flags. Syntax InitSeek() Arguments: None. Returns Nothing Example Related Functions SpeedSeek() Page 16 1.8 SPEEDSEEK Description Required before a speed search is started to initialize interal flags. Syntax SpeedSeek(cDbf,cSeekValue) Arguments: cDbf This is the name of the source database. cSeekValue Character string to be searched for Returns True .T. everytime a matching value is found. Everytime SpeedSeek is called, it will attempt to find the next corresponding value. When it can no longer find a match, it returns False .F. Example The following would display the fields search and search2 for all occurences of FRED. InitSeek() // Must be called before SpeedSeek is performed.. do while SpeedSeek("FRED") ? search+" "+search2 enddo Related Functions InitSeek()