The Amazing English Query Tool

Developers who are proficient with SQL are accustomed to posing questions to SQL Server in its native tongue. However, SQL statements are arcane, and you can't expect your Web site's visitors to be able to create complex query statements to extract the information they're looking for. You can try to anticipate visitors' requests and add parameters to a catalog of queries, but writing all the possible SQL statements for how a visitor might extract information is a monumental chore. For example, Listing 1 shows an SQL query to retrieve stage information for a shooting match held on a particular date. Table 1 shows the results for January 16, 1999.

Imagine a fairy-tale world in which developers didn't need to create such queries. In this world, your Web site's visitors can use a simple English phrase to ask for any information in the database, and the Web server magically dishes out the correct results. To receive the results in Table 1, a user can type the following English query statement:

Show the stages for 1/16/1999.

You can use Microsoft English Query 7.0 to make that fantasy a reality. English Query uses knowledge of the English language with information you provide about the structure of the underlying data to convert a simple English phrase into an SQL statement.

Although English Query offers the functionality of a standalone product, you can find this tool in the Mseq directory on the SQL Server 7.0 CD-ROM. However, English Query isn't new to SQL Server; SQL Server 6.5 Enterprise Edition includes English Query 1.0. If you'd like to explore English Query before you install this tool, Books Online (BOL) contains information about English Query, a tutorial, and descriptions of two Web samples.

Installation

Installing English Query is simple and takes only a moment. The tool requires 12MB of hard disk space on your development system. The Setup program installs four components: Core, which is the query engine; Developer, which is the domain editor; Help; and Samples. After installation, I suggest you walk through the Help file's tutorial, which uses the pubs database, to learn the concepts and terminology of English Query.

Development

Creating English Query applications is a straightforward pvocess. When you create an English Query application, you're creating a cross-reference that lets the English Query engine translate between an English phrase and the SQL statement that extracts the correct information from a particular database. The English Query engine can handle plural word forms, sentence structure, and other facets of the English language, but it needs you to specify the relationships between the entities, synonyms for entities, and an entity's type (e.g., whether an entity represents a time, place, or person). This process can be tedious for databases with several entities and relationships, so English Query provides tools to minimize this tedium: Autotrait, Autoname Import Structure, and a wizard that walks you through application creation.

The English Query domain editor is the tool you use to import the structure of your database, identify your domain's major and minor entities, create relationships between those entities, test sample visitor queries, and build the application for deployment. Screen 1 shows the English Query domain editor during the early stages of developing an application.

The domain editor is also the tool you use to define an entity's properties. Screen 2 shows the properties of an entity called stage. In this example, when a visitor uses the word stage, SQL Query editor will display three fields. You can also define an entity's synonyms; in this example, I defined the phrase stage info as equivalent to the word stage.

After you define all the entities and their relationships, English Query compiles the information you provide into a domain knowledge file with the extension .eqd. The runtime engine, which provides an object-based interface to English Query, then uses this file. The English Query engine is a COM component, so you can use it from virtually any Microsoft application. (Although you might be tempted to apply the COM English Query engine in applications that use databases other than SQL Server, ignore this temptation. The SQL commands that English Query generates take advantage of features unique to SQL Server.)

The domain editor lets you test questions against the application you're building. In some cases, English Query interactively refines users' questions. For example, English Query will ask visitors for clarification if they pose the following query:

Show me all shooters in Washington.

In this case, English Query doesn't know whether the user wants information about the city or the state.

Testing your application reveals questions that English Query can't interactively refine and requires you to modify the application. Listing 2 shows a basic English Query object. You begin using English Query by instantiating an instance of the component. Next, the code tells English Query to load your definition file. Now you can use the ParseRequest method to submit questions to English Query.

English Query's Help file contains an object model that shows the relationship among the steps of the runtime engine. (Figure 1 shows how BOL illustrates this model.) You can use this model to visualize the following events. Submitting a question to English Query will generate one of several possible results. The result might be a command that answers the question, or the result might be a SQL statement that you must run against the database to provide the answer. Another possible outcome is that English Query might need clarification about the question. A third possible outcome is that English Query doesn't understand the question. This result might mean the developer didn't provide English Query enough information during development or that someone asked a nonsense question. Track questions that English Query can't answer so that you can modify your domain model to fix the problem.

After you're satisfied that your English Query application is working, you're ready to deploy it to your Web server. To begin deployment, copy the .eqd file to the Web server directory that contains the Active Server Pages (ASP) for your application. Be sure to set read and execute or read and script privileges on this directory.

English Query Tips

English Query works best with normalized databases; however, your circumstances might mandate a structure that is not fully normalized. In this case, you can use views to solve problems that non-normalized databases cause. The English Query domain editor doesn't automatically import views. To add a view as an entity, select Table from the Insert menu and enter the name of the view. The English Query Help file provides examples of how to use views with non-normalized data.

Another tip is to define a primary key for each table in your English Query application. English Query requires primary keys to perform joins between tables to satisfy user requests. If you haven't defined keys in your database, you will need to define them in the domain editor. English Query can't build your application correctly without primary keys.

When you develop English Query applications, remember that case is significant. For example, English Query knows that you are asking about a proper noun because you capitalize the words in the query. Finally, if you're running Internet Information Server (IIS) 4.0 with the Windows Scripting Host (WSH), the fastest way to build and deploy a test application is to run the setupasp.vbs macro from the C:programfilesmicrosoftenglishquery samplesasp2. This macro automatically installs and configures your data, so you can start testing immediately.

The effort you put into an English Query application is worthwhile. Visitors to your Web site will be pleasantly surprised at the ease with which they find information.