File Coverage

blib/lib/MySQL/Backup.pm

Criterion	Covered	Total	%
statement	6	311	1.9
branch	0	144	0.0
condition	0	29	0.0
subroutine	2	13	15.3
pod	0	11	0.0
total	8	508	1.5

package MySQL::Backup;

use strict;

use DBI;
our $VERSION = '0.04';

sub new{   #standart constructor
    my ($pkg, $dbname, $dbhost, $dbuser, $dbpass, $param) = @_;

    my $self           = {};
    my $dbh            = DBI->connect("DBI:mysql:$dbname:$dbhost", $dbuser, $dbpass, {RaiseError=>1});
    $self->{'DBH_OBJ'} = $dbh;
    $self->{'param'}   = {};

    foreach my $key(keys %{$param}){
      $self->{'param'}->{$key} = $param->{$key};
    }

    bless($self, $pkg);
    return $self;
}

sub new_from_DBH{   #if you have already DBI connection, you can use this
    my ($pkg, $dbh, $param) = @_;

    my $self           = {};
    $self->{'DBH_OBJ'} = $dbh;
    $self->{'param'}   = {};

    foreach my $key(keys %{$param}){
      $self->{'param'}->{$key} = $param->{$key};
    }

    bless($self, $pkg);
    return $self;
}

sub run_sql{
    my ($self, $sql) = @_;
    my $dbh = $self->{'DBH_OBJ'};
    #die $dbh->prepare($sql);

    my $sth = $dbh->prepare($sql) || die $self->caller();

    if (not $sth){
      die $DBI::errstr;
    }

    my $res = $sth->execute;
    if (not $res){
      return undef;
    }
    return $sth;
}

sub arr_hash{
    my ($self, $sql) = @_;
    my @res;
    if (my $sth = $self->run_sql($sql)){
      while (my $ref = $sth->fetchrow_hashref){
        push @res, $ref;
      }
    }
    return @res;
}

sub param{
    my ($self, $ref) = @_;
    if(ref $ref eq 'HASH'){
      foreach my $key(keys %{$ref}){
        $self->{'param'}->{$key} = $ref->{$key};
      }
    }
    elsif(ref $ref eq 'SCALAR'){
       if(defined $self->{'param'}->{$ref}){
         return $self->{'param'}->{$ref};
       }
       else{
         $self->{'error'} = 'can\'t return this param: please check if name of it is right. Also, this param can be undefined';
         return undef;
       }
    }
}

sub table_desc{ #creates a structure of the inputed table

    my ($self, $table) = @_;
    my @temp = $self->arr_hash("SHOW COLUMNS FROM $table");
    my @temp2;

    foreach my $ref(@temp){
      my $null = 'NOT NULL' if ($ref->{'Null'} !~ m/YES/i);
      my $default;
      if($ref->{'default'}){
        $default .= $null.' default '."'".$ref->{'Default'}."'";
      }
      else{
        if (($ref->{'Null'} =~ m/YES/i)and(!($ref->{'Type'} =~ m/timestamp/i))){
	  $default .= 'default '.'NULL';
        }
        else{
          $default .= $null;
	}
      }
      chomp $default;
      push @temp2, join(' ', '`'.$ref->{'Field'}.'`', $ref->{'Type'}, $default.($ref->{'Extra'}?' '.$ref->{'Extra'}:''));
    }

    my $columns = join(', ', @temp2);

    @temp = $self->arr_hash("SHOW KEYS FROM $table");
       foreach my $ref(@temp){
         if ($ref->{'Key_name'} =~ m/PRIMARY/i){
           $columns .= ", PRIMARY KEY (`".$ref->{'Column_name'}."`)";
	 }
         elsif ($ref->{'Non_unique'} =~ m/0/i){
           $columns .= ", UNIQUE INDEX (`".$ref->{'Column_name'}."`)";
	 }
         elsif ($ref->{'Index_type'} =~ m/FULLTEXT/i){
           $columns .= ", FULLTEXT INDEX (`".$ref->{'Column_name'}."`)";
	 }
         else{
           $columns .= ", INDEX (`".$ref->{'Column_name'}."`)";
	 }
       }
    my $sql = "CREATE TABLE `$table` ($columns);";

    return $sql;
}

