Create or Renovate - Experiences with CGI::Ajax and CGI::CRUD::* CPAN Modules

When I recently once again had to create a web interface to a MySQL database I did a little searching and came across Cruddy!, a CRUD (Create, Read, Update and Delete) web based database interface system built using the CGI::CRUD Perl5 CPAN modules. This article relates some points of the process of solving the task at hand using this approach, how some issues with the system was solved and finally some conclusions are drawn from the experience as a whole.

The installation was pretty simple and straight forward thanks to the excellent instructions at the above site, and my great hosting company(HostMonster) offering a CPAN interface inside the cPanel associated with my account. Once the demo database packaged with the system had been installed and I had a look at the meta data table's contents, it became time to configure the system for our database.

Out of the box Cruddy! provided all the required operations on the different tables in our database, but because the meta data table had not yet been populated, the system did not understand how to (for example) relate tables to each other via select lists – it required you to enter ID values by hand instead of selecting from a list of human friendly names. By default, labels for form fields are simply the column names of the corresponding database columns which is in many cases undesireable.

On it's own the system reads as much data dictionary information from your database but this is usually not enough unless your schema corresponds perfectly with the default assumptions the system makes regarding one-to-many entity relationships among the tables.

After a few entries in the ui_control_table, the meta data table, this changed and you could now simply select via a HTML select input control the right entry for a foreign key column – exactly what you would expect from any such system.

Next came the more elaborate task of fine tuning the displayed labels for each table column, adding some help text here and there and selecting the right controls for each data type of the columns in the tables. This took some time but it was made simpler via directly editing the SQL statements and uploading them straight into the database via the MySQL command line monitoron the server.

Since the approach Cruddy! takes of one edit/view form per table tends to fragment information a lot I also had to set up some views on the data to provide a more compact representation of related records. Because of the large number of joins in the SQL selects, some of these views were executing too slowly. The situation was greatly improved by caching the views to normal tables instead of accessing them directly. The

create table cache_table_name select * from view_name

MySQL syntax prooved very useful here, as well as the

group_concat()

aggregate function for combining many fields into one.

Being 100% Perl and MySQL the system seemed fast and responsive, but it was not long before I discovered a serious performance problem. For most of the one-to-many relationships between the different tables, the foreign table contained more than 6,000 entries which meant that on search, add and edit forms select lists were being constructed with more than 6,000 OPTION tags in them! To make things worse, some of the forms contained more than one of these massive selects, rendering HTML pages with unacceptably long load times.

Up until this point no actual Perl coding had to be done – it was all a matter of SQL, some HTML and even less CSS and I did get quite far with this only, I must admit, but now there was no other option left – I had to jump into the source code.

Was choosing this Perl based solution a bad choice after all or was there some hope? Should I rather have spent the time on developing something from scratch in PHP? Was the many hours spent researching for a solution and implementing it in a Perl based system a waste of time or could the longest lived web scripting language, community and module archive offer a reasonable solution?

Surely someone must have faced this exact same problem before with this same module and system I told myself and started searching, but to no avail. Either this module was not really used much or the few uses were on a small dataset operating over high speed local networks where the load time was neglegable, or maybe a combination of the above, but I couldn't find any quick and dirty solution to the problem.

The perfect solution would be to have a different type of select input control that could take as input a few characters, rush it off to the server which in turn could then apply it as a filter, only returning to the page the entries matching the filter text. Reloading the entire page would not be an option since it would break the process flow, no it had to be an inline Ajax based solution, but would it be possible to alter such a fundamental portion of the system easily?

The simplest module I could find that could possibly do the job was CGI::Ajax again from CPAN; simple to install, man page style documentation with perldoc, examples right there in the docs, just beautiful! After nine years of copious mixtures of Visual Basic, C#, Python, PHP a bit of Java, and there I was, right back at from wence I originally started with when I was coding my first CGI programs in Perl.

This time round the many brackets and seemingly awkward looking syntax did not bother me as much as before; one has to show some respect for something like Perl having stood the test of time (more than 22 years now) in an industry where languages appear and fade faster than mist before the sun.

It wasn't exactly a piece of cake finding the right place to insert the CGI::Ajax inside the CGI::CRUD::* modules and altering the select input control and writing the Perl function which become exposed to the Ajax code on the page – it took about 2 days – but when it was done it worked and it worked well making the system usable over the net with even a slow connection.

Because all the source is open and plain text it was possible to go in anywhere and see what is happening. Because the author structured everything properly in seperate modules it was not too difficult to grasp what was going on, and because there was proper documentation for everything it was not a matter of trial and error but rather paying close attention to what was documented.

The final solution took the form of an additional small text input just preceding the select input. If the options for the select input exceeded a fixed number the server would not load the select input with any options but will rather wait for the user to first enter some filter characters in the text input appearing just before the select input on the forms. When the browser looses focus on the filter text (when the user moves away from the filter text input) the Ajax function on the page sends the contentes of the text input back to the server and it is applied as a filter to reduce the number of options in the select input such that it is less than the max number of allowed options.

The server then sends via Ajax the bit of HTML that will populate the select input and the JavaScript (all handled by the Ajax module) on the page makes sure it gets inserted inside the select input control. Focus is passed to the select input control and the user can now select from a much smaller and more appropriate set of options.

Create or Renovate?
When you search for Perl based e-commerce, content management, accounting, enterprise management, customer relationship management or whatever other business function system; there does not seem to be a lot of those out there written in Perl compared to PHP or Java for example.

There are probably much fewer generalised monolythic systems built using mainly Perl, but this doesn't say anything about the existance of possibly many large specialised business systems that do in fact mainly rely on Perl for holding everything together properly and that have grown so large and specialised that it has become impossible to package it up as a general system any more.

Think for a moment about Amazon.com. It's common knowledge that they are probably the largest Perl shop on the planett. I would not be surprised if their e-commerce arm started out, or at some stage at least, was being developed with the intention of selling or reusing the whole system again but then pretty soon grew too big and complex for any such a possability to exist any longer.

In fact, the truly gigantic amount of CPAN modules suggest that this is indeed the case, that there are far many more enterprises dependant on Perl based systems than one is led to believe and that the only real indicator of this is the ever growing collection of reusable modules.

It seems to me that the approach to follow if Perl is to be used for development, is to build something from a combination of available modules as apposed to searching for a closest fit generalised full solution to customise to your needs – an approach I've used a lot when developing something in PHP. It is very tempting, especially in the PHP world, to take one of many fine generalised full systems and start customising it to sute your needs because it appears that you get a lot “for free” straight out of the box. While such an approach could work wonders for some projects it could also be the worst choice for others.

Using Cruddy! and the CGI::Ajax combination described in this article is sort of mid way between building with modules only and customising a generalised full solution. I say this because Cruddy! is a demo implementation making use of the CGI::CRUD::* modules while CGI::Ajax and DBIX:IO:::Table used in the modifications are true modules, not demo implementations of how to use a particular module.

I'm don't think I'll easily use Cruddy! Again for a similar task unless it would be possible to add better support for editing related records from one screen since things get too fragmented in a highly normalised data model. As for Perl, I would like to use it again, and next time make even better use of the available modules – perhaps even the Catalyst web framework?

Useful Links