server-cfg

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

	$tmp =~ s/\s+/ /g;	# Remove unnecessary spaces
	$tmp .= "/ODBC" if ($self->{'data_source'} =~ /:ODBC:/);

	return $tmp;
      }
    }
  }
  return "mSQL 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;
}

#
# Can't handle many field types, so we map everything to int and real.
#

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

  $query="create table $table_name (";
  foreach $field (@$fields)
  {
    $field =~ s/varchar/char/i;		# mSQL doesn't have VARCHAR()
    # mSQL can't handle more than the real basic int types
    $field =~ s/tinyint|smallint|mediumint|integer/int/i;
    # mSQL can't handle different visual lengths
    $field =~ s/int\(\d*\)/int/i;
    # mSQL doesn't have float, change it to real
    $field =~ s/float(\(\d*,\d*\)){0,1}/real/i;
    $field =~ s/double(\(\d*,\d*\)){0,1}/real/i;
    # mSQL doesn't have blob, it has text instead
    if ($field =~ / blob/i)
    {
      $name=$self->{'blob'};
      $field =~ s/ blob/ $name/;
    }
    $query.= $field . ',';
  }
  substr($query,-1)=")";		# Remove last ',';
  push(@queries,$query);
  $nr=0;

  # Prepend table_name to index name because the the name may clash with
  # a field name. (Should be diffent name space, but this is mSQL...)

  foreach $index (@$index)
  {
    # Primary key is unique index in mSQL
    $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");
    }
  }
  return @queries;
}


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


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

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

sub abort_if_fatal_error
{
  return 0;
}

sub small_rollback_segment
{
  return 0;
}

sub reconnect_on_errors
{
  return 0;
}

sub fix_for_insert
{
  my ($self,$cmd) = @_;
  return $cmd;
}

#############################################################################
#		     Definitions for PostgreSQL				    #
#############################################################################

package db_Pg;

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

  $self->{'cmp_name'}		= "pg";
  $self->{'data_source'}	= "DBI:Pg:dbname=$database";
  $self->{'limits'}		= \%limits;
  $self->{'blob'}		= "text";
  $self->{'text'}		= "text";
  $self->{'double_quotes'}	= 1;
  $self->{'drop_attr'}		= "";
  $self->{'transactions'}	= 1; # Transactions enabled
  $self->{"vacuum"}		= 1;
  $limits{'join_optimizer'}	= 1;		# Can optimize FROM tables
  $limits{'load_data_infile'}	= 0;

  $limits{'NEG'}		= 1;
  $limits{'alter_add_multi_col'}= 0;		# alter_add_multi_col ?
  $limits{'alter_table'}	= 1;
  $limits{'alter_table_dropcol'}= 0;
  $limits{'column_alias'}	= 1;
  $limits{'func_extra_%'}	= 1;
  $limits{'func_extra_if'}	= 0;
  $limits{'func_extra_in_num'}	= 1;
  $limits{'func_odbc_floor'}	= 1;
  $limits{'func_odbc_mod'}	= 1;		# Has %
  $limits{'functions'}		= 1;
  $limits{'group_by_position'}  = 1;
  $limits{'group_distinct_functions'}= 1; # Have count(distinct)
  $limits{'group_func_extra_std'} = 0;
  $limits{'group_func_sql_min_str'}= 1; # Can execute MIN() and MAX() on strings
  $limits{'group_functions'}	= 1;
  $limits{'having_with_alias'}  = 0;
  $limits{'having_with_group'}	= 1;
  $limits{'insert_select'}	= 1;
  $limits{'left_outer_join'}	= 1;
  $limits{'like_with_column'}	= 1;
  $limits{'lock_tables'}	= 0;		# in ATIS gives this a problem
  $limits{'max_column_name'} 	= 128;
  $limits{'max_columns'}	= 1000;		# 500 crashes pg 6.3
  $limits{'max_conditions'}	= 9999;		# This makes Pg real slow
  $limits{'max_index'}		= 64;		# Big enough
  $limits{'max_index_parts'}	= 16;
  $limits{'max_tables'}		= 5000;		# 10000 crashes pg 7.0.2
  $limits{'max_text_size'}	= 65000;	# Good enough for test
  $limits{'multi_drop'}		= 1;
  $limits{'order_by_position'}  = 1;
  $limits{'order_by_unused'}	= 1;
  $limits{'query_size'}		= 16777216;
  $limits{'select_without_from'}= 1;
  $limits{'subqueries'}		= 1;
  $limits{'table_wildcard'}	= 1;
  $limits{'truncate_table'}	= 1;
  $limits{'unique_index'}	= 1; # Unique index works or not
  $limits{'working_all_fields'} = 1;
  $limits{'working_blobs'}	= 1; # If big varchar/blobs works
  $limits{'multi_distinct'}     = 1; # allows select count(distinct a),count(distinct b).. 

  return $self;
}

