|
2324.2.1
by patrick crews
Initial work for sql-bench mode. Added sql-bench to the tree. Test script for running entire suite added |
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; |