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 WHERE 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`")->selectall_arraryref({Slice=>{}})

my $user      = $sql->user->selectrow_hashref(undef, $userid);
$user_profile = $sql->user_x(which => 'profile')->selectrow_hashref(undef, $userid);

##</code><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->{default};

    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;

##</code><code>##

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_arrayref
              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;