SQLEdit: a WebGUI W(eb)object.

Announce: I received many requests about a
new version of SQLEdit for latest version of WebGUI that is a conversion of this
Wobject in the new format of WebGUI objects:
Assets

This module has
been developed in my free time and although this page have, at the bottom, a
modulo to make a voluntary donation to support this project, I received nothing.
Unfortunately I have, in the near future, no free time and so I must to consider
this update request as a fund-a-feature.

So, when I reach, starting from today May, 2 2005, the amount of at least 100
EUROS of donation for this project I'll begin the conversion of this module to
WebGUI 6.5.x.

Do you need of this module for WebGUI 6.5.x? Donate
5 €,
10 €,
20 €,
100 € and send me your e-mail. When I reach 100 EUROS of donation, you will
be notify of the start and release of a new version of SQLEdit for WebGUI. Help
me to help you.

When I saw WebGUI for the first time I was very impressed about its
simplicity to create website. When I looked at the programming mode under WebGUI,
I made pleasantly an impression of the simplicity of building of WebGUI plugins,
also called Wobject.

To that time, I was looking for a content managment system to give to
inexperienced users that have necessity to manage a complex website. I found
that WebGUI was the correct solution. This website must have the necessity to
access some database tables and WebGUI replies me with SQLReport Wobject.

The problem raised when website manager asked me for having the possibility
to modify database records. WebGUI doesn't offer this feature, and so, I have
had built it.

What's SQLEdit?

SQLEdit is a W(eb)object for WebGUI (http://www.plainblack.com/webgui)
one of the most popular open source content management systems (CMS).
Currently supports WebGUI release 5.5, 6.0, 6.1 and 6.2 under Linux
and Windows. SQLEdit fully supported MySQL database.

Hot news: Starting from 06.03.01, SQLEdit
supports Microsoft® SQL Server database. This support is currently in
beta and may be some bugs or unsupported features. As an example, binary data
via upload, shouldn't work.

All these tests have been made with Microsoft® SQL Server 2000 under Windows
2000.

This module allows to build a web form to add, to edit and to
delete data from database tables using the WebGUI philosophy to
generate it.

By giving the database link property (DSN,username and password) and a table
name, this module builds a web form with all table columns by trying to set the
best field type. As an example, if a column is of type "date", a field with a
calendar button and javascript to validate date input in this field, will be
placed on form, if a column is an auto_incremental column, i.e. automatically
its numerical value increases when new records is added, a visible and not
writable field will be placed on web form.

However, properties of web fields can be changed by content manager so, e.g.,
you can change a text field into a select list field to force users to enter
data selecting it from some possible values.

Layout of web form generated by this module is personalized because it uses
templates, following WebGUI philosophy.

Key features

With SQLEdit Wobject you can

Easy create database web forms.

Insert, edit and delete records from a database table.

Upload files into BLOB fields

Modify and delete records from one-to-one or one-to-many related tables with
foreing keys.

Personalized layout of every columns table, setting visibility and HTML Form
field module type.

Check and restriction for possible values of fields assignable by users

Notification, via e-mail, of changes in the database records.

How
to install.

Take a look at
docs/SQLEdit/install.txt file for detailed installation instructions.
Remember that to use this module (and SQL Report standard module too) it's
necessary to be an user with UId Level 9 (guru) otherwhise this module doesn't
show in the available modules.

How to use.

Let's show how to use SQLEdit with some examples.

First of all, create a new table in WebGUI database. We can use tables in
whatever database, but now, for semplicity, we use the same database where are
WebGUI tables.

Execute this from a shell console to create a new table called test_anagr to
WebGUI database:

If you have WebGUI 6.x, you must create a new template. Click on "manage"
button of the template, copy default SQLReport template and named "SQLReport test_anagr"
the new template. Edit this template so that the <tmpl_loop
rows_loop> is as:

Obviously in every case, the edit and delete link
must be adapted to your table and they must be
such to having all primary keys with their relative values of the record you
wish to edit or to delete.

Switch to privileges and enter DSN,username, password to WebGUI database or
select it from database link field, if available.

After save you should have something like this:

Untill now, nothing strange, we have create an SQLReport that lists all "id"
and "name" columns presents in "test_anagr" table.

It's now time to create test_edit.

Add a new page to WebGUI and call it test_edit, set "Cache Timeout" and
"Cache Timeout (Visitors)" property to 0. Now add an SQLEdit Wobject to
this page. Let's go directly to the priviliges tabs that looks like this:

Here select the same database connection insert in SQLReport. As you can see,
there is another field: "table name". Here you must set the name of table that
you with to edit. You can type it now or you can select it from a list of
database tables if you have a correct DSN database link. Enter only the correct
database link, save it and then edit again the Wobject, you can see this list.

select test_anagr and press save (module can warn you that it's required to
reload database schema. Press Yes to reload it). If you see this....the module
works :-)

