[Metamodel Wiki] Update of "UserDefinedFunctions" by KasperSorensen

[Apache Wiki <wi...@apache.org>]

Dear Wiki user,

You have subscribed to a wiki page or wiki category on "Metamodel Wiki" for change notification.

The "UserDefinedFunctions" page has been changed by KasperSorensen:
https://wiki.apache.org/metamodel/UserDefinedFunctions

Comment:
Added page about UDFs

New page:
= User Defined Functions (UDFs) =

Apache MetaModel allows for extending the query language using User Defined Functions. In fact, the functions that are built into Apache MetaModel are also "just" built-in UDFs.

Functions in MetaModel come in two forms:

 * Aggregate functions, which implement the org.apache.metamodel.query.AggregateFunction interface.
 * Scalar functions, which implement the org.apache.metamodel.query.ScalarFunction interface.

== Scalar functions ==

A scalar function is a function that provides a result on each row of the dataset that it is applied to. For instance, the built-in function TO_NUMBER will convert each value to a java.lang.Number instead of some other data type.

Normally you can apply the Scalar function TO_NUMBER like this:

{{{#!java
Query q = dataContext.query().from(table).select(FunctionType.TO_NUMBER, "id").toQuery();
}}}

If you dig into FunctionType.TO_NUMBER you will find out that it is simply an object that implements ScalarFunction.
Let's imagine you wanted to implement instead now a hash code function. We can implement it like this:

{{{#!java
public class HashCodeFunction implements ScalarFunction {

    @Override
    public ColumnType getExpectedColumnType(ColumnType type) {
        return ColumnType.INTEGER;
    }

    @Override
    public String getFunctionName() {
        return "HASH_CODE";
    }

    @Override
    public Object evaluate(Row row, SelectItem operandItem) {
        Object value = row.getValue(operandItem);
        return value == null ? null : value.hashCode();
    }
}
}}}

As you can see the implementation part here is pretty easy. We need only to provide a name, a data type and implement the evaluate(...) method. Now to apply the function to our query:

{{{#!java
Query q = dataContext.query().from(table).select(new HashCodeFunction(), "id").toQuery();
}}}

== Aggregate functions ==

Aggregate functions are used to make calculations which span multiple rows of the dataset. Typically used on a complete dataset or in combination with a GROUP BY condition.

In a similar way to scalar functions, you can also implement your own aggregate functions. Let's say we wanted to implement a DISTINCT_COUNT function (ie. a count of distinct/unique values), we could do it like this:

{{{#!java
public class DistinctCountFunction implements AggregateFunction {

    @Override
    public String getFunctionName() {
        return "DISTINCT_COUNT";
    }

    @Override
    public ColumnType getExpectedColumnType(ColumnType type) {
        return ColumnType.INTEGER;
    }

    @Override
    public Object evaluate(Object... values) {
        AggregateBuilder<?> aggregateBuilder = createAggregateBuilder();
        for (Object value : values) {
            aggregateBuilder.add(value);
        }
        return aggregateBuilder.getAggregate();
    }

    @Override
    public AggregateBuilder<?> createAggregateBuilder() {
        return new AggregateBuilder<Integer>() {
            
            private Set<Object> uniqueSet = new HashSet<>();
            
            @Override
            public void add(Object o) {
                uniqueSet.add(o);
            }

            @Override
            public Integer getAggregate() {
                return uniqueSet.size();
            }
        };
    }
}
}}}

(you may choose to extend DefaultAggregateFunction which will save you the effort of implementing evaluate(...))

And again you can apply your function in a query like this:

{{{#!java
Query q = dataContext.query().from(table).select(new DistinctCountFunction(), "type").toQuery();
}}}