DataBlade Developers Kit User's Guide

DataBlade Developers Kit User's Guide
Chapter 2: Designing DataBlade Modules

Home Contents Index Master Index New Book

Query Language Interface

The next component in DataBlade module design is the query language interface. Because Informix Dynamic Server is an object-relational database server, you access it by formulating queries in SQL. DataBlade modules extend SQL by defining new types and new routines that are available to queries. Consider the syntax that users must master to use a DataBlade module.

SQL Query Structure

SQL includes Data Definition Language (DDL) statements and Data Manipulation Language (DML) statements.

DDL statements, such as CREATE, ALTER, and DROP, modify the schema of a database. DML statements, such as SELECT, INSERT, UPDATE, and DELETE, manipulate data in tables.

Most SQL queries use DML statements. When you design a DataBlade module, consider DML statements in the abstract. DML statements are in the following forms:

select something from some table 

	where some conditions are satisfied

update some table set something 

	where some conditions are satisfied

The italicized components above serve different purposes in the DML query. The some table part is called "the from list" and is not important to consider when you design a DataBlade module. The something part is called "the target list" and identifies the columns for retrieval or update. The target list is the target on which the query is operating. The some conditions are satisfied part of the query is called "a qualification" because it identifies the rows that qualify to participate in the operation.

When you develop a DataBlade module, consider where you expect a particular routine to be used. In the following two sections, the DataBlade module routines that typically appear in the target list and qualification are addressed.

The Target List

The target list is where simple computation occurs. Consider providing DataBlade module routines for common computations on opaque data types. You can perform the computation in the DataBlade module to eliminate the need to implement the routine in the client application. You can use DataBlade module routines in the target list to reduce the amount of data transferred from the server to the client and thereby improve performance.

Consider the following sample query from the SimpleMap DataBlade module discussed earlier in this chapter:

create table cities (name text, population integer, 
	boundary Polygon);

Polygon is a data type supplied by the SimpleMap DataBlade module.

To retrieve a list of all cities, their populations, and population densities, you can submit the following query:

select name, population, population / Area(boundary) 
	as density from cities;

In this example, the Area() routine is supplied by the SimpleMap DataBlade module. Area() returns a floating-point number that is the area of the supplied polygon. You can invoke the built-in division operator to compute density from population and area. This query does a simple computation in the target list, using a mixture of DataBlade module and built-in routines.

This computation can also be done on the client. However, the client must implement the Area() routine for polygons, and the server must ship all of the polygons to the client. This operation is more expensive than shipping the results of the division across the network because polygons can be quite large. Generally, any computation that appears in the target list can also be done by the client. Thus, place target-list routines in the DataBlade module server routines only if there is an advantage to be gained by doing so.

If there is no advantage to running the routine on the server, leave the routine out of the DataBlade module and allow the client application developers to implement it in the client. If the server routine provides any of the following advantages, include it in the DataBlade module:

It reduces the volume of data transferred to the client.
It simplifies application development by sharing code among clients more effectively.
It benefits from Informix Dynamic Server parallelism and scalability.

A simple DataBlade module that integrates well with existing data types is always better than a complicated one with many predefined routines that cannot be used in conjunction with built-in or other DataBlade module routines.

The division operator that appears in the query calls a division routine built into Informix Dynamic Server. Built-in routines and DataBlade module routines can be combined in queries, as shown in the previous example using division with Area(). Routines from different DataBlade modules can be mixed to provide additional services.

When you design a data model, consider using built-in types and types provided by other DataBlade modules. In the example above, you might define a new data type, called AreaType, to represent the area of geographic objects. However, then you must implement all the math on AreaType values yourself. By using real numbers to represent areas, you can leverage existing math and computational support in the database server and allow users to mix SimpleMap DataBlade module routines with other routines.

You might define a routine that computes population density inside the SimpleMap DataBlade module. The routine takes two parameters-a polygon and an integer-and does the division itself. However, no real semantic power is derived from this design. Leave special-purpose routines out of the DataBlade module to keep the interface simple and to let developers define their own expressions or routines to compute specific values.

The Qualification

The SQL qualification restricts the set of rows returned to the user. The qualification filters out records that are not interesting. Only the records that pass the qualification are evaluated in the target list. Thus, a qualification is a more powerful tool than the target list.

A single expression in a qualification is called a predicate. A qualification can contain multiple predicates joined by the Boolean operators AND and OR.

If a DataBlade module routine is used in a qualification, it filters the records returned to the client. Informix Dynamic Server can filter by the contents of new data types. (This capability is not available in conventional relational databases.)

Consider whether the routines you define are more likely to be used in the target list or the qualification. Routines more commonly used in the qualification make better use of Informix Dynamic Server extensibility because they support searches that cannot be done efficiently on conventional relational servers.

The following is an example of a DataBlade module routine used in a qualification:

select name, boundary from cities where 
	Overlaps(boundary, '(1,1), (5,5), (7,7), (8,9)');

In this example, the Overlaps() routine is provided by the SimpleMap DataBlade module and takes two polygon arguments: the first argument specifies the polygon you are checking; the second specifies the polygon with which the first is compared. Overlaps() returns TRUE if the two polygons overlap and FALSE otherwise. This query searches the cities table for those cities that overlap the region of interest.

The separation between routines used in the target list and those used in the qualification is not absolute. For example, the following query finds the names and populations of large cities:

select name, population from cities 
	where Area(boundary) > 500;

In this example, the Area() routine appears in the qualification. In the section "The Target List", the Area() routine appeared in the target list.

Some routines are better suited to the qualification than the target list. A good example of this distinction is the Overlaps() routine. This routine is more powerful in the qualification. While it is possible to formulate a query like the following example, it is not very common:

select Overlaps(boundary, '(1,1), (5,5), (7,7), (8,9)') 

	from cities;

This query returns a list of yes-or-no answers for each city in the table that overlaps the supplied constant polygon. It is more common to use the Overlaps() routine to filter rows than to compute values returned to the user. However, important and useful exceptions to this rule exist, for example:

select a.name, Overlaps(a.boundary, b.boundary) 
	from cities a, cities b
	where b.name = 'Los Angeles' and
	a.name = b.name;

This query returns a list of all cities in the table and whether they overlap Los Angeles.

To help decide which routines to include in the DataBlade module, consider the following questions:

What questions do users want to ask about the contents of the new data types in the DataBlade module?
What routines allow them to ask those content-based questions? These routines are used in the qualification.

DataBlade Developers Kit User's GuideChapter 2: Designing DataBlade Modules Home Contents Index Master Index New Book

Query Language Interface

SQL Query Structure

The Target List

The Qualification

DataBlade Developers Kit User's Guide
Chapter 2: Designing DataBlade Modules

Home Contents Index Master Index New Book