NAME

DBIx::Browse - Perl extension to browse tables.

SYNOPSIS

  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
    $dbixbr->add({
        name       => 'John',
        department => 'Sales',
        category   => 'Sales Representant',
        phone      => '1114'
    });

    ## Update a record
    $dbixbr->update({
        record => { phone => '1113', category => 'Sales Manager' }
        where  => 'id = 123 ' 
    });

...etc

DESCRIPTION

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

METHODS

new

Creates a new DBIx::Browse object. The parameters are passed through 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 (defaults to the corresponding name in linked_fields).

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, linked_values 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.

debug

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

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

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