DBIx::BlackBox - access database with stored procedures only

NAME

DBIx::BlackBox - access database with stored procedures only

SYNOPSIS

the DBIx::BlackBox manpage provides access to database using stored procedures only (the only SQL command available is exec). That allows to treat your database as a black box into which only the database administrator provides access by stored procedures.

Setup base class:

    package MyDBBB;
    use Moose;

    with 'DBIx::BlackBox' => {
        connect_info => [
            'dbi:Sybase:server=sqlserver',
            'username',
            'password',
            {
                RaiseError = 1,
                PrintError = 0,
            }
        ]
    };

Create procedures classes. Attributes define stored procedure parameters.

    package MyDBBB::Procedures::ListCatalogs;
    use Moose;

    with 'DBIx::BlackBox::Procedure' => {
        name => 'DB_Live..list_catalogs',
        resultsets => [qw(
            MyDBBB::ResultSet::Catalogs
            MyDBBB::ResultSet::CatalogData
        )],
    };

    has 'root_id' => (
        is => 'rw',
        isa => 'Int',
        required => 1,
    );
    has 'org_id' => (
        is => 'rw',
        isa => 'Maybe[Int]',
    );

    package MyDBBB::Procedures::UpdateCatalog;
    use Moose;

    with 'DBIx::BlackBox::Procedure' => {
        name => 'DB_Live..update_catalog',
    };

    has 'id' => (
        is => 'rw',
        isa => 'Int',
        required => 1,
    );
    has 'name' => (
        is => 'rw',
        isa => 'Str',
        required => 1,
    );

Describe result sets for procedures. They could (and should) be shared between procedures.

    package MyDBBB::ResultSet::Catalogs;
    use Moose;

    has 'id' => (
        is => 'rw',
        isa => 'Int',
    );
    has 'name' => (
        is => 'rw',
        isa => 'Str',
    );

    package MyDBBB::ResultSet::CatalogData;
    use Moose;

    has 'id' => (
        is => 'rw',
        isa => 'Int',
    );
    has 'hierarchy' => (
        is => 'rw',
        isa => 'Int',
    );
    has 'description' => (
        is => 'rw',
        isa => 'Str',
    );

and then

    use MyDBBB;

    my $dbbb = MyDBBB->new();

execute stored procedure and get result object to iterate over

    my $rs = eval {
        $dbbb->exec('ListCatalogs',
            root_id => $root_id,
            org_id => $org_id,
        );
    } or do {
        die $@;
    }

    my @columns = (
        [qw( id name )],
        [qw( id hierarchy description )],
    );
    do {
        my @c = @{ $columns[ $rs->idx ] };
        while ( my $row = $rs->next_row ) {
            print "$_: ", $row->$_, "\n"
                for @c;
        }
    } while ( $rs->next_resultset );

    print "procedure_result: ", $rs->procedure_result, "\n";

or get all rows at once


    my ( $catalogs, $data, $rv ) = $dbbb->exec('ListCatalogs',
        root_id => $root_id,
        org_id => $org_id,
    )->all;

    for my $catalog ( @$catalogs ) {
        print $catalog->id, ": ", $catalog->name, "\n";
    }

    for my $row ( @$data ) {
        print $row->id, "[", $row->hierarchy, "]: ", $row->description, "\n";
    }

    print "procedure result: $rv";

ROLE PARAMETERS

connect_info

Database connection arguments passed to DBI/"connect".

Required.

Note: currently only DBD::Sybase (MS SQL Server) is supported.

procedures_namespace

All classes in provided namespace them will be automatically loaded.

Defaults to name of the consumer of DBIx::BlackBox role with ::Procedures appended.

Note: those classes need to consume DBIx::BlackBox::Procedure role.

ATTRIBUTES

connect_info

Returns the value of role parameter connect_info.

procedures_namespace

Returns the value of role parameter procedures_namespace.

METHODS

exec

    my $rs = $dbbb->exec($procedure_class, %args);

Instantiates an object of the $procedure_class (which is appended to procedures_namespaces) with arguments provided by %args and executes procedure defined by class.

Procedures should used named paremeters only.

INSTALLATION

Following installation steps were tested with both Microsoft SQL Server 2000 and Microsoft SQL Server 2008.

unixODBC

Install unixODBC from your system packages or download sources from http://www.unixodbc.org/.

FreeTDS

Download dev release of FreeTDS from http://www.freetds.org (tested with freetds-0.83.dev.20100122).

    ./configure --with-unixodbc=/usr/local/ \
        --with-tdsver=8.0 --prefix=/usr/local/freetds
    make
    sudo make install

Edit /usr/local/freetds/etc/freetds.conf and specify access to your database.

    ...
    [sqlserver]
        host = 1.2.3.4
        port = 1433
        tds version = 8.0

DBD::Sybase

Install the DBD::Sybase manpage.

    SYBASE=/usr/local/freetds perl Makefile.PL
    make
    sudo make install

If you want to test DBD::Sybase most likely you would need to modify tests that come with the module (some queries in test files will not work with MS SQL Server).

CAVEATS

Neither the stored procedures nor result sets classes can have attributes/columns/parameters that would clash with Moose internals, e.g. new.

AUTHOR

Alex J. G. Burzyński, <ajgb at cpan.org>

BUGS

Please report any bugs or feature requests to bug-dbix-blackbox at rt.cpan.org, or through the web interface at http://rt.cpan.org/NoAuth/ReportBug.html. I will be notified, and then you'll automatically be notified of progress on your bug as I make changes.

LICENSE AND COPYRIGHT

Copyright 2010 Alex J. G. Burzyński.

This program is free software; you can redistribute it and/or modify it under the terms of either: the GNU General Public License as published by the Free Software Foundation; or the Artistic License.

See http://dev.perl.org/licenses/ for more information.

 DBIx::BlackBox - access database with stored procedures only