Embedding Perl in MySQL

The structure is as follows:

typedef struct {
    char *returnable;
    size_t  size;
    PerlInterpreter *myperl;
    size_t  messagesize;
} myperl_passable;

The char pointer returnable is used to store the block of memory, and size and messagesize are used to track both the total size and the current size of the returnable data. Because creating and destroying memory is quite costly, it is important to keep this down to a minimum. The Perl interpreter also will be stored in this structure.

At this point, the work of setting up the Perl interpreter that will be used for the query must be done. Currently, MyPerl creates a new Perl interpreter for each request to keep from leaking memory and ensure the security of data between requests. The odds of this becoming a pool of Perl interpreters in the future is quite high:

if((my_perl = perl_alloc()) == NULL) {
    strncpy(message, "Could not allocate perl",
            MYSQL_ERRMSG_SIZE);
    return 1;
}
perl_construct(my_perl);
exitstatus = perl_parse(my_perl, xs_init, 4,
                        embedding, environ);
PL_exit_flags |= PERL_EXIT_DESTRUCT_END;
if (exitstatus) {
    strncpy(message, "Error in creating perl parser",
            MYSQL_ERRMSG_SIZE);
    goto error;
}
exitstatus = perl_run(my_perl);
if (exitstatus) {
    strncpy(message, "Error in parsing your perl",
            MYSQL_ERRMSG_SIZE);
    goto error;
}

The first function, perl_alloc(), allocates a new Perl interpreter that is then constructed with perl_construct(). Now it is simply a matter of starting up Perl. The embedding variable is used as parameters for the Perl interpreter. These are exactly the same parameters you would use on the command line. Errors must be checked for at every point in dealing with the Perl interpreter. In the current design, if an error occurs, MyPerl jumps to a collection of function calls that will clean up the memory that has been allocated.

Now that a good Perl interpreter exists, a few defaults in the pass structure need to be set; the interpreter must be stored, and the address of the structure must be stored in initid->ptr pointer so it can be used throughout the query:

pass->returnable = NULL;
pass->size = 0;
pass->messagesize = 0;
pass->myperl = my_perl;
initid->ptr = (char*)pass;
return 0;

After all of this setup, MyPerl is ready to start taking requests:

char *
myperl(UDF_INIT *initid, UDF_ARGS *args,
       char *result, unsigned long *length,
       char *is_null, char *error)
{
    myperl_passable *pass =
        (myperl_passable *)initid->ptr ;
    char *returnable = NULL;
    unsigned long x = 0;
    size_t size = 0;
    char *newspot = NULL;
    char *string = NULL;
    myperl_passable *pass =
        (myperl_passable *)initid->ptr ;
    STRLEN n_a; //Return strings length
    PerlInterpreter *my_perl = pass->myperl;

It is important that the address of the interpreter is copied into a variable named my_perl. Much of the Perl internals are based on macros that expect you to use variables with certain names. STRLEN is a variable type that Perl uses to store the size of strings.

At this point the interpreter is called:

dSP;
ENTER;
SAVETMPS;
PUSHMARK(SP);
// Now we push the additional values into ARGV
for(x = 0; x < args->arg_count  ; x++) {
    XPUSHs(sv_2mortal(newSVpvn(args->args[x],
           args->lengths[x])));
}
PUTBACK;
call_pv("MyPerl::Request::handler", G_SCALAR);
SPAGAIN;
string = POPpx;
size = (size_t)n_a;

XPUSHs is used to push all of the rows into an array of strings that will be passed to the Perl function handler() in the library MyPerl::Request(). This Perl module is similar to the Apache::Request module except where the Apache module uses a filename to track code it has eval'ed, MyPerl uses the code itself to determine this.

Because the variable named size now holds the size of the data that will be returned, space needs to be allocated for it:

if (size) {
    if(pass->size < size) {
        newspot = (char *)realloc(pass->returnable,
                                  size);
        if(!newspot) {
            error[0] = '1';
            returnable =  NULL;
            goto error;
        }
        pass->size = size;
        pass->returnable = newspot;
    }
    // Always know the current size,
    // it may be less than the full size
    pass->messagesize = size;
    memcpy(pass->returnable, string, size);
} else {
    is_null[0] = '1';
}

error:
PUTBACK;
FREETMPS;
LEAVE;
*length = pass->messagesize;

return pass->returnable;
}

This is where the information that needs to be sent to the server is stored. The memory call realloc() is used if more memory needs to be allocated. If no data is received from the Perl interpreter, is_null is set to 1 so that MySQL knows a null result should be returned to the client. MyPerl also makes sure to clean up memory that might have been used for the call_pv() function.

The myperl() function is now called for each row. After MySQL returns its data to the client, it calls the deinit function to free the interpreter and release any allocated memory:

void myperl_deinit(UDF_INIT *initid)
{
    myperl_passable *pass =
        (myperl_passable *)initid->ptr ;
    perl_destruct(pass->myperl);
    perl_free(pass->myperl);
    free(pass->returnable);
    free(initid->ptr);
}