Working with data returned from a query

Using Verity, you can search data returned by a query--such as a database record set--as if it were a collection of documents stored on your web server. Using Verity to search makes implementing a search interface much easier, as well as letting users more easily find information contained in database files. A database can direct the indexing process, by using different values for the type attribute of the cfindex tag. There are also several reasons and procedures for indexing the results of database and other queries.

Recordsets and types of queries

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

Type	Attribute values
File	The `key` attribute is the name of a column in the query that contains a full filename (including path).
Path	The `key` attribute is the name of a column in the query that contains a directory pathname.
Custom	The `key` attribute specifies a column name that can contain anything you want. In this case, the `body` attribute is required, and is a comma-delimited list of the names of the columns that contain the text data that is to be indexed.

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 data returned by a query

Indexing the results of a query is similar to 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 record sets returned from a query:

Create a collection.
Write a query that retrieves the data you want to search, and generate a record set.
Index the record set using the cfindex tag.
The cfindex tag indexes the record set as if it were a collection of documents in a folder within your website.
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.

You should 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--you must 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, you must 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:

Attribute	Description
`key`	Primary key column of the data source table.
`title`	Specifies a query column name.
`body`	Columns that you want to search for the index.
`type`	If set to `custom`, this attribute specifies the columns that you want to index. If set to `file` or `path`, this is a column that contains either a directory path and filename, or a directory path that contains the documents to be indexed.

Using the cfindex tag to index tabular data is similar to indexing documents, with the exception that you refer to column names from the generated record set 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 record set 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 record set.

Index a ColdFusion query

Create a Verity collection for the data that you want to index.
The following example assumes that you have a Verity collection named CodeColl. You can use the ColdFusion Administrator to create the collection, or you can create the collection programmatically by using the cfcollection tag. For more information, see Creating a collection with the ColdFusion Administrator or Creating a collection with the cfcollection tag.

Create a ColdFusion page with the following content:

<html>
<head>
    <title>Adding Query Data to an Index</title>
</head>
<body>

<!--- Retrieve data from the table. --->
<cfquery name="getEmps" datasource="cfdocexamples">
    SELECT * FROM EMPLOYEE
</cfquery>

<!--- Update the collection with the above query results. --->
<cfindex 
    query="getEmps"
    collection="CodeColl"
    action="Update"
    type="Custom"
    key="Emp_ID"
    title="Emp_ID"
    body="Emp_ID,FirstName,LastName,Salary">

<h2>Indexing Complete</h2>

<!--- Output the record set. --->
<p>Your collection now includes the following items:</p>
<cfoutput query="getEmps">
    <p>#Emp_ID# #FirstName# #LastName# #Salary#</p>
</cfoutput>
</body>
</html>

Save the file as collection_db_index.cfm in the myapps directory under the web root directory.
Open the file in the web browser to index the collection.
The resulting record set appears.

Search and display the query results

Create a ColdFusion page with the following content:

<html>
<head>
    <title>Searching a collection</title>
</head>
<body>

<h2>Searching a collection</h2>

<form method="post" action="collection_db_results.cfm">
    <p>Collection name: <input type="text" name="collname" size="30" maxLength="30"></p>

    <p>Enter search term(s) in the box below. You can use AND, OR, NOT, 
    and parentheses. Surround an exact phrase with quotation marks.</p>
    <p><input type="text" name="criteria" size="50" maxLength="50">
    </p>
    <p><input type="submit" value="Search"></p>
</form>

</body>
</html>

Save the file as collection_db_search_form.cfm in the myapps directory under the web_root.
This file is similar to collection_search_form.cfm, except the form uses collection_db_results.cfm, which you create in the next step, as its action page.

Create another ColdFusion page with the following content:

<html>
<head>
<title>Search Results</title>
</head>

<body>

<cfsearch 
     collection="#Form.collname#"
     name="getEmps"
     criteria="#Form.Criteria#"
    maxrows = "100">

<!--- Output 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>
</body>
</html>

Save the file as collection_db_results.cfm in the myapps directory under the web_root.
View collection_db_search_form.cfm in the web browser and enter the name of the collection and search terms.

Indexing a file returned by using a query

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 were 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>

Indexing a path returned by using a query

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 were 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 were specified as a fixed path pointing to a collection of documents without the use of a query.

Index a directory path within a query

Create a ColdFusion page that contains the following content:

<cfquery name="getEmps" datasource="cfdocexamples">
SELECT * FROM EMPLOYEE WHERE Emp_ID = 15
</cfquery>
<!--- Update the collection with the above query results. --->
<!--- Key specifies a column that contains a directory path. --->
<cfindex 
    query="getEmps"
    collection="CodeColl"
    action="update"
    type="path"
    key="Project_Docs"
    title="Project_Docs"
    body="Emp_ID,FirstName,LastName,Project_Docs">

<h2>Indexing Complete</h2>
<p>Your collection now includes the following items:</p>
<cfoutput query="getEmps">
<p>#Emp_ID# #FirstName# #LastName# #Project_Docs#</p>
</cfoutput>

Save the file as indexdir.cfm in the myapps directory.

The ColdFusion cfindex tag indexes the contents of the specified directory path.

Search and display the directory path

Create a ColdFusion page that contains the following content:

<cfsearch 
    collection="#Form.collname#"
    name="getEmps"
    criteria="#Form.Criteria#"
    maxrows = "100">

<!--- Output the directory path 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>

Save the file as displaydir.cfm.

Indexing query results obtained from an LDAP directory

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 server's directory schema 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>

Indexing cfpop query results

The contents of mail servers are generally 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 must re-index the collection before processing a search.

As with the other query types, you must 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>

Adobe ColdFusion 8