CML and Full-Text Search

A requirement of the CML application is a searchable library database in which all users can easily and quickly locate library titles of interest. Adding full-text search capabilities allows users to narrow and focus searches. The users are no longer overwhelmed by the task of looking through excess material, and the CML application performance is better when excess records are no longer retrieved and sent across the network.

Implementing full-text search enables the F & M employees to search library materials using the asterisk (*) wildcard and complex search criteria. Searches are constructed using the text values and operators the user enters on the Library Search Web page. The searches can contain AND, OR, and NEAR operators. The Library Search Web page applies full-text search to the fname and lname columns of the author table, the text column of the subject table, and the title, ISBN, and notes columns of the title table. The Catalog Filter Web page uses full-text search on the title column of the title table.

The CML application uses the CONTAINS predicate in searches on library materials. On the Library Search Web page the borrowers have three search options: author, subject, and title. The Catalog Filter Web page searches the title table. When designing the database tables that have columns to be registered for full-text search, those columns must have character-based data types. Adding a row-versioning (timestamp) column to a table allows for efficient incremental population of the full-text index. The Author Table, Subject Table, and Title Table topics describe timestamp columns. When a table is registered for full-text search, but has no timestamp column, the full-text catalog is completely repopulated when the catalog is refreshed. This can be a time-consuming process. When the schema changes or the table is activated since the last population a complete repopulation is performed, regardless of the presence of a row-versioning column. The following topics contain further information on full-text search in the CML application.

CML Full-Text Tables and Columns

The first column of the following table lists the tables registered for full-text search in the CML SQL Server 7.0 database. The second column lists the fields registered for full-text search in the named table.

Table name Column name Data type
author table fname

lname

 varchar

varchar
subject table text varchar
title table title

ISBN

notes

 varchar

varchar

text

Getting the CML Database Ready for Full-Text Search

Take the following steps to prepare the CML database for full-text search:

  1. Enable the database for full-text search. Use the SQL Server Enterprise Manager to perform this task. Select the database you want to enable for full-text search, click Full-Text Indexing on the Tools menu, and complete the Full-Text Indexing Wizard.
  2. Create a full-text catalog for the database. When first built, metadata about a full-text catalog in the CML database system tables is created and an empty full-text catalog is built in the file system. Typically, all the full-text index data for a database is placed in a single full-text catalog, but you have the flexibility to partition the full-text data for a single database into multiple full-text catalogs. The CML database has one full-text search catalog named FmLib. Full-text indexes for the CML database tables registered for full-text search reside in the FmLib catalog.
  3. Register tables and columns for full-text search. Keep in mind that only one full-text index is allowed per table, though you can register as many columns as you wish for full-text search. The only requirement is that columns for full-text search must contain character data such as char, varchar, or text. In the CML, the columns registered for full-text search have varchar or text data types. Also, the table must have a primary key. All CML database tables have primary keys.
  4. Populate the FmLib full-text indexes. The entire FmLib full-text catalog is populated at once. Since the three tables (author, subject, and title) are linked to the FmLib full-text catalog, populating the catalog will in fact populate the three full-text indexes.
  5. Keep full-text indexes up to date. Full-text indexes are not automatically updated when the values in a full-text registered column are added, deleted, or updated, but require periodic repopulation to be up-to-date. The refresh can be a scheduled or an on-demand event. The three CML database tables, author, subject, and title, registered for full-text search, have timestamp (row-versioning) columns enabling an incremental population of indexes. CML and Full-Text Search describes the benefits of timestamps.