server-cfg

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

sub new
{
  my ($type,$host,$database)= @_;
  my $self= {};
  my %limits;
  bless $self;

  $self->{'cmp_name'}		= "Oracle";
  $self->{'data_source'}	= "DBI:Oracle:$database";
  $self->{'limits'}		= \%limits;
  $self->{'blob'}		= "long";
  $self->{'text'}		= "long";
  $self->{'double_quotes'}	= 1; # Can handle:  'Walker''s'
  $self->{'drop_attr'}		= "";
  $self->{'transactions'}	= 1; # Transactions enabled
  $self->{"vacuum"}		= 1;

  $limits{'max_conditions'}	= 9999; # (Actually not a limit)
  $limits{'max_columns'}	= 254;	# Max number of columns in table
  $limits{'max_tables'}		= 65000; # Should be big enough
  $limits{'max_text_size'}	= 2000; # Limit for blob test-connect
  $limits{'query_size'}		= 65525; # Max size with default buffers.
  $limits{'max_index'}		= 16; # Max number of keys
  $limits{'max_index_parts'}	= 16; # Max segments/key
  $limits{'max_column_name'} = 32; # max table and column name

  $limits{'truncate_table'}	= 1;
  $limits{'join_optimizer'}	= 1; # Can optimize FROM tables
  $limits{'load_data_infile'}	= 0; # Has load data infile
  $limits{'lock_tables'}	= 0; # Has lock tables
  $limits{'functions'}		= 1; # Has simple functions (+/-)
  $limits{'group_functions'}	= 1; # Have group functions
  $limits{'group_func_sql_min_str'}	= 1; # Can execute MIN() and MAX() on strings
  $limits{'group_distinct_functions'}= 1; # Have count(distinct)
  $limits{'select_without_from'}= 0;
  $limits{'multi_drop'}		= 0;
  $limits{'subqueries'}		= 1;
  $limits{'left_outer_join'}	= 0; # This may be fixed in the query module
  $limits{'table_wildcard'}	= 1; # Has SELECT table_name.*
  $limits{'having_with_alias'}  = 0; # Can use aliases in HAVING
  $limits{'having_with_group'}	= 1; # Can't use group functions in HAVING
  $limits{'like_with_column'}	= 1; # Can use column1 LIKE column2
  $limits{'order_by_position'}  = 1; # Can use 'ORDER BY 1'
  $limits{'group_by_position'}  = 0;
  $limits{'alter_table'}	= 1;
  $limits{'alter_add_multi_col'}= 0;
  $limits{'alter_table_dropcol'}= 0;

  $limits{'group_func_extra_std'}	= 0; # Have group function std().

  $limits{'func_odbc_mod'}	= 0; # Oracle has problem with mod()
  $limits{'func_extra_%'}	= 0; # Has % as alias for mod()
  $limits{'func_odbc_floor'}	= 1; # Has func_odbc_floor function
  $limits{'func_extra_if'}	= 0; # Have function if.
  $limits{'column_alias'}	= 1; # Alias for fields in select statement.
  $limits{'NEG'}		= 1; # Supports -id
  $limits{'func_extra_in_num'}	= 1; # Has function in
  $limits{'unique_index'}	= 1; # Unique index works or not
  $limits{'insert_select'}	= 1;
  $limits{'working_blobs'}	= 1; # If big varchar/blobs works
  $limits{'order_by_unused'}	= 1;
  $limits{'working_all_fields'} = 1;
  $limits{'multi_distinct'}     = 1; # allows select count(distinct a),count(distinct b).. 


  return $self;
}

#
# Get the version number of the database
#

sub version
{
  my ($self)=@_;
  my ($dbh,$sth,$version,@row);

  $dbh=$self->connect();
  $sth = $dbh->prepare("select VERSION from product_component_version WHERE PRODUCT like 'Oracle%'") or die $DBI::errstr;
  $version="Oracle 7.x";
  if ($sth->execute && (@row = $sth->fetchrow_array))
  {
    $version="Oracle $row[0]";
  }
  $sth->finish;
  $dbh->disconnect;
  $version .= "/ODBC" if ($self->{'data_source'} =~ /:ODBC:/);
  return $version;
}

sub connect
{
  my ($self)=@_;
  my ($dbh);
  $dbh=DBI->connect($self->{'data_source'}, $main::opt_user,
		    $main::opt_password,{ PrintError => 0}) ||
		      die "Got error: '$DBI::errstr' when connecting to " . $self->{'data_source'} ." with user: '$main::opt_user' password: '$main::opt_password'\n";
  return $dbh;
}

#
# Returns a list of statements to create a table
# The field types are in ANSI SQL format.
#
# If one uses $main::opt_fast then one is allowed to use
# non standard types to get better speed.
#

sub create
{
  my($self,$table_name,$fields,$index) = @_;
  my($query,@queries,$ind,@keys);

  $query="create table $table_name (";
  foreach $field (@$fields)
  {
    $field =~ s/ character\((\d+)\)/ char\($1\)/i;
    $field =~ s/ character varying\((\d+)\)/ varchar\($1\)/i;
    $field =~ s/ char varying\((\d+)\)/ varchar\($1\)/i;
    $field =~ s/ integer/ number\(38\)/i;
    $field =~ s/ int/ number\(38\)/i;
    $field =~ s/ tinyint/ number\(38\)/i;
    $field =~ s/ smallint/ number\(38\)/i;
    $field =~ s/ mediumint/ number\(38\)/i;
    $field =~ s/ tinynumber\((\d+)\)\((\d+)\)/ number\($1,$2\)/i;
    $field =~ s/ smallnumber\((\d+)\)\((\d+)\)/ number\($1,$2\)/i;
    $field =~ s/ mediumnumber\((\d+)\)\((\d+)\)/ number\($1,$2\)/i;
    $field =~ s/ number\((\d+)\)\((\d+)\)/ number\($1,$2\)/i;
    $field =~ s/ numeric\((\d+)\)\((\d+)\)/ number\($1,$2\)/i;
    $field =~ s/ decimal\((\d+)\)\((\d+)\)/ number\($1,$2\)/i;
    $field =~ s/ dec\((\d+)\)\((\d+)\)/ number\($1,$2\)/i;
    $field =~ s/ float/ number/;
    $field =~ s/ real/ number/;
    $field =~ s/ double precision/ number/;
    $field =~ s/ double/ number/;
    $field =~ s/ blob/ long/;
    $query.= $field . ',';
  }

  foreach $ind (@$index)
  {
    my @index;
    if ( $ind =~ /\bKEY\b/i ){
      push(@keys,"ALTER TABLE $table_name ADD $ind");
    }else{
      my @fields = split(' ',$index);
      my $query="CREATE INDEX $fields[1] ON $table_name $fields[2]";
      push(@index,$query);
    }
  }
  substr($query,-1)=")";		# Remove last ',';
  push(@queries,$query,@keys,@index);
#print "query:$query\n";

  return @queries;
}

sub insert_file {
  my($self,$dbname, $file) = @_;
  print "insert an ascii file isn't supported by Oracle (?)\n";
  return 0;
}

#
# Do any conversions to the ANSI SQL query so that the database can handle it
#

sub query {
  my($self,$sql) = @_;
  return $sql;
}

sub fix_for_insert
{
  my ($self,$cmd) = @_;
  $cmd =~ s/\'\'/\' \'/g;
  return $cmd;
}


sub drop_index
{
  my ($self,$table,$index) = @_;
  return "DROP INDEX $index";
}

#
# Abort if the server has crashed
# return: 0 if ok
#	  1 question should be retried
#

sub abort_if_fatal_error
{
  return 0;
}

sub small_rollback_segment
{
  return 1;
}

sub reconnect_on_errors
{
  return 0;
}

