NAME
HTML::FormEngine::DBSQL - create html/xhtml forms for adding, updating and removing records to / in / from sql database tables
DEPENDENCIES
Perl Version
5.004Standard Modules
Carp 1.01Nonstandard Modules
HTML::FormEngine 0.7.2
Clone 0.13
Hash::Merge 0.07
Locale::gettext 1.01REQUIREMENTS
Currently the only supported DBMS is PostgreSQL (see below for
information on how to add support for your favorite DBMS).
You'll need to create a view (column_info), this view supports
information about the columns of the database tables. Please see the
POD of PGSQL.pm for more information!SYNOPSIS
Example Code
#!/usr/bin/perl -w
use strict;
use HTML::FormEngine::DBSQL::PGSQL;
use DBI;
use CGI;
#use POSIX; #for setlocale
#setlocale(LC_MESSAGES, 'german'); #for german error messages
my $q = new CGI;
print $q->header;
my $dbh = DBI->connect('dbi:Pg:dbname=test', 'test');
my $Form = HTML::FormEngine::DBSQL::PGSQL->new(scalar $q->Vars, $dbh);
my %preconf = (
phone => {
SIZE => [[5,13]],
SUBTITLE => [['', ' / ']],
}
);
$Form->dbsql_preconf(\%preconf);
$Form->dbsql_conf('user');
$Form->make();
print $q->start_html('FormEngine-dbsql example: User Administration');
if($Form->ok) {
if($_ = $Form->dbsql_insert()) {
print "Sucessfully added $_ user(s)!<br>";
$Form->clear;
}
}
print $Form->get,
$q->end_html;
$dbh->disconnect;Example Database Table
Execute the following (Postgre)SQL commands to create the tables I used when developing the examples:
CREATE SEQUENCE user_uid_seq;
CREATE TABLE "user" (
uid integer DEFAULT nextval('user_uid_seq'::text) NOT NULL,
name character varying(40) NOT NULL,
forename character varying(40) NOT NULL,
street character varying(40) NOT NULL,
zip integer NOT NULL,
town character varying(40) NOT NULL,
email character varying(40) NOT NULL,
phone character varying(15)[] DEFAULT '{{,}}',
birthday date NOT NULL,
newsletter boolean DEFAULT 't'
);
ALTER TABLE ONLY "user"
ADD CONSTRAINT user_pkey PRIMARY KEY (uid);
COMMENT ON COLUMN "user".zip IS 'ERROR=digitonly;';
COMMENT ON COLUMN "user".email IS 'ERROR=rfc822;';
COMMENT ON COLUMN "user".phone IS 'ERROR_IN={{{not_null,digitonly},{not_null,digitonly}}};SUBTITLE={{,/}};SIZE={{5,10}};';
COMMENT ON COLUMN "user".birthday IS 'ERROR=date;';
CREATE TABLE login (
uid integer DEFAULT currval('user_uid_seq'::text) NOT NULL,
username character varying(30) DEFAULT '-' NOT NULL,
"password" character varying(30) DEFAULT '-' NOT NULL
);
ALTER TABLE ONLY login
ADD CONSTRAINT login_pkey PRIMARY KEY (uid);
ALTER TABLE ONLY login
ADD CONSTRAINT "$1" FOREIGN KEY (uid) REFERENCES "user"(uid) MATCH FULL ON UPDATE CASCADE ON DELETE CASCADE;
COMMENT ON COLUMN login.password IS 'TYPE=password;VALUE=;ERROR=regex;regex=".{5,}";errmsg="must have more than 4 chars!";';
COMMENT ON COLUMN login.username IS 'ERROR=dbsql_unique;';Of course you can use any other table(s) as well.
Example Output
This output is produced by FormEngine::DBSQL when using the example code and the example table and no data was submitted:
<form action="/cgi-bin/FormEngine-DBSQL/createuser.cgi" method="post">
<table border=0 align="center" summary="">
<tr>
<td colspan=3>
<table border=0 summary="">
<tr>
<td colspan=3>
<input type="hidden" name=uid value="" />
</td>
</tr>
<tr>
<td valign="top">Name</td>
<td>
<table border=0 cellspacing=0 cellpadding=0 summary="">
<tr>
<td valign="top">
<table border=0 cellspacing=0 cellpadding=0 summary="">
<tr>
<td></td>
<td>
<input type="text" value="" name="name" maxlength="40" size="20" /><br/>
</td>
</tr>
<tr><td></td><td style="color:#FF0000"></td></tr>
</table>
</td>
</tr>
</table>
</td>
<td style="color:#FF0000" valign="bottom"></td>
</tr>
<tr>
<td valign="top">Forename</td>
<td>
<table border=0 cellspacing=0 cellpadding=0 summary="">
<tr>
<td valign="top">
<table border=0 cellspacing=0 cellpadding=0 summary="">
<tr>
<td></td>
<td>
<input type="text" value="" name="forename" maxlength="40" size="20" /><br/>
</td>
</tr>
<tr><td></td><td style="color:#FF0000"></td></tr>
</table>
</td>
</tr>
</table>
</td>
<td style="color:#FF0000" valign="bottom"></td>
</tr>
<tr>
<td valign="top">Street</td>
<td>
<table border=0 cellspacing=0 cellpadding=0 summary="">
<tr>
<td valign="top">
<table border=0 cellspacing=0 cellpadding=0 summary="">
<tr>
<td></td>
<td>
<input type="text" value="" name="street" maxlength="40" size="20" /><br/>
</td>
</tr>
<tr><td></td><td style="color:#FF0000"></td></tr>
</table>
</td>
</tr>
</table>
</td>
<td style="color:#FF0000" valign="bottom"></td>
</tr>
<tr>
<td valign="top">Zip</td>
<td>
<table border=0 cellspacing=0 cellpadding=0 summary="">
<tr>
<td valign="top">
<table border=0 cellspacing=0 cellpadding=0 summary="">
<tr>
<td></td>
<td>
<input type="text" value="" name="zip" maxlength="" size="20" /><br/>
</td>
</tr>
<tr><td></td><td style="color:#FF0000"></td></tr>
</table>
</td>
</tr>
</table>
</td>
<td style="color:#FF0000" valign="bottom"></td>
</tr>
<tr>
<td valign="top">Town</td>
<td>
<table border=0 cellspacing=0 cellpadding=0 summary="">
<tr>
<td valign="top">
<table border=0 cellspacing=0 cellpadding=0 summary="">
<tr>
<td></td>
<td>
<input type="text" value="" name="town" maxlength="40" size="20" /><br/>
</td>
</tr>
<tr><td></td><td style="color:#FF0000"></td></tr>
</table>
</td>
</tr>
</table>
</td>
<td style="color:#FF0000" valign="bottom"></td>
</tr>
<tr>
<td valign="top">Email</td>
<td>
<table border=0 cellspacing=0 cellpadding=0 summary="">
<tr>
<td valign="top">
<table border=0 cellspacing=0 cellpadding=0 summary="">
<tr>
<td></td>
<td>
<input type="text" value="" name="email" maxlength="40" size="20" /><br/>
</td>
</tr>
<tr><td></td><td style="color:#FF0000"></td></tr>
</table>
</td>
</tr>
</table>
</td>
<td style="color:#FF0000" valign="bottom"></td>
</tr>
<tr>
<td valign="top">Phone</td>
<td>
<table border=0 cellspacing=0 cellpadding=0 summary="">
<tr>
<td valign="top">
<table border=0 cellspacing=0 cellpadding=0 summary="">
<tr>
<td></td>
<td>
<input type="text" value="" name="phone[0][0]" maxlength="15" size="5" /><br/>
</td>
</tr>
<tr><td></td><td style="color:#FF0000"></td></tr>
</table>
</td>
<td valign="top">
<table border=0 cellspacing=0 cellpadding=0 summary="">
<tr>
<td> / </td>
<td>
<input type="" value="" name="phone[0][1]" maxlength="15" size="13" /><br/>
</td>
</tr>
<tr><td></td><td style="color:#FF0000"></td></tr>
</table>
</td>
</tr>
</table>
</td>
<td style="color:#FF0000" valign="bottom"></td>
</tr>
<tr>
<td valign="top">Birthday</td>
<td>
<table border=0 cellspacing=0 cellpadding=0 summary="">
<tr>
<td valign="top">
<table border=0 cellspacing=0 cellpadding=0 summary="">
<tr>
<td></td>
<td>
<input type="text" value="" name="birthday" maxlength="10" size="10" /><br/>
</td>
</tr>
<tr><td></td><td style="color:#FF0000"></td></tr>
</table>
</td>
</tr>
</table>
</td>
<td style="color:#FF0000" valign="bottom"></td>
</tr>
<tr>
<td valign="top">Newsletter</td>
<td>
<select size="1" name="newsletter">
<option value="1">Yes</option>
<option value="0">No</option>
</select>
</td>
<td style="color:#FF0000" valign="bottom"></td>
</tr>
<tr>
<td colspan=3> </td>
</tr>
</table>
</td>
</tr>
<tr>
<td align="right" colspan=3>
<input type="submit" value="Ok" name="FormEngine" />
</td>
</tr>
</table>
</form>DESCRIPTION
DBSQL.pm is a exentsion of HTML::FormEngine, that means it inherits all functionality from HTML::FormEngine and adds some new features.
In web development, form data is mostly used to update a database. For example most guestbooks or any similar webapplications store the entered data in a database. Often very large forms are needed, e.g. when the user should provide his personal data to subscribe to an certain service.
In most cases a SQL database is used. If you don't know anything about SQL databases or you're not using such things, this module will hardly help you. But if you do, you'll know that every record, that you want to store in a certain SQL database table, has to have certain fields and these fields must contain data of an certain type (datatype). So the tables structure already defines how a form, that wants to add data to this table, might look like (in case that you don't want to process the whole data before adding it to the table).
DBSQL.pm reads out the tables structure and creates a form definition for HTML::FormEngine.
Two examples:
a field of type boolean will only accept 0 or 1, this is represented in the form as 'Yes' or 'No'.
a field of type VARCHAR(30) will accept strings of maximal 30 characters, so it's represented as an one-line-text-input-field in which you can put maximal 30 characters.
Of course you can fit the resulting form configuration to your needs, but you don't have to!
DBSQL.pm also provides methods for adding and updating records. So you don't have to deal with sql commands.
Provided that you have HTML::FormEngine and all its dependencies installed, DBSQL.pm still won't be a working module. Its only a template from which other modules shall inherit. These childs (drivers) add DBMS specific features to the general DBSQL.pm module, but they don't change DBSQLs API, so the here explained things are valid for all drivers. Currently the only supported DBMS is PostgreSQL, but it shouldn't be difficult to add support for other DBMSs, e.g. MySQL. If you're interested in this task, see below for further information.
OVERVIEW
You have to use the right module (driver) for your DBMS. Currently the only available is PGSQL.pm which supports PostgreSQL.
We expect that you know how to use HTML::FormEngine, if not, please first read its documentation. Using HTML::FormEngine:DBSQL isn't much diffrent: the conf method is replaced by dbsql_conf and you may pass a database handle as second argument to the new method, using dbsql_set_dbh is possible too. Before calling dbsql_conf, you may call dbsql_preconf for setting some variables by hand.
To dbsql_conf you pass the tables name and optionally a where condition (for updating records) and/or an reference to an array with fieldnames (for setting explecit which fields to show resp. not to show).
USING FormEngine::DBSQL
Configuring The Form Through The Database
datatype handlers
In DBSQL::DtHandler.pm you'll find all DBMS independent datatype handlers. The PostgreSQL specific datatype handlers are placed in DBSQL::PGSQL::DtHandler.pm. Which handler to use for which datatype is defined in DBSQL::PGSQL::Config.pm. If for a certain datatype no handler is defined, the default handler will be called.
A handler creates the main part of the form field configuration.
You can easily add your own datatype handlers (see below).
array handling
Though the idea how to store arrays is taken from PostgreSQL, this should work with any other DBMS too!
In PostgreSQL every datatype can be arrayed. PostgreSQL arrays have the following structure: '{firstelem,secondelem}', a two dimensional array looks like this: '{{first,second},{third,fourth}}'. The problem is that PostgreSQL arrays don't have a fixed size, but FormEngine::DBSQL need such to represent the array in the form. Here we use a trick: the size which should be represented in the form is determined by the default value. So an field with '{,}' as default value will be represented as an one dimensional array. Of course you can put values between the commas, which will then printed as defaults.
NOT NULL fields
The form value of fields which have the NOT NULL property will be automatically passed to the not_null check method. This means that their ERROR variable will be set to not_null.
If the ERROR variable was already set set through dbsql_preconf, nothing will be changed. If the variable was set through the fields comment (see below), the not_null check will be added in front.
If you called dbsql_set_show_default and committed false (0), the above described will only be done if the field hasn't a default value.
assigning FormEngine variables in the database
PostgreSQL offers to set comments on database objects. This feature can be used to explicitly set form field variables in the database.
You might e.g. want to store emailadresses in a certain field of an database table, it makes sense to validate an address before inserting it. First possibility is to use dbsql_preconf to set the ERROR variable to 'email' or 'rfc822', but perhaps you've more than one script which inserts or updates the table and so you're using several forms. In every script you now have to call the dbsql_preconf method and set the ERROR variable for the email field. This isn't nice, because the necessity to check this field is given by the table structure and so the check should also be set by the database. You might set a check constraint, but this will cause an ugly database error which the user might not understand. So beside defining an constraint (which is recommended), FormEngine::DBSQL should check the address before inserting it. Setting the database fields comment to 'ERROR=rfc822;' will force FormEngine::DBSQL to do so. You can still overwrite this setting with dbsql_preconf.
Below you see the whole command:
COMMENT ON COLUMN "user".email IS 'ERROR=rfc822;'Whenever you pass this tables name to the new method of FormEngine::DBSQL, it'll remember to call the rfc822 check method before inserting oder updating an email field value.
You can even assign array structures to an variable:
COMMENT ON COLUMN "user".phone IS 'ERROR_IN={{{not_null,digitonly},{not_null,digitonly}}};';The phone field is an string array, with the above command we forbid NULL values and demand digits for the first two elements. More about arrays and their representation in the form is described above.
It is possible to assign several variables:
COMMENT ON COLUMN "user".zip IS 'ERROR=digitonly;TITLE=Postcode;';Don't forget the ';' at the end of every assignment!
Of course you can still use the comment field to place normal comments there as well:
COMMENT ON COLUMN "user".birthday IS 'We\'re really a bit curious!;ERROR=date;';Note the ';' at the end of the trivial comment!
In quoted areas ("..") '{', '}' and ',' are not interpreted. You can prevent the parsing of '"' and ';' by putting an '\' (backslash) in front.
Methods
dbsql_preconf ( HASHREF, PREPEND, APPEND )
In the referenced hash, you can predefine some parts of the form configuration by hand. The hash keys must be named after the tables fields. Every element must be a hash reference, in the referenced hash you can set variables.
You can use the special keys prepend and append to add extra fields before or after the field.
An example:
my %preconf = (
name => {
TITLE => 'Fore- and Surname',
ERROR => sub {$_ = shift; m/\w\W\w/ ? return 0 : return 'failed';}
},
email => {
TITLE => 'Your Emailadress',
ERROR => 'email'
}
);
$Form->dbsql_preconf(\%preconf);With PREPEND and APPEND you can define extra fields which will then be added to the top resp. the bottom of the generated form. If you want to add more than one field, you have to reference the array which contains the definitions, else you can reference the hash directly. See the POD of HTML::FormEngine for information about field definitions.
Note: If you pass more than one table name to dbsql_conf, you must reference the fields with tablename.fieldname!
dbsql_conf ( TABLENAME|ARRAYREF, [ COUNT|WHERECONDITION|HASHREF, FIELDNAMES|HASHREF ] )
Creates an FormEngine-form-definition and calls FormEngines conf method.
Normally you only want to manage records out of one table, then it is sufficient to give this tables name as first argument. But you can also pass several table names by using an array reference.
If you provide COUNT, the form fields will be displayed COUNT times, which means that you can insert COUNT records.
If you want to update records, you should provide WHERECONDITION instead. This must be a valid where-condition without the 'WHERE' directive in front, or a hash reference. A hash reference you must provide if you passed several tablenames and want to define diffrent where conditions for theses tables. The keys must be the table names and the elements the appendant conditions.
DBSQL then shows input fields for every found record and uses the current values as defaults. The primary keys are stored in hidden fields, so that they can't be changed. Later they're used for updating the records.
If you'd like to set only some of the tables fields, put their names in an array and pass a reference to that as third and last argument (FIELDNAMES). If the first array element is '!', all fields which aren't found in the array will be displayed. You must use a hash reference here, if you passed more than one table name.
dbsql_update
This method can only be used if a where-condition was passed to dbsql_conf. It updates the found table records to the submitted values. If an error occurs, the update statement and the DBMSs error message and number are printed. If you don't want all or some of this information be displayed, see dbsql_set_sqlerr.
Normally you must have defined a secret string if you want to use this method, else an error message will be printed. See dbsql_set_secret for more information.
dbsql_insert
This method inserts the transmitted data into the table. If an error occurs, the insert statement and the DBMSs error message and number are printed. If you don't want all or some of this information be displayed, see dbsql_set_sqlerr. Before calling this method, you should prove that the form content is valid (see FormEngines ok method).
dbsql_set_dbh ( DBH )
Use this function to set the internally used database handle. If you don't call this funtion, you must set it when creating the object with the new method.
dbsql_set_hide_pkey ( BOOLEAN )
By default the primary key fields are represented as hidden form fields. This makes sense, because when updating records they mustn't be changed. Sometimes, especially when inserting new records, one might want to set them by hand. Then he should pass false (0) to this method.
Passing false to this method will also disable the primary key md5 checksum check when calling dbsql_update. This means that it'll be allowed to change the primary keys even when updating records. By default this is not allowed for security reasons. So be carefull with this method!
You can as well define the template by hand using dbsql_preconf.
dbsql_set_show_value ( BOOLEAN )
When you pass a valid where clause to the new method, the contents of the found records will be read in and displayed as defaults. In certain situations, one might like to have the fields empty though. Passing false (0) to this method will do it.
dbsql_set_pkey ( SCALAR|ARRAYREF|HASHREF )
Normally the primary key of an database table is autodetected. Sometimes someone might like to define other fields as primary key though (the primary key is important when updating records). You can pass a fieldname or an reference to an array with fieldnames to this method. This method should be called before dbsql_conf (for being sure, call this method as early as possible).
Note: If you pass several table names to dbsql_conf, you must pass as hash reference here, else the passed pkeys will only be used for the first table.
dbsql_set_show_default ( BOOLEAN )
In most DBMSs, you can define a default value for each field. Normally, if you don't submit a where clause, this value is read out by FormEngine::DBSQL and displayed as default value in the complying form field. One might then change the value in the form and even remove it completly, so that undef is transmitted. This will have the effect, that when inserting or updating records, NULL is set for this field (and not the defined default value). This is just logical, because the user transmitted NULL for the field.
Perhaps you better don't want to display this default value, but like to have it set in the database if undef was submitted for the field.
Passing false (0) to this method will cause the object to not display any database defaults and to not pass undefined (NULL) field values to the database when executing an INSERT. So, when inserting new records in a database table, the default values will be set instead of the submitted NULL values.
Note: When updating records, empty field values will cause passing NULL to the database, independ of wether this method was called or not!
dbsql_set_errmsg_templ ( TEMPLATENAME )
If you want to modifiy the output of the system error messages, create a new template (e.g. copy the default and fit it to your needs) and pass the new templates name to this method. By default errmsg of the DBSQL skin is used.
dbsql_set_sqlerr ( INTEGER )
Perhaps you already read, that whenever a database error occurs, the error message, error number and query command is printed out by default. Sometimes you might prove displaying the sql query a security lack. With the help of this method, you can define which information will be printed.
Listing of the bits and their influence:
1 error number
2 error message
4 sql command
So if you pass 3 to this method the error number and message will be printed, but not the sql command.
dbsql_set_sqlerr_templ ( TEMPLATENAME )
If you want to modifiy the output of the sql error messages, create a new template (e.g. copy the default and fit it to your needs) and pass the new templates name to this method. By default sqlerror of the DBSQL skin is used.
dbsql_set_secret ( SECRET )
If you want to update records, you can use the dbsql_update method. That method uses the given values of the primary key to create where conditions, so that the right records are updated. The weak point is, that someone could corrupt the input data, so that the primary key values are changed and the wrong records are updated. To prevent this, for every record a extra hidden field is created which contains the md5 sum of the primary key concatenated with a secret string. So it is recognized if a primary key value was changed (because the newly created md5 sum won't match the submitted md5 sum).
With this method you can set the secret string. By default it is set to NULL, which means that calling dbsql_conf will raise an error. For security reason an update isn't allowed without a secret string, except you pass false (0) to dbsql_set_hide_pkey, which will allow changing the primary key and so no secret string will be needed.
Another possibilty is changing the value of $secret in Config.pm and so set a valid default secret string. But be careful, someone might just edit Config.pm and so get the secret string, whereas using diffrent keys in your scripts is much more secure.
It is recommended that you set the read permissions of scripts, which define secret keys, as restrictive as possible. For cgi scripts this means, that only the webserver user (mostly nobody oder www-data) must be able to read them.
dbsql_set_row ( BOOLEAN )
If you provided a where-condition and more than one record was found, or you provided a number instead and it was higher than 1, then by default it'll be used only one line per record.
With this method you can force the object to use one line per field (0) or to use one line per record (1).
dbsql_set_row_tmpl ( TEMPLATENAME )
By default the row template is used. If you want to use another template for placing the fields which belong to one record into one line, pass it to this method.
dbsql_set_empty_tmpl ( TEMPLATENAME )
By default the empty template is used. If you want to use another template for inserting space between the records, pass it to this method. This space is only inserted if every field takes one line.
dbsql_get_sqlerr
This method returns an array with the error number and error message from the last database error. The sql command which causes the error will be the third and last element.
dbsql_add_extra_sql(SQLCOMMAND, ARRAY)
This method can be used to define some more sql commands which then will be executed for each record when insert or <update> is called.
The sql command might contain '?' (question marks). These will be replaced with the current values of the fields defined by the second argument. The first '?' is replaced with the value of the first element and so on.
A backslash before an question mark will prevent it from being parsed.
EXTENDING FORMENGINE::DBSQL
Add Support For Another DBMS
Please have a look at PGSQL.pm and its POD.
Write A Handler For Another Datatype
First you have to decide wether this handler will be DBMS independent or not. Then have a look at DtHandler.pm and PGSQL::DtHandler.pm (which is a good example for DBMS dependent handlers).
Suiting the Layout
For this task, you should create a new skin. For general information about FormEngine skins, look at the POD of FormEngine.pm and its submodules. Then also look at DBSQL::Skin.pm, the templates which are defined there are necessary for DBSQL.pm and you should at least implement replacements for them in your new skin. At last you can make your new skin the default for DBSQL.pm by editing DBSQL::Config.pm.
MORE INFORMATION
Have a look at ...
the POD of DBSQL::DtHandler.pm for information about writing datatype handlers.
the POD of DBSQL::PGSQL.pm for information about adding support for another DBMS.
DBSQL::Skin.pm for information about the DBSQL.pm specific templates.
BUGS
Send bug reports to: moritz@freesources.org
Thanks!
AUTHOR
(c) 2003, Moritz Sinn. This module is free software; you can redistribute it and/or modify it under the terms of the GNU General Public License (see http://www.gnu.org/licenses/gpl.txt) as published by the Free Software Foundation; either version 2 of the License, or (at your option) any later version.
This module is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
GNU General Public License for more details.I am always interested in knowing how my work helps others, so if you put this module to use in any of your own code then please send me the URL. Also, if you make modifications to the module because it doesn't work the way you need, please send me a copy so that I can roll desirable changes into the main release.
Address comments, suggestions, and bug reports to moritz@freesources.org.
SEE ALSO
HTML::FormEngine by Moritz Sinn
HTML::FormTemplate by Darren Duncan