.
contact contact


Database Enhancements

 
.
  The Eloquence DBFIND, DBGET and DBINFO statements were enhanced to support new modes, mostly related to FTS indexing.

Please note that this documentation covers the Eloquence programming language specifically and may not apply to other means of accessing the Eloquence database.


Introduction

Eloquence Full Text Search ("FTS") indexes enable applications to efficiently lookup data by arbitrary words or combination of words in indexed fields. It's like adding a search engine to the application with some database configuration.

Eloquence FTS indexes are different from index items. When using index items the item value (or parts of it) is added to an index and allows sorted retrieval or efficient lookup by partial item values. Eloquence full text indexing extracts words from configured fields and allows to search for any or a combination of words.

FTS fields may be grouped, a word would match regardless of which fields it orinally came from. A typical example would be a customer address with name, street and city fields. When placed in the same group, a partial name and/or city would identify a customer. FTS fields in related sets (connected by a common master set) may be aggregated so a word occurring in any of the sets would qualify all related entries. An example would be an order that could be identified by any information in the order, the order details or some associated notes.

While the familiar DBFIND and DBGET calls are used to use the Eloquence FTS indexing there are some important differences. Only one FTS index context is maintained per database open. Subsequent DBFIND calls may be used to refine previous results. As usual, DBGET mode 5 and 6 may be used to obtain the results of the most recent DBFIND. In addition DBGET modes 26 and 27 may be used to obtain the results of a previous FTS DBFIND even if another DBFIND was executed.


DBFIND

DBFIND supports new modes related to FTS search.
DBFIND(base name, data set, mode, status, item, argument)
An FTS index field may be specified by the FTS field name or item number.

DBFIND modes 12 to 15 perform a keyword search. An FTS item name or number must be specified for the item parameter. The FTS item name is the item name if no separate name was defined. The FTS item number may be obtained with DBINFO.

DBFIND modes 20 and 21 undo the last FTS search, and restores the previously qualified list of records. An FTS item name or number must be specified for the item parameter.

DBFIND mode 1 may also be used to perform a keyword search if an FTS item is specified (similar to DBFNID mode 12). However, with DBFIND mode 1 the item argument may ambiguous. It may refer to a search item or an index item in addition to an FTS item. To avoid introducing behaviour changes an FTS search is only performed with DBFIND mode 1 if the item is not ambiguous. Oftherwise, use a keyword search mode (12 to 15) or define a unique name for the FTS field.

DBFIND mode 12 to 15 - keyword search

DBFIND mode 12 to 15 perform an FTS search and optionally return the number of records that qualified. To perform an FTS search that qualifies records based on the contents of several keys, perform successive DBFINDs.

The item argument must specify an FTS field. Either the FTS field name or number must be passed or the name of an item for which an FTS index is defined.

The DBFIND mode specifies the exact behavior:

  • DBFIND mode 12 returns the qualifying number of records.

  • DBFIND mode 13 does not return the qualifying number of records. This is faster when searching aggregated FTS keys on a detail set. Instead, the number of FTS search results is returned (which may be different to the number of detail records for aggregated keys).

  • DBFIND mode 14 returns the qualifying number of records (same as mode 12). However, if no records qualify, the previous FTS result is retained (implicit undo).

  • DBFIND mode 15 does not return the qualifying number of records (same as mode 13). However, if no records qualify, the previous FTS result is retained (implicit undo).

DBFIND mode 20, 21 - FTS UNDO

DBFIND mode 20 undoes the last FTS search, and restores the previously qualified list of records. The number of qualifying records is returned.

DBFIND mode 21 undoes the last FTS search but does not return the number of qualifying records.

FTS syntax

DBFIND modes 12 to 15 qualify records by keywords. The search argument consists of keywords (or ranges), separated by operators. A leading operator applies previous FTS search results.
[op] word [[op] word...]
  • word is a keyword or a range. Words are separated by operators. Double quotes may be used to designate a literal word.

  • op indicates an operator
The keywords are searched in the FTS dictionary index and operators define the relationship of the keywords. For example:
mike AND wuppertal
This consists of the keywords mike and wuppertal. As the boolean AND operator is used only records qualify having both mike and wuppertal.

Keywords and operators are case insensitive (with the exception of keywords designated specifically to be case sensitive).

The example below demonstrates use of the FTS DBFIND modes:

! search for computer and software
Arg$="COMPUTER AND SOFTWARE"
DBFIND(Db$,Set$,12,S(*),Item$,Arg$)

