Programming user-defined aggregates

The DISTINCT and ALL qualifiers eliminate or retain duplicates, but these qualifiers have no effect in a MIN expression. Only one DISTINCT aggregate expression Modified: db/derby/docs/trunk/src/ref/rrefsqlj33923.dita URL: http://svn.apache.org/viewvc/db/derby/docs/trunk/src/ref/rrefsqlj33923.dita?rev=1402539&r1=1402538&r2=1402539&view=diff ============================================================================== --- db/derby/docs/trunk/src/ref/rrefsqlj33923.dita (original) +++ db/derby/docs/trunk/src/ref/rrefsqlj33923.dita Fri Oct 26 15:05:08 2012 @@ -34,8 +34,21 @@ other built-in functions operate on a si on a set of values and reduce them to a single scalar value. Built-in aggregates can calculate the minimum, maximum, sum, count, and average of an expression over a set of values as well as count rows.

The built-in aggregates can operate on the data types shown in -the following table.

In addition to the built-in aggregates, + allows you to create +custom aggregate operators, called user-defined aggregates (UDAs). For +information on creating and removing UDAs, see + and +. See + and + for information on usage +privileges for UDAs.

For information on writing the Java classes that implement UDAs, see +"Programming user-defined aggregates" in the +.

The built-in aggregates can operate on expressions that evaluate to +the data types shown in the following table.

Permitted data types for built-in aggregatesThis table lists the data types that are valid for each of the built-in aggregates. @@ -50,23 +63,23 @@ the following table.

-COUNT +COUNT All types -MIN -Numeric built-in data types +MIN +Data types that can be indexed -MAX -Numeric built-in data types +MAX +Data types that can be indexed -AVG +AVG Numeric built-in data types -SUM +SUM Numeric built-in data types @@ -103,14 +116,6 @@ HAVING c2 > WHERE t2.y = SUM(t1.c3))

A cursor declared on a ResultSet that includes an aggregate in the outer query block is not updatable.

supports -the following aggregates:

Added: db/derby/docs/trunk/src/ref/rrefsqljcreateaggregate.dita URL: http://svn.apache.org/viewvc/db/derby/docs/trunk/src/ref/rrefsqljcreateaggregate.dita?rev=1402539&view=auto ============================================================================== --- db/derby/docs/trunk/src/ref/rrefsqljcreateaggregate.dita (added) +++ db/derby/docs/trunk/src/ref/rrefsqljcreateaggregate.dita Fri Oct 26 15:05:08 2012 @@ -0,0 +1,104 @@ + + + + + +CREATE DERBY AGGREGATE statement + +CREATE DERBY AGGREGATE statement +SQL statementsCREATE DERBY AGGREGATE +user-defined aggregatescreating +aggregatesuser-defined + + + +

The CREATE DERBY AGGREGATE statement creates a user-defined +aggregate (UDA). A UDA is a custom aggregate operator.

+Syntax +CREATE DERBY AGGREGATE [ schemaName. ] SQL92Identifier +FOR ValueDataType +[ RETURNS ReturnDataType ] +EXTERNAL NAME ClassNameString +

The aggregate name is composed of an optional schemaName and a +SQL92Identifier. If a schemaName is not provided, the current +schema is the default schema. If a qualified aggregate name is specified, the +schema name cannot begin with SYS.

In general, UDAs live in the same namespace as one-argument user-defined +functions (see +). +A schema-qualified UDA name may not be the schema-qualified name of a +one-argument user-defined function.

An unqualified UDA name may not be the name of an aggregate defined in +part 2 of the SQL Standard, section 10.9:

+ANY +AVG +COLLECT +COUNT +EVERY +FUSION +INTERSECTION +MAX +MIN +SOME +STDDEV_POP +STDDEV_SAMP +SUM +VAR_POP +VAR_SAMP +

In addition, an unqualified UDA name may not be the name of any of the + built-in functions +which take one argument.

The ValueDataType can be any valid nullable + data type except for +XML, including user-defined types.

The ReturnDataType can be any valid nullable + data type except for +XML. If the ReturnDataType is omitted, it defaults to be the same as +ValueDataType.

The ClassNameString is a single-quoted string. It is the full name of +a Java class which implements the org.apache.derby.agg.Aggregator +interface. That contract is not checked until a statement is compiled which +invokes the UDA.

The owner of the schema where the UDA lives automatically gains the USAGE +privilege on the UDA and can grant this privilege to other users and roles. Only +the database owner and +the owner of the UDA can grant these USAGE privileges. The USAGE privilege +cannot be revoked from the schema owner. See + and + for more +information.

+ +Examples +CREATE DERBY AGGREGATE mode FOR INT +EXTERNAL NAME 'com.example.myapp.aggs.Mode'; + +CREATE DERBY AGGREGATE types.maxPrice FOR PRICE +EXTERNAL NAME 'com.example.myapp.types.PriceMaxer'; + +CREATE DERBY AGGREGATE types.avgLength FOR VECTOR +RETURNS DOUBLE +EXTERNAL NAME 'com.example.myapp.types.VectorLength'; +

See "Programming user-defined aggregates" in the + for more details about +creating and using user-defined aggregates.

+ + + Propchange: db/derby/docs/trunk/src/ref/rrefsqljcreateaggregate.dita ------------------------------------------------------------------------------ svn:eol-style = native Added: db/derby/docs/trunk/src/ref/rrefsqljdropaggregate.dita URL: http://svn.apache.org/viewvc/db/derby/docs/trunk/src/ref/rrefsqljdropaggregate.dita?rev=1402539&view=auto ============================================================================== --- db/derby/docs/trunk/src/ref/rrefsqljdropaggregate.dita (added) +++ db/derby/docs/trunk/src/ref/rrefsqljdropaggregate.dita Fri Oct 26 15:05:08 2012 @@ -0,0 +1,57 @@ + + + + + +DROP DERBY AGGREGATE statement + +DROP DERBY AGGREGATE statement +SQL statementsDROP DERBY AGGREGATE +user-defined aggregatesdropping +aggregatesuser-defined + + + +

