DBIx::Browse - Perl extension to browse tables.


  use DBIx::Browse;
  my ($dbh, $dbb, $q);
  $dbh = DBI->connect("DBI:Pg:dbname=enterprise")
    or croak "Can't connect to database: $@";
 $dbixbr = 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'
    ## Insert a record
        name       => 'John',
        department => 'Sales',
        category   => 'Sales Representant',
        phone      => '1114'
    ## Update a record
        record => { phone => '1113', category => 'Sales Manager' }
        where  => 'id = 123 ' 



The purpose of DBIx::Browse is to handle the browsing of relational tables.

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


Creates a new DBIx::Browse object. The parameters are passed through a hash with the following keys:
A DBI database handle already opened that will be used for all database interaction.

The main (detail) table to browse.

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

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

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

An array reference of related table names corresponding to each element of the linked_fields parameter (defaults to the corresponding name in linked_fields).

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

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

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

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

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

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 AS name, AS departament, AS category
  FROM employee, departament, category
  WHERE   = employee.departament AND      = employee.category    AND = 'Administration'     AND
        employee.age     < 35

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

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

The ``GROUP BY'' clause.

The ``HAVING'' clause.

The ``ORDER BY'' clause.

The ``LIMIT'' clause.

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.

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

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

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

This method updates rows of the main table. It takes two parmeters:
A hash reference containing the field names as keys with the corresponding values.

The ``WHERE'' clause of the ``UPDATE''.

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.

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


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


Evilio José del Río Silván,


perl(1), DBI(3).