mysqlaccess

开启mysql的远程连接的方法 mysql-noinstall-5.1.6-alpha-win32.zip

    print MYSQL_Q "$query\n";
#    print STDERR $query;

    $answer = <MYSQL_A>; #answer from mysql
#    print STDERR "A>",$answer;
    MySQLaccess::Debug::Print(2,"Answer: $answer\n");
    foreach $nerror (sort(keys(%ACCESS_ERR))) {
#       print STDERR "->$nerror?";
       MySQLaccess::Debug::Print(3,"check answer for error $ACCESS_ERR{$nerror}");
       if (grep(/$ACCESS_ERR{$nerror}/i,$answer)) { 
#          print STDERR "Yes!";
          MySQLaccess::Debug::Print(2,"Answer contain error [$nerror]");
          return $nerror; 
       }
    }

    $delim = <MYSQL_A>; # read header
    if ($delim ne "ok\n") {
       while (($line=<MYSQL_A>) ne "ok\n")
       { MySQLaccess::Debug::Print(3," A> $line"); }
        $skip = <MYSQL_A>; # skip result 'ok'
    }
#    print STDERR "CreateTable done\n";
    return 0;
}


# ===========================================================
# sub CopyTable()
#  Copy the structure and the data of a table to another table
# ===========================================================
sub CopyTable {
    my ($from,$to,$force) = @_;
    my @known_tables  = Show_Tables();    
    my $query = "";
    my $nerror= 0;
    my $skip;

#    print STDERR "CopyTable($from,$to)\n";
    MySQLaccess::Debug::Print(1,"MySQLaccess::DB::CopyTable($from,$to)");

    ## error-handling
    if (!grep(/^$from$/,@known_tables)) { return 'Unknown_table'; }

    ## copy structure 
    ## if forced
    if (defined($force) and $force) {
       return $nerror if ($nerror=CreateTable($to,$force)); 
#       print STDERR "Structure copied\n";
    }

    ## copy data
    $query .= "DELETE FROM $to;";
    $query .= "INSERT INTO $to SELECT * FROM $from;";
    $query .= "SELECT 'ok';\n";
    MySQLaccess::Debug::Print(2,"Query: $query");
       
    ## execute query
    print MYSQL_Q "$query\n";
#    print STDERR $query;

    ## check for errors...
    my $answer = <MYSQL_A>; #answer from mysql
#    print STDERR $answer;
    MySQLaccess::Debug::Print(2,"Answer: $answer\n");
    foreach $nerror (sort(keys(%ACCESS_ERR))) {
       MySQLaccess::Debug::Print(3,"check answer for error $ACCESS_ERR{$nerror}");
       if (grep(/$ACCESS_ERR{$nerror}/i,$answer)) { 
          MySQLaccess::Debug::Print(2,"Answer contain error [$nerror]");
          return $nerror; 
       }
    }

    my $delim = <MYSQL_A>; # read header
#    print STDERR $delim;
    if ($delim ne "ok\n") {
       while (($line=<MYSQL_A>) ne "ok\n")
       { MySQLaccess::Debug::Print(3," A> $line"); }
       $skip = <MYSQL_A>; # skip result 'ok'
    }

    return 0;
}

# ===========================================================
# sub LoadTmpTables()
#  (Re)load temporary tables with entries of ACL-tables
# ===========================================================
sub LoadTmpTables {
    my %tables = ( $MySQLaccess::ACCESS_U => $MySQLaccess::ACCESS_U_TMP,
                   $MySQLaccess::ACCESS_H => $MySQLaccess::ACCESS_H_TMP,
                   $MySQLaccess::ACCESS_D => $MySQLaccess::ACCESS_D_TMP,
                 ); 
    my $tbl;
    my $nerror;
    
#    print STDERR "LoadTmpTables:\n";
    MySQLaccess::Debug::Print(1,"LoadTmpTables():");
    foreach $tbl (keys(%tables)) {
#       print STDERR "$tbl -> $tables{$tbl}\n";
       MySQLaccess::Debug::Print(2,"Loading table $tbl -> $tables{$tbl}.");
       return $nerror if ($nerror=CopyTable($tbl,$tables{$tbl},'force'));
    }
    return 0;
}

# ===========================================================
# sub BackupGrantTables()
#  Make a backup of the original grant-tables
# ===========================================================
sub BackupGrantTables {
    my %tables = ( $MySQLaccess::ACCESS_U => $MySQLaccess::ACCESS_U_BCK,
                   $MySQLaccess::ACCESS_H => $MySQLaccess::ACCESS_H_BCK,
                   $MySQLaccess::ACCESS_D => $MySQLaccess::ACCESS_D_BCK,
                 ); 
    my $tbl;
    my $nerror;
    
#    print STDERR "BackupGrantTables:\n";
    MySQLaccess::Debug::Print(1,"BackupGrantTables():");
    foreach $tbl (keys(%tables)) {
#       print STDERR "$tbl -> $tables{$tbl}\n";
       MySQLaccess::Debug::Print(2,"Backup table $tbl -> $tables{$tbl}.");
       return $nerror if ($nerror=CopyTable($tbl,$tables{$tbl},'force'));
    }
    return 0;
}

# ===========================================================
# sub RollbackGrantTables()
#  Rollback the backup of the grant-tables
# ===========================================================
sub RollbackGrantTables {
    my %tables = ( $MySQLaccess::ACCESS_U_BCK => $MySQLaccess::ACCESS_U,
                   $MySQLaccess::ACCESS_H_BCK => $MySQLaccess::ACCESS_H,
                   $MySQLaccess::ACCESS_D_BCK => $MySQLaccess::ACCESS_D,
                 ); 
    my $tbl;
    my $nerror;
    
#    print STDERR "RollbackGrantTables:\n";
    MySQLaccess::Debug::Print(1,"RollbackGrantTables():");
    foreach $tbl (keys(%tables)) {
#       print STDERR "$tbl -> $tables{$tbl}\n";
       MySQLaccess::Debug::Print(2,"Rollback table $tbl -> $tables{$tbl}.");
       return $nerror if ($nerror=CopyTable($tbl,$tables{$tbl},'force'));
    }
    return 0;
}


# ===========================================================
# sub CommitGrantTables()
#  Copy grant-rules from temporary tables to the ACL-tables
# ===========================================================
sub CommitGrantTables {
    my %tables = ( $MySQLaccess::ACCESS_U => $MySQLaccess::ACCESS_U_TMP,
                   $MySQLaccess::ACCESS_H => $MySQLaccess::ACCESS_H_TMP,
                   $MySQLaccess::ACCESS_D => $MySQLaccess::ACCESS_D_TMP,
                 ); 
    my $tbl;
    my $query;
    my $delim;
    my $skip;
    my $create;

    print STDERR "CommitGrantTables()\n";
    MySQLaccess::Debug::Print(1,"CommitGrantTables():");
    
    ## Make backup of original grant-tables
    MySQLaccess::Debug::Print(2,"Making backup of original grant-tables...");
    BackupGrantTables();

    ## Copy data from temporay tables to grant-tables
    foreach $tbl (keys(%tables)) {
       print STDERR "$tbl -> $tables{$tbl}\n";
       MySQLaccess::Debug::Print(2,"Loading data $tables{$tbl} -> $tbl.");
       return $nerror if ($nerror=CopyTable($tables{$tbl},$tbl));
    }
    return 0;
}


# ===========================================================
# sub Show_Fields($table): 
#  return (a reference to) a hash which holds the names
#  of all relevant grant-fields, with their index in the record,
#  and (a reference to) an array which holds the fieldnames.
# ===========================================================
sub Show_Fields {
    my ($table) = @_;
    my %skip = ('host' => [0,1]
               ,'user' => [0,1,2]
               ,'db'   => [0,1,2]
               );
    my %Struct = ();
    my @Struct = ();
    my $query = "show fields from $table;select 'ok';\n";
    my $i=0;
    my $line;

#print STDERR $query;
    MySQLaccess::Debug::Print(1,"Show_Fields($table):");
    MySQLaccess::Debug::Print(2,"SQL: $query");

    print MYSQL_Q "$query";
    my $skip = <MYSQL_A>;  #skip header
    while (($line=<MYSQL_A>) ne "ok\n")
    {
#print STDERR ">",$line;
	chop($line);
	MySQLaccess::Debug::Print(2," $table>: $line");
	my ($field,$type,$null,$key,$default,$extra) = split(' ',$line);
        $field = ucfirst($field); 
	MySQLaccess::Debug::Print(3, " <split: $field - $type - $null - $key - $default - $extra");
	if (! grep(/$i/,@{$skip{$table}}) ){
	   $Struct{$field} = $i; #hash
	   push(@Struct,$field); #array
	   MySQLaccess::Debug::Print(3," ==> added column[$i]: $field ($Struct{$field})");
	} 
        else {
           MySQLaccess::Debug::Print(3," ==> skipped column[$i], value=[$field]");
        }
	$i++;
    }

    $skip=<MYSQL_A>;  # Get ok row (found already ok header)

    MySQLaccess::Debug::Print(2, "Array:");
    foreach $field (@Struct) { MySQLaccess::Debug::Print(2,"+ $field"); }
    MySQLaccess::Debug::Print(2,"Hash:");
    foreach $field (keys(%Struct)) { MySQLaccess::Debug::Print(2,"+ $field -> $Struct{$field}"); }

    return  (\%Struct,\@Struct); 
}

