The SQLForm asset allows you to dynamically create data input and storage functionality in your site. All data is put in a table of your choice in a database of your choice. An arbitrary form element can be tied to each field and input forms can be built in diffrent ways to ensure optimal adaptability to your needs.

The SQLForm features creation of new tables, import of existing tables and re-importing previously imported tables. Fields can be linked to other fields in other tables in serveral ways, making it possible to dynamically resolve id's to values using joins, add constraints to inputted data or connect different SQLForms together.

User input can also be checked against regular expressions and manual constraints that are definable by you. In addition it's possible to add an autoincrement flag and macros to fields, among other functionality.

All inputted data is versioned and a two level (delete/purge) trash is build in. Three privilege layers are available to split access to different actions.

Requirements: 

• You need WebGUI version 6.8.x.
• This software is built and tested for MySQL, using it on another database will probably fail!
  

Installation instructions:

Unpack the file in the docs directory of your webgui installation:

cd <webgui-root>/docs
tar xzf SQLForm-1.0.2.tar.gz

Then follow the instructions in the install.txt file.

 WARNING:

If you import a table into the SQLForm, it will drop its keys and add columns containing meta information to that table. This is not clear from the docs! Do not import tables with a critical key structure or which cannot have columns added.

Changelog:

 1.0.4

• Fixed some typos in i18n labels.
• Added POD.
• Fixed some privilege issues (Thanks to Lucas Bartholemy for pointing them out).
• Fixed a bug where blobs and chars would fatal error while importing a table (Thanks to Bas Jansen for for finding it).
• Fixed a bug where viewing a record with a date field would throw an error (Thanks to Bas Jansen for finding it).
• Fixed some persistency and related bugs in the search and supersearch (Thanks to Bas Jansen for finding them).
• Added functionality to copy records.
• Added a clear button to selectlists in edit/add record.

 1.0.3

• Updated meta.wg to correct value.
• Fixed some bugs in the i18n file and some labels in the code.
• Fixed a bug where joined selectlist were not shown in view mode.
• Added view option for users with edit rights.
• Added the Dutch and English manual that were accidentally not included in the first release.
  

Comments

isaac: "
This is great!  Thanks for filling the gap since ebruni kinda left us hanging!

 I'm pretty impressed with the features on this.  Is there somewhere to go to track development/request features?

"

martink: "For now you can post bugs and feature requests here on the board, or if
you'd like that better, you can email them to me: (martin(a
t)procolix.com).
"

isaac: "In the field management area, in 'Form Population' the object only lists databases that are actually in the current webgui database installation, not the databases from the database links.  Is this correct? it seems like a security hole, also this does not allow me to base the field population on the database that  the field is in, since I am connecting to another mysql instance.
"

martink: "
It will list all databases and tables on which the WebGUI db user
has at least select privs. I'm not quite sure anymore why I used the
WebGUI user and not the db link user for this, but I know I had a
reason. I'll give it some more thought.

As
a workaround you can give the webgui db user select privs on the tables
you want to include in the join. Then there's no problem at all.


This
is not a security issue though, since all databases in the join
selector are used just for joining. No write actions will be done to
any of these tables. Addionally, you only have to give select privs on
the tables you want to show up in the selector .

Risking to be
superflous, it is not possible to generate joins from diffrent db
handles. So it's not possible to put all db links in that list, and
join tables in those together. They must all be within the same
database server (they can be in diffrent databases though) and 
they must all be selectable by one user (in this case: the wg db
user).  
"

isaac: "Ok. I understand.  What I'm trying to do is have a field use a select list that is populated by a select distinct -fieldname- from the table that the SQLForm is pointed at. I can't since it's pointed at an external db.
"

martink: "As of now yhis is unsupported. You can only use tables in join that are
on the same server as the wg db, and on which you have select rights.
"

martink: "
Ok I remembered. The reason that you can only include tables in
joins on which the wg db user has select privs is that in the search
mode the values are resolved by joins. Since every field can have a
join associated with it, allowing diffrent dblinks for each field will
very probably make the join in the search fail.
"

yhkhoe: "
I would like the search template to be more templatable like the edit record template.

If
these lines are added to www_search at line 2834 then you can choose in the search
template between a complete form and seperate form elements/labels:

