|
DBIx::glueHTML - Class for creating a CGI interface to a database |
DBIx::glueHTML - Class for creating a CGI interface to a database
use CGI; use DBI; use DBIx::glueHTML;
$cgi = new CGI;
$dbh = DBI->connect("DBI:mysql:[DATABASE]:[HOSTNAME]","[USERNAME]","[PASSWORD]") );
$DBinterface = new DBIx::glueHTML ($cgi, $dbh, "[INFOTABLE NAME]");
# Below here is only executed if a glueHTML action was not taken, so print a menu
print $cgi->header; print "<A HREF=" . $cgi->url . "?glueHTML-table=[TABLENAME]&glueHTML-action=add>Add</A>\n<BR>"; print "<A HREF=" . $cgi->url . "?glueHTML-table=[TABLENAME]&glueHTML-action=search>Search</A>\n";
The DBIx::glueHTML 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::glueHTML 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 'glueHTML-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.
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 forms will not work. Overriding the script in this way is not recommended unless necessary for error handler or security check handler setting.
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 glueHTML-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 glueHTML object is created, unless the 'suppress paramcheck' parameter is set to 1.
# Now set the 'glueHTML-table' parameter so the script knows # what table to deal with $cgi->param(-name=>'glueHTML-table',-value=>'mytable');
# Now call the function $DBinterface->exec_search;
Searches the table named in the CGI parameter 'glueHTML-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 'glueHTML-table' parameter so the script knows # what table to deal with $cgi->param(-name=>'glueHTML-table',-value=>'mytable');
# Now call the function $DBinterface->exec_modify;
Modifies a record from the table named in the CGI parameter 'glueHTML-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 'glueHTML-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 'glueHTML-table' parameter so the script knows # what table to deal with $cgi->param(-name=>'glueHTML-table',-value=>'mytable');
# Now call the function $DBinterface->exec_add;
Adds a record to the table named in the CGI parameter 'glueHTML-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 'glueHTML-table' parameter so the script knows # what table to deal with $cgi->param(-name=>'glueHTML-table',-value=>'mytable');
# Now call the function $DBinterface->exec_delete;
Deletes a record from the table named in the CGI parameter 'glueHTML-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 'glueHTML-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 = new DBIx::glueHTML ($cgi, $dbh, $table, 1);
$DBinterface->set_logcallback(\&log_callback);
$DBinterface->check_params();
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.
NOTE: check_params() MUST be called or glueHTML will not function correctly.
NOTE: check_params() MUST be called or glueHTML will not function correctly.
sub errorhandler {
my $errstr = shift;
print "<h1>Fatal Error</h1>";
print $errstr;
exit;
}
$DBinterface = new DBIx::glueHTML ($cgi, $dbh, $table, 1);
$DBinterface->set_errorhandler(\&errorhandler);
$DBinterface->check_params();
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.
NOTE: check_params() MUST be called or glueHTML will not function correctly.
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 = new DBIx::glueHTML ($cgi, $dbh, $table, 1);
$DBinterface->set_accesscallback(\&checkaccess);
$DBinterface->check_params();
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.
NOTE: check_params() MUST be called or glueHTML will not function correctly.
sub setfieldvalue {
my $table = shift; # Database table name
my $mode = shift; # "add"/"modify"/"search"
my $field = shift; # Field name
my $value = shift; # Field value in database or form
my $default= shift; # Field default value
# Additions to the News table
if ($table eq "news" && $mode eq "add") {
# If it's the UserID field
if ($field eq "UserID") {
# Hide the field and set it to the current user's ID
return ($userID, 1);
}
# Modifications to
} else if ($table eq "something" && $mode eq "modify") {
# Something field
if ($field eq "something") {
# Do Something
} else if () {
# Do Something else
}
}
# Etc....
($val, $forcehidden) = &{$self->{FORMFIELDCALLBACK}}($table, $mode, $field, $value, $default);
# Default behaviour - CALLBACK MUST RETURN THIS FOR UNRECOGNISED TABLES/MODES
return (undef, 0);
}
$DBinterface = new DBIx::glueHTML ($cgi, $dbh, $table, 1);
$DBinterface->set_fieldvaluecallback(\&setfieldvalue);
$DBinterface->check_params();
Enables the script to override data in any input form printed by the script. The callback is passed the database table, the mode (add/modify), the current value and the default value. The callback can then change the value of the field, and/or choose to force the field to be hidden. For example a user ID field can be defaulted to the currently logged on user's ID and hidden to prevent changing.
The callback should return an array consisting of the value to be replaced or undef if the value is not to be changed, and 1 to hide the field or 0 to allow the field to remain visible. If no changes are to be made, (undef, 0) must be returned.
NOTE: check_params() MUST be called or glueHTML will not function correctly.
$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->header;
print $cgi->start_html(-title=>"$headtext");
if ($headtext ne "") {
print $cgi->h3($headtext);
}
}
$DBinterface->set_printheader(\&printheader, 1);
Transfers the header HTML outputting function to a user defined function to allow HTML customisation. (This is printed at the top of every page outputed by this module)
The first parameter is a function reference, the second parameter is 1 to allow this module to print the HTTP Content-Type header automatically, 0 to suppress this.
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. (This is printed at the bottom of every page outputed by this module)
sub starttable {
my $colwidth = shift;
my $title = shift;
my $instructions = shift;
print "<CENTER><BLOCKQUOTE>" .
"<TABLE BORDER=0 CELLSPACING=5 CELLPADDING=5>\n" .
"<TR><TD COLSPAN=\"$colwidth\" BGCOLOR=\"#FFEEBB\"><H3><FONT COLOR=\"#000000\">" .
"$title</FONT></H3><FONT SIZE=\"1\">$instructions</FONT></TD></TR>\n\n";
}
$DBinterface->set_starttable(\&starttable);
Transfers the table beginning HTML outputting function to a user defined function to allow HTML customisation. (This is used to begin all tables)
sub starttablerow {
print "<TR>";
}
$DBinterface->set_starttablerow(\&starttablerow);
Transfers the table row beginning HTML outputting function to a user defined function to allow HTML customisation. (This is used to generate <TR> row beginnings, and is not used in printedittablerow-outputted rows)
sub printtablecell {
my $content = shift;
print "<TD BGCOLOR=\"#FFFFEE\">";
print $content;
print "</TD>";
}
$DBinterface->set_printtablecell(\&printtablecell);
Transfers the table cell printing HTML outputting function to a user defined function to allow HTML customisation. (This is used to generate <TD></TD> cells, and is not used in printedittablerow-outputted rows)
sub printtableheadercell {
my $content = shift;
print "<TD BGCOLOR=\"#EEEEDD\" ALIGN=CENTER><STRONG>";
print $content;
print "</STRONG></TD>";
}
$DBinterface->set_printtableheadercell(\&printtableheadercell);
Transfers the table header cell printing HTML outputting function to a user defined function to allow HTML customisation. (This is used to generate <TD></TD> header cells (Usually bold), and is not used in printedittablerow-outputted rows)
sub printendtablerow {
print "</TR>";
}
$DBinterface->set_printendtablerow(\&printendtablerow);
Transfers the table row ending HTML outputting function to a user defined function to allow HTML customisation. (This is used to generate </TR> row endings, and is not used in printedittablerow-outputted rows)
sub printedittablerow {
my $name = shift;
my $form = shift;
my $label = shift;
print "<TR><TD BGCOLOR=\"#EEEEDD\" VALIGN=TOP><B><FONT COLOR=\"#111199\">";
print $name;
print "</FONT></B></TD>\n";
print "<TD BGCOLOR=\"#FFFFEE\">";
print $form;
print "<BR><I><FONT SIZE=\"-1\">";
print $label;
print "</FONT></I></TD></TR>";
}
$DBinterface->set_printedittablerow(\&printedittablerow);
Transfers the edit table's row HTML outputting function to a user defined function to allow HTML customisation. (This prints a whole row without calling printendtablerow or printstarttablerow, and is used in add/modify forms)
sub endtable {
print "</TABLE>";
print "</CENTER></BLOCKQUOTE><HR>";
}
$DBinterface->set_endtable(\&endtable);
Transfers the table ending HTML outputting function to a user defined function to allow HTML customisation. (This is used to end all tables)
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 glueHTML.
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:
users
Registered Users
name=User Name&dob=User's Date of Birth&occupation=User's Occupation&password=User's Password
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
<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.
PASSWORD() function. For example:
password=encryptpassword
id&secretdata
invisiblefield&invisiblefield2
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'.
[TODO - Believed to be fixed, awaiting testing]
James Furness, furn@base6.com
Parts based upon mysql-lib.pl by Ron Crisco <ronsolo@ronsolo.com>.
the CGI manpage the DBI manpage
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 the perltoot manpage manpage which was useful in writing this module in addition to the the perlmod manpage, the perlmodlib manpage and Tim Bunce's modules file (Available on CPAN).
|
DBIx::glueHTML - Class for creating a CGI interface to a database |