Databases and the applications that use them are essential to today's computing infrastructures. They are everywhere, from plain-text databases such as the UNIX® /etc/passwd file to large databases such as those that track shopping habits or fight credit card fraud.

This article looks at a specific aspect of the integration of Perl tools with generic RDBMSs (relational database management systems): embedding Perl in the database tables. I can already feel the tug of the database design purists at my sleeve -- this is neither a standard nor a portable approach. I know this, but there are two things about Perl that purists should know:

• "TMTOWTDI" is the Perl mantra. It means "There's More Than One Way To Do It."
• The quintessential quality of a Perl programmer is productive laziness (or lazy productivity).

Embedding is a productively lazy thing to do, but if you do not understand the consequences of moving to Java®, for example, you should avoid embedding Perl code in databases. In this article we'll use the Class::DBI CPAN module to manage database tables (more on that in the next section). While this article is suitable for beginner programmers as well the experienced, you will need some knowledge of database programming, especially with the Class::DBI CPAN module using MySQL (my environment). At a minimum, you should understand how to set up and use MySQL and how to create tables in a MySQL database. (And if you don't, see the Resources section for a quick-start tutorial on MySQL.)

Class::DBI capabilities

The Class::DBI module is a powerful Perl module that can model relational database table designs in code. With it, one-to-one and one-to-N relationships are possible, and you can even create an N-to-N relationship with some extra work (all covered in the Class::DBI documentation; see the link to the Class::DBI homepage in Resources).

Using the Class::DBI module is simple once the setup code is written. The setup code is usually a few lines that describe the database such as the database driver, username and password, and where to find it. From that point on, inserting, deleting, and modifying records are one-line operations. The fact that Class::DBI simplifies record operations to one-liners makes embedding Perl code in the database feasible and palatable. Anything longer would be too unwieldy.

Class::DBI can do much, much more. Be sure to look at the documentation -- I guarantee you will get a headache (and some happy surprises) from slapping your forehead so many times, so grab some aspirin first.

Now let's set up some tables.

Back to top

Setting up tables

Before delving into details, we should review the architecture and tables, such as the template table for Perl code. For example, object deletion will use the "DELETE" template. The following listing demonstrates the way you can define that table with MySQL.

DROP TABLE IF EXISTS codetemplates;
CREATE TABLE codetemplates (
  name varchar(200) NOT NULL,
  template longtext NOT NULL,
  PRIMARY KEY  (name)
) TYPE=MyISAM;
LOCK TABLES codetemplates WRITE;
INSERT INTO codetemplates VALUES ('DELETE', '$target->delete()');
INSERT INTO codetemplates VALUES ('MODIFY', '$target->VERB(DATUM)');
UNLOCK TABLES;

This is just a basic two-column table with the template name as the primary key, so no two templates can have the same name.

Programming is a lot like speaking because you need verbs, nouns, adjectives, and so on. In fact, Larry Wall, Perl's creator, is a linguist whose experience in that field has obviously influenced Perl's evolution. In terms of embedded Perl code, the things you always need are a "noun" (the thing affected) and a "verb" (the action taken). The "modifier" (adverb or adjective in a sentence) is not required, usually.

The "noun" is the target of the code. I'll call it $target and it will be required. The code that prepares for the action will have $target ready.

The "verb" is the action taken by the code. I'll run that action by evaluating the code; if the code should fail, then I will catch that error. Perl provides the eval() function for that purpose. The string VERB will be a placeholder for the modifier action.

The "modifier" is the tricky one. When it's not needed, as in the case of simple object deletion, you don't have to worry. When it is needed, we'll provide a placeholder string. The string will be DATUM, and you will see how to use it in the section on embedded modifications.

Back to top

Embedded deletion

Embedded deletion is truly simple. The embedded command is just $target->delete(); the Class::DBI module takes care of everything else. This usually works fine, but if you have has_many() relationships defined, you may trigger a cascading delete.

