 |
|
|
|
| |
Full Text Search Online Help
 | Introduction
|
| Setting Up
|
| Using FTS
|
| The Search Base Metadata
|
FTS is a tool for creating full text search indexing in an existing database. You can also populate, activate, search and synchronize the data.
At the current stage of development, it is necessary to have the tool store the Search Base tables inside your database, for synchronization to occur. The final product will offer the option to store the Search Base in a separate database, using a simple Replication utility for the auto-synchronization.
Start the program. If you are familiar with IB_WISQL, you will recognize the interface!
To begin, connect to the database of your choice:
Click the button labeled "Load FTS Base" at top right of the window to open the interface to the base table where the application stores information about the search indexes.
When new tab labelled "Load Indexes" appears, you can proceed to add search indexes, one at a time. You will notice that there are buttons for three steps in the process for adding a new search index to your database.
The demo table is called MEMBERS. Its primary key is a CHAR(15) called NAMECODE.
Start in the grid at the left by typing in a name for the search index.
Next, move over to the fields in the righthand panel and select the values for the other columns from the combo boxes.
The first field lets you select a TABLE.PRIMARYKEY pair. Locate and select the one you want from the dropdown list:
Below this field is the 'Search' field, where you define the actual text column to be indexed. The utility offers you a list of all char and varchar columns in the selected table which are more than 50 characters long and all text blob columns. Columns shorter than this would be searched more efficiently using SQL functions and existing indexes.
| ON BLOBS: For obvious reasons, for FTS to be capable of searching BLOB columns, they must be defined as SUB_TYPE 1 or use a blob filter that returns plain text. In the Browser, you can recognize suitable columns by the description 'Memo' in the Relations display. If the Index page is not showing your BLOB column as a candidate for a search index, yet you are certain it is a text blob, you will need to define a new BLOB SUB_TYPE 1 column and move your text data into it.
| |
The next 'Auxiliary' column is optional. It can be any column containing additional information that you want to define to help refine searches on the search column you are defining.
If the Max Search Length is larger than the length of the column in your metadata, alter it to fit your column. Adjust the Max and Min word lengths to suit your needs.
Click 'Load Metadata' to move to the next stage. The caption on the button changes to 'Drop Metadata'. Don't click this unless you want to start over!
Customizing your data for effective searching
Sometimes you will find it very useful to define a COMPUTED column in your table that compounds data for searches that are likely to be needed on two or more columns simultaneously.
For example, you might need to make a computed column FULLNAME which concatenates FIRSTNAME, MIDDLENAME and SURNAME. You can then set up a search index for it here.
Now, just go ahead and click each of the other buttons in turn to populate the database of search indexes and activate it.
Once activation completes, you will see this message pop up:
It doesn't mean you did anything wrong! Disconnection is necessary because it wasn't possible to unload the index without getting the 'Object in Use' error.
Once you have a search base activated according to the preceding step, go back to the 'Load Base' tab and reconnect. Four more tabs will appear:
The first one is where you can enter in search criteria. Type in a word or two and hit enter. If there are any matching data in any rows, they will appear:
LOG ENTRIES: Look at the next tab and see a log entry about the search you just did.
BLOB COLUMNS: Full Text Search handles BLOB columns provided they are defined as BLOB SUB_TYPE 1 or another plain text blob subtype returned by a blob filter.
The 'Thesaurus' is for entering synonyms (words of equivalent meaning) and antonyms (words of opposite meaning). The text-searching facility will use the Thesaurus for any of the search indexes you have defined.
Always enter one synonym and one antonym for every keyword (the 'Word' column). If you want the search to ignore the 'Synonym' or 'Antonym' entry for a word (e.g. the proper name 'Sarah' cannot logically have an antonym), simply copy the 'Word' entry into the column which you want it to ignore.
Post and commit as and when you are ready. You can maintain the Thesaurus at at any time.
You will get an error if you attempt to post more than one Thesaurus entry for a single word - ou can have one and only one synonym and antonym per word. However, you can use the same synonym or antonym for different words to store several possibilities for the same word.
On the last tab is the synchronization log.
Recall that our demo search index, SI_URL, was designed to provide a search base for the URL column in the MEMBERS table. To demonstrate the synchronization at work, we start up IB_WISQL and enter and commit some changes to data that will affect that search index:
Watching the Sync Events tab, we observe a log of the information being handled as the search indexes are kept current automatically:
If you click the Browse button on the 'Load Base' tab, you will notice that new tables have been added:
It isn't important to know about these structures but you can browse through the search base data and notice how the data from your database tables are dissected for various search features (Metaphone, Soundex, Thesaurus and so on).
You may also observe a degree of "bloat" in your database. At the current stage of development, this is necessary for synchronization to occur. The final product will offer the option to store the Search Base in a separate database, using a simple Replication utility for the auto-synchronization.
|
|
|
