Zope Products

SQL Blender

SQL Blender is a form generating tool that uses a database connection to learn about your schema and automagically build useful forms and methods.

History

One of the earliest form generating tools for Zope is the P SQL Input Wizard. After a multipage input form, asking what tables and columns you are interested in, it generates a usable input form and Z SQL Methods. This was expanded to the Znolk SQL Wizard which added some additional features. Both of these Products provide a way to generate a simple form relatively quickly, but suffer the same flaw: The entry pages are somewhat protracted and unforgiving; if an error is made in selecting columns, you need to start over or manually edit.

Behold the SQL Blender!

The SQL Blender Product was designed with these goals in mind:

• Make it fast.
• Make it easy.
• Make it forgiving.

With one form, SQL Blender is able to interrogate your database and build all the forms and Z SQL Methods you need to list, display, and edit records for every table in your database. It will also massage the column labels if you want. And if you need to make changes, the SQL Blender is a persistent object you can edit to add more columns or computed fields, or even JOIN conditions.

SQL Blender works best with a Database Adapter that supports a table browser. If your DA does not have a table browser, SQL Blender will be unable to determine your database's schema, but you can still create SQL Blenders and SQLB Columns manually. So far, the only tested DA is ZMySQLDA, but a number of others should work.

Starting with ZMySQLDA-2.0.8, a great deal of column information is stored in the table browser data, such as primary key, index, unique, NOT NULL, etc. This information is used by SQL Blender to implement various interesting features. Other DAs probably do not provide all this information yet, but can probably be easily modified to do so.

Features

• Generates Z SQL Methods

  SELECT

  Searchable on any column that is marked as an INDEX.

  UPDATE

  Updates based on the PRIMARY KEY, and only updates the columns which are passed in.

  DELETE

  Deletes based on the PRIMARY KEY.

  SELECT_XXX_options

  A special SELECT query that returns items suitable for doing a HTML SELECT ... OPTION dropdown box (for PRIMARY KEY XXX).

• Generates DTML Methods

  list

  Produces a listing of all rows in the table. Columns with a FOREIGN KEY relationship are hyperlinked to the corresponding row in that table. The PRIMARY KEY is linked to display that row alone in a vertical format.

  edit

  An HTML form for updating or adding new records. If any of the columns have FOREIGN KEY references, you automatically get a SELECT box with the legal values.

  select_XXX

  The method for generating the SELECT box HTML.

  even/odd

  With one checkbox, you can generate different code for even and odd rows.

No form-generating product is going to be flexible enough to cover every possible contingency. Even with SQL Blender, you should expect to do some editing to get things where you want them.

Caveats

• If you have columns with the same names as some of your tables, that can confuse things. In this case, you can alias your column to a unique name.
• Security: This adds NO security whatsoever at this point. You'll have to figure out for yourself if the generated output is secure, and if not, how to secure it.
• SQL Blender is still under active development, but works well enough for an initial release. User feedback is solicited to help guide future development.
• Zope-2.4 is broken with respect to Z SQL Method arguments; to be specific, the parser barfs if you supply a default value, and optional values in dtml-sqltest elements... aren't. I have two patches: One modifies Aqueduct to fix the default value problem, and allow you to specify that a value is optional in the arguments; the other (gleaned from the collector) is a useful enhancement that implements dtml-comma, and allows set as a dtml-sqlgroup type. You need to install these, or SQL Blender won't work. Don't ask me how to install these; if you can't figure it out, wait for a future release.

Planned Features

• Pluggable Brain support
• User-sortable listings, based on known column indices
• Role support
• Some more helper methods
• More documentation