sub create_structure{ #creates a structure of the current DB
    my $self = shift;
    my @arr;
    my $sql;

    unless($self->{'param'}->{'tables'}){
      my $sth = $self->run_sql("SHOW TABLES");
      while(my @temp = $sth->fetchrow_array()){
        push @arr, $temp[0];
      }

      foreach my $temp(@arr){
        $sql .= $self->table_desc($temp)."\n";
      }
    }
    else{
      @arr = @{$self->{'param'}->{'tables'}};
      foreach my $temp(@arr){
        $sql .= $self->table_desc($temp)."\n";
      }
    }

    return $sql;
}

sub get_table_data{

    my ($self, $table) = @_;
    my $data;
    my @temp = $self->arr_hash("SELECT * FROM $table WHERE 1");

    foreach my $ref (@temp){
      my @keys = keys %$ref;
      my $key_list = '`'.join('`, `', @keys).'`';
      my @values;
      for(my $i=0; $i<=$#keys; $i++){
        push @values, $self->{'DBH_OBJ'}->quote($ref->{$keys[$i]});
      }
      my $value_list = join(', ', @values);
      if($self->{'param'}->{'USE_REPLACE'}){
        $data .= "REPLACE INTO `$table` ($key_list) VALUES ($value_list);\n";
      }
      else{
        $data .= "INSERT INTO `$table` ($key_list) VALUES ($value_list);\n";
      }
    }

    return $data;
}

sub data_backup{ #get all data from current database

    my $self = shift;
    #my $sth = $self->run_sql("SHOW TABLES");
    #my (@tables, @tables_for_lock);
    #while(my $temp = $sth->fetchrow_array()){
    #  push @tables, "$temp";
    #  push @tables_for_lock, "$temp WRITE";
    #}
    #$self->run_sql("LOCK TABLES ".join(', ', @tables_for_lock));
    my @arr;
    my $sql = '';
    #foreach my $temp(@tables){
    #  $sql .= $self->get_table_data($temp);
    #}

    unless($self->{'param'}->{'tables'}){
      my $sth = $self->run_sql("SHOW TABLES");
      while(my @temp = $sth->fetchrow_array()){
        push @arr, $temp[0];
      }

      foreach my $temp(@arr){
        my $table_data = $self->get_table_data($temp);
        $sql .= "/* $temp */\n" if (($self->{'param'}->{'SHOW_TABLE_NAMES'})and($table_data));
        $sql .= $table_data if $table_data;
      }
    }
    else{
      foreach my $temp(@{$self->{'param'}->{'tables'}}){
        my $table_data = $self->get_table_data($temp);
        $sql .= "/* $temp */\n" if (($self->{'param'}->{'SHOW_TABLE_NAMES'})and($table_data));
        $sql .= $table_data if $table_data;
      }
    }

    #$self->run_sql("UNLOCK TABLES");
    return $sql;
}

sub run_restore_script{
    my ($self, $file) = @_;
    my $sth = $self->run_sql("SHOW TABLES");
    my $dbh = $self->{'DBH_OBJ'};
    my (@tables, @tables_for_lock);
    while(my $temp = $sth->fetchrow_array()){
      push @tables, "$temp";
      push @tables_for_lock, "$temp WRITE";
    }
    #$self->run_sql("LOCK TABLES ".join(', ', @tables_for_lock));
       #$sth = run_sql("FLUSH TABLES");
    foreach my $temp(@tables){
      $dbh->do("DROP TABLE IF EXISTS `$temp`");
    }

    open(FILE, $file);
    my $fline = readline(FILE);
    if ($fline =~ m/\r\n/){
         $/ = ";\r\n";
    }
    elsif($fline =~ m/\n\r/){
         $/ = ";\n\r";
    }
    elsif($fline =~ m/\r/){
         $/ = ";\r";
    }
    else{
         $/ = ";\n";
    }
    my @sql = ;
    unshift @sql, $fline;
    $/= "\n";
    close(FILE);

    foreach my $sql(@sql){
      chomp $sql;
      $self->run_sql($sql);
    }

    #$dbh->do("RESET MASTER");
    #$self->run_sql("UNLOCK TABLES");
    return \@sql;
}

