NAME

DBIx::Browse - Perl extension to browse tables with a CGI/Web interface.

SYNOPSIS

 use DBIx::Browse;
 my ($dbh, $dbb, $q);
 $dbh = DBI->connect("DBI:Pg:dbname=enterprise")
   or croak "Can't connect to database: $@";
$q   = new CGI;
$dbb = new  DBIx::Browse({
   dbh => $dbh, 
   table => 'employee', 
   proper_fields => [ qw ( name fname ) ],
   linked_fields => [ qw ( department category office ) ], 
   linked_tables => [ qw ( department category office ) ], 
   linked_values => [ qw ( name       name     phone  ) ], 
   linked_refs   => [ qw ( id         id       ide    ) ],
   aliases       => [ qw ( name fname department category phone )],
   primary_key   => 'id',
   cgi           => $q
});
print
   $q->start_html(
                  -title => "Test DBIx::Browse"
                  );
$dbb->list_form({
   field_order  => [  1,  0,  4,  3,  2 ],
   field_length => [ 14, 15, 15, 15, 10 ]
});

...etc

DESCRIPTION

The purpose of DBIx::Browse is to handle the browsing of relational
tables with a human-like interface via Web.

DBIx::Browse transparently translates SELECTs, UPDATEs, DELETEs and INSERTs
from the desired "human view" to the values needed for the table. This is the
case when you have related tables (1 to n) where the detail table
has a reference (FOREIGN KEY) to a generic table (i.e. Customers and
Bills) with some index (tipically an integer).

METHODS

new

Creates a new DBIx::Browse object. The parameters are passed throug a hash with the following keys:

dbh

A DBI database handle already opened that will be used for all database interaction.

table

The main (detail) table to browse.

primary_key

The primary key of the main table (default: 'id').

proper_fields

An array ref of field names of the main table that are not related to any other table.

linked_fields

An array reference of field names of the main table that are related to other tables.

linked_tables

An array reference of related table names corresponding to each element of the linked_fields parameter.

linked_values

The "human" values of each linked_fields (a field name of the corresponding linked_tables element, default: 'name').

linked_refs

The foreign key field name that relates the values of the linked_fields with the linked_tables (default: 'id').

If present, linked_tables, and linked_refs must have the same number of elements than linked_fields.

aliases

An array ref containing the field aliases (names that will be displayed) of the table. This must include all, proper and linked fields.

cgi

A CGI object that will be used for Web interaction. If it is not defined a new CGI object will be created.

max_rows

The maximum number of rows to be displayed per Web page (default: 10).

max_flength

The maximum field length to be displayed (also the default for unknown field lengths).

debug

If set, it will output a lot of debug information.

default_action

The default action (web page) that will be displayed if not set by the calling program (currently "List" or "Edit".

form_params

A hash ref containing other form parameters that will appear as "HIDDEN" input fields.

styles

An anonymous arrays of css styles ("CLASS") that will be applied to succesive rows of output.

prepare

It will create a statement handle (see DBI manpage) suited so that the caller does not need to explicitly set the "WHERE" clause to reflect the main table structure and relations, just add the interesting part. For example, using an already initialized DBIx::Browse object, you can "prepare" like this:

    my $dbixbr = new DBIx::Browse({
	table         => 'employee',
        proper_fields => 'name',
        linked_fields => ['departament','category']
    })

 (...)

    $my $sth = $dbixbr->prepare({
	where => "departament = 'Adminitstration' AND age < 35",
        order => "name ASC, departament ASC"
	}

instead of:

     $my $sth = $dbh->prepare(
 "SELECT employee.name AS name, 
        departament.name AS departament, 
        category.name AS category
  FROM employee, departament, category
  WHERE departament.id   = employee.departament AND
        category.id      = employee.category    AND
        departament.name = 'Administration'     AND
        employee.age     < 35
  ORDER BY employee.name ASC, departament.name ASC"
			      );

All parameters are passed in a hash reference containig the following fields (all optional):

where

The WHERE clause to be added to the query (after the join conditions).

group

The "GROUP BY" clause.

having

The "HAVING" clause.

order

The "ORDER BY" clause.

limit

The "LIMIT" clause.

offset

The "OFFSET" clause.

The last column will always be the declared primary key for the main table. The column name will be generated with the pkey_name method.

pkey_name

It returns the primary key field name that will be the last field in a prepared statement.

count

It will return the number of rows in a query. The hash reference parameter is the same than the prepare method.

insert

This method inserts a new row into the main table. The input parameter is a hash reference containing the field names (keys) and values of the record to be inserted. The field names must correspond to those declared when calling the new method in the aliases parameter. Not all the field names and values must be passed as far as the table has no restriction on the missing fields (like "NOT NULL" or "UNIQUE").

update

This method updates rows of the main table. It takes two parmeters:

record

A hash reference containing the field names as keys with the corresponding values.

where

The "WHERE" clause of the "UPDATE".

delete

This method deletes a row in the main table. It takes one parameter pkey, the value of the primary key of the row to delete. Multiple deletes are not allowed and should be addressed directly to the DBI driver.

field_values

This method returns an array reference with the list of possible field values for a given field in the main table. It takes one parameter:

field_number: An index indicating the field number (as declared in the new method). If the field is a linked field (related to other table) it will return the values of the related table (as described by linked_table, and linked_values in the new method).

list_form

This method produces a CGI form suitable to explore the main table. It will list its rows in chunks of max_rows. It will present also the possibility to edit (see edit_register) any row and to filter the rows to display.

It takes one optional parameter with a hash reference with the following keys:

field_names

An array reference containing the field names to be displayed.

field_order

An array reference with the desired order index in wich the fields will appear.

field_length

An array reference with the desired field length.

edit_form

This method produces a CGI form suitable to browse the main table record by record. You can update, delete and insert new records.

It takes one optional parameter with a hash reference with the same structure than list_form.

browse

This method will call list_form or edit_form as needed depending on the user input.

It takes one optional parameter with a hash reference with the same structure than list_form.

The last three methods will probably be moved to a subclass in the future.

RESTRICTIONS

The DBI driver to use MUST allow to set AutoCommit to zero.

The syntax construction of queries have only been tested against PostgreSQL and MySQL.

Not all the clauses are supported by all DBI drivers. In particular, the "LIMIT" and "OFFSET" ones are non SQL-standard and have been only tested in PostgresSQL and MySQL (in this later case, no especific OFFSET clause exists but the DBIx::Browse simulates it by setting accordingly the "LIMIT" clause).

AUTHOR

Evilio José del Río Silván, edelrio@icm.csic.es

SEE ALSO

perl(1), DBI(3), CGI(3).