I've been playing with CGI using CGI.pm, and recently decided to take another step - and downloaded MySQL and the DBI module.

SQL and DBI are excellent technologies: I feel very silly for having been so afraid of them for so long - it's a conceptual hurdle from text-based queries but it's way cool:

In a lazy (I was getting bored with manually creating forms) and hubris-fully over-ambitious moment yesterday, I started to work on a CGI script that:

1. Connects to a specified database
2. Allows you to select a table
3. Creates a display with all the fields in that table, and suggstions for how to render them as a form. e.g. varchar(255) would be rendered as <input type=text maxlength=255>
4. Allows you to choose which fields are displayed in the form, which are required etc.

This is all worked better, and quicker than expected, partly because I'm also using YACM - Yet Another Cool Module - the Template-Toolkit to create the HTML form for the interface.
Now all I need to do (;->) is render the forms (with Template.pm again of course)

My problems and/or questions to you are

1. Has something like this been done before? I'm sure it has, but couldn't find references to it. I won't regret working on this, because it is a great learning experience, but I'd like to use something better/more standard if possible
2. What advice do you have on the interface for creating the forms. For now, I've created a huge HTML <table> that scrolls off the screen with all the various check-boxes and text fields.
3. What advice do you have on where to limit the scope of this: e.g. should I
   • Just create the HTML tables but no Perl code?
   • Create tables with hooks to a validator like Data::FormValidator?
   • Create tables with hooks to actions on creating and searching tickets...? complicated - but useful?: we could define snippets for each widget that generate a SELECT or INSERT SQL statement. Then we could think about code that allowed you to search/create entries that referred to multiple tables... now this makes my head hurt, so keep it simple (for now!)

I imagine that I'll rewrite the code to have each widget type as a class (something like CGI::DBI::{widget} with the widget name being either an SQL datatype name like 'text', 'varchar', 'enum' etc., an HTML input type like 'SELECT', 'TEXT', 'PASSWORD', or a custom type like 'Calendar', 'Time')

Each widget would then expose methods that create the HTML table snippets to start off with. When/if I move onto generating code or validations then I can just add new methods as I go along.

Apologies if I'm thinking out loud: I'd really appreciate some insight into the process of planning this!

Cheerio!
Osfameron