I don't remember the circumstances that brought this module on. Probably drugs of some sort. In any case, I thought of this minimal DBI that uses nothing more than a tied scalar to play with databases. I've only really tested it with SELECT statements, but it works pretty well. Since I have a tendency to create somewhat crazy modules (like Parse::Tinymush), I thought this one would get my usual response ("Stop the madness!", "Peope are starving and you wrote THIS?"). Instead, people thought it was "perly and useful" and "/cool/". So, does this module demonstrate insanity or genius? 

package Tie::DBI;

use 5.008003;
use strict;
use warnings;

use base 'Tie::Scalar';

use DBI;

our $VERSION = '0.01';

sub TIESCALAR {
  my $class = shift;
  my ($dsn, $user, $pass) = @_;
  my $self = {
    dbh => undef,
  };
  $self->{dbh} = DBI->connect($dsn, $user, $pass);
  bless $self, $class;
}

sub STORE {
  my ($self, $value) = @_;
  if ( $self->{sth} && ref($value) eq 'ARRAY' ) {
    $self->{sth}->execute(@$value);
  } else {
    $self->{sth} = $self->{dbh}->prepare($value);
    if ( $self->{sth}->FETCH('NUM_OF_PARAMS') == 0 ) {
      $self->{sth}->execute();
    }
  }
}

sub FETCH {
  my ($self) = @_;
  return undef if !$self->{sth} || $self->{dbh}->err;
  return $self->{sth}->fetchrow_arrayref() || 0;
}

1;

[download]
It works like this:
my $dbh;
tie $dbh, 'Tie::DBI', $dsn, $user, $pass;
$dbh = "select * from table where field = $value";
while ( my $row = $dbh ) {
  print $row->[0], "\n";
}

[download]

Assigning to the tied variable causes the value to be prepared and executed. Reading from the variable causes fetchrow_arrayref() to be called and one row is returned. 'undef' is returned if there is no statement handle or there is an error. 0 is returned if there are no more rows.

Placeholders can be used as well: 

$dbh = "select * from table where field = ?";
$dbh = [$value];
while ( my $row = $dbh ) {
  print $row->[0], "\n";
}

[download]

Any comments or questions?

Note 1: I realize that Tie::DBI already exists on CPAN. I just couldn't think of a good name, except for Tie::DBI::Minimalist, which is kinda dumb. Any good suggestions?

Note 2: I think an even better minimal interface can be done using a tied filehandle. I'm working on that.

Note 3: On a side note, is this a meditation or a craft?

Replies are listed 'Best First'.
Re: Genius or Insanity: My minimal DBI
by perrin (Chancellor) on Jan 13, 2004 at 04:23 UTC
    For the name, I think Tie::DBIQuery or DBIx::TieQuery would work. You are dealing with the results from a query here, not a whole table like Tie::DBI.

    I don't know how to say this nicely: I hate the interface. It looks like you are doing something insane to a string. I think a much more natural interface would be to tie an array and make the SQL query one of the args to tie().

[reply]
      No need to be nice. This is what I was expecting, actually. :) The interface was more of a thought experiment than anything. I think both a tied array and a tied filehandle would make for a much, much nicer interface. Plus, I can't quite get this one to work with anything but SELECTs :(
[reply]
Re: Genius or Insanity: My minimal DBI
by theorbtwo (Prior) on Jan 13, 2004 at 05:21 UTC

    While I can certianly see this as a potential growth of a desire to explore a tied scalar, as an interface, it sucks.

    Here is, perhaps, a better API. Tied filehandles are, indeed, the way I'd go. For reading: 

    tie $dbfh, 'Tie::DBI', $dsn, $user, $pass, 'id, name from foo';
while (my $record = <$dbfh>) {
 print "ID:   ", $record->{id}, "\n";
 print "Name: ", $record->{name}, "\n";
}

    [download]
    For writing: 
    # $dbfh continues from above
$dbfh->print(join $/, (id => '42', name => 'Answer', '');

    [download]
    The rule for reading is that each "line" is a record, as a hash-ref. The rule for writing is that you should give the row hash, flattened to a string by joining it with $/ (normaly newline), with an extra newline meaning "done with this record". Printing hashrefs (or arrayrefs, at the user's option) would be nicer, but I don't think you can do that with the tied FH interface.

    Really, I don't see all that much use for the "write" interface, but the "read" interface seems interesting to me -- there's a lot of idioms around file-processing that don't translate 1:1 to table processing for no good reason.

    In fact, now that I think about it more, it might be more interesting to have the tied FH behave exactly like a CSV file. 

    my $dbfh = tie $dbfh, 'Tie::DBI', $dsn, $user, $pass, 'id, name from f
+oo'; # Support optional prameters to give the defintion of comma, new
+line, etc?  Take ideas from CSV_XS
while (<$dbfh>) {
  chomp;
  my ($id, $name) = split /,/, $_;
  print "$id: $name\n";
}

print $dbfh (qq(42, "Answer"\n));
# Note that the \n isn't optional; you should wait for it, putting inp
+ut in an interal buffer until you see it, then flush the whole line. 
+ Support some special syntax to specifiy a NULL, or to leave it out a
+nd let an AUTONUMBER column do it's thing?

    [download]

    Warning: Unless otherwise stated, code is untested. Do not use without understanding. Code is posted in the hopes it is useful, but without warranty. All copyrights are relinquished into the public domain unless otherwise stated. I am not an angel. I am capable of error, and err on a fairly regular basis. If I made a mistake, please let me know (such as by replying to this node).

[reply]
[d/l]
[select]
Re: Genius or Insanity: My minimal DBI
by dws (Chancellor) on Jan 13, 2004 at 05:37 UTC
    Any comments or questions?

    Some databases get persnickety if you don't manually "finish" a statement handle (cursor) before attempting to reuse it when there are still pending results. That's going to be tricky the way you have things set up.

[reply]
Re: Genius or Insanity: My minimal DBI
by gmpassos (Priest) on Jan 14, 2004 at 01:52 UTC
    Take a look at Object::MultiType. It can help you to make an even more crazy and cool way to use directly the Perl syntax for variables to handle DBI.

    With it you can do: 

      use Object::MultiType ;

  my $scalar = 'abc' ;
  local(\*GLOB) ;
  tie(*GLOB => 'TieGlobPack') ;

  my $obj = Object::MultiType->new(
  scalar   => \$scalar ,
  tiearray => 'TieArrayPack'
  tiehash  => 'TieHashPack'
  code     => sub{ return("I'm a sub ref!") ; }
  glob     => \*GLOB ,
  ) ;
  
  print "Me as scalar: $obj\n" ;
  
  my $array_1 = $obj->[1] ;
  
  my $hash_B = $obj->{B} ;
  
  while( <$obj> ) {
    print $obj "$_\n" ;
  }
  
  &$obj(args) ;

    [download]
    ENJOY! ;-P

    Graciliano M. P.
    "Creativity is the expression of the liberty".

[reply]
[d/l]

Back to Meditations