# couldn't find the option to get the version number

sub version
{
  my ($version,$dir);
  $version = "PostgreSQL version ???";
  foreach $dir ($ENV{'PGDATA'},"/usr/local/pgsql/data", "/usr/local/pg/data")
  {
    if ($dir && -e "$dir/PG_VERSION")
    {
      $version= `cat $dir/PG_VERSION`;
      if ($? == 0)
      {
	chomp($version);
	$version .= "/ODBC" if ($self->{'data_source'} =~ /:ODBC:/);
	return "PostgreSQL $version";
      }
    }
  }
  $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;
}


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

  $query="create table $table_name (";
  foreach $field (@$fields)
  {
    if ($main::opt_fast)
    {
      # Allow use of char2, char4, char8 or char16
      $field =~ s/char(2|4|8|16)/char$1/;
    }
    # Pg can't handle more than the real basic int types
    $field =~ s/tinyint|smallint|mediumint|integer/int/;
    # Pg can't handle different visual lengths
    $field =~ s/int\(\d*\)/int/;
    $field =~ s/float\(\d*,\d*\)/float/;
    $field =~ s/ double/ float/;
#    $field =~ s/ decimal/ float/i;
#    $field =~ s/ big_decimal/ float/i;
#    $field =~ s/ date/ int/i;
    # Pg doesn't have blob, it has text instead
    $field =~ s/ blob/ text/;
    $query.= $field . ',';
  }
  substr($query,-1)=")";		# Remove last ',';
  push(@queries,$query);
  foreach $index (@$index)
  {
    $index =~ s/primary key/unique index primary_key/i;
    if ($index =~ /^unique.*\(([^\(]*)\)$/i)
    {
      # original: $indfield="using btree (" .$1.")";
      # using btree doesn磘 seem to work with Postgres anymore; it creates
      # the table and adds the index, but it isn磘 unique
      $indfield=" (" .$1.")";	
      $in="unique index";
      $table="index_$nr"; $nr++;
    }
    elsif ($index =~ /^(.*index)\s+(\w*)\s+(\(.*\))$/i)
    {
      # original: $indfield="using btree (" .$1.")";
      $indfield=" " .$3;
      $in="index";
      $table="index_$nr"; $nr++;
    }
    else
    {
      die "Can't parse index information in '$index'\n";
    }
    push(@queries,"create $in ${table_name}_$table on $table_name $indfield");
  }
  $queries[0]=$query;
  return @queries;
}

sub insert_file {
  my ($self,$dbname, $file, $dbh) = @_;
  my ($command, $sth);

# Syntax:
# copy [binary] <class_name> [with oids]
#      {to|from} {<filename>|stdin|stdout} [using delimiters <delim>]
  print "The ascii files aren't correct for postgres ....!!!\n";
  $command = "copy $dbname from '$file' using delimiters ','";
  print "$command\n";
  $sth = $dbh->do($command) or die $DBI::errstr;
  return $sth;
}

#
# As postgreSQL wants A % B instead of standard mod(A,B) we have to map
# This will not handle all cases, but as the benchmarks doesn't use functions
# inside MOD() the following should work
#
# PostgreSQL cant handle count(*) or even count(1), but it can handle
# count(1+1) sometimes. ==> this is solved in PostgreSQL 6.3
#
# PostgreSQL 6.5 is supporting MOD.