alternatively,

! first search for computer then refine results by searching
! for software
Arg$="COMPUTER"
DBFIND(Db$,Set$,12,S(*),Item$,Arg$)
Arg$="AND SOFTWARE"
DBFIND(Db$,Set$,12,S(*),Item$,Arg$)

Ranges

A keyword range may be specified by entering a minimum value, the range operator (:) and a maximum value.
[startvalue]:[stopvalue]
If both the start and stop values are present, any keywords between (and including) the specified values qualify. If either the start or stop value is not present then a range specifies an open range qualifying any value from the start or up to the stop value. For example,
a:z
2011:2012
2011:
:2012
The first example qualifies all keywords between "a" and "z", the second example qualifies any keywords between 2011 and 2012. The third example qualifies any keyword larger than or equal to 2011. The fourth example qualifies any keyword smaller than or equal to 2012.

Ranges on text fields can be performed using partial words as well. The range operation A@:C@ would qualify all records with keywords between "a" and (including) "c".

Relational operators

Relational operators are an alternate means for specifying ranges.

=  Equal to (redundant)
>=  Greater than or equal
>  Greater than
<=  Less than or equal
<  Less than

A relational operator is specified with the keyword (no separating space allowed). For example, >=A@ has the same meaning as A@: indicating an open range starting with A.

Two relational terms may also combined to specify a range with both a lower an upper boundary. For example, >=A@<=B@ is equivalent to A@:B@. Please note that no space is allowed between the two relational terms.

Pattern matching

odxfind supports the following wildcard characters anywhere in a character keyword argument:
  • ? - represents any single printable character
  • # - represents any single digit (0-9) of an ASCII number
  • @ - represents any number of ASCII characters, including spaces

Boolean operators

The boolean operators AND, OR and NOT may be used to define keyword relationships. For example,
mike AND NOT wuppertal
NOT test
would only qualify records with "mike" but not wuppertal. The second example would qualify any records NOT having the keyword test. The Boolean operations are shown below.
AND
An AND operator between search values qualifies records that contain all of those values. The argument COMPUTER AND SOFTWARE qualifies records with both "COMPUTER" and "SOFTWARE" in the search field.

An AND operation is implied when two values are separated by a single space. For example, the argument COMPUTER SOFTWARE is equivalent to COMPUTER AND SOFTWARE.

In successive calls to DBFIND, an AND operator at the beginning of an argument list intersects records that satisfy the argument with any previously qualified records.

OR
An OR operator between search values qualifies a union of records that contain either value. The argument COMPUTER OR SOFTWARE qualifies records with either "COMPUTER" or "SOFTWARE" in the search field.

In successive calls to DBFIND, an OR operator at the beginning of an argument adds records that satisfy the argument to any previously qualified records.

NOT
A NOT operator between search arguments qualifies records that contain the first argument, but not the second. COMPUTER NOT SOFTWARE or COMPUTER AND NOT SOFTWARE qualifies records with "COMPUTER" in the search field, but excludes those with "SOFTWARE" in the search field.

A NOT operator at the beginning of an argument qualifies records that do not satisfy the argument supplied. For example, NOT SOFTWARE qualifies records without SOFTWARE in the search field.

A NOT operator between search arguments qualifies records that contain the first argument, but not the second. COMPUTER NOT SOFTWARE or COMPUTER AND NOT SOFTWARE qualify records with "COMPUTER" in the search field, but excludes those with "SOFTWARE" in that field.

()
Parentheses are used to nest Boolean expressions to override the precedence of operations (NOT, AND, OR).

Soundex phonetic searches

To specify a phonetic, or Soundex, search on a FTS field with the SX option append the Soundex operator (!) to the keyword(s) in question, for example ALAN! AND ANDERSON!.

Quoting

Double quotes may be used to remove a special or confflicting meaning or words. For example, the word OR would be understood as the operator OR while "OR" is the word OR. "ABC@" searches for the literal word "ABC@" rather than the partial word "ABC".

Aggregated FTS fields

When performing FTS searches on aggregated FTS fields, remember that entire chains qualify, not individual detail records. The qualifying count returned reflects the combined number of details in the chains that qualified.

Progressively qualifying detail records using aggregated FTS fields can produce misleading results. Because aggregated FTS fields qualify master records (chain heads) and therefore entire detail chains, it is possible to qualify chains where no single record contains the keywords specified for the keys searched.

Aggregated FTS fields cannot qualify individual detail records.

When no records qualify

