Indexing Query Results

The following sections describe the reasons and procedures for indexing the results of database, LDAP, and pop queries.

Indexing database query results

The main advantage of performing searches against a Verity collection over using cfquery alone is that the database is indexed in a form that provides faster access. Use this technique instead of cfquery in the following cases:

You want to index textual data. You can search Verity collections containing textual data much more efficiently with cfindex than by searching a database with cfquery.
You want to give your users access to data without interacting directly with the data source itself.
You want to improve the speed of queries.
You want your end users to run queries but not update database tables.

Indexing the result set from a ColdFusion query involves an extra step not required when you index documents. You must code the query and output parameters, and then point the cfindex tag at the result set from a cfquery, cfldap, or cfpop query.

To index a ColdFusion query:

Create a collection on the ColdFusion Administrator Verity Collections page.
Execute a query and output the data.
Populate the collection using the cfindex tag.

To populate a collection from a cfquery you specify a key attribute, which corresponds to the primary key of the data source table, and a body attribute, the column or columns that you want to search for the index. The following extract shows only the cfquery and cfindex parts of the process.

<!--- Select the entire table --->

<cfquery name="Messages" 

  datasource="MyMail">

  SELECT * 

    FROM Messages

</cfquery>



<!--- Output the result set --->

<cfoutput query="Messages">

  #Message_ID#, #Subject#, #Title#, #MessageText#



</cfoutput>



<!--- Index the result set --->

<cfindex collection="DBIndex"

  action="Update"

  type="Custom"

  body="MessageText"

  key="Message_ID"

  title="Subject"

  query="Messages">

This cfindex statement specifies the MessageText column as the information to be indexed and names the table's primary key, the Message_ID column, as the key value. Note that the title attribute names the Subject column. You can use the title attribute to designate an output parameter.

To index more than one column in a collection, enter a comma-separated list of column names for values of the body attribute, such as:

body=FirstName,LastName,Company

Indexing cfldap query results

The widespread use of the Lightweight Directory Access Protocol to build searchable directory structures, both internally and across the Web, gives you opportunities to add value to the sites you create. You can index contact information or other data from an LDAP-accessible server and allow users to search it.

When creating an index from an LDAP query, remember the following considerations:

Because LDAP structures vary greatly, you must know the server's directory schema and the exact name of every LDAP attribute you intend to use in a query.
The records on an LDAP server can be subject to frequent change. You might want to re-index the collection before processing a search request.

In the example below, the search criterion is records with a telephone number in the 617 area code. Generally, LDAP servers use the Distinguished Name (dn) attribute as the unique identifier for each record so that is used as the key value for the index.

<!--- Run the LDAP query --->

<cfldap name="OrgList"

  server="myserver"

  action="query"

  attributes="o, telephonenumber, dn, mail"

  scope="onelevel"

  filter="(|(O=a*) (O=b*))"

  sort="o"

  start="c=US">



<!--- Output query result set --->

<cfoutput query="OrgList">

  DN: #dn# <br>

  O: #o# <br>

  TELEPHONENUMBER: #telephonenumber# <br>

  MAIL: #mail# <br>

=============================<br>

</cfoutput>



<!--- Index the result set --->



<cfindex action="update"

  collection="ldap_query"

  key="dn"

  type="custom"

  title="o"

  query="OrgList"

  body="telephonenumber">



<!--- Search the collection --->

<!--- Use the wildcard * to contain the search string --->

<cfsearch collection="ldap_query"

  name="s_ldap"

  criteria="*617*">



<!--- Output returned records --->

<cfoutput query="s_ldap">

  #Key#, #Title#, #Body# <br>

</cfoutput>

Indexing cfpop query results

The contents of mail servers are generally quite volatile; specifically, the message number is reset as messages are added and deleted. To avoid mismatches between the unique message number identifiers on the server and in the Verity collection, you should re-index the collection before processing a search.

As with the other query types, you need to provide a unique value for the key attribute and enter the data fields to index in the body attribute.

The following example updates the pop_query collection with the current mail for user1 and searches and returns the message number and subject line for all messages containing the word "action":

<!--- Run POP query --->

<cfpop action="getall"

  name="p_messages"

  server="mail.company.com"

  userName="user1"

  password="user1">



<!--- Output POP query result set --->

<cfoutput query="p_messages">

  #messagenumber# <br>

  #from# <br>

  #to# <br>

  #subject# <br>

  #body# <br>

<hr>

</cfoutput>



<!--- Index result set --->

<cfindex action="update"

  collection="pop_query"

  key="messagenumber"

  type="custom"

  title="subject"

  query="p_messages"

  body="body">



<!--- Search messages for the word "action" --->

<cfsearch collection="pop_query"

  name="s_messages"

  criteria="action">

  

<!--- Output search result set --->

<cfoutput query="s_messages">

  #key#, #title# <br>

</cfoutput>

Developing ColdFusion Applications
Indexing and Searching Data