SQLEdit: a WebGUI W(eb)object.
Current version is: 06.03.03/2005-01-25 (beta)
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.
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.
Microsoft® SQL Server support has been tested with these DBD drivers:
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.
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.
With SQLEdit Wobject you can
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
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.
|Copyright© 1997-2006 Emiliano Bruni||Online from 16/08/1998 with visitors||Write me to:|