There no records are qualified a status 17 is returned. Even if all keywords exist, no record may qualify based on the boolean conditions. For example, no entries qualify if two keyword arguments in an AND operation exist but do not apply to the same record (or same master record for an aggregated FTS field).

Using DBFIND modes 14 or 15 previous search results are retained so a search my be re-tried by the user.

Undo previous search

After every call to DBFIND mode 12 to 15 (except those that qualify no records) the currently qualified and previously qualified results are saved in memory.

  • The DBFIND search result just performed
  • The DBFIND search results that immediately preceded the search just performed
This allows to undo the most recent DBFIND keyword search. For example, if 100 customers initially qualified and the result was subsequently refined to customers in Wuppertal it is possible to undo the last search and re-instate the previous result reflecting 100 customers. One may then retry the search with different arguments.

Note that you can only back out of the last search performed.


DBGET

DBGET mode 5 or 6 may be used to obtain records qualified by a FTS search (DBFIND mode 12 to 15). In addition DBGET also supports modes specific to FTS that add additional functionality.

The first DBGET mode 21, 22, 23, 25 or 26 after a FTS DBFIND (mode 12 to 15) defines the "retrieval" data set, which could be different from the last data set a DBFIND was used on. DBGET supports using database chains to apply FTS search results to a related master or detail set.

DBGET mode 21, 22 and 23 affect the position in the FTS search results. No data is read.

DBGET mode 25 and 26 obtain the next or previous FTS result (similar to DBGET mode 5 or 6). However, DBGET mode 25 and 26 are specific to FTS searches and are not affected by subsequent DBFINDs that are not FTS specific.

DBGET mode 21 - reset result pointer
DBGET mode 21 resets pointer to the beginning of the list of records qualified by keyword search (DBFIND mode 12 to 15).

DBGET mode 22 - move forward
DBGET mode 22 moves pointer forward n entries in the list of records qualified by a keyword search, without actually retrieving a record. n is passed in argument.

DBGET mode 23 - move backward
DBGET mode 23 moves pointer backward n entries in the list of records qualified by a keyword search, without actually retrieving a record. n is passed in argument.

DBGET mode 25 - retrieve next record
DBGET mode 25 retrieves the next record from those qualified by a FTS search.

DBGET mode 26 - retrieve previous record
DBGET mode 26 retrieves the previous record buffer from those qualified by an FTS search.

DBINFO

DBINFO supports new modes related to obtaining information on FTS fields.

DBINFO mode 114 - Field attributes of items in data set

Mode 114 obtains field attributes of items in a specific data set.
Qualifier  identifies the data set name or number for which the
           information is requested.

Buffer     returns the following (note that each element is an 
           integer or two bytes):

--------------------------------------------------------------
|   Element    |              Contents                       |
--------------------------------------------------------------
|      1       |  Item count x                               |
--------------------------------------------------------------
|      2       |  Field attributes for first item            |
--------------------------------------------------------------
|      :       |  :                                          |
--------------------------------------------------------------
|    n + 1     |  Field attributes for nth item              |
--------------------------------------------------------------
DBINFO mode 114 is similar to DBINFO mode 104 but returns field specific status information rather than item numbers.

The returned field attributes is a bit encoded (per item) integer as indicated below:

Bit 0 (0x1) - encrypted field
Bit 0 is set if the field is stored on disk in encrypted format.

Bit 1 (0x2) - encrypted content not available
Bit 1 is set if an encryption key for the database is not available. If this affects actual record, the field is blanked (if a string item) or zeroed when read.

Bit 2 (0x4) - item mask is defined
Bit 2 is set if an item mask exists for this item, even if it does not apply for the current user.

Bit 3 (0x8) - item content is masked
Bit 3 is set if an item mask affects information in this field (eg. information is truncated or blanked).

Bit 0 and and bit 2 may be used by an application to understand a field has sensitive information, so it should be handled with extra care (eg. not included in application logs).

Bit 1 and bit 3 may be used to indicate the field content is not available or only partially returned.


DBINFO mode 511 - FTS field number for a given FTS field and data set

Mode 511 obtains the FTS field number for a given FTS field and data set. The FTS field number uniquely identifies an FTS index.
Qualifier

--------------------------------------------------------------
|   Element    |              Contents                       |
--------------------------------------------------------------
|    1-8       |  data set name or number                    |
|              |  When to be ignored, first                  |
|              |  character must be a blank or a ";"         |
--------------------------------------------------------------
|    9-16      |  FTS field name or number or Item name      |
--------------------------------------------------------------

