Cultured Perl: Embedding Perl in database tables

By Teodor Zlatanov
2005-04-27

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.

Listing 2. Disabling cascading deletes on the fly

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.

Listing 3. Executing embedded code

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.

Tutorial Pages:
» Put Perl into your RDBMS Design to Reach Database Nirvana
» Class::DBI Capabilities
» Setting up Tables
» Embedded Deletion
» Embedded Modifications
» Compatibility with Other Languages and Alternative Approaches
» Conclusion
» Resources

First published by IBM DeveloperWorks

Related Tutorials:
» Random subroutines in Perl
» Log Script Use
» Creating Perl Modules for Web Sites
» Bit Vector, Using Perl Vec
» Build a Perl/CGI Voting System
» Perl Range Operator