File Coverage

blib/lib/Config/Backend/SQL.pm

Criterion	Covered	Total	%
statement	9	81	11.1
branch	0	18	0.0
condition	0	2	0.0
subroutine	3	9	33.3
pod	5	5	100.0
total	17	115	14.7

package Config::Backend::SQL;

use 5.006;
use strict;
use DBI;

our $VERSION='0.10';

sub new {
  my $class=shift;
  my $args={
	    DSN => undef,
	    DBUSER => undef,
	    DBPASS => undef,
	    TABLE => "conf_table",
	    @_
	    };

  my $dsn=$args->{"DSN"} or die "You need to specify a valid DSN for Conf::SQL";
  my $user=$args->{"DBUSER"} or die "You need to specify a valid DB User for Conf::SQL";
  my $pass=$args->{"DBPASS"};
  my $table=$args->{"TABLE"} or die "You need to specify a valid tablename for Conf::SQL";
  my $self;

  # Read in config

  $self->{"dbh"}=DBI->connect($dsn,$user,$pass);
  $self->{"TABLE"}=$table;
  $self->{"dbh"}->{"PrintError"}=0;

  # Table exists?

  my $sth=$self->{"dbh"}->prepare("SELECT COUNT(var) FROM $table");
  $sth->execute();
  if (not $sth->execute()) {
    $sth->finish();

    my $dbh=$self->{"dbh"};
    my $driver=lc($dbh->{Driver}->{Name});

    if ($driver eq "pg") { # PostgreSQL
      $dbh->do("CREATE TABLE $table(uid varchar,var varchar,value varchar)");
      $dbh->do("CREATE INDEX $table"."_idx ON $table(uid, var)");
    }
    elsif ($driver eq "mysql") { # mysql
      $dbh->do("CREATE TABLE $table(uid varchar(250),var text,value mediumtext)");
      $dbh->do("CREATE INDEX $table"."_idx ON $table(uid, var(200))");
    }
    elsif ($driver eq "sqlite") { # sqlite
      $dbh->do("CREATE TABLE $table(uid varchar(250),var varchar(1024),value text)");
      $dbh->do("CREATE INDEX $table"."_idx ON $table(uid, var)");
    }
    else {
      die "Cannot create table CREATE TABLE $table(uid varchar(250),var varchar(1024),value text)\n".
          "and index           CREATE INDEX $table"."_idx ON $table(uid, var)\n".
	  "I don't know this database system '$driver'";
    }
  }
  else {
    $sth->finish();
  }

  # Get USER ID

  $self->{"user"}=getlogin() || getpwuid( $< ) ||
                  $ENV{ LOGNAME } || $ENV{ USER } ||
                  $ENV{ USERNAME } || 'unknown';

  # bless

  bless $self,$class;

return $self;
}

sub DESTROY {
  my $self=shift;
  $self->{"dbh"}->disconnect();
}

sub set {
  my $self=shift;
  my $var=shift;
  my $val=shift;

  my $user=$self->{"user"};
  my $dbh=$self->{"dbh"};
  my $table=$self->{"TABLE"};

  # Update or insert?

  my $sth=$dbh->prepare("SELECT COUNT(var) FROM $table WHERE uid='$user' AND var='$var'");
  $sth->execute();
  my ($count)=$sth->fetchrow_array();
  $sth->finish();

  if ($count==0) {
    $dbh->do("INSERT INTO $table (var,uid,value) VALUES (".$dbh->quote($var).",".$dbh->quote($user).",".$dbh->quote($val).")");
  }
  else {
    $dbh->do("UPDATE $table SET value=".$dbh->quote($val)." WHERE uid=".$dbh->quote($user)." AND var=".$dbh->quote($var));
  }
}

sub get {
  my $self=shift;
  my $var=shift;

  my $user=$self->{"user"};
  my $dbh=$self->{"dbh"};
  my $table=$self->{"TABLE"};

  # get

  my $val=undef;
  my $sth=$dbh->prepare("SELECT value FROM $table WHERE uid=".$dbh->quote($user)." AND var=".$dbh->quote($var));
  $sth->execute();
  if ($sth->rows()!=0) {
    ($val)=$sth->fetchrow_array();
  }
  $sth->finish();

return $val;
}

sub del {
  my ($self,$var)=@_;

  my $user=$self->{"user"};
  my $dbh=$self->{"dbh"};
  my $table=$self->{"TABLE"};

  $dbh->do("DELETE FROM $table WHERE uid=".$dbh->quote($user)." AND var=".$dbh->quote($var));
}

sub variables {
  my $self=shift;

  my $user=$self->{"user"};
  my $dbh=$self->{"dbh"};
  my $table=$self->{"TABLE"};

  # get variables

  my @vars;
  my $sth=$dbh->prepare("SELECT var FROM $table WHERE uid='$user'");
  $sth->execute();
  for (1..$sth->rows()) {
    my ($var)=$sth->fetchrow_array();
    push @vars,$var;
  }
  $sth->finish();

return @vars;
}

1;
__END__