View on
MetaCPAN
Alex J. G. Burzyński > DBIx-BlackBox-0.02 > DBIx::BlackBox

Download:
DBIx-BlackBox-0.02.tar.gz

Dependencies

Annotate this POD

CPAN RT

Open  1
View/Report Bugs
Module Version: 0.02   Source  

NAME ^

DBIx::BlackBox - access database with stored procedures only

SYNOPSIS ^

DBIx::BlackBox 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 "connect" in DBI.

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 DBD::Sybase.

    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?Queue=DBIx-BlackBox. 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.

syntax highlighting: