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

In reply to CGI Form builder for DBI? by osfameron

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.