SQLEdit: a WebGUI W(eb)object.

Current version is: 06.03.03/2005-01-25 (beta)

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.

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:

	root@devel# mysql -p WebGUI

	mysql> CREATE TABLE `test_anagr` (
		`id` INT NOT NULL AUTO_INCREMENT ,
		`name` VARCHAR( 255 ) NOT NULL ,
		`birthday` DATE NOT NULL ,
		`sex` ENUM( 'M', 'F' ) NOT NULL ,
		PRIMARY KEY ( `id` )
		);

Now open WebGUI site in administrative mode and add a new page, call it test and set "Cache Timeout" and "Cache Timeout (Visitors)" property to 0. Add it an SQL Report.

Now add this string in the description field (if you are using Internet Explorer enter in code-mode)

and enter this query in query field

Now, switch to layout tab.

If you have WebGui 5.x, enter this code directly in the report template field:

	<table border=1>
	^-;
	<tr><td>^0;</td><td>^1;</td>
	<td><a href="test_edit?exec=edit&id=^0;">Edit</td>
	<td><a href="test_edit?exec=delete&id=^0;">Delete</td>
	^-;
	</table>

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:

	<tmpl_loop rows_loop>
	<tr>
	<tmpl_loop row.field_loop>
	<td class="tableData"><tmpl_var field.value></td>
	</tmpl_loop>
	<td><a href="test_edit?exec=edit&id=<tmpl_var row.field.id.value>">Edit</td>
	<td><a href="test_edit?exec=delete&id=<tmpl_var row.field.id.value>">Delete</td>
	</tr>
	</tmpl_loop>

Use this new template as the template for our SQLReport element.

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

1 => Visitor
3 => Admin

You can also "lookup"an external database using my SQLExt WebGui macro.

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