Locale::Framework::SQL - An SQL Backend for Locale::Framework

  use Locale::Framework;
  use Locale::Framework::SQL;

  Locale::Framework::init(new Locale::Framework::SQL(
                               DSN => "dbi:Pg:dbname=zclass;host=localhost", 
                               DBUSER => "test", 
                               DBPASS => "testpass", 
                               [TABLE => "testtrans"]));

  Locale::Framework::language("en");

  print _T("This is a test");

  Locale::Framework::language("nl");

  print _T("This is a test");

package Locale::Framework::SQL;

use strict;
use DBI;
use DBI::Const::GetInfoType;

our $VERSION='0.07';

my %cache;

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


  my $dsn=$args->{"DSN"} or die "You need to specify a valid DSN for DBI (DSN => ...)";
  my $user=$args->{"DBUSER"} or die "You need to specify a database user (DBUSER => ...)";
  my $pass=$args->{"DBPASS"};

  my $self;

  $self->{"dsn"}=$dsn;
  $self->{"dbh"}=DBI->connect($dsn,$user,$pass);
  $self->{"dbh"}->{"PrintError"}=0;
  $self->{"table"}=$args->{"TABLE"} or die "You need to specify a valid table name (TABLE => ...)";
  $self->{"status"}="none";

  my $table=$self->{"table"};

  bless $self,$class;

  { # Check existence

    my $sth=$self->{"dbh"}->prepare("SELECT COUNT(txt) FROM $table");
    my $dbh=$self->{"dbh"};

    if (not $sth->execute()) {
      $sth->finish();

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

      if ($driver eq "pg") {
	$self->{"dbh"}->do("CREATE TABLE $table (txt varchar, lang varchar(32), translation varchar, translated numeric(1))");
	$self->{"dbh"}->do("CREATE INDEX $table"."_idx ON $table(lang,txt)");
      }
      elsif ($driver eq "mysql") {
	$self->{"dbh"}->do("CREATE TABLE $table (txt text, lang varchar(32), translation text, translated numeric(1))");
	$self->{"dbh"}->do("CREATE INDEX $table"."_idx ON $table(lang,txt(200))");
      }
      elsif ($driver eq "sqlite") {
	$self->{"dbh"}->do("CREATE TABLE $table (txt text, lang varchar(32), translation text, translated numeric(1))");
	$self->{"dbh"}->do("CREATE INDEX $table"."_idx ON $table(lang,txt)");
      }
      else {
	die "Cannot create  table $table (txt varchar(BIG), lang varchar(32), translation varchar(big),translated numeric(1))\n".
	    "I don't know this database system '$driver'";
      }
    }
    else {
      $sth->finish();
    }
  }

return $self;
}

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

sub translate {
  my $self=shift;
  my $lang=shift;
  my $text=shift;

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

  if ($lang eq "") { return $text; }
  else {
    if (exists $cache{"$lang && $text"}) { 
      return $cache{"$lang && $text"};
    }
    else {
      my $sth=$dbh->prepare("SELECT translation FROM $table WHERE txt='$text' AND lang='$lang'");
      $sth->execute();
      if ($sth->rows() gt 0) {
	my @r=$sth->fetchrow_array();
	$cache{"$lang && $text"}=shift @r;
	$sth->finish();
	return $self->translate($lang,$text);
      }
      else {
	$cache{"$lang && $text"}=$text;
	$sth->finish();
	$dbh->do("INSERT INTO $table (translation, lang, txt, translated) VALUES ('$text','$lang','$text',0)");
	return $self->translate($lang,$text);
      }
    }
  }
}

sub clear_cache {
    %cache = ();
}

sub set_translation {
  my $self=shift;
  my $lang=shift;
  my $text=shift;
  my $translation=shift;

  if ($lang eq "") {
    die "Cannot set a translation for an empty language";
  }

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

  my $sth=$dbh->prepare("SELECT translation FROM $table WHERE txt='$text' AND lang='$lang'");
  $sth->execute();
  if ($sth->rows() gt 0) {
    $sth->finish();
    $dbh->do("UPDATE $table SET translation='$translation', translated=1 WHERE txt='$text' AND lang='$lang'");
  }
  else {
    $sth->finish();
    $dbh->do("INSERT INTO $table (translation, lang, txt, translated) VALUES ('$text','$lang','$text',0)");
  }

  $cache{"$lang && $text"}=$translation;

return 1;
}

1;

__END__