BTREE.EXTRACT

The BTREE.EXTRACT subroutine will search one or more Btree indexes for data that matches the search criteria passed to BTREE.EXTRACT. It returns the keys of records having matching data.

BTREE.EXTRACT(srch_strng, file, dictvar, keys, option, flag)

Access to Advanced Revelation Btree indexes is normally handled transparently at the time a SELECT is generated or when you do a [Ctrl-F10] search from a window. You can also have the system do direct lookups into Btree indexes by using a CATALYST "B" call, but this requires user interaction. When you want more direct access, use BTREE.EXTRACT.

BTREE.EXTRACT can look into more than one index in one call and can also look up more than one data value. The system allows you to use the AND and OR logical operators to retrieve values from the Btree index. BTREE.EXTRACT only searches Advanced Revelation indexes. It will not search a foreign index via an Environmental Bond.

BTREE.EXTRACT also allows you to provide your own routines for preprocessing search data, as well as for your own search algorithm. See "User Index" following the sample code below.

You must open the associated dictionary file before calling BTREE.EXTRACT.

The basic unit of srch_strng is a search field. This field consists of the name of an indexed field together with search values to be located within that field.

search_field = indexed_field:@VM:data1:@VM:data2:@FM

A Btree index must have been applied to the specified record field or an error is generated. Within the search field, search values are delimited from the indexed field name and each other by a value mark (@VM). Within the search field, search values are located based on an OR relation: a record key is returned when any of the values is found within the specified field.

The implied OR relation for the @VM-delimited search values may be forced to an AND relation by prefixing any desired search value with "&" (ampersand). This situation presumes a multivalued field, because every AND relation means that two values must be found before a hit is registered. If you want to search by more than one criterion, include additional search fields in srch_strng. Each search field is delimited from another by a field mark (@FM), and multiple search fields imply an AND relation: conditions must be satisfied for all search fields before any key is returned. The AND relation may be overridden for individual search values by prefixing each with a semicolon (;). Srch_strng must end with a field mark.

The search values in srch_strng are substrings, and as such, you can modify the search relations according to Query substring search characters (see the chapter "Using Query" in Accessing Data of the main system documentation). For example,

srch_strng = "COMPANY_NAME":@VM:"#TRUST INSURANCE":@FM

will find all values of the indexed field COMPANY_NAME that are not "TRUST INSURANCE".

BTREE.EXTRACT supports a search operator in addition to those used with Query: between ("~"[CHAR(126)]). Between differs from range in that it is not inclusive. For example, the following search string will return record keys for all records having zip codes between, but not including, 98100 and 98111:

       srch_strng = "ZIP":@VM:"98100~98111":@FM

As noted above, multiple search criteria are linked with an implicit AND. The TCL-level statement WITH STATE = WA AND CITY = SEATTLE is performed in BTREE.EXTRACT by the code:

"STATE":@VM:"WA":@FM:"CITY":@VM:"SEATTLE":@FM

To change the implicit AND to an OR, insert a semicolon (;) before each search value in the second (and subsequent) search field(s). The statement WITH STATE = WA OR CITY = SEATTLE is performed in BTREE.EXTRACT by the code:

"STATE":@VM:"WA":@FM:"CITY":@VM:";SEATTLE":@FM

Use file to pass the name of the file to be searched.

Use dictvar to pass the file handle for the dictionary of the specified file.

Record keys for the records that satisfy the criteria in srch_strng are returned in keys. Multiple keys are delimited with value marks. The list of keys is limited to 64K and will be truncated if longer. If the list is truncated, an appropriate error code is returned in the flag argument.

Three values for option are possible:

Error codes are returned in flag.

After execution, keys contains a list of keys matching the search criteria.

Flag returns one of several possible values, depending on the success of the search process:

/* The following code opens a file, then prompts for the 
fields to search and for values to locate within those 
fields.  BTREE.EXTRACT is used to obtain record keys, which 
are displayed upon completion. */

DECLARE SUBROUTINE MSG, INPUT.CHAR, FSMSG, BTREE.EXTRACT
EQUATE false$ TO 0
EQUATE true$ TO 1

index  = "COMPANY_NAME"
error  = false$

file = "SAMPLE_CUSTOMERS"

OPEN "DICT.":file TO @DICT ELSE
       FSMSG()
       STOP
