diff options
Diffstat (limited to 'sql-bench/server-cfg.sh')
| -rw-r--r-- | sql-bench/server-cfg.sh | 221 |
1 files changed, 192 insertions, 29 deletions
diff --git a/sql-bench/server-cfg.sh b/sql-bench/server-cfg.sh index 886c428e3b9..a4e74fcb4f9 100644 --- a/sql-bench/server-cfg.sh +++ b/sql-bench/server-cfg.sh @@ -33,10 +33,10 @@ sub get_server { - my ($name,$host,$database,$odbc,$machine)=@_; + my ($name,$host,$database,$odbc,$machine,$socket)=@_; my ($server); if ($name =~ /mysql/i) - { $server=new db_MySQL($host, $database, $machine); } + { $server=new db_MySQL($host, $database, $machine, $socket); } elsif ($name =~ /pg/i) { $server= new db_Pg($host,$database); } elsif ($name =~ /msql/i) @@ -73,7 +73,7 @@ sub get_server { $server= new db_interbase($host,$database); } else { - die "Unknown sql server name used: $name\nUse one of: Access, Adabas, AdabasD, Empress, FrontBase, Oracle, Informix, DB2, mSQL, Mimer, MS-SQL, MySQL, Pg, Solid or Sybase.\nIf the connection is done trough ODBC the name must end with _ODBC\n"; + die "Unknown sql server name used: $name\nUse one of: Access, Adabas, AdabasD, Empress, FrontBase, Oracle, Informix, InterBase, DB2, mSQL, Mimer, MS-SQL, MySQL, Pg, Solid or Sybase.\nIf the connection is done trough ODBC the name must end with _ODBC\n"; } if ($name =~ /_ODBC$/i || defined($odbc) && $odbc) { @@ -106,13 +106,14 @@ package db_MySQL; sub new { - my ($type,$host,$database,$machine)= @_; + my ($type,$host,$database,$machine,$socket)= @_; my $self= {}; my %limits; bless $self; $self->{'cmp_name'} = "mysql"; - $self->{'data_source'} = "DBI:mysql:$database:$host"; + $self->{'data_source'} = "DBI:mysql:database=$database;host=$host"; + $self->{'data_source'} .= ";mysql_socket=$socket" if($socket); $self->{'limits'} = \%limits; $self->{'smds'} = \%smds; $self->{'blob'} = "blob"; @@ -120,6 +121,7 @@ sub new $self->{'double_quotes'} = 1; # Can handle: 'Walker''s' $self->{'vacuum'} = 1; # When using with --fast $self->{'drop_attr'} = ""; + $self->{'transactions'} = 0; # Transactions disabled by default $limits{'NEG'} = 1; # Supports -id $limits{'alter_add_multi_col'}= 1; #Have ALTER TABLE t add a int,add b int; @@ -144,7 +146,8 @@ sub new $limits{'join_optimizer'} = 1; # Can optimize FROM tables $limits{'left_outer_join'} = 1; # Supports left outer joins $limits{'like_with_column'} = 1; # Can use column1 LIKE column2 - $limits{'limit'} = 1; # supports the limit attribute + $limits{'limit'} = 1; # supports the limit attribute + $limits{'truncate_table'} = 1; $limits{'load_data_infile'} = 1; # Has load data infile $limits{'lock_tables'} = 1; # Has lock tables $limits{'max_column_name'} = 64; # max table and column name @@ -194,12 +197,14 @@ sub new $main::opt_create_options =~ /type=innodb/i) { $limits{'max_text_size'} = 8000; # Limit in Innobase + $self->{'transactions'} = 1; # Transactions enabled } if (defined($main::opt_create_options) && $main::opt_create_options =~ /type=gemini/i) { $limits{'working_blobs'} = 0; # Blobs not implemented yet $limits{'max_tables'} = 500; + $self->{'transactions'} = 1; # Transactions enabled } return $self; @@ -330,6 +335,12 @@ sub reconnect_on_errors return 0; } +sub fix_for_insert +{ + my ($self,$cmd) = @_; + return $cmd; +} + # # Optimize tables for better performance # @@ -349,7 +360,6 @@ sub vacuum } } - ############################################################################# # Definitions for mSQL ############################################################################# @@ -368,6 +378,7 @@ sub new $self->{'limits'} = \%limits; $self->{'double_quotes'} = 0; $self->{'drop_attr'} = ""; + $self->{'transactions'} = 0; # No transactions $self->{'blob'} = "text(" . $limits{'max_text_size'} .")"; $self->{'text'} = "text(" . $limits{'max_text_size'} .")"; @@ -542,6 +553,12 @@ sub reconnect_on_errors return 0; } +sub fix_for_insert +{ + my ($self,$cmd) = @_; + return $cmd; +} + ############################################################################# # Definitions for PostgreSQL # ############################################################################# @@ -563,6 +580,7 @@ sub new $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; @@ -603,6 +621,7 @@ sub new $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 @@ -798,6 +817,12 @@ sub reconnect_on_errors return 0; } +sub fix_for_insert +{ + my ($self,$cmd) = @_; + return $cmd; +} + sub vacuum { my ($self,$full_vacuum,$dbh_ref,@tables)=@_; @@ -851,6 +876,7 @@ sub new $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 @@ -1063,6 +1089,12 @@ sub small_rollback_segment return 0; } +sub fix_for_insert +{ + my ($self,$cmd) = @_; + return $cmd; +} + sub reconnect_on_errors { return 0; @@ -1092,6 +1124,7 @@ sub new $self->{'text'} = "text"; $self->{'double_quotes'} = 1; # Can handle: 'Walker''s' $self->{'drop_attr'} = ""; + $self->{'transactions'} = 1; # Transactions enabled $limits{'max_conditions'} = 1258; $limits{'max_columns'} = 226; # server is disconnecting???? @@ -1325,6 +1358,14 @@ sub query { return $sql; } +sub fix_for_insert +{ + my ($self,$cmd) = @_; + $cmd =~ s/\'\'/\' \'/g; + return $cmd; +} + + sub drop_index { my ($self,$table,$index) = @_; @@ -1374,6 +1415,7 @@ sub new $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) @@ -1385,6 +1427,7 @@ sub new $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 @@ -1546,6 +1589,14 @@ sub query { return $sql; } +sub fix_for_insert +{ + my ($self,$cmd) = @_; + $cmd =~ s/\'\'/\' \'/g; + return $cmd; +} + + sub drop_index { my ($self,$table,$index) = @_; @@ -1629,6 +1680,7 @@ sub new $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 @@ -1778,6 +1830,16 @@ sub query { return $sql; } + +sub fix_for_insert +{ + my ($self,$cmd) = @_; + $cmd =~ s/\\\'//g; + return $cmd; +} + + + sub drop_index { my ($self,$table,$index) = @_; @@ -1805,6 +1867,7 @@ sub reconnect_on_errors return 0; } + ############################################################################# # Configuration for Access ############################################################################# @@ -1830,6 +1893,7 @@ sub new $self->{'text'} = "blob"; # text ? $self->{'double_quotes'} = 1; # Can handle: 'Walker''s' $self->{'drop_attr'} = ""; + $self->{'transactions'} = 1; # Transactions enabled $limits{'max_conditions'} = 97; # We get 'Query is too complex' $limits{'max_columns'} = 255; # Max number of columns in table @@ -1981,6 +2045,12 @@ sub reconnect_on_errors return 1; } +sub fix_for_insert +{ + my ($self,$cmd) = @_; + return $cmd; +} + ############################################################################# # Configuration for Microsoft SQL server ############################################################################# @@ -2006,6 +2076,7 @@ sub new $self->{'text'} = "text"; $self->{'double_quotes'} = 1; # Can handle: 'Walker''s' $self->{'drop_attr'} = ""; + $self->{'transactions'} = 1; # Transactions enabled $limits{'max_conditions'} = 1030; # We get 'Query is too complex' $limits{'max_columns'} = 250; # Max number of columns in table @@ -2168,10 +2239,15 @@ sub reconnect_on_errors return 0; } +sub fix_for_insert +{ + my ($self,$cmd) = @_; + return $cmd; +} + ############################################################################# # Configuration for Sybase ############################################################################# - package db_sybase; sub new @@ -2182,10 +2258,10 @@ sub new bless $self; $self->{'cmp_name'} = "sybase"; - $self->{'data_source'} = "DBI:ODBC:$database"; + $self->{'data_source'} = "DBI:Sybase:database=$database"; if (defined($host) && $host ne "") { - $self->{'data_source'} .= ":$host"; + $self->{'data_source'} .= ";hostname=$host"; } $self->{'limits'} = \%limits; $self->{'smds'} = \%smds; @@ -2193,6 +2269,7 @@ sub new $self->{'text'} = "text"; $self->{'double_quotes'} = 1; # Can handle: 'Walker''s' $self->{'drop_attr'} = ""; + $self->{'transactions'} = 1; # Transactions enabled $self->{"vacuum"} = 1; $limits{'max_conditions'} = 1030; # We get 'Query is too complex' @@ -2249,7 +2326,19 @@ sub new sub version { my ($self)=@_; - return "Sybase enterprise 11.5 NT"; #DBI/ODBC can't return the server version + my ($dbh,$sth,$version,@row); + + $dbh=$self->connect(); + $sth = $dbh->prepare('SELECT @@version') or die $DBI::errstr; + $version="Sybase (unknown)"; + if ($sth->execute && (@row = $sth->fetchrow_array)) + { + $version=$row[0]; + } + $sth->finish; + $dbh->disconnect; + return $version; + } sub connect @@ -2257,7 +2346,7 @@ sub connect my ($self)=@_; my ($dbh); $dbh=DBI->connect($self->{'data_source'}, $main::opt_user, - $main::opt_password,{ PrintError => 0}) || + $main::opt_password,{ PrintError => 0 , AutoCommit => 1}) || die "Got error: '$DBI::errstr' when connecting to " . $self->{'data_source'} ." with user: '$main::opt_user' password: '$main::opt_password'\n"; return $dbh; } @@ -2342,9 +2431,17 @@ sub reconnect_on_errors return 0; } +sub fix_for_insert +{ + my ($self,$cmd) = @_; + return $cmd; +} + # # optimize the tables .... -# +# WARNING (from walrus)! This sub will work only from DBD:sybase +# driver. Because if we use ODBC we don't know actual database name +# (but DSN name only) sub vacuum { my ($self,$full_vacuum,$dbh_ref)=@_; @@ -2356,7 +2453,25 @@ sub vacuum } $dbh=$$dbh_ref; $loop_time=new Benchmark; - $dbh->do("analyze table ?? compute statistics") || die "Got error: $DBI::errstr when executing 'vacuum'\n"; + my (@tables,$sth,$current_table,$current_base); + $dbh->do("dump tran $database with truncate_only"); + $sth=$dbh->prepare("sp_tables" ) or die "prepere"; + $sth->execute() or die "execute"; + while (@row = $sth->fetchrow_array()) { + $current_table = $row[2]; + $current_base = $row[0]; + next if ($current_table =~ /^sys/); + push(@tables,$current_table) if ($database == $current_base); + } + + $sth->finish(); + + foreach $table (@tables) { +# print "$table: \n"; + $dbh->do("update statistics $table") or print "Oops!"; + } + +# $dbh->do("analyze table ?? compute statistics") || 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"; @@ -2364,6 +2479,8 @@ sub vacuum } + + ############################################################################# # Definitions for Adabas ############################################################################# @@ -2385,6 +2502,7 @@ sub new $self->{'text'} = "long"; $self->{'double_quotes'} = 1; # Can handle: 'Walker''s' $self->{'drop_attr'} = ""; + $self->{'transactions'} = 1; # Transactions enabled $limits{'max_conditions'} = 50; # (Actually not a limit) $limits{'max_columns'} = 254; # Max number of columns in table @@ -2574,6 +2692,12 @@ sub reconnect_on_errors return 0; } +sub fix_for_insert +{ + my ($self,$cmd) = @_; + return $cmd; +} + ############################################################################# # Configuration for IBM DB2 ############################################################################# @@ -2599,6 +2723,7 @@ sub new $self->{'text'} = "varchar(255)"; $self->{'double_quotes'} = 1; # Can handle: 'Walker''s' $self->{'drop_attr'} = ""; + $self->{'transactions'} = 1; # Transactions enabled $limits{'max_conditions'} = 418; # We get 'Query is too complex' $limits{'max_columns'} = 500; # Max number of columns in table @@ -2747,6 +2872,12 @@ sub reconnect_on_errors return 0; } +sub fix_for_insert +{ + my ($self,$cmd) = @_; + return $cmd; +} + ############################################################################# # Configuration for MIMER ############################################################################# @@ -2768,6 +2899,7 @@ sub new $self->{'text'} = "character varying(15000)"; $self->{'double_quotes'} = 1; # Can handle: 'Walker''s' $self->{'drop_attr'} = ""; + $self->{'transactions'} = 1; # Transactions enabled $self->{'char_null'} = "cast(NULL as char(1))"; $self->{'numeric_null'} = "cast(NULL as int)"; @@ -2836,7 +2968,6 @@ sub new $smds{'q15'} = 'd'; $smds{'q16'} = 'a'; $smds{'q17'} = 'c'; - return $self; } @@ -2885,23 +3016,35 @@ sub connect sub create { my($self,$table_name,$fields,$index,$options) = @_; - my($query,@queries); + my($query,@queries,@indexes); $query="create table $table_name ("; foreach $field (@$fields) { $field =~ s/ decimal/ double(10,2)/i; $field =~ s/ big_decimal/ double(10,2)/i; - $field =~ s/ date/ int/i; # Because of tcp ? + $field =~ s/ tinyint\(.*\)/ smallint/i; + $field =~ s/ smallint\(.*\)/ smallint/i; + $field =~ s/ mediumint/ integer/i; + $field =~ s/ float\(.*\)/ float/i; +# $field =~ s/ date/ int/i; # Because of tcp ? $query.= $field . ','; } foreach $index (@$index) { - $query.= $index . ','; + if ( $index =~ /\bINDEX\b/i ) + { + my @fields = split(' ',$index); + my $query="CREATE INDEX $fields[1] ON $table_name $fields[2]"; + push(@indexes,$query); + + } else { + $query.= $index . ','; + } } substr($query,-1)=")"; # Remove last ','; $query.=" $options" if (defined($options)); - push(@queries,$query); + push(@queries,$query,@indexes); return @queries; } @@ -2947,6 +3090,12 @@ sub reconnect_on_errors return 0; } +sub fix_for_insert +{ + my ($self,$cmd) = @_; + return $cmd; +} + ############################################################################# # Configuration for InterBase ############################################################################# @@ -2961,13 +3110,14 @@ sub new bless $self; $self->{'cmp_name'} = "interbase"; - $self->{'data_source'} = "DBI:InterBase:database=$database"; + $self->{'data_source'} = "DBI:InterBase:database=$database:ib_dialect=3"; $self->{'limits'} = \%limits; $self->{'smds'} = \%smds; $self->{'blob'} = "blob"; $self->{'text'} = ""; $self->{'double_quotes'} = 1; # Can handle: 'Walker''s' $self->{'drop_attr'} = ""; + $self->{'transactions'} = 1; # Transactions enabled $self->{'char_null'} = ""; $self->{'numeric_null'} = ""; @@ -3159,6 +3309,12 @@ sub reconnect_on_errors return 1; } +sub fix_for_insert +{ + my ($self,$cmd) = @_; + return $cmd; +} + ############################################################################# # Configuration for FrontBase ############################################################################# @@ -3180,6 +3336,7 @@ sub new $self->{'text'} = "varchar(8000000)"; $self->{'double_quotes'} = 1; # Can handle: 'Walker''s' $self->{'drop_attr'} = ' restrict'; + $self->{'transactions'} = 1; # Transactions enabled $self->{'error_on_execute_means_zero_rows'}=1; $limits{'max_conditions'} = 5427; # (Actually not a limit) @@ -3247,13 +3404,13 @@ sub version my ($self)=@_; my ($dbh,$sth,$version,@row); - $dbh=$self->connect(); +# $dbh=$self->connect(); # # Pick up SQLGetInfo option SQL_DBMS_VER (18) # #$version = $dbh->func(18, GetInfo); - $version="FrontBase 2.1"; - $dbh->disconnect; + $version="FrontBase 3.3"; +# $dbh->disconnect; return $version; } @@ -3288,7 +3445,7 @@ sub connect sub create { my($self,$table_name,$fields,$index,$options) = @_; - my($query,@queries); + my($query,@queries,@indexes,@keys); $query="create table $table_name ("; foreach $field (@$fields) @@ -3306,18 +3463,18 @@ sub create } foreach $ind (@$index) { - my @index; - if ( $ind =~ /\bKEY\b/i ){ +# my @index; + if ( $ind =~ /(\bKEY\b)|(\bUNIQUE\b)/i ){ push(@keys,"ALTER TABLE $table_name ADD $ind"); }else{ - my @fields = split(' ',$index); + my @fields = split(' ',$ind); my $query="CREATE INDEX $fields[1] ON $table_name $fields[2]"; - push(@index,$query); + push(@indexes,$query); } } substr($query,-1)=")"; # Remove last ','; $query.=" $options" if (defined($options)); - push(@queries,$query); + push(@queries,$query,@keys,@indexes); return @queries; } @@ -3363,4 +3520,10 @@ sub reconnect_on_errors return 1; } +sub fix_for_insert +{ + my ($self,$cmd) = @_; + return $cmd; +} + 1; |