Log in

SQL Blender

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.

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.

Copyright (c) 2011 Zope Foundation. All rights reserved.
Legal | Contact
If you can read this text, it means you are not experiencing the Plone design at its best.
Plone makes heavy use of CSS, which means it is accessible to any internet browser,
but the design needs a
standards-compliant browser to look like we intended it.
Just so you know ;)