release/src/router/mysql/sql-bench/server-cfg.sh

   1 #!/usr/bin/perl
   2 # -*- perl -*-
   3 # Copyright (c) 2000-2006 MySQL AB, 2009 Sun Microsystems, Inc.
   4 # Use is subject to license terms.
   5 #
   6 # This library is free software; you can redistribute it and/or
   7 # modify it under the terms of the GNU Library General Public
   8 # License as published by the Free Software Foundation; version 2
   9 # of the License.
  10 #
  11 # This library is distributed in the hope that it will be useful,
  12 # but WITHOUT ANY WARRANTY; without even the implied warranty of
  13 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
  14 # Library General Public License for more details.
  15 #
  16 # You should have received a copy of the GNU Library General Public
  17 # License along with this library; if not, write to the Free
  18 # Software Foundation, Inc., 51 Franklin St, Fifth Floor, Boston,
  19 # MA 02110-1301, USA
  20 #
  21 # The configuration file for the DBI/DBD tests on different databases ....
  22 # You will need the DBD module for the database you are running.
  23 # Monty made this bench script and I (Luuk de Boer) rewrote it to DBI/DBD.
  24 # Monty rewrote this again to use packages.
  25 #
  26 # Each database has a different package that has 3 functions:
  27 # new           Creates a object with some standard slot
  28 # version       Version number of the server
  29 # create        Generates commands to create a table
  30 #
  31
  32 #
  33 # First some global functions that help use the packages:
  34 #
  35
  36 sub get_server
  37 {
  38   my ($name,$host,$database,$odbc,$machine,$socket,$connect_options)=@_;
  39   my ($server);
  40   if ($name =~ /mysql/i)
  41   { $server=new db_MySQL($host, $database, $machine, $socket,$connect_options); }
  42   elsif ($name =~ /pg/i)
  43   { $server= new db_Pg($host,$database); }
  44   elsif ($name =~ /msql/i)
  45   { $server= new db_mSQL($host,$database); }
  46   elsif ($name =~ /solid/i)
  47   { $server= new db_Solid($host,$database); }
  48   elsif ($name =~ /Empress/i)
  49   { $server= new db_Empress($host,$database); }
  50   elsif ($name =~ /FrontBase/i)
  51   { $server= new db_FrontBase($host,$database); }
  52   elsif ($name =~ /Oracle/i)
  53   { $server= new db_Oracle($host,$database); }
  54   elsif ($name =~ /Access/i)
  55   { $server= new db_access($host,$database); }
  56   elsif ($name =~ /Informix/i)
  57   { $server= new db_Informix($host,$database); }
  58   elsif ($name =~ /ms-sql/i)
  59   { $server= new db_ms_sql($host,$database); }
  60   elsif ($name =~ /sybase/i)
  61   { $server= new db_sybase($host,$database); }
  62   elsif ($name =~ /Adabas/i)                    # Adabas has two drivers
  63   {
  64     $server= new db_Adabas($host,$database);
  65     if ($name =~ /AdabasD/i)
  66     {
  67       $server->{'data_source'} =~ s/:Adabas:/:AdabasD:/;
  68     }
  69   }
  70   elsif ($name =~ /DB2/i)
  71   { $server= new db_db2($host,$database); }
  72   elsif ($name =~ /Mimer/i)
  73   { $server= new db_Mimer($host,$database); }
  74   elsif ($name =~ /Sapdb/i)
  75   { $server= new db_sapdb($host,$database); }
  76   elsif ($name =~ /interBase/i)
  77   { $server= new db_interbase($host,$database); }
  78   else
  79   {
  80       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, SAPDB or Sybase.\nIf the connection is done trough ODBC the name must end with _ODBC\n";
  81   }
  82   if ($name =~ /_ODBC$/i || defined($odbc) && $odbc)
  83   {
  84     if (! ($server->{'data_source'} =~ /^([^:]*):([^:]+):([^:]*)/ ))
  85     {
  86       die "Can't find databasename in data_source: '" .
  87           $server->{'data_source'}. "'\n";
  88     }
  89     if ($3) {
  90       $server->{'data_source'} = "$1:ODBC:$3";
  91     } else {
  92       $server->{'data_source'} = "$1:ODBC:$database";
  93     }
  94   }
  95   return $server;
  96 }
  97
  98 sub all_servers
  99 {
 100   return ["Access", "Adabas", "DB2", "Empress", "FrontBase", "Oracle",
 101           "Informix", "InterBase", "Mimer", "mSQL", "MS-SQL", "MySQL", "Pg","SAPDB",
 102           "Solid", "Sybase"];
 103 }
 104
 105 #############################################################################
 106 #            First the configuration for MySQL off course :-)
 107 #############################################################################
 108
 109 package db_MySQL;
 110
 111 sub new
 112 {
 113   my ($type,$host,$database,$machine,$socket,$connect_options)= @_;
 114   my $self= {};
 115   my %limits;
 116   bless $self;
 117
 118   $self->{'cmp_name'}           = "mysql";
 119   $self->{'data_source'}        = "DBI:mysql:database=$database;host=$host";
 120   $self->{'data_source'} .= ";mysql_socket=$socket" if($socket);
 121   $self->{'data_source'} .= ";$connect_options" if($connect_options);
 122   $self->{'limits'}             = \%limits;
 123   $self->{'blob'}               = "blob";
 124   $self->{'text'}               = "text";
 125   $self->{'double_quotes'}      = 1; # Can handle:  'Walker''s'
 126   $self->{'vacuum'}             = 1; # When using with --fast
 127   $self->{'drop_attr'}          = "";
 128   $self->{'transactions'}       = 0; # Transactions disabled by default
 129
 130   $limits{'NEG'}                = 1; # Supports -id
 131   $limits{'alter_add_multi_col'}= 1; #Have ALTER TABLE t add a int,add b int;
 132   $limits{'alter_table'}        = 1; # Have ALTER TABLE
 133   $limits{'alter_table_dropcol'}= 1; # Have ALTER TABLE DROP column
 134   $limits{'column_alias'}       = 1; # Alias for fields in select statement.
 135   $limits{'func_extra_%'}       = 1; # Has % as alias for mod()
 136   $limits{'func_extra_if'}      = 1; # Have function if.
 137   $limits{'func_extra_in_num'}  = 1; # Has function in
 138   $limits{'func_odbc_floor'}    = 1; # Has func_odbc_floor function
 139   $limits{'func_odbc_mod'}      = 1; # Have function mod.
 140   $limits{'functions'}          = 1; # Has simple functions (+/-)
 141   $limits{'group_by_position'}  = 1; # Can use 'GROUP BY 1'
 142   $limits{'group_distinct_functions'}= 1; # Have count(distinct)
 143   $limits{'group_func_extra_std'} = 1; # Have group function std().
 144   $limits{'group_func_sql_min_str'} = 1; # Can execute MIN() and MAX() on strings
 145   $limits{'group_functions'}    = 1; # Have group functions
 146   $limits{'having_with_alias'}  = 1; # Can use aliases in HAVING
 147   $limits{'having_with_group'}  = 1; # Can use group functions in HAVING
 148   $limits{'insert_multi_value'} = 1; # Have INSERT ... values (1,2),(3,4)
 149   $limits{'insert_select'}      = 1;
 150   $limits{'join_optimizer'}     = 1; # Can optimize FROM tables
 151   $limits{'left_outer_join'}    = 1; # Supports left outer joins
 152   $limits{'like_with_column'}   = 1; # Can use column1 LIKE column2
 153   $limits{'limit'}              = 1; # supports the limit attribute
 154   $limits{'truncate_table'}     = 1;
 155   $limits{'load_data_infile'}   = 1; # Has load data infile
 156   $limits{'lock_tables'}        = 1; # Has lock tables
 157   $limits{'max_column_name'}    = 64; # max table and column name
 158   $limits{'max_columns'}        = 2000; # Max number of columns in table
 159   $limits{'max_conditions'}     = 9999; # (Actually not a limit)
 160   $limits{'max_index'}          = 16; # Max number of keys
 161   $limits{'max_index_parts'}    = 16; # Max segments/key
 162   $limits{'max_tables'}         = (($machine || '') =~ "^win") ? 5000 : 65000;
 163   $limits{'max_text_size'}      = 1000000; # Good enough for tests
 164   $limits{'multi_drop'}         = 1; # Drop table can take many tables
 165   $limits{'order_by_position'}  = 1; # Can use 'ORDER BY 1'
 166   $limits{'order_by_unused'}    = 1;
 167   $limits{'query_size'}         = 1000000; # Max size with default buffers.
 168   $limits{'select_without_from'}= 1; # Can do 'select 1';
 169   $limits{'subqueries'}         = 0; # Doesn't support sub-queries.
 170   $limits{'table_wildcard'}     = 1; # Has SELECT table_name.*
 171   $limits{'unique_index'}       = 1; # Unique index works or not
 172   $limits{'working_all_fields'} = 1;
 173   $limits{'working_blobs'}      = 1; # If big varchar/blobs works
 174   $limits{'multi_distinct'}     = 1; # allows select count(distinct a),count(distinct b)..
 175
 176   # Some fixes that depends on the environment
 177   if (defined($main::opt_create_options) &&
 178       $main::opt_create_options =~ /engine=heap/i)
 179   {
 180     $limits{'working_blobs'}    = 0; # HEAP tables can't handle BLOB's
 181   }
 182   if (defined($main::opt_create_options) &&
 183       $main::opt_create_options =~ /engine=innodb/i)
 184   {
 185     $self->{'transactions'}     = 1;    # Transactions enabled
 186   }
 187   if (defined($main::opt_create_options) &&
 188       $main::opt_create_options =~ /engine=ndb/i)
 189   {
 190     $self->{'transactions'}     = 1;    # Transactions enabled
 191     $limits{'max_columns'}      = 90;   # Max number of columns in table
 192     $limits{'max_tables'}       = 32;   # No comments
 193   }
 194   if (defined($main::opt_create_options) &&
 195       $main::opt_create_options =~ /engine=bdb/i)
 196   {
 197     $self->{'transactions'}     = 1;    # Transactions enabled
 198   }
 199   if (defined($main::opt_create_options) &&
 200       $main::opt_create_options =~ /engine=gemini/i)
 201   {
 202     $limits{'working_blobs'}    = 0; # Blobs not implemented yet
 203     $limits{'max_tables'}       = 500;
 204     $self->{'transactions'}     = 1;    # Transactions enabled
 205   }
 206
 207   return $self;
 208 }
 209
 210 #
 211 # Get the version number of the database
 212 #
 213
 214 sub version
 215 {
 216   my ($self)=@_;
 217   my ($dbh,$sth,$version,@row);
 218
 219   $dbh=$self->connect();
 220   $sth = $dbh->prepare("select VERSION()") or die $DBI::errstr;
 221   $version="MySQL 3.20.?";
 222   if ($sth->execute && (@row = $sth->fetchrow_array))
 223   {
 224     $row[0] =~ s/-/ /g;                 # To get better tables with long names
 225     $version="MySQL $row[0]";
 226   }
 227   $sth->finish;
 228
 229   $sth = $dbh->prepare("show status like 'ssl_version'") or die $DBI::errstr;
 230   if ($sth->execute && (@row = $sth->fetchrow_array) && $row[1])
 231   {
 232     $version .= "/$row[1]";
 233   }
 234   $sth->finish;
 235   $dbh->disconnect;
 236   $version .= "/ODBC" if ($self->{'data_source'} =~ /:ODBC:/);
 237   return $version;
 238 }
 239
 240 #
 241 # Connection with optional disabling of logging
 242 #
 243
 244 sub connect
 245 {
 246   my ($self)=@_;
 247   my ($dbh);
 248   $dbh=DBI->connect($self->{'data_source'}, $main::opt_user,
 249                     $main::opt_password,{ PrintError => 0}) ||
 250                       die "Got error: '$DBI::errstr' when connecting to " . $self->{'data_source'} ." with user: '$main::opt_user' password: '$main::opt_password'\n";
 251
 252   $dbh->do("SET OPTION LOG_OFF=1,UPDATE_LOG=0");
 253   return $dbh;
 254 }
 255
 256 #
 257 # Returns a list of statements to create a table
 258 # The field types are in ANSI SQL format.
 259 #
 260 # If one uses $main::opt_fast then one is allowed to use
 261 # non standard types to get better speed.
 262 #
 263
 264 sub create
 265 {
 266   my($self,$table_name,$fields,$index,$options) = @_;
 267   my($query,@queries);
 268
 269   $query="create table $table_name (";
 270   foreach $field (@$fields)
 271   {
 272 #    $field =~ s/ decimal/ double(10,2)/i;
 273     $field =~ s/ big_decimal/ double(10,2)/i;
 274     $query.= $field . ',';
 275   }
 276   foreach $index (@$index)
 277   {
 278     $query.= $index . ',';
 279   }
 280   substr($query,-1)=")";                # Remove last ',';
 281   $query.=" $options" if (defined($options));
 282   $query.=" $main::opt_create_options" if (defined($main::opt_create_options));
 283   push(@queries,$query);
 284   return @queries;
 285 }
 286
 287 sub insert_file {
 288   my ($self,$dbname, $file, $dbh) = @_;
 289   my ($command, $sth);
 290
 291   $file =~ s|\\|/|g;                    # Change Win32 names to Unix syntax
 292   $command = "load data infile '$file' into table $dbname columns optionally enclosed by '\\'' terminated by ','";
 293 #  print "$command\n";
 294   $sth = $dbh->do($command) or die $DBI::errstr;
 295   return $sth;                  # Contains number of rows
 296 }
 297
 298 #
 299 # Do any conversions to the ANSI SQL query so that the database can handle it
 300 #
 301
 302 sub query {
 303   my($self,$sql) = @_;
 304   return $sql;
 305 }
 306
 307 sub drop_index {
 308   my ($self,$table,$index) = @_;
 309   return "DROP INDEX $index ON $table";
 310 }
 311
 312 #
 313 # Abort if the server has crashed
 314 # return: 0 if ok
 315 #         1 question should be retried
 316 #
 317
 318 sub abort_if_fatal_error
 319 {
 320   return 0;
 321 }
 322
 323 #
 324 # This should return 1 if we to do disconnect / connect when doing
 325 # big batches
 326 #
 327
 328 sub small_rollback_segment
 329 {
 330   return 0;
 331 }
 332
 333 #
 334 # reconnect on errors (needed mainly be crash-me)
 335 #
 336
 337 sub reconnect_on_errors
 338 {
 339   return 0;
 340 }
 341
 342 sub fix_for_insert
 343 {
 344   my ($self,$cmd) = @_;
 345   return $cmd;
 346 }
 347
 348 #
 349 # Optimize tables for better performance
 350 #
 351
 352 sub vacuum
 353 {
 354   my ($self,$full_vacuum,$dbh_ref,@tables)=@_;
 355   my ($loop_time,$end_time,$dbh);
 356   if ($#tables >= 0)
 357   {
 358     $dbh=$$dbh_ref;
 359     $loop_time=new Benchmark;
 360     $dbh->do("OPTIMIZE TABLE " . join(',',@tables)) || die "Got error: $DBI::errstr when executing 'OPTIMIZE TABLE'\n";
 361     $end_time=new Benchmark;
 362     print "Time for book-keeping (1): " .
 363       Benchmark::timestr(Benchmark::timediff($end_time, $loop_time),"all") . "\n\n";
 364   }
 365 }
 366
 367 #############################################################################
 368 #                    Definitions for mSQL
 369 #############################################################################
 370
 371 package db_mSQL;
 372
 373 sub new
 374 {
 375   my ($type,$host,$database)= @_;
 376   my $self= {};
 377   my %limits;
 378   bless $self;
 379
 380   $self->{'cmp_name'}           = "msql";
 381   $self->{'data_source'}        = "DBI:mSQL:$database:$host";
 382   $self->{'limits'}             = \%limits;
 383   $self->{'double_quotes'}      = 0;
 384   $self->{'drop_attr'}          = "";
 385   $self->{'transactions'}       = 0;  # No transactions
 386   $self->{'blob'}               = "text(" . $limits{'max_text_size'} .")";
 387   $self->{'text'}               = "text(" . $limits{'max_text_size'} .")";
 388
 389   $limits{'max_conditions'}     = 74;
 390   $limits{'max_columns'}        = 75;
 391   $limits{'max_tables'}         = 65000;        # Should be big enough
 392   $limits{'max_text_size'}      = 32000;
 393   $limits{'query_size'}         = 65535;
 394   $limits{'max_index'}          = 5;
 395   $limits{'max_index_parts'}    = 10;
 396   $limits{'max_column_name'} = 35;
 397
 398   $limits{'join_optimizer'}     = 0;            # Can't optimize FROM tables
 399   $limits{'load_data_infile'}   = 0;
 400   $limits{'lock_tables'}        = 0;
 401   $limits{'functions'}          = 0;
 402   $limits{'group_functions'}    = 0;
 403   $limits{'group_distinct_functions'}= 0;        # Have count(distinct)
 404   $limits{'multi_drop'}         = 0;
 405   $limits{'select_without_from'}= 0;
 406   $limits{'subqueries'}         = 0;
 407   $limits{'left_outer_join'}    = 0;
 408   $limits{'table_wildcard'}     = 0;
 409   $limits{'having_with_alias'}  = 0;
 410   $limits{'having_with_group'}  = 0;
 411   $limits{'like_with_column'}   = 1;
 412   $limits{'order_by_position'}  = 1;
 413   $limits{'group_by_position'}  = 1;
 414   $limits{'alter_table'}        = 0;
 415   $limits{'alter_add_multi_col'}= 0;
 416   $limits{'alter_table_dropcol'}= 0;
 417   $limits{'group_func_extra_std'} = 0;
 418   $limits{'limit'}              = 1;            # supports the limit attribute
 419   $limits{'unique_index'}       = 1; # Unique index works or not
 420   $limits{'insert_select'}      = 0;
 421
 422   $limits{'func_odbc_mod'}      = 0;
 423   $limits{'func_extra_%'}       = 0;
 424   $limits{'func_odbc_floor'}    = 0;
 425   $limits{'func_extra_if'}      = 0;
 426   $limits{'column_alias'}       = 0;
 427   $limits{'NEG'}                = 0;
 428   $limits{'func_extra_in_num'}  = 0;
 429   $limits{'working_blobs'}      = 1; # If big varchar/blobs works
 430   $limits{'order_by_unused'}    = 1;
 431   $limits{'working_all_fields'} = 1;
 432   $limits{'multi_distinct'}     = 1; # allows select count(distinct a),count(distinct b)..
 433   return $self;
 434 }
 435
 436 #
 437 # Get the version number of the database
 438 #
 439
 440 sub version
 441 {
 442   my ($tmp,$dir);
 443   foreach $dir ("/usr/local/Hughes", "/usr/local/mSQL","/my/local/mSQL",
 444                 "/usr/local")
 445   {
 446     if (-x "$dir/bin/msqladmin")
 447     {
 448       $tmp=`$dir/bin/msqladmin version | grep server`;
 449       if ($tmp =~ /^\s*(.*\w)\s*$/)
 450       {                         # Strip pre- and endspace
 451         $tmp=$1;
 452         $tmp =~ s/\s+/ /g;      # Remove unnecessary spaces
 453         $tmp .= "/ODBC" if ($self->{'data_source'} =~ /:ODBC:/);
 454
 455         return $tmp;
 456       }
 457     }
 458   }
 459   return "mSQL version ???";
 460 }
 461
 462
 463 sub connect
 464 {
 465   my ($self)=@_;
 466   my ($dbh);
 467   $dbh=DBI->connect($self->{'data_source'}, $main::opt_user,
 468                     $main::opt_password,{ PrintError => 0}) ||
 469                       die "Got error: '$DBI::errstr' when connecting to " . $self->{'data_source'} ." with user: '$main::opt_user' password: '$main::opt_password'\n";
 470   return $dbh;
 471 }
 472
 473 #
 474 # Can't handle many field types, so we map everything to int and real.
 475 #
 476
 477 sub create
 478 {
 479   my($self,$table_name,$fields,$index) = @_;
 480   my($query,@queries,$name,$nr);
 481
 482   $query="create table $table_name (";
 483   foreach $field (@$fields)
 484   {
 485     $field =~ s/varchar/char/i;         # mSQL doesn't have VARCHAR()
 486     # mSQL can't handle more than the real basic int types
 487     $field =~ s/tinyint|smallint|mediumint|integer/int/i;
 488     # mSQL can't handle different visual lengths
 489     $field =~ s/int\(\d*\)/int/i;
 490     # mSQL doesn't have float, change it to real
 491     $field =~ s/float(\(\d*,\d*\)){0,1}/real/i;
 492     $field =~ s/double(\(\d*,\d*\)){0,1}/real/i;
 493     # mSQL doesn't have blob, it has text instead
 494     if ($field =~ / blob/i)
 495     {
 496       $name=$self->{'blob'};
 497       $field =~ s/ blob/ $name/;
 498     }
 499     $query.= $field . ',';
 500   }
 501   substr($query,-1)=")";                # Remove last ',';
 502   push(@queries,$query);
 503   $nr=0;
 504
 505   # Prepend table_name to index name because the the name may clash with
 506   # a field name. (Should be diffent name space, but this is mSQL...)
 507
 508   foreach $index (@$index)
 509   {
 510     # Primary key is unique index in mSQL
 511     $index =~ s/primary key/unique index primary/i;
 512     if ($index =~ /^unique\s*\(([^\(]*)\)$/i)
 513     {
 514       $nr++;
 515       push(@queries,"create unique index ${table_name}_$nr on $table_name ($1)");
 516     }
 517     else
 518     {
 519       if (!($index =~ /^(.*index)\s+(\w*)\s+(\(.*\))$/i))
 520       {
 521         die "Can't parse index information in '$index'\n";
 522       }
 523       push(@queries,"create $1 ${table_name}_$2 on $table_name $3");
 524     }
 525   }
 526   return @queries;
 527 }
 528
 529
 530 sub insert_file {
 531   my($self,$dbname, $file) = @_;
 532   print "insert an ascii file isn't supported by mSQL\n";
 533   return 0;
 534 }
 535
 536
 537 sub query {
 538   my($self,$sql) = @_;
 539   return $sql;
 540 }
 541
 542 sub drop_index
 543 {
 544   my ($self,$table,$index) = @_;
 545   return "DROP INDEX $index FROM $table";
 546 }
 547
 548 sub abort_if_fatal_error
 549 {
 550   return 0;
 551 }
 552
 553 sub small_rollback_segment
 554 {
 555   return 0;
 556 }
 557
 558 sub reconnect_on_errors
 559 {
 560   return 0;
 561 }
 562
 563 sub fix_for_insert
 564 {
 565   my ($self,$cmd) = @_;
 566   return $cmd;
 567 }
 568
 569 #############################################################################
 570 #                    Definitions for PostgreSQL                             #
 571 #############################################################################
 572
 573 package db_Pg;
 574
 575 sub new
 576 {
 577   my ($type,$host,$database)= @_;
 578   my $self= {};
 579   my %limits;
 580   bless $self;
 581
 582   $self->{'cmp_name'}           = "pg";
 583   $self->{'data_source'}        = "DBI:Pg:dbname=$database";
 584   $self->{'limits'}             = \%limits;
 585   $self->{'blob'}               = "text";
 586   $self->{'text'}               = "text";
 587   $self->{'double_quotes'}      = 1;
 588   $self->{'drop_attr'}          = "";
 589   $self->{'transactions'}       = 1; # Transactions enabled
 590   $self->{"vacuum"}             = 1;
 591   $limits{'join_optimizer'}     = 1;            # Can optimize FROM tables
 592   $limits{'load_data_infile'}   = 0;
 593
 594   $limits{'NEG'}                = 1;
 595   $limits{'alter_add_multi_col'}= 0;            # alter_add_multi_col ?
 596   $limits{'alter_table'}        = 1;
 597   $limits{'alter_table_dropcol'}= 0;
 598   $limits{'column_alias'}       = 1;
 599   $limits{'func_extra_%'}       = 1;
 600   $limits{'func_extra_if'}      = 0;
 601   $limits{'func_extra_in_num'}  = 1;
 602   $limits{'func_odbc_floor'}    = 1;
 603   $limits{'func_odbc_mod'}      = 1;            # Has %
 604   $limits{'functions'}          = 1;
 605   $limits{'group_by_position'}  = 1;
 606   $limits{'group_distinct_functions'}= 1; # Have count(distinct)
 607   $limits{'group_func_extra_std'} = 0;
 608   $limits{'group_func_sql_min_str'}= 1; # Can execute MIN() and MAX() on strings
 609   $limits{'group_functions'}    = 1;
 610   $limits{'having_with_alias'}  = 0;
 611   $limits{'having_with_group'}  = 1;
 612   $limits{'insert_select'}      = 1;
 613   $limits{'left_outer_join'}    = 1;
 614   $limits{'like_with_column'}   = 1;
 615   $limits{'lock_tables'}        = 0;            # in ATIS gives this a problem
 616   $limits{'max_column_name'}    = 128;
 617   $limits{'max_columns'}        = 1000;         # 500 crashes pg 6.3
 618   $limits{'max_conditions'}     = 9999;         # This makes Pg real slow
 619   $limits{'max_index'}          = 64;           # Big enough
 620   $limits{'max_index_parts'}    = 16;
 621   $limits{'max_tables'}         = 5000;         # 10000 crashes pg 7.0.2
 622   $limits{'max_text_size'}      = 65000;        # Good enough for test
 623   $limits{'multi_drop'}         = 1;
 624   $limits{'order_by_position'}  = 1;
 625   $limits{'order_by_unused'}    = 1;
 626   $limits{'query_size'}         = 16777216;
 627   $limits{'select_without_from'}= 1;
 628   $limits{'subqueries'}         = 1;
 629   $limits{'table_wildcard'}     = 1;
 630   $limits{'truncate_table'}     = 1;
 631   $limits{'unique_index'}       = 1; # Unique index works or not
 632   $limits{'working_all_fields'} = 1;
 633   $limits{'working_blobs'}      = 1; # If big varchar/blobs works
 634   $limits{'multi_distinct'}     = 1; # allows select count(distinct a),count(distinct b)..
 635
 636   return $self;
 637 }
 638
 639 # couldn't find the option to get the version number
 640
 641 sub version
 642 {
 643   my ($version,$dir);
 644   $version = "PostgreSQL version ???";
 645   foreach $dir ($ENV{'PGDATA'},"/usr/local/pgsql/data", "/usr/local/pg/data")
 646   {
 647     if ($dir && -e "$dir/PG_VERSION")
 648     {
 649       $version= `cat $dir/PG_VERSION`;
 650       if ($? == 0)
 651       {
 652         chomp($version);
 653         $version .= "/ODBC" if ($self->{'data_source'} =~ /:ODBC:/);
 654         return "PostgreSQL $version";
 655       }
 656     }
 657   }
 658   $version .= "/ODBC" if ($self->{'data_source'} =~ /:ODBC:/);
 659   return $version;
 660 }
 661
 662
 663 sub connect
 664 {
 665   my ($self)=@_;
 666   my ($dbh);
 667   $dbh=DBI->connect($self->{'data_source'}, $main::opt_user,
 668                     $main::opt_password,{ PrintError => 0}) ||
 669                       die "Got error: '$DBI::errstr' when connecting to " . $self->{'data_source'} ." with user: '$main::opt_user' password: '$main::opt_password'\n";
 670   return $dbh;
 671 }
 672
 673
 674 sub create
 675 {
 676   my($self,$table_name,$fields,$index) = @_;
 677   my($query,@queries,$name,$in,$indfield,$table,$nr);
 678
 679   $query="create table $table_name (";
 680   foreach $field (@$fields)
 681   {
 682     if ($main::opt_fast)
 683     {
 684       # Allow use of char2, char4, char8 or char16
 685       $field =~ s/char(2|4|8|16)/char$1/;
 686     }
 687     # Pg can't handle more than the real basic int types
 688     $field =~ s/tinyint|smallint|mediumint|integer/int/;
 689     # Pg can't handle different visual lengths
 690     $field =~ s/int\(\d*\)/int/;
 691     $field =~ s/float\(\d*,\d*\)/float/;
 692     $field =~ s/ double/ float/;
 693 #    $field =~ s/ decimal/ float/i;
 694 #    $field =~ s/ big_decimal/ float/i;
 695 #    $field =~ s/ date/ int/i;
 696     # Pg doesn't have blob, it has text instead
 697     $field =~ s/ blob/ text/;
 698     $query.= $field . ',';
 699   }
 700   substr($query,-1)=")";                # Remove last ',';
 701   push(@queries,$query);
 702   foreach $index (@$index)
 703   {
 704     $index =~ s/primary key/unique index primary_key/i;
 705     if ($index =~ /^unique.*\(([^\(]*)\)$/i)
 706     {
 707       # original: $indfield="using btree (" .$1.")";
 708       # using btree doesn´t seem to work with Postgres anymore; it creates
 709       # the table and adds the index, but it isn´t unique
 710       $indfield=" (" .$1.")";
 711       $in="unique index";
 712       $table="index_$nr"; $nr++;
 713     }
 714     elsif ($index =~ /^(.*index)\s+(\w*)\s+(\(.*\))$/i)
 715     {
 716       # original: $indfield="using btree (" .$1.")";
 717       $indfield=" " .$3;
 718       $in="index";
 719       $table="index_$nr"; $nr++;
 720     }
 721     else
 722     {
 723       die "Can't parse index information in '$index'\n";
 724     }
 725     push(@queries,"create $in ${table_name}_$table on $table_name $indfield");
 726   }
 727   $queries[0]=$query;
 728   return @queries;
 729 }
 730
 731 sub insert_file {
 732   my ($self,$dbname, $file, $dbh) = @_;
 733   my ($command, $sth);
 734
 735 # Syntax:
 736 # copy [binary] <class_name> [with oids]
 737 #      {to|from} {<filename>|stdin|stdout} [using delimiters <delim>]
 738   print "The ascii files aren't correct for postgres ....!!!\n";
 739   $command = "copy $dbname from '$file' using delimiters ','";
 740   print "$command\n";
 741   $sth = $dbh->do($command) or die $DBI::errstr;
 742   return $sth;
 743 }
 744
 745 #
 746 # As postgreSQL wants A % B instead of standard mod(A,B) we have to map
 747 # This will not handle all cases, but as the benchmarks doesn't use functions
 748 # inside MOD() the following should work
 749 #
 750 # PostgreSQL cant handle count(*) or even count(1), but it can handle
 751 # count(1+1) sometimes. ==> this is solved in PostgreSQL 6.3
 752 #
 753 # PostgreSQL 6.5 is supporting MOD.
 754
 755 sub query {
 756   my($self,$sql) = @_;
 757   my(@select,$change);
 758 # if you use PostgreSQL 6.x and x is lower as 5 then uncomment the line below.
 759 #  $sql =~ s/mod\(([^,]*),([^\)]*)\)/\($1 % $2\)/gi;
 760 #
 761 # if you use PostgreSQL 6.1.x uncomment the lines below
 762 #  if ($sql =~ /select\s+count\(\*\)\s+from/i) {
 763 #  }
 764 #  elsif ($sql =~ /count\(\*\)/i)
 765 #  {
 766 #    if ($sql =~ /select\s+(.*)\s+from/i)
 767 #    {
 768 #      @select = split(/,/,$1);
 769 #      if ($select[0] =~ /(.*)\s+as\s+\w+$/i)
 770 #      {
 771 #       $change = $1;
 772 #      }
 773 #      else
 774 #      {
 775 #       $change = $select[0];
 776 #      }
 777 #    }
 778 #    if (($change =~ /count/i) || ($change eq "")) {
 779 #      $change = "1+1";
 780 #    }
 781 #    $sql =~ s/count\(\*\)/count($change)/gi;
 782 #  }
 783 # till here.
 784   return $sql;
 785 }
 786
 787 sub drop_index
 788 {
 789   my ($self,$table,$index) = @_;
 790   return "DROP INDEX $index";
 791 }
 792
 793 sub abort_if_fatal_error
 794 {
 795   return 1 if ($DBI::errstr =~ /sent to backend, but backend closed/i);
 796   return 0;
 797 }
 798
 799 sub small_rollback_segment
 800 {
 801   return 0;
 802 }
 803
 804 sub reconnect_on_errors
 805 {
 806   return 0;
 807 }
 808
 809 sub fix_for_insert
 810 {
 811   my ($self,$cmd) = @_;
 812   return $cmd;
 813 }
 814
 815 sub vacuum
 816 {
 817   my ($self,$full_vacuum,$dbh_ref,@tables)=@_;
 818   my ($loop_time,$end_time,$dbh,$table);
 819   if (defined($full_vacuum))
 820   {
 821     $$dbh_ref->disconnect;  $$dbh_ref= $self->connect();
 822   }
 823   $dbh=$$dbh_ref;
 824   $loop_time=new Benchmark;
 825   if ($#tables >= 0)
 826   {
 827     foreach $table (@tables)
 828     {
 829       $dbh->do("vacuum analyze $table") || die "Got error: $DBI::errstr when executing 'vacuum analyze $table'\n";
 830       $dbh->do("vacuum $table") || die "Got error: $DBI::errstr when executing 'vacuum'\n";
 831     }
 832   }
 833   else
 834   {
 835 #    $dbh->do("vacuum pg_attributes") || die "Got error: $DBI::errstr when executing 'vacuum'\n";
 836 #    $dbh->do("vacuum pg_index") || die "Got error: $DBI::errstr when executing 'vacuum'\n";
 837     $dbh->do("vacuum analyze") || die "Got error: $DBI::errstr when executing 'vacuum analyze'\n";
 838     $dbh->do("vacuum") || die "Got error: $DBI::errstr when executing 'vacuum'\n";
 839   }
 840   $end_time=new Benchmark;
 841   print "Time for book-keeping (1): " .
 842   Benchmark::timestr(Benchmark::timediff($end_time, $loop_time),"all") . "\n\n";
 843   $dbh->disconnect;  $$dbh_ref= $self->connect();
 844 }
 845
 846
 847 #############################################################################
 848 #                    Definitions for Solid
 849 #############################################################################
 850
 851 package db_Solid;
 852
 853 sub new
 854 {
 855   my ($type,$host,$database)= @_;
 856   my $self= {};
 857   my %limits;
 858   bless $self;
 859
 860   $self->{'cmp_name'}           = "solid";
 861   $self->{'data_source'}        = "DBI:Solid:";
 862   $self->{'limits'}             = \%limits;
 863   $self->{'blob'}               = "long varchar";
 864   $self->{'text'}               = "long varchar";
 865   $self->{'double_quotes'}      = 1;
 866   $self->{'drop_attr'}          = "";
 867   $self->{'transactions'}       = 1; # Transactions enabled
 868
 869   $limits{'max_conditions'}     = 9999;         # Probably big enough
 870   $limits{'max_columns'}        = 2000;         # From crash-me
 871   $limits{'max_tables'}         = 65000;        # Should be big enough
 872   $limits{'max_text_size'}      = 65492;        # According to tests
 873   $limits{'query_size'}         = 65535;        # Probably a limit
 874   $limits{'max_index'}          = 64;           # Probably big enough
 875   $limits{'max_index_parts'}    = 64;
 876   $limits{'max_column_name'} = 80;
 877
 878   $limits{'join_optimizer'}     = 1;
 879   $limits{'load_data_infile'}   = 0;
 880   $limits{'lock_tables'}        = 0;
 881   $limits{'functions'}          = 1;
 882   $limits{'group_functions'}    = 1;
 883   $limits{'group_func_sql_min_str'}     = 1; # Can execute MIN() and MAX() on strings
 884   $limits{'group_distinct_functions'}= 1; # Have count(distinct)
 885   $limits{'select_without_from'}= 0;            # Can do 'select 1' ?;
 886   $limits{'multi_drop'}         = 0;
 887   $limits{'subqueries'}         = 1;
 888   $limits{'left_outer_join'}    = 1;
 889   $limits{'table_wildcard'}     = 1;
 890   $limits{'having_with_alias'}  = 0;
 891   $limits{'having_with_group'}  = 1;
 892   $limits{'like_with_column'}   = 1;
 893   $limits{'order_by_position'}  = 0;            # 2.30.0018 can this
 894   $limits{'group_by_position'}  = 0;
 895   $limits{'alter_table'}        = 1;
 896   $limits{'alter_add_multi_col'}= 0;
 897   $limits{'alter_table_dropcol'}= 0;
 898
 899   $limits{'group_func_extra_std'}       = 0;    # Have group function std().
 900
 901   $limits{'func_odbc_mod'}      = 1;
 902   $limits{'func_extra_%'}       = 0;
 903   $limits{'func_odbc_floor'}    = 1;
 904   $limits{'column_alias'}       = 1;
 905   $limits{'NEG'}                = 1;
 906   $limits{'func_extra_in_num'}  = 1;
 907   $limits{'unique_index'}       = 1; # Unique index works or not
 908   $limits{'insert_select'}      = 1;
 909   $limits{'working_blobs'}      = 1; # If big varchar/blobs works
 910   $limits{'order_by_unused'}    = 1;
 911   $limits{'working_all_fields'} = 1;
 912   $limits{'multi_distinct'}     = 1; # allows select count(distinct a),count(distinct b)..
 913
 914   return $self;
 915 }
 916
 917 #
 918 # Get the version number of the database
 919 #
 920
 921 sub version
 922 {
 923   my ($version,$dir);
 924   $version="Solid version ??";
 925   foreach $dir ($ENV{'SOLIDDIR'},"/usr/local/solid", "/my/local/solid")
 926   {
 927     if ($dir && -e "$dir/bin/solcon")
 928     {
 929       $version=`$dir/bin/solcon -e"ver" $main::opt_user $main::opt_password | grep Server | sed q`;
 930       if ($? == 0)
 931       {
 932         chomp($version);
 933         $version .= "/ODBC" if ($self->{'data_source'} =~ /:ODBC:/);
 934         return $version;
 935       }
 936     }
 937   }
 938   $version .= "/ODBC" if ($self->{'data_source'} =~ /:ODBC:/);
 939   return $version;
 940 }
 941
 942 sub connect
 943 {
 944   my ($self)=@_;
 945   my ($dbh);
 946   $dbh=DBI->connect($self->{'data_source'}, $main::opt_user,
 947                     $main::opt_password,{ PrintError => 0}) ||
 948                       die "Got error: '$DBI::errstr' when connecting to " . $self->{'data_source'} ." with user: '$main::opt_user' password: '$main::opt_password'\n";
 949   return $dbh;
 950 }
 951
 952 #
 953 # Returns a list of statements to create a table
 954 # The field types are in ANSI SQL format.
 955 #
 956
 957 sub create
 958 {
 959   my($self,$table_name,$fields,$index) = @_;
 960   my($query,@queries,$nr);
 961
 962   $query="create table $table_name (";
 963   foreach $field (@$fields)
 964   {
 965     $field =~ s/mediumint/integer/i;
 966     $field =~ s/ double/ float/i;
 967     # Solid doesn't have blob, it has long varchar
 968     $field =~ s/ blob/ long varchar/;
 969 #    $field =~ s/ decimal/ float/i;
 970 #    $field =~ s/ big_decimal/ float/i;
 971 #    $field =~ s/ date/ int/i;
 972     $query.= $field . ',';
 973   }
 974   substr($query,-1)=")";                # Remove last ',';
 975   push(@queries,$query);
 976   $nr=0;
 977   foreach $index (@$index)
 978   {
 979     if ($index =~ /^primary key/i || $index =~ /^unique/i)
 980     {                                   # Add to create statement
 981       substr($queries[0],-1,0)="," . $index;
 982     }
 983     else
 984     {
 985       $index =~ /^(.*)\s+(\(.*\))$/;
 986       push(@queries,"create ${1}$nr on $table_name $2");
 987       $nr++;
 988     }
 989   }
 990   return @queries;
 991 }
 992
 993 # there is no sql statement in solid which can do the load from
 994 # an ascii file in the db ... but there is the speedloader program
 995 # an external program which can load the ascii file in the db ...
 996 # the server must be down before using speedloader !!!!
 997 # (in the standalone version)
 998 # it works also with a control file ... that one must be made ....
 999 sub insert_file {
1000   my ($self, $dbname, $file) = @_;
1001   my ($speedcmd);
1002   $speedcmd = '/usr/local/solid/bin/solload';
1003   print "At this moment not supported - solid server must go down \n";
1004   return 0;
1005 }
1006
1007 # solid can't handle an alias in a having statement so
1008 # select test as foo from tmp group by foo having foor > 2
1009 # becomes
1010 # select test as foo from tmp group by foo having test > 2
1011 #
1012 sub query {
1013   my($self,$sql) = @_;
1014   my(@select,$tmp,$newhaving,$key,%change);
1015
1016   if ($sql =~ /having\s+/i)
1017   {
1018     if ($sql =~ /select (.*) from/i)
1019     {
1020       (@select) = split(/,\s*/, $1);
1021       foreach $tmp (@select)
1022       {
1023         if ($tmp =~ /(.*)\s+as\s+(\w+)/)
1024         {
1025           $change{$2} = $1;
1026         }
1027       }
1028     }
1029     if ($sql =~ /having\s+(\w+)/i)
1030     {
1031       $newhaving = $1;
1032       foreach $key (sort {$a cmp $b} keys %change)
1033       {
1034         if ($newhaving eq $key)
1035         {
1036           $newhaving =~ s/$key/$change{$key}/g;
1037         }
1038       }
1039     }
1040     $sql =~ s/(having)\s+(\w+)/$1 $newhaving/i;
1041   }
1042   return $sql;
1043 }
1044
1045
1046 sub drop_index
1047 {
1048   my ($self,$table,$index) = @_;
1049   return "DROP INDEX $index";
1050 }
1051
1052 sub abort_if_fatal_error
1053 {
1054   return 0;
1055 }
1056
1057 sub small_rollback_segment
1058 {
1059   return 0;
1060 }
1061
1062 sub fix_for_insert
1063 {
1064   my ($self,$cmd) = @_;
1065   return $cmd;
1066 }
1067
1068 sub reconnect_on_errors
1069 {
1070   return 0;
1071 }
1072
1073 #############################################################################
1074 #                    Definitions for Empress
1075 #
1076 # at this moment DBI:Empress can only handle 200 prepare statements ...
1077 # so Empress can't be tested with the benchmark test :(
1078 #############################################################################
1079
1080 package db_Empress;
1081
1082 sub new
1083 {
1084   my ($type,$host,$database)= @_;
1085   my $self= {};
1086   my %limits;
1087   bless $self;
1088
1089   $self->{'cmp_name'}           = "empress";
1090   $self->{'data_source'}        = "DBI:EmpressNet:SERVER=$host;Database=/usr/local/empress/rdbms/bin/$database";
1091   $self->{'limits'}             = \%limits;
1092   $self->{'blob'}               = "text";
1093   $self->{'text'}               = "text";
1094   $self->{'double_quotes'}      = 1; # Can handle:  'Walker''s'
1095   $self->{'drop_attr'}          = "";
1096   $self->{'transactions'}       = 1; # Transactions enabled
1097
1098   $limits{'max_conditions'}     = 1258;
1099   $limits{'max_columns'}        = 226;          # server is disconnecting????
1100                         # above this value .... but can handle 2419 columns
1101                         # maybe something for crash-me ... but how to check ???
1102   $limits{'max_tables'}         = 65000;        # Should be big enough
1103   $limits{'max_text_size'}      = 4095;         # max returned ....
1104   $limits{'query_size'}         = 65535;        # Not a limit, big enough
1105   $limits{'max_index'}          = 64;           # Big enough
1106   $limits{'max_index_parts'}    = 64;           # Big enough
1107   $limits{'max_column_name'}    = 31;
1108
1109   $limits{'join_optimizer'}     = 1;
1110   $limits{'load_data_infile'}   = 0;
1111   $limits{'lock_tables'}        = 1;
1112   $limits{'functions'}          = 1;
1113   $limits{'group_functions'}    = 1;
1114   $limits{'group_func_sql_min_str'}     = 1; # Can execute MIN() and MAX() on strings
1115   $limits{'group_distinct_functions'}= 1; # Have count(distinct)
1116   $limits{'select_without_from'}= 0;
1117   $limits{'multi_drop'}         = 0;
1118   $limits{'subqueries'}         = 1;
1119   $limits{'table_wildcard'}     = 0;
1120   $limits{'having_with_alias'}  = 0;    # AS isn't supported in a select
1121   $limits{'having_with_group'}  = 1;
1122   $limits{'like_with_column'}   = 1;
1123   $limits{'order_by_position'}  = 1;
1124   $limits{'group_by_position'}  = 0;
1125   $limits{'alter_table'}        = 1;
1126   $limits{'alter_add_multi_col'}= 0;
1127   $limits{'alter_table_dropcol'}= 0;
1128
1129   $limits{'group_func_extra_std'}= 0;   # Have group function std().
1130
1131   $limits{'func_odbc_mod'}      = 0;
1132   $limits{'func_extra_%'}       = 1;
1133   $limits{'func_odbc_floor'}    = 1;
1134   $limits{'func_extra_if'}      = 0;
1135   $limits{'column_alias'}       = 0;
1136   $limits{'NEG'}                = 1;
1137   $limits{'func_extra_in_num'}  = 0;
1138   $limits{'unique_index'}       = 1; # Unique index works or not
1139   $limits{'insert_select'}      = 1;
1140   $limits{'working_blobs'}      = 1; # If big varchar/blobs works
1141   $limits{'order_by_unused'}    = 1;
1142   $limits{'working_all_fields'} = 1;
1143   $limits{'multi_distinct'}     = 1; # allows select count(distinct a),count(distinct b)..
1144
1145   return $self;
1146 }
1147
1148 #
1149 # Get the version number of the database
1150 #
1151
1152 sub version
1153 {
1154   my ($self,$dbh)=@_;
1155   my ($version);
1156   $version="";
1157   if (-x "/usr/local/empress/rdbms/bin/empvers")
1158   {
1159     $version=`/usr/local/empress/rdbms/bin/empvers | grep Version`;
1160   }
1161   if ($version)
1162   {
1163     chomp($version);
1164   }
1165   else
1166   {
1167     $version="Empress version ???";
1168   }
1169
1170   $version .= "/ODBC" if ($self->{'data_source'} =~ /:ODBC:/);
1171   return $version;
1172 }
1173
1174 sub connect
1175 {
1176   my ($self)=@_;
1177   my ($dbh);
1178   $dbh=DBI->connect($self->{'data_source'}, $main::opt_user,
1179                     $main::opt_password,{ PrintError => 0}) ||
1180                       die "Got error: '$DBI::errstr' when connecting to " . $self->{'data_source'} ." with user: '$main::opt_user' password: '$main::opt_password'\n";
1181   return $dbh;
1182 }
1183
1184 sub insert_file {
1185   my($self,$dbname, $file) = @_;
1186   my($command,$sth);
1187   $command = "insert into $dbname from '$file'";
1188   print "$command\n" if ($opt_debug);
1189   $sth = $dbh->do($command) or die $DBI::errstr;
1190
1191   return $sth;
1192 }
1193
1194 #
1195 # Returns a list of statements to create a table
1196 # The field types are in ANSI SQL format.
1197 #
1198
1199 sub create
1200 {
1201   my($self,$table_name,$fields,$index) = @_;
1202   my($query,@queries,$nr);
1203
1204   $query="create table $table_name (";
1205   foreach $field (@$fields)
1206   {
1207     $field =~ s/mediumint/int/i;
1208     $field =~ s/tinyint/int/i;
1209     $field =~ s/smallint/int/i;
1210     $field =~ s/longint/int/i;
1211     $field =~ s/integer/int/i;
1212     $field =~ s/ double/ longfloat/i;
1213     # Solid doesn't have blob, it has long varchar
1214 #    $field =~ s/ blob/ text(65535,65535,65535,65535)/;
1215     $field =~ s/ blob/ text/;
1216     $field =~ s/ varchar\((\d+)\)/ char($1,3)/;
1217     $field =~ s/ char\((\d+)\)/ char($1,3)/;
1218 #    $field =~ s/ decimal/ float/i;
1219 #    $field =~ s/ big_decimal/ longfloat/i;
1220 #    $field =~ s/ date/ int/i;
1221     $field =~ s/ float(.*)/ float/i;
1222     if ($field =~ / int\((\d+)\)/) {
1223       if ($1 > 4) {
1224         $field =~ s/ int\(\d+\)/ longinteger/i;
1225       } else {
1226         $field =~ s/ int\(\d+\)/ longinteger/i;
1227       }
1228     } else {
1229       $field =~ s/ int/ longinteger/i;
1230     }
1231     $query.= $field . ',';
1232   }
1233   substr($query,-1)=")";                # Remove last ',';
1234   push(@queries,$query);
1235   $nr=1;
1236   foreach $index (@$index)
1237   {
1238     # Primary key is unique index in Empress
1239     $index =~ s/primary key/unique index/i;
1240     if ($index =~ /^unique.*\(([^\(]*)\)$/i)
1241     {
1242       $nr++;
1243       push(@queries,"create unique index ${table_name}_$nr on $table_name ($1)");
1244     }
1245     else
1246     {
1247       if (!($index =~ /^(.*index)\s+(\w*)\s+(\(.*\))$/i))
1248       {
1249         die "Can't parse index information in '$index'\n";
1250       }
1251       push(@queries,"create $1 ${table_name}_$2 on $table_name $3");
1252     }
1253   }
1254   return @queries;
1255 }
1256
1257 # empress can't handle an alias and but can handle the number of the
1258 # columname - so
1259 # select test as foo from tmp order by foo
1260 # becomes
1261 # select test from tmp order by 1
1262 #
1263 sub query {
1264   my($self,$sql) = @_;
1265   my(@select,$i,$tmp,$newselect,$neworder,@order,$key,%change);
1266   my($tmp1,$otmp,$tmp2);
1267
1268   if ($sql =~ /\s+as\s+/i)
1269   {
1270     if ($sql =~ /select\s+(.*)\s+from/i) {
1271       $newselect = $1;
1272       (@select) = split(/,\s*/, $1);
1273       $i = 1;
1274       foreach $tmp (@select) {
1275         if ($tmp =~ /\s+as\s+(\w+)/) {
1276           $change{$1} = $i;
1277         }
1278         $i++;
1279       }
1280     }
1281     $newselect =~ s/\s+as\s+(\w+)//gi;
1282     $tmp2 = 0;
1283     if ($sql =~ /order\s+by\s+(.*)$/i) {
1284       (@order) = split(/,\s*/, $1);
1285       foreach $otmp (@order) {
1286         foreach $key (sort {$a cmp $b} keys %change) {
1287           if ($otmp eq $key) {
1288             $neworder .= "$tmp1"."$change{$key}";
1289             $tmp1 = ", ";
1290             $tmp2 = 1;
1291           } elsif ($otmp =~ /(\w+)\s+(.+)$/) {
1292             if ($key eq $1) {
1293               $neworder .= "$tmp1"."$change{$key} $2";
1294               $tmp2 = 1;
1295             }
1296           }
1297         }
1298         if ($tmp2 == 0) {
1299           $neworder .= "$tmp1"."$otmp";
1300         }
1301         $tmp2 = 0;
1302         $tmp1 = ", ";
1303       }
1304     }
1305     $sql =~ s/(select)\s+(.*)\s+(from)/$1 $newselect $3/i;
1306     $sql =~ s/(order\s+by)\s+(.*)$/$1 $neworder/i;
1307   }
1308   return $sql;
1309 }
1310
1311 sub fix_for_insert
1312 {
1313   my ($self,$cmd) = @_;
1314   $cmd =~ s/\'\'/\' \'/g;
1315   return $cmd;
1316 }
1317
1318
1319 sub drop_index
1320 {
1321   my ($self,$table,$index) = @_;
1322   return "DROP INDEX $index";
1323 }
1324
1325 # This is a because of the 200 statement problem with DBI-Empress
1326
1327 sub abort_if_fatal_error
1328 {
1329   if ($DBI::errstr =~ /Overflow of table of prepared statements/i)
1330   {
1331     print "Overflow of prepared statements ... killing the process\n";
1332     exit 1;
1333   }
1334   return 0;
1335 }
1336
1337 sub small_rollback_segment
1338 {
1339   return 0;
1340 }
1341
1342 sub reconnect_on_errors
1343 {
1344   return 0;
1345 }
1346
1347 #############################################################################
1348 #                        Definitions for Oracle
1349 #############################################################################
1350
1351 package db_Oracle;
1352
1353 sub new
1354 {
1355   my ($type,$host,$database)= @_;
1356   my $self= {};
1357   my %limits;
1358   bless $self;
1359
1360   $self->{'cmp_name'}           = "Oracle";
1361   $self->{'data_source'}        = "DBI:Oracle:$database";
1362   $self->{'limits'}             = \%limits;
1363   $self->{'blob'}               = "long";
1364   $self->{'text'}               = "long";
1365   $self->{'double_quotes'}      = 1; # Can handle:  'Walker''s'
1366   $self->{'drop_attr'}          = "";
1367   $self->{'transactions'}       = 1; # Transactions enabled
1368   $self->{"vacuum"}             = 1;
1369
1370   $limits{'max_conditions'}     = 9999; # (Actually not a limit)
1371   $limits{'max_columns'}        = 254;  # Max number of columns in table
1372   $limits{'max_tables'}         = 65000; # Should be big enough
1373   $limits{'max_text_size'}      = 2000; # Limit for blob test-connect
1374   $limits{'query_size'}         = 65525; # Max size with default buffers.
1375   $limits{'max_index'}          = 16; # Max number of keys
1376   $limits{'max_index_parts'}    = 16; # Max segments/key
1377   $limits{'max_column_name'} = 32; # max table and column name
1378
1379   $limits{'truncate_table'}     = 1;
1380   $limits{'join_optimizer'}     = 1; # Can optimize FROM tables
1381   $limits{'load_data_infile'}   = 0; # Has load data infile
1382   $limits{'lock_tables'}        = 0; # Has lock tables
1383   $limits{'functions'}          = 1; # Has simple functions (+/-)
1384   $limits{'group_functions'}    = 1; # Have group functions
1385   $limits{'group_func_sql_min_str'}     = 1; # Can execute MIN() and MAX() on strings
1386   $limits{'group_distinct_functions'}= 1; # Have count(distinct)
1387   $limits{'select_without_from'}= 0;
1388   $limits{'multi_drop'}         = 0;
1389   $limits{'subqueries'}         = 1;
1390   $limits{'left_outer_join'}    = 0; # This may be fixed in the query module
1391   $limits{'table_wildcard'}     = 1; # Has SELECT table_name.*
1392   $limits{'having_with_alias'}  = 0; # Can use aliases in HAVING
1393   $limits{'having_with_group'}  = 1; # Can't use group functions in HAVING
1394   $limits{'like_with_column'}   = 1; # Can use column1 LIKE column2
1395   $limits{'order_by_position'}  = 1; # Can use 'ORDER BY 1'
1396   $limits{'group_by_position'}  = 0;
1397   $limits{'alter_table'}        = 1;
1398   $limits{'alter_add_multi_col'}= 0;
1399   $limits{'alter_table_dropcol'}= 0;
1400
1401   $limits{'group_func_extra_std'}       = 0; # Have group function std().
1402
1403   $limits{'func_odbc_mod'}      = 0; # Oracle has problem with mod()
1404   $limits{'func_extra_%'}       = 0; # Has % as alias for mod()
1405   $limits{'func_odbc_floor'}    = 1; # Has func_odbc_floor function
1406   $limits{'func_extra_if'}      = 0; # Have function if.
1407   $limits{'column_alias'}       = 1; # Alias for fields in select statement.
1408   $limits{'NEG'}                = 1; # Supports -id
1409   $limits{'func_extra_in_num'}  = 1; # Has function in
1410   $limits{'unique_index'}       = 1; # Unique index works or not
1411   $limits{'insert_select'}      = 1;
1412   $limits{'working_blobs'}      = 1; # If big varchar/blobs works
1413   $limits{'order_by_unused'}    = 1;
1414   $limits{'working_all_fields'} = 1;
1415   $limits{'multi_distinct'}     = 1; # allows select count(distinct a),count(distinct b)..
1416
1417
1418   return $self;
1419 }
1420
1421 #
1422 # Get the version number of the database
1423 #
1424
1425 sub version
1426 {
1427   my ($self)=@_;
1428   my ($dbh,$sth,$version,@row);
1429
1430   $dbh=$self->connect();
1431   $sth = $dbh->prepare("select VERSION from product_component_version WHERE PRODUCT like 'Oracle%'") or die $DBI::errstr;
1432   $version="Oracle 7.x";
1433   if ($sth->execute && (@row = $sth->fetchrow_array))
1434   {
1435     $version="Oracle $row[0]";
1436   }
1437   $sth->finish;
1438   $dbh->disconnect;
1439   $version .= "/ODBC" if ($self->{'data_source'} =~ /:ODBC:/);
1440   return $version;
1441 }
1442
1443 sub connect
1444 {
1445   my ($self)=@_;
1446   my ($dbh);
1447   $dbh=DBI->connect($self->{'data_source'}, $main::opt_user,
1448                     $main::opt_password,{ PrintError => 0}) ||
1449                       die "Got error: '$DBI::errstr' when connecting to " . $self->{'data_source'} ." with user: '$main::opt_user' password: '$main::opt_password'\n";
1450   return $dbh;
1451 }
1452
1453 #
1454 # Returns a list of statements to create a table
1455 # The field types are in ANSI SQL format.
1456 #
1457 # If one uses $main::opt_fast then one is allowed to use
1458 # non standard types to get better speed.
1459 #
1460
1461 sub create
1462 {
1463   my($self,$table_name,$fields,$index) = @_;
1464   my($query,@queries,$ind,@keys);
1465
1466   $query="create table $table_name (";
1467   foreach $field (@$fields)
1468   {
1469     $field =~ s/ character\((\d+)\)/ char\($1\)/i;
1470     $field =~ s/ character varying\((\d+)\)/ varchar\($1\)/i;
1471     $field =~ s/ char varying\((\d+)\)/ varchar\($1\)/i;
1472     $field =~ s/ integer/ number\(38\)/i;
1473     $field =~ s/ int/ number\(38\)/i;
1474     $field =~ s/ tinyint/ number\(38\)/i;
1475     $field =~ s/ smallint/ number\(38\)/i;
1476     $field =~ s/ mediumint/ number\(38\)/i;
1477     $field =~ s/ tinynumber\((\d+)\)\((\d+)\)/ number\($1,$2\)/i;
1478     $field =~ s/ smallnumber\((\d+)\)\((\d+)\)/ number\($1,$2\)/i;
1479     $field =~ s/ mediumnumber\((\d+)\)\((\d+)\)/ number\($1,$2\)/i;
1480     $field =~ s/ number\((\d+)\)\((\d+)\)/ number\($1,$2\)/i;
1481     $field =~ s/ numeric\((\d+)\)\((\d+)\)/ number\($1,$2\)/i;
1482     $field =~ s/ decimal\((\d+)\)\((\d+)\)/ number\($1,$2\)/i;
1483     $field =~ s/ dec\((\d+)\)\((\d+)\)/ number\($1,$2\)/i;
1484     $field =~ s/ float/ number/;
1485     $field =~ s/ real/ number/;
1486     $field =~ s/ double precision/ number/;
1487     $field =~ s/ double/ number/;
1488     $field =~ s/ blob/ long/;
1489     $query.= $field . ',';
1490   }
1491
1492   foreach $ind (@$index)
1493   {
1494     my @index;
1495     if ( $ind =~ /\bKEY\b/i ){
1496       push(@keys,"ALTER TABLE $table_name ADD $ind");
1497     }else{
1498       my @fields = split(' ',$index);
1499       my $query="CREATE INDEX $fields[1] ON $table_name $fields[2]";
1500       push(@index,$query);
1501     }
1502   }
1503   substr($query,-1)=")";                # Remove last ',';
1504   push(@queries,$query,@keys,@index);
1505 #print "query:$query\n";
1506
1507   return @queries;
1508 }
1509
1510 sub insert_file {
1511   my($self,$dbname, $file) = @_;
1512   print "insert an ascii file isn't supported by Oracle (?)\n";
1513   return 0;
1514 }
1515
1516 #
1517 # Do any conversions to the ANSI SQL query so that the database can handle it
1518 #
1519
1520 sub query {
1521   my($self,$sql) = @_;
1522   return $sql;
1523 }
1524
1525 sub fix_for_insert
1526 {
1527   my ($self,$cmd) = @_;
1528   $cmd =~ s/\'\'/\' \'/g;
1529   return $cmd;
1530 }
1531
1532
1533 sub drop_index
1534 {
1535   my ($self,$table,$index) = @_;
1536   return "DROP INDEX $index";
1537 }
1538
1539 #
1540 # Abort if the server has crashed
1541 # return: 0 if ok
1542 #         1 question should be retried
1543 #
1544
1545 sub abort_if_fatal_error
1546 {
1547   return 0;
1548 }
1549
1550 sub small_rollback_segment
1551 {
1552   return 1;
1553 }
1554
1555 sub reconnect_on_errors
1556 {
1557   return 0;
1558 }
1559
1560 #
1561 # optimize the tables ....
1562 #
1563 sub vacuum
1564 {
1565   my ($self,$full_vacuum,$dbh_ref)=@_;
1566   my ($loop_time,$end_time,$sth,$dbh);
1567
1568   if (defined($full_vacuum))
1569   {
1570     $$dbh_ref->disconnect;  $$dbh_ref= $self->connect();
1571   }
1572   $dbh=$$dbh_ref;
1573   $loop_time=new Benchmark;
1574   # first analyze all tables
1575   $sth = $dbh->prepare("select table_name from user_tables") || die "Got error: $DBI::errstr";
1576   $sth->execute || die "Got error: $DBI::errstr when select user_tables";
1577   while (my @r = $sth->fetchrow_array)
1578   {
1579     $dbh->do("analyze table $r[0] compute statistics") || die "Got error: $DBI::errstr when executing 'analyze table'\n";
1580   }
1581   # now analyze all indexes ...
1582   $sth = $dbh->prepare("select index_name from user_indexes") || die "Got error: $DBI::errstr";
1583   $sth->execute || die "Got error: $DBI::errstr when select user_indexes";
1584   while (my @r1 = $sth->fetchrow_array)
1585   {
1586     $dbh->do("analyze index $r1[0] compute statistics") || die "Got error: $DBI::errstr when executing 'analyze index $r1[0]'\n";
1587   }
1588   $end_time=new Benchmark;
1589   print "Time for book-keeping (1): " .
1590   Benchmark::timestr(Benchmark::timediff($end_time, $loop_time),"all") . "\n\n";
1591   $dbh->disconnect;  $$dbh_ref= $self->connect();
1592 }
1593
1594
1595 #############################################################################
1596 #                        Definitions for Informix
1597 #############################################################################
1598
1599 package db_Informix;
1600
1601 sub new
1602 {
1603   my ($type,$host,$database)= @_;
1604   my $self= {};
1605   my %limits;
1606   bless $self;
1607
1608   $self->{'cmp_name'}           = "Informix";
1609   $self->{'data_source'}        = "DBI:Informix:$database";
1610   $self->{'limits'}             = \%limits;
1611   $self->{'blob'}               = "byte in table";
1612   $self->{'text'}               = "byte in table";
1613   $self->{'double_quotes'}      = 0; # Can handle:  'Walker''s'
1614   $self->{'drop_attr'}          = "";
1615   $self->{'transactions'}       = 1; # Transactions enabled
1616   $self->{'host'}               = $host;
1617
1618   $limits{'NEG'}                = 1; # Supports -id
1619   $limits{'alter_table'}        = 1;
1620   $limits{'alter_add_multi_col'}= 0;
1621   $limits{'alter_table_dropcol'}= 1;
1622   $limits{'column_alias'}       = 1; # Alias for fields in select statement.
1623   $limits{'func_extra_%'}       = 0; # Has % as alias for mod()
1624   $limits{'func_extra_if'}      = 0; # Have function if.
1625   $limits{'func_extra_in_num'}= 0; # Has function in
1626   $limits{'func_odbc_floor'}    = 0; # Has func_odbc_floor function
1627   $limits{'func_odbc_mod'}      = 1; # Have function mod.
1628   $limits{'functions'}          = 1; # Has simple functions (+/-)
1629   $limits{'group_by_position'}  = 1; # Can use 'GROUP BY 1'
1630   $limits{'group_by_alias'}  = 0; # Can use 'select a as ab from x GROUP BY ab'
1631   $limits{'group_func_extra_std'} = 0; # Have group function std().
1632   $limits{'group_functions'}    = 1; # Have group functions
1633   $limits{'group_func_sql_min_str'}     = 1; # Can execute MIN() and MAX() on strings
1634   $limits{'group_distinct_functions'}= 1; # Have count(distinct)
1635   $limits{'having_with_alias'}  = 0; # Can use aliases in HAVING
1636   $limits{'having_with_group'}= 1; # Can't use group functions in HAVING
1637   $limits{'join_optimizer'}     = 1; # Can optimize FROM tables (always 1 only for msql)
1638   $limits{'left_outer_join'}    = 0; # Supports left outer joins (ANSI)
1639   $limits{'like_with_column'}   = 1; # Can use column1 LIKE column2
1640   $limits{'load_data_infile'}   = 0; # Has load data infile
1641   $limits{'lock_tables'}        = 1; # Has lock tables
1642   $limits{'max_conditions'}     = 1214; # (Actually not a limit)
1643   $limits{'max_column_name'}    = 18; # max table and column name
1644   $limits{'max_columns'}        = 994;  # Max number of columns in table
1645   $limits{'max_tables'}         = 65000;        # Should be big enough
1646   $limits{'max_index'}          = 64; # Max number of keys
1647   $limits{'max_index_parts'}    = 15; # Max segments/key
1648   $limits{'max_text_size'}      = 65535;  # Max size with default buffers. ??
1649   $limits{'multi_drop'}         = 0; # Drop table can take many tables
1650   $limits{'order_by_position'}  = 1; # Can use 'ORDER BY 1'
1651   $limits{'query_size'}         = 32766; # Max size with default buffers.
1652   $limits{'select_without_from'}= 0; # Can do 'select 1';
1653   $limits{'subqueries'}         = 1; # Doesn't support sub-queries.
1654   $limits{'table_wildcard'}     = 1; # Has SELECT table_name.*
1655   $limits{'unique_index'}       = 1; # Unique index works or not
1656   $limits{'insert_select'}      = 1;
1657   $limits{'working_blobs'}      = 1; # If big varchar/blobs works
1658   $limits{'order_by_unused'}    = 1;
1659   $limits{'working_all_fields'} = 1;
1660   $limits{'multi_distinct'}     = 1; # allows select count(distinct a),count(distinct b)..
1661
1662   return $self;
1663 }
1664
1665 #
1666 # Get the version number of the database
1667 #
1668
1669 sub version
1670 {
1671   my ($self)=@_;
1672   my ($dbh,$sth,$version,@row);
1673
1674   $ENV{'INFORMIXSERVER'} = $self->{'host'};
1675   $dbh=$self->connect();
1676   $sth = $dbh->prepare("SELECT owner FROM systables WHERE tabname = ' VERSION'")
1677                                                       or die $DBI::errstr;
1678   $version='Informix unknown';
1679   if ($sth->execute && (@row = $sth->fetchrow_array))
1680   {
1681     $version="Informix $row[0]";
1682   }
1683   $sth->finish;
1684   $dbh->disconnect;
1685   $version .= "/ODBC" if ($self->{'data_source'} =~ /:ODBC:/);
1686   return $version;
1687 }
1688
1689 sub connect
1690 {
1691   my ($self)=@_;
1692   my ($dbh);
1693   $dbh=DBI->connect($self->{'data_source'}, $main::opt_user,
1694                     $main::opt_password,{ PrintError => 0}) ||
1695                       die "Got error: '$DBI::errstr' when connecting to " . $self->{'data_source'} ." with user: '$main::opt_user' password: '$main::opt_password'\n";
1696   return $dbh;
1697 }
1698
1699
1700 #
1701 # Create table
1702 #
1703
1704 sub create
1705 {
1706   my($self,$table_name,$fields,$index) = @_;
1707   my($query,@queries,$name,$nr);
1708
1709   $query="create table $table_name (";
1710   foreach $field (@$fields)
1711   {
1712 #    $field =~ s/\btransport_description\b/transport_desc/;
1713                                 # to overcome limit 18 chars
1714     $field =~ s/tinyint/smallint/i;
1715     $field =~ s/tinyint\(\d+\)/smallint/i;
1716     $field =~ s/mediumint/integer/i;
1717     $field =~ s/mediumint\(\d+\)/integer/i;
1718     $field =~ s/smallint\(\d+\)/smallint/i;
1719     $field =~ s/integer\(\d+\)/integer/i;
1720     $field =~ s/int\(\d+\)/integer/i;
1721 #    $field =~ s/\b(?:small)?int(?:eger)?\((\d+)\)/decimal($1)/i;
1722 #    $field =~ s/float(\(\d*,\d*\)){0,1}/real/i;
1723     $field =~ s/(float|double)(\(.*?\))?/float/i;
1724
1725     if ($field =~ / blob/i)
1726     {
1727       $name=$self->{'blob'};
1728       $field =~ s/ blob/ $name/;
1729     }
1730     $query.= $field . ',';
1731   }
1732   substr($query,-1)=")";                # Remove last ',';
1733   push(@queries,$query);
1734   $nr=0;
1735
1736   foreach $index (@$index)
1737   {
1738     # Primary key is unique index in Informix
1739     $index =~ s/primary key/unique index primary/i;
1740     if ($index =~ /^unique\s*\(([^\(]*)\)$/i)
1741     {
1742       $nr++;
1743       push(@queries,"create unique index ${table_name}_$nr on $table_name ($1)");
1744     }
1745     else
1746     {
1747       if (!($index =~ /^(.*index)\s+(\w*)\s+(\(.*\))$/i))
1748       {
1749         die "Can't parse index information in '$index'\n";
1750       }
1751       ### push(@queries,"create $1 ${table_name}_$2 on $table_name $3");
1752       $nr++;
1753       push(@queries,"create $1 ${table_name}_$nr on $table_name $3");
1754     }
1755   }
1756   return @queries;
1757 }
1758 #
1759 # Some test needed this
1760 #
1761
1762 sub query {
1763   my($self,$sql) = @_;
1764   return $sql;
1765 }
1766
1767
1768 sub fix_for_insert
1769 {
1770   my ($self,$cmd) = @_;
1771   $cmd =~ s/\\\'//g;
1772   return $cmd;
1773 }
1774
1775
1776
1777 sub drop_index
1778 {
1779   my ($self,$table,$index) = @_;
1780   return "DROP INDEX $index";
1781 }
1782
1783 #
1784 # Abort if the server has crashed
1785 # return: 0 if ok
1786 #         1 question should be retried
1787 #
1788
1789 sub abort_if_fatal_error
1790 {
1791   return 0;
1792 }
1793
1794 sub small_rollback_segment
1795 {
1796   return 0;
1797 }
1798
1799 sub reconnect_on_errors
1800 {
1801   return 0;
1802 }
1803
1804
1805 #############################################################################
1806 #            Configuration for Access
1807 #############################################################################
1808
1809 package db_access;
1810
1811 sub new
1812 {
1813   my ($type,$host,$database)= @_;
1814   my $self= {};
1815   my %limits;
1816   bless $self;
1817
1818   $self->{'cmp_name'}           = "access";
1819   $self->{'data_source'}        = "DBI:ODBC:$database";
1820   if (defined($host) && $host ne "")
1821   {
1822     $self->{'data_source'}      .= ":$host";
1823   }
1824   $self->{'limits'}             = \%limits;
1825   $self->{'blob'}               = "blob";
1826   $self->{'text'}               = "blob"; # text ?
1827   $self->{'double_quotes'}      = 1; # Can handle:  'Walker''s'
1828   $self->{'drop_attr'}          = "";
1829   $self->{'transactions'}       = 1; # Transactions enabled
1830
1831   $limits{'max_conditions'}     = 97; # We get 'Query is too complex'
1832   $limits{'max_columns'}        = 255;  # Max number of columns in table
1833   $limits{'max_tables'}         = 65000;        # Should be big enough
1834   $limits{'max_text_size'}      = 255;  # Max size with default buffers.
1835   $limits{'query_size'}         = 65535; # Not a limit, big enough
1836   $limits{'max_index'}          = 32; # Max number of keys
1837   $limits{'max_index_parts'}    = 10; # Max segments/key
1838   $limits{'max_column_name'}    = 64; # max table and column name
1839
1840   $limits{'join_optimizer'}     = 1; # Can optimize FROM tables
1841   $limits{'load_data_infile'}   = 0; # Has load data infile
1842   $limits{'lock_tables'}        = 0; # Has lock tables
1843   $limits{'functions'}          = 1; # Has simple functions (+/-)
1844   $limits{'group_functions'}    = 1; # Have group functions
1845   $limits{'group_func_sql_min_str'}     = 1; # Can execute MIN() and MAX() on strings
1846   $limits{'group_distinct_functions'}= 0; # Have count(distinct)
1847   $limits{'select_without_from'}= 1; # Can do 'select 1';
1848   $limits{'multi_drop'}         = 0; # Drop table can take many tables
1849   $limits{'subqueries'}         = 1; # Supports sub-queries.
1850   $limits{'left_outer_join'}    = 1; # Supports left outer joins
1851   $limits{'table_wildcard'}     = 1; # Has SELECT table_name.*
1852   $limits{'having_with_alias'}  = 0; # Can use aliases in HAVING
1853   $limits{'having_with_group'}  = 1; # Can use group functions in HAVING
1854   $limits{'like_with_column'}   = 1; # Can use column1 LIKE column2
1855   $limits{'order_by_position'}  = 1; # Can use 'ORDER BY 1'
1856   $limits{'group_by_position'}  = 0; # Can use 'GROUP BY 1'
1857   $limits{'alter_table'}        = 1;
1858   $limits{'alter_add_multi_col'}= 2; #Have ALTER TABLE t add a int, b int;
1859   $limits{'alter_table_dropcol'}= 1;
1860
1861   $limits{'group_func_extra_std'} = 0; # Have group function std().
1862
1863   $limits{'func_odbc_mod'}      = 0; # Have function mod.
1864   $limits{'func_extra_%'}       = 0; # Has % as alias for mod()
1865   $limits{'func_odbc_floor'}    = 0; # Has func_odbc_floor function
1866   $limits{'func_extra_if'}      = 0; # Have function if.
1867   $limits{'column_alias'}       = 1; # Alias for fields in select statement.
1868   $limits{'NEG'}                = 1; # Supports -id
1869   $limits{'func_extra_in_num'}  = 1; # Has function in
1870   $limits{'unique_index'}       = 1; # Unique index works or not
1871   $limits{'insert_select'}      = 1;
1872   $limits{'working_blobs'}      = 1; # If big varchar/blobs works
1873   $limits{'order_by_unused'}    = 1;
1874   $limits{'working_all_fields'} = 1;
1875   $limits{'multi_distinct'}     = 1; # allows select count(distinct a),count(distinct b)..
1876   return $self;
1877 }
1878
1879 #
1880 # Get the version number of the database
1881 #
1882
1883 sub version
1884 {
1885   my ($self)=@_;
1886   my $version="Access 2000";
1887   $version .= "/ODBC" if ($self->{'data_source'} =~ /:ODBC:/);
1888   return $version;              #DBI/ODBC can't return the server version
1889 }
1890
1891 sub connect
1892 {
1893   my ($self)=@_;
1894   my ($dbh);
1895   $dbh=DBI->connect($self->{'data_source'}, $main::opt_user,
1896                     $main::opt_password,{ PrintError => 0}) ||
1897                       die "Got error: '$DBI::errstr' when connecting to " . $self->{'data_source'} ." with user: '$main::opt_user' password: '$main::opt_password'\n";
1898   return $dbh;
1899 }
1900
1901 #
1902 # Returns a list of statements to create a table
1903 # The field types are in ANSI SQL format.
1904 #
1905
1906 sub create
1907 {
1908   my($self,$table_name,$fields,$index) = @_;
1909   my($query,@queries,$nr);
1910
1911   $query="create table $table_name (";
1912   foreach $field (@$fields)
1913   {
1914     $field =~ s/mediumint/integer/i;
1915     $field =~ s/tinyint/smallint/i;
1916     $field =~ s/float\(\d+,\d+\)/float/i;
1917     $field =~ s/integer\(\d+\)/integer/i;
1918     $field =~ s/smallint\(\d+\)/smallint/i;
1919     $field =~ s/int\(\d+\)/integer/i;
1920     $field =~ s/blob/text/i;
1921     $query.= $field . ',';
1922   }
1923   substr($query,-1)=")";                # Remove last ',';
1924   push(@queries,$query);
1925   $nr=0;
1926   foreach $index (@$index)
1927   {
1928     $ext="WITH DISALLOW NULL";
1929     if (($index =~ s/primary key/unique index primary_key/i))
1930     {
1931       $ext="WITH PRIMARY;"
1932     }
1933     if ($index =~ /^unique.*\(([^\(]*)\)$/i)
1934     {
1935       $nr++;
1936       $index="unique index ${table_name}_$nr ($1)";
1937     }
1938     $index =~ /^(.*)\s+(\(.*\))$/;
1939     push(@queries,"create ${1} on $table_name $2");
1940   }
1941   return @queries;
1942 }
1943
1944 #
1945 # Do any conversions to the ANSI SQL query so that the database can handle it
1946 #
1947
1948 sub query {
1949   my($self,$sql) = @_;
1950   return $sql;
1951 }
1952
1953 sub drop_index
1954 {
1955   my ($self,$table,$index) = @_;
1956   return "DROP INDEX $index ON $table";
1957 }
1958
1959 #
1960 # Abort if the server has crashed
1961 # return: 0 if ok
1962 #         1 question should be retried
1963 #
1964
1965 sub abort_if_fatal_error
1966 {
1967   return 1 if (($DBI::errstr =~ /The database engine couldn\'t lock table/i) ||
1968                ($DBI::errstr =~ /niet vergrendelen. De tabel is momenteel in gebruik /i) ||
1969                ($DBI::errstr =~ /Den anv.* redan av en annan/i) ||
1970                ($DBI::errstr =~ /non-exclusive access/));
1971   return 0;
1972 }
1973
1974 sub small_rollback_segment
1975 {
1976   return 0;
1977 }
1978
1979 sub reconnect_on_errors
1980 {
1981   return 1;
1982 }
1983
1984 sub fix_for_insert
1985 {
1986   my ($self,$cmd) = @_;
1987   return $cmd;
1988 }
1989
1990 #############################################################################
1991 #            Configuration for Microsoft SQL server
1992 #############################################################################
1993
1994 package db_ms_sql;
1995
1996 sub new
1997 {
1998   my ($type,$host,$database)= @_;
1999   my $self= {};
2000   my %limits;
2001   bless $self;
2002
2003   $self->{'cmp_name'}           = "ms-sql";
2004   $self->{'data_source'}        = "DBI:ODBC:$database";
2005   if (defined($host) && $host ne "")
2006   {
2007     $self->{'data_source'}      .= ":$host";
2008   }
2009   $self->{'limits'}             = \%limits;
2010   $self->{'blob'}               = "text";
2011   $self->{'text'}               = "text";
2012   $self->{'double_quotes'}      = 1; # Can handle:  'Walker''s'
2013   $self->{'drop_attr'}          = "";
2014   $self->{'transactions'}       = 1; # Transactions enabled
2015
2016   $limits{'max_conditions'}     = 1030; # We get 'Query is too complex'
2017   $limits{'max_columns'}        = 250;  # Max number of columns in table
2018   $limits{'max_tables'}         = 65000;        # Should be big enough
2019   $limits{'max_text_size'}      = 9830;  # Max size with default buffers.
2020   $limits{'query_size'}         = 9830; # Max size with default buffers.
2021   $limits{'max_index'}          = 64; # Max number of keys
2022   $limits{'max_index_parts'}    = 15; # Max segments/key
2023   $limits{'max_column_name'}    = 30; # max table and column name
2024
2025   $limits{'join_optimizer'}     = 1; # Can optimize FROM tables
2026   $limits{'load_data_infile'}   = 0; # Has load data infile
2027   $limits{'lock_tables'}        = 0; # Has lock tables
2028   $limits{'functions'}          = 1; # Has simple functions (+/-)
2029   $limits{'group_functions'}    = 1; # Have group functions
2030   $limits{'group_func_sql_min_str'}     = 1; # Can execute MIN() and MAX() on strings
2031   $limits{'group_distinct_functions'}= 1; # Have count(distinct)
2032   $limits{'select_without_from'}= 1; # Can do 'select 1';
2033   $limits{'multi_drop'}         = 1; # Drop table can take many tables
2034   $limits{'subqueries'}         = 1; # Supports sub-queries.
2035   $limits{'left_outer_join'}    = 1; # Supports left outer joins
2036   $limits{'table_wildcard'}     = 1; # Has SELECT table_name.*
2037   $limits{'having_with_alias'}  = 0; # Can use aliases in HAVING
2038   $limits{'having_with_group'}  = 1; # Can't use group functions in HAVING
2039   $limits{'like_with_column'}   = 1; # Can use column1 LIKE column2
2040   $limits{'order_by_position'}  = 1; # Can use 'ORDER BY 1'
2041   $limits{'group_by_position'}  = 0; # Can use 'GROUP BY 1'
2042   $limits{'alter_table'}        = 1;
2043   $limits{'alter_add_multi_col'}= 0;
2044   $limits{'alter_table_dropcol'}= 0;
2045
2046   $limits{'group_func_extra_std'} = 0; # Have group function std().
2047
2048   $limits{'func_odbc_mod'}      = 0; # Have function mod.
2049   $limits{'func_extra_%'}       = 1; # Has % as alias for mod()
2050   $limits{'func_odbc_floor'}    = 1; # Has func_odbc_floor function
2051   $limits{'func_extra_if'}      = 0; # Have function if.
2052   $limits{'column_alias'}       = 1; # Alias for fields in select statement.
2053   $limits{'NEG'}                = 1; # Supports -id
2054   $limits{'func_extra_in_num'}  = 0; # Has function in
2055   $limits{'unique_index'}       = 1; # Unique index works or not
2056   $limits{'insert_select'}      = 1;
2057   $limits{'working_blobs'}      = 1; # If big varchar/blobs works
2058   $limits{'order_by_unused'}    = 1;
2059   $limits{'working_all_fields'} = 1;
2060   $limits{'multi_distinct'}     = 1; # allows select count(distinct a),count(distinct b)..
2061   return $self;
2062 }
2063
2064 #
2065 # Get the version number of the database
2066 #
2067
2068 sub version
2069 {
2070   my ($self)=@_;
2071   my($sth,@row, $version);
2072   $version='MS SQL server ?';
2073   $dbh=$self->connect();
2074   $sth = $dbh->prepare("SELECT \@\@VERSION") or die $DBI::errstr;
2075   $sth->execute or die $DBI::errstr;
2076   @row = $sth->fetchrow_array;
2077   if ($row[0]) {
2078      @server = split(/\n/,$row[0]);
2079      chomp(@server);
2080      $version= "$server[0]";
2081   }
2082   $sth->finish;
2083   $version .= "/ODBC" if ($self->{'data_source'} =~ /:ODBC:/);
2084   return $version;
2085 }
2086
2087 sub connect
2088 {
2089   my ($self)=@_;
2090   my ($dbh);
2091   $dbh=DBI->connect($self->{'data_source'}, $main::opt_user,
2092                     $main::opt_password,{ PrintError => 0}) ||
2093                       die "Got error: '$DBI::errstr' when connecting to " . $self->{'data_source'} ." with user: '$main::opt_user' password: '$main::opt_password'\n";
2094   return $dbh;
2095 }
2096
2097 #
2098 # Returns a list of statements to create a table
2099 # The field types are in ANSI SQL format.
2100 #
2101
2102 sub create
2103 {
2104   my($self,$table_name,$fields,$index) = @_;
2105   my($query,@queries,$nr);
2106
2107   $query="create table $table_name (";
2108   foreach $field (@$fields)
2109   {
2110     $field =~ s/mediumint/integer/i;
2111     $field =~ s/float\(\d+,\d+\)/float/i;
2112     $field =~ s/double\(\d+,\d+\)/float/i;
2113     $field =~ s/double/float/i;
2114     $field =~ s/integer\(\d+\)/integer/i;
2115     $field =~ s/int\(\d+\)/integer/i;
2116     $field =~ s/smallint\(\d+\)/smallint/i;
2117     $field =~ s/smallinteger/smallint/i;
2118     $field =~ s/tinyint\(\d+\)/tinyint/i;
2119     $field =~ s/tinyinteger/tinyint/i;
2120     $field =~ s/blob/text/i;
2121     $query.= $field . ',';
2122   }
2123   substr($query,-1)=")";                # Remove last ',';
2124   push(@queries,$query);
2125   $nr=0;
2126   foreach $index (@$index)
2127   {
2128     $ext="WITH DISALLOW NULL";
2129     if (($index =~ s/primary key/unique index primary_key/i))
2130     {
2131       $ext="WITH PRIMARY;"
2132     }
2133     if ($index =~ /^unique.*\(([^\(]*)\)$/i)
2134     {
2135       $nr++;
2136       $index="unique index ${table_name}_$nr ($1)";
2137     }
2138     $index =~ /^(.*)\s+(\(.*\))$/;
2139     push(@queries,"create ${1} on $table_name $2");
2140   }
2141   return @queries;
2142 }
2143
2144 #
2145 # Do any conversions to the ANSI SQL query so that the database can handle it
2146 #
2147
2148 sub query {
2149   my($self,$sql) = @_;
2150   return $sql;
2151 }
2152
2153 sub drop_index
2154 {
2155   my ($self,$table,$index) = @_;
2156   return "DROP INDEX $table.$index";
2157 }
2158
2159 #
2160 # Abort if the server has crashed
2161 # return: 0 if ok
2162 #         1 question should be retried
2163 #
2164
2165 sub abort_if_fatal_error
2166 {
2167   return 0;
2168 }
2169
2170 sub small_rollback_segment
2171 {
2172   return 0;
2173 }
2174
2175 sub reconnect_on_errors
2176 {
2177   return 0;
2178 }
2179
2180 sub fix_for_insert
2181 {
2182   my ($self,$cmd) = @_;
2183   return $cmd;
2184 }
2185
2186 #############################################################################
2187 #            Configuration for Sybase
2188 #############################################################################
2189 package db_sybase;
2190
2191 sub new
2192 {
2193   my ($type,$host,$database)= @_;
2194   my $self= {};
2195   my %limits;
2196   bless $self;
2197
2198   $self->{'cmp_name'}           = "sybase";
2199   $self->{'data_source'}        = "DBI:Sybase:database=$database";
2200   if (defined($host) && $host ne "")
2201   {
2202     $self->{'data_source'}      .= ";hostname=$host";
2203   }
2204   $self->{'limits'}             = \%limits;
2205   $self->{'blob'}               = "text";
2206   $self->{'text'}               = "text";
2207   $self->{'double_quotes'}      = 1; # Can handle:  'Walker''s'
2208   $self->{'drop_attr'}          = "";
2209   $self->{'transactions'}       = 1; # Transactions enabled
2210   $self->{"vacuum"}             = 1;
2211
2212   $limits{'max_conditions'}     = 1030; # We get 'Query is too complex'
2213   $limits{'max_columns'}        = 250;  # Max number of columns in table
2214   $limits{'max_tables'}         = 65000;        # Should be big enough
2215   $limits{'max_text_size'}      = 9830;  # Max size with default buffers.
2216   $limits{'query_size'}         = 9830; # Max size with default buffers.
2217   $limits{'max_index'}          = 64; # Max number of keys
2218   $limits{'max_index_parts'}    = 15; # Max segments/key
2219   $limits{'max_column_name'}    = 30; # max table and column name
2220
2221   $limits{'join_optimizer'}     = 1; # Can optimize FROM tables
2222   $limits{'load_data_infile'}   = 0; # Has load data infile
2223   $limits{'lock_tables'}        = 0; # Has lock tables
2224   $limits{'functions'}          = 1; # Has simple functions (+/-)
2225   $limits{'group_functions'}    = 1; # Have group functions
2226   $limits{'group_func_sql_min_str'}     = 1; # Can execute MIN() and MAX() on strings
2227   $limits{'group_distinct_functions'}= 1; # Have count(distinct)
2228   $limits{'select_without_from'}= 1; # Can do 'select 1';
2229   $limits{'multi_drop'}         = 1; # Drop table can take many tables
2230   $limits{'subqueries'}         = 1; # Supports sub-queries.
2231   $limits{'left_outer_join'}    = 1; # Supports left outer joins
2232   $limits{'table_wildcard'}     = 1; # Has SELECT table_name.*
2233   $limits{'having_with_alias'}  = 0; # Can use aliases in HAVING
2234   $limits{'having_with_group'}  = 1; # Can't use group functions in HAVING
2235   $limits{'like_with_column'}   = 1; # Can use column1 LIKE column2
2236   $limits{'order_by_position'}  = 1; # Can use 'ORDER BY 1'
2237   $limits{'group_by_position'}  = 0; # Can use 'GROUP BY 1'
2238   $limits{'alter_table'}        = 1;
2239   $limits{'alter_add_multi_col'}= 0;
2240   $limits{'alter_table_dropcol'}= 0;
2241
2242   $limits{'group_func_extra_std'} = 0; # Have group function std().
2243
2244   $limits{'func_odbc_mod'}      = 0; # Have function mod.
2245   $limits{'func_extra_%'}       = 1; # Has % as alias for mod()
2246   $limits{'func_odbc_floor'}    = 1; # Has func_odbc_floor function
2247   $limits{'func_extra_if'}      = 0; # Have function if.
2248   $limits{'column_alias'}       = 1; # Alias for fields in select statement.
2249   $limits{'NEG'}                = 1; # Supports -id
2250   $limits{'func_extra_in_num'}  = 0; # Has function in
2251   $limits{'unique_index'}       = 1; # Unique index works or not
2252   $limits{'insert_select'}      = 1;
2253   $limits{'working_blobs'}      = 1; # If big varchar/blobs works
2254   $limits{'order_by_unused'}    = 1;
2255   $limits{'working_all_fields'} = 1;
2256   $limits{'multi_distinct'}     = 1; # allows select count(distinct a),count(distinct b)..
2257   return $self;
2258 }
2259
2260 #
2261 # Get the version number of the database
2262 #
2263
2264 sub version
2265 {
2266   my ($self)=@_;
2267   my ($dbh,$sth,$version,@row);
2268
2269   $dbh=$self->connect();
2270   $sth = $dbh->prepare('SELECT @@version') or die $DBI::errstr;
2271   $version="Sybase (unknown)";
2272   if ($sth->execute && (@row = $sth->fetchrow_array))
2273   {
2274     $version=$row[0];
2275   }
2276   $sth->finish;
2277   $dbh->disconnect;
2278   $version .= "/ODBC" if ($self->{'data_source'} =~ /:ODBC:/);
2279   return $version;
2280 }
2281
2282 sub connect
2283 {
2284   my ($self)=@_;
2285   my ($dbh);
2286   $dbh=DBI->connect($self->{'data_source'}, $main::opt_user,
2287                     $main::opt_password,{ PrintError => 0 , AutoCommit => 1}) ||
2288                       die "Got error: '$DBI::errstr' when connecting to " . $self->{'data_source'} ." with user: '$main::opt_user' password: '$main::opt_password'\n";
2289   return $dbh;
2290 }
2291
2292 #
2293 # Returns a list of statements to create a table
2294 # The field types are in ANSI SQL format.
2295 #
2296
2297 sub create
2298 {
2299   my($self,$table_name,$fields,$index) = @_;
2300   my($query,@queries,$nr);
2301
2302   $query="create table $table_name (";
2303   foreach $field (@$fields)
2304   {
2305     $field =~ s/mediumint/integer/i;
2306     $field =~ s/float\(\d+,\d+\)/float/i;
2307     $field =~ s/int\(\d+\)/int/i;
2308     $field =~ s/double/float/i;
2309     $field =~ s/integer\(\d+\)/integer/i;
2310     $field =~ s/smallint\(\d+\)/smallint/i;
2311     $field =~ s/tinyint\(\d+\)/tinyint/i;
2312     $field =~ s/blob/text/i;
2313     $query.= $field . ',';
2314   }
2315   substr($query,-1)=")";                # Remove last ',';
2316   push(@queries,$query);
2317   $nr=0;
2318   foreach $index (@$index)
2319   {
2320 #    $ext="WITH DISALLOW NULL";
2321     if (($index =~ s/primary key/unique index primary_key/i))
2322     {
2323 #      $ext="WITH PRIMARY;"
2324     }
2325     if ($index =~ /^unique.*\(([^\(]*)\)$/i)
2326     {
2327       $nr++;
2328       $index="unique index ${table_name}_$nr ($1)";
2329     }
2330     $index =~ /^(.*)\s+(\(.*\))$/;
2331     push(@queries,"create ${1} on $table_name $2");
2332   }
2333   return @queries;
2334 }
2335
2336 #
2337 # Do any conversions to the ANSI SQL query so that the database can handle it
2338 #
2339
2340 sub query {
2341   my($self,$sql) = @_;
2342   return $sql;
2343 }
2344
2345 sub drop_index
2346 {
2347   my ($self,$table,$index) = @_;
2348   return "DROP INDEX $table.$index";
2349 }
2350
2351 #
2352 # Abort if the server has crashed
2353 # return: 0 if ok
2354 #         1 question should be retried
2355 #
2356
2357 sub abort_if_fatal_error
2358 {
2359   return 0;
2360 }
2361
2362 sub small_rollback_segment
2363 {
2364   return 0;
2365 }
2366
2367 sub reconnect_on_errors
2368 {
2369   return 0;
2370 }
2371
2372 sub fix_for_insert
2373 {
2374   my ($self,$cmd) = @_;
2375   return $cmd;
2376 }
2377
2378 #
2379 # optimize the tables ....
2380 #  WARNING (from walrus)! This sub will work only from DBD:sybase
2381 # driver. Because if we use ODBC we don't know actual database name
2382 # (but DSN name only)
2383 sub vacuum
2384 {
2385   my ($self,$full_vacuum,$dbh_ref)=@_;
2386   my ($loop_time,$end_time,$dbh);
2387
2388   if (defined($full_vacuum))
2389   {
2390     $$dbh_ref->disconnect;  $$dbh_ref= $self->connect();
2391   }
2392   $dbh=$$dbh_ref;
2393   $loop_time=new Benchmark;
2394   my (@tables,$sth,$current_table,$current_base);
2395   $dbh->do("dump tran $database with truncate_only");
2396   $sth=$dbh->prepare("sp_tables" ) or die "prepere";
2397   $sth->execute() or die "execute";
2398   while (@row = $sth->fetchrow_array()) {
2399     $current_table = $row[2];
2400     $current_base = $row[0];
2401     next if ($current_table =~ /^sys/);
2402     push(@tables,$current_table) if ($database == $current_base);
2403    }
2404
2405   $sth->finish();
2406
2407   foreach $table (@tables) {
2408 #    print "$table: \n";
2409     $dbh->do("update statistics $table") or print "Oops!";
2410   }
2411
2412 #  $dbh->do("analyze table ?? compute statistics") || die "Got error: $DBI::errstr when executing 'vacuum'\n";
2413   $end_time=new Benchmark;
2414   print "Time for book-keeping (1): " .
2415   Benchmark::timestr(Benchmark::timediff($end_time, $loop_time),"all") . "\n\n";
2416   $dbh->disconnect;  $$dbh_ref= $self->connect();
2417 }
2418
2419
2420
2421
2422 #############################################################################
2423 #                        Definitions for Adabas
2424 #############################################################################
2425
2426 package db_Adabas;
2427
2428 sub new
2429 {
2430   my ($type,$host,$database)= @_;
2431   my $self= {};
2432   my %limits;
2433   bless $self;
2434
2435   $self->{'cmp_name'}           = "Adabas";
2436   $self->{'data_source'}        = "DBI:Adabas:$database";
2437   $self->{'limits'}             = \%limits;
2438   $self->{'blob'}               = "long";
2439   $self->{'text'}               = "long";
2440   $self->{'double_quotes'}      = 1; # Can handle:  'Walker''s'
2441   $self->{'drop_attr'}          = "";
2442   $self->{'transactions'}       = 1; # Transactions enabled
2443
2444   $limits{'max_conditions'}     = 50; # (Actually not a limit)
2445   $limits{'max_columns'}        = 254;  # Max number of columns in table
2446   $limits{'max_tables'}         = 65000;        # Should be big enough
2447   $limits{'max_text_size'}      = 2000; # Limit for blob test-connect
2448   $limits{'query_size'}         = 65525; # Max size with default buffers.
2449   $limits{'max_index'}          = 16; # Max number of keys
2450   $limits{'max_index_parts'}    = 16; # Max segments/key
2451   $limits{'max_column_name'} = 32; # max table and column name
2452
2453   $limits{'join_optimizer'}     = 1; # Can optimize FROM tables
2454   $limits{'load_data_infile'}   = 0; # Has load data infile
2455   $limits{'lock_tables'}        = 0; # Has lock tables
2456   $limits{'functions'}          = 1; # Has simple functions (+/-)
2457   $limits{'group_functions'}    = 1; # Have group functions
2458   $limits{'group_func_sql_min_str'}     = 1; # Can execute MIN() and MAX() on strings
2459   $limits{'group_distinct_functions'}= 1; # Have count(distinct)
2460   $limits{'select_without_from'}= 0;
2461   $limits{'multi_drop'}         = 0;
2462   $limits{'subqueries'}         = 1;
2463   $limits{'left_outer_join'}    = 0; # This may be fixed in the query module
2464   $limits{'table_wildcard'}     = 1; # Has SELECT table_name.*
2465   $limits{'having_with_alias'}  = 0; # Can use aliases in HAVING
2466   $limits{'having_with_group'}  = 1; # Can't use group functions in HAVING
2467   $limits{'like_with_column'}   = 1; # Can use column1 LIKE column2
2468   $limits{'order_by_position'}  = 1; # Can use 'ORDER BY 1'
2469   $limits{'group_by_position'}  = 1;
2470   $limits{'alter_table'}        = 1;
2471   $limits{'alter_add_multi_col'}= 2; #Have ALTER TABLE t add a int, b int;
2472   $limits{'alter_table_dropcol'}= 1;
2473
2474   $limits{'group_func_extra_std'}       = 0; # Have group function std().
2475
2476   $limits{'func_odbc_mod'}      = 0; # Oracle has problem with mod()
2477   $limits{'func_extra_%'}       = 0; # Has % as alias for mod()
2478   $limits{'func_odbc_floor'}    = 1; # Has func_odbc_floor function
2479   $limits{'func_extra_if'}      = 0; # Have function if.
2480   $limits{'column_alias'}       = 1; # Alias for fields in select statement.
2481   $limits{'NEG'}                = 1; # Supports -id
2482   $limits{'func_extra_in_num'}  = 1; # Has function in
2483   $limits{'unique_index'}       = 1; # Unique index works or not
2484   $limits{'insert_select'}      = 1;
2485   $limits{'working_blobs'}      = 1; # If big varchar/blobs works
2486   $limits{'order_by_unused'}    = 1;
2487   $limits{'working_all_fields'} = 1;
2488   $limits{'multi_distinct'}     = 1; # allows select count(distinct a),count(distinct b)..
2489
2490
2491   return $self;
2492 }
2493
2494 #
2495 # Get the version number of the database
2496 #
2497
2498 sub version
2499 {
2500   my ($self)=@_;
2501   my ($dbh,$sth,$version,@row);
2502
2503   $dbh=$self->connect();
2504   $sth = $dbh->prepare("SELECT KERNEL FROM VERSIONS") or die $DBI::errstr;
2505   $version="Adabas (unknown)";
2506   if ($sth->execute && (@row = $sth->fetchrow_array)
2507       && $row[0] =~ /([\d\.]+)/)
2508   {
2509     $version="Adabas $1";
2510   }
2511   $sth->finish;
2512   $dbh->disconnect;
2513   $version .= "/ODBC" if ($self->{'data_source'} =~ /:ODBC:/);
2514   return $version;
2515 }
2516
2517 sub connect
2518 {
2519   my ($self)=@_;
2520   my ($dbh);
2521   $dbh=DBI->connect($self->{'data_source'}, $main::opt_user,
2522                     $main::opt_password,{ PrintError => 0}) ||
2523                       die "Got error: '$DBI::errstr' when connecting to " . $self->{'data_source'} ." with user: '$main::opt_user' password: '$main::opt_password'\n";
2524   return $dbh;
2525 }
2526
2527 #
2528 # Returns a list of statements to create a table
2529 # The field types are in ANSI SQL format.
2530 #
2531 # If one uses $main::opt_fast then one is allowed to use
2532 # non standard types to get better speed.
2533 #
2534
2535 sub create
2536 {
2537   my($self,$table_name,$fields,$index) = @_;
2538   my($query,@queries,$ind,@keys);
2539
2540   $query="create table $table_name (";
2541   foreach $field (@$fields)
2542   {
2543     $field =~ s/CHARACTER\s+VARYING/VARCHAR/i;
2544     $field =~ s/TINYINT/SMALLINT/i;
2545     $field =~ s/MEDIUMINT/INT/i;
2546     $field =~ s/SMALLINT\s*\(\d+\)/SMALLINT/i;
2547     $field =~ s/INT\s*\(\d+\)/INT/i;
2548     $field =~ s/BLOB/LONG/i;
2549     $field =~ s/INTEGER\s*\(\d+\)/INTEGER/i;
2550     $field =~ s/FLOAT\s*\((\d+),\d+\)/FLOAT\($1\)/i;
2551     $field =~ s/DOUBLE/FLOAT\(38\)/i;
2552     $field =~ s/DOUBLE\s+PRECISION/FLOAT\(38\)/i;
2553     $query.= $field . ',';
2554   }
2555
2556   foreach $ind (@$index)
2557   {
2558     my @index;
2559     if ( $ind =~ /\bKEY\b/i ){
2560       push(@keys,"ALTER TABLE $table_name ADD $ind");
2561     }else{
2562       my @fields = split(' ',$index);
2563       my $query="CREATE INDEX $fields[1] ON $table_name $fields[2]";
2564       push(@index,$query);
2565     }
2566   }
2567   substr($query,-1)=")";                # Remove last ',';
2568   push(@queries,$query,@keys,@index);
2569 #print "query:$query\n";
2570
2571   return @queries;
2572 }
2573
2574 sub insert_file {
2575   my($self,$dbname, $file) = @_;
2576   print "insert an ascii file isn't supported by Oracle (?)\n";
2577   return 0;
2578 }
2579
2580 #
2581 # Do any conversions to the ANSI SQL query so that the database can handle it
2582 #
2583
2584 sub query {
2585   my($self,$sql) = @_;
2586   return $sql;
2587 }
2588
2589 sub drop_index
2590 {
2591   my ($self,$table,$index) = @_;
2592   return "DROP INDEX $index";
2593 }
2594
2595 #
2596 # Abort if the server has crashed
2597 # return: 0 if ok
2598 #         1 question should be retried
2599 #
2600
2601 sub abort_if_fatal_error
2602 {
2603   return 0;
2604 }
2605
2606 sub small_rollback_segment
2607 {
2608   return 0;
2609 }
2610
2611 sub reconnect_on_errors
2612 {
2613   return 0;
2614 }
2615
2616 sub fix_for_insert
2617 {
2618   my ($self,$cmd) = @_;
2619   return $cmd;
2620 }
2621
2622 #############################################################################
2623 #            Configuration for IBM DB2
2624 #############################################################################
2625
2626 package db_db2;
2627
2628 sub new
2629 {
2630   my ($type,$host,$database)= @_;
2631   my $self= {};
2632   my %limits;
2633   bless $self;
2634
2635   $self->{'cmp_name'}           = "DB2";
2636   $self->{'data_source'}        = "DBI:ODBC:$database";
2637   if (defined($host) && $host ne "")
2638   {
2639     $self->{'data_source'}      .= ":$host";
2640   }
2641   $self->{'limits'}             = \%limits;
2642   $self->{'blob'}               = "varchar(255)";
2643   $self->{'text'}               = "varchar(255)";
2644   $self->{'double_quotes'}      = 1; # Can handle:  'Walker''s'
2645   $self->{'drop_attr'}          = "";
2646   $self->{'transactions'}       = 1; # Transactions enabled
2647
2648   $limits{'max_conditions'}     = 418; # We get 'Query is too complex'
2649   $limits{'max_columns'}        = 500;  # Max number of columns in table
2650   $limits{'max_tables'}         = 65000;        # Should be big enough
2651   $limits{'max_text_size'}      = 254;  # Max size with default buffers.
2652   $limits{'query_size'}         = 254; # Max size with default buffers.
2653   $limits{'max_index'}          = 48; # Max number of keys
2654   $limits{'max_index_parts'}    = 15; # Max segments/key
2655   $limits{'max_column_name'}    = 18; # max table and column name
2656
2657   $limits{'join_optimizer'}     = 1; # Can optimize FROM tables
2658   $limits{'load_data_infile'}   = 0; # Has load data infile
2659   $limits{'lock_tables'}        = 0; # Has lock tables
2660   $limits{'functions'}          = 1; # Has simple functions (+/-)
2661   $limits{'group_functions'}    = 1; # Have group functions
2662   $limits{'group_func_sql_min_str'}= 1;
2663   $limits{'group_distinct_functions'}= 1; # Have count(distinct)
2664   $limits{'select_without_from'}= 0; # Can do 'select 1';
2665   $limits{'multi_drop'}         = 0; # Drop table can take many tables
2666   $limits{'subqueries'}         = 1; # Supports sub-queries.
2667   $limits{'left_outer_join'}    = 1; # Supports left outer joins
2668   $limits{'table_wildcard'}     = 1; # Has SELECT table_name.*
2669   $limits{'having_with_alias'}  = 0; # Can use aliases in HAVING
2670   $limits{'having_with_group'}  = 1; # Can't use group functions in HAVING
2671   $limits{'like_with_column'}   = 0; # Can use column1 LIKE column2
2672   $limits{'order_by_position'}  = 1; # Can use 'ORDER BY 1'
2673   $limits{'group_by_position'}  = 0; # Can use 'GROUP BY 1'
2674   $limits{'alter_table'}        = 1;
2675   $limits{'alter_add_multi_col'}= 0;
2676   $limits{'alter_table_dropcol'}= 0;
2677
2678   $limits{'group_func_extra_std'} = 0; # Have group function std().
2679
2680   $limits{'func_odbc_mod'}      = 1; # Have function mod.
2681   $limits{'func_extra_%'}       = 0; # Has % as alias for mod()
2682   $limits{'func_odbc_floor'}    = 1; # Has func_odbc_floor function
2683   $limits{'func_extra_if'}      = 0; # Have function if.
2684   $limits{'column_alias'}       = 1; # Alias for fields in select statement.
2685   $limits{'NEG'}                = 1; # Supports -id
2686   $limits{'func_extra_in_num'}  = 0; # Has function in
2687   $limits{'unique_index'}       = 1; # Unique index works or not
2688   $limits{'insert_select'}      = 1;
2689   $limits{'working_blobs'}      = 1; # If big varchar/blobs works
2690   $limits{'order_by_unused'}    = 1;
2691   $limits{'working_all_fields'} = 1;
2692   $limits{'multi_distinct'}     = 1; # allows select count(distinct a),count(distinct b)..
2693   return $self;
2694 }
2695
2696 #
2697 # Get the version number of the database
2698 #
2699
2700 sub version
2701 {
2702   my ($self)=@_;
2703   return "IBM DB2 5";           #DBI/ODBC can't return the server version
2704 }
2705
2706 sub connect
2707 {
2708   my ($self)=@_;
2709   my ($dbh);
2710   $dbh=DBI->connect($self->{'data_source'}, $main::opt_user, $main::opt_password) ||
2711     die "Got error: '$DBI::errstr' when connecting to " . $self->{'data_source'} ." with user: '$main::opt_user' password: '$main::opt_password'\n";
2712   return $dbh;
2713 }
2714
2715 #
2716 # Returns a list of statements to create a table
2717 # The field types are in ANSI SQL format.
2718 #
2719
2720 sub create
2721 {
2722   my($self,$table_name,$fields,$index) = @_;
2723   my($query,@queries,$nr);
2724
2725   $query="create table $table_name (";
2726   foreach $field (@$fields)
2727   {
2728     $field =~ s/mediumint/integer/i;
2729     $field =~ s/float\(\d+,\d+\)/float/i;
2730     $field =~ s/integer\(\d+\)/integer/i;
2731     $field =~ s/int\(\d+\)/integer/i;
2732     $field =~ s/tinyint\(\d+\)/smallint/i;
2733     $field =~ s/tinyint/smallint/i;
2734     $field =~ s/smallint\(\d+\)/smallint/i;
2735     $field =~ s/smallinteger/smallint/i;
2736     $field =~ s/blob/varchar(256)/i;
2737     $query.= $field . ',';
2738   }
2739   substr($query,-1)=")";                # Remove last ',';
2740   push(@queries,$query);
2741   $nr=0;
2742   foreach $index (@$index)
2743   {
2744     $ext="WITH DISALLOW NULL";
2745     if (($index =~ s/primary key/unique index primary_key/i))
2746     {
2747       $ext="WITH PRIMARY;"
2748     }
2749     if ($index =~ /^unique.*\(([^\(]*)\)$/i)
2750     {
2751       $nr++;
2752       $index="unique index ${table_name}_$nr ($1)";
2753     }
2754     $index =~ /^(.*)\s+(\(.*\))$/;
2755     push(@queries,"create ${1} on $table_name $2");
2756   }
2757   return @queries;
2758 }
2759
2760 #
2761 # Do any conversions to the ANSI SQL query so that the database can handle it
2762 #
2763
2764 sub query {
2765   my($self,$sql) = @_;
2766   return $sql;
2767 }
2768
2769 sub drop_index
2770 {
2771   my ($self,$table,$index) = @_;
2772   return "DROP INDEX $table.$index";
2773 }
2774
2775 #
2776 # Abort if the server has crashed
2777 # return: 0 if ok
2778 #         1 question should be retried
2779 #
2780
2781 sub abort_if_fatal_error
2782 {
2783   return 0;
2784 }
2785
2786 sub small_rollback_segment
2787 {
2788   return 1;
2789 }
2790
2791 sub reconnect_on_errors
2792 {
2793   return 0;
2794 }
2795
2796 sub fix_for_insert
2797 {
2798   my ($self,$cmd) = @_;
2799   return $cmd;
2800 }
2801
2802 #############################################################################
2803 #            Configuration for MIMER
2804 #############################################################################
2805
2806 package db_Mimer;
2807
2808 sub new
2809 {
2810   my ($type,$host,$database)= @_;
2811   my $self= {};
2812   my %limits;
2813   bless $self;
2814
2815   $self->{'cmp_name'}           = "mimer";
2816   $self->{'data_source'}        = "DBI:mimer:$database:$host";
2817   $self->{'limits'}             = \%limits;
2818   $self->{'blob'}               = "binary varying(15000)";
2819   $self->{'text'}               = "character varying(15000)";
2820   $self->{'double_quotes'}      = 1; # Can handle:  'Walker''s'
2821   $self->{'drop_attr'}          = "";
2822   $self->{'transactions'}       = 1; # Transactions enabled
2823   $self->{'char_null'}          = "cast(NULL as char(1))";
2824   $self->{'numeric_null'}       = "cast(NULL as int)";
2825
2826   $limits{'max_conditions'}     = 9999; # (Actually not a limit)
2827   $limits{'max_columns'}        = 252;  # Max number of columns in table
2828   $limits{'max_tables'}         = 65000;        # Should be big enough
2829   $limits{'max_text_size'}      = 15000; # Max size with default buffers.
2830   $limits{'query_size'}         = 1000000; # Max size with default buffers.
2831   $limits{'max_index'}          = 32; # Max number of keys
2832   $limits{'max_index_parts'}    = 16; # Max segments/key
2833   $limits{'max_column_name'}    = 128; # max table and column name
2834
2835   $limits{'join_optimizer'}     = 1; # Can optimize FROM tables
2836   $limits{'load_data_infile'}   = 1; # Has load data infile
2837   $limits{'lock_tables'}        = 0; # Has lock tables
2838   $limits{'functions'}          = 1; # Has simple functions (+/-)
2839   $limits{'group_functions'}    = 1; # Have group functions
2840   $limits{'group_func_sql_min_str'} = 1; # Can execute MIN() and MAX() on strings
2841   $limits{'group_distinct_functions'}= 1; # Have count(distinct)
2842   $limits{'select_without_from'}= 0; # Cannot do 'select 1';
2843   $limits{'multi_drop'}         = 0; # Drop table cannot take many tables
2844   $limits{'subqueries'}         = 1; # Supports sub-queries.
2845   $limits{'left_outer_join'}    = 1; # Supports left outer joins
2846   $limits{'table_wildcard'}     = 1; # Has SELECT table_name.*
2847   $limits{'having_with_alias'}  = 0; # Can use aliases in HAVING
2848   $limits{'having_with_group'}  = 1; # Can use group functions in HAVING
2849   $limits{'like_with_column'}   = 1; # Can use column1 LIKE column2
2850   $limits{'order_by_position'}  = 1; # Can use 'ORDER BY 1'
2851   $limits{'group_by_position'}  = 0; # Cannot use 'GROUP BY 1'
2852   $limits{'alter_table'}        = 1; # Have ALTER TABLE
2853   $limits{'alter_add_multi_col'}= 0; # Have ALTER TABLE t add a int,add b int;
2854   $limits{'alter_table_dropcol'}= 1; # Have ALTER TABLE DROP column
2855   $limits{'insert_multi_value'} = 0; # Does not have INSERT ... values (1,2),(3,4)
2856   $limits{'multi_distinct'}     = 0; # Does not allow select count(distinct a),count(distinct b)..
2857
2858   $limits{'group_func_extra_std'} = 0; # Does not have group function std().
2859
2860   $limits{'func_odbc_mod'}      = 1; # Have function mod.
2861   $limits{'func_extra_%'}       = 0; # Does not have % as alias for mod()
2862   $limits{'func_odbc_floor'}    = 1; # Has func_odbc_floor function
2863   $limits{'func_extra_if'}      = 0; # Does not have function if.
2864   $limits{'column_alias'}       = 1; # Alias for fields in select statement.
2865   $limits{'NEG'}                = 1; # Supports -id
2866   $limits{'func_extra_in_num'}  = 1; # Has function in
2867   $limits{'limit'}              = 0; # Does not support the limit attribute
2868   $limits{'unique_index'}       = 1; # Unique index works or not
2869   $limits{'insert_select'}      = 1;
2870   $limits{'working_blobs'}      = 1; # If big varchar/blobs works
2871   $limits{'order_by_unused'}    = 0;
2872   $limits{'working_all_fields'} = 1;
2873
2874   return $self;
2875 }
2876
2877 #
2878 # Get the version number of the database
2879 #
2880
2881 sub version
2882 {
2883   my ($self)=@_;
2884   my ($dbh,$sth,$version,@row);
2885
2886   $dbh=$self->connect();
2887 #
2888 #  Pick up SQLGetInfo option SQL_DBMS_VER (18)
2889 #
2890   $version = $dbh->func(18, GetInfo);
2891   $dbh->disconnect;
2892   $version .= "/ODBC" if ($self->{'data_source'} =~ /:ODBC:/);
2893   return $version;
2894 }
2895
2896 #
2897 # Connection with optional disabling of logging
2898 #
2899
2900 sub connect
2901 {
2902   my ($self)=@_;
2903   my ($dbh);
2904   $dbh=DBI->connect($self->{'data_source'}, $main::opt_user,
2905                     $main::opt_password,{ PrintError => 0}) ||
2906                       die "Got error: '$DBI::errstr' when connecting to " . $self->{'data_source'} ." with user: '$main::opt_user' password: '$main::opt_password'\n";
2907
2908   $dbh->do("SET OPTION LOG_OFF=1,UPDATE_LOG=0");
2909   return $dbh;
2910 }
2911
2912 #
2913 # Returns a list of statements to create a table
2914 # The field types are in ANSI SQL format.
2915 #
2916 # If one uses $main::opt_fast then one is allowed to use
2917 # non standard types to get better speed.
2918 #
2919
2920 sub create
2921 {
2922   my($self,$table_name,$fields,$index,$options) = @_;
2923   my($query,@queries,@indexes);
2924
2925   $query="create table $table_name (";
2926   foreach $field (@$fields)
2927   {
2928 #    $field =~ s/ decimal/ double(10,2)/i;
2929 #    $field =~ s/ big_decimal/ double(10,2)/i;
2930     $field =~ s/ double/ double precision/i;
2931     $field =~ s/ tinyint\(.*\)/ smallint/i;
2932     $field =~ s/ smallint\(.*\)/ smallint/i;
2933     $field =~ s/ mediumint/ integer/i;
2934     $field =~ s/ float\(.*\)/ float/i;
2935 #    $field =~ s/ date/ int/i;          # Because of tcp ?
2936     $query.= $field . ',';
2937   }
2938   foreach $index (@$index)
2939   {
2940     if ( $index =~ /\bINDEX\b/i )
2941     {
2942       my @fields = split(' ',$index);
2943       my $query="CREATE INDEX $fields[1] ON $table_name $fields[2]";
2944       push(@indexes,$query);
2945
2946     } else {
2947       $query.= $index . ',';
2948     }
2949   }
2950   substr($query,-1)=")";                # Remove last ',';
2951   $query.=" $options" if (defined($options));
2952   push(@queries,$query,@indexes);
2953   return @queries;
2954 }
2955
2956 sub insert_file {
2957   my($self,$dbname, $file) = @_;
2958   print "insert of an ascii file isn't supported by Mimer\n";
2959   return 0;
2960 }
2961
2962 #
2963 # Do any conversions to the ANSI SQL query so that the database can handle it
2964 #
2965
2966 sub query {
2967   my($self,$sql) = @_;
2968   return $sql;
2969 }
2970
2971 sub drop_index {
2972   my ($self,$table,$index) = @_;
2973   return "DROP INDEX $index";
2974 }
2975
2976 #
2977 # Abort if the server has crashed
2978 # return: 0 if ok
2979 #         1 question should be retried
2980 #
2981
2982 sub abort_if_fatal_error
2983 {
2984   return 1 if ($DBI::errstr =~ /Table locked by another cursor/);
2985   return 0;
2986 }
2987
2988 sub small_rollback_segment
2989 {
2990   return 0;
2991 }
2992
2993 sub reconnect_on_errors
2994 {
2995   return 0;
2996 }
2997
2998 sub fix_for_insert
2999 {
3000   my ($self,$cmd) = @_;
3001   return $cmd;
3002 }
3003
3004 #############################################################################
3005 #            Configuration for InterBase
3006 #############################################################################
3007
3008 package db_interbase;
3009
3010 sub new
3011 {
3012   my ($type,$host,$database)= @_;
3013   my $self= {};
3014   my %limits;
3015   bless $self;
3016
3017   $self->{'cmp_name'}           = "interbase";
3018   $self->{'data_source'}        = "DBI:InterBase:database=$database;ib_dialect=3";
3019   $self->{'limits'}             = \%limits;
3020   $self->{'blob'}               = "blob";
3021   $self->{'text'}               = "";
3022   $self->{'double_quotes'}      = 1; # Can handle:  'Walker''s'
3023   $self->{'drop_attr'}          = "";
3024   $self->{'transactions'}       = 1; # Transactions enabled
3025   $self->{'char_null'}          = "";
3026   $self->{'numeric_null'}       = "";
3027
3028   $limits{'max_conditions'}     = 9999; # (Actually not a limit)
3029   $limits{'max_columns'}        = 252;  # Max number of columns in table
3030   $limits{'max_tables'}         = 65000;        # Should be big enough
3031   $limits{'max_text_size'}      = 15000; # Max size with default buffers.
3032   $limits{'query_size'}         = 1000000; # Max size with default buffers.
3033   $limits{'max_index'}          = 65000; # Max number of keys
3034   $limits{'max_index_parts'}    = 8; # Max segments/key
3035   $limits{'max_column_name'}    = 128; # max table and column name
3036
3037   $limits{'join_optimizer'}     = 1; # Can optimize FROM tables
3038   $limits{'load_data_infile'}   = 0; # Has load data infile
3039   $limits{'lock_tables'}        = 0; # Has lock tables
3040   $limits{'functions'}          = 1; # Has simple functions (+/-)
3041   $limits{'group_functions'}    = 1; # Have group functions
3042   $limits{'group_func_sql_min_str'} = 1; # Can execute MIN() and MAX() on strings
3043   $limits{'group_distinct_functions'}= 1; # Have count(distinct)
3044   $limits{'select_without_from'}= 0; # Cannot do 'select 1';
3045   $limits{'multi_drop'}         = 0; # Drop table cannot take many tables
3046   $limits{'subqueries'}         = 1; # Supports sub-queries.
3047   $limits{'left_outer_join'}    = 1; # Supports left outer joins
3048   $limits{'table_wildcard'}     = 1; # Has SELECT table_name.*
3049   $limits{'having_with_alias'}  = 0; # Can use aliases in HAVING
3050   $limits{'having_with_group'}  = 1; # Can use group functions in HAVING
3051   $limits{'like_with_column'}   = 0; # Can use column1 LIKE column2
3052   $limits{'order_by_position'}  = 1; # Can use 'ORDER BY 1'
3053   $limits{'group_by_position'}  = 0; # Cannot use 'GROUP BY 1'
3054   $limits{'alter_table'}        = 1; # Have ALTER TABLE
3055   $limits{'alter_add_multi_col'}= 1; # Have ALTER TABLE t add a int,add b int;
3056   $limits{'alter_table_dropcol'}= 1; # Have ALTER TABLE DROP column
3057   $limits{'insert_multi_value'} = 0; # Does not have INSERT ... values (1,2),(3,4)
3058
3059   $limits{'group_func_extra_std'} = 0; # Does not have group function std().
3060
3061   $limits{'func_odbc_mod'}      = 0; # Have function mod.
3062   $limits{'func_extra_%'}       = 0; # Does not have % as alias for mod()
3063   $limits{'func_odbc_floor'}    = 0; # Has func_odbc_floor function
3064   $limits{'func_extra_if'}      = 0; # Does not have function if.
3065   $limits{'column_alias'}       = 1; # Alias for fields in select statement.
3066   $limits{'NEG'}                = 0; # Supports -id
3067   $limits{'func_extra_in_num'}  = 0; # Has function in
3068   $limits{'limit'}              = 0; # Does not support the limit attribute
3069   $limits{'working_blobs'}      = 1; # If big varchar/blobs works
3070   $limits{'order_by_unused'}    = 1;
3071   $limits{'working_all_fields'} = 1;
3072   $limits{'multi_distinct'}     = 1; # allows select count(distinct a),count(distinct b)..
3073
3074   return $self;
3075 }
3076
3077 #
3078 # Get the version number of the database
3079 #
3080
3081 sub version
3082 {
3083   my ($self)=@_;
3084   my ($dbh,$version);
3085
3086   $version='Interbase ?';
3087
3088   $dbh=$self->connect();
3089   eval { $version =   $dbh->func('version','ib_database_info')->{'version'}; };
3090   $dbh->disconnect;
3091   $version .= "/ODBC" if ($self->{'data_source'} =~ /:ODBC:/);
3092   return $version;
3093 }
3094
3095 #
3096 # Connection with optional disabling of logging
3097 #
3098
3099 sub connect
3100 {
3101   my ($self)=@_;
3102   my ($dbh);
3103   $dbh=DBI->connect($self->{'data_source'}, $main::opt_user,
3104                     $main::opt_password,{ PrintError => 0, AutoCommit => 1}) ||
3105                       die "Got error: '$DBI::errstr' when connecting to " . $self->{'data_source'} ." with user: '$main::opt_user' password: '$main::opt_password'\n";
3106
3107   return $dbh;
3108 }
3109
3110 #
3111 # Returns a list of statements to create a table
3112 # The field types are in ANSI SQL format.
3113 #
3114 # If one uses $main::opt_fast then one is allowed to use
3115 # non standard types to get better speed.
3116 #
3117
3118 sub create
3119 {
3120   my($self,$table_name,$fields,$index,$options) = @_;
3121   my($query,@queries,@keys,@indexes);
3122
3123   $query="create table $table_name (";
3124   foreach $field (@$fields)
3125   {
3126 #    $field =~ s/ big_decimal/ decimal/i;
3127     $field =~ s/ double/ double precision/i;
3128     $field =~ s/ tinyint/ smallint/i;
3129     $field =~ s/ mediumint/ integer/i;
3130     $field =~ s/\bint\b/integer/i;
3131     $field =~ s/ float\(\d,\d\)/ float/i;
3132     $field =~ s/ smallint\(\d\)/ smallint/i;
3133     $field =~ s/ integer\(\d\)/ integer/i;
3134     $query.= $field . ',';
3135   }
3136   foreach $ind (@$index)
3137   {
3138     if ( $ind =~ /(\bKEY\b)|(\bUNIQUE\b)/i ){
3139       push(@keys,"ALTER TABLE $table_name ADD $ind");
3140     }else{
3141       my @fields = split(' ',$ind);
3142       my $query="CREATE INDEX $fields[1] ON $table_name $fields[2]";
3143       push(@indexes,$query);
3144     }
3145   }
3146   substr($query,-1)=")";                # Remove last ',';
3147   $query.=" $options" if (defined($options));
3148   push(@queries,$query,@keys,@indexes);
3149   return @queries;
3150 }
3151
3152 sub insert_file {
3153   my($self,$dbname, $file) = @_;
3154   print "insert of an ascii file isn't supported by InterBase\n";
3155   return 0;
3156 }
3157
3158 #
3159 # Do any conversions to the ANSI SQL query so that the database can handle it
3160 #
3161
3162 sub query {
3163   my($self,$sql) = @_;
3164   return $sql;
3165 }
3166
3167 sub drop_index {
3168   my ($self,$table,$index) = @_;
3169   return "DROP INDEX $index";
3170 }
3171
3172 #
3173 # Abort if the server has crashed
3174 # return: 0 if ok
3175 #         1 question should be retried
3176 #
3177
3178 sub abort_if_fatal_error
3179 {
3180   return 1 if ($DBI::errstr =~ /Table locked by another cursor/);
3181   return 0;
3182 }
3183
3184 sub small_rollback_segment
3185 {
3186   return 1;
3187 }
3188
3189 sub reconnect_on_errors
3190 {
3191   return 1;
3192 }
3193
3194 sub fix_for_insert
3195 {
3196   my ($self,$cmd) = @_;
3197   return $cmd;
3198 }
3199
3200 #############################################################################
3201 #            Configuration for FrontBase
3202 #############################################################################
3203
3204 package db_FrontBase;
3205
3206 sub new
3207 {
3208   my ($type,$host,$database)= @_;
3209   my $self= {};
3210   my %limits;
3211   bless $self;
3212
3213   $self->{'cmp_name'}           = "FrontBase";
3214   $self->{'data_source'}        = "DBI:FB:dbname=$database;host=$host";
3215   $self->{'limits'}             = \%limits;
3216   $self->{'blob'}               = "varchar(8000000)";
3217   $self->{'text'}               = "varchar(8000000)";
3218   $self->{'double_quotes'}      = 1; # Can handle:  'Walker''s'
3219   $self->{'drop_attr'}          = ' restrict';
3220   $self->{'transactions'}       = 1; # Transactions enabled
3221   $self->{'error_on_execute_means_zero_rows'}=1;
3222
3223   $limits{'max_conditions'}     = 5427; # (Actually not a limit)
3224   # The following should be 8192, but is smaller because Frontbase crashes..
3225   $limits{'max_columns'}        = 150;  # Max number of columns in table
3226   $limits{'max_tables'}         = 5000; # 10000 crashed FrontBase
3227   $limits{'max_text_size'}      = 65000; # Max size with default buffers.
3228   $limits{'query_size'}         = 8000000; # Max size with default buffers.
3229   $limits{'max_index'}          = 38; # Max number of keys
3230   $limits{'max_index_parts'}    = 20; # Max segments/key
3231   $limits{'max_column_name'}    = 128; # max table and column name
3232
3233   $limits{'join_optimizer'}     = 1; # Can optimize FROM tables
3234   $limits{'load_data_infile'}   = 1; # Has load data infile
3235   $limits{'lock_tables'}        = 0; # Has lock tables
3236   $limits{'functions'}          = 1; # Has simple functions (+/-)
3237   $limits{'group_functions'}    = 1; # Have group functions
3238   $limits{'group_distinct_functions'}= 0; # Have count(distinct)
3239   $limits{'select_without_from'}= 0;
3240   $limits{'multi_drop'}         = 0; # Drop table cannot take many tables
3241   $limits{'subqueries'}         = 1; # Supports sub-queries.
3242   $limits{'left_outer_join'}    = 1; # Supports left outer joins
3243   $limits{'table_wildcard'}     = 1; # Has SELECT table_name.*
3244   $limits{'having_with_alias'}  = 0; # Can use aliases in HAVING
3245   $limits{'having_with_group'}  = 0; # Can use group functions in HAVING
3246   $limits{'like_with_column'}   = 1; # Can use column1 LIKE column2
3247   $limits{'order_by_position'}  = 1; # Can use 'ORDER BY 1'
3248   $limits{'group_by_position'}  = 0; # Use of 'GROUP BY 1'
3249   $limits{'alter_table'}        = 1; # Have ALTER TABLE
3250   $limits{'alter_add_multi_col'}= 0; # Have ALTER TABLE t add a int,add b int;
3251   $limits{'alter_table_dropcol'}= 0; # Have ALTER TABLE DROP column
3252   $limits{'insert_multi_value'} = 1;
3253
3254   $limits{'group_func_extra_std'} = 0; # Does not have group function std().
3255
3256   $limits{'func_odbc_mod'}      = 0; # Have function mod.
3257   $limits{'func_extra_%'}       = 0; # Does not have % as alias for mod()
3258   $limits{'func_odbc_floor'}    = 0; # Has func_odbc_floor function
3259   $limits{'func_extra_if'}      = 0; # Does not have function if.
3260   $limits{'column_alias'}       = 1; # Alias for fields in select statement.
3261   $limits{'NEG'}                = 1; # Supports -id
3262   $limits{'func_extra_in_num'}  = 0; # Has function in
3263   $limits{'limit'}              = 0; # Does not support the limit attribute
3264   $limits{'insert_select'}      = 0;
3265   $limits{'order_by_unused'}    = 0;
3266
3267   # We don't get an error for duplicate row in 'test-insert'
3268   $limits{'unique_index'}       = 0; # Unique index works or not
3269   # We can't use a blob as a normal string (we got a wierd error)
3270   $limits{'working_blobs'}      = 0;
3271   # 'select min(region),max(region) from bench1' kills the server after a while
3272   $limits{'group_func_sql_min_str'} = 0;
3273   # If you do select f1,f2,f3...f200 from table, Frontbase dies.
3274   $limits{'working_all_fields'} = 0;
3275   $limits{'multi_distinct'}     = 1; # allows select count(distinct a),count(distinct b)..
3276
3277   return $self;
3278 }
3279
3280 #
3281 # Get the version number of the database
3282 #
3283
3284 sub version
3285 {
3286   my ($self)=@_;
3287   my ($dbh,$sth,$version,@row);
3288
3289 #  $dbh=$self->connect();
3290 #
3291 #  Pick up SQLGetInfo option SQL_DBMS_VER (18)
3292 #
3293   #$version = $dbh->func(18, GetInfo);
3294   $version="FrontBase 3.3";
3295 #  $dbh->disconnect;
3296   $version .= "/ODBC" if ($self->{'data_source'} =~ /:ODBC:/);
3297   return $version;
3298 }
3299
3300 #
3301 # Connection with optional disabling of logging
3302 #
3303
3304 sub connect
3305 {
3306   my ($self)=@_;
3307   my ($dbh);
3308   $dbh=DBI->connect($self->{'data_source'},
3309                     $main::opt_user,
3310                     $main::opt_password,
3311                     { PrintError => 0 ,
3312                       'fb_host'=>$main::opt_host
3313                     }) ||
3314                       die "Got error: '$DBI::errstr' when connecting to " . $self->{'data_source'} ." with user: '$main::opt_user' password: '$main::opt_password'\n";
3315   $db->{AutoCommit}=1;
3316   # $dbh->do("SET OPTION LOG_OFF=1,UPDATE_LOG=0");
3317   return $dbh;
3318 }
3319
3320 #
3321 # Returns a list of statements to create a table
3322 # The field types are in ANSI SQL format.
3323 #
3324 # If one uses $main::opt_fast then one is allowed to use
3325 # non standard types to get better speed.
3326 #
3327
3328 sub create
3329 {
3330   my($self,$table_name,$fields,$index,$options) = @_;
3331   my($query,@queries,@indexes,@keys);
3332
3333   $query="create table $table_name (";
3334   foreach $field (@$fields)
3335   {
3336     $field =~ s/ blob/ varchar(32000)/i;
3337     $field =~ s/ big_decimal/ float/i;
3338     $field =~ s/ double/ float/i;
3339     $field =~ s/ tinyint/ smallint/i;
3340     $field =~ s/ mediumint/ int/i;
3341     $field =~ s/ integer/ int/i;
3342     $field =~ s/ float\(\d,\d\)/ float/i;
3343     $field =~ s/ smallint\(\d\)/ smallint/i;
3344     $field =~ s/ int\(\d\)/ int/i;
3345     $query.= $field . ',';
3346   }
3347   foreach $ind (@$index)
3348   {
3349 #    my @index;
3350     if ( $ind =~ /(\bKEY\b)|(\bUNIQUE\b)/i ){
3351       push(@keys,"ALTER TABLE $table_name ADD $ind");
3352     }else{
3353       my @fields = split(' ',$ind);
3354       my $query="CREATE INDEX $fields[1] ON $table_name $fields[2]";
3355       push(@indexes,$query);
3356     }
3357   }
3358   substr($query,-1)=")";                # Remove last ',';
3359   $query.=" $options" if (defined($options));
3360   push(@queries,$query,@keys,@indexes);
3361   return @queries;
3362 }
3363
3364 sub insert_file {
3365   my($self,$dbname, $file) = @_;
3366   print "insert of an ascii file isn't supported by InterBase\n";
3367   return 0;
3368 }
3369
3370 #
3371 # Do any conversions to the ANSI SQL query so that the database can handle it
3372 #
3373
3374 sub query {
3375   my($self,$sql) = @_;
3376   return $sql;
3377 }
3378
3379 sub drop_index {
3380   my ($self,$table,$index) = @_;
3381   return "DROP INDEX $index";
3382 }
3383
3384 #
3385 # Abort if the server has crashed
3386 # return: 0 if ok
3387 #         1 question should be retried
3388 #
3389
3390 sub abort_if_fatal_error
3391 {
3392   return 0 if ($DBI::errstr =~ /No raw data handle/);
3393   return 1;
3394 }
3395
3396 sub small_rollback_segment
3397 {
3398   return 0;
3399 }
3400
3401 sub reconnect_on_errors
3402 {
3403   return 1;
3404 }
3405
3406 sub fix_for_insert
3407 {
3408   my ($self,$cmd) = @_;
3409   return $cmd;
3410 }
3411
3412 #############################################################################
3413 #            Configuration for SAPDB
3414 #############################################################################
3415
3416 package db_sapdb;
3417
3418 sub new
3419 {
3420   my ($type,$host,$database)= @_;
3421   my $self= {};
3422   my %limits;
3423   bless $self;
3424
3425   $self->{'cmp_name'}           = "sapdb";
3426   $self->{'data_source'}        = "DBI:SAP_DB:$database";
3427   $self->{'limits'}             = \%limits;
3428   $self->{'blob'}               = "LONG"; # *
3429   $self->{'text'}               = "LONG"; # *
3430   $self->{'double_quotes'}      = 1; # Can handle:  'Walker''s'
3431   $self->{'drop_attr'}          = "";
3432   $self->{'transactions'}       = 1; # Transactions enabled *
3433   $self->{'char_null'}          = "";
3434   $self->{'numeric_null'}       = "";
3435
3436   $limits{'max_conditions'}     = 9999; # (Actually not a limit) *
3437   $limits{'max_columns'}        = 1023; # Max number of columns in table *
3438   $limits{'max_tables'}         = 65000;        # Should be big enough * unlimited actually
3439   $limits{'max_text_size'}      = 15000; # Max size with default buffers.
3440   $limits{'query_size'}         = 64*1024; # Max size with default buffers. *64 kb by default. May be set by system variable
3441   $limits{'max_index'}          = 510; # Max number of keys *
3442   $limits{'max_index_parts'}    = 16; # Max segments/key *
3443   $limits{'max_column_name'}    = 32; # max table and column name *
3444
3445   $limits{'join_optimizer'}     = 1; # Can optimize FROM tables *
3446   $limits{'load_data_infile'}   = 0; # Has load data infile *
3447   $limits{'lock_tables'}        = 1; # Has lock tables
3448   $limits{'functions'}          = 1; # Has simple functions (+/-) *
3449   $limits{'group_functions'}    = 1; # Have group functions *
3450   $limits{'group_func_sql_min_str'} = 1; # Can execute MIN() and MAX() on strings *
3451   $limits{'group_distinct_functions'}= 1; # Have count(distinct)  *
3452   $limits{'select_without_from'}= 0; # Cannot do 'select 1';  *
3453   $limits{'multi_drop'}         = 0; # Drop table cannot take many tables *
3454   $limits{'subqueries'}         = 1; # Supports sub-queries. *
3455   $limits{'left_outer_join'}    = 1; # Supports left outer joins *
3456   $limits{'table_wildcard'}     = 1; # Has SELECT table_name.*
3457   $limits{'having_with_alias'}  = 0; # Can use aliases in HAVING *
3458   $limits{'having_with_group'}  = 1; # Can use group functions in HAVING *
3459   $limits{'like_with_column'}   = 1; # Can use column1 LIKE column2 *
3460   $limits{'order_by_position'}  = 1; # Can use 'ORDER BY 1' *
3461   $limits{'group_by_position'}  = 0; # Cannot use 'GROUP BY 1' *
3462   $limits{'alter_table'}        = 1; # Have ALTER TABLE *
3463   $limits{'alter_add_multi_col'}= 1; # Have ALTER TABLE t add a int,add b int; *
3464   $limits{'alter_table_dropcol'}= 1; # Have ALTER TABLE DROP column  *
3465   $limits{'insert_multi_value'} = 0; # INSERT ... values (1,2),(3,4) *
3466
3467   $limits{'group_func_extra_std'} = 0; # Does not have group function std().
3468
3469   $limits{'func_odbc_mod'}      = 0; # Have function mod. *
3470   $limits{'func_extra_%'}       = 0; # Does not have % as alias for mod() *
3471   $limits{'func_odbc_floor'}    = 1; # Has func_odbc_floor function *
3472   $limits{'func_extra_if'}      = 0; # Does not have function if. *
3473   $limits{'column_alias'}       = 1; # Alias for fields in select statement. *
3474   $limits{'NEG'}                = 1; # Supports -id *
3475   $limits{'func_extra_in_num'}  = 0; # Has function in *
3476   $limits{'limit'}              = 0; # Does not support the limit attribute *
3477   $limits{'working_blobs'}      = 1; # If big varchar/blobs works *
3478   $limits{'order_by_unused'}    = 1; #
3479   $limits{'working_all_fields'} = 1; #
3480   $limits{'multi_distinct'}     = 1; # allows select count(distinct a),count(distinct b)..
3481
3482
3483   return $self;
3484 }
3485
3486 #
3487 # Get the version number of the database
3488 #
3489
3490 sub version
3491 {
3492   my ($self)=@_;
3493   my ($dbh,$sth,$version,@row);
3494
3495   $dbh=$self->connect();
3496   $sth = $dbh->prepare("SELECT KERNEL FROM VERSIONS") or die $DBI::errstr;
3497   $version="SAP DB (unknown)";
3498   if ($sth->execute && (@row = $sth->fetchrow_array)
3499       && $row[0] =~ /([\d\.]+)/)
3500   {
3501     $version=$row[0];
3502     $version =~ s/KERNEL/SAP DB/i;
3503   }
3504   $sth->finish;
3505   $dbh->disconnect;
3506   $version .= "/ODBC" if ($self->{'data_source'} =~ /:ODBC:/);
3507   return $version;
3508 }
3509
3510 #
3511 # Connection with optional disabling of logging
3512 #
3513
3514 sub connect
3515 {
3516   my ($self)=@_;
3517   my ($dbh);
3518   $dbh=DBI->connect($self->{'data_source'}, $main::opt_user,
3519                     $main::opt_password,{ PrintError => 0, AutoCommit => 1}) ||
3520                       die "Got error: '$DBI::errstr' when connecting to " . $self->{'data_source'} ." with user: '$main::opt_user' password: '$main::opt_password'\n";
3521
3522   return $dbh;
3523 }
3524
3525 #
3526 # Returns a list of statements to create a table
3527 # The field types are in ANSI SQL format.
3528 #
3529
3530 sub create
3531 {
3532   my($self,$table_name,$fields,$index,$options) = @_;
3533   my($query,@queries,$nr);
3534   my @index;
3535   my @keys;
3536
3537   $query="create table $table_name (";
3538   foreach $field (@$fields)
3539   {
3540     $field =~ s/\bmediumint\b/int/i;
3541     $field =~ s/\btinyint\b/int/i;
3542     $field =~ s/ int\(\d\)/ int/i;
3543     $field =~ s/BLOB/LONG/i;
3544     $field =~ s/INTEGER\s*\(\d+\)/INTEGER/i;
3545     $field =~ s/SMALLINT\s*\(\d+\)/SMALLINT/i;
3546     $field =~ s/FLOAT\s*\((\d+),\d+\)/FLOAT\($1\)/i;
3547     $field =~ s/DOUBLE/FLOAT\(38\)/i;
3548     $field =~ s/DOUBLE\s+PRECISION/FLOAT\(38\)/i;
3549     $query.= $field . ',';
3550   }
3551   $nr=0;
3552   foreach $ind (@$index)
3553   {
3554     if ( $ind =~ /\bKEY\b/i ){
3555       push(@keys,"ALTER TABLE $table_name ADD $ind");
3556     } elsif ($ind =~ /^unique.*\(([^\(]*)\)$/i)  {
3557       $nr++;
3558       my $query="create unique index ${table_name}_$nr on $table_name ($1)";
3559       push(@index,$query);
3560     }else{
3561       my @fields = split(' ',$ind);
3562       my $query="CREATE INDEX $fields[1] ON $table_name $fields[2]";
3563       push(@index,$query);
3564     }
3565   }
3566   substr($query,-1)=")";                # Remove last ',';
3567   $query.=" $options" if (defined($options));
3568   push(@queries,$query);
3569   push(@queries,@keys);
3570   push(@queries,@index);
3571   return @queries;
3572 }
3573
3574 sub insert_file {
3575   my($self,$dbname, $file) = @_;
3576   print "insert of an ascii file isn't supported by SAPDB\n";
3577   return 0;
3578 }
3579
3580 #
3581 # Do any conversions to the ANSI SQL query so that the database can handle it
3582 #
3583
3584 sub query {
3585   my($self,$sql) = @_;
3586   return $sql;
3587 }
3588
3589 sub drop_index {
3590   my ($self,$table,$index) = @_;
3591   return "DROP INDEX $index";
3592 }
3593
3594 #
3595 # Abort if the server has crashed
3596 # return: 0 if ok
3597 #         1 question should be retried
3598 #
3599
3600 sub abort_if_fatal_error
3601 {
3602   return 0;
3603 }
3604
3605 sub small_rollback_segment
3606 {
3607   return 0;
3608 }
3609
3610 sub reconnect_on_errors
3611 {
3612   return 0;
3613 }
3614
3615 sub fix_for_insert
3616 {
3617   my ($self,$cmd) = @_;
3618   return $cmd;
3619 }
3620
3621 1;