use strict;
use warnings;

use DBI;
use DBIx::Library;

my $dbh = DBI->connect() or die DBI->errstr;

my $sql = new DBIx::Library(
               dbh    => $dbh,
               quries => { 
                           offices   => 'SELECT * FROM Offices',
                           offices_x => 'SELECT $$what$$ FROM Offices'
+,
                           user      => 'SELECT `username`, `profile` 
+FROM users WHERE user_id = ?',
                           user_x    => 'SELECT $$which$$ FROM users W
+HERE user = ?',                           
                         },               
               );

my $actual_SQL   =  $sql->offices->sql;
my $offices_hash =  $sql->offices->selectall_arraryref({Slice=>{}})
# and a final one using the subsitution
$offices_hash =  $sql->offices_x->(what => "`state`, `zip`")->selectal
+l_arraryref({Slice=>{}})

my $user      = $sql->user->selectrow_hashref(undef, $userid);
$user_profile = $sql->user_x(which => 'profile')->selectrow_hashref(un
+def, $userid);
[download]

---

SYNOPSIS

    use DBIx::Library
    my $sql = new DBIx::Library(
                   dbh    => $dbh,
                   quries => { 
                               offices   => 'SELECT * FROM Offices LIMIT $$limit$$',
                             },
                   );
    
    my $offices = $sql->offices->selectall_arrayref({Slice => {}});

---

DESCRIPTION

For storeing SQL in a central place. The sql is loaded via a hash ref. It would be a trivial matter to load that hashref via XML, ini, or database.

The module takes the list of labeled queries and create methods for each. So if you load a sql statment with the key 'offices' then $sql->offices will return that query. If you have defined dbh in the options then it will return a DIBx::Library::SQL object with that query and database handle setup.

Parameters passed to the method will be substituded when found in the SQL.

     # offices => "SELECT $$what$$ FROM Offices"
     $sql->offices(what => "`address`, `address2`, `city`, `state`");
     # resulting SQL =>  "SELECT `address`, `address2`, `city`, `state` FROM Offices"

---

OPTIONS

dbh

The database handle to tie SQL statements to. This is optional, but convenient. Since it returns a DBIx::Library::SQL object, that object can then be tied to the database handle.

With dbh defined:

     $sql->offices->do();

With out:

     $dbh->do($sql->offices);

You can see that this saves only a minimal amount of space. However the object returned when dbh defined can be kept around and used repeatedly.

     my $offices = $sql->offices;
     my $office_count = $offices->do();
     my $office_hash  = $offices->selectall_arrayref({Slice=>{}});

delimeter

This sets the delimiter for substitutions. By default it is $$ so any strings inside the query surrounded by $$ are variables.

    my $sql = new DBIx::Library(
                   quries => {offices   => 'SELECT * FROM Offices LIMIT $$limit$$'},
                   );
     $sql->offices(limit => 1);

clean

If clean is set to true then un assigned place holders will be cleared out. Defaults to 1.

    use DBIx::Library
    my $sql = new DBIx::Library(
                   quries => { 
                               offices   => 'SELECT * FROM Offices LIMIT $$limit$$',
                             },
                   clean  => 0,
                   );
                   
    print $sql->offices;
    # prints SELECT * FROM Offices LIMIT $$limit$$
    $sql->{clean} = 1;
    print $sql->offices;
    # prints SELECT * FROM Offices LIMIT 
    
This may or may not be desired behavior depending on the type of query and the circumstances

---

KNOWN BUGS

None at this time, but we're still looking.

---

---

AUTHOR

Eric Hodges <eric256@gmail.com>. Comments, bug reports, and patches are appreciated.

And then the code

package DBIx::Library;

use strict;
use Carp;
use vars qw( $VERSION );

use Data::Dumper;

use DBIx::Library::SQL;

$VERSION = 0.02;

# Initiate a new object:
sub new {
    my $object_or_class = shift;
    my $class   = ref($object_or_class) || $object_or_class;
    
    my $options = { @_ };
        
    my $self = {
        quries    => $options->{quries} || {},
        default   => "SELECT 0;",
        clean     => 1,
        delimeter => '$$',
        dbh       => $options->{dbh} || undef,      
    };
    
    $self->{clean}   = $options->{clean}   if exists $options->{clean}
+;
    $self->{default} = $options->{default} if exists $options->{defaul
+t};

    bless $self, $class;
    return $self;
}

sub AUTOLOAD
{
    my $self = shift;

    my $method = $DBIx::Library::AUTOLOAD;
  
  $method =~ s/^DBIx::Library:://;
  return if $method =~ /DESTROY$/;
  $self->_get_SQL($method, @_);
}

sub _get_SQL {
    my $self = shift;
    my $method = shift;
    if (exists $self->{quries}->{$method}) {           
            my $sql = $self->{quries}->{$method};
            my $del = qr/\Q$self->{delimeter}\E/;
            
            my $params = { @_ };
            foreach my $param ( keys %$params ) {
               my $match = $del . $param .$del;
               $sql =~ s/$match/$params->{$param}/igs;
            }
            $sql =~ s/$del(.+?)$del//g if $self->{clean};
            return $self->_wrap_SQL($sql);
    }
    return $self->_wrap_SQL($self->{default});
}

sub _wrap_SQL {
    my $self = shift;
    my $sql  = shift;
    if (defined $self->{dbh}) {
        return DBIx::Library::SQL->new($sql, $self->{dbh});
    } else {
        return $sql;
    }
}
1;
[download]

And then the baby module to go with it

package DBIx::Library::SQL;

use overload '""' => sub {return shift->{sql}};

sub new {
    my $proto = shift;
    my $class = ref($proto) || $proto;
   
    my $sql = shift;
      my $dbh = shift || undef;;
    my $self = {
        dbh  => $dbh,
        sql  => $sql,
    };


    bless $self, $class;
    return $self;
}

sub sql {
    my $self = shift;
    return $self->{sql};
}

my @okay = qw/do selectall_arrayref selectall_hashref selectcol_arrayr
+ef
              selectcol_arrayref selectrow_array selectrow_arrayref
                  selectrow_hashref prepare prepare_cached/;

our $AUTOLOAD;              
sub AUTOLOAD {
    my $self = shift;
    my $type = ref($self) or die "$self is not an object";

    my $name = $AUTOLOAD;
    $name =~ s/.*://;   # strip fully-qualified portion
   unless (defined $self->{dbh}) {
        die "Please define the database handle before calling methods"
+;
    }
   
    unless (grep {$_ eq $name} @okay ) {
        die "Bad method";
    }

    if (@_) {
        return $self->{dbh}->$name($self->{sql}, @_);
    } else {
        return $self->{dbh}->$name($self->{sql});
    }
}

1;
[download]