Cascading deletes are a scary concept initially. Essentially, the Class::DBI module knows (through the has_many() relationships) what objects depend on the object being deleted. For example, a car has_many() tires.

Usually, it makes sense to delete dependent objects, but often that's not the case. A common example is with a department and its employees in which the employees are not dependent on the department for their existence, but merely linked to it (so that an employee can be in multiple departments).

The author of Class::DBI has promised a standard solution for this that will allow the programmer to specify that a has_many() relationship should not trigger a cascading delete. Right now, the suggested solution is shown in Listing 2.

My::Department->add_trigger(
 before_delete => sub
 {
  $_->department(undef) foreach shift->employees;
 });

This disassociates every employee of the department from the department before the department's deletion.

Check with the Class::DBI site to see if the standard solution has been released. The latest UNDOCUMENTED and UNRELIABLE way to do it is to give the no_cascade_delete => 1 option to the has_many() setup call. It's certainly more appealing than custom triggers, but it may stop working if Class::DBI stops supporting it. Caveat emptor!

Now that you know all about cascading deletes and you've promised to be careful, you can see the actual code to execute the embedded $target->delete() code. This assumes that you have set up the "codetemplates" table from Listing 1, as you would normally with the Class::DBI module, and have written the CodeTemplate.pm module to use that table.

my $codetemplate = CodeTemplate->retrieve('DELETE');
foreach my $target (Class1->retrieve(500), Class2->retrieve(600))
{
 next unless defined $target;
 if ($codetemplate->template())
 {
  my $result = eval $codetemplate->template();
  if ($@)
  {
   print "The evaluation failed\n";
  }
  elsif (defined $result)
  {
   print "This operation resulted in [$result]\n";
  }
  else
  {
   print "This operation's result was undefined\n";
  }
 }
}

Class1 and Class2 are fictitious; you could just as well use the retrieve_all() function to get all the rows of each table that Class1 and Class2 represent.

As you can see, most of the work is in handling errors as it should be in such simple code. Note you can save the result of the operation as you like. For deletions this is not essential, but for other operations it can be important.

Back to top

Embedded modifications

Modification is more fun. The sharp-eyed ones have already noticed the code in Listing 1 that shows the template for modification: $target->VERB(DATUM). Other than the singular of "data," what does this template show?

Recall that earlier, I defined the necessary information for an operation as the noun, verb, and optional modifier. If the operation is "MODIFY," then the modifier is only optional for retrieving the value.

Let's do an example. The object (target noun or subject) is $target, which is of class Employee. That class has, from the "employees" table somewhere in the database, the field "name" so the raw code to set the name would be as follows.

my $target = Employee->retrieve(500);
$target->name("Jonesy");

Of course, if I did something so simple, I'd lose my professional pride and never work as a programmer again. I must obfuscate this example.

my $target = Employee->retrieve(500);
my $data = "Jonesy";
my $verb = "name";
my $codetemplate = CodeTemplate->retrieve('MODIFY');
my $template = $codetemplate->template();

if ($template)
{
 $template =~ s/VERB/$verb/g;

 # this is how you can get the OLD value of the field
 $retriever = $template;
 $retriever =~ s/DATUM//g;

 $old_value = eval $retriever;

 $template =~ s/DATUM/\$data/g;
}

my $result = eval $template;
if ($@)
{
 print "The evaluation failed\n";
}
elsif (defined $result)
{
 print "This operation resulted in [$result]\n";
}
else
{
 print "This operation's result was undefined\n";
}

Kidding aside, this example is complicated for a reason. Now you can set any field at all while keeping its previous value by setting a modifier and a verb.

Do you notice something funny? The code here looks a lot like Listing 3, which handles deletions. You can easily combine Listings 3 and 5 to make a generic code template handler. The deletion template would not suffer from any of the substitutions, but you do have to be careful about retrieving the old value of the field because for a deletion, that would just delete the object. So make the old value retrieval only happen for a "MODIFY" template.

