# file KLEIDER/web/src/localization/MySQLLookup.pm
# Übersetzung in MySQL-Datentenbanksdatenbank suchen und einfügen
# 2016-07-18, Herbert Schiemann <h.schiemann@herbaer.de>
# GPL Version 2 oder neuer

package Herbaer::Translate::MySQLLookup ;

use parent Herbaer::Translate::Base ;
use Encode;
use utf8;

sub new {
   use DBI;
   my ($class
       , $secrets
       , $dbkey
       , $learner
       , $dbg
      ) = @_;
   $dbg ||= 0;
   if ($dbg) {
      print STDERR "Herbaer::Translate::MySQLLookup::new\n";
   }
   my $dbname;

   my $hnd;
   if (! open ($hnd, "<:encoding(utf-8)", $secrets)) {
      print STDERR "Kann Datei $secrets nicht lesen:$!\n" if $dbg;
      return undef;
   }
   my $line;
   my $user;
   my $password;
   while (defined ($line = <$hnd>)) {
      $line =~ s/\s*$//;
      if ( $line =~ /^\s*translate.mysql.([a-z0-9]+)\s*=\s*(.+)/ ) {
         if ($1 eq $dbkey) {
            $dbname = $2;
         }
      }
      elsif ($line =~ /^\s*mysql\.([a-z0-9]+)\.([a-z]+)\s*=\s*(.+)/) {
         if ($dbname && $1 eq $dbname) {
            if ($2 eq "user") {
               $user = $3;
            }
            elsif ($2 eq "password") {
               $password = $3;
            }
         }
      }
   }
   close $hnd;
   my $db = DBI -> connect ("DBI:mysql:$dbname", $user, $password);
   if (!$db) {
      print STDERR "Keine Verbindung zur MySQL-Datenbank $dbname\n" if $dbg;
      return undef;
   }
   my $self = {
      # Datenbank-Handles
      "db"  => $db,
      "ss"  => $db -> prepare (
         "SELECT id FROM src WHERE lang = ? AND txt = ?"
         ),
      "st"  => $db -> prepare (
         "SELECT txt FROM translation WHERE src_id = ? AND lang = ?"
         ),
      "is"  => $db -> prepare (
         "INSERT INTO src (lang, txt) VALUES (?, ?)"
         ),
      "it"  => $db -> prepare (
         "INSERT INTO translation (txt, src_id, lang) VALUES (?, ?, ?)"
         ),
      "ut"  => $db -> prepare (
         "UPDATE translation SET txt = ? WHERE src_id = ? AND lang = ?"
         ),
      "dbg"     => $dbg,
      "dbname"  => $dbname,
      "learner" => $learner,
   } ;
   return bless ($self, $class);
} # new

sub translate {
   my ($self, $text, $srl, $tgl) = @_ ;
   my $dbg = $self -> {"dbg"};
   print STDERR "Herbaer::Translate::MySQLLookup::translate\n  $srl -> $tgl: $text\n"
      if $dbg;

   # hier keine Prüfung der Parameter
   my $h = $self -> {"ss"};
   my $ar;
   my $id;
   my $tt;
   $h -> execute ($srl, encode ("utf-8", $text));
   if ($ar = $h -> fetchrow_arrayref()) {
      $id = $ar -> [0];
      print STDERR "  Gefunden id: $id\n" if $dbg;
   }
   if ($id) {
      $h = $self -> {"st"};
      $h -> execute ($id, $tgl);
      if ($ar = $h -> fetchrow_arrayref()) {
         $tt = decode ("utf-8", $ar -> [0]);
         print STDERR "  Übersetzung gefunden\n" if $dbg;
      }
   }
   $tt;
} # translate