sub run_upgrade_script{
       my $self =shift;
       my $file = shift;
       my $db_vers;
       my $dbh = $self->{'DBH_OBJ'};
       my $sth = $self->run_sql("SHOW TABLES");
       my (@tables, @tables_for_lock, $table_list);
       while(my $temp = $sth->fetchrow_array()){
         push @tables, "`$temp`";
         push @tables_for_lock, "`$temp` WRITE";
       }
       #run_sql("LOCK TABLES ".join(', ', @tables_for_lock));
       #$sth = run_sql("FLUSH TABLES");

       open(FILE, $file);
       my $fline = readline(FILE);
       if ($fline =~ m/\r\n/){
         $/ = ";\r\n";
       }
       elsif($fline =~ m/\n\r/){
         $/ = ";\n\r";
       }
       else{
         $/ = ";\n";
       }

       my @sql = ;
       unshift @sql, $fline;
       $/= "\n";
       close(FILE);

       foreach my $sql(@sql){
         chomp $sql;
         if($sql =~ /^CREATE TABLE ([`\w]+) \((.*)\)/i){
           for(my $i=0; $i<=$#tables; $i++){
	    #die "lc($tables[$i]) eq lc($1)";
             if(($tables[$i] eq $1)or(lc($tables[$i]) eq lc($1))){
                my $temp_2_1 = $2;
                my @columns_desc = split /, /, $temp_2_1;
                my $real_table = $self->table_desc($tables[$i]);
                #die $real_table
                #die $real_table."".$sql if $tables[$i] eq 'perldesk_kb_ratings';
		$real_table =~ /^CREATE TABLE ([`\w]+) \((.*)\)/i;
                my $temp_2_2 = $2;
                my @real_columns_desc = split /, /, $temp_2_2;



		my (@columns, @real_columns);
                $#columns      = $#columns_desc;
                $#real_columns = $#real_columns_desc;
		for(my $j=0; $j<=$#columns_desc; $j++){
                  $columns_desc[$j] =~ s/^\s*(.*?)\s*$/$1/gi;
                  if($columns_desc[$j] =~ m/^([`\w]+) /){
                    $columns[$j] = $1;
                  }
   	        }

		for(my $j=0; $j<=$#real_columns_desc; $j++){
                  $real_columns_desc[$j] =~ s/^\s*(.*?)\s*$/$1/gi;
                  if($real_columns_desc[$j] =~ m/^([`\w]+) /){
                    $real_columns[$j] = $1;
                  }
	        }

		my $bool = 0;
                for(my $j=0; $j<=$#columns; $j++){  #checking for existing of columns
                 for(my $k=0; $k<=$#real_columns; $k++){
                   $bool = 0;
		   if ($columns_desc[$j] =~ /^PRIMARY KEY \(([`\w]+)\)/){ #checking if it's Primary key
                      my $column_name = $1;
                      #die $tables[$i];
                      $sth = $self->run_sql("SHOW KEYS FROM $tables[$i]");

                      while(my $temp = $sth->fetchrow_hashref()){
                       # die $temp->{'Column_name'}.' '.$column_name;
                        if((($temp->{'Key_name'} eq 'PRIMARY')or($temp->{'Key_name'} eq 'primary'))and(('`'.$temp->{'Column_name'}.'`' eq $column_name)or('`'.lc($temp->{'Column_name'}).'`' eq lc($column_name)))){
                         $bool= 1;
                         #die $bool;
                         last;
			}
                      }
	  	   }


		   elsif ($columns_desc[$j] =~ /^KEY(.*)\(([`\w]+)\)/){  #checking if it's key
                    #for(my $l=0; $l<$#real_columns; $l++){
                     if($columns_desc[$j] eq $real_columns_desc[$k]){

                      $bool = 1;
                      last;
		     }
                    #}
                    if($bool){last;}
	  	   }
                  # elsif ($columns_desc[$j] =~ /^INDEX/){

                  # }
                   elsif ($columns_desc[$j] =~ /^INDEX(.*)\(([`\w]+)\)/){  #checking if it's key
                    #for(my $l=0; $l<$#real_columns; $l++){
                     if($columns_desc[$j] eq $real_columns_desc[$k]){
                     #die $real_columns_desc[$k];
                      $bool = 1;
                      #die $columns_desc[$j]."  ".$real_columns_desc[$k];
                      #die $bool;
                      last;
		     }
                    #}
                    if($bool){last;}
	  	   }
                   elsif ($columns_desc[$j] =~ /^UNIQUE INDEX(.*)\(([`\w]+)\)/){  #checking if it's key UNIQUE

                    #for(my $l=0; $l<$#real_columns; $l++){
                     if($columns_desc[$j] eq $real_columns_desc[$k]){
                      $bool = 1;
                      #die $columns_desc[$j];
                      last;
		     }
                    #}
                    if($bool){last;}
	  	   }

                   elsif ($columns_desc[$j] =~ /^FULLTEXT INDEX(.*)\(([`\w]+)\)/){  #checking if it's key FULLTEXT
                   #die $columns_desc[$j];
                    #for(my $l=0; $l<$#real_columns; $l++){
                     if($columns_desc[$j] eq $real_columns_desc[$k]){
                      $bool = 1;
                      last;
		     }
                    #}
                    if($bool){last;}
	  	   }

		   elsif(lc($columns[$j]) eq lc($real_columns[$k])){ #checking for existence of column
                    if(lc($columns_desc[$j]) eq lc($real_columns_desc[$k])){
                     $bool = 1;
                     last;
                    }
                    else{
                    #die "lc($columns[$j]) eq lc($real_columns[$k])";
                    # die "$columns_desc[$j] eq $real_columns_desc[$k]";
                     $bool = 2;
                     last;
		    }
		   }
                 }
                   my $key;
		 unless($bool){ #the column or Key doesn't exist
                   #chomp $columns_desc[$j];

                   if($columns_desc[$j] =~ /^PRIMARY KEY \(([`\w]+)\)/){

                     my $column_name = $1; #die $column_name;
                     $key = "PRIMARY KEY ($column_name)";
                     #killimg duplicates
                     my $sth = $self->run_sql("SELECT * FROM $tables[$i] WHERE 1");
                     while(my $temp = $sth->fetchrow_hashref()){
                      my $th = $self->run_sql("SELECT * FROM $tables[$i] WHERE $column_name = ".$dbh->quote($temp->{$column_name}));
                      my $rows = $th->rows;
                      if($rows > 1){
                          $self->run_sql("DELETE FROM $tables[$i] WHERE $column_name = ".$dbh->quote($temp->{$column_name})." LIMIT 1");
                          $table_list.="DELETE FROM $tables[$i] WHERE $column_name = ".$dbh->quote($temp->{$column_name})." LIMIT 1\n";
		      }
                     }
                   }
                   my $key_name;
		   unless ($key){
                    if($columns_desc[$j] =~ /^KEY (.*) \((.*)\)/){
                      $key = "INDEX $1 ($2)";
                      $key_name = $1;
		    }
                    elsif($columns_desc[$j] =~ /^KEY(.*)\((.*)\)/){
                      $key = "INDEX ($2)";
                      $key_name = $2;
		    }
                    elsif($columns_desc[$j] =~ /^INDEX (.*) \((.*)\)/){
                      $key = "INDEX $1 ($2)";
                      $key_name = $1;
		    }
                    elsif($columns_desc[$j] =~ /^INDEX(.*)\((.*)\)/){
                      $key = "INDEX ($2)";
                      $key_name = $2;
		    }
                    elsif($columns_desc[$j] =~ /^UNIQUE INDEX (.*) \((.*)\)/){
                      $key = "UNIQUE INDEX $1 ($2)";
                      $key_name = $1;
		    }
		    elsif($columns_desc[$j] =~ /^UNIQUE INDEX(.*)\((.*)\)/){
                      $key = "UNIQUE INDEX ($2)";
                      $key_name = $2;
		    }
                    elsif($columns_desc[$j] =~ /^FULLTEXT INDEX (.*) \((.*)\)/){
                      $key = "FULLTEXT INDEX $1 ($2)";
                      $key_name = $1;
		    }
		    elsif($columns_desc[$j] =~ /^FULLTEXT INDEX(.*)\((.*)\)/){
                      $key = "FULLTEXT INDEX ($2)";
                      $key_name = $2;
		    }

		   }

                   unless($key){
                    my $add;
                    if($columns_desc[$j] =~ m/auto_increment/i){$add=' PRIMARY KEY';}
                    my $sql2 = "ALTER TABLE $tables[$i] ADD COLUMN ".$columns_desc[$j].$add;
                    eval{$dbh->do($sql2)} or die $self->errstr("Couldn't execute statement: $sql2 $DBI::errstr: stopped");
		    $table_list .= $sql2."\n";
                   }
                   else{

                    my $sql2 = "SHOW KEYS FROM $tables[$i]";
                    #die $sql2;
                    my $sth = $self->run_sql($sql2);
		    $table_list .= $sql2."\n";
                    my $key_exists;
                    while(my $ref = $sth->fetchrow_hashref()){
                        die $ref->{'Key_name'}.' ? '.$key_name;
                        if('`'.$ref->{'Key_name'}.'`' eq $key_name){
                           $key_exists = 1;
                           last;
			}
		    }
                    unless($key_exists){
                        $sql2 = "ALTER TABLE $tables[$i] ADD $key";
                        die $sql2;
                        $self->run_sql($sql2);
		        $table_list .= $sql2."\n";
                    }
		   }

                   #$bool =1;
 	         }
                 elsif($bool == 2){
                    my $add;
                    #if($columns_desc[$j] =~ m/auto_increment/i){$add=' PRIMARY KEY';}
                    my $sql2 = "ALTER TABLE $tables[$i] MODIFY COLUMN ".$columns_desc[$j].$add;
                    eval{$dbh->do($sql2)} or die $self->errstr("Couldn't execute statement: $sql2 $DBI::errstr: stopped");
		    $table_list .= $sql2."\n";
		 }
                }

	        $table_list .= "Log: ".$tables[$i]." ".$2."\n";
                last;
	       }
             if($i == $#tables){ #table doesn't exist
              $dbh->do($sql);
	      $table_list .= $sql."\n";
	     }
	   }
	 }
         elsif($sql =~ /^ALTER TABLE (.*) DROP INDEX (.*);/i){
               $sth = $self->run_sql("SHOW KEYS FROM $1");
               my $bool = 0;
               while(my $temp = $sth->fetchrow_hashref()){
                   if(lc($temp->{'Key_name'}) eq lc($2)){
                      $bool= 1;
                      last;
	           }
               }
               if ($bool){
                 $table_list .= $sql."\n";
                 $dbh->do($sql);
	       }
	 }
         elsif(($sql =~ /^INSERT/i)or($sql =~ /^REPLACE/i)){
           $sql =~ s/^INSERT/REPLACE/i;
           $sql =~ m/INTO (\w+)/i;
           my $table = $1;
           if(($table eq 'perlDesk_settings')and($sql !~ /^ALTER/i)){
              if($sql =~ m/\(value, setting\) VALUES \('(.*)', 'db_version'\);$/i){
                $db_vers = $1;
                $table_list .= $sql."\n";
                $self->run_sql($sql);
              }
              else{
                $sql =~ m/\(value, setting\) VALUES \('(.*)', '(.*)'\);$/i;
                my ($value, $setting) = ($1, $2);
                my $sth = $self->run_sql("SELECT * FROM perlDesk_settings WHERE setting=".$dbh->quote($setting));
                unless($sth->rows){
                 $table_list .= $sql."\n";
                 $self->run_sql($sql);
		}
	      }
	   }
           else{
	     $table_list .= $sql."\n";
             my $rt = $dbh->prepare($sql);
             $rt->execute;
           }
	 }
	 else{
             $table_list .= $sql."\n";
             my $rt = $dbh->prepare($sql);
             $rt->execute;
         }
       }




       $self->run_sql("UNLOCK TABLES");
       return $db_vers;
      }

1;

__END__