Your Mother has asked for the wisdom of the Perl Monks concerning the following question:

A long and kind of convoluted DB coding question, slash, opinion gathering. I'll try to be as specific as possible with related info.

I'm working on an idea for a personal module to simplify automatic and appropriate generation of html form fields from DBI or from a db handle. I'd like to know:

Oh, and the CGI::MxScreen family is something I came across while digging that looks pretty interesting but doesn't do what I'm after. Anyone have any feedback on it?

Any related general feedback, experience, code snippets is, of course, welcome. Code bits and expanded query follow.

Given a hypothetical module SQL::DBIforms. It should take a DBI handle and return sensible form fields by querying the table for column info. A VARCHAR(10) would become a text input limited to 10 chars. An ENUM field would become a dropdown. A SET would become a scrolling menu allowing multiple selections and so on. 

# Given a test table along the lines of
use test;
CREATE TABLE userish (
    id           INT(6) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    user         VARCHAR(16)     UNIQUE NOT NULL,
    updated      DATE            NOT NULL,
    type         ENUM('admin','user','root','bot') 
                    NOT NULL DEFAULT 'user'
       ) TYPE = InnoDB;

[download]
Then with the supposed module, SQL::DBIforms, something along these lines (loose code, no error checking): 
use DBI;
use SQL::DBIforms;

my $connect_file = '/etc/www/.mysql.www';

my $dbh = DBI->connect( "DBI:mysql:database=userish;host=localhost;" .
                        "mysql_read_default_file=$connect_file;" );

my $db_fields = SQL::DBIforms::make_form_fields($dbh, 'userish');

for my $field ( @{db_fields} ) {

    next if $field->type eq 'DATE'; # want to stamp in background
    print 
        $field . ": \n",
        ucfirst $field->form_field, 
        $field->is_required ? ' (required)' : '', # for NOT NULL
        "\n<p>\n";
}

[download]
Which would result in something like: 
Id: 
<input type="text" name="userish.id" size=6 maxlength=6> (required)
<p>
User: 
<input type="text" name="userish.user size=16 maxlength=16>  (required
+)
<p>
Type: 
<select name="userish.type">
<option value="admin">admin
<option selected value="user">user
<option value="root">root
<option value="bot">bot
</select> (required)

[download]
The code in the SQL::DBIforms to get info out of the db handle would look something like: 
use Data::Dumper;
sub make_form_fields {
    my ( $dbh, $table ) = @_;

    my $sth = $dbh->prepare
        (qq{
            DESCRIBE ?;
        });

    my $table_info = $dbh->selectall_arrayref($sth, $ATTR, $table);

# then parse that structure to get info and turn it into an array of f
+ield objects
#     NOT NULL would set a required flag 
#     DEFAULT value would drop into same for field and so on

# I haven't written the parsing, just playing still so a cop out...
    return = "<PRE>", Dumper($table), "</PRE>\n";
}

[download]
Thank you for looking!

Replies are listed 'Best First'.
Re: Automatically generating html form fields from a DBI handle
by edoc (Chaplain) on May 03, 2003 at 13:37 UTC

    I'd have to strongly recommend looking into Class::DBI before you write too much code.

    I'm currently in the middle of trying to convert over a fairly large project to use it (as well as a bunch of new code of my own) and would be a lot happier if I'd discovered/started using it a lot sooner.

    You'll still have to write a few queries of your own, but at the very least it'll help you keep them all in the one spot so if, err, when you do have to make changes to your table structure, it'll be a lot easier.

    cheers,
    J

[reply]
      Thank you for pointing me to that. It's very interesting and does quite a bit of what I would like and have been sketching out. I think I can adapt it into a master subclass to do everything I want; generating classes on the fly based on a given table.

      If I ever come up with any code that does it and isn't too kludgey, I'll put it on my scratch pad and ask for everyone to take a peek.

[reply]
Re: Automatically generating html form fields from a DBI handle
by Abstraction (Friar) on May 03, 2003 at 03:03 UTC
    Is there something that does this already and I just missed it? If so, point me at it and skip the rest. :)

    phpmyadmin: It's not in Perl (It's in PHP), but it does do auto creation of form fields based on table structure. Specifically when you click on the 'Insert' tab when working with a table. This would be a good place to start if you don't mind reading a little PHP.

[reply]
[reply]
      Thanks. Both good approaches but I'm planning on using this in mod_perl (and a plain old CGI here and there for development) to roll into a large web app. I'm after an automatic approach b/c this thing will have a few dozen tables in a couple DBs and I can't bear the thought of changing the interface as the DBs are altered/corrected and mature. I couldn't bear the work load for that matter.

      Oh, on a related note, to everyone who pointed monks toward looking at the Template Toolkit: grazie mille! I had never heard of it and was about to go with Mason but I think I like TT2 better. Really amazing, fast, easy to dive into, powerful... wow.

[reply]
        Regardless of what environment you plan on using this in, the concept of how the form creation is done can be applied.


        A possible issue with this is: do you want the additional overhead that this approach will create? Or are the additional queries against MySQL not an issue?


        Just a thought.

[reply]
Re: Automatically generating html form fields from a DBI handle
by iguanodon (Priest) on May 03, 2003 at 12:31 UTC
[reply]
Re: Automatically generating html form fields from a DBI handle
by hv (Prior) on May 07, 2003 at 15:45 UTC

    This is certainly something that could be useful, and I was trying to work out how to do something very similar quite recently.

    You might want to think a bit about how to get a useful separation of concepts: in my case, I already have a bunch of field definition objects (constructed from the same data as was used to create the table in the first place), so I hope you'd provide a mechanism (by subclassing or with a direct hook) to bypass the "fetch and parse the describe table info".

    Another issue is the level of control the caller has over the output: she might want to choose, for example, whether a particular varchar field should be presented as a text input field or as a textarea, and might also want to supply the visual dimensions of the result. I haven't yet come up with a clean way of passing such information around.

    If you allow such levels of control, you'll probably find (as I did) that you need to incorporate both the field name and the display type into the parameter name, so that you know how to decode the parameters when the form is submitted. This also allowed me, for example, to invent a pseudo input-type of "date" and present it as 3 drop-downs for day/month/year instead of being forced to present it as a string.

    Hugo
[reply]
[d/l]
[select]

Back to Seekers of Perl Wisdom