Perl/CGI Database Techniques

[yojimbo]

I'm getting increasing amounts of work which involve allowing a database to be manipulated, searched, edited, etc via a web-page. The back-end DB is MySQL, and I have no problem with the DBI module which provides a very complete set of DB access routines. I also make extensive use of HTML::Template, which makes data presentation a bit much easier.

However, it is a pita to write routines which create an SQL query based on user input (eg when searching); to check for errors in data entry and notify the user of those errors; to format the records for viewing by the user. If I could do it just once in a generic fashion, that would save me hours per project, but it seems like each application needs numerous tweaks and adjustments which take a long time to code and maintain.

I realise that for some of this, "them's the breaks" and it's what a programmer is for. But if you have any techniques, modules or tricks that you use to make such programming easier, please share your insight so I can become better at this sort of thing. I'm particularly interested in genericising the code I write so I can reuse it, in CPAN-type modules, and web resources or books which might help.

TIA.

[dragonchild]

Start with Class::DBI to help automate the SQL generation. Continue with the various validation modules on CPAN. Email::Valid for email addresses, Regexp::Common for common tasks (like identifying numbers), and URI for web addresses (and so much more). Notification of errors is a matter of designing a generic template that uses a TMPL_LOOP to list errors found. Formatting records is the same. You might have to do a TMPL_INCLUDE for the specific layout of this listing, but it's not that big a deal.

If you put some thought into it, you should be able to come up with a system. The big thing is that you need to stop development/tweaking, sit down for a week or so, and really think about how you want to lay things out. It most definitely is time well-spent.

------
We are the carpenters and bricklayers of the Information Age.

The idea is a little like C++ templates, except not quite so brain-meltingly complicated. -- TheDamian, Exegesis 6

Please remember that I'm crufty and crochety. All opinions are purely mine and all code is untested, unless otherwise specified.

[perrin]

This article shows how to automate some of it with Class::DBI.

[jdtoronto]

Some great advice!

I have a small shop doing two things - Per/Tk for stand-alone apps for a variety of platforms - AND Perl with MySQL webb-apps and CGI.

My list of indispensable stuff includes SQL::Abstract, CGI::FormBuilder, HTML::QuickTable, HTML::Template.

I am also looking at other modules to help, CGI::Session and Apache::Session among them along with CGI::Application.

Currently I do input field validation, and a degree of untainting, by having generic subroutines that use the metadata from the MySQL database itself. Here is one attempt at a sub that returns a HASH of table information.

sub getTableStructure {
    # need: $dbh
    #        tablename
    # returns: hash of field(column) records

    my ($dbh, $tablename) = @_;
    my %struct_hash;
    my $SQL = "SHOW COLUMNS FROM $tablename";
    my $sth = $dbh->prepare( $SQL );
    $sth->execute();
    while ( my $inphash = $sth->fetchrow_hashref() ) {
        $struct_hash{$inphash->{Field}} = $inphash->{Type};
    }
    return %struct_hash;
}

This hash can be used to look at the structure for each field and can help you automate the validation process. This code was written in a rush for a project that would have otherwise need a host of validation subs. I have to go back and clean up all of it, but maybe you get the idea of what I did.

jdtoronto

[princepawn]

have a look at DBIx::Recordset and the "living documentation" at DBIx::Recordset::Playground

Carter's compass: I know I'm on the right track when by deleting something, I'm adding functionality.

[BUU]

Maybe I'm barking up the wrong tree, but a lot of what your doing sounds like rewriting of PerlMyAdmin, which does many of the same things, database display and editing and so forth. Or a slightly better developed solution, in *gasp* php, PhpMyAdmin.

[jdtoronto]

I had noticed PerlMyAdmin before, but there has been no nchange for over 12 months. Any idea if it is still being devoloped, or what sort of state the code is in?

jdtoronto

[BUU]

I'm fairly certain it isn't being developed any more, since it won't even run under perl58 (activestate atleast), complaining about depreceated syntax errors and so on. But it does work on 5.6 and provides a nice clean interface for basic mysql tasks.