#
# optimize the tables ....
#
sub vacuum
{
  my ($self,$full_vacuum,$dbh_ref)=@_;
  my ($loop_time,$end_time,$sth,$dbh);

  if (defined($full_vacuum))
  {
    $$dbh_ref->disconnect;  $$dbh_ref= $self->connect();
  }
  $dbh=$$dbh_ref;
  $loop_time=new Benchmark;
  # first analyze all tables
  $sth = $dbh->prepare("select table_name from user_tables") || die "Got error: $DBI::errstr";
  $sth->execute || die "Got error: $DBI::errstr when select user_tables";
  while (my @r = $sth->fetchrow_array)
  {
    $dbh->do("analyze table $r[0] compute statistics") || die "Got error: $DBI::errstr when executing 'analyze table'\n";
  }
  # now analyze all indexes ...
  $sth = $dbh->prepare("select index_name from user_indexes") || die "Got error: $DBI::errstr";
  $sth->execute || die "Got error: $DBI::errstr when select user_indexes";
  while (my @r1 = $sth->fetchrow_array)
  {
    $dbh->do("analyze index $r1[0] compute statistics") || die "Got error: $DBI::errstr when executing 'analyze index $r1[0]'\n";
  }
  $end_time=new Benchmark;
  print "Time for book-keeping (1): " .
  Benchmark::timestr(Benchmark::timediff($end_time, $loop_time),"all") . "\n\n";
  $dbh->disconnect;  $$dbh_ref= $self->connect();
}


#############################################################################
#	                 Definitions for Informix
#############################################################################

package db_Informix;

sub new
{
  my ($type,$host,$database)= @_;
  my $self= {};
  my %limits;
  bless $self;

  $self->{'cmp_name'}		= "Informix";
  $self->{'data_source'}	= "DBI:Informix:$database";
  $self->{'limits'}		= \%limits;
  $self->{'blob'}		= "byte in table";
  $self->{'text'}		= "byte in table";
  $self->{'double_quotes'}	= 0; # Can handle:  'Walker''s'
  $self->{'drop_attr'}		= "";
  $self->{'transactions'}	= 1; # Transactions enabled
  $self->{'host'}		= $host;

  $limits{'NEG'}		= 1; # Supports -id
  $limits{'alter_table'}	= 1;
  $limits{'alter_add_multi_col'}= 0;
  $limits{'alter_table_dropcol'}= 1;
  $limits{'column_alias'}	= 1; # Alias for fields in select statement.
  $limits{'func_extra_%'}	= 0; # Has % as alias for mod()
  $limits{'func_extra_if'}	= 0; # Have function if.
  $limits{'func_extra_in_num'}= 0; # Has function in
  $limits{'func_odbc_floor'}	= 0; # Has func_odbc_floor function
  $limits{'func_odbc_mod'}	= 1; # Have function mod.
  $limits{'functions'}		= 1; # Has simple functions (+/-)
  $limits{'group_by_position'}  = 1; # Can use 'GROUP BY 1'
  $limits{'group_by_alias'}  = 0; # Can use 'select a as ab from x GROUP BY ab'
  $limits{'group_func_extra_std'} = 0; # Have group function std().
  $limits{'group_functions'}	= 1; # Have group functions
  $limits{'group_func_sql_min_str'}	= 1; # Can execute MIN() and MAX() on strings
  $limits{'group_distinct_functions'}= 1; # Have count(distinct)
  $limits{'having_with_alias'}  = 0; # Can use aliases in HAVING
  $limits{'having_with_group'}= 1; # Can't use group functions in HAVING
  $limits{'join_optimizer'}	= 1; # Can optimize FROM tables (always 1 only for msql)
  $limits{'left_outer_join'}	= 0; # Supports left outer joins (ANSI)
  $limits{'like_with_column'}	= 1; # Can use column1 LIKE column2
  $limits{'load_data_infile'}	= 0; # Has load data infile
  $limits{'lock_tables'}	= 1; # Has lock tables
  $limits{'max_conditions'}	= 1214; # (Actually not a limit)
  $limits{'max_column_name'}	= 18; # max table and column name
  $limits{'max_columns'}	= 994;	# Max number of columns in table
  $limits{'max_tables'}		= 65000;	# Should be big enough
  $limits{'max_index'}		= 64; # Max number of keys
  $limits{'max_index_parts'}	= 15; # Max segments/key
  $limits{'max_text_size'}	= 65535;  # Max size with default buffers. ??
  $limits{'multi_drop'}		= 0; # Drop table can take many tables
  $limits{'order_by_position'}  = 1; # Can use 'ORDER BY 1'
  $limits{'query_size'}		= 32766; # Max size with default buffers.
  $limits{'select_without_from'}= 0; # Can do 'select 1';
  $limits{'subqueries'}		= 1; # Doesn't support sub-queries.
  $limits{'table_wildcard'}	= 1; # Has SELECT table_name.*
  $limits{'unique_index'}	= 1; # Unique index works or not
  $limits{'insert_select'}	= 1;
  $limits{'working_blobs'}	= 1; # If big varchar/blobs works
  $limits{'order_by_unused'}	= 1;
  $limits{'working_all_fields'} = 1;
  $limits{'multi_distinct'}     = 1; # allows select count(distinct a),count(distinct b).. 

  return $self;
}