Other templates you could add are, of course, ADD and SEARCH. They work the same way: give the template handler a template, a verb, and a modifier, and it will happily run the resulting code.

Back to top

Compatibility with other languages and alternative approaches

If you thought "Hey, why just Perl?" when you read this article, you were not the only one. I considered writing this article for Perl and Java™ or other mixed-language environments, but Perl is singularly well suited for one-line templates. I believe that longer templates in multiple languages will make the system unmaintainable in short order, so I recommend sticking with one language if you can -- and let that language be succinct. Python may work instead of Perl (as it has fairly short database manipulation directives), but I haven't done a test implementation in Python.

The other idea everyone has when looking at this, sooner or later, is "Why not put the opcode-to-template translation table in the code?"

You can. I don't recommend it because you are effectively translating code twice, once from opcode to template, and then from template to actual code. You may as well just map the opcode "DELETE" to whatever function is appropriate for deletion in your environment and language, to take that example. The general idea of abstracting operations to simplify data management is valid, no matter how you implement it.

The key to the abstraction, expressed in templates or raw code, should be to simplify operations to the noun-verb or noun-verb-modifier forms. When you perform this abstraction, you will learn valuable lessons about the needs and wants of your software. You will understand the ways in which the software will get from any state to another state, and (very importantly) if you can define every operation, then you can track and undo it as well.

Back to top

Conclusion

I hope you enjoyed learning about code templates stored in a database.

You may be interested in the Template Toolkit, which can make your templates much more powerful than the simple substitutions shown above. Beware, however, of making all your code a series of templates. The templates will be too complex and will become difficult to update very quickly. Make sure you balance power with simplicity, and when possible, allow both. That's the trademark of a good designer.

In the case of the Template Toolkit, a good rule of thumb is to have no more than one loop and no more than three interpolated variables per template (assuming the templates are intended as one-liners).

Once you consider code templates for your operations, you should look at the general MVC (model-view-controller) patterns implemented in the Java Swing toolkit, for example. There are many benefits to separating model, view, and controller; when you abstract operations as this article has shown, you can easily do the next step of separating the view and the model of your data. Even if you don't do the view-model separation, the MVC pattern is valuable in many situations, so you should take a look at how it works and what it can do for you.

Have fun with your database operations and remember, TMTOWTDI!

Resources

• Visit the homepage of the Class::DBI module.
  
  
• For more details on the MySQL database mentioned in this article, go to the homepage of MySQL.
  
  
• To get familiar with MySQL, take this MySQL tutorial; it shows how to get started, create and delete databases, create and alter tables, and more.
  
  
• "Build Web apps with Maypole" (developerWorks, May 2004) describes a Perl framework to build fast and easy database-backed applications; it provides some information on Class::DBI.
  
  
• Get an overview of the MVC architecture in the Java Swing Toolkit.
  
  
• Read all articles in Ted's Cultured Perl series on developerWorks.
  
  
• Find more resources for Linux developers in the developerWorks Linux zone.
  
  
• Get involved in the developerWorks community by participating in developerWorks blogs.
  
  
• Browse for books on these and other technical topics.
  
  
• Order the SEK for Linux, a two-DVD set containing the latest IBM trial software for Linux from DB2®, Lotus®, Rational®, Tivoli®, and WebSphere®.
  
  
• Innovate your next Linux development project with IBM trial software, available for download directly from developerWorks.
  
  

About the author

Teodor Zlatanov graduated with an M.S. in computer engineering from Boston University in 1999. He has worked as a programmer since 1992, using Perl, Java, C, and C++. His interests are in open source work, Perl, text parsing, three-tier client-server database architectures, and UNIX system administration. Contact Ted with suggestions and corrections at tzz@bu.edu.

Rate this article

Comments

Back to top

Table of contents

• Class::DBI capabilities
• Setting up tables
• Embedded deletion
• Embedded modifications
• Compatibility with other languages and alternative approaches
• Conclusion
• Resources
• About the author
• Comments