END

LOOP
    error = false$
    MSG("Enter name of field to search: ","RC",index,"")
    IF index THEN
        READ field_name FROM @DICT, index ELSE
            FSMSG()
            error = true$
        END
     END ELSE STOP
WHILE error REPEAT

value      = "Security Insurance"
value_text = "Enter values to search for, separated by 
commas|"

MSG(value_text,"RC",value,"")
IF value THEN
    CONVERT "," TO @VM IN value
    srch_strng = index:@VM:value:@FM
    BTREE.EXTRACT(srch_strng, file, @DICT, keys, option, 
flag)
    IF flag THEN
        IF flag > 0 THEN
            msg_no = 4
        END ELSE
         msg_no = 3
        END;* if flag > 0
    END ELSE
        IF keys THEN
             msg_no = 1
        END ELSE
             msg_no = 2
        END;* if keys
    END;* if flag

    messages    = ""
    messages<<1>> = "Successful search: keys returned 
are:|":keys
    messages<<2>> = "No matching keys were found."
    messages<<3>> = "Search failed."
    messages<<4>> = "Search was successful, but list > 64K."
    messages<<4,-1>> = "Returned keys are:|":keys

    MSG(messages<<msg_no>>,"","","")

END;* if value
STOP

When the Query comparison operators are not applicable to your task, you may specify your own search algorithm using the BTREE.EXTRACT user index facility.

The BTREE.EXTRACT user index facility consists of two parts: a parsing module and a compare module. The parsing module is a pre-process to BTREE.EXTRACT that determines whether the search value should cause BTREE.EXTRACT to call a custom comparison module. The compare module is called as an alternative to the comparison logic normally done by BTREE.EXTRACT.

Create a record in the VOC file called %USER.INDEX%. When BTREE.EXTRACT is run, Advanced Revelation looks for this record before BTREE.EXTRACT does any other processing. Be aware that when you create %USER.INDEX%, all system processing that accesses BTREE.EXTRACT will access this record and attempt to follow its specifications.

The %USER.INDEX% record must have two fields, with an optional third:

<<1>>USER.INDEX
<<2>>parsing_module
<<3>>default_compare

This literal text must be the first field in %USER.INDEX%.

In this field of the %USER.INDEX% record, enter the name of your own parsing routine. It must either be cataloged or be in the VERBS file.

The routine named in this field should examine the search data that has been passed to BTREE.EXTRACT to determine whether the custom comparison routine should be called. If you choose, this preprocessor can alter the search data. Parsing_module is passed four arguments by BTREE.EXTRACT:

parser(search_val, start_value, user_index_flag, compare_mod)

Search_val is the value of srch_strng, as passed to BTREE.EXTRACT. It may contain special characters or patterns that trigger the custom compare module. If a custom compare module is called later in the BTREE.EXTRACT process, the value of search_val as (possibly) modified by the parsing routine will be passed to that compare module to determine an index value hit or miss.

If srch_strng contains multiple search criteria, BTREE.EXTRACT parses and passes each one to the parsing module one at a time. All AND and OR logic for multiple search criteria is therefore handled by BTREE.EXTRACT. Parsing_module is invoked for each value.

Your parsing module can use a start_value argument to pass a value to BTREE.EXTRACT indicating the point in the index from which to start examining index values. If start_value is null (the default), the search will begin at one end or the other of the Btree index, depending on the direction of the search (determined in user_index_flag below). If a specific value is passed, BTREE.EXTRACT begins from the point in the index where that value would be found.

An argument for user_index_flag determines whether the custom compare module will be called to examine the search value.

The flag can have these values:

Parsing_module returns in compare_mod the name of the compare module that is to be called by BTREE.EXTRACT. If null (the default), the compare module used is that specified in default_compare (of the %USER.INDEX% record).

If user_index_flag is 1 or 2, but no comparison module is specified in either the VOC record or in compare_mod, a "null load" error will occur when BTREE.EXTRACT is called.

The compare module specified in this field contains custom logic you have developed to compare the search data against values in the Btree index. Based on its own comparison logic, the compare module determines whether the search data matches values in the index. BTREE.EXTRACT calls the compare module for each value in the Btree index, starting as specified in start_value.

The compare module (the value of compare below) is called by BTREE.EXTRACT with the following parameters:

compare(candidate, search_val, flag)

