/ 
DBIx-HTMLinterface-0.11
River stage zero No dependents
/ DBIx::HTMLinterface

NAME

DBIx::HTMLinterface - Class for creating a CGI interface to a database

SYNOPSIS

use CGI;
use DBI;
use 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";

DESCRIPTION

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.

Features

Simple database administration

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.

Full form configuration

Forms can be modified to add descriptions and extra information to fields, making it easy to change output without having to edit code.

Control

Extensive callback procedures and configuration options allow output, password protection and logging to be configured as desired.

Full HTML customisation

HTML output and table formats can be customised easily by the user.

METHODS

Main Methods

$DBinterface = new DBIx::HTMLinterface (CGI DBI Infotable Name [Suppress paramcheck]);

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.

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

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_params ();
# 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.

exec_search ();
# 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.

exec_modify ();
# 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

exec_add ();
# 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.

exec_delete ();
# 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 Customisation Methods

Optional methods which can be called to alter the behaviour of the script or enable features such as logging.

set_logcallback (Callback function address);
sub log_callback {
    my $description = shift;
    my $sql         = shift;

    open (LOG,">>$logfile")
    print LOG "$description (Executing $sql)";
    close(LOG);
}
$DBinterface = new DBIx::HTMLinterface ($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 HTMLinterface will not function correctly.

set_logfile (Logfile name); $DBinterface = new DBIx::HTMLinterface ($cgi, $dbh, $table, 1); $DBinterface->set_logfile("/usr/local/logs/mydb-log"); $DBinterface->check_params();

Enables logging of SQL changes to the database automatically without providing a callback. The script will open the file specified, with no locking (Althoughthis 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.

NOTE: check_params() MUST be called or HTMLinterface will not function correctly.

set_errhandler (Error handler function address);
sub errorhandler {
    my $errstr  = shift;

    print "<h1>Fatal Error</h1>";
    print $errstr;

    exit;
}
$DBinterface = new DBIx::HTMLinterface ($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 HTMLinterface will not function correctly.

set_accesscallback
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::HTMLinterface ($cgi, $dbh, $table, 1);
$DBinterface->set_accesscallback(\&errorhandler);
$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 HTMLinterface will not function correctly.

set_timezone (UseGMT (1/0), Time change (hours));
$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.

Optional HTML Customisation Methods

Future Additions

In a later version, callbacks to print table cells, start and finish tables, print form fields, print back links and add user defined form parameters to allow state keeping such as password protection etc.

set_printheader
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.

set_printfooter
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.

INFOTABLE FORMAT

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:

TableID

The table's identifier. This should exactly match the table's name in the database. For example:

users
TableName

A user friendly name for the table, shown in the output. For example:

Registered Users
NameHash

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
LabelHash

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
LookupHash

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.

ExtraHash

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
Hidden

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
Exclude

Completely removes fields from forms, formatted in the same way as Hidden. For example:

invisiblefield&invisiblefield2
AdditionalWhere

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'.

KNOWN BUGS

  • Hidden and exclude arrays are nonfunctional due to array handling problems...

    [TODO - Believed to be fixed, awaiting testing]

  • 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.

AUTHOR

James Furness, furn@base6.com

Parts based upon mysql-lib.pl by Ron Crisco <ronsolo@ronsolo.com>.

SEE ALSO

CGI DBI

COPYRIGHT

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.

Acknowledgments

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).