The DROP DERBY AGGREGATE statement removes a user-defined aggregate +(UDA) that was created using a +.

+Syntax +DROP DERBY AGGREGATE [ schemaName. ] SQL92Identifier RESTRICT +

The RESTRICT keyword is required. CASCADE semantics are not supported. That +is, will not track down +and drop orphaned objects.

Dropping a UDA implicitly drops all USAGE privileges that reference it. See + and + for more +information.

raises an error if a +trigger or view references the UDA.

+ +Example +DROP DERBY AGGREGATE mode RESTRICT; + + + Propchange: db/derby/docs/trunk/src/ref/rrefsqljdropaggregate.dita ------------------------------------------------------------------------------ svn:eol-style = native Modified: db/derby/docs/trunk/src/ref/rrefsqljgrant.dita URL: http://svn.apache.org/viewvc/db/derby/docs/trunk/src/ref/rrefsqljgrant.dita?rev=1402539&r1=1402538&r2=1402539&view=diff ============================================================================== --- db/derby/docs/trunk/src/ref/rrefsqljgrant.dita (original) +++ db/derby/docs/trunk/src/ref/rrefsqljgrant.dita Fri Oct 26 15:05:08 2012 @@ -40,7 +40,8 @@ from a table.

Create a trigger on a table.

Update data in a table or in a subset of columns in a table.

Run a specified function or procedure.

Use a sequence generator or a user-defined type.

Use a sequence generator, a user-defined type, or a user-defined +aggregate.

Before you issue a GRANT statement, check that the derby.database.sqlAuthorization property is set to true. The derby.database.sqlAuthorization property @@ -85,6 +86,18 @@ information.

SQL92Identifier. If a schemaName is not provided, the current schema is the default schema. If a qualified type name is specified, the schema name cannot begin with SYS.

Syntax for user-defined aggregates +GRANT USAGE ON DERBY AGGREGATE [ schemaName. ] SQL92Identifier TO grantees +

In order to use a user-defined aggregate, you must have the USAGE privilege +on it. This privilege can be granted to users and to roles. See + for +more information.

Syntax for roles GRANT roleName [ {, roleName }* ] TO price to the role finance_role, use the following syntax:
GRANT USAGE ON TYPE price TO finance_role; +
To grant the USAGE privilege on the user-defined aggregate +types.maxPrice to the role sales_role, use the +following syntax:
+GRANT USAGE ON DERBY AGGREGATE types.maxPrice TO sales_role; - Modified: db/derby/docs/trunk/src/ref/rrefsqljrevoke.dita URL: http://svn.apache.org/viewvc/db/derby/docs/trunk/src/ref/rrefsqljrevoke.dita?rev=1402539&r1=1402538&r2=1402539&view=diff ============================================================================== --- db/derby/docs/trunk/src/ref/rrefsqljrevoke.dita (original) +++ db/derby/docs/trunk/src/ref/rrefsqljrevoke.dita Fri Oct 26 15:05:08 2012 @@ -39,7 +39,8 @@ from a table.
Create a trigger on a table.

Update data in a table or in a subset of columns in a table.

Run a specified routine (function or procedure).
-
Use a sequence generator or a user-defined type.
+
Use a sequence generator, a user-defined type, or a user-defined +aggregate.

The derby.database.sqlAuthorization property must be set to true before you can use the GRANT @@ -95,6 +96,21 @@ information.
SQL92Identifier. If a schemaName is not provided, the current schema is the default schema. If a qualified type name is specified, the schema name cannot begin with SYS.

+
Syntax for user-defined aggregates +REVOKE USAGE ON DERBY AGGREGATE [ schemaName. ] SQL92Identifier FROM grantees RESTRICT +
In order to use a user-defined aggregate, you must have the USAGE privilege +on it. This privilege can be revoked from users and roles. Only RESTRICTed +revokes are allowed. This means that the REVOKE statement cannot make a view or +trigger unusable by its owner. The USAGE privilege cannot be revoked from the +schema owner. See + for +more information.
+
The aggregate name is composed of an optional schemaName and a +SQL92Identifier. If a schemaName is not provided, the current +schema is the default schema. If a qualified aggregate name is specified, the +schema name cannot begin with SYS.

Syntax for roles REVOKE roleName [ {, roleName }* ] FROM price from the role finance_role, use the following syntax:
REVOKE USAGE ON TYPE price FROM finance_role; +
To revoke the USAGE privilege on the user-defined aggregate +types.maxPrice from the role sales_role, use +the following syntax:
+REVOKE USAGE ON DERBY AGGREGATE types.maxPrice FROM sales_role; Modified: db/derby/docs/trunk/src/tools/ctoolsgenddldb.dita URL: http://svn.apache.org/viewvc/db/derby/docs/trunk/src/tools/ctoolsgenddldb.dita?rev=1402539&r1=1402538&r2=1402539&view=diff ============================================================================== --- db/derby/docs/trunk/src/tools/ctoolsgenddldb.dita (original) +++ db/derby/docs/trunk/src/tools/ctoolsgenddldb.dita Fri Oct 26 15:05:08 2012 @@ -34,6 +34,9 @@ generating the DDL for a database:
Triggers

Tables

Views
+
Roles
+
User-defined types
+
User-defined aggregates
When dblook runs against a database that has jar files installed, it will create a new directory, called DERBYJARS, within