Chapter 14. Extending MySQL

Example 14-1. A UDF that returns the contents of an image file based on its filename

#include <mysql/my_global.h>
#include <mysql/my_sys.h>
#include <mysql/mysql.h>
#include <mysql/m_ctype.h>
#include <mysql/m_string.h>
   
#define MAX_IMAGE_SIZE 1048576
#define MAX_FILE_NAME 1024

/* Our 'init' routine is responsible for some simple set-up:
      - Check the argument count. This function must have exactly
             one argument, the filename, so we return an error 
             if there are any other number.
      - Coerce the types of the arguments. Theoretically, the 
              image names could be stored as integer IDs 
              (and thus stored in files with simple numbers 
              for names in the filesystem). Therefore, we want
              to allow any type of argument, and coerce it to a string.
     - Allocate the data buffer to hold the image data. This is a fairly large
              buffer to accommodate images of differing sizes.
*/
my_bool getImage_init(UDF_INIT *initid, UDF_ARGS *args, char *message) {
    if (args->arg_count != 1) {
        strmov(message,"Usage: getImage( fileName )");
        return 1;
    }
    args->arg_type[0] = STRING_RESULT;
    if ( !(initid->ptr = 
        (char *) malloc( sizeof(char) * MAX_IMAGE_SIZE ) ) ) {
        strmov(message, "Couldn't allocate memory!");
        return 1;
    }
    bzero( initid->ptr, sizeof(char) * MAX_IMAGE_SIZE );
    return 0;
}

/* This routine frees the memory allocated by the image buffer */
void getImage_deinit(UDF_INIT *initid) {
    if (initid->ptr)
        free(initid->ptr);
}

/* The main routine does most of the work for this function. The argument
   passed to the function is the filename of an image file. This function first
   uses 'chdir' to set the directory to the directory of the image file. This
   prevents misuse of this function by using it to read an arbitrary file on
   the filesystem. (We also remember the old working directory so that we can
   reset it at the end.)

   Once we've set the current directory, we open the image file using the
   Filename passed in as the argument. If the image opens successfully, we read
   the contents of the file into the empty data buffer. 

   Before returning the data, we set the length pointer to tell MySQL how
   Large our image buffer is. Finally, we return the pointer to the data 
   as the string result of this function.
*/
char *getImage(UDF_INIT *initid, UDF_ARGS *args, char *result,
               unsigned long *length, char *is_null, char *error) {
    FILE *img_file;
    size_t img_size;
    char filename[MAX_FILE_NAME];
    char old_dir[MAX_FILE_NAME];

    bzero(filename, MAX_FILE_NAME);
    bzero(old_dir, MAX_FILE_NAME);

    getcwd(old_dir, MAX_FILE_NAME);
    chdir("/path/to/my/pictures");
    strncpy( filename, args->args[0], args->lengths[0] );
    img_file = fopen( filename, "r" );
    if (! img_file ) {
        *is_null = 1;
        return 0;    
    }
    
    img_size =
        fread( (void *)initid->ptr, sizeof(char),
            MAX_IMAGE_SIZE, img_file );
    
    *length = (unsigned long)img_size;
    chdir(old_dir);
    fclose(img_file);
    return initid->ptr;
}

Example 14-2. An aggregate UDF that calculates the distance from the center of a graph

#include <mysql/my_global.h>
#include <mysql/my_sys.h>
#include <mysql/mysql.h>
#include <mysql/m_ctype.h>
#include <mysql/m_string.h>
   
/* This is the structure that defines the aggregate of all of the points
 * It contains the sum of all 'x' values, the sum of all 'y' values,
 * and the total number of points counted (for a single group).
 */
struct point {
	double x;
	double y;
	long count;
};

/* Our 'init' routine has several duties, this time:
      - Check the argument count. This function must have exactly
             two arguments, x and y, so we return an error if there are
             any other number.
      - Coerce the types of the arguments. Since we are dealing with decimal
              values, we don't want to penalize the user for passing in
              integers, or even numeric values stored within a string, so
              we coerce the incoming arguments into a 'real' type.
      - Set the 'maybe_null' flag to zero. This function will never return
              a null value. Maybe a 'zero' value, but not null.
      - Set the 'decimals' flag to 5 and the 'max_length' flag to 10.
              This will cause the results to be in the format +/-###.#####
              with a guarantee of 5 decimal places and up to three integer
              places.
     - Allocate the point structure. This structure will be used to house
              the aggregate point data as we accumulate it.
*/
my_bool distanceFromCenter_init( UDF_INIT* initid, UDF_ARGS* args, 
	char* message ) {

	/* Check number of arguments */
    if (args->arg_count != 2) {
		strmov(message,"Usage: distanceFromCenter( x, y )");
		return 1;
	}

    /* Coerce arguments to 'real' */
	args->arg_type[0] = REAL_RESULT;
	args->arg_type[1] = REAL_RESULT;

   /* Set flags */
	initid->maybe_null = 0;
	initid->decimals = 5;
	initid->max_length = 10;

    /* Allocate pointer */
	initid->ptr = malloc( sizeof( struct point ) );
    if (! initid->ptr ) {
        strmov(message, "Cannot allocate memory for 'point' structure");
        return 1;
    }
	return 0;
}

/* The 'deinit' routine only has to de-allocate the 'point' structure */
void distanceFromCenter_deinit( UDF_INIT* initid ) {
	free(initid->ptr);
}

/* The 'add' routine is called once for each row of data. It is responsible
   for aggregating the point information. It simply adds the 'x' and 'y'
   values to the totals stored in the point structure and then increments
   the count in the same structure.
*/
void distanceFromCenter_add( UDF_INIT* initid, UDF_ARGS* args, 
	char* is_null, char *error ) {
	((struct point *)initid->ptr)->x += *((double *)args->args[0]);
	((struct point *)initid->ptr)->y += *((double *)args->args[0]);
	((struct point *)initid->ptr)->count++;
}

/* The 'reset' routine is called at the beginning of each group. It simply
   zeros out the information  in the point structure in preperation for a new
   group of data. As mentioned earlier, this is the only routine called on the
   first row of data in a group. Therefore, we must also call the 'add' routine
   from this routine, to include the first row of data in the aggregate set.
*/
void distanceFromCenter_reset( UDF_INIT* initid, UDF_ARGS* args, 
	char* is_null, char *error ) {
	((struct point *)initid->ptr)->x = 0.0;
	((struct point *)initid->ptr)->y = 0.0;
	((struct point *)initid->ptr)->count = 0;
	distanceFromCenter_add( initid, args, is_null, error );
}

/* The 'main' routine has a very simple role here. It just has to take the
   aggregate data from the point structure and calculate a point (x,y) value
   for that data. It then runs the classic a^2 + b^2 = c^2 formula to determine
   the distance to the center of the grid for that point.
double distanceFromCenter( UDF_INIT* initid, UDF_ARGS* args, 
	char* is_null, char *error ) {
	double avg_x, avg_y;

    /* Calculate the average 'x' and the average 'y'
	avg_x = ((struct point *)initid->ptr)->x / 
		(double)((struct point *)initid->ptr)->count;
	avg_y = ((struct point *)initid->ptr)->y / 
		(double)((struct point *)initid->ptr)->count;
	
    /* Return the square root of a^2 + b^2 */
	return sqrt( avg_x * avg_x + avg_y * avg_y );

}