sub learn {
   my ($self, $text, $srl, $tgl, $trans) = @_ ;
   my $dbg = $self -> {"dbg"};
   print STDERR
      "Herbaer::Translate::MySQLLookup::learn\n",
      "  $srl: $text\n",
      "  $tgl: $trans\n" 
      if $dbg;
   # hier keine Parameter-Prüfung

   my $id;
   my $ar;
   my $h = $self -> {"ss"};
   $text  = encode ("utf-8", $text);
   $trans = encode ("utf-8", $trans);
   $h -> execute ($srl, $text);
   if ($ar = $h -> fetchrow_arrayref()) {
      $id = $ar -> [0];
      print STDERR "  Gefunden id: $id\n" if $dbg;
   } 
   if (!$id) {
      $h = $self -> {"is"};
      $h -> execute ($srl, $text);
      $id = $h -> {"mysql_insertid"};
      print STDERR "  Eingefügt id: $id\n" if $dbg;
   }
   return 0 if !$id;
   my $tt;
   $h = $self -> {"st"};
   $h -> execute ($id, $tgl);
   if ($ar = $h -> fetchrow_arrayref()) {
      $tt = $ar -> [0] || "";
      $self -> {"ut"} -> execute ($trans, $id, $tgl) if $tt ne $trans;
   }
   else {
      $self -> {"it"} -> execute ($trans, $id, $tgl);
   }
   return 1;
} # learn

sub _learner {
   my $self = shift;
   my $lrn;
   if (! ( $lrn = $self -> {"lrn"}) && $self -> {"learner"} ) {
      $self -> {"lrn"} ||= new Herbaer::Translate ($self -> {"learner"});
   }
   $lrn;
} # _learner

sub finish {
   my $self = shift;
   my $dbg = $self -> {"dbg"};
   print STDERR "Herbaer::Translate::MySQLLookup::finish\n" if $dbg;
   if ($self -> {"learner"}) {
      my $lrn = $self -> _learner ();
      my $db = $self -> {"db"};
      if (!$lrn) {
         print STDERR
            "Herbaer::Translate::MySQLLookup::finish\n",
            "Kein Übersetzer mit Kennung \"", $self -> {"learner"}, "\"\n"
            if $dbg;
      }
      elsif (!$db) {
         print STDERR
            "Herbaer::Translate::MySQLLookup::finish\n",
            "Keine Verbindung zur Datenbank \"", $self -> {"dbname"}, "\"\n"
            if $dbg;
      }
      else {
         my $ss = $db -> prepare ("SELECT id, lang, txt FROM src");
         my $st = $db -> prepare ("SELECT lang, txt FROM translation where src_id = ?");
         my $id;
         my $sr;
         my $tr;
         my $srl;
         my $text;
         my $tgl;
         my $trans;
         $ss -> execute ();
         while ($sr = $ss -> fetchrow_arrayref()) {
            ($id, $srl, $text) = @$sr;
            $text = decode ("utf-8", $text);
            $st -> execute ($id);
            while ($tr = $st -> fetchrow_arrayref()) {
               ($tgl, $trans) = @$tr;
               $lrn -> learn ($text, $srl, $tgl, decode ("utf-8", $trans));
            }
         }
         $ss -> finish ();
         $st -> finish ();
         $db -> do ("DELETE FROM src");
         $db -> do ("DELETE FROM translation");
         $lrn -> finish ();
      }
   }
   $self;
} # finish

# Name des Uebersetzers
sub translator_name {
   my $self = shift;
   my $lrn  = $self -> _learner ();
   my $tn   = "mysql_" . $self -> {"dbname"};
   if ($lrn) {
      $tn . "_" . $lrn -> translator_name ();
   }
   else {
      $tn;
   }
} # translator_name

sub DESTROY {
   my $self = shift;
   my $dbg = $self -> {"dbg"};
   print STDERR "Herbaer::Translate::MySQLLookup::DESTROY\n" if $dbg;
   if ($self -> {"db"}) {
      $self -> {"ss"} -> finish();
      $self -> {"st"} -> finish();
      $self -> {"is"} -> finish();
      $self -> {"it"} -> finish();
      $self -> {"ut"} -> finish();
      $self -> {"db"} -> disconnect();
   }
} # DESTROY

1;
# end of file KLEIDER/web/src/localization/MySQLLookup.pm