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