#
# Get the version number of the database
#

sub version
{
  my ($self)=@_;
  my ($dbh,$sth,$version,@row);

  $ENV{'INFORMIXSERVER'} = $self->{'host'};
  $dbh=$self->connect();
  $sth = $dbh->prepare("SELECT owner FROM systables WHERE tabname = ' VERSION'")
						      or die $DBI::errstr;
  $version='Informix unknown';
  if ($sth->execute && (@row = $sth->fetchrow_array))
  {
    $version="Informix $row[0]";
  }
  $sth->finish;
  $dbh->disconnect;
  $version .= "/ODBC" if ($self->{'data_source'} =~ /:ODBC:/);
  return $version;
}

sub connect
{
  my ($self)=@_;
  my ($dbh);
  $dbh=DBI->connect($self->{'data_source'}, $main::opt_user,
		    $main::opt_password,{ PrintError => 0}) ||
		      die "Got error: '$DBI::errstr' when connecting to " . $self->{'data_source'} ." with user: '$main::opt_user' password: '$main::opt_password'\n";
  return $dbh;
}


#
# Create table
#

sub create
{
  my($self,$table_name,$fields,$index) = @_;
  my($query,@queries,$name,$nr);

  $query="create table $table_name (";
  foreach $field (@$fields)
  {
#    $field =~ s/\btransport_description\b/transport_desc/;
				# to overcome limit 18 chars
    $field =~ s/tinyint/smallint/i;
    $field =~ s/tinyint\(\d+\)/smallint/i;
    $field =~ s/mediumint/integer/i;
    $field =~ s/mediumint\(\d+\)/integer/i;
    $field =~ s/smallint\(\d+\)/smallint/i;
    $field =~ s/integer\(\d+\)/integer/i;
    $field =~ s/int\(\d+\)/integer/i;
#    $field =~ s/\b(?:small)?int(?:eger)?\((\d+)\)/decimal($1)/i;
#    $field =~ s/float(\(\d*,\d*\)){0,1}/real/i;
    $field =~ s/(float|double)(\(.*?\))?/float/i;

    if ($field =~ / blob/i)
    {
      $name=$self->{'blob'};
      $field =~ s/ blob/ $name/;
    }
    $query.= $field . ',';
  }
  substr($query,-1)=")";		# Remove last ',';
  push(@queries,$query);
  $nr=0;

  foreach $index (@$index)
  {
    # Primary key is unique index in Informix
    $index =~ s/primary key/unique index primary/i;
    if ($index =~ /^unique\s*\(([^\(]*)\)$/i)
    {
      $nr++;
      push(@queries,"create unique index ${table_name}_$nr on $table_name ($1)");
    }
    else
    {
      if (!($index =~ /^(.*index)\s+(\w*)\s+(\(.*\))$/i))
      {
	die "Can't parse index information in '$index'\n";
      }
      ### push(@queries,"create $1 ${table_name}_$2 on $table_name $3");
      $nr++;
      push(@queries,"create $1 ${table_name}_$nr on $table_name $3");
    }
  }
  return @queries;
}
#
# Some test needed this
#

sub query {
  my($self,$sql) = @_;
  return $sql;
}


sub fix_for_insert
{
  my ($self,$cmd) = @_;
  $cmd =~ s/\\\'//g;
  return $cmd;
}



sub drop_index
{
  my ($self,$table,$index) = @_;
  return "DROP INDEX $index";
}

#
# Abort if the server has crashed
# return: 0 if ok
#	  1 question should be retried
#

sub abort_if_fatal_error
{
  return 0;
}

sub small_rollback_segment
{
  return 0;
}

sub reconnect_on_errors
{
  return 0;