DBIx::HTMLinterface - Class for creating a CGI interface to a database
The simplest possible use of the script is shown below. The extremely small amount of code needed to get an editing system running is an indication of the level of automation available in the script.
use CGI; use DBI; require DBIx::HTMLinterface; $cgi = new CGI; $dbh = DBI->connect("DBI:mysql:[DATABASE]:[HOSTNAME]","[USERNAME]","[PASSWORD]") ); $DBinterface = new DBIx::HTMLinterface ($cgi, $dbh, "[INFOTABLE NAME]"); # Below here is only executed if a HTMLinterface action was not taken, so print a menu print $cgi->header; print "<A HREF=" . $cgi->url . "?HTMLinterface-table=[TABLENAME]&HTMLinterface-action=add>Add</A>\n<BR>"; print "<A HREF=" . $cgi->url . "?HTMLinterface-table=[TABLENAME]&HTMLinterface-action=search>Search</A>\n";
The DBIx::HTMLinterface class allows a CGI interface to a database. It enables a CGI interface to a database to be created, supporting record addition, modification, deletion and searching. It provides a user friendly interface with descriptions of fields provided. The field descriptions along with information on whether the field is visible, hidden or excluded are extracted from a table, allowing easy modification and addition of fields and tables without having to edit code.
DBIx::HTMLinterface
Forms are created automatically on demand, SQL statements are generated as needed and processed. The module contains enough autonomy to potentially run with only wrapper perl code placed around it.
Forms can be modified to add descriptions and extra information to fields, making it easy to change output without having to edit code.
Extensive callback procedures and configuration options allow output, password protection and logging to be configured as desired.
Someday I will finish this list...
Constructs a new DBIx::HTMLinterface object. You must pass a reference to a CGI object which will be used to get the script's parameters and a database handle (Returned from a DBI->connect function) which will be used to communicate with the database. The third parameter defines the name of the info table which is used to determine hidden/excluded fields, field names and descriptions as described below in INFOTABLE FORMAT. After initialisation, the CGI object is checked for a 'HTMLinterface-action' parameter. If this is present, control is taken from the script and the specified action is performed on the specified table. This parameter is set when an action which requires further processing is in progress.
DBI->connect
The final parameter, suppress paramcheck, is optional and when set to 1 will cause the script NOT to perform the parameter check. You MUST then call the check_params function in your code or 2-part forms will not work. Overriding the script in this way is not recommended.
Optional methods which can be called to directly jump to a script function, for example to directly initiate a delete or modify on a record.
# Check form parameters $DBinterface->check_params;
Causes the HTMLinterface-action parameter to be rechecked. If it contains the value 'add','modify','delete' or 'search', the respective function will be called ('exec_add','exec_modify','exec_delete' or 'exec_search'). this function is essential to the correct functioning of the interfaces with two and three part forms, and is called automatically when a HTMLinterface object is created, unless the 'suppress paramcheck' parameter is set to 1.
# Now set the 'HTMLinterface-table' parameter so the script knows # what table to deal with $cgi->param(-name=>'HTMLinterface-table',-value=>'mytable'); # Now call the function $DBinterface->exec_search;
Searches the table named in the CGI parameter 'HTMLinterface-table'. The user will be presented with a blank form with the fields of the table. They press submit to search the table (Wildcards can be used). They are then returned a table with a modify and delete button and the fields for each record found.
# Assume $cgi->param has been set to indicate the primary keys # for the table being modified, i.e 'Primary Key Name' = 'Primary # Key Value' # Now set the 'HTMLinterface-table' parameter so the script knows # what table to deal with $cgi->param(-name=>'HTMLinterface-table',-value=>'mytable'); # Now call the function $DBinterface->exec_modify;
Modifies a record from the table named in the CGI parameter 'HTMLinterface-table' where the CGI parameters which have the same name as a table column. For example for a table called 'data' with an 'ID' column containing the primary keys for that table, set the 'HTMLinterface-table' parameter to 'data' and set the 'ID' parameter to the ID number of the record you want to modify. The user will then be presented with a form containing the data in the table for them to modify. They then press submit to commit the data
# Now set the 'HTMLinterface-table' parameter so the script knows # what table to deal with $cgi->param(-name=>'HTMLinterface-table',-value=>'mytable'); # Now call the function $DBinterface->exec_add;
Adds a record to the table named in the CGI parameter 'HTMLinterface-table'. The user will be presented with a empty form containing just the defaults for the values of that table (Defined in the SQL). They then press submit to commit the data to the table.
# Assume $cgi->param has been set to indicate the primary keys # for the table being modified, i.e 'Primary Key Name' = 'Primary # Key Value' # Now set the 'HTMLinterface-table' parameter so the script knows # what table to deal with $cgi->param(-name=>'HTMLinterface-table',-value=>'mytable'); # Now call the function $DBinterface->exec_delete;
Deletes a record from the table named in the CGI parameter 'HTMLinterface-table' where the CGI parameters which have the same name as a table column. For example for a table called 'data' with an 'ID' column containing the primary keys for that table, set the 'HTMLinterface-table' parameter to 'data' and set the 'ID' parameter to the ID number of the record you want to delete.
This function will output a confirmation page requiring users to confirm the delete or press their browser's back button to cancel. To skip confirmation, set the 'confirm' parameter to 'Y'.
Optional methods which can be called to alter the behaviour of the script or enable features such as logging.
sub log_callback { my $description = shift; my $sql = shift; open (LOG,">>$logfile") print LOG "$description (Executing $sql)"; close(LOG); } $DBinterface->set_logcallback(\&log_callback);
Enables logging of SQL changes to the database via the user defined routine. The first parameter passed is a description, such as 'Record added to mytable' and the second parameter is the SQL statement which was used.
$DBinterface->set_logfile("/usr/local/logs/mydb-log");
Enables logging of SQL changes to the database automatically without providing a callback. The script will open the file specified, with no locking in the current version although this might be added in future. The file must be writeable to the CGI, on UNIX you normally need to chmod 666 mydb-log. However this may differ depending on your system and what operating system you have.
sub errorhandler { my $errstr = shift; print "<h1>Fatal Error</h1>"; print $errstr; exit; } $DBinterface->set_errhandler(\&errorhandler);
Transfers error handling in the script from the die() procedure to the subroutine passed as the argument. The errorhandling routine should not return, and should terminate the program after the error has been output.
$DBinterface->set_timezone(1, 0); # Set time to GMT +0000 $DBinterface->set_timezone(0, -5); # Set time to server time -0500 $DBinterface->set_timezone(1, -8); # Set time to GMT -0800 $DBinterface->set_timezone(0, 2); # Set time to server time +0200
Changes the time zone used for timestamps inserted into database records. The first parameter specifies whether to use GMT time or to use the server time, i.e the computer running this script's internal clock. The second parameter allows time to be added or subtracted in hours.
sub printheader { my $title = shift; my $headtext = shift; print $cgi->start_html(-title=>"$title_txt"); if ($headtext ne "") { print $cgi->h3($headtext); } } $DBinterface->set_printheader(\&printheader);
Transfers the header HTML outputting function to a user defined function to allow HTML customisation.
sub printfooter { print $cgi->end_html; } $DBinterface->set_printfooter(\&printfooter);
Transfers the footer HTML outputting function to a user defined function to allow HTML customisation.
sub checkaccess { if ($cgi->param("password") eq "letmein") { # Example security check return; # Valid password - return to allow function to continue } else { die ("Incorrect password"); # Incorrect - die to stop execution } } $DBinterface->set_accesscallback(\&checkaccess);
Enables a security check function to approve or deny access. The function is called before changes to the database are made. The function should return to allow an action to complete or die to terminate the program and prevent access.
The correct SQL structure for the infotable is shown below, in MySQL format. If another database is being used, an equivalent SQL structure should work correctly providing the field names remain the same. User defined fields can be safely appended to the table and will be ignored by HTMLinterface.
CREATE TABLE [infotable name] ( TableID varchar(200) DEFAULT '' NOT NULL, # SQL name of table TableName tinytext DEFAULT '' NOT NULL, # User friendly name of table NameHash text DEFAULT '' NOT NULL, # 'name=value&name2=value2' style entry for names of fields LabelHash text DEFAULT '' NOT NULL, # 'name=value&name2=value2' style entry for labels of fields LookupHash text DEFAULT '' NOT NULL, # 'name=select Thing from Table&name2=select Somethingelse from Table' style entry for value lookup ExtraHash text DEFAULT '' NOT NULL, # 'name=extra_info&name2=extrainfo' style entry containing extra information. Currently recognised values # include 'encryptpassword' which causes SQL statements to encrypt this field with the mysql 'PASSWORD' # function. Hidden text DEFAULT '' NOT NULL, # 'name&name2' style entry for hidden columns Exclude text DEFAULT '' NOT NULL, # 'name&name2' style entry for excluded columns AdditionalWhere text DEFAULT '' NOT NULL, # Additional SQL 'where' clause for search modes, e.g to exclude items from searches # Add any user defined fields here PRIMARY KEY (TableID) );
A description of each field follows:
The table's identifier. This should exactly match the table's name in the database. For example:
users
A user friendly name for the table, shown in the output. For example:
Registered Users
A string containing user-friendly names for the fields in the table. Formatted similar to a HTML query string, although no form of escaping is available at the moment. For example:
name=User Name&dob=User's Date of Birth&occupation=User's Occupation&password=User's Password
Similar to NameHash, contains a description for each field that is displayed below any form input fields. For example:
name=Enter the user's name&dob=Enter the user's date of birth in the format DDMMYY&occupation=Select the user's occupation&password=The user's password
Used to create dropdown lists, such as in the current example, an additional table called 'occupations' can be created and filled with a list of possible occupations. In editing modes, a drop down list of all available occupations can then be created. LookupHash should contain a select statement to associate with a field. The select statement should return one or two fields. If it returns two, the first will be used as the value of the form element, the value that will be placed in the database and the second will be used as the value displayed to the user, i.e a select box will be created with the syntax:
<option value="[FIRSTFIELD]">[SECONDFIELD]
If only one field is returned, it is used as both the value displayed and the value of the form element.
For example:
occupation=select ID, Name from occupations order by Name
If other lookups are needed they can be joined using the usual '&' syntax.
Used to pass additional information to HTMLinterface. Currently only one value is supported, 'encryptpassword' which causes any entries in the field to be encrypted using the MySQL PASSWORD() function. For example:
password=encryptpassword
Used to hide fields from the user, for example primary key ID fields should be hidden. Simply a list of each hidden field, separated by '&'. Hidden fields are printed as 'hidden' input fields. For example to hide 'id' and 'secretdata':
id&secretdata
Completely removes fields from forms, formatted in the same way as Hidden. For example:
invisiblefield&invisiblefield2
Additional SQL clause to be appended to the select statement used in search modes, for example:
AND NOT name = 'Fred'
could be used to exclude 'Fred' from searches. Also, order by clauses can be appended to sort output.
NOTE: appending to the where clause requires the statement to begin with 'AND'.
Hidden and exclude arrays are nonfunctional due to array handling problems...
\@hidden and \@exclude are returned from a function, i.e ($hidden, $exclude) = &function(); Then in the code, a for loop is used to run through these: for ($i=0;$i<=$#exclude;$i++). But the 'strict' package dies on the $# reference.... Anyone have any ideas how to fix this?
Code may be too MySQL specific and not function on other databases. This needs to be tested.
Currently may allow users to manipulate tables not defined in the infotable depending on database in use. This will be switchable in the next version.
CGI DBI
James Furness <furn@base6.com>
Parts based upon mysql-lib.pl by Ron Crisco <ronsolo@ronsolo.com>.
Copyright (c)1999 James Furness <furn@base6.com>. All Rights Reserved. This module is free software; it may be used freely and redistributed for free providing this copyright header remains part of the module. You may not charge for the redistribution of this module. Selling this code without James Furness' written permission is expressly forbidden.
This module may not be modified without first notifying James Furness <furn@base6.com> (This is to enable me to track modifications). In all cases the copyright header should remain fully intact in all modifications.
This code is provided on an "As Is" basis, without warranty, expressed or implied. The author disclaims all warranties with regard to this software, including all implied warranties of merchantability and fitness, in no event shall the author, James Furness be liable for any special, indirect or consequential damages or any damages whatsoever including but not limited to loss of use, data or profits. By using this module you agree to indemnify James Furness from any liability that might arise from it's use. Should this code prove defective, you assume the cost of any and all necessary repairs, servicing, correction and any other costs arising directly or indrectly from it's use.
The copyright notice must remain fully intact at all times. Use of this program or its output constitutes acceptance of these terms.
Parts of this module are based upon mysql-lib.pl by Ron Crisco.
Thanks to Ron Crisco, Richard Smith and Stephen Heaslip without who I would probably have not written this. Thanks to Tom Christiansen for his perltoot manpage which was useful in writing this module in addition to the perlmod, perlmodlib and Tim Bunce's modules file (Available on CPAN).
To install DBIx::HTMLinterface, copy and paste the appropriate command in to your terminal.
cpanm
cpanm DBIx::HTMLinterface
CPAN shell
perl -MCPAN -e shell install DBIx::HTMLinterface
For more information on module installation, please visit the detailed CPAN module installation guide.