sub query {
  my($self,$sql) = @_;
  my(@select,$change);
# if you use PostgreSQL 6.x and x is lower as 5 then uncomment the line below.
#  $sql =~ s/mod\(([^,]*),([^\)]*)\)/\($1 % $2\)/gi;
#
# if you use PostgreSQL 6.1.x uncomment the lines below
#  if ($sql =~ /select\s+count\(\*\)\s+from/i) {
#  }
#  elsif ($sql =~ /count\(\*\)/i)
#  {
#    if ($sql =~ /select\s+(.*)\s+from/i)
#    {
#      @select = split(/,/,$1);
#      if ($select[0] =~ /(.*)\s+as\s+\w+$/i)
#      {
# 	$change = $1;
#      }
#      else
#      {
#	$change = $select[0];
#      }
#    }
#    if (($change =~ /count/i) || ($change eq "")) {
#      $change = "1+1";
#    }
#    $sql =~ s/count\(\*\)/count($change)/gi;
#  }
# till here.
  return $sql;
}

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

sub abort_if_fatal_error
{
  return 1 if ($DBI::errstr =~ /sent to backend, but backend closed/i);
  return 0;
}

sub small_rollback_segment
{
  return 0;
}

sub reconnect_on_errors
{
  return 0;
}

sub fix_for_insert
{
  my ($self,$cmd) = @_;
  return $cmd;
}

sub vacuum
{
  my ($self,$full_vacuum,$dbh_ref,@tables)=@_;
  my ($loop_time,$end_time,$dbh,$table);
  if (defined($full_vacuum))
  {
    $$dbh_ref->disconnect;  $$dbh_ref= $self->connect();
  }
  $dbh=$$dbh_ref;
  $loop_time=new Benchmark;
  if ($#tables >= 0)
  {
    foreach $table (@tables)
    {
      $dbh->do("vacuum analyze $table") || die "Got error: $DBI::errstr when executing 'vacuum analyze $table'\n";
      $dbh->do("vacuum $table") || die "Got error: $DBI::errstr when executing 'vacuum'\n";
    }
  }
  else
  {
#    $dbh->do("vacuum pg_attributes") || die "Got error: $DBI::errstr when executing 'vacuum'\n";
#    $dbh->do("vacuum pg_index") || die "Got error: $DBI::errstr when executing 'vacuum'\n";
    $dbh->do("vacuum analyze") || die "Got error: $DBI::errstr when executing 'vacuum analyze'\n";
    $dbh->do("vacuum") || die "Got error: $DBI::errstr when executing 'vacuum'\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 Solid
#############################################################################

package db_Solid;

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

  $self->{'cmp_name'}		= "solid";
  $self->{'data_source'}	= "DBI:Solid:";
  $self->{'limits'}		= \%limits;
  $self->{'blob'}		= "long varchar";
  $self->{'text'}		= "long varchar";
  $self->{'double_quotes'}	= 1;
  $self->{'drop_attr'}		= "";
  $self->{'transactions'}	= 1; # Transactions enabled

  $limits{'max_conditions'}	= 9999;		# Probably big enough
  $limits{'max_columns'}	= 2000;		# From crash-me
  $limits{'max_tables'}		= 65000;	# Should be big enough
  $limits{'max_text_size'}	= 65492;	# According to tests
  $limits{'query_size'}		= 65535;	# Probably a limit
  $limits{'max_index'}		= 64;		# Probably big enough
  $limits{'max_index_parts'}	= 64;
  $limits{'max_column_name'} = 80;

  $limits{'join_optimizer'}	= 1;
  $limits{'load_data_infile'}	= 0;
  $limits{'lock_tables'}	= 0;
  $limits{'functions'}		= 1;
  $limits{'group_functions'}	= 1;
  $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;		# Can do 'select 1' ?;
  $limits{'multi_drop'}		= 0;
  $limits{'subqueries'}		= 1;
  $limits{'left_outer_join'}	= 1;
  $limits{'table_wildcard'}	= 1;
  $limits{'having_with_alias'}  = 0;
  $limits{'having_with_group'}	= 1;
  $limits{'like_with_column'}	= 1;
  $limits{'order_by_position'}  = 0;		# 2.30.0018 can this
  $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'}	= 1;