Forum OpenACS Development: Form generator for OpenACS4 / PostgreSQL

I just uploaded a tool I wrote called formwizard to see if anyone else finds it useful. file_id=334
I wrote the formwizard tool to help me create a web UI for some complicated client databases with dozens of tables and hundreds of columns. In the process I learned a lot about the OpenACS4 templating system's approach to forms, which I've always avoided learning about before because the API looked yucky. I hope that other people will find that formwizard provides a gentle slope to using ATS forms.

Formwizard is a code generator. It gives you an almost-ready-to-use page for adding and editing rows in a table that you specify. It gets the table information it uses by querying the PostgreSQL system catalogs. It wouldn't be hard to make an Oracle version.

The most important characteristic of this approach is unlimited flexibility. If you don't like the decisions the code generator made, you can change the results all you want. Using formwizard simply provides you with over 100 lines of boilerplate code, customized to the table you're working with. You can put the page wherever you want in your page flow. You're free to customize the form elements in whatever tricky way you want. Some of the things you'll probably want to do are:

  • add some code to populate a select element with the results of a database query
  • if you don't like the "Yes/No" approach to booleans, change them to checkboxes
  • if the basic DML insert/update isn't good enough for you, change it to call stored procedures instead
  • choose your own text or textarea sizes
  • add section names
  • modify the "standard" form template to look good on your website
Here are some highlights of what formwizard does for you:
  • DB not null -> ATS "-optional" validation
  • DB check constraint -> ATS select element with options parsed from check constraint
  • DB integer -> ATS integer validation
  • DB date,timestamp,time -> ATS date widget
  • DB boolean -> Yes/No select
  • DB primary key -> ATS "hidden" form element and the so-called doubleclick protection
I might overlook some of these things if I were writing the form by hand. I'm looking forward to better quality and consistency with the auto generated forms.

Please let me know if you can suggest any improvements or if you identify any bugs.

Posted by Don Baccus on
This sounds like a really nice idea. The only negative thing that sticks out is this:
if the basic DML insert/update isn't good enough for you, change it to call stored procedures instead
Because that really breaks the API abstraction that permeates [Open]ACS 4, and doing that is risky (for instance, you might stick a mixed cased e-mail address in a party which breaks things - it's already broken in places and I'm busy fixing this so I thought it was a timely time to mention this particular example :)

But there's no good solution to get around this at the moment. Not all packages provide an "edit" API anyway and UPDATEs permeate the code, but the INSERTs really need to be avoided in general.

Maybe there could be a default "safe" mode that just screams "insert your NEW() and EDIT() calls here"?

Long term this and other things (Tom Jackson's "non-programming" tool) make me long for a higher-level description of the API that could be read and used to generate the proper calls ... another idea I've not thought much about.

Also ... is this written in a self-submission style using the form handler's validation code or ad_page_contract. OK, I'm being lazy by not looking but figured you probably know :) This is really how the form handler's meant to be used and it is nice when you do rather than validate via ad_page_contract, because the errors appear in the form with the offending values there for you to see.

Several of us have been talking elsewhere about better integration of the template and form builder systems with ad_page_contract and other ACS fundamentals but for now it ain't there, making things messier than they should be.

If people like Luke's package it would be great if someone would step up to look into doing an Oracle port. In general digging information out of Oracle's system tables is more straightforward than doing so with PG anyway.

Posted by Luke Pond on
Thanks Don. The generated code works great for plain old database tables that are not ACS objects. That's what I'm working with at the moment, and that's what the insert and update statements are intended for. I don't think this tool will encourage people to ignore the PL/SQL API; they'd have to go out of their way to do that. If used against a typical OpenACS table, the default DML code will fail when trying to insert the table's primary key, since no corresponding row exists in acs_objects.

A different version of this tool could be produced that was more directly helpful in the effort to revamp the OpenACS UI. But it couldn't help you very much with the difficult task of figuring out the positional parameters in each API call, since as you say, the API for every object type is slightly different.

I followed the example of all the packages I could find that use ATS forms (like photo-album) and didn't attempt to do any validation of user input with ad_page_contract. That gets done by the template::element calls. I'm pretty happy with the way that validation errors get reported inline with the form.

Posted by Tom Jackson on

Just to mention this here, my query-writer package has a tcl level api that can be used to either generate pl/dml or execute it. One thing that is lacking in my package is an automatic way of getting the tables and attributes loaded. Maybe Luke could figure out this part and load the data, which could then be edited to correct any differences. These calls could be used in Luke's file to execute the pl or dml available.

I'm now wondering if I should commit this package to the repository (Given the authority I could do it tonight), or if, as Luke has done, upload the .apm to the file storage?

Posted by Don Baccus on
Ahh, I see, Luke, this makes perfect sense with non-objects.

I'll have to take a look ... yes, avoiding ad_page_contract is unfortunately the right thing to do (until we figure out how to integrate form building with ad_page_contract, there's no reason we can't).  "self-submission" and a single form to create and edit get messy but have the advantage of only requiring one set of "element" calls to be generated.  So if you want to change the form you only have to edit one piece of code to do it.

But it does get messy.  A tool like this can help and integration with ad_page_contract would (*will*!) help, too.

Tom ... let's wait until after beta, at least, to put your package in?

Posted by Tom Jackson on

Okay, that's what I was hoping to do anyway, I'm just itching to download the beta and verify my package installs in it properly.