The argument for candidate is the value extracted from the Btree index by BTREE.EXTRACT. The candidate value is passed to the compare module and is used to compare against the search data.

A value for search_val has been returned by the parser. Values are compared against candidate by the custom compare routine.

If the value of flag is true upon entry, the value of candidate is the last value in the current Btree leaf node (flag is otherwise false). The compare module must set flag with one of the following values:

In addition to the arguments just described, the labeled COMMON area USERIX is available to the parsing and compare routines. There are two labeled common variables to provide additional information about the indexed values. The labeled block is defined as follows:

COMMON /USERIX/ UIX.SM.FLAG, UIX.DCONV

The variable uix.sm.flag is a boolean value, true if the field is left-justified sorted and false if the field is right- justified. The variable uix.dconv contains the output conversion to be applied to the value to change it to its external representation (data is stored in the index in its internal representation).

/*  The following code establishes three independent 
subroutines, each with a different purpose within the user 
index facility of BTREE.EXTRACT. */

SUBROUTINE PARSER(search_val, start_value, user_index_flag, 
comp_mod)
*  [The SUBROUTINE statement must be in line 1]

/* This code is an example of a user index parser routine to 
intercept Soundex lookups (it looks for values starting with 
"$") */

DECLARE SUBROUTINE SOUNDEX      ;*  code supplied below

EQUATE true$     TO 1
EQUATE trigger$  TO "$"
EQUATE comp_mod$ TO "COMPARE"

/* The following code examines the index lookup value and 
determines whether it is a Soundex lookup.  If not, the 
search value is passed through to BTREE.EXTRACT as normal.  
*/

IF search_val[1,1] = trigger THEN
   search_val[1,1] = ""            ;* delete the trigger 
character
   SOUNDEX(search_val)             ;* convert to Soundex 
value
   user_index_flag = true$         ;* use custom compare in 
ascending
   start_value = search_val[1,1]   ;* start searching at 
first letter
   comp_mod = comp_mod$            ;* specify the compare 
module
END
RETURN

* [The following code must be in a separate record]

SUBROUTINE COMPARE(candidate, search_val, flag)

DECLARE SUBROUTINE SOUNDEX

EQUATE hit_true$    TO 1
EQUATE hit_false$   TO 0
EQUATE quit_search$ TO 2

IF candidate[1,1] GT search_val[1,1] THEN
    flag = quit_search$           ;* end search if first 
char not same
  END ELSE
    SOUNDEX(candidate)            ;* convert CANDIDATE to 
Soundex
    IF search_val EQ candidate THEN
        flag = hit_true$
      END ELSE
        flag = hit_false$
    END
END
RETURN

* [The following code must be in a separate record]

SUBROUTINE SOUNDEX(soundex_value)

/* This is code for a subroutine that returns the Soundex 
equivalent of a single word.
   First, establish the numeric code equivalents for all 
letters of the alphabet--vowels, plus 'w', 'y', and 'h' are 
ignored. */

EQUATE sound.codes$ TO "01230120022455012623010202"
EQUATE punctuation$ TO ".,/`';][-
=<<>>?:~}{+_)(*&^%$#@!\|":CHAR(34)
EQUATE numerals$    TO "1234567890"
EQUATE null$        TO ""

/* be sure there is only one word, no punctuation and no 
lower case or numeric characters */

text = TRIM(soundex_value)
text = FIELD(text, " ", 1)
CONVERT punctuation$ TO null$ IN text
CONVERT numerals$ TO null$ IN text
CONVERT @LOWER.CASE TO @UPPER.CASE IN text

/* In accordance with the Soundex algorithm, start with the 
2nd character. */

first_char    = text[1,1]
text          = text[2,999]
text_length   = LEN(text)
soundex_value = first_char
previous_char = null$

FOR loop_count = 1 TO text_length WHILE LEN(soundex_value) < 
4

* strip off next character
       next_char = text[loop_count, 1]
       IF next_char NE previous_char THEN
           CONVERT @UPPER.CASE TO sound_codes$ IN next_char
           IF next_char NE 0 THEN
               soundex_value := next_char
               previous_char = next_char
           END
       END
NEXT loop_count

/* Format is four characters in length, zero-padded at right 
if necessary. */

soundex_value = FMT(soundex_value, "L(0)#4")

RETURN