# ===========================================================
# sub Show_Tables(): 
#  return (a reference to) an array which holds all 
#  known tables.
# ===========================================================
sub Show_Tables {
    my @Tables = ();
    my $query = "show tables;select 'ok';\n";
    my $i=0;
    my $line;

    MySQLaccess::Debug::Print(1,"Show_Tables():");
    MySQLaccess::Debug::Print(2,"SQL: $query");

    print MYSQL_Q "$query";
    my $skip = <MYSQL_A>;  #skip header
    while (($line=<MYSQL_A>) ne "ok\n")
    {
	chop($line);
	push(@Tables,$line); #array
	MySQLaccess::Debug::Print(3," ==> added table: $line");
    }

    $skip=<MYSQL_A>;  # Get ok row (found already ok header)

    MySQLaccess::Debug::Print(2, "Array:");
    foreach $tbl (@Tables) { MySQLaccess::Debug::Print(2,"+ $tbl"); }

    return @Tables; 
}

# ======================================
# sub Validate_Password($passwd,$host,$user,$encpw)
#  Validate the given password 
#  for user '$user' 
#  connecting from host '$host'
# ======================================
sub Validate_Password {
    my ($password,$host,$user,$encpw) = @_;
    my $valid=0;

    MySQLaccess::Debug::Print(1,"Validate_Password($password,$host,$user,$encpw)");
    my $sql = "select host,user,password from user having "
             ."host='$host' and user='$user' and password='$encpw' "
             ."and password=PASSWORD('$password');\n";
    $sql .= "select 'ok';\n";
    MySQLaccess::Debug::Print(2,"SQL = $sql");
    print MYSQL_Q "$sql";
    
    # if password is valid, at least 1 row returns before we read 'ok'
    while ( ($line=<MYSQL_A>) ne "ok\n") {
      MySQLaccess::Debug::Print(2," A> $line");
      $valid = defined($line); 
    }
    my $skip = <MYSQL_A>; # read 'ok'

    return $valid;
}


# ==========================================================
# sub Sort_fields: (rewritten by psmith)
#  Build the query for an ordered list of entries
# ==========================================================
sub Sort_fields {
  my ($start, $end, $sofar, $this, @rest) = (@_);
  my @where = ("((FIELD not like '\\%') AND (FIELD <> ''))",
               "((FIELD like '%\\%%') OR (FIELD like '%\\_%'))",
               "(FIELD = '')");
  my $res = '';

  $this or return ("$start $sofar $end");

  $sofar .= ' AND ' if $sofar;

  foreach $w (@where) {
    my $f = $w;
    $f =~ s/FIELD/$this/g;

    $res .= Sort_fields($start, $end, "$sofar$f", @rest);
  }

  return ($res);
}

# ===========================================================
# sub Sort_table: (rewritten by psmith)
#  return all entries in the given table,
#  in an ordered fashion
# ===========================================================
sub Sort_table {
    my ($tbl, @order) = @_;
    my @res=();

    # as long as there's no full where clause (Distrib 3.20)...
    # use having :-(
    # NOTE: this clause WILL NOT work on 3.21, because of the
    # order of 'ORDER BY' and 'HAVING'
    my $start = "SELECT *,UCASE(host) as ucase_host FROM $tbl ";
    $start   .= 'ORDER BY ' . join(',', @order) ." HAVING ";
    my $end   = ";\n";

    # server version 3.21 has a full where clause :-)
    if ($MySQLaccess::Host::SERVER >= '3.21') {
    # print "+++USING FULL WHERE CLAUSE+++\n";
       $start = "SELECT *,UCASE(host) as ucase_host FROM $tbl WHERE ";
       $end = ' ORDER BY ' . join(',', @order) . ";\n";
    }

    MySQLaccess::Debug::Print(1,"Sort_table():");
    MySQLaccess::Debug::Print(2,"Sorting table $tbl by `@order'");

    my $tmp;
    foreach $tmp (@order)
    {
      $tmp="UCASE(host)" if ($tmp eq "ucase_host");
    }
    my $query  = Sort_fields($start, $end, '', @order);
    $query    .= "select 'ok';\n";
    MySQLaccess::Debug::Print(2,"Query: $query");

    print MYSQL_Q "$query\n";

    my $delim = <MYSQL_A>; # read header
    MySQLaccess::Debug::Print(3," A> $delim");
    if ($delim ne "ok\n") {
       if ($delim =~ /^ERROR/) {
       push(@MySQLaccess::Grant::Error,'use_old_server');
       MySQLaccess::Report::Print_Error_Messages() ;
       exit 1;
       }
       while (($line=<MYSQL_A>) ne "ok\n")
       {
           MySQLaccess::Debug::Print(3," A> $line");
           push(@res,$line);
       }
    }
    my $skip = <MYSQL_A>; # skip result 'ok'

    # remove columnheaders from output