Embedding Perl in MySQL
Now that a Perl UDF exists, simple tricks like this can be performed:
mysql> select myperl('return $ARGV',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); 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.
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.
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 (email@example.com) 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.
- Readers' Choice Awards 2013
- IBM Will Minimize Impact of Future Disasters
- December 2013 Issue of Linux Journal: Readers' Choice
- Sublime Text: One Editor to Rule Them All?
- RSS Feeds
- Raspberry Pi: the Perfect Home Server
- Tech Tip: Really Simple HTTP Server with Python
- Linux Systems Administrator
- New Products
- Senior Perl Developer
1 min 43 sec ago
- yes it's Jupiter Broadcasting
1 hour 21 min ago
- nice to see PClinuxOS finally
3 hours 54 min ago
- Personally, I am no longer a
4 hours 51 min ago
- Reply to comment | Linux Journal
18 hours 16 min ago
- It's Jupiter
19 hours 16 min ago
- GIMP is certainly a graphic
20 hours 18 min ago
- Thanks For Your Sharing
1 day 1 hour ago
- Studying linux, and looking
1 day 4 hours ago
- voting for Best Linux Distribution
1 day 12 hours ago