Embedding Perl in MySQL

Add your own functionality to MySQL with MyPerl, which brings the powerful, versatile Perl interpreter into the heart of the relational database.
Examples Using MyPerl

Now that a Perl UDF exists, simple tricks like this can be performed:

mysql> select myperl('return $ARGV[0]',User)
       from mysql.user;

As you can see, each row corresponds with a value in @ARGV. You also can use MyPerl with CPAN modules to enter data directly. This example fetches the content for a list of URLs and inserts the content into the database:

mysql> insert into html select
       myperl("use LWP::Simple;
       my $content = get($ARGV[0]);
       return $content", url) from urls;

Using modules like XML::Simple and XML::XPath, you can even query any XML you may have stored in your database. I have used MyPerl for quickly debugging serialized Perl objects I have stored in the database.

But What about GROUP BY?

Although the above demonstrates how to handle row requests with this code, it does not work for queries that use GROUP BY to treat data as sets. For this reason, there is an additional type of UDF called an aggregate. Aggregates differ from their more bland cousins by having two additional stages, reset and add. With aggregate UDFs, the add function handles each row, and the request stage sorts out the results and sends the data on to the client. The reset stage is called at the beginning of each data set, so it is guaranteed to be called at least once. MyPerl currently has an aggregate UDF, but its design is still in flux.

Conclusion

By embedding Perl into MySQL, the realm of possibilities as to what you can do in your database has expanded. Although frequently it is better to keep your database as simple as possible, you may find in some cases this is not practical. Imagine having to pull a gigabyte of text out of the database and send it to a client to be used. The time spent on sending the data would be considerable; being able to do work on the data directly in the database with Perl may turn out to be a real time-saver. Being able to make use of Perl advanced regular expressions may allow you to write simple clients in other languages that do not have a good regular expression support. I am sure you will find many uses for this in your own environments. MyPerl can be found at software.tangent.org along with other UDFs you can use as examples to write your own.

Brian Aker (brian@tangent.org) spends his time working on MySQL and Apache modules, which include mod_layout and the Apache streaming services module, mod_mp3. He recently coauthored the Running Weblogs with Slash book for O'Reilly. For years he worked on Slashdot and now works for MySQL AB as a senior software architect. He also teaches the Perl Certification course at the University of Washington. He lives in Seattle, Washington with his dog Rosalynd.

______________________

Comments

Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.

Memory leak

Anonymous's picture

Hi sir,

I'm Nguyen Ngoc Tam. After reading your this topic, i found that the memory of your MYSQL server would increase so that it could cause the memory leak, when it handled a lot of perl queries. Have you controlled this situation ?.

Regards
Ngoc Tam

Webinar
One Click, Universal Protection: Implementing Centralized Security Policies on Linux Systems

As Linux continues to play an ever increasing role in corporate data centers and institutions, ensuring the integrity and protection of these systems must be a priority. With 60% of the world's websites and an increasing share of organization's mission-critical workloads running on Linux, failing to stop malware and other advanced threats on Linux can increasingly impact an organization's reputation and bottom line.

Learn More

Sponsored by Bit9

Webinar
Linux Backup and Recovery Webinar

Most companies incorporate backup procedures for critical data, which can be restored quickly if a loss occurs. However, fewer companies are prepared for catastrophic system failures, in which they lose all data, the entire operating system, applications, settings, patches and more, reducing their system(s) to “bare metal.” After all, before data can be restored to a system, there must be a system to restore it to.

In this one hour webinar, learn how to enhance your existing backup strategies for better disaster recovery preparedness using Storix System Backup Administrator (SBAdmin), a highly flexible bare-metal recovery solution for UNIX and Linux systems.

Learn More

Sponsored by Storix