$var->{searchFormHeader} = WebGUI::Form::formHeader().

               
WebGUI::Form::hidden({name=>'func', value=>'search'}).

               
WebGUI::Form::hidden({name=>'searchType', value=>'or'});

        $var->{'searchFormQuery.form'} = WebGUI::Form::text({

                name=>'searchQuery',

                value=>$query

                });

        $var->{'searchFormQuery.label'} = $i18n->get('s query');

        $var->{'searchFormMode.label'} = $i18n->get('s mode');

        $var->{'searchFormMode.form'} = WebGUI::Form::radioList({

                name=>'searchMode',

                value=>$useRegex,

               
options=> {'normal' => 'Normal search', 'regexp' => 'Regex
search'},

                });

        $var->{'searchFormSearchIn.label'} = $i18n->get('s search in fields');

        $var->{'searchFormSearchIn.form'} = WebGUI::Form::checkList({

                name=>'searchIn',

                value=>\@searchIn,

                options=> \%searchableFields,

                });

        $var->{'searchFormTrash.label'} = $i18n->get('s location');

        $var->{'searchFormTrash.form'} = WebGUI::Form::radioList({

                name=>'searchInTrash',

                value=>$searchInTrash,

                options=> \%searchInTrashOptions,

                });

        $var->{searchFormSubmit}
= WebGUI::Form::submit({value => $i18n->get('s search button')});

        $var->{searchFormFooter} = WebGUI::Form::formFooter();

      


The template could then be like this:

<tmpl_var searchFormHeader>

<input type="hidden" name="searchMode" value="normal">

<table>

<tr>

<td><tmpl_var searchFormQuery.label></td><td><tmpl_var searchFormQuery.form></td>

</tr>

<tr>

<td><tmpl_var searchFormSearchIn.label></td><td><tmpl_var searchFormSearchIn.form></td>

</tr>

<tr>

<td></td><td><tmpl_var searchFormSubmit></td>

</tr>

</table>

<tmpl_var searchFormFooter>

"

martink: "
I'm not opposed to making the search form more templatable. However, the template you submitted does not work for advanced search (func=superSearch).

One option is, of course, possible to also make the individual field of the super search available to the template. But there are probably also diffrent (maybe better) solutions. Any suggestions?

"

yhkhoe: "
For now, maybe we could use a tmpl_if to switch between the complete
form for superSearch and a choice between the seperate form elements
and the complete form for the normal search.


I didn't really look at the superSearch yet. The reason i wanted to
make the search more templatable was so i could dumb down the
interface.

I will have a look to see if i can come up with
other solutions. But making the individual super search fields
available sounds allright to me.

I also found a bug in the code
I posted. The searchFormHeader needs an action. Otherwise something
goes wrong when the SQLForm is viewed inside a page Layout. The line
that creates the searchFormHeader should be:

$var->{searchFormHeader} = WebGUI::Form::formHeader({action     => $self->getUrl}).

               
WebGUI::Form::hidden({name=>'func', value=>'search'}).

               
WebGUI::Form::hidden({name=>'searchType', value=>'or'});

 

Yung 
"

raidro: "
The documentation of SQLForm looks very promising. Nevertheless, after playing around about 2 hours and debugging the module, the following result was coming out:


SQLForm is a very, very restrictive and exceptional database editing interface:
not all MySQL field types are availableprimary keys of existing tables are deleted! No, this is not correct, SQLForm tries to delete them, but SQLForm fails on  databases with foreign keysSQLForm can not handle InnoDB with foreign keys
additional columns are added for version management, etc., and modifies/destroys existing database schemas

Some hints:
what about integration of Alzabo as database backend?what about storing version management information in some additional tables?
"

martink: "not all MySQL field types are available
This is correct, but the frequently used field are there. I built this asset is such a way that additional field types can be added in a later stage, however.
primary
keys of existing tables are deleted! No, this is not correct, SQLForm
tries to delete them, but SQLForm fails on  databases with foreign keysThis is a design choice. Yes it will alter your table structure. That's one of the reasons why the webgui db is off limits. That it fails on tables with foreign keys is actually a bug. I thought this was mentioned in the docs, but I looked it up and it is not, which is of course a bad thing. I'll get the docs updated, but that can take a while so I'll put a warning notice in the description.
SQLForm can not handle InnoDB with foreign keysThat can very well be. I have not had the chance to test it on other table type than MyIsam. If you know what causes the problem, please let me know.
additional columns are added for version management, etc., and modifies/destroys existing database schemasThis is also by design.

Some hints:
what about integration of Alzabo as database backend?I never heard of it so I googled and it sure does look very cool. However, at first glance, integrating it in the SQLForm will mean a rewrite of a large part of the code, which is something I do not have time for. I will look into it a bit further though.
what about storing version management information in some additional tables?
When building the SQLForm I actually considered this, but I figured that the only way to do it is by normalizing tables. Which will make searching and connecting them to other tables very difficult. Also you'd need huge queries for viewing the history. Therefore I chose to include the meta information in the table itself.

 
"

robbert: "It would be nice to have a loop containing all the fields. Now you need to search, but I like to see all data added.
"