If you come back to "test" page and try to add a new record, edit and delete
records you can see that all works fine.

Add a new record:

To "save and come back" you must enter the referer page in the Wobject
property tab. Set it to the page that have SQLReport

Save and come back:

now edit this record

and we can delete it from this page or from previous page

If you "save and continue to edit" you continue to edit current record after
save it. If you "save and come back" you come back to test page after save
current record. If you "save and add new" you save current record and go to a
new page for adding a new record.

As you can see SQLEdit try to understand fields type from database schema. Let's
show how to change this. Press the edit button at left of sex field. We can give
more meaning to "M" and "F" database keys in this way:

As you can see, you can choose in which kind of order, fields possible values
will be listed: preserving input order, ordered by key or by values.

We can, if you wish, change fields type and label:

You can also use macros to set possible values and default value for fields. To
set possible values is important that result macro returns values in format "key
=> value\n" or "key\n".

As an example you can configure a field like this (using some programmer
macros)

and having, as result, something like this:

Macros can be used also in "default value" field. In the example above, the selected
item will depend from your login name.

Note that the newline before the end of SQL macro is very important because,
with it, the result of the macro will be

For every field it is moreover possible to set up several check or
restriction for the possible values assignable by users. It's possible, as an
example, to assign the minimal and/or maximum number of characters, the range of
valid characters, the range of valid values. All this with an easy configuration
page.

In the manual you can find how i can create one-to-one or
one-to-many relation tables editing/deleting records.

To DO.

Permit to send a different email when new records have been added or
records are updated or deleted

Binary uload file on Microsoft® SQL Server

"Come back" feature doesn't work on Windows

PostgreSQL database support

Make a donation

This free software has been developed during my spare time, and therefore, during
some nights lost to develop this for you instead sleeping. If you found this
product useful, feel free to send me a donation. You will be remember in my
prayers and you will help my soul to feel less alone during long the long nights of coding.
Of course, if you make a donation and have a feature request, I'll try to place
your feature with a high priority.

You can fast and free donate using a credit card, bank account, or existing
Paypal balance. After you push the button, you'll be taken to Paypal's
secure website, where you'll supply a username and password (if you already have
a Paypal account) or you'll
sign up
for an account (it's easy). You get to choose the amount you'd like to donate. Once you've
authorized the transaction, you're done. Paypal does take a cut of what you send
(so if you send me $20, I get $19.12; if you send $5, I actually get $4.55; if
you send me $3, I get $2.61).

Allow to insert in your web pages and, however, to download, binary or textual data stored in database columns of BLOB type. These data can be of whatever type such as images, pdf documents, autocad files, etcetera.

The ideal solution to realize and update web contents in a collaborative environment or anyhow from whom is 'not assigned to the jobs' requires the use of a content management system. Of these environments we'll analyze the salient characteristics for dealing therefore the main installation and property of one of theirs: WebGUI.

A versatile content managment system (C.M.S.) to simply build dynamical web site. Written in Perl, it generates dynamical content from templates using the HTML::Template module. It represents a simple framework to give also to who knows nothing about HTML.