#
# (c) Jan Gehring <jan.gehring@gmail.com>
# 
# vim: set ts=3 sw=3 tw=0:
# vim: set expandtab:

=head1 NAME

Rex::Commands::DB - Simple Database Access

=head1 DESCRIPTION

This module gives you simple access to a database. Currently I<select>, I<delete>, I<insert> and I<update> is supported.

=head1 SYNOPSIS

 use Rex::Commands::DB {
                           dsn      => "DBI:mysql:database=test;host=dbhost",
                           user     => "username",
                           password => "password",
                       };
 
 task "list", sub {
    my @data = db select => {
                  fields => "*",
                  from   => "table",
                  where  => "enabled=1",
               };
           
   db insert => "table", {
                field1 => "value1",
                 field2 => "value2",
                 field3 => 5,
               };
                
   db update => "table", {
                    set => {
                       field1 => "newvalue",
                       field2 => "newvalue2",
                    },
                    where => "id=5",
                };
                
   db delete => "table", {
                 where => "id < 5",
              };
           
 };


=head1 EXPORTED FUNCTIONS

=over 4

=cut

package Rex::Commands::DB;

use strict;
use warnings;

use DBI;
use Rex::Logger;
use Data::Dumper;

use vars qw(@EXPORT $dbh);

@EXPORT = qw(db);

=item db

Do a database action.

 my @data = db select => {
               fields => "*",
               from   => "table",
               where  => "host='myhost'",
            };
            
 db insert => "table", {
               field1 => "value1",
               field2 => "value2",
               field3 => 5,
            };
             
 db update => "table", {
                  set => {
                     field1 => "newvalue",
                     field2 => "newvalue2",
                  },
                  where => "id=5",
             };
             
 db delete => "table", {
               where => "id < 5",
            };

=cut

sub db {

   my ($type, $table, $data) = @_;
   if(ref($table)) {
      my %d = %{$table};
      delete $d{"from"};
      $data = \%d;

      $table = $table->{"from"};
   }

   unless($table) {
      Rex::Logger::info("No table defined...')");
      return;
   }

   if($type eq "select") {
      my $sql = sprintf("SELECT %s FROM %s WHERE %s", $data->{"fields"} || "*", $table, $data->{"where"} || "1=1");
      if(defined $data->{"order"}) {
         $sql .= " ORDER BY " . $data->{"order"};
      }
      Rex::Logger::debug("sql: $sql");

      my $sth = $dbh->prepare($sql);
      $sth->execute or die($sth->errstr);

      my @return;

      while(my $row = $sth->fetchrow_hashref) {
         push @return, $row;
      }
      $sth->finish;

      return @return;
   }
   elsif($type eq "insert") {
      my $sql = "INSERT INTO %s (%s) VALUES(%s)";

      my @values;
      for my $key (keys %{$data}) {
         push(@values, "?");
      }

      $sql = sprintf($sql, $table, join(",", keys %{$data}), join(",", @values));
      Rex::Logger::debug("sql: $sql");

      my $sth = $dbh->prepare($sql);
      my $i=1;
      for my $key (keys %{$data}) {
         $data->{$key} ||= '';
         Rex::Logger::debug("sql: binding: " . $data->{$key});
         $sth->bind_param($i, $data->{$key}) or die($sth->errstr);
         $i++;
      }

      $sth->execute or die($sth->errstr);
   }
   elsif($type eq "update") {
      my $sql = "UPDATE %s SET %s WHERE %s";

      my @values;
      for my $key (keys %{$data->{"set"}}) {
         push(@values, "$key = ?");
      }

      $sql = sprintf($sql, $table, join(",", @values), $data->{"where"});
      Rex::Logger::debug("sql: $sql");

      my $sth = $dbh->prepare($sql);
      my $i=1;
      for my $key (keys %{$data->{"set"}}) {
         Rex::Logger::debug("sql: binding: " . $data->{"set"}->{$key});
         $sth->bind_param($i, $data->{"set"}->{$key}) or die($sth->errstr);
         $i++;
      }

      $sth->execute or die($sth->errstr);
   }
   elsif($type eq "delete") {
      my $sql = sprintf("DELETE FROM %s WHERE %s", $table, $data->{"where"});
      my $sth = $dbh->prepare($sql);
      $sth->execute or die($sth->errstr);
   }
   else {
      Rex::Logger::info("DB: action $type not supported.");
   }

}

=back

=cut

sub import {

   my ($class, $opt) = @_;

   if($opt) {
      $dbh = DBI->connect($opt->{"dsn"}, $opt->{"user"}, $opt->{"password"} || "");  
      $dbh->{mysql_auto_reconnect} = 1;
   }

   my ($ns_register_to, $file, $line) = caller;   

   no strict 'refs';
   for my $func_name (@EXPORT) {
      *{"${ns_register_to}::$func_name"} = \&$func_name;
   }
   use strict;

}

1;