.. _mariadb_udaf:

MariaDB UDAF
============

In order for the Columnstore UDAF to be parsed by MariaDB, a standard MariaDB c UDAF must be written and included in a library which is placed in the mysql/lib directory of the Columnstore install directory (default: /usr/local/mariadb/columstore/mysql/lib).

This set of c functions may be just a stub out to tell the parser about the function, or may be a fully implemented function if you want the UDAF to be usable by other engines.

The library placed in mysql/lib is the name you use in the SQL CREATE AGGREGATE FUNCTION statement to tell the parser where to find the function:

.. code-block:: sql

    CREATE AGGREGATE FUNCTION ssq returns REAL soname 'libudf_mysql.so';

Unlike the code you write for the Columnstore UDAF, MariaDB does not handle allocation and de-allocation of your memory structures in other engines. If writing your function for other engines, you must handle allocation and de-alloaction in :ref:`function_init <func_init>` and :ref:`function_deinit <func_deinit>`

All of the MariaDB UDF and UDAF example functions are in a single source file named udfmysql.cpp and linked into libudf_mysql.so.

For more information on MariaDB UDF see the `MariaDB library UDF <https://mariadb.com/kb/en/the-mariadb-library/user-defined-functions/>`_

The following functions must be defined. The function names and signatures are generated by the CREATE AGGREGATE FUNCTION statement and are not optional. Replace "function" with your function name.
 
* :ref:`my_bool function_init <func_init>`
* :ref:`void function_deinit <func_deinit>`
* :ref:`void function_clear <func_clear>`
* :ref:`void function_add <func_add>`
* :ref:`long long function <func_body>`

.. _func_init:

function_init()
---------------

.. c:function:: my_bool function_init(UDF_INIT* initid, UDF_ARGS* args, char* message);

:param initd [in/out]: A pointer to a pr-allocated UDF_INIT struct. UDF_INIT is defined in the libmariadb/include/mariadb_com.h file in the mariadb source structure. See also the `MariaDB UDF calling sequence <https://mariadb.com/kb/en/the-mariadb-library/user-defined-functions-calling-sequences/>`_. The ptr member is a char* which can be used to point to user allocated memory.

:param args [in]: An pointer to a UDF_ARGS struct defining the input arguments as entered in the SQL query. UDF_ARGS is defined in the libmariadb/include/mariadb_com.h file in the mariadb source structure. See also the `MariaDB library <https://mariadb.com/kb/en/the-mariadb-library/user-defined-functions-calling-sequences/>`_

:param message [out]: A pre-allocated buffer in which to copy an error message if needed. The size of the buffer is MYSQL_ERRMSG_SIZE, currently 512 bytes.

:returns: 1 on failure or 0 on success.

 The init function does any argument checking, sets values in initid and allocates up any function specific memory. 

::

 my_bool ssq_init(UDF_INIT* initid, UDF_ARGS* args, char* message)
 {
    struct ssq_data* data;
    if (args->arg_count != 1)
    {
        strcpy(message,"ssq() requires one argument");
        return 1;
    }

    if (!(data = (struct ssq_data*) malloc(sizeof(struct ssq_data))))
    {
        strmov(message,"Couldn't allocate memory");
        return 1;
    }
    data->sumsq	= 0;

    initid->ptr = (char*)data;
    return 0;
 }


.. _func_deinit:

function_deinit()
-----------------

.. c:function:: void function_deinit(UDF_INIT* initid);

:param initd [in]: A pointer to a pr-allocated UDF_INIT struct. UDF_INIT is defined in the libmariadb/include/mariadb_com.h file in the mariadb source structure. See also the `MariaDB library <https://mariadb.com/kb/en/the-mariadb-library/user-defined-functions-calling-sequences/>`_. If you allocated memory to the ptr member in function_init, then you must deallocate it here.

:returns: nothing.

 The deinit function is used to free any memory allocated in function_init

::

 void ssq_deinit(UDF_INIT* initid)
 {
    free(initid->ptr);
 }	

.. _func_clear:

function_clear()
----------------

.. c:function:: void function_clear(UDF_INIT* initid, char* is_null, char* message);

