When indexing recordsets generated from a query (using the cfquery, cfldap, or cfpop tag), cfindex creates indexes based on the type attribute and its set value:

The cfindex tag treats all collections the same, whether they originate from a database recordset, or if they are a collection of documents stored within your website’s root folder.

Indexing the results of a query is like indexing physical files located on your website, with the added step that you must write a query that retrieves the data to search. The following are the steps to perform a Verity search on recordsets returned from a query:

1. Create a collection.

2. Write a query that retrieves the data you want to search, and generate a recordset.

3. Index the recordset using the cfindex tag.

   The cfindex tag indexes the recordset as if it is a collection of documents in a folder within your website.

4. Search the collection.

   The information returned from the collection includes the database key and other selected columns. You can then use the information as-is, or use the key value to retrieve the entire row from the database table.

Use Verity to search databases in the following cases:

• You want to perform full-text search on database data. You can search Verity collections that contain textual data much more efficiently with a Verity search than using SQL to search database tables.

• 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 users to be able to execute queries, but not update database tables.

Unlike indexing documents stored on your web server, indexing information contained in a database requires an additional step— first write a query (using the cfquery, cfldap, or cfpop tag) that retrieves the data you want to let your users search. You then pass the information retrieved by the query to a cfindex tag, which indexes the data.

When indexing data with the cfindex tag, specify which column of the query represents the filename, which column represents the document title, and which column (or columns) represents the document’s body (the information that you want to make searchable).

When indexing a recordset retrieved from a database, the cfindex tag uses the following attributes that correspond to the data source:

Using the cfindex tag to index tabular data is like indexing documents, with the exception that you refer to column names from the generated recordset in the body attribute. In the following example, the type attribute is set to custom, specifying that the cfindex tag index the contents of the recordset columns Emp_ID, FirstName, LastName, and Salary, which are identified using the body attribute. The Emp_ID column is listed as the key attribute, making it the primary key for the recordset.

You can index an individual file that uses a query by retrieving a table row whose contents are a filename. In this case, the key specifies the column that contains the complete filename. The file is indexed using the cfindex tag as if it is a document under the web server root folder.

In the following example, the cfindex tag’s type attribute has been set to file, and the specified key is the name of the column that contains the full path to the file and the filename.

<cfquery name="getEmps" datasource="cfdocexamples"> 
SELECT * FROM EMPLOYEE WHERE EMP_ID = 1 
</cfquery> 
<cfindex  
    query="getEmps" 
    collection="CodeColl" 
    action="Update" 
    type="file" 
    key="Contract_File"  
    title="Contract_File" 
    body="Emp_ID,FirstName,LastName,Contract_File">

Search and display the file

 Create a ColdFusion page that contains the following content:

<!--- Output the record set.---> 
<p>Your collection now includes the following items:</p> 
<cfoutput query="getEmps"> 
<p>#Emp_ID# #FirstName# #LastName# #Contract_File#</p> 
</cfoutput> 
<cfsearch  
    collection="#Form.collname#" 
    name="getEmps" 
    criteria="#Form.Criteria#" 
    maxrows = "100"> 
 
<!--- Output the filename contained in the record set. ---> 
<cfoutput> 
Your search returned #getEmps.RecordCount# file(s). 
</cfoutput> 
<cfoutput query="getEmps"> 
<p><table> 
    <tr><td>Title: </td><td>#Title#</td></tr> 
    <tr><td>Score: </td><td>#Score#</td></tr> 
    <tr><td>Key: </td><td>#Key#</td></tr> 
    <tr><td>Summary: </td><td>#Summary#</td></tr> 
    <tr><td>Custom 1:</td><td>#Custom1#</td></tr> 
    <tr><td>Column list: </td><td>#ColumnList#</td></tr> 
</table></p> 
</cfoutput>

You can index a directory path to a document (or collection of documents) using a query by retrieving a row whose contents are a full directory path name. In this case, the key specifies the column that contains the complete directory path. Documents located in the directory path are indexed using the cfindex tag as if they are under the web server root folder.

In this example, the type attribute is set to path, and the key attribute is assigned the column name Project_Docs. The Project_Docs column contains directory paths, which Verity indexes as if they are specified as a fixed path pointing to a collection of documents without the use of a query.

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

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

• Because LDAP structures vary greatly, you must know the directory schema of the server and the exact name of every LDAP attribute that you intend to use in a query.

• The records on an LDAP server can be subject to frequent change.

In the following example, 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 attribute 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 record set. ---> 
<cfoutput query="OrgList"> 
    DN: #dn# <br> 
    O: #o# <br> 
    TELEPHONENUMBER: #telephonenumber# <br> 
    MAIL: #mail# <br> 
=============================<br> 
</cfoutput> 
 
<!--- Index the record 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*" 
    maxrows = "100"> 
 
<!--- Output returned records. ---> 
<cfoutput query="s_ldap"> 
    #Key#, #Title#, #Body# <br> 
</cfoutput>

The contents of mail servers are 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, reindex the collection before processing a search.

As with the other query types, 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 that contain the word action:

<!--- Run POP query. ---> 
<cfpop action="getall" 
    name="p_messages" 
    server="mail.company.com" 
    userName="user1" 
    password="user1"> 
 
<!--- Output POP query record set. ---> 
<cfoutput query="p_messages"> 
    #messagenumber# <br> 
    #from# <br> 
    #to# <br> 
    #subject# <br> 
    #body# <br> 
<hr> 
</cfoutput> 
 
<!--- Index record set. ---> 
<cfindex action="refresh" 
    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" 
    maxrows = "100"> 
     
<!--- Output search record set. ---> 
<cfoutput query="s_messages"> 
    #key#, #title# <br> 
</cfoutput>