NAME

App::Repository::DBI - a repository which relies on a DBI interface to a relational database (no caching)

SYNOPSIS

   use App::Repository::DBI;

   (see man page for App::Repository for additional methods)

   $rep = App::Repository::DBI->new();        # looks for %ENV, then config file
   $rep = App::Repository::DBI->new("mysql","mydb","user001","pass001");
   $rep = App::Repository::DBI->new("mysql","mydb","user001","pass001","port=3307");
   $rep = App::Repository::DBI->new("mysql","mydb","user001","pass001","port=3307","user001");

   $ok = $rep->_connect();         # initialize repository (will happen automatically in constructor)
   $ok = $rep->_disconnect();      # cleanup repository (will happen automatically in destructor)
   $rep->_is_connected();          # returns 1 if connected (ready for use), 0 if not
   $errmsg = $rep->error();       # returns the error string for prev op ("" if no error)
   $numrows = $rep->numrows();    # returns the number of rows affected by prev op
   print $rep->error(), "\n" if (!$rep->_connect());

   $value  = $rep->get ($table, $key,     $col,   \%options);
   $value  = $rep->get ($table, \%params, $col,   \%options);
   @row    = $rep->get ($table, $key,     \@cols, \%options);
   @row    = $rep->get ($table, \%params, \@cols, \%options);

   $nrows = $rep->set($table, $key,     $col,   $value,    \%options);
   $nrows = $rep->set($table, \%params, $col,   $value,    \%options);

   $row    = $rep->get_row ($table, $key,     \@cols, \%options);
   $row    = $rep->get_row ($table, \%params, \@cols, \%options);

   $nrows = $rep->set_row($table, $key,     \@cols, $row, \%options);
   $nrows = $rep->set_row($table, \%params, \@cols, $row, \%options);
   $nrows = $rep->set_row($table, undef,    \@cols, $row, \%options);

   $colvalues = $rep->get_column ($table, \%params, $col, \%options);

   $rows = $rep->get_rows ($table, \%params, \@cols, \%options);
   $rows = $rep->get_rows ($table, \%params, $col,   \%options);
   $rows = $rep->get_rows ($table, \@keys,   \@cols, \%options);

   $nrows = $rep->set_rows($table, \%params, \@cols, $rows, \%options);
   $nrows = $rep->set_rows($table, undef,    \@cols, $rows, \%options);
   $nrows = $rep->set_rows($table, \@keys,   \@cols, $rows, \%options);

   $values = $rep->get_values ($table, $key,     \@cols, \%options);
   $values = $rep->get_values ($table, \%params, \@cols, \%options);
   $values = $rep->get_values ($table, $key,     undef,  \%options);
   $values = $rep->get_values ($table, \%params, undef,  \%options);

   $values_list = $rep->get_values_list ($table, $key,     \@cols, \%options);
   $values_list = $rep->get_values_list ($table, \%params, \@cols, \%options);
   $values_list = $rep->get_values_list ($table, $key,     undef,  \%options);
   $values_list = $rep->get_values_list ($table, \%params, undef,  \%options);

   $nrows = $rep->set_values ($table, $key,     \@cols, $values, \%options);
   $nrows = $rep->set_values ($table, $key,     undef,  $values, \%options);
   $nrows = $rep->set_values ($table, undef,    \@cols, $values, \%options);
   $nrows = $rep->set_values ($table, undef,    undef,  $values, \%options);
   $nrows = $rep->set_values ($table, \%params, \@cols, $values, \%options);
   $nrows = $rep->set_values ($table, \%params, undef,  $values, \%options);

DESCRIPTION

The App::Repository::DBI class encapsulates all access to the database, changing SQL statements into get(), save(), and delete() methods.

Public Methods

_connect()

    * Signature: $repository->_connect();
    * Param:     void
    * Return:    void
    * Throws:    App::Exception::Repository
    * Since:     0.50

    Sample Usage: 

    $repository->_connect();

Connects to the repository. Most repositories have some connection initialization that takes time and therefore should be done once. Then many operations may be executed against the repository. Finally the connection to the repository is closed (_disconnect()).

The default implementation of _connect() does nothing. It is intended to be overridden in the subclass (if necessary).

_disconnect()

    * Signature: $repository->_disconnect();
    * Param:     void
    * Return:    void
    * Throws:    App::Exception::Repository
    * Since:     0.50

    Sample Usage: 

    $repository->_disconnect();

Disconnects from the repository.

The default implementation of _disconnect() does nothing. It is intended to be overridden in the subclass (if necessary).

All implementations of _disconnect() by a subclass must be sensitive to whether the object is actually currently connected to the repository. Thus, _disconnect() should be callable without negative consequences even when the repository is already disconnected.

Private Methods

begin_work()

    * Signature: $rep->begin_work();
    * Param:     void
    * Return:    void
    * Throws:    App::Exception::Repository
    * Since:     0.01

    Sample Usage: 

    $rep->begin_work();

commit()

    * Signature: $rep->commit();
    * Param:     void
    * Return:    void
    * Throws:    App::Exception::Repository
    * Since:     0.01

    Sample Usage: 

    $rep->commit();

rollback()

    * Signature: $rep->rollback();
    * Param:     void
    * Return:    void
    * Throws:    App::Exception::Repository
    * Since:     0.01

    Sample Usage: 

    $rep->rollback();

call_procedure()

    * Signature: $rep->call_procedure($call_str);
    * Signature: $rep->call_procedure($call_str, $return_type, $param_types, @params);
    * Signature: $result  = $rep->call_procedure($call_str, $return_type);
    * Signature: $result  = $rep->call_procedure($call_str, $return_type, $param_types, @params);
    * Signature: @results = $rep->call_procedure($call_str, $return_type);
    * Signature: @results = $rep->call_procedure($call_str, $return_type, $param_types, @params);
    * Signature: $rows    = $rep->call_procedure($call_str, $return_type);
    * Signature: $rows    = $rep->call_procedure($call_str, $return_type, $param_types, @params);
    * Param:     void
    * Return:    $result   string    (if $return_type is "SCALAR")
    * Return:    @results  ARRAY     (if $return_type is "LIST")
    * Return:    $row      ARRAY     (if $return_type is "ROW")
    * Return:    $rows     ARRAY     (if $return_type is "ROWS")
    * Throws:    App::Exception::Repository
    * Since:     0.01

There is no standard way to call stored procedures in the DBI. This is an attempt to provide access to them.

    MySQL: Sample Usage
    1. As of DBD-mysql-3.0008 and MySQL 5.1.12, INOUT and OUT parameters are not supported
    2. In order to receive values back from a stored procedure in MySQL,
       you need to have applied the "dbd-mysql-multi-statements.patch" patch.
       https://rt.cpan.org/Public/Bug/Display.html?id=12322
       https://rt.cpan.org/Ticket/Attachment/167152/53763/dbd-mysql-multi-statements.patch
       This supports the "SCALAR" return type (and maybe "LIST" and "ROW"), but
       a stored procedure can still not return multiple rows ("ROWS"). (I think.)
       You DSN needs to have "mysql_multi_results=1" set to activate the ability to
       get rows back from a stored procedure.

    $rep->call_procedure("call sp_doit('prod',5)");
    $val           = $rep->call_procedure("call sp_doit_return_val('prod',5)", "SCALAR");
    ($val1, $val2) = $rep->call_procedure("call sp_doit_return_vals('prod',5)", "LIST");
    $row           = $rep->call_procedure("call sp_doit_return_vals('prod',5)", "ROW");