[
https://issues.apache.org/jira/browse/DERBY-5466?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14639469#comment-14639469
]
Rick Hillegas commented on DERBY-5466:
--------------------------------------
Hi Scott,
Thanks for this contribution. It seems to me that there are two approaches to
adding these aggregators to Derby:
1) Simple - They could be added as an optional tool. See the documentation on
optional tools in the Derby Tools Guide. Under this approach, you would write a
small class which implements org.apache.derby.iapi.sql.dictionary.OptionalTool.
That interface has entry points for loading and unloading an optional tool. In
your loadTool() method, you would issue your CREATE DERBY AGGREGATE statements
and GRANT USAGE privilege on them to PUBLIC. Your unloadTool() method would do
the reverse: It would issue DROP DERBY AGGREGATE statements. Your actual
aggregate code would then go into the derbyoptionaltools.jar file. Under this
approach, I would recommend putting the code in a new directory,
java/optional/org/apache/derby/optional/agg, and the corresponding package
would be org.apache.derby.optional.agg.
2) Complicated - Alternatively, you could add your aggregates as builtin Derby
aggregates. Under this approach, your code would go into the engine jar file:
derby.jar. You would put your execution code in the
org.apache.derby.impl.sql.execute package next to the existing builtin
aggregators like AvgAggregator. This approach would require providing compiler
support for the new aggregators: parser support, bind-time (resolution)
support, and code-generation support.
>From the user's perspective, these are the key differences between the two
>approaches:
A) Startup - Under the simple approach, the user would have to load the new
optional tool as part of configuring the application. Typically, this would be
done when the application creates/upgrades its schema. E.g., the user would add
something like this to the application's configuration DDL script:
call syscs_util.syscs_register_tool( 'standardStatisticsAggregates', true )
In contrast, under the complicated approach, the new aggregators would be
available all of the time and no special load step would be needed.
B) Steady-state - Under the simple approach, you would need to schema-qualify
your aggregators if you invoked them from a schema which was different from the
schema in which you loaded the tool. E.g.:
select new_agg_schema.var_pop( value ) from mytable;
In contrast, under the complicated approach, no schema qualification would ever
be needed (or possible):
select var_pop( value ) from mytable;
There are more details to discuss once you have picked the approach you want to
pursue. But, hopefully, this gives you an overview of how to integrate your
code into Derby.
Hope this helps,
-Rick
> Add support for SQL Standard statistics functions, such as STDDEV_POP,
> STDDEV_SAMP, VAR_POP, VAR_SAMP
> -----------------------------------------------------------------------------------------------------
>
> Key: DERBY-5466
> URL: https://issues.apache.org/jira/browse/DERBY-5466
> Project: Derby
> Issue Type: Improvement
> Components: SQL
> Affects Versions: 10.8.1.2
> Reporter: Lukas Eder
> Priority: Minor
> Labels: derby_triage10_10
>
> Any of these RDBMS support the SQL standard statistics functions STDDEV_POP,
> STDDEV_SAMP, VAR_POP, VAR_SAMP:
> - DB2 (only STDDEV, VARIANE)
> - H2
> - HSQLDB
> - Ingres
> - MySQL
> - Oracle
> - Postgres
> - SQL Server (named STDEVP, STDEV, VARP, VAR)
> - Sybase ASE
> - Sybase SQL Anywhere
> These don't:
> - Derby
> - SQLite
> This would be a useful addition for Derby, I think.
> An even larger example list of possible statistics aggregate functions is
> listed in the Postgres documentation:
> http://www.postgresql.org/docs/9.0/static/functions-aggregate.html#FUNCTIONS-AGGREGATE-STATISTICS-TABLE
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