Buffer     returns the following (note that each element is an
           integer or two bytes):

--------------------------------------------------------------
|   Element    |              Contents                       |
--------------------------------------------------------------
|      1       |  FTS field number                           |
--------------------------------------------------------------
FTS fields are data set specific. Consequently, a data set must be specified unless an FTS field number is passed (this uniquely identifies the FTS field) or a unique FTS field name (only used in a single set).

The qualifier of DBINFO mode 501 is 16 Elements or 32 bytes. It specifies both the FTS field and the data set. The data set is optional and a space or semicolon may be used as the data set name.

A status -21 indicates the data set or FTS field is invalid or does not exist for the given data set. If the set is not specified the FTS field name or Item name or number may not specify a unique FTS field.


DBINFO mode 512 - FTS field details for a given FTS field and data set

Mode 512 obtains the details for the given FTS field. The qualifier is the same as for mode 511.
Qualifier

--------------------------------------------------------------
|   Element    |              Contents                       |
--------------------------------------------------------------
|    1-8       |  data set name or number                    |
|              |  When to be ignored, first                  |
|              |  character must be a blank or a ";"         |
--------------------------------------------------------------
|    9-16      |  FTS field name or number or Item name      |
--------------------------------------------------------------

Buffer     returns the following (note that each element is an
           integer or two bytes):

--------------------------------------------------------------
|   Element    |              Contents                       |
--------------------------------------------------------------
|    1-8       |  FTS field name (or item name)              |
--------------------------------------------------------------
|      9       |  FTS field options (bit encoded)            |
--------------------------------------------------------------
|     10       |  FTS field group (0 if no field group)      |
--------------------------------------------------------------
|     11       |  Max. keyword length                        |
--------------------------------------------------------------
|     12       |  Min. keyword length                        |
--------------------------------------------------------------
|     13       |  Exclusion list ID (0 = default)            |
--------------------------------------------------------------
|     14       |  Parser config ID (0 = default)             |
--------------------------------------------------------------
|     15       |  Reserved                                   |
--------------------------------------------------------------
|     16       |  Number of field segments                   |
--------------------------------------------------------------
|     17       |  Reserved                                   |
--------------------------------------------------------------
|     18       |  Item number of 1st segment                 |
--------------------------------------------------------------
|     19       |  Offset into item (in bytes)                |
--------------------------------------------------------------
|     20       |  Number of bytes                            |
--------------------------------------------------------------
|     21       |  Reserved                                   |
--------------------------------------------------------------
|     ...      |  Same as element 18-21 for each additional  |
|              |  segment                                    |
--------------------------------------------------------------
DBINFO mode 512 obtains the FTS field details.

The FTS field options is a bit encoded value:

Bit 0 (0x01) - no parse
Bit 0 is set if the option NP is specified, indicating the field is not parsed.

Bit 1 (0x02) - no translate
Bit 1 is set if the option NT is specified, indicating the field is not translated (converted to upper case).

Bit 2 (0x04) - no exclude
Bit 2 is set if the option NE is specified, indicating words in this field are not excluded.

Bit 3 (0x08) - no multiple keywords
Bit 3 is set if the option NM is specified, indicating words in this field are recognized as multiple keywords.

Bit 4 (0x10) - soundex
Bit 4 is set if the option SX is specified, indicating words in this field are indexed with the SOUNDEX value in addition.

Bit 5 (0x20) - aggregated field
Bit 5 is set if the FTS field is defined as an aggregated field in a detail set.


DBINFO mode 513 - FTS fields in data base

DBINFO mode 513 obtains the FTS fields in the data base.
Qualifier  is ignored.

Buffer     returns the following (note that each element is an 
           integer or two bytes):

--------------------------------------------------------------
|   Element    |              Contents                       |
--------------------------------------------------------------
|      1       |  Number of FTS fields                       |
--------------------------------------------------------------
|      2       |  1st FTS field number                       |
--------------------------------------------------------------
|      :       |  :                                          |
--------------------------------------------------------------
|    n + 1     |  nth FTS field number                       |
--------------------------------------------------------------

DBINFO mode 514 - FTS fields in data set

DBINFO mode 514 obtains the FTS fields in the specified dataset.
Qualifier  data set name or number

Buffer     returns the following (note that each element is an 
           integer or two bytes):

