#
# (c) Jan Gehring <jan.gehring@gmail.com>
#
# vim: set ts=2 sw=2 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.

Version <= 1.0: All these functions will not be reported.

=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;
{
  $Rex::Commands::DB::VERSION = '0.55.3';
}

use strict;
use warnings;

BEGIN {
  use Rex::Require;
  DBI->require;
}

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"} || "", $opt->{"attr"}
    );
    $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;