:param initd [in]: A pointer to a pr-allocated UDF_INIT struct. UDF_INIT is defined in the libmariadb/include/mariadb_com.h file in the mariadb source structure. See also the `MariaDB library <https://mariadb.com/kb/en/the-mariadb-library/user-defined-functions-calling-sequences/>`_. use the initid->ptr member to access your user allocated memory.

:param is_null [out]: A pointer to a single byte that you can set and use in later functions. is_null is set to 0 before each call to clear.

:param message [out]: A pointer to a single byte that you can set and use in later functions. Do not copy a string to this parameter, as it is not a buffer. The initial value is 0 and is not reset for further calls to any function including clear.

:returns: nothing.

 clear is called to reset the summary results. It is called at the beginning of each GROUP BY, and may also be called where there are no matching rows.

::

 void
 ssq_clear(UDF_INIT* initid, char* is_null __attribute__((unused)),
               char* message __attribute__((unused)))
 {
    struct ssq_data* data = (struct ssq_data*)initid->ptr;
    data->sumsq = 0;
 }

.. _func_add:

function_add()
--------------

.. c:function:: void function_add(UDF_INIT* initid UDF_ARGS* args, char* is_null, char* message);

:param initd [in]: A pointer to a pr-allocated UDF_INIT struct. UDF_INIT is defined in the libmariadb/include/mariadb_com.h file in the mariadb source structure. See also the `MariaDB library <https://mariadb.com/kb/en/the-mariadb-library/user-defined-functions-calling-sequences/>`_. use the initid->ptr member to access your user allocated memory.

:param args [in]: An array of UDF_ARGS structs defining the input arguments as entered in the SQL query. UDF_ARGS is defined in the libmariadb/include/mariadb_com.h file in the mariadb source structure. See also the `MariaDB library <https://mariadb.com/kb/en/the-mariadb-library/user-defined-functions-calling-sequences/>`_. The args array in args will contain the values of the args as char**. These must be cast to the type indicated in args->arg_type

:param is_null [in/out]: A pointer to a single byte that you can set and use in later functions. is_null will contain the most recent value you set since the last clear call.

:param message [in/out]: A pointer to a single byte that you can set and use in later functions. Do not copy a string to this parameter, as it is not a buffer. message will contain the last value you set.

:returns: nothing.

 add is called for each row in the filtered result set. Used to insert the row data into the functions summary data.

::

 void ssq_add(UDF_INIT* initid, UDF_ARGS* args,
                  char* is_null,
                  char* message __attribute__((unused)))
 {
    struct ssq_data* data = (struct ssq_data*)initid->ptr;
    double val = cvtArgToDouble(args->arg_type[0], args->args[0]);
    data->sumsq = val*val;
 }

.. _func_body:

function
--------

.. c:function:: <data type> function_add(UDF_INIT* initid UDF_ARGS* args, char* is_null, char* message);

:param initd [in]: A pointer to a pr-allocated UDF_INIT struct. UDF_INIT is defined in the libmariadb/include/mariadb_com.h file in the mariadb source structure. See also the `MariaDB library <https://mariadb.com/kb/en/the-mariadb-library/user-defined-functions-calling-sequences/>`_. use the initid->ptr member to access your user allocated memory.

:param args [in]: An array of UDF_ARGS structs defining the input arguments as entered in the SQL query. UDF_ARGS is defined in the libmariadb/include/mariadb_com.h file in the mariadb source structure. See also the `MariaDB library <https://mariadb.com/kb/en/the-mariadb-library/user-defined-functions-calling-sequences/>`_. The values in args->args are undefined here.

:param is_null [in/out]: A pointer to a single byte that you can set and use in later functions. is_null will contain the most recent value you set since the last clear call.

:param message [in/out]: A pointer to a single byte that you can set and use in later functions. Do not copy a string to this parameter, as it is not a buffer. message will contain the last value you set.

:returns: The data type as set by the SQL CREATE AGGREGATE FUNCTION.

 This is considered the function body. Use your summary data as accumulated in the calls to function_add and do any manipulation needed to come up with your answer for the GROUP.

::

 long long ssq(UDF_INIT* initid, UDF_ARGS* args __attribute__((unused)),
               char* is_null, char* error __attribute__((unused)))
 {
    struct ssq_data* data = (struct ssq_data*)initid->ptr;
    return data->sumsq;
 }