--------------------------------------------------------------
|   Element    |              Contents                       |
--------------------------------------------------------------
|      1       |  Number of FTS fields                       |
--------------------------------------------------------------
|      2       |  1st FTS field number                       |
--------------------------------------------------------------
|      :       |  :                                          |
--------------------------------------------------------------
|    n + 1     |  nth FTS field number                       |
--------------------------------------------------------------
A status -21 indicates the data set is invalid or inaccessible.


DBINFO mode 515 - Data sets with FTS field

DBINFO mode 515 obtains data sets (and access) that have the specified FTS field.
Qualifier  FTS field name or number or Item name or number

Buffer     returns the following (note that each element is an
           integer or two bytes):

--------------------------------------------------------------
|   Element    |              Contents                       |
--------------------------------------------------------------
|      1       |  Set count n                                |
--------------------------------------------------------------
|      2       |  +/- Data set number 1                      |
--------------------------------------------------------------
|      :       |  :                                          |
--------------------------------------------------------------
|    n + 1     |  +/- Data set number n                      |
--------------------------------------------------------------

           If the data set number is positive, the session has 
	   only read access to the data set.  If the number is 
	   negative, the session has both read and write access.
	   The data sets are listed in data set number order.
A status -21 indicates the specified FTS field does not exist or does not exist in an accessible dataset.


DBINFO mode 521 - FTS fields and grouping in data set

DBINFO mode 521 obtains a list of FTS fields and group number for a given data set. This is similar to mode 514 but in addition returns information on linked fields and the FTS field group number.
Qualifier  data set name or number

Buffer     returns the following (note that each element is an 
           integer or two bytes):

--------------------------------------------------------------
|   Element    |              Contents                       |
--------------------------------------------------------------
|      1       |  Data set number                            |
--------------------------------------------------------------
|      2       |  Master set number (or 0 if not linked)     |
--------------------------------------------------------------
|      3       |  Detail item linking to master set          |
--------------------------------------------------------------
|      4       |  Reserved                                   |
--------------------------------------------------------------
|      5       |  Number of FTS fields for data set          |
--------------------------------------------------------------
|      6       |  Item number of 1st FTS field               |
--------------------------------------------------------------
|      7       |  FTS field group of 1st FTS field           |
--------------------------------------------------------------
|     ...      |  Same as element 6-7 for each additional    |
|              |  segment                                    |
--------------------------------------------------------------
The FTS field group is zero if the field is not in a field group.

Please note that the master set and detail search item returned apply to at least one FTS field but may not apply to all of them.

A status -21 indicates the specified data set does not exist, is inaccessible or does not have an FTS field.


DBINFO mode 522 - Data sets in the FTS set group

DBINFO mode 522 obtains the list of data sets in the FTS set group.
Qualifier  data set name or number

Buffer     returns the following (note that each element is an 
           integer or two bytes):

--------------------------------------------------------------
|   Element    |              Contents                       |
--------------------------------------------------------------
|      1       |  Number of datasets                         |
--------------------------------------------------------------
|      2       |  1st data set number                        |
--------------------------------------------------------------
|      :       |  :                                          |
--------------------------------------------------------------
|    n + 1     |  nth data set number                        |
--------------------------------------------------------------
Please note that a set is considered part of a set group if at lest one FTS field is linked. This may not apply to all FTS fields.

A status -21 indicates the data set is invalid or inaccessible.


DBINFO mode 523 - FTS set groups in the database

DBINFO mode 523 obtains a list of FTS set groups in the database. This includes all master sets that has linked FTS fields or any set that without any linked FTS fields.
Qualifier  is ignored.

Buffer     returns the following (note that each element is an 
           integer or two bytes):

--------------------------------------------------------------
|   Element    |              Contents                       |
--------------------------------------------------------------
|      1       |  Number of datasets                         |
--------------------------------------------------------------
|      2       |  1st data set number                        |
--------------------------------------------------------------
|      :       |  :                                          |
--------------------------------------------------------------
|    n + 1     |  nth data set number                        |
--------------------------------------------------------------
Please note that a set is considered part of a set group if at lest one FTS field is linked. This may not apply to all FTS fields.


QFIND statement

The QFIND statement may be used to search FTS indexes with the "matches" clause. For example:
QFIND "CITY","MATCHES","wup@ or berlin"
Where "CITY" identifies a FTS field. QFIND will locate all records where the city has words starting with "wup@" or the word berlin.


 
 
.
 
 
  Privacy | Webmaster | Terms of use | Impressum Revision:  2012-11-09  
  Copyright © 2012 Marxmeier Software AG