Special Cloudscape Programming
Page 5 of 7

Programming User-Defined Aggregates

Cloudscape allows you to create user-defined aggregates. User-defined aggregates allow you to gather more sophisticated information than is available from MAX, MIN, SUM, and the other built-in aggregates. If you can do it in Java, you can do it in Cloudscape.

On the language side, you issue a CREATE AGGREGATE statement that is in essence an alias for the Java class that provides the logic for performing the aggregate.

If you provide the correct implementation, a user-defined aggregate can return any built-in type or any user-defined Java data type.

This section discusses how to create classes that perform aggregation for Cloudscape use.

Developing the Java Classes that Perform the Aggregation

To support Cloudscape aggregation, you must provide a Java class or classes that implements two interfaces:

  • COM.cloudscape.aggregates.AggregateDefinition

    For each valid input type, specifies the return type of the aggregate and the name of the class that will perform the aggregation (which implements COM.cloudscape.aggregates.Aggregator). This information is needed at compile time.

    The name of this class is typically the same as the name you give the SQL-J aggregate with the CREATE AGGREGATE statement. For example:

    CREATE AGGREGATE MAXBUTONE FOR JBMSTours.aggregates.MaxButOneDef

    You specify the aggregator by filling in the StringBuffer passed in to the getAggregator method.

    You specify the return type in the same method by returning an object of type COM.cloudscape.types.TypeDescriptor, which is a Cloudscape object for describing types. For aggregates that simply return the input type, return the TypeDescriptor that was passed in as a parameter.

    To return a TypeDescriptor representing any other Java type, construct a TypeDescriptor like this:

    COM.cloudscape.database.Factory.getTypeFactory().getTypeDescriptor(javaClassName)

    For example, if the aggregate returned a vector, the getAggregator method would return:

    COM.cloudscape.database.Factory.getTypeFactory().getTypeDescriptor("java.util.Vector")

    To return a TypeDescriptor representing any other built-in type, construct a TypeDescriptor like this:

    COM.cloudscape.database.Factory.getTypeFactory().getTypeDescriptor(JDBCType)

    For example, if the aggregate returned an INTEGER the getAggregator method would return:

    COM.cloudscape.database.Factory.getTypeFactory().getTypeDescriptor(java.sql.Types.INTEGER)

    For Java type, do not use java.sql.Types.OTHER; instead, specify the class name as a string.

    For more information about the COM.cloudscape.aggregates.AggregateDefinition interface, see the javadoc.

  • COM.cloudscape.aggregates.Aggregator

    The class that implements this interface performs the aggregation. This class is used at execution time. It is instantiated at run time and is passed in each value that is to be aggregated. The class is responsible for calculating the aggregate based on the input (which it does with the accumulate method). When Cloudscape has finished processing all rows, it calls the getResult method to retrieve the final result.

    This interface requires five methods. The interesting ones are:

    • accumulate(ResultSet). Iteratively accumulates the addend into the aggregator. For an aggregator called CountMe that returns a simple int count, this method would simply add one to the count each time it was called. (Cloudscape will call the method once for each input row).
    • getResult(). Produces the result to be returned by the query. For an aggregator called CountMe that returns a simple int count, this method would return the accumulated count.
    • merge(Aggregator). Merges one aggregator into another aggregator. For an aggregator called CountMe that returns a simple int count, this method would aggregate (in this case, add) the count of the passed-in aggregator with the count of this. (This method is called instead of the accumulate method when the aggregate is used in a GROUP BY.)

      Vector aggregates, aggregates that are used in a GROUP BY statement, require sorting. Aggregators must be serializable in case the sort is too large to keep in memory.

      For more information about COM.cloudscape.aggregates.Aggregator, see the javadoc.

The sample application JBMSTours comes with a few examples of aggregate classes (in the /demo/programs/tours/JBMSTours/aggregates directory), which may help you get started programming your own aggregates:

  • JBMSTours.aggregates.MaxButOneDef

    Implements AggregateDefinition. A very simple example of an aggregate that returns the "second highest" value in a column (like MAX, only it returns the value just below MAX). This class specifies that the aggregate accepts only values of type INTEGER and DOUBLE PRECISION; that it returns the same type as the input type; and that for values of type INTEGER, the aggregator is JBMSTours.aggregates.MaxButOneIntegerAggregator, and for values of type DOUBLE PRECISION, the aggregator is JBMSTours.aggregates.MaxButOneDoubleAggregator.

    This is the only class for which an aggregate database object has been created in toursDB (with the CREATE AGGREGATE statement).

  • JBMSTours.aggregates.MaxButOneDoubleAggregator

    Implements COM.cloudscape.aggregates.Aggregator. The aggregator for JBMSTours.aggregates.MaxButOneDef when the input type is DOUBLE PRECISION.

  • JBMSTours.Aggregates.MaxButOneIntegerAggregator

    Implements COM.cloudscape.aggregates.Aggregator. The aggregator for JBMSTours.aggregates.MaxButOneDef when the input type is INTEGER.

  • JBMSTours.aggregates.MaxButOneJDK12

    A JDK1.2-only class that is similar to JBMSTours.aggregates.MaxButOneDef, except that this class implements both AggregateDefinition and Aggregator. The JDK 1.2's improvements to java.lang.Object make it much easier to compare values among the standard data types, so this class accepts input values of all the built-in data types. This class also implements Aggregator and so performs the aggregation itself as well as the aggregate definition.

  • JBMSTours.aggregates.StandardDeviation

    Implements both AggregateDefinition and Aggregator. Uses the VisualNumerics.math.Statistics package to implement a poor man's standard deviation. Accepts only values of type DOUBLE PRECISION.

NOTE: You can create an aggregate for this class if you have the VisualNumerics.math.Statistics package available from http://www.vni.com.