test.cpp

MySQL用户自定义函数的使用示例: 详细说明了整个过程.

/* 
Written by: Philipp Simon

Examples for MySQL UDFs

Version: 1.0 September 2006



*/

/* STANDARD is defined, don't use any mysql functions */
#ifdef STANDARD
#include <stdlib.h>
#include <stdio.h>
#include <string.h>
#ifdef __WIN__
typedef unsigned __int64 ulonglong;	/* Microsofts 64 bit types */
typedef __int64 longlong;
#else
typedef unsigned long long ulonglong;
typedef long long longlong;
#endif /*__WIN__*/
#else
#include <my_global.h>
#include <my_sys.h>

#endif
#include <mysql.h>
#include <ctype.h>

static pthread_mutex_t LOCK_hostname;



/* These must be right or mysqld will not find the symbol! */

// The normal function example
extern "C" my_bool MyTest_init(UDF_INIT *initid, UDF_ARGS *args, char *message);
extern "C" void MyTest_deinit(UDF_INIT *initid);
extern "C" longlong MyTest(UDF_INIT *initid, UDF_ARGS *args, char *is_null, char *error);

/* For creation/deleting use: 
CREATE FUNCTION MyTest RETURNS INTEGER SONAME 'TestUDF.dll' and
DROP MyTest 

For testing something like:
SELECT data1, MyTest(data1) FROM test.x ;*/


// The aggregate function example
extern "C" my_bool MyTestAgg_init(UDF_INIT *initid, UDF_ARGS *args, char *message);
extern "C" void MyTestAgg_deinit(UDF_INIT *initid);
extern "C" longlong MyTestAgg(UDF_INIT *initid, UDF_ARGS *args, char *is_null, char *error);
extern "C" void MyTestAgg_clear(UDF_INIT *initid, char *is_null, char *error);
extern "C" void MyTestAgg_add(UDF_INIT *initid, UDF_ARGS *args, char *is_null, char *error);

/* For creation/deleting use: 
CREATE AGGREGATE FUNCTION MyTestAgg RETURNS INTEGER SONAME 'TestUDF.dll' and
DROP MyTestAgg 

For testing something like:
SELECT MyTestAgg(data1), data2 FROM test.x  GROUP BY data2;*/


/* NORMAL FUNCTION EXAMPLE */

my_bool MyTest_init(UDF_INIT *initid, UDF_ARGS *args, char *message)
{
	// The most important thing to do here is setting up the memory you need...
	// Lets say we need a lonlong type variable to keep a checksum
	// Although we do not need one in this case
	longlong* i = new longlong; // create the variable
	*i = 0;                     // set it to a value
        
	// store it as a char pointer in the pointer variable
	// Make sure that you don`t run in typecasting troubles later!!
	initid->ptr = (char*)i;
        
	// check the arguments format
	if (args->arg_count != 1)
	{
		strcpy(message,"MyTest() requires one arguments");
		return 1;
	}

	if (args->arg_type[0] != INT_RESULT)
	{
		strcpy(message,"MyTest() requires an integer");
		return 1;
	}       
	return 0;    
}

void MyTest_deinit(UDF_INIT *initid)
{
	// Here you have to free the memory you allocated in the initialization function
	delete (longlong*)initid->ptr;
}

longlong MyTest(UDF_INIT *initid, UDF_ARGS *args, char *is_null, char *error)
{
	/* So finally this is the part were we do the real work. This function is called for every record and the
	current value(s) or better pointers to the current values are stroed in the UDF_ARGS variable. We have to get
	the values, do our calculation and return the result. NOTE: You can access the memory allocated in MyTest_init
	through the UDF_INIT variable.
	In this example we will just add 5 to every value...*/
	return *((longlong*)args->args[0])+5;
}




/* AGGREGATE FUNCTION EXAMPLE */


my_bool MyTestAgg_init(UDF_INIT *initid, UDF_ARGS *args, char *message)
{
	// The most important thing to do here is setting up the memory you need...
	// Lets say we need a lonlong type variable to keep a checksum
	longlong* i = new longlong; // create the variable
	*i = 0;                     // set it to a value
        
	// store it as a char pointer in the pointer variable
	// Make sure that you don`t run in typecasting troubles later!!
	initid->ptr = (char*)i;
        
	// check the arguments format
	if (args->arg_count != 1)
	{
		strcpy(message,"MyTestAgg() requires one arguments");
		return 1;
	}

	if (args->arg_type[0] != INT_RESULT)
	{
		strcpy(message,"MyTestAgg() requires an integer");
		return 1;
	}       
	return 0;    
}

void MyTestAgg_deinit(UDF_INIT *initid)
{
	// Here you have to free the memory you allocated in the initialization function
	delete (longlong*)initid->ptr;
}

longlong MyTestAgg(UDF_INIT *initid, UDF_ARGS *args, char *is_null, char *error)
{
	// And in the end the sum is returned
	return *((longlong*)initid->ptr);
}

void MyTestAgg_clear(UDF_INIT *initid, char *is_null, char *error)
{
	/* The clear function resets the sum to 0 for each new group
	Of course you have to allocate a longlong variable in the init function and
	assign it to the pointer as seen above */
	*((longlong*)initid->ptr) = 0;
}

void MyTestAgg_add(UDF_INIT *initid, UDF_ARGS *args, char *is_null, char *error)
{
	// For each row the current value is added to the sum
	*((longlong*)initid->ptr) = *((longlong*)initid->ptr) + *((longlong*)args->args[0]);
}