|
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
DBFIND on an FTS index supports the following wildcard characters
anywhere in a 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.
|
