« back to all changes in this revision
Viewing changes to tests/sql-bench/crash-me
- browse files at revision 2324.2.1
- compare with another revision
- compare with revision 1099.4.115
- download diff
- download tarball
- view history from revision 2324.2.1
- Committer: patrick crews
- Date: 2011-06-07 18:45:15 UTC
- mto: This revision was merged to the branch mainline in revision 2329.
- Revision ID: gleebix@gmail.com-20110607184515-vwzakr741grhr2yl
Initial work for sql-bench mode. Added sql-bench to the tree. Test script for running entire suite added
- files added:
- tests/lib/sqlbench
- tests/sql-bench
- tests/sql-bench/Comments
- tests/sql-bench/Data
- tests/sql-bench/Data/ATIS
- tests/sql-bench/Data/Wisconsin
- tests/sql-bench/limits
- tests/sql-bench/output
- tests/sqlbench_tests
- tests/sqlbench_tests/crashme
- tests/sqlbench_tests/main
- files modified:
- tests/lib/test_mode.py
added
removed
tests/sql-bench/crash-me
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
# Written by Monty for the TCX/Monty Program/Detron benchmark suite.
21
# Empress and PostgreSQL patches by Luuk de Boer
22
# Extensions for ANSI SQL and Mimer by Bengt Gunne
23
# Some additions and corrections by Matthias Urlich
24
#
25
# This programs tries to find all limits for a sql server
26
# It gets the name from what it does to most servers :)
27
#
28
# Be sure to use --help before running this!
29
#
30
# If you want to add support for another server, add a new package for the
31
# server in server-cfg. You only have to support the 'new' and 'version'
32
# functions. new doesn't need to have any limits if one doesn't want to
33
# use the benchmarks.
34
#
35
36
# TODO:
37
# CMT includes types and functions which are synonyms for other types
38
# and functions, including those in SQL9x. It should label those synonyms
39
# as such, and clarify ones such as "mediumint" with comments such as
40
# "3-byte int" or "same as xxx".
41
42
$version="1.62";
43
44
use Cwd;
45
use DBI;
46
use Getopt::Long;
47
use POSIX;
48
$pwd = cwd(); $pwd = "." if ($pwd eq '');
49
require "$pwd/server-cfg" || die "Can't read Configuration file: $!\n";
50
51
our $opt_batch_mode=0;
52
our $opt_check_server=0; # Check if server is alive before each query
53
our $opt_comment="";
54
our $opt_config_file="";
55
our $opt_connect_options="";
56
our $opt_database="test";
57
our $opt_db_start_cmd=""; # the db server start command
58
our $opt_debug=0;
59
our $opt_dir="limits";
60
our $opt_fix_limit_file=0;
61
our $opt_force=0;
62
our $opt_help=0;
63
our $opt_host="localhost";
64
our $opt_Information=0;
65
our $opt_log_all_queries=0;
66
our $opt_log_queries_to_file="";
67
our $opt_machine="";
68
our $opt_odbc="";
69
our $opt_password="";
70
our $opt_quick=0;
71
our $opt_restart=0;
72
our $opt_server="mysql";
73
our $opt_sleep=10; # time to sleep while starting the db server
74
our $opt_socket=""; # time to sleep while starting the db server
75
our $opt_suffix="";
76
our $opt_user="";
77
our $opt_verbose=1;
78
our $opt_version=0;
79
80
$limit_changed=0; # For configure file
81
$reconnect_count=0;
82
$limits{'crash_me_safe'}='yes';
83
$prompts{'crash_me_safe'}='crash me safe';
84
$limits{'operating_system'}= machine();
85
$prompts{'operating_system'}='crash-me tested on';
86
$retry_limit=3;
87
88
GetOptions("Information","help","server=s","debug","user=s","password=s",
89
"database=s","restart","force","quick","log-all-queries","comment=s",
90
"host=s","fix-limit-file","dir=s","db-start-cmd=s","sleep=s","suffix=s",
91
"batch-mode","config-file=s","log-queries-to-file=s","check-server",
92
"version", "connect-options=s", "machine=s", "socket=s",
93
"verbose!" => \$opt_verbose) || usage();
94
95
#
96
# Find limit using binary search. This is a weighed binary search that
97
# will prefere lower limits to get the server to crash as
98
# few times as possible
99
sub find_limit
100
{
101
my ($prompt,$limit,$query)=@_;
102
my ($first,$end,$i,$tmp,@tmp_array, $queries);
103
print "$prompt: ";
104
if (defined($end=$limits{$limit}))
105
{
106
print "$end (cache)\n";
107
return $end;
108
}
109
save_incomplete($limit,$prompt);
110
add_log($limit,"We are trying (example with N=5):");
111
$queries = $query->query(5);
112
if (ref($queries) ne "ARRAY")
113
{
114
push(@tmp_array,$queries);
115
$queries= \@tmp_array;
116
}
117
foreach $tmp (@$queries)
118
{ add_log($limit,repr_query($tmp)); }
119
120
if (defined($queries = $query->check_query()))
121
{
122
if (ref($queries) ne "ARRAY")
123
{
124
@tmp_array=();
125
push(@tmp_array,$queries);
126
$queries= \@tmp_array;
127
}
128
foreach $tmp (@$queries)
129
{ add_log($limit,repr_query($tmp)); }
130
}
131
if (defined($query->{'init'}) && !defined($end=$limits{'restart'}{'tohigh'}))
132
{
133
if (!safe_query_l($limit,$query->{'init'}))
134
{
135
$query->cleanup();
136
return "error";
137
}
138
}
139
140
if (!limit_query($query,1)) # This must work
141
{
142
print "\nMaybe fatal error: Can't check '$prompt' for limit=1\n".
143
"error: $last_error\n";
144
return "error";
145
}
146
147
$first=0;
148
$first=$limits{'restart'}{'low'} if ($limits{'restart'}{'low'});
149
150
if (defined($end=$limits{'restart'}{'tohigh'}))
151
{
152
$end--;
153
print "\nRestarting this with low limit: $first and high limit: $end\n";
154
delete $limits{'restart'};
155
$i=$first+int(($end-$first+4)/5); # Prefere lower on errors
156
}
157
else
158
{
159
$end= $query->max_limit();
160
$i=int(($end+$first)/2);
161
}
162
my $log_str = "";
163
unless(limit_query($query,0+$end)) {
164
while ($first < $end)
165
{
166
print "." if ($opt_debug);
167
save_config_data("restart",$i,"") if ($opt_restart);
168
if (limit_query($query,$i))
169
{
170
$first=$i;
171
$log_str .= " $i:OK";
172
$i=$first+int(($end-$first+1)/2); # to be a bit faster to go up
173
}
174
else
175
{
176
$end=$i-1;
177
$log_str .= " $i:FAIL";
178
$i=$first+int(($end-$first+4)/5); # Prefere lower on errors
179
}
180
}
181
}
182
$end+=$query->{'offset'} if ($end && defined($query->{'offset'}));
183
if ($end >= $query->{'max_limit'} &&
184
substr($query->{'max_limit'},0,1) eq '+')
185
{
186
$end= $query->{'max_limit'};
187
}
188
print "$end\n";
189
add_log($limit,$log_str);
190
save_config_data($limit,$end,$prompt);
191
delete $limits{'restart'};
192
return $end;
193
}
194
195
usage() if ($opt_help || $opt_Information);
196
197
version() && exit(0) if ($opt_version);
198
199
$opt_suffix = '-'.$opt_suffix if (length($opt_suffix) != 0);
200
$opt_config_file = "$pwd/$opt_dir/$opt_server$opt_suffix.cfg"
201
if (length($opt_config_file) == 0);
202
$safe_query_log='';
203
$safe_query_result_log='';
204
$log{"crash-me"}="";
205
206
#!!!
207
208
if ($opt_fix_limit_file)
209
{
210
print "Fixing limit file for $opt_server\n";
211
read_config_data();
212
$limit_changed=1;
213
save_all_config_data();
214
exit 0;
215
}
216
217
$server=get_server($opt_server,$opt_host,$opt_database,$opt_odbc, machine_part(), $opt_socket, $opt_connect_options);
218
$opt_server=$server->{'cmp_name'};
219
220
$|=1; # For debugging
221
222
print "Running $0 $version on '",($server_version=$server->version()),"'\n\n";
223
print "I hope you didn't have anything important running on this server....\n";
224
read_config_data();
225
if ($limit_changed) # Must have been restarted
226
{
227
save_config_data('crash_me_safe','no',"crash me safe");
228
}
229
230
if (!$opt_force && !$opt_batch_mode)
231
{
232
server_info();
233
}
234
else
235
{
236
print "Using --force. I assume you know what you are doing...\n";
237
}
238
print "\n";
239
240
save_config_data('crash_me_version',$version,"crash me version");
241
if ($server_version)
242
{
243
save_config_data('server_version',$server_version,"server version");
244
}
245
if (length($opt_comment))
246
{
247
save_config_data('user_comment',$opt_comment,"comment");
248
}
249
250
$opt_log=0;
251
if (length($opt_log_queries_to_file))
252
{
253
open(LOG,">$opt_log_queries_to_file") ||
254
die "Can't open file $opt_log_queries_to_file\n";
255
$opt_log=1;
256
}
257
258
#
259
# Set up some limits that's regared as unlimited
260
# We don't want to take up all resources from the server...
261
#
262
263
$max_connections="+1000"; # Number of simultaneous connections
264
$max_buffer_size="+16000000"; # size of communication buffer.
265
$max_string_size="+8000000"; # Enough for this test
266
$max_name_length="+512"; # Actually 256, but ...
267
$max_keys="+64"; # Probably too big.
268
$max_join_tables="+64"; # Probably too big.
269
$max_columns="+8192"; # Probably too big.
270
$max_row_length=$max_string_size;
271
$max_key_length="+8192"; # Big enough
272
$max_order_by="+64"; # Big enough
273
$max_expressions="+10000";
274
$max_big_expressions="+100";
275
$max_stacked_expressions="+2000";
276
$query_size=$max_buffer_size;
277
$longreadlen=16000000; # For retrieval buffer
278
279
280
#
281
# First do some checks that needed for the rest of the benchmark
282
#
283
use sigtrap; # Must be removed with perl5.005_2 on Win98
284
$SIG{PIPE} = 'IGNORE';
285
$problem_counter=0;
286
$SIG{SEGV} = sub {
287
$problem_counter +=1;
288
if ($problem_counter >= 100) {
289
die("Too many problems, try to restart");
290
} else {
291
warn('SEGFAULT');
292
};
293
};
294
$dbh=safe_connect();
295
296
#
297
# Test if the database require RESTRICT/CASCADE after DROP TABLE
298
#
299
300
# Really remove the crash_me table
301
$prompt="drop table require cascade/restrict";
302
$drop_attr="";
303
$dbh->do("drop table crash_me");
304
$dbh->do("drop table crash_me cascade");
305
if (!safe_query_l('drop_requires_cascade',
306
["create table crash_me (a integer not null)",
307
"drop table crash_me"]))
308
{
309
$dbh->do("drop table crash_me cascade");
310
if (safe_query_l('drop_requires_cascade',
311
["create table crash_me (a integer not null)",
312
"drop table crash_me cascade"]))
313
{
314
save_config_data('drop_requires_cascade',"yes","$prompt");
315
$drop_attr="cascade";
316
}
317
else
318
{
319
die "Can't create and drop table 'crash_me'\n";
320
}
321
}
322
else
323
{
324
save_config_data('drop_requires_cascade',"no","$prompt");
325
$drop_attr="";
326
}
327
328
# Remove tables from old runs
329
$dbh->do("drop table crash_me $drop_attr");
330
$dbh->do("drop table crash_me2 $drop_attr");
331
$dbh->do("drop table crash_me3 $drop_attr");
332
$dbh->do("drop table crash_q $drop_attr");
333
$dbh->do("drop table crash_q1 $drop_attr");
334
335
$prompt="Tables without primary key";
336
if (!safe_query_l('no_primary_key',
337
["create table crash_me (a integer not null,b char(10) not null)",
338
"insert into crash_me (a,b) values (1,'a')"]))
339
{
340
if (!safe_query_l('no_primary_key',
341
["create table crash_me (a integer not null,b char(10) not null".
342
", primary key (a))",
343
"insert into crash_me (a,b) values (1,'a')"]))
344
{
345
die "Can't create table 'crash_me' with one record: $DBI::errstr\n";
346
}
347
save_config_data('no_primary_key',"no",$prompt);
348
}
349
else
350
{
351
save_config_data('no_primary_key',"yes",$prompt);
352
}
353
354
#
355
# Define strings for character NULL and numeric NULL used in expressions
356
#
357
$char_null=$server->{'char_null'};
358
$numeric_null=$server->{'numeric_null'};
359
if ($char_null eq '')
360
{
361
$char_null="NULL";
362
}
363
if ($numeric_null eq '')
364
{
365
$numeric_null="NULL";
366
}
367
368
print "$prompt: $limits{'no_primary_key'}\n";
369
370
report("SELECT without FROM",'select_without_from',"select 1");
371
if ($limits{'select_without_from'} ne "yes")
372
{
373
$end_query=" from crash_me";
374
$check_connect="select a from crash_me";
375
}
376
else
377
{
378
$end_query="";
379
$check_connect="select 1";
380
}
381
382
assert_crash($check_connect);
383
assert_crash("select a from crash_me where b<'b'");
384
385
report("Select constants",'select_constants',"select 1 $end_query");
386
report("Select table_name.*",'table_wildcard',
387
"select crash_me.* from crash_me");
388
report("Allows \' and \" as string markers",'quote_with_"',
389
'select a from crash_me where b<"c"');
390
check_and_report("Double '' as ' in strings",'double_quotes',[],
391
"select 'Walker''s' $end_query",[],"Walker's",1);
392
check_and_report("Multiple line strings","multi_strings",[],
393
"select a from crash_me where b < 'a'\n'b'",[],"1",0);
394
check_and_report("\" as identifier quote (ANSI SQL)",'quote_ident_with_"',[],
395
'select "A" from crash_me',[],"A",0);
396
check_and_report("\` as identifier quote",'quote_ident_with_`',[],
397
'select `A` from crash_me',[],"1",0);
398
check_and_report("[] as identifier quote",'quote_ident_with_[',[],
399
'select [A] from crash_me',[],"1",0);
400
report('Double "" in identifiers as "','quote_ident_with_dbl_"',
401
'create table crash_me1 ("abc""d" integer)',
402
'drop table crash_me1');
403
404
report("Column alias","column_alias","select a as ab from crash_me");
405
report("Table alias","table_alias","select b.a from crash_me as b");
406
report("Functions",'functions',"select 1+1 $end_query");
407
report("Group functions",'group_functions',"select count(*) from crash_me");
408
report("Group functions with distinct",'group_distinct_functions',
409
"select count(distinct a) from crash_me");
410
report("Group functions with several distinct",'group_many_distinct_functions',
411
"select count(distinct a), count(distinct b) from crash_me");
412
report("Group by",'group_by',"select a from crash_me group by a");
413
report("Group by position",'group_by_position',
414
"select a from crash_me group by 1");
415
report("Group by alias",'group_by_alias',
416
"select a as ab from crash_me group by ab");
417
report("Group on unused column",'group_on_unused',
418
"select count(*) from crash_me group by a");
419
420
report("Order by",'order_by',"select a from crash_me order by a");
421
report("Order by position",'order_by_position',
422
"select a from crash_me order by 1");
423
report("Order by function","order_by_function",
424
"select a from crash_me order by a+1");
425
report("Order by on unused column",'order_on_unused',
426
"select b from crash_me order by a");
427
# little bit deprecated
428
#check_and_report("Order by DESC is remembered",'order_by_remember_desc',
429
# ["create table crash_q (s int,s1 int)",
430
# "insert into crash_q values(1,1)",
431
# "insert into crash_q values(3,1)",
432
# "insert into crash_q values(2,1)"],
433
# "select s,s1 from crash_q order by s1 DESC,s",
434
# ["drop table crash_q $drop_attr"],[3,2,1],7,undef(),3);
435
report("Compute",'compute',
436
"select a from crash_me order by a compute sum(a) by a");
437
report("INSERT with Value lists",'insert_multi_value',
438
"create table crash_q (s char(10))",
439
"insert into crash_q values ('a'),('b')",
440
"drop table crash_q $drop_attr");
441
report("INSERT with set syntax",'insert_with_set',
442
"create table crash_q (a integer)",
443
"insert into crash_q SET a=1",
444
"drop table crash_q $drop_attr");
445
report("INSERT with DEFAULT","insert_with_default",
446
"create table crash_me_q (a int)",
447
"insert into crash_me_q (a) values (DEFAULT)",
448
"drop table crash_me_q $drop_attr");
449
450
report("INSERT with empty value list","insert_with_empty_value_list",
451
"create table crash_me_q (a int)",
452
"insert into crash_me_q (a) values ()",
453
"drop table crash_me_q $drop_attr");
454
455
report("INSERT DEFAULT VALUES","insert_default_values",
456
"create table crash_me_q (a int)",
457
"insert into crash_me_q DEFAULT VALUES",
458
"drop table crash_me_q $drop_attr");
459
460
report("allows end ';'","end_colon", "select * from crash_me;");
461
try_and_report("LIMIT number of rows","select_limit",
462
["with LIMIT",
463
"select * from crash_me limit 1"],
464
["with TOP",
465
"select TOP 1 * from crash_me"]);
466
report("SELECT with LIMIT #,#","select_limit2",
467
"select * from crash_me limit 1,1");
468
report("SELECT with LIMIT # OFFSET #",
469
"select_limit3", "select * from crash_me limit 1 offset 1");
470
471
# The following alter table commands MUST be kept together!
472
if ($dbh->do("create table crash_q (a integer, b integer,c1 CHAR(10))"))
473
{
474
report("Alter table add column",'alter_add_col',
475
"alter table crash_q add d integer");
476
report_one("Alter table add many columns",'alter_add_multi_col',
477
[["alter table crash_q add (f integer,g integer)","yes"],
478
["alter table crash_q add f integer, add g integer","with add"],
479
["alter table crash_q add f integer,g integer","without add"]] );
480
report("Alter table change column",'alter_change_col',
481
"alter table crash_q change a e char(50)");
482
483
# informix can only change data type with modify
484
report_one("Alter table modify column",'alter_modify_col',
485
[["alter table crash_q modify c1 CHAR(20)","yes"],
486
["alter table crash_q alter c1 CHAR(20)","with alter"]]);
487
report("Alter table alter column default",'alter_alter_col',
488
"alter table crash_q alter b set default 10");
489
report_one("Alter table drop column",'alter_drop_col',
490
[["alter table crash_q drop column b","yes"],
491
["alter table crash_q drop column b restrict",
492
"with restrict/cascade"]]);
493
report("Alter table rename table",'alter_rename_table',
494
"alter table crash_q rename to crash_q1");
495
}
496
# Make sure both tables will be dropped, even if rename fails.
497
$dbh->do("drop table crash_q1 $drop_attr");
498
$dbh->do("drop table crash_q $drop_attr");
499
500
report("rename table","rename_table",
501
"create table crash_q (a integer, b integer,c1 CHAR(10))",
502
"rename table crash_q to crash_q1",
503
"drop table crash_q1 $drop_attr");
504
# Make sure both tables will be dropped, even if rename fails.
505
$dbh->do("drop table crash_q1 $drop_attr");
506
$dbh->do("drop table crash_q $drop_attr");
507
508
report("truncate","truncate_table",
509
"create table crash_q (a integer, b integer,c1 CHAR(10))",
510
"truncate table crash_q",
511
"drop table crash_q $drop_attr");
512
513
if ($dbh->do("create table crash_q (a integer, b integer,c1 CHAR(10))") &&
514
$dbh->do("create table crash_q1 (a integer, b integer,c1 CHAR(10) not null)"))
515
{
516
report("Alter table add constraint",'alter_add_constraint',
517
"alter table crash_q add constraint c2 check(a > b)");
518
report_one("Alter table drop constraint",'alter_drop_constraint',
519
[["alter table crash_q drop constraint c2","yes"],
520
["alter table crash_q drop constraint c2 restrict",
521
"with restrict/cascade"]]);
522
report("Alter table add unique",'alter_add_unique',
523
"alter table crash_q add constraint u1 unique(c1)");
524
try_and_report("Alter table drop unique",'alter_drop_unique',
525
["with constraint",
526
"alter table crash_q drop constraint u1"],
527
["with constraint and restrict/cascade",
528
"alter table crash_q drop constraint u1 restrict"],
529
["with drop key",
530
"alter table crash_q drop key u1"]);
531
try_and_report("Alter table add primary key",'alter_add_primary_key',
532
["with constraint",
533
"alter table crash_q1 add constraint p1 primary key(c1)"],
534
["with add primary key",
535
"alter table crash_q1 add primary key(c1)"]);
536
report("Alter table add foreign key",'alter_add_foreign_key',
537
"alter table crash_q add constraint f1 foreign key(c1) references crash_q1(c1)");
538
try_and_report("Alter table drop foreign key",'alter_drop_foreign_key',
539
["with drop constraint",
540
"alter table crash_q drop constraint f1"],
541
["with drop constraint and restrict/cascade",
542
"alter table crash_q drop constraint f1 restrict"],
543
["with drop foreign key",
544
"alter table crash_q drop foreign key f1"]);
545
try_and_report("Alter table drop primary key",'alter_drop_primary_key',
546
["drop constraint",
547
"alter table crash_q1 drop constraint p1 restrict"],
548
["drop primary key",
549
"alter table crash_q1 drop primary key"]);
550
}
551
$dbh->do("drop table crash_q $drop_attr");
552
$dbh->do("drop table crash_q1 $drop_attr");
553
554
check_and_report("Case insensitive compare","case_insensitive_strings",
555
[],"select b from crash_me where b = 'A'",[],'a',1);
556
check_and_report("Ignore end space in compare","ignore_end_space",
557
[],"select b from crash_me where b = 'a '",[],'a',1);
558
check_and_report("Group on column with null values",'group_by_null',
559
["create table crash_q (s char(10))",
560
"insert into crash_q values(null)",
561
"insert into crash_q values(null)"],
562
"select count(*),s from crash_q group by s",
563
["drop table crash_q $drop_attr"],2,0);
564
565
$prompt="Having";
566
if (!defined($limits{'having'}))
567
{ # Complicated because of postgreSQL
568
if (!safe_query_result_l("having",
569
"select a from crash_me group by a having a > 0",1,0))
570
{
571
if (!safe_query_result_l("having",
572
"select a from crash_me group by a having a < 0",
573
1,0))
574
{ save_config_data("having","error",$prompt); }
575
else
576
{ save_config_data("having","yes",$prompt); }
577
}
578
else
579
{ save_config_data("having","no",$prompt); }
580
}
581
print "$prompt: $limits{'having'}\n";
582
583
if ($limits{'having'} eq 'yes')
584
{
585
report("Having with group function","having_with_group",
586
"select a from crash_me group by a having count(*) = 1");
587
}
588
589
if ($limits{'column_alias'} eq 'yes')
590
{
591
report("Order by alias",'order_by_alias',
592
"select a as ab from crash_me order by ab");
593
if ($limits{'having'} eq 'yes')
594
{
595
report("Having on alias","having_with_alias",
596
"select a as ab from crash_me group by a having ab > 0");
597
}
598
}
599
report("binary numbers (0b1001)","binary_numbers","select 0b1001 $end_query");
600
report("hex numbers (0x41)","hex_numbers","select 0x41 $end_query");
601
report("binary strings (b'0110')","binary_strings","select b'0110' $end_query");
602
report("hex strings (x'1ace')","hex_strings","select x'1ace' $end_query");
603
604
report_result("Value of logical operation (1=1)","logical_value",
605
"select (1=1) $end_query");
606
607
report_result("Value of TRUE","value_of_true","select TRUE $end_query");
608
report_result("Value of FALSE","value_of_false","select FALSE $end_query");
609
610
$logical_value= $limits{'logical_value'};
611
612
$false=0;
613
$result="no";
614
if ($res=safe_query_l('has_true_false',"select (1=1)=true $end_query")) {
615
$false="false";
616
$result="yes";
617
}
618
save_config_data('has_true_false',$result,"TRUE and FALSE");
619
620
#
621
# Check how many connections the server can handle:
622
# We can't test unlimited connections, because this may take down the
623
# server...
624
#
625
626
$prompt="Simultaneous connections (installation default)";
627
print "$prompt: ";
628
if (defined($limits{'connections'}))
629
{
630
print "$limits{'connections'}\n";
631
}
632
else
633
{
634
@connect=($dbh);
635
636
for ($i=1; $i < $max_connections ; $i++)
637
{
638
if (!($dbh=DBI->connect($server->{'data_source'},$opt_user,$opt_password,
639
{ PrintError => 0})))
640
{
641
print "Last connect error: $DBI::errstr\n" if ($opt_debug);
642
last;
643
}
644
$dbh->{LongReadLen}= $longreadlen; # Set retrieval buffer
645
print "." if ($opt_debug);
646
push(@connect,$dbh);
647
}
648
print "$i\n";
649
save_config_data('connections',$i,$prompt);
650
foreach $dbh (@connect)
651
{
652
print "#" if ($opt_debug);
653
$dbh->disconnect || warn $dbh->errstr; # close connection
654
}
655
656
$#connect=-1; # Free connections
657
658
if ($i == 0)
659
{
660
print "Can't connect to server: $DBI::errstr.".
661
" Please start it and try again\n";
662
exit 1;
663
}
664
$dbh=retry_connect();
665
}
666
667
668
#
669
# Check size of communication buffer, strings...
670
#
671
672
$prompt="query size";
673
print "$prompt: ";
674
if (!defined($limits{'query_size'}))
675
{
676
$query="select ";
677
$first=64;
678
$end=$max_buffer_size;
679
$select= $limits{'select_without_from'} eq 'yes' ? 1 : 'a';
680
681
assert_crash($query . "$select$end_query");
682
683
$first=$limits{'restart'}{'low'} if ($limits{'restart'}{'low'});
684
685
if ($limits{'restart'}{'tohigh'})
686
{
687
$end = $limits{'restart'}{'tohigh'} - 1;
688
print "\nRestarting this with low limit: $first and high limit: $end\n";
689
delete $limits{'restart'};
690
$first=$first+int(($end-$first+4)/5); # Prefere lower on errors
691
}
692
for ($i=$first ; $i < $end ; $i*=2)
693
{
694
#printf "Trying query %s %d with %d spaces\n",$query,$select,($i - length($query)-length($end_query) -1);
695
last if (!safe_query($query .
696
(" " x ($i - length($query)-length($end_query) -1))
697
. "$select$end_query"));
698
$first=$i;
699
save_config_data("restart",$i,"") if ($opt_restart);
700
}
701
$end=$i;
702
703
if ($i < $max_buffer_size)
704
{
705
while ($first != $end)
706
{
707
$i=int(($first+$end+1)/2);
708
if (safe_query($query .
709
(" " x ($i - length($query)-length($end_query) -1)) .
710
"$select$end_query"))
711
{
712
$first=$i;
713
}
714
else
715
{
716
$end=$i-1;
717
}
718
}
719
}
720
save_config_data('query_size',$end,$prompt);
721
}
722
$query_size=$limits{'query_size'};
723
724
print "$limits{'query_size'}\n";
725
726
#
727
# Check for reserved words
728
#
729
730
check_reserved_words($dbh);
731
732
#
733
# Test database types
734
#
735
736
@sql_types=("character(1)","char(1)","char varying(1)", "character varying(1)",
737
"boolean",
738
"varchar(1)",
739
"integer","int","smallint",
740
"numeric(9,2)","decimal(6,2)","dec(6,2)",
741
"bit", "bit(2)","bit varying(2)","float","float(8)","real",
742
"double precision", "date","time","timestamp",
743
"interval year", "interval year to month",
744
"interval month",
745
"interval day", "interval day to hour", "interval day to minute",
746
"interval day to second",
747
"interval hour", "interval hour to minute",
748
"interval hour to second",
749
"interval minute", "interval minute to second",
750
"interval second",
751
"national character varying(20)",
752
"national character(20)","nchar(1)",
753
"national char varying(20)","nchar varying(20)",
754
"national character varying(20)",
755
"timestamp with time zone");
756
@odbc_types=("binary(1)","varbinary(1)","tinyint","bigint",
757
"datetime");
758
@extra_types=("blob","byte","long varbinary","image","text","text(10)",
759
"mediumtext",
760
"long varchar(1)", "varchar2(257)",
761
"mediumint","middleint","int unsigned",
762
"int1","int2","int3","int4","int8","uint",
763
"money","smallmoney","float4","float8","smallfloat",
764
"float(6,2)","double",
765
"enum('red')","set('red')", "int(5) zerofill", "serial",
766
"char(10) binary","int not null auto_increment,unique(q)",
767
"abstime","year","datetime","smalldatetime","timespan","reltime",
768
# Sybase types
769
"int not null identity,unique(q)",
770
# postgres types
771
"box","bool","circle","polygon","point","line","lseg","path",
772
"interval", "inet", "cidr", "macaddr",
773
774
# oracle types
775
"varchar2(16)","nvarchar2(16)","number(9,2)","number(9)",
776
"number", "long","raw(16)","long raw","rowid","mlslabel","clob",
777
"nclob","bfile"
778
);
779
780
@types=(["sql",\@sql_types],
781
["odbc",\@odbc_types],
782
["extra",\@extra_types]);
783
784
foreach $types (@types)
785
{
786
print "\nSupported $types->[0] types\n";
787
$tmp=@$types->[1];
788
foreach $use_type (@$tmp)
789
{
790
$type=$use_type;
791
$type =~ s/\(.*\)/(1 arg)/;
792
if (index($use_type,",")>= 0)
793
{
794
$type =~ s/\(1 arg\)/(2 arg)/;
795
}
796
if (($tmp2=index($type,",unique")) >= 0)
797
{
798
$type=substr($type,0,$tmp2);
799
}
800
$tmp2=$type;
801
$tmp2 =~ s/ /_/g;
802
$tmp2 =~ s/_not_null//g;
803
report("Type $type","type_$types->[0]_$tmp2",
804
"create table crash_q (q $use_type)",
805
"drop table crash_q $drop_attr");
806
}
807
}
808
809
#
810
# Test some type limits
811
#
812
813
814
check_and_report("Remembers end space in char()","remember_end_space",
815
["create table crash_q (a char(10))",
816
"insert into crash_q values('hello ')"],
817
"select a from crash_q where a = 'hello '",
818
["drop table crash_q $drop_attr"],
819
'hello ',6);
820
821
check_and_report("Remembers end space in varchar()",
822
"remember_end_space_varchar",
823
["create table crash_q (a varchar(10))",
824
"insert into crash_q values('hello ')"],
825
"select a from crash_q where a = 'hello '",
826
["drop table crash_q $drop_attr"],
827
'hello ',6);
828
829
if (($limits{'type_extra_float(2_arg)'} eq "yes" ||
830
$limits{'type_sql_decimal(2_arg)'} eq "yes") &&
831
(!defined($limits{'storage_of_float'})))
832
{
833
my $type=$limits{'type_extra_float(2_arg)'} eq "yes" ? "float(4,1)" :
834
"decimal(4,1)";
835
my $result="undefined";
836
if (execute_and_check("storage_of_float",["create table crash_q (q1 $type)",
837
"insert into crash_q values(1.14)"],
838
"select q1 from crash_q",
839
["drop table crash_q $drop_attr"],1.1,0) &&
840
execute_and_check("storage_of_float",["create table crash_q (q1 $type)",
841
"insert into crash_q values(1.16)"],
842
"select q1 from crash_q",
843
["drop table crash_q $drop_attr"],1.1,0))
844
{
845
$result="truncate";
846
}
847
elsif (execute_and_check("storage_of_float",["create table crash_q (q1 $type)",
848
"insert into crash_q values(1.14)"],
849
"select q1 from crash_q",
850
["drop table crash_q $drop_attr"],1.1,0) &&
851
execute_and_check("storage_of_float",["create table crash_q (q1 $type)",
852
"insert into crash_q values(1.16)"],
853
"select q1 from crash_q",
854
["drop table crash_q $drop_attr"],1.2,0))
855
{
856
$result="round";
857
}
858
elsif (execute_and_check("storage_of_float",["create table crash_q (q1 $type)",
859
"insert into crash_q values(1.14)"],
860
"select q1 from crash_q",
861
["drop table crash_q $drop_attr"],1.14,0) &&
862
execute_and_check("storage_of_float",["create table crash_q (q1 $type)",
863
"insert into crash_q values(1.16)"],
864
"select q1 from crash_q",
865
["drop table crash_q $drop_attr"],1.16,0))
866
{
867
$result="exact";
868
}
869
$prompt="Storage of float values";
870
print "$prompt: $result\n";
871
save_config_data("storage_of_float", $result, $prompt);
872
}
873
874
try_and_report("Type for row id", "rowid",
875
["rowid",
876
"create table crash_q (a rowid)",
877
"drop table crash_q $drop_attr"],
878
["auto_increment",
879
"create table crash_q (a int not null auto_increment".
880
", primary key(a))","drop table crash_q $drop_attr"],
881
["oid",
882
"create table crash_q (a oid, primary key(a))",
883
"drop table crash_q $drop_attr"],
884
["serial",
885
"create table crash_q (a serial, primary key(a))",
886
"drop table crash_q $drop_attr"]);
887
888
try_and_report("Automatic row id", "automatic_rowid",
889
["_rowid",
890
"create table crash_q (a int not null, primary key(a))",
891
"insert into crash_q values (1)",
892
"select _rowid from crash_q",
893
"drop table crash_q $drop_attr"]);
894
895
#
896
# Test functions
897
#
898
899
@sql_functions=
900
(["+, -, * and /","+","5*3-4/2+1",14,0],
901
["ANSI SQL SUBSTRING","substring","substring('abcd' from 2 for 2)","bc",1],
902
["BIT_LENGTH","bit_length","bit_length('abc')",24,0],
903
["searched CASE","searched_case",
904
"case when 1 > 2 then 'false' when 2 > 1 then 'true' end", "true",1],
905
["simple CASE","simple_case",
906
"case 2 when 1 then 'false' when 2 then 'true' end", "true",1],
907
["CAST","cast","CAST(1 as CHAR)","1",1],
908
["CHARACTER_LENGTH","character_length","character_length('abcd')","4",0],
909
["CHAR_LENGTH","char_length","char_length(b)","1",0],
910
["CHAR_LENGTH(constant)","char_length(constant)",
911
"char_length('abcd')","4",0],
912
["COALESCE","coalesce","coalesce($char_null,'bcd','qwe')","bcd",1],
913
["CURRENT_DATE","current_date","current_date",0,2],
914
["CURRENT_TIME","current_time","current_time",0,2],
915
["CURRENT_TIMESTAMP","current_timestamp","current_timestamp",0,2],
916
["EXTRACT","extract_sql",
917
"extract(minute from timestamp '2000-02-23 18:43:12.987')",43,0],
918
["LOCALTIME","localtime","localtime",0,2],
919
["LOCALTIMESTAMP","localtimestamp","localtimestamp",0,2],
920
["LOWER","lower","LOWER('ABC')","abc",1],
921
["NULLIF with strings","nullif_string",
922
"NULLIF(NULLIF('first','second'),'first')",undef(),4],
923
["NULLIF with numbers","nullif_num","NULLIF(NULLIF(1,2),1)",undef(),4],
924
["OCTET_LENGTH","octet_length","octet_length('abc')",3,0],
925
["POSITION","position","position('ll' in 'hello')",3,0],
926
["TRIM","trim","trim(trailing from trim(LEADING FROM ' abc '))","abc",3],
927
["UPPER","upper","UPPER('abc')","ABC",1],
928
["concatenation with ||","concat_as_||","'abc' || 'def'","abcdef",1],
929
);
930
931
@odbc_functions=
932
(["ASCII", "ascii", "ASCII('A')","65",0],
933
["CHAR", "char", "CHAR(65)" ,"A",1],
934
["CONCAT(2 arg)","concat", "concat('a','b')","ab",1],
935
["DIFFERENCE()","difference","difference('abc','abe')",3,0],
936
["INSERT","insert","insert('abcd',2,2,'ef')","aefd",1],
937
["LEFT","left","left('abcd',2)","ab",1],
938
["LTRIM","ltrim","ltrim(' abcd')","abcd",1],
939
["REAL LENGTH","length","length('abcd ')","5",0],
940
["ODBC LENGTH","length_without_space","length('abcd')","4",0],
941
["LOCATE(2 arg)","locate_2","locate('bcd','abcd')","2",0],
942
["LOCATE(3 arg)","locate_3","locate('bcd','abcd',3)","0",0],
943
["LCASE","lcase","lcase('ABC')","abc",1],
944
["REPEAT","repeat","repeat('ab',3)","ababab",1],
945
["REPLACE","replace","replace('abbaab','ab','ba')","bababa",1],
946
["RIGHT","right","right('abcd',2)","cd",1],
947
["RTRIM","rtrim","rtrim(' abcd ')"," abcd",1],
948
["SPACE","space","space(5)"," ",3],
949
["SOUNDEX","soundex","soundex('hello')",0,2],
950
["ODBC SUBSTRING","substring","substring('abcd',3,2)","cd",1],
951
["UCASE","ucase","ucase('abc')","ABC",1],
952
953
["ABS","abs","abs(-5)",5,0],
954
["ACOS","acos","acos(0)","1.570796",0],
955
["ASIN","asin","asin(1)","1.570796",0],
956
["ATAN","atan","atan(1)","0.785398",0],
957
["ATAN2","atan2","atan2(1,0)","1.570796",0],
958
["CEILING","ceiling","ceiling(-4.5)",-4,0],
959
["COS","cos","cos(0)","1.00000",0],
960
["COT","cot","cot(1)","0.64209262",0],
961
["DEGREES","degrees","degrees(6.283185)","360",0],
962
["EXP","exp","exp(1.0)","2.718282",0],
963
["FLOOR","floor","floor(2.5)","2",0],
964
["LOG","log","log(2)","0.693147",0],
965
["LOG10","log10","log10(10)","1",0],
966
["MOD","mod","mod(11,7)","4",0],
967
["PI","pi","pi()","3.141593",0],
968
["POWER","power","power(2,4)","16",0],
969
["RAND","rand","rand(1)",0,2], # Any value is acceptable
970
["RADIANS","radians","radians(360)","6.283185",0],
971
["ROUND(2 arg)","round","round(5.63,2)","5.6",0],
972
["SIGN","sign","sign(-5)",-1,0],
973
["SIN","sin","sin(1)","0.841471",0],
974
["SQRT","sqrt","sqrt(4)",2,0],
975
["TAN","tan","tan(1)","1.557408",0],
976
["TRUNCATE","truncate","truncate(18.18,-1)",10,0],
977
["NOW","now","now()",0,2], # Any value is acceptable
978
["CURDATE","curdate","curdate()",0,2],
979
["CURTIME","curtime","curtime()",0,2],
980
["TIMESTAMPADD","timestampadd",
981
"timestampadd(SQL_TSI_SECOND,1,'1997-01-01 00:00:00')",
982
"1997-01-01 00:00:01",1],
983
["TIMESTAMPDIFF","timestampdiff",
984
"timestampdiff(SQL_TSI_SECOND,'1997-01-01 00:00:01',".
985
" '1997-01-01 00:00:02')","1",0],
986
["USER()","user()","user()",0,2],
987
["DATABASE","database","database()",0,2],
988
["IFNULL","ifnull","ifnull(2,3)",2,0],
989
["ODBC syntax LEFT & RIGHT", "fn_left",
990
"{ fn LEFT( { fn RIGHT('abcd',2) },1) }","c",1],
991
);
992
993
@extra_functions=
994
(
995
["& (bitwise and)",'&',"5 & 3",1,0],
996
["| (bitwise or)",'|',"1 | 2",3,0],
997
["<< and >> (bitwise shifts)",'binary_shifts',"(1 << 4) >> 2",4,0],
998
["<> in SELECT","<>","1<>1","0",0],
999
["=","=","(1=1)",1,$logical_value],
1000
["~* (case insensitive compare)","~*","'hi' ~* 'HI'",1,$logical_value],
1001
["AND and OR in SELECT","and_or","1=1 AND 2=2",$logical_value,0],
1002
["AND as '&&'",'&&',"1=1 && 2=2",$logical_value,0],
1003
["ASCII_CHAR", "ascii_char", "ASCII_CHAR(65)","A",1],
1004
["ASCII_CODE", "ascii_code", "ASCII_CODE('A')","65",0],
1005
["ATN2","atn2","atn2(1,0)","1.570796",0],
1006
["BETWEEN in SELECT","between","5 between 4 and 6",$logical_value,0],
1007
["BIT_COUNT","bit_count","bit_count(5)",2,0],
1008
["CEIL","ceil","ceil(-4.5)",-4,0], # oracle
1009
["CHARINDEX","charindex","charindex('a','crash')",3,0],
1010
["CHR", "chr", "CHR(65)" ,"A",1], # oracle
1011
["CONCAT(list)","concat_list", "concat('a','b','c','d')","abcd",1],
1012
["CONVERT","convert","convert(CHAR,5)","5",1],
1013
["COSH","cosh","cosh(0)","1",0], # oracle hyperbolic cosine of n.
1014
["ELT","elt","elt(2,'ONE','TWO','THREE')","TWO",1],
1015
["ENCRYPT","encrypt","encrypt('hello')",0,2],
1016
["FIELD","field","field('IBM','NCA','ICL','SUN','IBM','DIGITAL')",4,0],
1017
["FORMAT","format","format(1234.5555,2)","1,234.56",1],
1018
["GETDATE","getdate","getdate()",0,2],
1019
["GREATEST","greatest","greatest('HARRY','HARRIOT','HAROLD')","HARRY",1],
1020
["IF","if", "if(5,6,7)",6,0],
1021
["IN on numbers in SELECT","in_num","2 in (3,2,5,9,5,1)",$logical_value,0],
1022
["IN on strings in SELECT","in_str","'monty' in ('david','monty','allan')", $logical_value,0],
1023
["INITCAP","initcap","initcap('the soap')","The Soap",1],
1024
# oracle Returns char, with the first letter of each word in uppercase
1025
["INSTR (Oracle syntax)", "instr_oracle", "INSTR('CORPORATE FLOOR','OR',3,2)" ,"14",0], # oracle instring
1026
["INSTRB", "instrb", "INSTRB('CORPORATE FLOOR','OR',5,2)" ,"27",0],
1027
# oracle instring in bytes
1028
["INTERVAL","interval","interval(55,10,20,30,40,50,60,70,80,90,100)",5,0],
1029
["LAST_INSERT_ID","last_insert_id","last_insert_id()",0,2],
1030
["LEAST","least","least('HARRY','HARRIOT','HAROLD')","HAROLD",1],
1031
# oracle
1032
["LENGTHB","lengthb","lengthb('CANDIDE')","14",0],
1033
# oracle length in bytes
1034
["LIKE ESCAPE in SELECT","like_escape",
1035
"'%' like 'a%' escape 'a'",$logical_value,0],
1036
["LIKE in SELECT","like","'a' like 'a%'",$logical_value,0],
1037
["LN","ln","ln(95)","4.55387689",0],
1038
# oracle natural logarithm of n
1039
["LOCATE as INSTR","instr","instr('hello','ll')",3,0],
1040
["LOG(m,n)","log(m_n)","log(10,100)","2",0],
1041
# oracle logarithm, base m, of n
1042
["LOGN","logn","logn(2)","0.693147",0],
1043
# informix
1044
["LPAD","lpad","lpad('hi',4,'??')",'??hi',3],
1045
["MOD as %","%","10%7","3",0],
1046
["NOT BETWEEN in SELECT","not_between","5 not between 4 and 6",0,0],
1047
["NOT LIKE in SELECT","not_like","'a' not like 'a%'",0,0],
1048
["NOT as '!' in SELECT","!","! 1",0,0],
1049
["NOT in SELECT","not","not $false",$logical_value,0],
1050
["ODBC CONVERT","odbc_convert","convert(5,SQL_CHAR)","5",1],
1051
["OR as '||'",'||',"1=0 || 1=1",$logical_value,0],
1052
["PASSWORD","password","password('hello')",0,2],
1053
["PASTE", "paste", "paste('ABCDEFG',3,2,'1234')","AB1234EFG",1],
1054
["PATINDEX","patindex","patindex('%a%','crash')",3,0],
1055
["POW","pow","pow(3,2)",9,0],
1056
["RANGE","range","range(a)","0.0",0],
1057
# informix range(a) = max(a) - min(a)
1058
["REGEXP in SELECT","regexp","'a' regexp '^(a|b)*\$'",$logical_value,0],
1059
["REPLICATE","replicate","replicate('a',5)","aaaaa",1],
1060
["REVERSE","reverse","reverse('abcd')","dcba",1],
1061
["ROOT","root","root(4)",2,0], # informix
1062
["ROUND(1 arg)","round1","round(5.63)","6",0],
1063
["RPAD","rpad","rpad('hi',4,'??')",'hi??',3],
1064
["SINH","sinh","sinh(1)","1.17520119",0], # oracle hyperbolic sine of n
1065
["STR","str","str(123.45,5,1)",123.5,3],
1066
["STRCMP","strcmp","strcmp('abc','adc')",-1,0],
1067
["STUFF","stuff","stuff('abc',2,3,'xyz')",'axyz',3],
1068
["SUBSTRB", "substrb", "SUBSTRB('ABCDEFG',5,4.2)" ,"CD",1],
1069
# oracle substring with bytes
1070
["SUBSTRING as MID","mid","mid('hello',3,2)","ll",1],
1071
["SUBSTRING_INDEX","substring_index",
1072
"substring_index('www.tcx.se','.',-2)", "tcx.se",1],
1073
["SYSDATE","sysdate","sysdate()",0,2],
1074
["TAIL","tail","tail('ABCDEFG',3)","EFG",0],
1075
["TANH","tanh","tanh(1)","0.462117157",0],
1076
# oracle hyperbolic tangent of n
1077
["TRANSLATE","translate","translate('abc','bc','de')",'ade',3],
1078
["TRIM; Leading char extension", "trim_leading_char","trim(LEADING ':' FROM ':abc!')","abc!",3],
1079
["TRIM; Trailing char extension", "trim_trailing_char","trim(TRAILING'!' FROM ':abc!')",":abc",3],
1080
["TRIM; Substring extension",
1081
"trim_substring","trim('cb' FROM 'abccb')","abc",3],
1082
["TRUNC","trunc","trunc(18.18,-1)",10,0], # oracle
1083
["UID","uid","uid",0,2], # oracle uid from user
1084
["UNIX_TIMESTAMP","unix_timestamp","unix_timestamp()",0,2],
1085
["USERENV","userenv","userenv",0,2], # oracle user enviroment
1086
["VERSION","version","version()",0,2],
1087
["automatic num->string convert","auto_num2string","concat('a',2)","a2",1],
1088
["automatic string->num convert","auto_string2num","'1'+2",3,0],
1089
["concatenation with +","concat_as_+","'abc' + 'def'","abcdef",1],
1090
["SUBSTR (2 arg)",'substr2arg',"substr('abcd',2)",'bcd',1], #sapdb func
1091
["SUBSTR (3 arg)",'substr3arg',"substr('abcd',2,2)",'bc',1],
1092
["LFILL (3 arg)",'lfill3arg',"lfill('abcd','.',6)",'..abcd',1],
1093
["RFILL (3 arg)",'rfill3arg',"rfill('abcd','.',6)",'abcd..',1],
1094
["RPAD (4 arg)",'rpad4arg',"rpad('abcd',2,'+-',8)",'abcd+-+-',1],
1095
["LPAD (4 arg)",'rpad4arg',"lpad('abcd',2,'+-',8)",'+-+-abcd',1],
1096
["TRIM (1 arg)",'trim1arg',"trim(' abcd ')",'abcd',1],
1097
["TRIM (2 arg)",'trim2arg',"trim('..abcd..','.')",'abcd',1],
1098
["LTRIM (2 arg)",'ltrim2arg',"ltrim('..abcd..','.')",'abcd..',1],
1099
["RTRIM (2 arg)",'rtrim2arg',"rtrim('..abcd..','.')",'..abcd',1],
1100
["EXPAND",'expand2arg',"expand('abcd',6)",'abcd ',0],
1101
["REPLACE (2 arg) ",'replace2arg',"replace('AbCd','bC')",'Ad',1],
1102
["MAPCHAR",'mapchar',"mapchar('A�')",'Aa',1],
1103
["ALPHA",'alpha',"alpha('A�',2)",'AA',1],
1104
["ASCII in string cast",'ascii_string',"ascii('a')",'97',1],
1105
["EBCDIC in string cast",'ebcdic_string',"ebcdic('a')",'a',1],
1106
["TRUNC (1 arg)",'trunc1arg',"trunc(222.6)",222,0],
1107
["FIXED",'fixed',"fixed(222.6666,10,2)",'222.67',0],
1108
["FLOAT",'float',"float(6666.66,4)",6667,0],
1109
["LENGTH",'length',"length(1)",1,0],
1110
["INDEX",'index',"index('abcdefg','cd',1,1)",3,0],
1111
["MICROSECOND",'microsecond',
1112
"MICROSECOND('19630816200212111111')",'111111',0],
1113
["TIMESTAMP",'timestamp',
1114
"timestamp('19630816','00200212')",'19630816200212000000',0],
1115
["VALUE",'value',"value(NULL,'WALRUS')",'WALRUS',0],
1116
["DECODE",'decode',"DECODE('S-103','T72',1,'S-103',2,'Leopard',3)",2,0],
1117
["NUM",'num',"NUM('2123')",2123,0],
1118
["CHR (any type to string)",'chr_str',"CHR(67)",'67',0],
1119
["HEX",'hex',"HEX('A')",41,0],
1120
);
1121
1122
1123
@sql_group_functions=
1124
(
1125
["AVG","avg","avg(a)",1,0],
1126
["COUNT (*)","count_*","count(*)",1,0],
1127
["COUNT column name","count_column","count(a)",1,0],
1128
["COUNT(DISTINCT expr)","count_distinct","count(distinct a)",1,0],
1129
["MAX on numbers","max","max(a)",1,0],
1130
["MAX on strings","max_str","max(b)","a",1],
1131
["MIN on numbers","min","min(a)",1,0],
1132
["MIN on strings","min_str","min(b)","a",1],
1133
["SUM","sum","sum(a)",1,0],
1134
["ANY","any","any(a)",$logical_value,0],
1135
["EVERY","every","every(a)",$logical_value,0],
1136
["SOME","some","some(a)",$logical_value,0],
1137
);
1138
1139
@extra_group_functions=
1140
(
1141
["BIT_AND",'bit_and',"bit_and(a)",1,0],
1142
["BIT_OR", 'bit_or', "bit_or(a)",1,0],
1143
["COUNT(DISTINCT expr,expr,...)",
1144
"count_distinct_list","count(distinct a,b)",1,0],
1145
["STD","std","std(a)",0,0],
1146
["STDDEV","stddev","stddev(a)",0,0],
1147
["VARIANCE","variance","variance(a)",0,0],
1148
);
1149
1150
@where_functions=
1151
(
1152
["= ALL","eq_all","b =all (select b from crash_me)",1,0],
1153
["= ANY","eq_any","b =any (select b from crash_me)",1,0],
1154
["= SOME","eq_some","b =some (select b from crash_me)",1,0],
1155
["BETWEEN","between","5 between 4 and 6",1,0],
1156
["EXISTS","exists","exists (select * from crash_me)",1,0],
1157
["IN on numbers","in_num","2 in (3,2,5,9,5,1)",1,0],
1158
["LIKE ESCAPE","like_escape","b like '%' escape 'a'",1,0],
1159
["LIKE","like","b like 'a%'",1,0],
1160
["MATCH UNIQUE","match_unique",
1161
"1 match unique (select a from crash_me)",1,0],
1162
["MATCH","match","1 match (select a from crash_me)",1,0],
1163
["MATCHES","matches","b matches 'a*'",1,0],
1164
["NOT BETWEEN","not_between","7 not between 4 and 6",1,0],
1165
["NOT EXISTS","not_exists",
1166
"not exists (select * from crash_me where a = 2)",1,0],
1167
["NOT LIKE","not_like","b not like 'b%'",1,0],
1168
["NOT UNIQUE","not_unique",
1169
"not unique (select * from crash_me where a = 2)",1,0],
1170
["UNIQUE","unique","unique (select * from crash_me)",1,0],
1171
);
1172
1173
@types=(["sql",\@sql_functions,0],
1174
["odbc",\@odbc_functions,0],
1175
["extra",\@extra_functions,0],
1176
["where",\@where_functions,0]);
1177
1178
@group_types=(["sql",\@sql_group_functions,0],
1179
["extra",\@extra_group_functions,0]);
1180
1181
1182
foreach $types (@types)
1183
{
1184
print "\nSupported $types->[0] functions\n";
1185
$tmp=@$types->[1];
1186
foreach $type (@$tmp)
1187
{
1188
if (defined($limits{"func_$types->[0]_$type->[1]"}))
1189
{
1190
next;
1191
}
1192
if ($types->[0] eq "where")
1193
{
1194
check_and_report("Function $type->[0]","func_$types->[0]_$type->[1]",
1195
[],"select a from crash_me where $type->[2]",[],
1196
$type->[3],$type->[4]);
1197
}
1198
elsif ($limits{'functions'} eq 'yes')
1199
{
1200
if (($type->[2] =~ /char_length\(b\)/) && (!$end_query))
1201
{
1202
my $tmp= $type->[2];
1203
$tmp .= " from crash_me ";
1204
undef($limits{"func_$types->[0]_$type->[1]"});
1205
check_and_report("Function $type->[0]",
1206
"func_$types->[0]_$type->[1]",
1207
[],"select $tmp ",[],
1208
$type->[3],$type->[4]);
1209
}
1210
else
1211
{
1212
undef($limits{"func_$types->[0]_$type->[1]"});
1213
$result = check_and_report("Function $type->[0]",
1214
"func_$types->[0]_$type->[1]",
1215
[],"select $type->[2] $end_query",[],
1216
$type->[3],$type->[4]);
1217
if (!$result)
1218
{
1219
# check without type specifyer
1220
if ($type->[2] =~ /DATE /)
1221
{
1222
my $tmp= $type->[2];
1223
$tmp =~ s/DATE //;
1224
undef($limits{"func_$types->[0]_$type->[1]"});
1225
$result = check_and_report("Function $type->[0]",
1226
"func_$types->[0]_$type->[1]",
1227
[],"select $tmp $end_query",[],
1228
$type->[3],$type->[4]);
1229
}
1230
if (!$result)
1231
{
1232
if ($types->[0] eq "odbc" && ! ($type->[2] =~ /\{fn/))
1233
{
1234
my $tmp= $type->[2];
1235
# Check by converting to ODBC format
1236
undef($limits{"func_$types->[0]_$type->[1]"});
1237
$tmp= "{fn $tmp }";
1238
$tmp =~ s/('1997-\d\d-\d\d \d\d:\d\d:\d\d')/{ts $1}/g;
1239
$tmp =~ s/(DATE '1997-\d\d-\d\d')/{d $1}/g;
1240
$tmp =~ s/(TIME '12:13:14')/{t $1}/g;
1241
$tmp =~ s/DATE //;
1242
$tmp =~ s/TIME //;
1243
check_and_report("Function $type->[0]",
1244
"func_$types->[0]_$type->[1]",
1245
[],"select $tmp $end_query",[],
1246
$type->[3],$type->[4]);
1247
}
1248
}
1249
}
1250
}
1251
}
1252
}
1253
}
1254
1255
if ($limits{'functions'} eq 'yes')
1256
{
1257
foreach $types (@group_types)
1258
{
1259
print "\nSupported $types->[0] group functions\n";
1260
$tmp=@$types->[1];
1261
foreach $type (@$tmp)
1262
{
1263
check_and_report("Group function $type->[0]",
1264
"group_func_$types->[0]_$type->[1]",
1265
[],"select $type->[2],a from crash_me group by a",[],
1266
$type->[3],$type->[4]);
1267
}
1268
}
1269
print "\n";
1270
report("mixing of integer and float in expression","float_int_expr",
1271
"select 1+1.0 $end_query");
1272
if ($limits{'func_odbc_exp'} eq 'yes')
1273
{
1274
report("No need to cast from integer to float",
1275
"dont_require_cast_to_float", "select exp(1) $end_query");
1276
}
1277
check_and_report("Is 1+NULL = NULL","null_num_expr",
1278
[],"select 1+$numeric_null $end_query",[],undef(),4);
1279
$tmp=sql_concat("'a'",$char_null);
1280
if (defined($tmp))
1281
{
1282
check_and_report("Is $tmp = NULL", "null_concat_expr", [],
1283
"select $tmp $end_query",[], undef(),4);
1284
}
1285
$prompt="Need to cast NULL for arithmetic";
1286
add_log("Need_cast_for_null",
1287
" Check if numeric_null ($numeric_null) is 'NULL'");
1288
save_config_data("Need_cast_for_null",
1289
($numeric_null eq "NULL") ? "no" : "yes",
1290
$prompt);
1291
}
1292
else
1293
{
1294
print "\n";
1295
}
1296
1297
1298
# Test: NOROUND
1299
{
1300
my $result = 'undefined';
1301
my $error;
1302
print "NOROUND: ";
1303
save_incomplete('func_extra_noround','Function NOROUND');
1304
1305
# 1) check if noround() function is supported
1306
$error = safe_query_l('func_extra_noround',"select noround(22.6) $end_query");
1307
if ($error ne 1) # syntax error -- noround is not supported
1308
{
1309
$result = 'no'
1310
}
1311
else # Ok, now check if it really works
1312
{
1313
$error=safe_query_l('func_extra_noround',
1314
["create table crash_me_nr (a int)",
1315
"insert into crash_me_nr values(noround(10.2))",
1316
"drop table crash_me_nr $drop_attr"]);
1317
if ($error == 1)
1318
{
1319
$result= "syntax only";
1320
}
1321
else
1322
{
1323
$result= 'yes';
1324
}
1325
}
1326
print "$result\n";
1327
save_config_data('func_extra_noround',$result,"Function NOROUND");
1328
}
1329
1330
check_parenthesis("func_sql_","CURRENT_USER");
1331
check_parenthesis("func_sql_","SESSION_USER");
1332
check_parenthesis("func_sql_","SYSTEM_USER");
1333
check_parenthesis("func_sql_","USER");
1334
1335
1336
if ($limits{'type_sql_date'} eq 'yes')
1337
{ #
1338
# Checking the format of date in result.
1339
1340
safe_query("drop table crash_me_d $drop_attr");
1341
assert_crash("create table crash_me_d (a date)");
1342
# find the example of date
1343
my $dateexample;
1344
if ($limits{'func_extra_sysdate'} eq 'yes') {
1345
$dateexample=' sysdate() ';
1346
}
1347
elsif ($limits{'func_sql_current_date'} eq 'yes') {
1348
$dateexample='CURRENT_DATE';
1349
}
1350
elsif ($limits{'func_odbc_curdate'} eq 'yes') {
1351
$dateexample='curdate()';
1352
}
1353
elsif ($limits{'func_extra_getdate'} eq 'yes') {
1354
$dateexample='getdate()';
1355
}
1356
elsif ($limits{'func_odbc_now'} eq 'yes') {
1357
$dateexample='now()';
1358
} else {
1359
#try to guess
1360
$dateexample="DATE '1963-08-16'";
1361
} ;
1362
1363
my $key = 'date_format_inresult';
1364
my $prompt = "Date format in result";
1365
if (! safe_query_l('date_format_inresult',
1366
"insert into crash_me_d values($dateexample) "))
1367
{
1368
die "Cannot insert date ($dateexample):".$last_error;
1369
};
1370
my $sth= $dbh->prepare("select a from crash_me_d");
1371
add_log('date_format_inresult',"< select a from crash_me_d");
1372
$sth->execute;
1373
$_= $sth->fetchrow_array;
1374
add_log('date_format_inresult',"> $_");
1375
safe_query_l($key,"delete from crash_me_d");
1376
if (/\d{4}-\d{2}-\d{2}/){ save_config_data($key,"iso",$prompt);}
1377
elsif (/\d{2}-\d{2}-\d{2}/){ save_config_data($key,"short iso",$prompt);}
1378
elsif (/\d{2}\.\d{2}\.\d{4}/){ save_config_data($key,"euro",$prompt);}
1379
elsif (/\d{2}\.\d{2}\.\d{2}/){ save_config_data($key,"short euro",$prompt);}
1380
elsif (/\d{2}\/\d{2}\/\d{4}/){ save_config_data($key,"usa",$prompt);}
1381
elsif (/\d{2}\/\d{2}\/\d{2}/){ save_config_data($key,"short usa",$prompt);}
1382
elsif (/\d*/){ save_config_data($key,"YYYYMMDD",$prompt);}
1383
else { save_config_data($key,"unknown",$prompt);};
1384
$sth->finish;
1385
1386
check_and_report("Supports YYYY-MM-DD (ISO) format","date_format_ISO",
1387
[ "insert into crash_me_d(a) values ('1963-08-16')"],
1388
"select a from crash_me_d",
1389
["delete from crash_me_d"],
1390
make_date_r(1963,8,16),1);
1391
1392
check_and_report("Supports DATE 'YYYY-MM-DD' (ISO) format",
1393
"date_format_ISO_with_date",
1394
[ "insert into crash_me_d(a) values (DATE '1963-08-16')"],
1395
"select a from crash_me_d",
1396
["delete from crash_me_d"],
1397
make_date_r(1963,8,16),1);
1398
1399
check_and_report("Supports DD.MM.YYYY (EUR) format","date_format_EUR",
1400
[ "insert into crash_me_d(a) values ('16.08.1963')"],
1401
"select a from crash_me_d",
1402
["delete from crash_me_d"],
1403
make_date_r(1963,8,16),1);
1404
check_and_report("Supports DATE 'DD.MM.YYYY' (EUR) format",
1405
"date_format_EUR_with_date",
1406
[ "insert into crash_me_d(a) values (DATE '16.08.1963')"],
1407
"select a from crash_me_d",
1408
["delete from crash_me_d"],
1409
make_date_r(1963,8,16),1);
1410
1411
check_and_report("Supports YYYYMMDD format",
1412
"date_format_YYYYMMDD",
1413
[ "insert into crash_me_d(a) values ('19630816')"],
1414
"select a from crash_me_d",
1415
["delete from crash_me_d"],
1416
make_date_r(1963,8,16),1);
1417
check_and_report("Supports DATE 'YYYYMMDD' format",
1418
"date_format_YYYYMMDD_with_date",
1419
[ "insert into crash_me_d(a) values (DATE '19630816')"],
1420
"select a from crash_me_d",
1421
["delete from crash_me_d"],
1422
make_date_r(1963,8,16),1);
1423
1424
check_and_report("Supports MM/DD/YYYY format",
1425
"date_format_USA",
1426
[ "insert into crash_me_d(a) values ('08/16/1963')"],
1427
"select a from crash_me_d",
1428
["delete from crash_me_d"],
1429
make_date_r(1963,8,16),1);
1430
check_and_report("Supports DATE 'MM/DD/YYYY' format",
1431
"date_format_USA_with_date",
1432
[ "insert into crash_me_d(a) values (DATE '08/16/1963')"],
1433
"select a from crash_me_d",
1434
["delete from crash_me_d"],
1435
make_date_r(1963,8,16),1);
1436
1437
1438
1439
1440
check_and_report("Supports 0000-00-00 dates","date_zero",
1441
["create table crash_me2 (a date not null)",
1442
"insert into crash_me2 values (".make_date(0,0,0).")"],
1443
"select a from crash_me2",
1444
["drop table crash_me2 $drop_attr"],
1445
make_date_r(0,0,0),1);
1446
1447
check_and_report("Supports 0001-01-01 dates","date_one",
1448
["create table crash_me2 (a date not null)",
1449
"insert into crash_me2 values (".make_date(1,1,1).")"],
1450
"select a from crash_me2",
1451
["drop table crash_me2 $drop_attr"],
1452
make_date_r(1,1,1),1);
1453
1454
check_and_report("Supports 9999-12-31 dates","date_last",
1455
["create table crash_me2 (a date not null)",
1456
"insert into crash_me2 values (".make_date(9999,12,31).")"],
1457
"select a from crash_me2",
1458
["drop table crash_me2 $drop_attr"],
1459
make_date_r(9999,12,31),1);
1460
1461
check_and_report("Supports 'infinity dates","date_infinity",
1462
["create table crash_me2 (a date not null)",
1463
"insert into crash_me2 values ('infinity')"],
1464
"select a from crash_me2",
1465
["drop table crash_me2 $drop_attr"],
1466
"infinity",1);
1467
1468
if (!defined($limits{'date_with_YY'}))
1469
{
1470
check_and_report("Supports YY-MM-DD dates","date_with_YY",
1471
["create table crash_me2 (a date not null)",
1472
"insert into crash_me2 values ('98-03-03')"],
1473
"select a from crash_me2",
1474
["drop table crash_me2 $drop_attr"],
1475
make_date_r(1998,3,3),5);
1476
if ($limits{'date_with_YY'} eq "yes")
1477
{
1478
undef($limits{'date_with_YY'});
1479
check_and_report("Supports YY-MM-DD 2000 compilant dates",
1480
"date_with_YY",
1481
["create table crash_me2 (a date not null)",
1482
"insert into crash_me2 values ('10-03-03')"],
1483
"select a from crash_me2",
1484
["drop table crash_me2 $drop_attr"],
1485
make_date_r(2010,3,3),5);
1486
}
1487
}
1488
1489
# Test: WEEK()
1490
{
1491
my $result="no";
1492
my $error;
1493
print "WEEK:";
1494
save_incomplete('func_odbc_week','WEEK');
1495
$error = safe_query_result_l('func_odbc_week',
1496
"select week(".make_date(1997,2,1).") $end_query",5,0);
1497
# actually this query must return 4 or 5 in the $last_result,
1498
# $error can be 1 (not supported at all) , -1 ( probably USA weeks)
1499
# and 0 - EURO weeks
1500
if ($error == -1) {
1501
if ($last_result == 4) {
1502
$result = 'USA';
1503
} else {
1504
$result='error';
1505
add_log('func_odbc_week',
1506
" must return 4 or 5, but $last_result");
1507
}
1508
} elsif ($error == 0) {
1509
$result = 'EURO';
1510
}
1511
print " $result\n";
1512
save_config_data('func_odbc_week',$result,"WEEK");
1513
}
1514
1515
my $insert_query ='insert into crash_me_d values('.
1516
make_date(1997,2,1).')';
1517
safe_query($insert_query);
1518
1519
foreach $fn ( (
1520
["DAYNAME","dayname","dayname(a)","",2],
1521
["MONTH","month","month(a)","",2],
1522
["MONTHNAME","monthname","monthname(a)","",2],
1523
["DAYOFMONTH","dayofmonth","dayofmonth(a)",1,0],
1524
["DAYOFWEEK","dayofweek","dayofweek(a)",7,0],
1525
["DAYOFYEAR","dayofyear","dayofyear(a)",32,0],
1526
["QUARTER","quarter","quarter(a)",1,0],
1527
["YEAR","year","year(a)",1997,0]))
1528
{
1529
$prompt='Function '.$fn->[0];
1530
$key='func_odbc_'.$fn->[1];
1531
add_log($key,"< ".$insert_query);
1532
check_and_report($prompt,$key,
1533
[],"select ".$fn->[2]." from crash_me_d",[],
1534
$fn->[3],$fn->[4]
1535
);
1536
1537
};
1538
safe_query(['delete from crash_me_d',
1539
'insert into crash_me_d values('.make_date(1963,8,16).')']);
1540
foreach $fn ((
1541
["DATEADD","dateadd","dateadd(day,3,make_date(1997,11,30))",0,2],
1542
["MDY","mdy","mdy(7,1,1998)","make_date_r(1998,07,01)",0], # informix
1543
["DATEDIFF","datediff",
1544
"datediff(month,'Oct 21 1997','Nov 30 1997')",0,2],
1545
["DATENAME","datename","datename(month,'Nov 30 1997')",0,2],
1546
["DATEPART","datepart","datepart(month,'July 20 1997')",0,2],
1547
["DATE_FORMAT","date_format",
1548
"date_format('1997-01-02 03:04:05','M W D Y y m d h i s w')", 0,2],
1549
["FROM_DAYS","from_days",
1550
"from_days(729024)","make_date_r(1996,1,1)",1],
1551
["FROM_UNIXTIME","from_unixtime","from_unixtime(0)",0,2],
1552
["MONTHS_BETWEEN","months_between",
1553
"months_between(make_date(1997,2,2),make_date(1997,1,1))",
1554
"1.03225806",0], # oracle number of months between 2 dates
1555
["PERIOD_ADD","period_add","period_add(9602,-12)",199502,0],
1556
["PERIOD_DIFF","period_diff","period_diff(199505,199404)",13,0],
1557
["WEEKDAY","weekday","weekday(make_date(1997,11,29))",5,0],
1558
["ADDDATE",'adddate',
1559
"ADDDATE(make_date(2002,12,01),3)",'make_date_r(2002,12,04)',0],
1560
["SUBDATE",'subdate',
1561
"SUBDATE(make_date(2002,12,04),3)",'make_date_r(2002,12,01)',0],
1562
["DATEDIFF (2 arg)",'datediff2arg',
1563
"DATEDIFF(make_date(2002,12,04),make_date(2002,12,01))",'3',0],
1564
["WEEKOFYEAR",'weekofyear',
1565
"WEEKOFYEAR(make_date(1963,08,16))",'33',0],
1566
# table crash_me_d must contain record with 1963-08-16 (for CHAR)
1567
["CHAR (conversation date)",'char_date',
1568
"CHAR(a,EUR)",'16.08.1963',0],
1569
["MAKEDATE",'makedate',"MAKEDATE(1963,228)"
1570
,'make_date_r(1963,08,16)',0],
1571
["TO_DAYS","to_days",
1572
"to_days(make_date(1996,01,01))",729024,0],
1573
["ADD_MONTHS","add_months",
1574
"add_months(make_date(1997,01,01),1)","make_date_r(1997,02,01)",0],
1575
# oracle the date plus n months
1576
["LAST_DAY","last_day",
1577
"last_day(make_date(1997,04,01))","make_date_r(1997,04,30)",0],
1578
# oracle last day of month of date
1579
["DATE",'date',"date(make_date(1963,8,16))",
1580
'make_date_r(1963,8,16)',0],
1581
["DAY",'day',"DAY(make_date(2002,12,01))",1,0]))
1582
{
1583
$prompt='Function '.$fn->[0];
1584
$key='func_extra_'.$fn->[1];
1585
my $qry="select ".$fn->[2]." from crash_me_d";
1586
while( $qry =~ /^(.*)make_date\((\d+),(\d+),(\d+)\)(.*)$/)
1587
{
1588
my $dt= &make_date($2,$3,$4);
1589
$qry=$1.$dt.$5;
1590
};
1591
my $result=$fn->[3];
1592
while( $result =~ /^(.*)make_date_r\((\d+),(\d+),(\d+)\)(.*)$/)
1593
{
1594
my $dt= &make_date_r($2,$3,$4);
1595
$result=$1.$dt.$5;
1596
};
1597
check_and_report($prompt,$key,
1598
[],$qry,[],
1599
$result,$fn->[4]
1600
);
1601
1602
}
1603
1604
safe_query("drop table crash_me_d $drop_attr");
1605
1606
}
1607
1608
if ($limits{'type_sql_time'} eq 'yes')
1609
{ #
1610
# Checking the format of date in result.
1611
1612
safe_query("drop table crash_me_t $drop_attr");
1613
assert_crash("create table crash_me_t (a time)");
1614
# find the example of time
1615
my $timeexample;
1616
if ($limits{'func_sql_current_time'} eq 'yes') {
1617
$timeexample='CURRENT_TIME';
1618
}
1619
elsif ($limits{'func_odbc_curtime'} eq 'yes') {
1620
$timeexample='curtime()';
1621
}
1622
elsif ($limits{'func_sql_localtime'} eq 'yes') {
1623
$timeexample='localtime';
1624
}
1625
elsif ($limits{'func_odbc_now'} eq 'yes') {
1626
$timeexample='now()';
1627
} else {
1628
#try to guess
1629
$timeexample="'02:55:12'";
1630
} ;
1631
1632
my $key = 'time_format_inresult';
1633
my $prompt = "Time format in result";
1634
if (! safe_query_l('time_format_inresult',
1635
"insert into crash_me_t values($timeexample) "))
1636
{
1637
die "Cannot insert time ($timeexample):".$last_error;
1638
};
1639
my $sth= $dbh->prepare("select a from crash_me_t");
1640
add_log('time_format_inresult',"< select a from crash_me_t");
1641
$sth->execute;
1642
$_= $sth->fetchrow_array;
1643
add_log('time_format_inresult',"> $_");
1644
safe_query_l($key,"delete from crash_me_t");
1645
if (/\d{2}:\d{2}:\d{2}/){ save_config_data($key,"iso",$prompt);}
1646
elsif (/\d{2}\.\d{2}\.\d{2}/){ save_config_data($key,"euro",$prompt);}
1647
elsif (/\d{2}:\d{2}\s+(AM|PM)/i){ save_config_data($key,"usa",$prompt);}
1648
elsif (/\d{8}$/){ save_config_data($key,"HHHHMMSS",$prompt);}
1649
elsif (/\d{4}$/){ save_config_data($key,"HHMMSS",$prompt);}
1650
else { save_config_data($key,"unknown",$prompt);};
1651
$sth->finish;
1652
1653
check_and_report("Supports HH:MM:SS (ISO) time format","time_format_ISO",
1654
[ "insert into crash_me_t(a) values ('20:08:16')"],
1655
"select a from crash_me_t",
1656
["delete from crash_me_t"],
1657
make_time_r(20,8,16),1);
1658
1659
check_and_report("Supports HH.MM.SS (EUR) time format","time_format_EUR",
1660
[ "insert into crash_me_t(a) values ('20.08.16')"],
1661
"select a from crash_me_t",
1662
["delete from crash_me_t"],
1663
make_time_r(20,8,16),1);
1664
1665
check_and_report("Supports HHHHmmSS time format",
1666
"time_format_HHHHMMSS",
1667
[ "insert into crash_me_t(a) values ('00200816')"],
1668
"select a from crash_me_t",
1669
["delete from crash_me_t"],
1670
make_time_r(20,8,16),1);
1671
1672
check_and_report("Supports HHmmSS time format",
1673
"time_format_HHHHMMSS",
1674
[ "insert into crash_me_t(a) values ('200816')"],
1675
"select a from crash_me_t",
1676
["delete from crash_me_t"],
1677
make_time_r(20,8,16),1);
1678
1679
check_and_report("Supports HH:MM:SS (AM|PM) time format",
1680
"time_format_USA",
1681
[ "insert into crash_me_t(a) values ('08:08:16 PM')"],
1682
"select a from crash_me_t",
1683
["delete from crash_me_t"],
1684
make_time_r(20,8,16),1);
1685
1686
my $insert_query ='insert into crash_me_t values('.
1687
make_time(20,8,16).')';
1688
safe_query($insert_query);
1689
1690
foreach $fn ( (
1691
["HOUR","hour","hour('".make_time(12,13,14)."')",12,0],
1692
["ANSI HOUR","hour_time","hour(TIME '".make_time(12,13,14)."')",12,0],
1693
["MINUTE","minute","minute('".make_time(12,13,14)."')",13,0],
1694
["SECOND","second","second('".make_time(12,13,14)."')",14,0]
1695
1696
))
1697
{
1698
$prompt='Function '.$fn->[0];
1699
$key='func_odbc_'.$fn->[1];
1700
add_log($key,"< ".$insert_query);
1701
check_and_report($prompt,$key,
1702
[],"select ".$fn->[2]." $end_query",[],
1703
$fn->[3],$fn->[4]
1704
);
1705
1706
};
1707
# safe_query(['delete from crash_me_t',
1708
# 'insert into crash_me_t values('.make_time(20,8,16).')']);
1709
foreach $fn ((
1710
["TIME_TO_SEC","time_to_sec","time_to_sec('".
1711
make_time(1,23,21)."')","5001",0],
1712
["SEC_TO_TIME","sec_to_time","sec_to_time(5001)",
1713
make_time_r(01,23,21),1],
1714
["ADDTIME",'addtime',"ADDTIME('".make_time(20,2,12).
1715
"','".make_time(0,0,3)."')",make_time_r(20,2,15),0],
1716
["SUBTIME",'subtime',"SUBTIME('".make_time(20,2,15)
1717
."','".make_time(0,0,3)."')",make_time_r(20,2,12),0],
1718
["TIMEDIFF",'timediff',"TIMEDIFF('".make_time(20,2,15)."','".
1719
make_time(20,2,12)."')",make_time_r(0,0,3),0],
1720
["MAKETIME",'maketime',"MAKETIME(20,02,12)",make_time_r(20,2,12),0],
1721
["TIME",'time',"time('".make_time(20,2,12)."')",make_time_r(20,2,12),0]
1722
))
1723
{
1724
$prompt='Function '.$fn->[0];
1725
$key='func_extra_'.$fn->[1];
1726
my $qry="select ".$fn->[2]." $end_query";
1727
my $result=$fn->[3];
1728
check_and_report($prompt,$key,
1729
[],$qry,[],
1730
$result,$fn->[4]
1731
);
1732
1733
}
1734
1735
safe_query("drop table crash_me_t $drop_attr");
1736
1737
}
1738
1739
1740
# NOT id BETWEEN a and b
1741
if ($limits{'func_where_not_between'} eq 'yes')
1742
{
1743
my $result = 'error';
1744
my $err;
1745
my $key='not_id_between';
1746
my $prompt='NOT ID BETWEEN interprets as ID NOT BETWEEN';
1747
print "$prompt:";
1748
save_incomplete($key,$prompt);
1749
safe_query_l($key,["create table crash_me_b (i int)",
1750
"insert into crash_me_b values(2)",
1751
"insert into crash_me_b values(5)"]);
1752
$err =safe_query_result_l($key,
1753
"select i from crash_me_b where not i between 1 and 3",
1754
5,0);
1755
if ($err eq 1) {
1756
if (not defined($last_result)) {
1757
$result='no';
1758
};
1759
};
1760
if ( $err eq 0) {
1761
$result = 'yes';
1762
};
1763
safe_query_l($key,["drop table crash_me_b"]);
1764
save_config_data($key,$result,$prompt);
1765
print "$result\n";
1766
};
1767
1768
1769
1770
1771
report("LIKE on numbers","like_with_number",
1772
"create table crash_q (a int,b int)",
1773
"insert into crash_q values(10,10)",
1774
"select * from crash_q where a like '10'",
1775
"drop table crash_q $drop_attr");
1776
1777
report("column LIKE column","like_with_column",
1778
"create table crash_q (a char(10),b char(10))",
1779
"insert into crash_q values('abc','abc')",
1780
"select * from crash_q where a like b",
1781
"drop table crash_q $drop_attr");
1782
1783
report("update of column= -column","NEG",
1784
"create table crash_q (a integer)",
1785
"insert into crash_q values(10)",
1786
"update crash_q set a=-a",
1787
"drop table crash_q $drop_attr");
1788
1789
if ($limits{'func_odbc_left'} eq 'yes' ||
1790
$limits{'func_odbc_substring'} eq 'yes')
1791
{
1792
my $type= ($limits{'func_odbc_left'} eq 'yes' ?
1793
"left(a,4)" : "substring(a for 4)");
1794
1795
check_and_report("String functions on date columns","date_as_string",
1796
["create table crash_me2 (a date not null)",
1797
"insert into crash_me2 values ('1998-03-03')"],
1798
"select $type from crash_me2",
1799
["drop table crash_me2 $drop_attr"],
1800
"1998",1);
1801
}
1802
1803
1804
$tmp=sql_concat("b","b");
1805
if (defined($tmp))
1806
{
1807
check_and_report("char are space filled","char_is_space_filled",
1808
[],"select $tmp from crash_me where b = 'a '",[],
1809
'a a ',6);
1810
}
1811
1812
if (!defined($limits{'multi_table_update'}))
1813
{
1814
if (check_and_report("Update with many tables","multi_table_update",
1815
["create table crash_q (a integer,b char(10))",
1816
"insert into crash_q values(1,'c')",
1817
"update crash_q left join crash_me on crash_q.a=crash_me.a set crash_q.b=crash_me.b"],
1818
"select b from crash_q",
1819
["drop table crash_q $drop_attr"],
1820
"a",1,undef(),2))
1821
{
1822
check_and_report("Update with many tables","multi_table_update",
1823
["create table crash_q (a integer,b char(10))",
1824
"insert into crash_q values(1,'c')",
1825
"update crash_q,crash_me set crash_q.b=crash_me.b ".
1826
"where crash_q.a=crash_me.a"],
1827
"select b from crash_q",
1828
["drop table crash_q $drop_attr"],
1829
"a",1,
1830
1);
1831
}
1832
}
1833
1834
report("DELETE FROM table1,table2...","multi_table_delete",
1835
"create table crash_q (a integer,b char(10))",
1836
"insert into crash_q values(1,'c')",
1837
"delete crash_q.* from crash_q,crash_me where crash_q.a=crash_me.a",
1838
"drop table crash_q $drop_attr");
1839
1840
check_and_report("Update with sub select","select_table_update",
1841
["create table crash_q (a integer,b char(10))",
1842
"insert into crash_q values(1,'c')",
1843
"update crash_q set b= ".
1844
"(select b from crash_me where crash_q.a = crash_me.a)"],
1845
"select b from crash_q",
1846
["drop table crash_q $drop_attr"],
1847
"a",1);
1848
1849
check_and_report("Calculate 1--1","minus_neg",[],
1850
"select a--1 from crash_me",[],0,2);
1851
1852
report("ANSI SQL simple joins","simple_joins",
1853
"select crash_me.a from crash_me, crash_me t0");
1854
1855
#
1856
# Check max string size, and expression limits
1857
#
1858
$found=undef;
1859
foreach $type (('mediumtext','text','text()','blob','long'))
1860
{
1861
if ($limits{"type_extra_$type"} eq 'yes')
1862
{
1863
$found=$type;
1864
last;
1865
}
1866
}
1867
if (defined($found))
1868
{
1869
$found =~ s/\(\)/\(%d\)/;
1870
find_limit("max text or blob size","max_text_size",
1871
new query_many(["create table crash_q (q $found)",
1872
"insert into crash_q values ('%s')"],
1873
"select * from crash_q","%s",
1874
["drop table crash_q $drop_attr"],
1875
min($max_string_size,$limits{'query_size'}-30)));
1876
1877
}
1878
1879
# It doesn't make lots of sense to check for string lengths much bigger than
1880
# what can be stored...
1881
1882
find_limit(($prompt="constant string size in where"),"where_string_size",
1883
new query_repeat([],"select a from crash_me where b >='",
1884
"","","1","","'"));
1885
if ($limits{'where_string_size'} == 10)
1886
{
1887
save_config_data('where_string_size','nonstandard',$prompt);
1888
}
1889
1890
if ($limits{'select_constants'} eq 'yes')
1891
{
1892
find_limit("constant string size in SELECT","select_string_size",
1893
new query_repeat([],"select '","","","a","","'$end_query"));
1894
}
1895
1896
goto no_functions if ($limits{'functions'} ne "yes");
1897
1898
if ($limits{'func_odbc_repeat'} eq 'yes')
1899
{
1900
find_limit("return string size from function","repeat_string_size",
1901
new query_many([],
1902
"select repeat('a',%d) $end_query","%s",
1903
[],
1904
$max_string_size,0));
1905
}
1906
1907
$tmp=find_limit("simple expressions","max_expressions",
1908
new query_repeat([],"select 1","","","+1","",$end_query,
1909
undef(),$max_expressions));
1910
1911
if ($tmp > 10)
1912
{
1913
$tmp= "(1" . ( '+1' x ($tmp-10) ) . ")";
1914
find_limit("big expressions", "max_big_expressions",
1915
new query_repeat([],"select 0","","","+$tmp","",$end_query,
1916
undef(),$max_big_expressions));
1917
}
1918
1919
find_limit("stacked expressions", "max_stack_expression",
1920
new query_repeat([],"select 1","","","+(1",")",$end_query,
1921
undef(),$max_stacked_expressions));
1922
1923
no_functions:
1924
1925
if (!defined($limits{'max_conditions'}))
1926
{
1927
find_limit("OR and AND in WHERE","max_conditions",
1928
new query_repeat([],
1929
"select a from crash_me where a=1 and b='a'","",
1930
"", " or a=%d and b='%d'","","","",
1931
[],($query_size-42)/29,undef,2));
1932
$limits{'max_conditions'}*=2;
1933
}
1934
# The 42 is the length of the constant part.
1935
# The 29 is the length of the variable part, plus two seven-digit numbers.
1936
1937
find_limit("tables in join", "join_tables",
1938
new query_repeat([],
1939
"select crash_me.a",",t%d.a","from crash_me",
1940
",crash_me t%d","","",[],$max_join_tables,undef,
1941
1));
1942
1943
# Different CREATE TABLE options
1944
1945
report("primary key in create table",'primary_key_in_create',
1946
"create table crash_q (q integer not null,primary key (q))",
1947
"drop table crash_q $drop_attr");
1948
1949
report("unique in create table",'unique_in_create',
1950
"create table crash_q (q integer not null,unique (q))",
1951
"drop table crash_q $drop_attr");
1952
1953
if ($limits{'unique_in_create'} eq 'yes')
1954
{
1955
report("unique null in create",'unique_null_in_create',
1956
"create table crash_q (q integer,unique (q))",
1957
"insert into crash_q (q) values (NULL)",
1958
"insert into crash_q (q) values (NULL)",
1959
"insert into crash_q (q) values (1)",
1960
"drop table crash_q $drop_attr");
1961
}
1962
1963
report("default value for column",'create_default',
1964
"create table crash_q (q integer default 10 not null)",
1965
"drop table crash_q $drop_attr");
1966
1967
report("default value function for column",'create_default_func',
1968
"create table crash_q (q integer not null,q1 integer default (1+1))",
1969
"drop table crash_q $drop_attr");
1970
1971
report("temporary tables",'temporary_table',
1972
"create temporary table crash_q (q integer not null)",
1973
"drop table crash_q $drop_attr");
1974
1975
report_one("create table from select",'create_table_select',
1976
[["create table crash_q SELECT * from crash_me","yes"],
1977
["create table crash_q AS SELECT * from crash_me","with AS"]]);
1978
$dbh->do("drop table crash_q $drop_attr");
1979
1980
report("index in create table",'index_in_create',
1981
"create table crash_q (q integer not null,index (q))",
1982
"drop table crash_q $drop_attr");
1983
1984
# The following must be executed as we need the value of end_drop_keyword
1985
# later
1986
if (!(defined($limits{'create_index'}) && defined($limits{'drop_index'})))
1987
{
1988
if ($res=safe_query_l('create_index',"create index crash_q on crash_me (a)"))
1989
{
1990
$res="yes";
1991
$drop_res="yes";
1992
$end_drop_keyword="";
1993
if (!safe_query_l('drop_index',"drop index crash_q"))
1994
{
1995
# Can't drop the standard way; Check if mSQL
1996
if (safe_query_l('drop_index',"drop index crash_q from crash_me"))
1997
{
1998
$drop_res="with 'FROM'"; # Drop is not ANSI SQL
1999
$end_drop_keyword="drop index %i from %t";
2000
}
2001
# else check if Access or MySQL
2002
elsif (safe_query_l('drop_index',"drop index crash_q on crash_me"))
2003
{
2004
$drop_res="with 'ON'"; # Drop is not ANSI SQL
2005
$end_drop_keyword="drop index %i on %t";
2006
}
2007
# else check if MS-SQL
2008
elsif (safe_query_l('drop_index',"drop index crash_me.crash_q"))
2009
{
2010
$drop_res="with 'table.index'"; # Drop is not ANSI SQL
2011
$end_drop_keyword="drop index %t.%i";
2012
}
2013
}
2014
else
2015
{
2016
# Old MySQL 3.21 supports only the create index syntax
2017
# This means that the second create doesn't give an error.
2018
$res=safe_query_l('create_index',["create index crash_q on crash_me (a)",
2019
"create index crash_q on crash_me (a)",
2020
"drop index crash_q"]);
2021
$res= $res ? 'ignored' : 'yes';
2022
}
2023
}
2024
else
2025
{
2026
$drop_res=$res='no';
2027
}
2028
save_config_data('create_index',$res,"create index");
2029
save_config_data('drop_index',$drop_res,"drop index");
2030
2031
print "create index: $limits{'create_index'}\n";
2032
print "drop index: $limits{'drop_index'}\n";
2033
}
2034
2035
# check if we can have 'NULL' as a key
2036
check_and_report("null in index","null_in_index",
2037
[create_table("crash_q",["a char(10)"],["(a)"]),
2038
"insert into crash_q values (NULL)"],
2039
"select * from crash_q",
2040
["drop table crash_q $drop_attr"],
2041
undef(),4);
2042
2043
if ($limits{'unique_in_create'} eq 'yes')
2044
{
2045
report("null in unique index",'null_in_unique',
2046
create_table("crash_q",["q integer"],["unique(q)"]),
2047
"insert into crash_q (q) values(NULL)",
2048
"insert into crash_q (q) values(NULL)",
2049
"drop table crash_q $drop_attr");
2050
report("null combination in unique index",'nulls_in_unique',
2051
create_table("crash_q",["q integer,q1 integer"],["unique(q,q1)"]),
2052
"insert into crash_q (q,q1) values(1,NULL)",
2053
"insert into crash_q (q,q1) values(1,NULL)",
2054
"drop table crash_q $drop_attr");
2055
}
2056
2057
if ($limits{'null_in_unique'} eq 'yes')
2058
{
2059
report("null in unique index",'multi_null_in_unique',
2060
create_table("crash_q",["q integer, x integer"],["unique(q)"]),
2061
"insert into crash_q(x) values(1)",
2062
"insert into crash_q(x) values(2)",
2063
"drop table crash_q $drop_attr");
2064
}
2065
2066
if ($limits{'create_index'} ne 'no')
2067
{
2068
$end_drop=$end_drop_keyword;
2069
$end_drop =~ s/%i/crash_q/;
2070
$end_drop =~ s/%t/crash_me/;
2071
report("index on column part (extension)","index_parts",,
2072
"create index crash_q on crash_me (b(5))",
2073
$end_drop);
2074
$end_drop=$end_drop_keyword;
2075
$end_drop =~ s/%i/crash_me/;
2076
$end_drop =~ s/%t/crash_me/;
2077
report("different namespace for index",
2078
"index_namespace",
2079
"create index crash_me on crash_me (b)",
2080
$end_drop);
2081
}
2082
2083
if (!report("case independent table names","table_name_case",
2084
"create table crash_q (q integer)",
2085
"drop table CRASH_Q $drop_attr"))
2086
{
2087
safe_query("drop table crash_q $drop_attr");
2088
}
2089
2090
if (!report("case independent field names","field_name_case",
2091
"create table crash_q (q integer)",
2092
"insert into crash_q(Q) values (1)",
2093
"drop table crash_q $drop_attr"))
2094
{
2095
safe_query("drop table crash_q $drop_attr");
2096
}
2097
2098
if (!report("drop table if exists","drop_if_exists",
2099
"create table crash_q (q integer)",
2100
"drop table if exists crash_q $drop_attr"))
2101
{
2102
safe_query("drop table crash_q $drop_attr");
2103
}
2104
2105
report("create table if not exists","create_if_not_exists",
2106
"create table crash_q (q integer)",
2107
"create table if not exists crash_q (q integer)");
2108
safe_query("drop table crash_q $drop_attr");
2109
2110
#
2111
# test of different join types
2112
#
2113
2114
assert_crash("create table crash_me2 (a integer not null,b char(10) not null,".
2115
" c1 integer)");
2116
assert_crash("insert into crash_me2 (a,b,c1) values (1,'b',1)");
2117
assert_crash("create table crash_me3 (a integer not null,b char(10) not null)");
2118
assert_crash("insert into crash_me3 (a,b) values (1,'b')");
2119
2120
report("inner join","inner_join",
2121
"select crash_me.a from crash_me inner join crash_me2 ON ".
2122
"crash_me.a=crash_me2.a");
2123
report("left outer join","left_outer_join",
2124
"select crash_me.a from crash_me left join crash_me2 ON ".
2125
"crash_me.a=crash_me2.a");
2126
report("natural left outer join","natural_left_outer_join",
2127
"select c1 from crash_me natural left join crash_me2");
2128
report("left outer join using","left_outer_join_using",
2129
"select c1 from crash_me left join crash_me2 using (a)");
2130
report("left outer join odbc style","odbc_left_outer_join",
2131
"select crash_me.a from { oj crash_me left outer join crash_me2 ON".
2132
" crash_me.a=crash_me2.a }");
2133
report("right outer join","right_outer_join",
2134
"select crash_me.a from crash_me right join crash_me2 ON ".
2135
"crash_me.a=crash_me2.a");
2136
report("full outer join","full_outer_join",
2137
"select crash_me.a from crash_me full join crash_me2 ON "."
2138
crash_me.a=crash_me2.a");
2139
report("cross join (same as from a,b)","cross_join",
2140
"select crash_me.a from crash_me cross join crash_me3");
2141
report("natural join","natural_join",
2142
"select * from crash_me natural join crash_me3");
2143
report("union","union",
2144
"select * from crash_me union select a,b from crash_me3");
2145
report("union all","union_all",
2146
"select * from crash_me union all select a,b from crash_me3");
2147
report("intersect","intersect",
2148
"select * from crash_me intersect select * from crash_me3");
2149
report("intersect all","intersect_all",
2150
"select * from crash_me intersect all select * from crash_me3");
2151
report("except","except",
2152
"select * from crash_me except select * from crash_me3");
2153
report("except all","except_all",
2154
"select * from crash_me except all select * from crash_me3");
2155
report("except","except",
2156
"select * from crash_me except select * from crash_me3");
2157
report("except all","except_all",
2158
"select * from crash_me except all select * from crash_me3");
2159
report("minus","minus",
2160
"select * from crash_me minus select * from crash_me3"); # oracle ...
2161
2162
report("natural join (incompatible lists)","natural_join_incompat",
2163
"select c1 from crash_me natural join crash_me2");
2164
report("union (incompatible lists)","union_incompat",
2165
"select * from crash_me union select a,b from crash_me2");
2166
report("union all (incompatible lists)","union_all_incompat",
2167
"select * from crash_me union all select a,b from crash_me2");
2168
report("intersect (incompatible lists)","intersect_incompat",
2169
"select * from crash_me intersect select * from crash_me2");
2170
report("intersect all (incompatible lists)","intersect_all_incompat",
2171
"select * from crash_me intersect all select * from crash_me2");
2172
report("except (incompatible lists)","except_incompat",
2173
"select * from crash_me except select * from crash_me2");
2174
report("except all (incompatible lists)","except_all_incompat",
2175
"select * from crash_me except all select * from crash_me2");
2176
report("except (incompatible lists)","except_incompat",
2177
"select * from crash_me except select * from crash_me2");
2178
report("except all (incompatible lists)","except_all_incompat",
2179
"select * from crash_me except all select * from crash_me2");
2180
report("minus (incompatible lists)","minus_incompat",
2181
"select * from crash_me minus select * from crash_me2"); # oracle ...
2182
2183
assert_crash("drop table crash_me2 $drop_attr");
2184
assert_crash("drop table crash_me3 $drop_attr");
2185
2186
# somethings to be added here ....
2187
# FOR UNION - INTERSECT - EXCEPT -> CORRESPONDING [ BY ]
2188
# after subqueries:
2189
# >ALL | ANY | SOME - EXISTS - UNIQUE
2190
2191
if (report("subqueries","subqueries",
2192
"select a from crash_me where crash_me.a in ".
2193
"(select max(a) from crash_me)"))
2194
{
2195
$tmp=new query_repeat([],"select a from crash_me","","",
2196
" where a in (select a from crash_me",")",
2197
"",[],$max_join_tables);
2198
find_limit("recursive subqueries", "recursive_subqueries",$tmp);
2199
}
2200
2201
report("insert INTO ... SELECT ...","insert_select",
2202
"create table crash_q (a int)",
2203
"insert into crash_q (a) SELECT crash_me.a from crash_me",
2204
"drop table crash_q $drop_attr");
2205
2206
if (!defined($limits{"transactions"}))
2207
{
2208
my ($limit,$type);
2209
$limit="transactions";
2210
$limit_r="rollback_metadata";
2211
print "$limit: ";
2212
foreach $type (('', 'type=bdb', 'type=innodb', 'type=gemini'))
2213
{
2214
undef($limits{$limit});
2215
if (!report_trans($limit,
2216
[create_table("crash_q",["a integer not null"],[],
2217
$type),
2218
"insert into crash_q values (1)"],
2219
"select * from crash_q",
2220
"drop table crash_q $drop_attr"
2221
))
2222
{
2223
report_rollback($limit_r,
2224
[create_table("crash_q",["a integer not null"],[],
2225
$type)],
2226
"insert into crash_q values (1)",
2227
"drop table crash_q $drop_attr" );
2228
last;
2229
};
2230
}
2231
print "$limits{$limit}\n";
2232
print "$limit_r: $limits{$limit_r}\n";
2233
}
2234
2235
report("atomic updates","atomic_updates",
2236
create_table("crash_q",["a integer not null"],["primary key (a)"]),
2237
"insert into crash_q values (2)",
2238
"insert into crash_q values (3)",
2239
"insert into crash_q values (1)",
2240
"update crash_q set a=a+1",
2241
"drop table crash_q $drop_attr");
2242
2243
if ($limits{'atomic_updates'} eq 'yes')
2244
{
2245
report_fail("atomic_updates_with_rollback","atomic_updates_with_rollback",
2246
create_table("crash_q",["a integer not null"],
2247
["primary key (a)"]),
2248
"insert into crash_q values (2)",
2249
"insert into crash_q values (3)",
2250
"insert into crash_q values (1)",
2251
"update crash_q set a=a+1 where a < 3",
2252
"drop table crash_q $drop_attr");
2253
}
2254
2255
# To add with the views:
2256
# DROP VIEW - CREAT VIEW *** [ WITH [ CASCADE | LOCAL ] CHECK OPTION ]
2257
report("views","views",
2258
"create view crash_q as select a from crash_me",
2259
"drop view crash_q $drop_attr");
2260
2261
# Test: foreign key
2262
{
2263
my $result = 'undefined';
2264
my $error;
2265
print "foreign keys: ";
2266
save_incomplete('foreign_key','foreign keys');
2267
2268
# 1) check if foreign keys are supported
2269
safe_query_l('foreign_key',
2270
create_table("crash_me_qf",
2271
["a integer not null"],
2272
["primary key (a)"]));
2273
$error= safe_query_l('foreign_key',
2274
create_table("crash_me_qf2",
2275
["a integer not null",
2276
"foreign key (a) references crash_me_qf (a)"],
2277
[]));
2278
2279
if ($error == 1) # OK -- syntax is supported
2280
{
2281
$result = 'error';
2282
# now check if foreign key really works
2283
safe_query_l('foreign_key', "insert into crash_me_qf values (1)");
2284
if (safe_query_l('foreign_key', "insert into crash_me_qf2 values (2)") eq 1)
2285
{
2286
$result = 'syntax only';
2287
}
2288
else
2289
{
2290
$result = 'yes';
2291
}
2292
}
2293
else
2294
{
2295
$result = "no";
2296
}
2297
safe_query_l('foreign_key', "drop table crash_me_qf2 $drop_attr");
2298
safe_query_l('foreign_key', "drop table crash_me_qf $drop_attr");
2299
print "$result\n";
2300
save_config_data('foreign_key',$result,"foreign keys");
2301
}
2302
2303
if ($limits{'foreign_key'} eq 'yes')
2304
{
2305
report("allows to update of foreign key values",'foreign_update',
2306
"create table crash_me1 (a int not null primary key)",
2307
"create table crash_me2 (a int not null," .
2308
" foreign key (a) references crash_me1 (a))",
2309
"insert into crash_me1 values (1)",
2310
"insert into crash_me2 values (1)",
2311
"update crash_me1 set a = 2", ## <- must fail
2312
"drop table crash_me2 $drop_attr",
2313
"drop table crash_me1 $drop_attr"
2314
);
2315
}
2316
2317
report("Create SCHEMA","create_schema",
2318
"create schema crash_schema create table crash_q (a int) ".
2319
"create table crash_q2(b int)",
2320
"drop schema crash_schema cascade");
2321
2322
if ($limits{'foreign_key'} eq 'yes')
2323
{
2324
if ($limits{'create_schema'} eq 'yes')
2325
{
2326
report("Circular foreign keys","foreign_key_circular",
2327
"create schema crash_schema create table crash_q ".
2328
"(a int primary key, b int, foreign key (b) references ".
2329
"crash_q2(a)) create table crash_q2(a int, b int, ".
2330
"primary key(a), foreign key (b) references crash_q(a))",
2331
"drop schema crash_schema cascade");
2332
}
2333
}
2334
2335
if ($limits{'func_sql_character_length'} eq 'yes')
2336
{
2337
my $result = 'error';
2338
my ($resultset);
2339
my $key = 'length_of_varchar_field';
2340
my $prompt='CHARACTER_LENGTH(varchar_field)';
2341
print $prompt," = ";
2342
if (!defined($limits{$key})) {
2343
save_incomplete($key,$prompt);
2344
safe_query_l($key,[
2345
"CREATE TABLE crash_me1 (S1 VARCHAR(100))",
2346
"INSERT INTO crash_me1 VALUES ('X')"
2347
]);
2348
my $recset = get_recordset($key,
2349
"SELECT CHARACTER_LENGTH(S1) FROM crash_me1");
2350
print_recordset($key,$recset);
2351
if (defined($recset)){
2352
if ( $recset->[0][0] eq 1 ) {
2353
$result = 'actual length';
2354
} elsif( $recset->[0][0] eq 100 ) {
2355
$result = 'defined length';
2356
};
2357
} else {
2358
add_log($key,$DBI::errstr);
2359
}
2360
safe_query_l($key, "drop table crash_me1 $drop_attr");
2361
save_config_data($key,$result,$prompt);
2362
} else {
2363
$result = $limits{$key};
2364
};
2365
print "$result\n";
2366
}
2367
2368
2369
check_constraint("Column constraints","constraint_check",
2370
"create table crash_q (a int check (a>0))",
2371
"insert into crash_q values(0)",
2372
"drop table crash_q $drop_attr");
2373
2374
2375
check_constraint("Table constraints","constraint_check_table",
2376
"create table crash_q (a int ,b int, check (a>b))",
2377
"insert into crash_q values(0,0)",
2378
"drop table crash_q $drop_attr");
2379
2380
check_constraint("Named constraints","constraint_check_named",
2381
"create table crash_q (a int ,b int, constraint abc check (a>b))",
2382
"insert into crash_q values(0,0)",
2383
"drop table crash_q $drop_attr");
2384
2385
2386
report("NULL constraint (SyBase style)","constraint_null",
2387
"create table crash_q (a int null)",
2388
"drop table crash_q $drop_attr");
2389
2390
report("Triggers (ANSI SQL)","psm_trigger",
2391
"create table crash_q (a int ,b int)",
2392
"create trigger crash_trigger after insert on crash_q referencing ".
2393
"new table as new_a when (localtime > time '18:00:00') ".
2394
"begin atomic end",
2395
"insert into crash_q values(1,2)",
2396
"drop trigger crash_trigger",
2397
"drop table crash_q $drop_attr");
2398
2399
report("PSM procedures (ANSI SQL)","psm_procedures",
2400
"create table crash_q (a int,b int)",
2401
"create procedure crash_proc(in a1 int, in b1 int) language ".
2402
"sql modifies sql data begin declare c1 int; set c1 = a1 + b1;".
2403
" insert into crash_q(a,b) values (a1,c1); end",
2404
"call crash_proc(1,10)",
2405
"drop procedure crash_proc",
2406
"drop table crash_q $drop_attr");
2407
2408
report("PSM modules (ANSI SQL)","psm_modules",
2409
"create table crash_q (a int,b int)",
2410
"create module crash_m declare procedure ".
2411
"crash_proc(in a1 int, in b1 int) language sql modifies sql ".
2412
"data begin declare c1 int; set c1 = a1 + b1; ".
2413
"insert into crash_q(a,b) values (a1,c1); end; ".
2414
"declare procedure crash_proc2(INOUT a int, in b int) ".
2415
"contains sql set a = b + 10; end module",
2416
"call crash_proc(1,10)",
2417
"drop module crash_m cascade",
2418
"drop table crash_q cascade $drop_attr");
2419
2420
report("PSM functions (ANSI SQL)","psm_functions",
2421
"create table crash_q (a int)",
2422
"create function crash_func(in a1 int, in b1 int) returns int".
2423
" language sql deterministic contains sql ".
2424
" begin return a1 * b1; end",
2425
"insert into crash_q values(crash_func(2,4))",
2426
"select a,crash_func(a,2) from crash_q",
2427
"drop function crash_func cascade",
2428
"drop table crash_q $drop_attr");
2429
2430
report("Domains (ANSI SQL)","domains",
2431
"create domain crash_d as varchar(10) default 'Empty' ".
2432
"check (value <> 'abcd')",
2433
"create table crash_q(a crash_d, b int)",
2434
"insert into crash_q(a,b) values('xyz',10)",
2435
"insert into crash_q(b) values(10)",
2436
"drop table crash_q $drop_attr",
2437
"drop domain crash_d");
2438
2439
2440
if (!defined($limits{'lock_tables'}))
2441
{
2442
report("lock table","lock_tables",
2443
"lock table crash_me READ",
2444
"unlock tables");
2445
if ($limits{'lock_tables'} eq 'no')
2446
{
2447
delete $limits{'lock_tables'};
2448
report("lock table","lock_tables",
2449
"lock table crash_me IN SHARE MODE");
2450
}
2451
}
2452
2453
if (!report("many tables to drop table","multi_drop",
2454
"create table crash_q (a int)",
2455
"create table crash_q2 (a int)",
2456
"drop table crash_q,crash_q2 $drop_attr"))
2457
{
2458
$dbh->do("drop table crash_q $drop_attr");
2459
$dbh->do("drop table crash_q2 $drop_attr");
2460
}
2461
2462
if (!report("drop table with cascade/restrict","drop_restrict",
2463
"create table crash_q (a int)",
2464
"drop table crash_q restrict"))
2465
{
2466
$dbh->do("drop table crash_q $drop_attr");
2467
}
2468
2469
2470
report("-- as comment (ANSI)","comment_--",
2471
"select * from crash_me -- Testing of comments");
2472
report("// as comment","comment_//",
2473
"select * from crash_me // Testing of comments");
2474
report("# as comment","comment_#",
2475
"select * from crash_me # Testing of comments");
2476
report("/* */ as comment","comment_/**/",
2477
"select * from crash_me /* Testing of comments */");
2478
2479
#
2480
# Check things that fails one some servers
2481
#
2482
2483
# Empress can't insert empty strings in a char() field
2484
report("insert empty string","insert_empty_string",
2485
create_table("crash_q",["a char(10) not null,b char(10)"],[]),
2486
"insert into crash_q values ('','')",
2487
"drop table crash_q $drop_attr");
2488
2489
report("Having with alias","having_with_alias",
2490
create_table("crash_q",["a integer"],[]),
2491
"insert into crash_q values (10)",
2492
"select sum(a) as b from crash_q group by a having b > 0",
2493
"drop table crash_q $drop_attr");
2494
2495
#
2496
# test name limits
2497
#
2498
2499
find_limit("table name length","max_table_name",
2500
new query_many(["create table crash_q%s (q integer)",
2501
"insert into crash_q%s values(1)"],
2502
"select * from crash_q%s",1,
2503
["drop table crash_q%s $drop_attr"],
2504
$max_name_length,7,1));
2505
2506
find_limit("column name length","max_column_name",
2507
new query_many(["create table crash_q (q%s integer)",
2508
"insert into crash_q (q%s) values(1)"],
2509
"select q%s from crash_q",1,
2510
["drop table crash_q $drop_attr"],
2511
$max_name_length,1));
2512
2513
if ($limits{'column_alias'} eq 'yes')
2514
{
2515
find_limit("select alias name length","max_select_alias_name",
2516
new query_many(undef,
2517
"select b as %s from crash_me",undef,
2518
undef, $max_name_length));
2519
}
2520
2521
find_limit("table alias name length","max_table_alias_name",
2522
new query_many(undef,
2523
"select %s.b from crash_me %s",
2524
undef,
2525
undef, $max_name_length));
2526
2527
$end_drop_keyword = "drop index %i" if (!$end_drop_keyword);
2528
$end_drop=$end_drop_keyword;
2529
$end_drop =~ s/%i/crash_q%s/;
2530
$end_drop =~ s/%t/crash_me/;
2531
2532
if ($limits{'create_index'} ne 'no')
2533
{
2534
find_limit("index name length","max_index_name",
2535
new query_many(["create index crash_q%s on crash_me (a)"],
2536
undef,undef,
2537
[$end_drop],
2538
$max_name_length,7));
2539
}
2540
2541
find_limit("max char() size","max_char_size",
2542
new query_many(["create table crash_q (q char(%d))",
2543
"insert into crash_q values ('%s')"],
2544
"select * from crash_q","%s",
2545
["drop table crash_q $drop_attr"],
2546
min($max_string_size,$limits{'query_size'})));
2547
2548
if ($limits{'type_sql_varchar(1_arg)'} eq 'yes')
2549
{
2550
find_limit("max varchar() size","max_varchar_size",
2551
new query_many(["create table crash_q (q varchar(%d))",
2552
"insert into crash_q values ('%s')"],
2553
"select * from crash_q","%s",
2554
["drop table crash_q $drop_attr"],
2555
min($max_string_size,$limits{'query_size'})));
2556
}
2557
2558
$found=undef;
2559
foreach $type (('mediumtext','text','text()','blob','long'))
2560
{
2561
if ($limits{"type_extra_$type"} eq 'yes')
2562
{
2563
$found=$type;
2564
last;
2565
}
2566
}
2567
if (defined($found))
2568
{
2569
$found =~ s/\(\)/\(%d\)/;
2570
find_limit("max text or blob size","max_text_size",
2571
new query_many(["create table crash_q (q $found)",
2572
"insert into crash_q values ('%s')"],
2573
"select * from crash_q","%s",
2574
["drop table crash_q $drop_attr"],
2575
min($max_string_size,$limits{'query_size'}-30)));
2576
2577
}
2578
2579
$tmp=new query_repeat([],"create table crash_q (a integer","","",
2580
",a%d integer","",")",["drop table crash_q $drop_attr"],
2581
$max_columns);
2582
$tmp->{'offset'}=1;
2583
find_limit("Columns in table","max_columns",$tmp);
2584
2585
# Make a field definition to be used when testing keys
2586
2587
$key_definitions="q0 integer not null";
2588
$key_fields="q0";
2589
for ($i=1; $i < min($limits{'max_columns'},$max_keys) ; $i++)
2590
{
2591
$key_definitions.=",q$i integer not null";
2592
$key_fields.=",q$i";
2593
}
2594
$key_values="1," x $i;
2595
chop($key_values);
2596
2597
if ($limits{'unique_in_create'} eq 'yes')
2598
{
2599
find_limit("unique indexes","max_unique_index",
2600
new query_table("create table crash_q (q integer",
2601
",q%d integer not null,unique (q%d)",")",
2602
["insert into crash_q (q,%f) values (1,%v)"],
2603
"select q from crash_q",1,
2604
"drop table crash_q $drop_attr",
2605
$max_keys,0));
2606
2607
find_limit("index parts","max_index_parts",
2608
new query_table("create table crash_q ".
2609
"($key_definitions,unique (q0",
2610
",q%d","))",
2611
["insert into crash_q ($key_fields) values ($key_values)"],
2612
"select q0 from crash_q",1,
2613
"drop table crash_q $drop_attr",
2614
$max_keys,1));
2615
2616
find_limit("max index part length","max_index_part_length",
2617
new query_many(["create table crash_q (q char(%d) not null,".
2618
"unique(q))",
2619
"insert into crash_q (q) values ('%s')"],
2620
"select q from crash_q","%s",
2621
["drop table crash_q $drop_attr"],
2622
$limits{'max_char_size'},0));
2623
2624
if ($limits{'type_sql_varchar(1_arg)'} eq 'yes')
2625
{
2626
find_limit("index varchar part length","max_index_varchar_part_length",
2627
new query_many(["create table crash_q (q varchar(%d) not null,".
2628
"unique(q))",
2629
"insert into crash_q (q) values ('%s')"],
2630
"select q from crash_q","%s",
2631
["drop table crash_q $drop_attr"],
2632
$limits{'max_varchar_size'},0));
2633
}
2634
}
2635
2636
2637
if ($limits{'create_index'} ne 'no')
2638
{
2639
if ($limits{'create_index'} eq 'ignored' ||
2640
$limits{'unique_in_create'} eq 'yes')
2641
{ # This should be true
2642
add_log('max_index',
2643
" max_unique_index=$limits{'max_unique_index'} ,".
2644
"so max_index must be same");
2645
save_config_data('max_index',$limits{'max_unique_index'},"max index");
2646
print "indexes: $limits{'max_index'}\n";
2647
}
2648
else
2649
{
2650
if (!defined($limits{'max_index'}))
2651
{
2652
safe_query_l('max_index',"create table crash_q ($key_definitions)");
2653
for ($i=1; $i <= min($limits{'max_columns'},$max_keys) ; $i++)
2654
{
2655
last if (!safe_query_l('max_index',
2656
"create index crash_q$i on crash_q (q$i)"));
2657
}
2658
save_config_data('max_index',$i == $max_keys ? $max_keys : $i,
2659
"max index");
2660
while ( --$i > 0)
2661
{
2662
$end_drop=$end_drop_keyword;
2663
$end_drop =~ s/%i/crash_q$i/;
2664
$end_drop =~ s/%t/crash_q/;
2665
assert_crash($end_drop);
2666
}
2667
assert_crash("drop table crash_q $drop_attr");
2668
}
2669
print "indexs: $limits{'max_index'}\n";
2670
if (!defined($limits{'max_unique_index'}))
2671
{
2672
safe_query_l('max_unique_index',
2673
"create table crash_q ($key_definitions)");
2674
for ($i=0; $i < min($limits{'max_columns'},$max_keys) ; $i++)
2675
{
2676
last if (!safe_query_l('max_unique_index',
2677
"create unique index crash_q$i on crash_q (q$i)"));
2678
}
2679
save_config_data('max_unique_index',$i == $max_keys ? $max_keys : $i,
2680
"max unique index");
2681
while ( --$i >= 0)
2682
{
2683
$end_drop=$end_drop_keyword;
2684
$end_drop =~ s/%i/crash_q$i/;
2685
$end_drop =~ s/%t/crash_q/;
2686
assert_crash($end_drop);
2687
}
2688
assert_crash("drop table crash_q $drop_attr");
2689
}
2690
print "unique indexes: $limits{'max_unique_index'}\n";
2691
if (!defined($limits{'max_index_parts'}))
2692
{
2693
safe_query_l('max_index_parts',
2694
"create table crash_q ($key_definitions)");
2695
$end_drop=$end_drop_keyword;
2696
$end_drop =~ s/%i/crash_q1%d/;
2697
$end_drop =~ s/%t/crash_q/;
2698
find_limit("index parts","max_index_parts",
2699
new query_table("create index crash_q1%d on crash_q (q0",
2700
",q%d",")",
2701
[],
2702
undef,undef,
2703
$end_drop,
2704
$max_keys,1));
2705
assert_crash("drop table crash_q $drop_attr");
2706
}
2707
else
2708
{
2709
print "index parts: $limits{'max_index_parts'}\n";
2710
}
2711
$end_drop=$end_drop_keyword;
2712
$end_drop =~ s/%i/crash_q2%d/;
2713
$end_drop =~ s/%t/crash_me/;
2714
2715
find_limit("index part length","max_index_part_length",
2716
new query_many(["create table crash_q (q char(%d))",
2717
"create index crash_q2%d on crash_q (q)",
2718
"insert into crash_q values('%s')"],
2719
"select q from crash_q",
2720
"%s",
2721
[ $end_drop,
2722
"drop table crash_q $drop_attr"],
2723
min($limits{'max_char_size'},"+8192")));
2724
}
2725
}
2726
2727
find_limit("index length","max_index_length",
2728
new query_index_length("create table crash_q ",
2729
"drop table crash_q $drop_attr",
2730
$max_key_length));
2731
2732
find_limit("max table row length (without blobs)","max_row_length",
2733
new query_row_length("crash_q ",
2734
"not null",
2735
"drop table crash_q $drop_attr",
2736
min($max_row_length,
2737
$limits{'max_columns'}*
2738
min($limits{'max_char_size'},255))));
2739
2740
find_limit("table row length with nulls (without blobs)",
2741
"max_row_length_with_null",
2742
new query_row_length("crash_q ",
2743
"",
2744
"drop table crash_q $drop_attr",
2745
$limits{'max_row_length'}*2));
2746
2747
find_limit("number of columns in order by","columns_in_order_by",
2748
new query_many(["create table crash_q (%F)",
2749
"insert into crash_q values(%v)",
2750
"insert into crash_q values(%v)"],
2751
"select * from crash_q order by %f",
2752
undef(),
2753
["drop table crash_q $drop_attr"],
2754
$max_order_by));
2755
2756
find_limit("number of columns in group by","columns_in_group_by",
2757
new query_many(["create table crash_q (%F)",
2758
"insert into crash_q values(%v)",
2759
"insert into crash_q values(%v)"],
2760
"select %f from crash_q group by %f",
2761
undef(),
2762
["drop table crash_q $drop_attr"],
2763
$max_order_by));
2764
2765
2766
2767
# Safe arithmetic test
2768
2769
$prompt="safe decimal arithmetic";
2770
$key="safe_decimal_arithmetic";
2771
if (!defined($limits{$key}))
2772
{
2773
print "$prompt=";
2774
save_incomplete($key,$prompt);
2775
if (!safe_query_l($key,$server->create("crash_me_a",
2776
["a decimal(10,2)","b decimal(10,2)"])))
2777
{
2778
print DBI->errstr();
2779
die "Can't create table 'crash_me_a' $DBI::errstr\n";
2780
};
2781
2782
if (!safe_query_l($key,
2783
["insert into crash_me_a (a,b) values (11.4,18.9)"]))
2784
{
2785
die "Can't insert into table 'crash_me_a' a record: $DBI::errstr\n";
2786
};
2787
2788
$arithmetic_safe = 'no';
2789
$arithmetic_safe = 'yes'
2790
if ( (safe_query_result_l($key,
2791
'select count(*) from crash_me_a where a+b=30.3',1,0) == 0)
2792
and (safe_query_result_l($key,
2793
'select count(*) from crash_me_a where a+b-30.3 = 0',1,0) == 0)
2794
and (safe_query_result_l($key,
2795
'select count(*) from crash_me_a where a+b-30.3 < 0',0,0) == 0)
2796
and (safe_query_result_l($key,
2797
'select count(*) from crash_me_a where a+b-30.3 > 0',0,0) == 0));
2798
save_config_data($key,$arithmetic_safe,$prompt);
2799
print "$arithmetic_safe\n";
2800
assert_crash("drop table crash_me_a $drop_attr");
2801
}
2802
else
2803
{
2804
print "$prompt=$limits{$key} (cached)\n";
2805
}
2806
2807
# Check where is null values in sorted recordset
2808
if (!safe_query($server->create("crash_me_n",["i integer","r integer"])))
2809
{
2810
print DBI->errstr();
2811
die "Can't create table 'crash_me_n' $DBI::errstr\n";
2812
};
2813
2814
safe_query_l("position_of_null",["insert into crash_me_n (i) values(1)",
2815
"insert into crash_me_n values(2,2)",
2816
"insert into crash_me_n values(3,3)",
2817
"insert into crash_me_n values(4,4)",
2818
"insert into crash_me_n (i) values(5)"]);
2819
2820
$key = "position_of_null";
2821
$prompt ="Where is null values in sorted recordset";
2822
if (!defined($limits{$key}))
2823
{
2824
save_incomplete($key,$prompt);
2825
print "$prompt=";
2826
$sth=$dbh->prepare("select r from crash_me_n order by r ");
2827
$sth->execute;
2828
add_log($key,"< select r from crash_me_n order by r ");
2829
$limit= detect_null_position($key,$sth);
2830
$sth->finish;
2831
print "$limit\n";
2832
save_config_data($key,$limit,$prompt);
2833
} else {
2834
print "$prompt=$limits{$key} (cache)\n";
2835
}
2836
2837
$key = "position_of_null_desc";
2838
$prompt ="Where is null values in sorted recordset (DESC)";
2839
if (!defined($limits{$key}))
2840
{
2841
save_incomplete($key,$prompt);
2842
print "$prompt=";
2843
$sth=$dbh->prepare("select r from crash_me_n order by r desc");
2844
$sth->execute;
2845
add_log($key,"< select r from crash_me_n order by r desc");
2846
$limit= detect_null_position($key,$sth);
2847
$sth->finish;
2848
print "$limit\n";
2849
save_config_data($key,$limit,$prompt);
2850
} else {
2851
print "$prompt=$limits{$key} (cache)\n";
2852
}
2853
2854
2855
assert_crash("drop table crash_me_n $drop_attr");
2856
2857
2858
2859
$key = 'sorted_group_by';
2860
$prompt = 'Group by always sorted';
2861
if (!defined($limits{$key}))
2862
{
2863
save_incomplete($key,$prompt);
2864
print "$prompt=";
2865
safe_query_l($key,[
2866
"create table crash_me_t1 (a int not null, b int not null)",
2867
"insert into crash_me_t1 values (1,1)",
2868
"insert into crash_me_t1 values (1,2)",
2869
"insert into crash_me_t1 values (3,1)",
2870
"insert into crash_me_t1 values (3,2)",
2871
"insert into crash_me_t1 values (2,2)",
2872
"insert into crash_me_t1 values (2,1)",
2873
"create table crash_me_t2 (a int not null, b int not null)",
2874
"create index crash_me_t2_ind on crash_me_t2 (a)",
2875
"insert into crash_me_t2 values (1,3)",
2876
"insert into crash_me_t2 values (3,1)",
2877
"insert into crash_me_t2 values (2,2)",
2878
"insert into crash_me_t2 values (1,1)"]);
2879
2880
my $bigqry = "select crash_me_t1.a,crash_me_t2.b from ".
2881
"crash_me_t1,crash_me_t2 where crash_me_t1.a=crash_me_t2.a ".
2882
"group by crash_me_t1.a,crash_me_t2.b";
2883
2884
my $limit='no';
2885
my $rs = get_recordset($key,$bigqry);
2886
print_recordset($key,$rs);
2887
if ( defined ($rs)) {
2888
if (compare_recordset($key,$rs,[[1,1],[1,3],[2,2],[3,1]]) eq 0)
2889
{
2890
$limit='yes'
2891
}
2892
} else {
2893
add_log($key,"error: ".$DBI::errstr);
2894
}
2895
2896
print "$limit\n";
2897
safe_query_l($key,["drop table crash_me_t1",
2898
"drop table crash_me_t2"]);
2899
save_config_data($key,$limit,$prompt);
2900
2901
} else {
2902
print "$prompt=$limits{$key} (cashed)\n";
2903
}
2904
2905
2906
#
2907
# End of test
2908
#
2909
2910
$dbh->do("drop table crash_me $drop_attr"); # Remove temporary table
2911
2912
print "crash-me safe: $limits{'crash_me_safe'}\n";
2913
print "reconnected $reconnect_count times\n";
2914
2915
$dbh->disconnect || warn $dbh->errstr;
2916
save_all_config_data();
2917
exit 0;
2918
2919
# End of test
2920
#
2921
2922
$dbh->do("drop table crash_me $drop_attr"); # Remove temporary table
2923
2924
print "crash-me safe: $limits{'crash_me_safe'}\n";
2925
print "reconnected $reconnect_count times\n";
2926
2927
$dbh->disconnect || warn $dbh->errstr;
2928
save_all_config_data();
2929
exit 0;
2930
2931
# Check where is nulls in the sorted result (for)
2932
# it expects exactly 5 rows in the result
2933
2934
sub detect_null_position
2935
{
2936
my $key = shift;
2937
my $sth = shift;
2938
my ($z,$r1,$r2,$r3,$r4,$r5);
2939
$r1 = $sth->fetchrow_array; add_log($key,"> $r1");
2940
$r2 = $sth->fetchrow_array; add_log($key,"> $r2");
2941
$r3 = $sth->fetchrow_array; add_log($key,"> $r3");
2942
$r4 = $sth->fetchrow_array; add_log($key,"> $r4");
2943
$r5 = $sth->fetchrow_array; add_log($key,"> $r5");
2944
return "first" if ( !defined($r1) && !defined($r2) && defined($r3));
2945
return "last" if ( !defined($r5) && !defined($r4) && defined($r3));
2946
return "random";
2947
}
2948
2949
sub check_parenthesis {
2950
my $prefix=shift;
2951
my $fn=shift;
2952
my $result='no';
2953
my $param_name=$prefix.lc($fn);
2954
my $r;
2955
2956
save_incomplete($param_name,$fn);
2957
$r = safe_query("select $fn $end_query");
2958
add_log($param_name,$safe_query_log);
2959
if ($r == 1)
2960
{
2961
$result="yes";
2962
}
2963
else{
2964
$r = safe_query("select $fn() $end_query");
2965
add_log($param_name,$safe_query_log);
2966
if ( $r == 1)
2967
{
2968
$result="with_parenthesis";
2969
}
2970
}
2971
2972
save_config_data($param_name,$result,$fn);
2973
}
2974
2975
sub check_constraint {
2976
my $prompt = shift;
2977
my $key = shift;
2978
my $create = shift;
2979
my $check = shift;
2980
my $drop = shift;
2981
save_incomplete($key,$prompt);
2982
print "$prompt=";
2983
my $res = 'no';
2984
my $t;
2985
$t=safe_query($create);
2986
add_log($key,$safe_query_log);
2987
if ( $t == 1)
2988
{
2989
$res='yes';
2990
$t= safe_query($check);
2991
add_log($key,$safe_query_log);
2992
if ($t == 1)
2993
{
2994
$res='syntax only';
2995
}
2996
}
2997
safe_query($drop);
2998
add_log($key,$safe_query_log);
2999
3000
save_config_data($key,$res,$prompt);
3001
print "$res\n";
3002
}
3003
3004
sub make_time_r {
3005
my $hour=shift;
3006
my $minute=shift;
3007
my $second=shift;
3008
$_ = $limits{'time_format_inresult'};
3009
return sprintf "%02d:%02d:%02d", ($hour%24),$minute,$second if (/^iso$/);
3010
return sprintf "%02d.%02d.%02d", ($hour%24),$minute,$second if (/^euro/);
3011
return sprintf "%02d:%02d %s",
3012
($hour >= 13? ($hour-12) : $hour),$minute,($hour >=13 ? 'PM':'AM')
3013
if (/^usa/);
3014
return sprintf "%02d%02d%02d", ($hour%24),$minute,$second if (/^HHMMSS/);
3015
return sprintf "%04d%02d%02d", ($hour%24),$minute,$second if (/^HHHHMMSS/);
3016
return "UNKNOWN FORMAT";
3017
}
3018
3019
sub make_time {
3020
my $hour=shift;
3021
my $minute=shift;
3022
my $second=shift;
3023
return sprintf "%02d:%02d:%02d", ($hour%24),$minute,$second
3024
if ($limits{'time_format_ISO'} eq "yes");
3025
return sprintf "%02d.%02d.%02d", ($hour%24),$minute,$second
3026
if ($limits{'time_format_EUR'} eq "yes");
3027
return sprintf "%02d:%02d %s",
3028
($hour >= 13? ($hour-12) : $hour),$minute,($hour >=13 ? 'PM':'AM')
3029
if ($limits{'time_format_USA'} eq "yes");
3030
return sprintf "%02d%02d%02d", ($hour%24),$minute,$second
3031
if ($limits{'time_format_HHMMSS'} eq "yes");
3032
return sprintf "%04d%02d%02d", ($hour%24),$minute,$second
3033
if ($limits{'time_format_HHHHMMSS'} eq "yes");
3034
return "UNKNOWN FORMAT";
3035
}
3036
3037
sub make_date_r {
3038
my $year=shift;
3039
my $month=shift;
3040
my $day=shift;
3041
$_ = $limits{'date_format_inresult'};
3042
return sprintf "%02d-%02d-%02d", ($year%100),$month,$day if (/^short iso$/);
3043
return sprintf "%04d-%02d-%02d", $year,$month,$day if (/^iso/);
3044
return sprintf "%02d.%02d.%02d", $day,$month,($year%100) if (/^short euro/);
3045
return sprintf "%02d.%02d.%04d", $day,$month,$year if (/^euro/);
3046
return sprintf "%02d/%02d/%02d", $month,$day,($year%100) if (/^short usa/);
3047
return sprintf "%02d/%02d/%04d", $month,$day,$year if (/^usa/);
3048
return sprintf "%04d%02d%02d", $year,$month,$day if (/^YYYYMMDD/);
3049
return "UNKNOWN FORMAT";
3050
}
3051
3052
3053
sub make_date {
3054
my $year=shift;
3055
my $month=shift;
3056
my $day=shift;
3057
return sprintf "'%04d-%02d-%02d'", $year,$month,$day
3058
if ($limits{'date_format_ISO'} eq 'yes');
3059
return sprintf "DATE '%04d-%02d-%02d'", $year,$month,$day
3060
if ($limits{'date_format_ISO_with_date'} eq 'yes');
3061
return sprintf "'%02d.%02d.%04d'", $day,$month,$year
3062
if ($limits{'date_format_EUR'} eq 'yes');
3063
return sprintf "DATE '%02d.%02d.%04d'", $day,$month,$year
3064
if ($limits{'date_format_EUR_with_date'} eq 'yes');
3065
return sprintf "'%02d/%02d/%04d'", $month,$day,$year
3066
if ($limits{'date_format_USA'} eq 'yes');
3067
return sprintf "DATE '%02d/%02d/%04d'", $month,$day,$year
3068
if ($limits{'date_format_USA_with_date'} eq 'yes');
3069
return sprintf "'%04d%02d%02d'", $year,$month,$day
3070
if ($limits{'date_format_YYYYMMDD'} eq 'yes');
3071
return sprintf "DATE '%04d%02d%02d'", $year,$month,$day
3072
if ($limits{'date_format_YYYYMMDD_with_date'} eq 'yes');
3073
return "UNKNOWN FORMAT";
3074
}
3075
3076
3077
sub print_recordset{
3078
my ($key,$recset) = @_;
3079
my $rec;
3080
foreach $rec (@$recset)
3081
{
3082
add_log($key, " > ".join(',', map(repr($_), @$rec)));
3083
}
3084
}
3085
3086
#
3087
# read result recordset from sql server.
3088
# returns arrayref to (arrayref to) values
3089
# or undef (in case of sql errors)
3090
#
3091
sub get_recordset{
3092
my ($key,$query) = @_;
3093
add_log($key, "< $query");
3094
return $dbh->selectall_arrayref($query);
3095
}
3096
3097
# function for comparing recordset (that was returned by get_recordset)
3098
# and arrayref of (arrayref of) values.
3099
#
3100
# returns : zero if recordset equal that array, 1 if it doesn't equal
3101
#
3102
# parameters:
3103
# $key - current operation (for logging)
3104
# $recset - recordset
3105
# $mustbe - array of values that we expect
3106
#
3107
# example: $a=get_recordset('some_parameter','select a,b from c');
3108
# if (compare_recordset('some_parameter',$a,[[1,1],[1,2],[1,3]]) neq 0)
3109
# {
3110
# print "unexpected result\n";
3111
# } ;
3112
#
3113
sub compare_recordset {
3114
my ($key,$recset,$mustbe) = @_;
3115
my ($rec,$recno,$fldno,$fcount);
3116
add_log($key,"\n Check recordset:");
3117
$recno=0;
3118
foreach $rec (@$recset)
3119
{
3120
add_log($key," " . join(',', map(repr($_),@$rec)) . " expected: " .
3121
join(',', map(repr($_), @{$mustbe->[$recno]} ) ));
3122
$fcount = @$rec;
3123
$fcount--;
3124
foreach $fldno (0 .. $fcount )
3125
{
3126
if ($mustbe->[$recno][$fldno] ne $rec->[$fldno])
3127
{
3128
add_log($key," Recordset doesn't correspond with template");
3129
return 1;
3130
};
3131
}
3132
$recno++;
3133
}
3134
add_log($key," Recordset corresponds with template");
3135
return 0;
3136
}
3137
3138
#
3139
# converts inner perl value to printable representation
3140
# for example: undef maps to 'NULL',
3141
# string -> 'string'
3142
# int -> int
3143
#
3144
sub repr {
3145
my $s = shift;
3146
return "'$s'"if ($s =~ /\D/);
3147
return 'NULL'if ( not defined($s));
3148
return $s;
3149
}
3150
3151
3152
sub version
3153
{
3154
print "$0 Ver $version\n";
3155
}
3156
3157
3158
sub usage
3159
{
3160
version();
3161
print <<EOF;
3162
3163
This program tries to find all limits and capabilities for a SQL
3164
server. As it will use the server in some 'unexpected' ways, one
3165
shouldn\'t have anything important running on it at the same time this
3166
program runs! There is a slight chance that something unexpected may
3167
happen....
3168
3169
As all used queries are legal according to some SQL standard. any
3170
reasonable SQL server should be able to run this test without any
3171
problems.
3172
3173
All questions is cached in $opt_dir/'server_name'[-suffix].cfg that
3174
future runs will use limits found in previous runs. Remove this file
3175
if you want to find the current limits for your version of the
3176
database server.
3177
3178
This program uses some table names while testing things. If you have any
3179
tables with the name of 'crash_me' or 'crash_qxxxx' where 'x' is a number,
3180
they will be deleted by this test!
3181
3182
$0 takes the following options:
3183
3184
--help or --Information
3185
Shows this help
3186
3187
--batch-mode
3188
Don\'t ask any questions, quit on errors.
3189
3190
--check-server
3191
Do a new connection to the server every time crash-me checks if the server
3192
is alive. This can help in cases where the server starts returning wrong
3193
data because of an earlier select.
3194
3195
--comment='some comment'
3196
Add this comment to the crash-me limit file
3197
3198
--config-file='filename'
3199
Read limit results from specific file
3200
3201
--connect-options='some connect options'
3202
Add options, which uses at DBI connect.
3203
For example --connect-options=mysql_read_default_file=/etc/my.cnf.
3204
3205
--database='database' (Default $opt_database)
3206
Create test tables in this database.
3207
3208
--debug
3209
Lots of printing to help debugging if something goes wrong.
3210
3211
--dir='limits'
3212
Save crash-me output in this directory
3213
3214
--fix-limit-file
3215
Reformat the crash-me limit file. crash-me is not run!
3216
3217
--force
3218
Start test at once, without a warning screen and without questions.
3219
This is a option for the very brave.
3220
Use this in your cron scripts to test your database every night.
3221
3222
--log-all-queries
3223
Prints all queries that are executed. Mostly used for debugging crash-me.
3224
3225
--log-queries-to-file='filename'
3226
Log full queries to file.
3227
3228
--host='hostname' (Default $opt_host)
3229
Run tests on this host.
3230
3231
--password='password'
3232
Password for the current user.
3233
3234
--restart
3235
Save states during each limit tests. This will make it possible to continue
3236
by restarting with the same options if there is some bug in the DBI or
3237
DBD driver that caused $0 to die!
3238
3239
--server='server name' (Default $opt_server)
3240
Run the test on the given server.
3241
Known servers names are: Access, Adabas, AdabasD, Empress, Oracle,
3242
Informix, DB2, Mimer, mSQL, MS-SQL, MySQL, Pg, Solid or Sybase.
3243
For others $0 can\'t report the server version.
3244
3245
--suffix='suffix' (Default '')
3246
Add suffix to the output filename. For instance if you run crash-me like
3247
"crash-me --suffix="myisam",
3248
then output filename will look "mysql-myisam.cfg".
3249
3250
--user='user_name'
3251
User name to log into the SQL server.
3252
3253
--db-start-cmd='command to restart server'
3254
Automaticly restarts server with this command if the database server dies.
3255
3256
--sleep='time in seconds' (Default $opt_sleep)
3257
Wait this long before restarting server.
3258
3259
--verbose
3260
--noverbose
3261
Log into the result file queries performed for determination parameter value
3262
3263
EOF
3264
exit(0);
3265
}
3266
3267
3268
sub server_info
3269
{
3270
my ($ok,$tmp);
3271
$ok=0;
3272
print "\nNOTE: You should be familiar with '$0 --help' before continuing!\n\n";
3273
if (lc($opt_server) eq "mysql")
3274
{
3275
$ok=1;
3276
print <<EOF;
3277
This test should not crash MySQL if it was distributed together with the
3278
running MySQL version.
3279
If this is the case you can probably continue without having to worry about
3280
destroying something.
3281
EOF
3282
}
3283
elsif (lc($opt_server) eq "msql")
3284
{
3285
print <<EOF;
3286
This test will take down mSQL repeatedly while finding limits.
3287
To make this test easier, start mSQL in another terminal with something like:
3288
3289
while (true); do /usr/local/mSQL/bin/msql2d ; done
3290
3291
You should be sure that no one is doing anything important with mSQL and that
3292
you have privileges to restart it!
3293
It may take awhile to determinate the number of joinable tables, so prepare to
3294
wait!
3295
EOF
3296
}
3297
elsif (lc($opt_server) eq "solid")
3298
{
3299
print <<EOF;
3300
This test will take down Solid server repeatedly while finding limits.
3301
You should be sure that no one is doing anything important with Solid
3302
and that you have privileges to restart it!
3303
3304
If you are running Solid without logging and/or backup YOU WILL LOSE!
3305
Solid does not write data from the cache often enough. So if you continue
3306
you may lose tables and data that you entered hours ago!
3307
3308
Solid will also take a lot of memory running this test. You will nead
3309
at least 234M free!
3310
3311
When doing the connect test Solid server or the perl api will hang when
3312
freeing connections. Kill this program and restart it to continue with the
3313
test. You don\'t have to use --restart for this case.
3314
EOF
3315
if (!$opt_restart)
3316
{
3317
print "\nWhen DBI/Solid dies you should run this program repeatedly\n";
3318
print "with --restart until all tests have completed\n";
3319
}
3320
}
3321
elsif (lc($opt_server) eq "pg")
3322
{
3323
print <<EOF;
3324
This test will crash postgreSQL when calculating the number of joinable tables!
3325
You should be sure that no one is doing anything important with postgreSQL
3326
and that you have privileges to restart it!
3327
EOF
3328
}
3329
else
3330
{
3331
print <<EOF;
3332
This test may crash $opt_server repeatedly while finding limits!
3333
You should be sure that no one is doing anything important with $opt_server
3334
and that you have privileges to restart it!
3335
EOF
3336
}
3337
print <<EOF;
3338
3339
Some of the tests you are about to execute may require a lot of
3340
memory. Your tests WILL adversely affect system performance. It\'s
3341
not uncommon that either this crash-me test program, or the actual
3342
database back-end, will DIE with an out-of-memory error. So might
3343
any other program on your system if it requests more memory at the
3344
wrong time.
3345
3346
Note also that while crash-me tries to find limits for the database server
3347
it will make a lot of queries that can\'t be categorized as \'normal\'. It\'s
3348
not unlikely that crash-me finds some limit bug in your server so if you
3349
run this test you have to be prepared that your server may die during it!
3350
3351
We, the creators of this utility, are not responsible in any way if your
3352
database server unexpectedly crashes while this program tries to find the
3353
limitations of your server. By accepting the following question with \'yes\',
3354
you agree to the above!
3355
3356
You have been warned!
3357
3358
EOF
3359
3360
#
3361
# No default reply here so no one can blame us for starting the test
3362
# automaticly.
3363
#
3364
for (;;)
3365
{
3366
print "Start test (yes/no) ? ";
3367
$tmp=<STDIN>; chomp($tmp); $tmp=lc($tmp);
3368
last if ($tmp =~ /^yes$/i);
3369
exit 1 if ($tmp =~ /^n/i);
3370
print "\n";
3371
}
3372
}
3373
3374
#sub machine
3375
#{
3376
# my @name = POSIX::uname();
3377
# my $name= $name[0] . " " . $name[2] . " " . $name[4];
3378
# return $name;
3379
#}
3380
3381
3382
#
3383
# Help functions that we need
3384
#
3385
3386
sub safe_connect
3387
{
3388
my ($object)=@_;
3389
my ($dbh,$tmp);
3390
3391
for (;;)
3392
{
3393
if (($dbh=DBI->connect($server->{'data_source'},$opt_user,$opt_password,
3394
{ PrintError => 0, AutoCommit => 1})))
3395
{
3396
$dbh->{LongReadLen}= 16000000; # Set max retrieval buffer
3397
return $dbh;
3398
}
3399
print "Error: $DBI::errstr; $server->{'data_source'} ".
3400
" - '$opt_user' - '$opt_password'\n";
3401
print "I got the above error when connecting to $opt_server\n";
3402
if (defined($object) && defined($object->{'limit'}))
3403
{
3404
print "This check was done with limit: $object->{'limit'}.".
3405
"\nNext check will be done with a smaller limit!\n";
3406
$object=undef();
3407
}
3408
save_config_data('crash_me_safe','no',"crash me safe");
3409
if ($opt_db_start_cmd)
3410
{
3411
print "Restarting the db server with:\n'$opt_db_start_cmd'\n";
3412
system("$opt_db_start_cmd");
3413
print "Waiting $opt_sleep seconds so the server can initialize\n";
3414
sleep $opt_sleep;
3415
}
3416
else
3417
{
3418
exit(1) if ($opt_batch_mode);
3419
print "Can you check/restart it so I can continue testing?\n";
3420
for (;;)
3421
{
3422
print "Continue test (yes/no) ? [yes] ";
3423
$tmp=<STDIN>; chomp($tmp); $tmp=lc($tmp);
3424
$tmp = "yes" if ($tmp eq "");
3425
last if (index("yes",$tmp) >= 0);
3426
exit 1 if (index("no",$tmp) >= 0);
3427
print "\n";
3428
}
3429
}
3430
}
3431
}
3432
3433
#
3434
# Test connecting a couple of times before giving an error
3435
# This is needed to get the server time to free old connections
3436
# after the connect test
3437
#
3438
3439
sub retry_connect
3440
{
3441
my ($dbh, $i);
3442
for ($i=0 ; $i < 10 ; $i++)
3443
{
3444
if (($dbh=DBI->connect($server->{'data_source'},$opt_user,$opt_password,
3445
{ PrintError => 0, AutoCommit => 1})))
3446
{
3447
$dbh->{LongReadLen}= 16000000; # Set max retrieval buffer
3448
return $dbh;
3449
}
3450
sleep(1);
3451
}
3452
return safe_connect();
3453
}
3454
3455
#
3456
# Check if the server is up and running. If not, ask the user to restart it
3457
#
3458
3459
sub check_connect
3460
{
3461
my ($object)=@_;
3462
my ($sth);
3463
print "Checking connection\n" if ($opt_log_all_queries);
3464
# The following line will not work properly with interbase
3465
if ($opt_check_server && defined($check_connect) && $dbh->{AutoCommit} != 0)
3466
{
3467
3468
$dbh->disconnect;
3469
$dbh=safe_connect($object);
3470
return;
3471
}
3472
return if (defined($check_connect) && defined($dbh->do($check_connect)));
3473
$dbh->disconnect || warn $dbh->errstr;
3474
print "\nreconnecting\n" if ($opt_debug);
3475
$reconnect_count++;
3476
undef($dbh);
3477
$dbh=safe_connect($object);
3478
}
3479
3480
#
3481
# print query if debugging
3482
#
3483
sub repr_query {
3484
my $query=shift;
3485
if (length($query) > 130)
3486
{
3487
$query=substr($query,0,120) . "...(" . (length($query)-120) . ")";
3488
}
3489
return $query;
3490
}
3491
3492
sub print_query
3493
{
3494
my ($query)=@_;
3495
$last_error=$DBI::errstr;
3496
if ($opt_debug)
3497
{
3498
if (length($query) > 130)
3499
{
3500
$query=substr($query,0,120) . "...(" . (length($query)-120) . ")";
3501
}
3502
printf "\nGot error from query: '%s'\n%s\n",$query,$DBI::errstr;
3503
}
3504
}
3505
3506
#
3507
# Do one or many queries. Return 1 if all was ok
3508
# Note that all rows are executed
3509
# (to ensure that we execute drop table commands)
3510
#
3511
3512
sub safe_query_l {
3513
my $key = shift;
3514
my $q = shift;
3515
my $r = safe_query($q);
3516
add_log($key,$safe_query_log);
3517
return $r;
3518
}
3519
3520
sub safe_query
3521
{
3522
my($queries)=@_;
3523
my($query,$ok,$retry_ok,$retry,@tmp,$sth);
3524
$safe_query_log="";
3525
$ok=1;
3526
if (ref($queries) ne "ARRAY")
3527
{
3528
push(@tmp,$queries);
3529
$queries= \@tmp;
3530
}
3531
foreach $query (@$queries)
3532
{
3533
printf "query1: %-80.80s ...(%d - %d)\n",$query,
3534
length($query),$retry_limit if ($opt_log_all_queries);
3535
print LOG "$query;\n" if ($opt_log);
3536
$safe_query_log .= "< $query\n";
3537
if (length($query) > $query_size)
3538
{
3539
$ok=0;
3540
$safe_query_log .= "Query is too long\n";
3541
next;
3542
}
3543
3544
$retry_ok=0;
3545
for ($retry=0; $retry < $retry_limit ; $retry++)
3546
{
3547
if (! ($sth=$dbh->prepare($query)))
3548
{
3549
print_query($query);
3550
$safe_query_log .= "> couldn't prepare:". $dbh->errstr. "\n";
3551
$retry=100 if (!$server->abort_if_fatal_error());
3552
# Force a reconnect because of Access drop table bug!
3553
if ($retry == $retry_limit-2)
3554
{
3555
print "Forcing disconnect to retry query\n" if ($opt_debug);
3556
$dbh->disconnect || warn $dbh->errstr;
3557
}
3558
check_connect(); # Check that server is still up
3559
}
3560
else
3561
{
3562
if (!$sth->execute())
3563
{
3564
print_query($query);
3565
$safe_query_log .= "> execute error:". $dbh->errstr. "\n";
3566
$retry=100 if (!$server->abort_if_fatal_error());
3567
# Force a reconnect because of Access drop table bug!
3568
if ($retry == $retry_limit-2)
3569
{
3570
print "Forcing disconnect to retry query\n" if ($opt_debug);
3571
$dbh->disconnect || warn $dbh->errstr;
3572
}
3573
check_connect(); # Check that server is still up
3574
}
3575
else
3576
{
3577
$retry = $retry_limit;
3578
$retry_ok = 1;
3579
$safe_query_log .= "> OK\n";
3580
}
3581
$sth->finish;
3582
}
3583
}
3584
$ok=0 if (!$retry_ok);
3585
if ($query =~ /create/i && $server->reconnect_on_errors())
3586
{
3587
print "Forcing disconnect to retry query\n" if ($opt_debug);
3588
$dbh->disconnect || warn $dbh->errstr;
3589
$dbh=safe_connect();
3590
}
3591
}
3592
return $ok;
3593
}
3594
3595
sub check_reserved_words
3596
{
3597
my ($dbh)= @_;
3598
3599
my ($answer, $prompt, $config, $keyword_type);
3600
3601
my @keywords_ext = ( "ansi-92/99", "ansi92", "ansi99", "extra");
3602
3603
my %reserved_words = (
3604
'ABSOLUTE' => 0, 'ACTION' => 0, 'ADD' => 0,
3605
'AFTER' => 0, 'ALIAS' => 0, 'ALL' => 0,
3606
'ALLOCATE' => 0, 'ALTER' => 0, 'AND' => 0,
3607
'ANY' => 0, 'ARE' => 0, 'AS' => 0,
3608
'ASC' => 0, 'ASSERTION' => 0, 'AT' => 0,
3609
'AUTHORIZATION' => 0, 'BEFORE' => 0, 'BEGIN' => 0,
3610
'BIT' => 0, 'BOOLEAN' => 0, 'BOTH' => 0,
3611
'BREADTH' => 0, 'BY' => 0, 'CALL' => 0,
3612
'CASCADE' => 0, 'CASCADED' => 0, 'CASE' => 0,
3613
'CAST' => 0, 'CATALOG' => 0, 'CHAR' => 0,
3614
'CHARACTER' => 0, 'CHECK' => 0, 'CLOSE' => 0,
3615
'COLLATE' => 0, 'COLLATION' => 0, 'COLUMN' => 0,
3616
'COMMIT' => 0, 'COMPLETION' => 0, 'CONNECT' => 0,
3617
'CONNECTION' => 0, 'CONSTRAINT' => 0, 'CONSTRAINTS' => 0,
3618
'CONTINUE' => 0, 'CORRESPONDING' => 0, 'CREATE' => 0,
3619
'CROSS' => 0, 'CURRENT' => 0, 'CURRENT_DATE' => 0,
3620
'CURRENT_TIME' => 0,'CURRENT_TIMESTAMP' => 0, 'CURRENT_USER' => 0,
3621
'CURSOR' => 0, 'CYCLE' => 0, 'DATA' => 0,
3622
'DATE' => 0, 'DAY' => 0, 'DEALLOCATE' => 0,
3623
'DEC' => 0, 'DECIMAL' => 0, 'DECLARE' => 0,
3624
'DEFAULT' => 0, 'DEFERRABLE' => 0, 'DEFERRED' => 0,
3625
'DELETE' => 0, 'DEPTH' => 0, 'DESC' => 0,
3626
'DESCRIBE' => 0, 'DESCRIPTOR' => 0, 'DIAGNOSTICS' => 0,
3627
'DICTIONARY' => 0, 'DISCONNECT' => 0, 'DISTINCT' => 0,
3628
'DOMAIN' => 0, 'DOUBLE' => 0, 'DROP' => 0,
3629
'EACH' => 0, 'ELSE' => 0, 'ELSEIF' => 0,
3630
'END' => 0, 'END-EXEC' => 0, 'EQUALS' => 0,
3631
'ESCAPE' => 0, 'EXCEPT' => 0, 'EXCEPTION' => 0,
3632
'EXEC' => 0, 'EXECUTE' => 0, 'EXTERNAL' => 0,
3633
'FALSE' => 0, 'FETCH' => 0, 'FIRST' => 0,
3634
'FLOAT' => 0, 'FOR' => 0, 'FOREIGN' => 0,
3635
'FOUND' => 0, 'FROM' => 0, 'FULL' => 0,
3636
'GENERAL' => 0, 'GET' => 0, 'GLOBAL' => 0,
3637
'GO' => 0, 'GOTO' => 0, 'GRANT' => 0,
3638
'GROUP' => 0, 'HAVING' => 0, 'HOUR' => 0,
3639
'IDENTITY' => 0, 'IF' => 0, 'IGNORE' => 0,
3640
'IMMEDIATE' => 0, 'IN' => 0, 'INDICATOR' => 0,
3641
'INITIALLY' => 0, 'INNER' => 0, 'INPUT' => 0,
3642
'INSERT' => 0, 'INT' => 0, 'INTEGER' => 0,
3643
'INTERSECT' => 0, 'INTERVAL' => 0, 'INTO' => 0,
3644
'IS' => 0, 'ISOLATION' => 0, 'JOIN' => 0,
3645
'KEY' => 0, 'LANGUAGE' => 0, 'LAST' => 0,
3646
'LEADING' => 0, 'LEAVE' => 0, 'LEFT' => 0,
3647
'LESS' => 0, 'LEVEL' => 0, 'LIKE' => 0,
3648
'LIMIT' => 0, 'LOCAL' => 0, 'LOOP' => 0,
3649
'MATCH' => 0, 'MINUTE' => 0, 'MODIFY' => 0,
3650
'MODULE' => 0, 'MONTH' => 0, 'NAMES' => 0,
3651
'NATIONAL' => 0, 'NATURAL' => 0, 'NCHAR' => 0,
3652
'NEW' => 0, 'NEXT' => 0, 'NO' => 0,
3653
'NONE' => 0, 'NOT' => 0, 'NULL' => 0,
3654
'NUMERIC' => 0, 'OBJECT' => 0, 'OF' => 0,
3655
'OFF' => 0, 'OLD' => 0, 'ON' => 0,
3656
'ONLY' => 0, 'OPEN' => 0, 'OPERATION' => 0,
3657
'OPTION' => 0, 'OR' => 0, 'ORDER' => 0,
3658
'OUTER' => 0, 'OUTPUT' => 0, 'PAD' => 0,
3659
'PARAMETERS' => 0, 'PARTIAL' => 0, 'PRECISION' => 0,
3660
'PREORDER' => 0, 'PREPARE' => 0, 'PRESERVE' => 0,
3661
'PRIMARY' => 0, 'PRIOR' => 0, 'PRIVILEGES' => 0,
3662
'PROCEDURE' => 0, 'PUBLIC' => 0, 'READ' => 0,
3663
'REAL' => 0, 'RECURSIVE' => 0, 'REF' => 0,
3664
'REFERENCES' => 0, 'REFERENCING' => 0, 'RELATIVE' => 0,
3665
'RESIGNAL' => 0, 'RESTRICT' => 0, 'RETURN' => 0,
3666
'RETURNS' => 0, 'REVOKE' => 0, 'RIGHT' => 0,
3667
'ROLE' => 0, 'ROLLBACK' => 0, 'ROUTINE' => 0,
3668
'ROW' => 0, 'ROWS' => 0, 'SAVEPOINT' => 0,
3669
'SCHEMA' => 0, 'SCROLL' => 0, 'SEARCH' => 0,
3670
'SECOND' => 0, 'SECTION' => 0, 'SELECT' => 0,
3671
'SEQUENCE' => 0, 'SESSION' => 0, 'SESSION_USER' => 0,
3672
'SET' => 0, 'SIGNAL' => 0, 'SIZE' => 0,
3673
'SMALLINT' => 0, 'SOME' => 0, 'SPACE' => 0,
3674
'SQL' => 0, 'SQLEXCEPTION' => 0, 'SQLSTATE' => 0,
3675
'SQLWARNING' => 0, 'STRUCTURE' => 0, 'SYSTEM_USER' => 0,
3676
'TABLE' => 0, 'TEMPORARY' => 0, 'THEN' => 0,
3677
'TIME' => 0, 'TIMESTAMP' => 0, 'TIMEZONE_HOUR' => 0,
3678
'TIMEZONE_MINUTE' => 0, 'TO' => 0, 'TRAILING' => 0,
3679
'TRANSACTION' => 0, 'TRANSLATION' => 0, 'TRIGGER' => 0,
3680
'TRUE' => 0, 'UNDER' => 0, 'UNION' => 0,
3681
'UNIQUE' => 0, 'UNKNOWN' => 0, 'UPDATE' => 0,
3682
'USAGE' => 0, 'USER' => 0, 'USING' => 0,
3683
'VALUE' => 0, 'VALUES' => 0, 'VARCHAR' => 0,
3684
'VARIABLE' => 0, 'VARYING' => 0, 'VIEW' => 0,
3685
'WHEN' => 0, 'WHENEVER' => 0, 'WHERE' => 0,
3686
'WHILE' => 0, 'WITH' => 0, 'WITHOUT' => 0,
3687
'WORK' => 0, 'WRITE' => 0, 'YEAR' => 0,
3688
'ZONE' => 0,
3689
3690
'ASYNC' => 1, 'AVG' => 1, 'BETWEEN' => 1,
3691
'BIT_LENGTH' => 1,'CHARACTER_LENGTH' => 1, 'CHAR_LENGTH' => 1,
3692
'COALESCE' => 1, 'CONVERT' => 1, 'COUNT' => 1,
3693
'EXISTS' => 1, 'EXTRACT' => 1, 'INSENSITIVE' => 1,
3694
'LOWER' => 1, 'MAX' => 1, 'MIN' => 1,
3695
'NULLIF' => 1, 'OCTET_LENGTH' => 1, 'OID' => 1,
3696
'OPERATORS' => 1, 'OTHERS' => 1, 'OVERLAPS' => 1,
3697
'PENDANT' => 1, 'POSITION' => 1, 'PRIVATE' => 1,
3698
'PROTECTED' => 1, 'REPLACE' => 1, 'SENSITIVE' => 1,
3699
'SIMILAR' => 1, 'SQLCODE' => 1, 'SQLERROR' => 1,
3700
'SUBSTRING' => 1, 'SUM' => 1, 'TEST' => 1,
3701
'THERE' => 1, 'TRANSLATE' => 1, 'TRIM' => 1,
3702
'TYPE' => 1, 'UPPER' => 1, 'VIRTUAL' => 1,
3703
'VISIBLE' => 1, 'WAIT' => 1,
3704
3705
'ADMIN' => 2, 'AGGREGATE' => 2, 'ARRAY' => 2,
3706
'BINARY' => 2, 'BLOB' => 2, 'CLASS' => 2,
3707
'CLOB' => 2, 'CONDITION' => 2, 'CONSTRUCTOR' => 2,
3708
'CONTAINS' => 2, 'CUBE' => 2, 'CURRENT_PATH' => 2,
3709
'CURRENT_ROLE' => 2, 'DATALINK' => 2, 'DEREF' => 2,
3710
'DESTROY' => 2, 'DESTRUCTOR' => 2, 'DETERMINISTIC' => 2,
3711
'DO' => 2, 'DYNAMIC' => 2, 'EVERY' => 2,
3712
'EXIT' => 2, 'EXPAND' => 2, 'EXPANDING' => 2,
3713
'FREE' => 2, 'FUNCTION' => 2, 'GROUPING' => 2,
3714
'HANDLER' => 2, 'HAST' => 2, 'HOST' => 2,
3715
'INITIALIZE' => 2, 'INOUT' => 2, 'ITERATE' => 2,
3716
'LARGE' => 2, 'LATERAL' => 2, 'LOCALTIME' => 2,
3717
'LOCALTIMESTAMP' => 2, 'LOCATOR' => 2, 'MEETS' => 2,
3718
'MODIFIES' => 2, 'NCLOB' => 2, 'NORMALIZE' => 2,
3719
'ORDINALITY' => 2, 'OUT' => 2, 'PARAMETER' => 2,
3720
'PATH' => 2, 'PERIOD' => 2, 'POSTFIX' => 2,
3721
'PRECEDES' => 2, 'PREFIX' => 2, 'READS' => 2,
3722
'REDO' => 2, 'REPEAT' => 2, 'RESULT' => 2,
3723
'ROLLUP' => 2, 'SETS' => 2, 'SPECIFIC' => 2,
3724
'SPECIFICTYPE' => 2, 'START' => 2, 'STATE' => 2,
3725
'STATIC' => 2, 'SUCCEEDS' => 2, 'TERMINATE' => 2,
3726
'THAN' => 2, 'TREAT' => 2, 'UNDO' => 2,
3727
'UNTIL' => 2,
3728
3729
'ACCESS' => 3, 'ANALYZE' => 3, 'AUDIT' => 3,
3730
'AUTO_INCREMENT' => 3, 'BACKUP' => 3, 'BDB' => 3,
3731
'BERKELEYDB' => 3, 'BIGINT' => 3, 'BREAK' => 3,
3732
'BROWSE' => 3, 'BTREE' => 3, 'BULK' => 3,
3733
'CHANGE' => 3, 'CHECKPOINT' => 3, 'CLUSTER' => 3,
3734
'CLUSTERED' => 3, 'COLUMNS' => 3, 'COMMENT' => 3,
3735
'COMPRESS' => 3, 'COMPUTE' => 3, 'CONTAINSTABLE' => 3,
3736
'DATABASE' => 3, 'DATABASES' => 3, 'DAY_HOUR' => 3,
3737
'DAY_MINUTE' => 3, 'DAY_SECOND' => 3, 'DBCC' => 3,
3738
'DELAYED' => 3, 'DENY' => 3, 'DISK' => 3,
3739
'DISTINCTROW' => 3, 'DISTRIBUTED' => 3, 'DUMMY' => 3,
3740
'DUMP' => 3, 'ENCLOSED' => 3, 'ERRLVL' => 3,
3741
'ERRORS' => 3, 'ESCAPED' => 3, 'EXCLUSIVE' => 3,
3742
'EXPLAIN' => 3, 'FIELDS' => 3, 'FILE' => 3,
3743
'FILLFACTOR' => 3, 'FREETEXT' => 3, 'FREETEXTTABLE' => 3,
3744
'FULLTEXT' => 3, 'GEOMETRY' => 3, 'HASH' => 3,
3745
'HIGH_PRIORITY' => 3, 'HOLDLOCK' => 3, 'HOUR_MINUTE' => 3,
3746
'HOUR_SECOND' => 3, 'IDENTIFIED' => 3, 'IDENTITYCOL' => 3,
3747
'IDENTITY_INSERT' => 3, 'INCREMENT' => 3, 'INDEX' => 3,
3748
'INFILE' => 3, 'INITIAL' => 3, 'INNODB' => 3,
3749
'KEYS' => 3, 'KILL' => 3, 'LINENO' => 3,
3750
'LINES' => 3, 'LOAD' => 3, 'LOCK' => 3,
3751
'LONG' => 3, 'LONGBLOB' => 3, 'LONGTEXT' => 3,
3752
'LOW_PRIORITY' => 3, 'MASTER_SERVER_ID' => 3, 'MAXEXTENTS' => 3,
3753
'MEDIUMBLOB' => 3, 'MEDIUMINT' => 3, 'MEDIUMTEXT' => 3,
3754
'MIDDLEINT' => 3, 'MINUS' => 3, 'MINUTE_SECOND' => 3,
3755
'MLSLABEL' => 3, 'MODE' => 3, 'MRG_MYISAM' => 3,
3756
'NOAUDIT' => 3, 'NOCHECK' => 3, 'NOCOMPRESS' => 3,
3757
'NONCLUSTERED' => 3, 'NOWAIT' => 3, 'NUMBER' => 3,
3758
'OFFLINE' => 3, 'OFFSETS' => 3, 'ONLINE' => 3,
3759
'OPENDATASOURCE' => 3, 'OPENQUERY' => 3, 'OPENROWSET' => 3,
3760
'OPENXML' => 3, 'OPTIMIZE' => 3, 'OPTIONALLY' => 3,
3761
'OUTFILE' => 3, 'OVER' => 3, 'PCTFREE' => 3,
3762
'PERCENT' => 3, 'PLAN' => 3, 'PRINT' => 3,
3763
'PROC' => 3, 'PURGE' => 3, 'RAISERROR' => 3,
3764
'RAW' => 3, 'READTEXT' => 3, 'RECONFIGURE' => 3,
3765
'REGEXP' => 3, 'RENAME' => 3, 'REPLICATION' => 3,
3766
'REQUIRE' => 3, 'RESOURCE' => 3, 'RESTORE' => 3,
3767
'RLIKE' => 3, 'ROWCOUNT' => 3, 'ROWGUIDCOL' => 3,
3768
'ROWID' => 3, 'ROWNUM' => 3, 'RTREE' => 3,
3769
'RULE' => 3, 'SAVE' => 3, 'SETUSER' => 3,
3770
'SHARE' => 3, 'SHOW' => 3, 'SHUTDOWN' => 3,
3771
'SONAME' => 3, 'SPATIAL' => 3, 'SQL_BIG_RESULT' => 3,
3772
'SQL_CALC_FOUND_ROWS' => 3,'SQL_SMALL_RESULT' => 3, 'SSL' => 3,
3773
'STARTING' => 3, 'STATISTICS' => 3, 'STRAIGHT_JOIN' => 3,
3774
'STRIPED' => 3, 'SUCCESSFUL' => 3, 'SYNONYM' => 3,
3775
'SYSDATE' => 3, 'TABLES' => 3, 'TERMINATED' => 3,
3776
'TEXTSIZE' => 3, 'TINYBLOB' => 3, 'TINYINT' => 3,
3777
'TINYTEXT' => 3, 'TOP' => 3, 'TRAN' => 3,
3778
'TRUNCATE' => 3, 'TSEQUAL' => 3, 'TYPES' => 3,
3779
'UID' => 3, 'UNLOCK' => 3, 'UNSIGNED' => 3,
3780
'UPDATETEXT' => 3, 'USE' => 3, 'USER_RESOURCES' => 3,
3781
'VALIDATE' => 3, 'VARBINARY' => 3, 'VARCHAR2' => 3,
3782
'WAITFOR' => 3, 'WARNINGS' => 3, 'WRITETEXT' => 3,
3783
'XOR' => 3, 'YEAR_MONTH' => 3, 'ZEROFILL' => 3
3784
);
3785
3786
3787
safe_query("drop table crash_me10 $drop_attr");
3788
3789
foreach my $keyword (sort {$a cmp $b} keys %reserved_words)
3790
{
3791
$keyword_type= $reserved_words{$keyword};
3792
3793
$prompt= "Keyword ".$keyword;
3794
$config= "reserved_word_".$keywords_ext[$keyword_type]."_".lc($keyword);
3795
3796
report_fail($prompt,$config,
3797
"create table crash_me10 ($keyword int not null)",
3798
"drop table crash_me10 $drop_attr"
3799
);
3800
}
3801
}
3802
3803
#
3804
# Do a query on a query package object.
3805
#
3806
3807
sub limit_query
3808
{
3809
my($object,$limit)=@_;
3810
my ($query,$result,$retry,$sth);
3811
3812
$query=$object->query($limit);
3813
$result=safe_query($query);
3814
if (!$result)
3815
{
3816
$object->cleanup();
3817
return 0;
3818
}
3819
if (defined($query=$object->check_query()))
3820
{
3821
for ($retry=0 ; $retry < $retry_limit ; $retry++)
3822
{
3823
printf "query2: %-80.80s\n",$query if ($opt_log_all_queries);
3824
print LOG "$query;\n" if ($opt_log);
3825
if (($sth= $dbh->prepare($query)))
3826
{
3827
if ($sth->execute)
3828
{
3829
$result= $object->check($sth);
3830
$sth->finish;
3831
$object->cleanup();
3832
return $result;
3833
}
3834
print_query($query);
3835
$sth->finish;
3836
}
3837
else
3838
{
3839
print_query($query);
3840
}
3841
$retry=100 if (!$server->abort_if_fatal_error()); # No need to continue
3842
if ($retry == $retry_limit-2)
3843
{
3844
print "Forcing discoennect to retry query\n" if ($opt_debug);
3845
$dbh->disconnect || warn $dbh->errstr;
3846
}
3847
check_connect($object); # Check that server is still up
3848
}
3849
$result=0; # Query failed
3850
}
3851
$object->cleanup();
3852
return $result; # Server couldn't handle the query
3853
}
3854
3855
3856
sub report
3857
{
3858
my ($prompt,$limit,@queries)=@_;
3859
print "$prompt: ";
3860
if (!defined($limits{$limit}))
3861
{
3862
#print "Found limits for $prompt $limit\n";
3863
my $queries_result = safe_query(\@queries);
3864
add_log($limit, $safe_query_log);
3865
my $report_result;
3866
if ( $queries_result) {
3867
$report_result= "yes";
3868
add_log($limit,"As far as all queries returned OK, result is YES");
3869
} else {
3870
$report_result= "no";
3871
add_log($limit,"As far as some queries didnt return OK, result is NO");
3872
}
3873
save_config_data($limit,$report_result,$prompt);
3874
}
3875
print "REPORT $prompt $limits{$limit}\n";
3876
return $limits{$limit} ne "no";
3877
}
3878
3879
sub report_fail
3880
{
3881
my ($prompt,$limit,@queries)=@_;
3882
print "$prompt: ";
3883
if (!defined($limits{$limit}))
3884
{
3885
my $queries_result = safe_query(\@queries);
3886
add_log($limit, $safe_query_log);
3887
my $report_result;
3888
if ( $queries_result) {
3889
$report_result= "no";
3890
add_log($limit,"As far as all queries returned OK, result is NO");
3891
} else {
3892
$report_result= "yes";
3893
add_log($limit,"As far as some queries didnt return OK, result is YES");
3894
}
3895
save_config_data($limit,$report_result,$prompt);
3896
}
3897
print "$limits{$limit}\n";
3898
return $limits{$limit} ne "no";
3899
}
3900
3901
3902
# Return true if one of the queries is ok
3903
3904
sub report_one
3905
{
3906
my ($prompt,$limit,$queries)=@_;
3907
my ($query,$res,$result);
3908
print "$prompt: ";
3909
if (!defined($limits{$limit}))
3910
{
3911
save_incomplete($limit,$prompt);
3912
$result="no";
3913
foreach $query (@$queries)
3914
{
3915
if (safe_query_l($limit,$query->[0]))
3916
{
3917
$result= $query->[1];
3918
last;
3919
}
3920
}
3921
save_config_data($limit,$result,$prompt);
3922
}
3923
print "$limits{$limit}\n";
3924
return $limits{$limit} ne "no";
3925
}
3926
3927
3928
# Execute query and save result as limit value.
3929
3930
sub report_result
3931
{
3932
my ($prompt,$limit,$query)=@_;
3933
my($error);
3934
print "$prompt: ";
3935
if (!defined($limits{$limit}))
3936
{
3937
save_incomplete($limit,$prompt);
3938
$error=safe_query_result($query,"1",2);
3939
add_log($limit,$safe_query_result_log);
3940
save_config_data($limit,$error ? "not supported" :$last_result,$prompt);
3941
}
3942
print "$limits{$limit}\n";
3943
return $limits{$limit} ne "not supported";
3944
}
3945
3946
sub report_trans
3947
{
3948
my ($limit,$queries,$check,$clear)=@_;
3949
if (!defined($limits{$limit}))
3950
{
3951
save_incomplete($limit,$prompt);
3952
eval {undef($dbh->{AutoCommit})};
3953
if (!$@)
3954
{
3955
if (safe_query(\@$queries))
3956
{
3957
$dbh->rollback;
3958
$dbh->{AutoCommit} = 1;
3959
if (safe_query_result($check,"","")) {
3960
add_log($limit,$safe_query_result_log);
3961
save_config_data($limit,"yes",$limit);
3962
}
3963
safe_query($clear);
3964
} else {
3965
add_log($limit,$safe_query_log);
3966
save_config_data($limit,"error",$limit);
3967
}
3968
$dbh->{AutoCommit} = 1;
3969
}
3970
else
3971
{
3972
add_log($limit,"Couldnt undef autocommit ?? ");
3973
save_config_data($limit,"no",$limit);
3974
}
3975
safe_query($clear);
3976
}
3977
return $limits{$limit} ne "yes";
3978
}
3979
3980
sub report_rollback
3981
{
3982
my ($limit,$queries,$check,$clear)=@_;
3983
if (!defined($limits{$limit}))
3984
{
3985
save_incomplete($limit,$prompt);
3986
eval {undef($dbh->{AutoCommit})};
3987
if (!$@)
3988
{
3989
if (safe_query(\@$queries))
3990
{
3991
add_log($limit,$safe_query_log);
3992
3993
$dbh->rollback;
3994
$dbh->{AutoCommit} = 1;
3995
if (safe_query($check)) {
3996
add_log($limit,$safe_query_log);
3997
save_config_data($limit,"no",$limit);
3998
} else {
3999
add_log($limit,$safe_query_log);
4000
save_config_data($limit,"yes",$limit);
4001
};
4002
safe_query($clear);
4003
} else {
4004
add_log($limit,$safe_query_log);
4005
save_config_data($limit,"error",$limit);
4006
}
4007
}
4008
else
4009
{
4010
add_log($limit,'Couldnt undef Autocommit??');
4011
save_config_data($limit,"error",$limit);
4012
}
4013
safe_query($clear);
4014
}
4015
$dbh->{AutoCommit} = 1;
4016
return $limits{$limit} ne "yes";
4017
}
4018
4019
4020
sub check_and_report
4021
{
4022
my ($prompt,$limit,$pre,$query,$post,$answer,$string_type,$skip_prompt,
4023
$function)=@_;
4024
my ($tmp);
4025
$function=0 if (!defined($function));
4026
4027
print "$prompt: " if (!defined($skip_prompt));
4028
if (!defined($limits{$limit}))
4029
{
4030
save_incomplete($limit,$prompt);
4031
$tmp=1-safe_query(\@$pre);
4032
add_log($limit,$safe_query_log);
4033
if (!$tmp)
4034
{
4035
$tmp=safe_query_result($query,$answer,$string_type) ;
4036
add_log($limit,$safe_query_result_log);
4037
};
4038
safe_query(\@$post);
4039
add_log($limit,$safe_query_log);
4040
delete $limits{$limit};
4041
if ($function == 3) # Report error as 'no'.
4042
{
4043
$function=0;
4044
$tmp= -$tmp;
4045
}
4046
if ($function == 0 ||
4047
$tmp != 0 && $function == 1 ||
4048
$tmp == 0 && $function== 2)
4049
{
4050
save_config_data($limit, $tmp == 0 ? "yes" : $tmp == 1 ? "no" : "error",
4051
$prompt);
4052
print "$limits{$limit}\n";
4053
return $function == 0 ? $limits{$limit} eq "yes" : 0;
4054
}
4055
return 1; # more things to check
4056
}
4057
print "$limits{$limit}\n";
4058
return 0 if ($function);
4059
return $limits{$limit} eq "yes";
4060
}
4061
4062
4063
sub try_and_report
4064
{
4065
my ($prompt,$limit,@tests)=@_;
4066
my ($tmp,$test,$type);
4067
4068
print "$prompt: ";
4069
4070
if (!defined($limits{$limit}))
4071
{
4072
save_incomplete($limit,$prompt);
4073
$type="no"; # Not supported
4074
foreach $test (@tests)
4075
{
4076
my $tmp_type= shift(@$test);
4077
if (safe_query_l($limit,\@$test))
4078
{
4079
$type=$tmp_type;
4080
goto outer;
4081
}
4082
}
4083
outer:
4084
save_config_data($limit, $type, $prompt);
4085
}
4086
print "$limits{$limit}\n";
4087
return $limits{$limit} ne "no";
4088
}
4089
4090
#
4091
# Just execute the query and check values; Returns 1 if ok
4092
#
4093
4094
sub execute_and_check
4095
{
4096
my ($key,$pre,$query,$post,$answer,$string_type)=@_;
4097
my ($tmp);
4098
4099
$tmp=safe_query_l($key,\@$pre);
4100
4101
$tmp=safe_query_result_l($key,$query,$answer,$string_type) == 0 if ($tmp);
4102
safe_query_l($key,\@$post);
4103
return $tmp;
4104
}
4105
4106
4107
# returns 0 if ok, 1 if error, -1 if wrong answer
4108
# Sets $last_result to value of query
4109
sub safe_query_result_l{
4110
my ($key,$query,$answer,$result_type)=@_;
4111
my $r = safe_query_result($query,$answer,$result_type);
4112
add_log($key,$safe_query_result_log);
4113
return $r;
4114
}
4115
4116
sub safe_query_result
4117
{
4118
# result type can be
4119
# 8 (must be empty), 2 (Any value), 0 (number)
4120
# 1 (char, endspaces can differ), 3 (exact char), 4 (NULL)
4121
# 5 (char with prefix), 6 (exact, errors are ignored)
4122
# 7 (array of numbers)
4123
my ($query,$answer,$result_type)=@_;
4124
my ($sth,$row,$result,$retry);
4125
undef($last_result);
4126
$safe_query_result_log="";
4127
4128
printf "\nquery3: %-80.80s\n",$query if ($opt_log_all_queries);
4129
print LOG "$query;\n" if ($opt_log);
4130
$safe_query_result_log="<".$query."\n";
4131
4132
for ($retry=0; $retry < $retry_limit ; $retry++)
4133
{
4134
if (!($sth=$dbh->prepare($query)))
4135
{
4136
print_query($query);
4137
$safe_query_result_log .= "> prepare failed:".$dbh->errstr."\n";
4138
4139
if ($server->abort_if_fatal_error())
4140
{
4141
check_connect(); # Check that server is still up
4142
next; # Retry again
4143
}
4144
check_connect(); # Check that server is still up
4145
return 1;
4146
}
4147
if (!$sth->execute)
4148
{
4149
print_query($query);
4150
$safe_query_result_log .= "> execute failed:".$dbh->errstr."\n";
4151
if ($server->abort_if_fatal_error())
4152
{
4153
check_connect(); # Check that server is still up
4154
next; # Retry again
4155
}
4156
check_connect(); # Check that server is still up
4157
return 1;
4158
}
4159
else
4160
{
4161
last;
4162
}
4163
}
4164
if (!($row=$sth->fetchrow_arrayref))
4165
{
4166
print "\nquery: $query didn't return any result\n" if ($opt_debug);
4167
$safe_query_result_log .= "> didn't return any result:".$dbh->errstr."\n";
4168
$sth->finish;
4169
return ($result_type == 8) ? 0 : 1;
4170
}
4171
if ($result_type == 8)
4172
{
4173
$sth->finish;
4174
return 1;
4175
}
4176
$result=0; # Ok
4177
$last_result= $row->[0]; # Save for report_result;
4178
$safe_query_result_log .= ">".$last_result."\n";
4179
# Note:
4180
# if ($result_type == 2) We accept any return value as answer
4181
4182
if ($result_type == 0) # Compare numbers
4183
{
4184
$row->[0] =~ s/,/./; # Fix if ',' is used instead of '.'
4185
if ($row->[0] != $answer && (abs($row->[0]- $answer)/
4186
(abs($row->[0]) + abs($answer))) > 0.01)
4187
{
4188
$result=-1;
4189
$safe_query_result_log .=
4190
"We expected '$answer' but got '$last_result' \n";
4191
}
4192
}
4193
elsif ($result_type == 1) # Compare where end space may differ
4194
{
4195
$row->[0] =~ s/\s+$//;
4196
if ($row->[0] ne $answer)
4197
{
4198
$result=-1;
4199
$safe_query_result_log .=
4200
"We expected '$answer' but got '$last_result' \n";
4201
} ;
4202
}
4203
elsif ($result_type == 3) # This should be a exact match
4204
{
4205
if ($row->[0] ne $answer)
4206
{
4207
$result= -1;
4208
$safe_query_result_log .=
4209
"We expected '$answer' but got '$last_result' \n";
4210
};
4211
}
4212
elsif ($result_type == 4) # If results should be NULL
4213
{
4214
if (defined($row->[0]))
4215
{
4216
$result= -1;
4217
$safe_query_result_log .=
4218
"We expected NULL but got '$last_result' \n";
4219
};
4220
}
4221
elsif ($result_type == 5) # Result should have given prefix
4222
{
4223
if (length($row->[0]) < length($answer) &&
4224
substr($row->[0],1,length($answer)) ne $answer)
4225
{
4226
$result= -1 ;
4227
$safe_query_result_log .=
4228
"Result must have prefix '$answer', but '$last_result' \n";
4229
};
4230
}
4231
elsif ($result_type == 6) # Exact match but ignore errors
4232
{
4233
if ($row->[0] ne $answer)
4234
{ $result= 1;
4235
$safe_query_result_log .=
4236
"We expected '$answer' but got '$last_result' \n";
4237
} ;
4238
}
4239
elsif ($result_type == 7) # Compare against array of numbers
4240
{
4241
if ($row->[0] != $answer->[0])
4242
{
4243
$safe_query_result_log .= "must be '$answer->[0]' \n";
4244
$result= -1;
4245
}
4246
else
4247
{
4248
my ($value);
4249
shift @$answer;
4250
while (($row=$sth->fetchrow_arrayref))
4251
{
4252
$safe_query_result_log .= ">$row\n";
4253
4254
$value=shift(@$answer);
4255
if (!defined($value))
4256
{
4257
print "\nquery: $query returned to many results\n"
4258
if ($opt_debug);
4259
$safe_query_result_log .= "It returned to many results \n";
4260
$result= 1;
4261
last;
4262
}
4263
if ($row->[0] != $value)
4264
{
4265
$safe_query_result_log .= "Must return $value here \n";
4266
$result= -1;
4267
last;
4268
}
4269
}
4270
if ($#$answer != -1)
4271
{
4272
print "\nquery: $query returned too few results\n"
4273
if ($opt_debug);
4274
$safe_query_result_log .= "It returned too few results \n";
4275
$result= 1;
4276
}
4277
}
4278
}
4279
$sth->finish;
4280
print "\nquery: '$query' returned '$row->[0]' instead of '$answer'\n"
4281
if ($opt_debug && $result && $result_type != 7);
4282
return $result;
4283
}
4284
4285
#
4286
# Check that the query works!
4287
#
4288
4289
sub assert_crash
4290
{
4291
my($query)=@_;
4292
4293
if (!safe_query($query))
4294
{
4295
$query=join("; ",@$query) if (ref($query) eq "ARRAY");
4296
print "\nFatal error:\nquery: '$query'\nerror: $DBI::errstr\n";
4297
exit 1;
4298
}
4299
}
4300
4301
4302
sub read_config_data
4303
{
4304
my ($key,$limit,$prompt);
4305
if (-e $opt_config_file)
4306
{
4307
open(CONFIG_FILE,"+<$opt_config_file") ||
4308
die "Can't open configure file $opt_config_file\n";
4309
print "Reading old values from cache: $opt_config_file\n";
4310
}
4311
else
4312
{
4313
open(CONFIG_FILE,"+>>$opt_config_file") ||
4314
die "Can't create configure file $opt_config_file: $!\n";
4315
}
4316
select CONFIG_FILE;
4317
$|=1;
4318
select STDOUT;
4319
while (<CONFIG_FILE>)
4320
{
4321
chomp;
4322
if (/^(\S+)=([^\#]*[^\#\s])\s*(\# .*)*$/)
4323
{
4324
$key=$1; $limit=$2 ; $prompt=$3;
4325
if (!$opt_quick || $limit =~ /\d/ || $key =~ /crash_me/)
4326
{
4327
if ($key !~ /restart/i)
4328
{
4329
$limits{$key}=$limit eq "null"? undef : $limit;
4330
$prompts{$key}=length($prompt) ? substr($prompt,2) : "";
4331
$last_read=$key;
4332
delete $limits{'restart'};
4333
}
4334
else
4335
{
4336
$limit_changed=1;
4337
if ($limit > $limits{'restart'}{'tohigh'})
4338
{
4339
$limits{'restart'}{'low'} = $limits{'restart'}{'tohigh'};
4340
}
4341
$limits{'restart'}{'tohigh'} = $limit;
4342
}
4343
}
4344
}
4345
elsif (/\s*###(.*)$/) # log line
4346
{
4347
# add log line for previously read key
4348
$log{$last_read} .= "$1\n";
4349
}
4350
elsif (!/^\s*$/ && !/^\#/)
4351
{
4352
die "Wrong config row: $_\n";
4353
}
4354
}
4355
}
4356
4357
4358
sub save_config_data
4359
{
4360
my ($key,$limit,$prompt)=@_;
4361
$prompts{$key}=$prompt;
4362
return if (defined($limits{$key}) && $limits{$key} eq $limit);
4363
if (!defined($limit) || $limit eq "")
4364
{
4365
# die "Undefined limit for $key\n";
4366
$limit = 'null';
4367
}
4368
print CONFIG_FILE "$key=$limit\t# $prompt\n";
4369
$limits{$key}=$limit;
4370
$limit_changed=1;
4371
# now write log lines (immediatelly after limits)
4372
my $line;
4373
my $last_line_was_empty=0;
4374
foreach $line (split /\n/, $log{$key})
4375
{
4376
print CONFIG_FILE " ###$line\n"
4377
unless ( ($last_line_was_empty eq 1)
4378
&& ($line =~ /^\s+$/) );
4379
$last_line_was_empty= ($line =~ /^\s+$/)?1:0;
4380
};
4381
4382
if (($opt_restart && $limits{'operating_system'} =~ /windows/i) ||
4383
($limits{'operating_system'} =~ /NT/))
4384
{
4385
# If perl crashes in windows, everything is lost (Wonder why? :)
4386
close CONFIG_FILE;
4387
open(CONFIG_FILE,"+>>$opt_config_file") ||
4388
die "Can't reopen configure file $opt_config_file: $!\n";
4389
}
4390
}
4391
4392
sub add_log
4393
{
4394
my $key = shift;
4395
my $line = shift;
4396
$log{$key} .= $line . "\n" if ($opt_verbose);;
4397
}
4398
4399
sub save_all_config_data
4400
{
4401
my ($key,$tmp);
4402
close CONFIG_FILE;
4403
return if (!$limit_changed);
4404
open(CONFIG_FILE,">$opt_config_file") ||
4405
die "Can't create configure file $opt_config_file: $!\n";
4406
select CONFIG_FILE;
4407
$|=1;
4408
select STDOUT;
4409
delete $limits{'restart'};
4410
4411
print CONFIG_FILE
4412
"#This file is automaticly generated by crash-me $version\n\n";
4413
foreach $key (sort keys %limits)
4414
{
4415
$tmp="$key=$limits{$key}";
4416
print CONFIG_FILE $tmp . ("\t" x (int((32-min(length($tmp),32)+7)/8)+1)) .
4417
"# $prompts{$key}\n";
4418
my $line;
4419
my $log;
4420
my $last_line_was_empty=0;
4421
foreach $line (split /\n/, $log{$key})
4422
{
4423
print CONFIG_FILE " ###$line\n" unless
4424
( ($last_line_was_empty eq 1) && ($line =~ /^\s*$/));
4425
$last_line_was_empty= ($line =~ /^\s*$/)?1:0;
4426
};
4427
}
4428
close CONFIG_FILE;
4429
}
4430
4431
#
4432
# Save 'incomplete' in the limits file to be able to continue if
4433
# crash-me dies because of a bug in perl/DBI
4434
4435
sub save_incomplete
4436
{
4437
my ($limit,$prompt)= @_;
4438
save_config_data($limit,"incompleted",$prompt) if ($opt_restart);
4439
}
4440
4441
4442
sub check_repeat
4443
{
4444
my ($sth,$limit)=@_;
4445
my ($row);
4446
4447
return 0 if (!($row=$sth->fetchrow_arrayref));
4448
return (defined($row->[0]) && ('a' x $limit) eq $row->[0]) ? 1 : 0;
4449
}
4450
4451
4452
sub min
4453
{
4454
my($min)=$_[0];
4455
my($i);
4456
for ($i=1 ; $i <= $#_; $i++)
4457
{
4458
$min=$_[$i] if ($min > $_[$i]);
4459
}
4460
return $min;
4461
}
4462
4463
sub sql_concat
4464
{
4465
my ($a,$b)= @_;
4466
return "$a || $b" if ($limits{'func_sql_concat_as_||'} eq 'yes');
4467
return "concat($a,$b)" if ($limits{'func_odbc_concat'} eq 'yes');
4468
return "$a + $b" if ($limits{'func_extra_concat_as_+'} eq 'yes');
4469
return undef;
4470
}
4471
4472
#
4473
# Returns a list of statements to create a table in a portable manner
4474
# but still utilizing features in the databases.
4475
#
4476
4477
sub create_table
4478
{
4479
my($table_name,$fields,$index,$extra) = @_;
4480
my($query,$nr,$parts,@queries,@index);
4481
4482
$extra="" if (!defined($extra));
4483
4484
$query="create table $table_name (";
4485
$nr=0;
4486
foreach $field (@$fields)
4487
{
4488
$query.= $field . ',';
4489
}
4490
foreach $index (@$index)
4491
{
4492
$index =~ /\(([^\(]*)\)$/i;
4493
$parts=$1;
4494
if ($index =~ /^primary key/)
4495
{
4496
if ($limits{'primary_key_in_create'} eq 'yes')
4497
{
4498
$query.= $index . ',';
4499
}
4500
else
4501
{
4502
push(@queries,
4503
"create unique index ${table_name}_prim on $table_name ($parts)");
4504
}
4505
}
4506
elsif ($index =~ /^unique/)
4507
{
4508
if ($limits{'unique_in_create'} eq 'yes')
4509
{
4510
$query.= "unique ($parts),";
4511
}
4512
else
4513
{
4514
$nr++;
4515
push(@queries,
4516
"create unique index ${table_name}_$nr on $table_name ($parts)");
4517
4518
}
4519
}
4520
else
4521
{
4522
if ($limits{'index_in_create'} eq 'yes')
4523
{
4524
$query.= "index ($parts),";
4525
}
4526
else
4527
{
4528
$nr++;
4529
push(@queries,
4530
"create index ${table_name}_$nr on $table_name ($1)");
4531
}
4532
}
4533
}
4534
chop($query);
4535
$query.= ") $extra";
4536
unshift(@queries,$query);
4537
return @queries;
4538
}
4539
4540
4541
#
4542
# This is used by some query packages to change:
4543
# %d -> limit
4544
# %s -> 'a' x limit
4545
# %v -> "1,1,1,1,1" where there are 'limit' number of ones
4546
# %f -> q1,q2,q3....
4547
# %F -> q1 integer,q2 integer,q3 integer....
4548
4549
sub fix_query
4550
{
4551
my ($query,$limit)=@_;
4552
my ($repeat,$i);
4553
4554
return $query if !(defined($query));
4555
$query =~ s/%d/$limit/g;
4556
if ($query =~ /%s/)
4557
{
4558
$repeat= 'a' x $limit;
4559
$query =~ s/%s/$repeat/g;
4560
}
4561
if ($query =~ /%v/)
4562
{
4563
$repeat= '1,' x $limit;
4564
chop($repeat);
4565
$query =~ s/%v/$repeat/g;
4566
}
4567
if ($query =~ /%f/)
4568
{
4569
$repeat="";
4570
for ($i=1 ; $i <= $limit ; $i++)
4571
{
4572
$repeat.="q$i,";
4573
}
4574
chop($repeat);
4575
$query =~ s/%f/$repeat/g;
4576
}
4577
if ($query =~ /%F/)
4578
{
4579
$repeat="";
4580
for ($i=1 ; $i <= $limit ; $i++)
4581
{
4582
$repeat.="q$i integer,";
4583
}
4584
chop($repeat);
4585
$query =~ s/%F/$repeat/g;
4586
}
4587
return $query;
4588
}
4589
4590
4591
#
4592
# Different query packages
4593
#
4594
4595
package query_repeat;
4596
4597
sub new
4598
{
4599
my ($type,$init,$query,$add1,$add_mid,$add,$add_end,$end_query,$cleanup,
4600
$max_limit, $check, $offset)=@_;
4601
my $self={};
4602
if (defined($init) && $#$init != -1)
4603
{
4604
$self->{'init'}=$init;
4605
}
4606
$self->{'query'}=$query;
4607
$self->{'add1'}=$add1;
4608
$self->{'add_mid'}=$add_mid;
4609
$self->{'add'}=$add;
4610
$self->{'add_end'}=$add_end;
4611
$self->{'end_query'}=$end_query;
4612
$self->{'cleanup'}=$cleanup;
4613
$self->{'max_limit'}=(defined($max_limit) ? $max_limit : $main::query_size);
4614
$self->{'check'}=$check;
4615
$self->{'offset'}=$offset;
4616
$self->{'printf'}= ($add =~ /%d/);
4617
bless $self;
4618
}
4619
4620
sub query
4621
{
4622
my ($self,$limit)=@_;
4623
if (!$self->{'printf'})
4624
{
4625
return $self->{'query'} . ($self->{'add'} x $limit) .
4626
($self->{'add_end'} x $limit) . $self->{'end_query'};
4627
}
4628
my ($tmp,$tmp2,$tmp3,$i);
4629
$tmp=$self->{'query'};
4630
if ($self->{'add1'})
4631
{
4632
for ($i=0; $i < $limit ; $i++)
4633
{
4634
$tmp3 = $self->{'add1'};
4635
$tmp3 =~ s/%d/$i/g;
4636
$tmp .= $tmp3;
4637
}
4638
}
4639
$tmp .= " ".$self->{'add_mid'};
4640
if ($self->{'add'})
4641
{
4642
for ($i=0; $i < $limit ; $i++)
4643
{
4644
$tmp2 = $self->{'add'};
4645
$tmp2 =~ s/%d/$i/g;
4646
$tmp .= $tmp2;
4647
}
4648
}
4649
return ($tmp .
4650
($self->{'add_end'} x $limit) . $self->{'end_query'});
4651
}
4652
4653
sub max_limit
4654
{
4655
my ($self)=@_;
4656
my $tmp;
4657
$tmp=int(($main::limits{"query_size"}-length($self->{'query'})
4658
-length($self->{'add_mid'})-length($self->{'end_query'}))/
4659
(length($self->{'add1'})+
4660
length($self->{'add'})+length($self->{'add_end'})));
4661
return main::min($self->{'max_limit'},$tmp);
4662
}
4663
4664
4665
sub cleanup
4666
{
4667
my ($self)=@_;
4668
my($tmp,$statement);
4669
$tmp=$self->{'cleanup'};
4670
foreach $statement (@$tmp)
4671
{
4672
main::safe_query($statement) if (defined($statement) && length($statement));
4673
}
4674
}
4675
4676
sub check
4677
{
4678
my ($self,$sth)=@_;
4679
my $check=$self->{'check'};
4680
return &$check($sth,$self->{'limit'}) if (defined($check));
4681
return 1;
4682
}
4683
4684
sub check_query
4685
{
4686
return undef;
4687
}
4688
4689
4690
package query_num;
4691
4692
sub new
4693
{
4694
my ($type,$query,$end_query,$cleanup,$max_limit,$check)=@_;
4695
my $self={};
4696
$self->{'query'}=$query;
4697
$self->{'end_query'}=$end_query;
4698
$self->{'cleanup'}=$cleanup;
4699
$self->{'max_limit'}=$max_limit;
4700
$self->{'check'}=$check;
4701
bless $self;
4702
}
4703
4704
sub query
4705
{
4706
my ($self,$i)=@_;
4707
$self->{'limit'}=$i;
4708
return "$self->{'query'}$i$self->{'end_query'}";
4709
}
4710
4711
sub max_limit
4712
{
4713
my ($self)=@_;
4714
return $self->{'max_limit'};
4715
}
4716
4717
sub cleanup
4718
{
4719
my ($self)=@_;
4720
my($statement);
4721
foreach $statement ($self->{'$cleanup'})
4722
{
4723
main::safe_query($statement) if (defined($statement) && length($statement));
4724
}
4725
}
4726
4727
4728
sub check
4729
{
4730
my ($self,$sth)=@_;
4731
my $check=$self->{'check'};
4732
return &$check($sth,$self->{'limit'}) if (defined($check));
4733
return 1;
4734
}
4735
4736
sub check_query
4737
{
4738
return undef;
4739
}
4740
4741
#
4742
# This package is used when testing CREATE TABLE!
4743
#
4744
4745
package query_table;
4746
4747
sub new
4748
{
4749
my ($type,$query, $add, $end_query, $extra_init, $safe_query, $check,
4750
$cleanup, $max_limit, $offset)=@_;
4751
my $self={};
4752
$self->{'query'}=$query;
4753
$self->{'add'}=$add;
4754
$self->{'end_query'}=$end_query;
4755
$self->{'extra_init'}=$extra_init;
4756
$self->{'safe_query'}=$safe_query;
4757
$self->{'check'}=$check;
4758
$self->{'cleanup'}=$cleanup;
4759
$self->{'max_limit'}=$max_limit;
4760
$self->{'offset'}=$offset;
4761
bless $self;
4762
}
4763
4764
4765
sub query
4766
{
4767
my ($self,$limit)=@_;
4768
$self->{'limit'}=$limit;
4769
$self->cleanup(); # Drop table before create
4770
4771
my ($tmp,$tmp2,$i,$query,@res);
4772
$tmp =$self->{'query'};
4773
$tmp =~ s/%d/$limit/g;
4774
for ($i=1; $i <= $limit ; $i++)
4775
{
4776
$tmp2 = $self->{'add'};
4777
$tmp2 =~ s/%d/$i/g;
4778
$tmp .= $tmp2;
4779
}
4780
push(@res,$tmp . $self->{'end_query'});
4781
$tmp=$self->{'extra_init'};
4782
foreach $query (@$tmp)
4783
{
4784
push(@res,main::fix_query($query,$limit));
4785
}
4786
return \@res;
4787
}
4788
4789
4790
sub max_limit
4791
{
4792
my ($self)=@_;
4793
return $self->{'max_limit'};
4794
}
4795
4796
4797
sub check_query
4798
{
4799
my ($self)=@_;
4800
return main::fix_query($self->{'safe_query'},$self->{'limit'});
4801
}
4802
4803
sub check
4804
{
4805
my ($self,$sth)=@_;
4806
my $check=$self->{'check'};
4807
return 0 if (!($row=$sth->fetchrow_arrayref));
4808
if (defined($check))
4809
{
4810
return (defined($row->[0]) &&
4811
$row->[0] eq main::fix_query($check,$self->{'limit'})) ? 1 : 0;
4812
}
4813
return 1;
4814
}
4815
4816
4817
# Remove table before and after create table query
4818
4819
sub cleanup()
4820
{
4821
my ($self)=@_;
4822
main::safe_query(main::fix_query($self->{'cleanup'},$self->{'limit'}));
4823
}
4824
4825
#
4826
# Package to do many queries with %d, and %s substitution
4827
#
4828
4829
package query_many;
4830
4831
sub new
4832
{
4833
my ($type,$query,$safe_query,$check_result,$cleanup,$max_limit,$offset,
4834
$safe_cleanup)=@_;
4835
my $self={};
4836
$self->{'query'}=$query;
4837
$self->{'safe_query'}=$safe_query;
4838
$self->{'check'}=$check_result;
4839
$self->{'cleanup'}=$cleanup;
4840
$self->{'max_limit'}=$max_limit;
4841
$self->{'offset'}=$offset;
4842
$self->{'safe_cleanup'}=$safe_cleanup;
4843
bless $self;
4844
}
4845
4846
4847
sub query
4848
{
4849
my ($self,$limit)=@_;
4850
my ($queries,$query,@res);
4851
$self->{'limit'}=$limit;
4852
$self->cleanup() if (defined($self->{'safe_cleanup'}));
4853
$queries=$self->{'query'};
4854
foreach $query (@$queries)
4855
{
4856
push(@res,main::fix_query($query,$limit));
4857
}
4858
return \@res;
4859
}
4860
4861
sub check_query
4862
{
4863
my ($self)=@_;
4864
return main::fix_query($self->{'safe_query'},$self->{'limit'});
4865
}
4866
4867
sub cleanup
4868
{
4869
my ($self)=@_;
4870
my($tmp,$statement);
4871
return if (!defined($self->{'cleanup'}));
4872
$tmp=$self->{'cleanup'};
4873
foreach $statement (@$tmp)
4874
{
4875
if (defined($statement) && length($statement))
4876
{
4877
main::safe_query(main::fix_query($statement,$self->{'limit'}));
4878
}
4879
}
4880
}
4881
4882
4883
sub check
4884
{
4885
my ($self,$sth)=@_;
4886
my ($check,$row);
4887
return 0 if (!($row=$sth->fetchrow_arrayref));
4888
$check=$self->{'check'};
4889
if (defined($check))
4890
{
4891
return (defined($row->[0]) &&
4892
$row->[0] eq main::fix_query($check,$self->{'limit'})) ? 1 : 0;
4893
}
4894
return 1;
4895
}
4896
4897
sub max_limit
4898
{
4899
my ($self)=@_;
4900
return $self->{'max_limit'};
4901
}
4902
4903
#
4904
# Used to find max supported row length
4905
#
4906
4907
package query_row_length;
4908
4909
sub new
4910
{
4911
my ($type,$create,$null,$drop,$max_limit)=@_;
4912
my $self={};
4913
$self->{'table_name'}=$create;
4914
$self->{'null'}=$null;
4915
$self->{'cleanup'}=$drop;
4916
$self->{'max_limit'}=$max_limit;
4917
bless $self;
4918
}
4919
4920
4921
sub query
4922
{
4923
my ($self,$limit)=@_;
4924
my ($res,$values,$size,$length,$i);
4925
$self->{'limit'}=$limit;
4926
4927
$res="";
4928
$size=main::min($main::limits{'max_char_size'},255);
4929
$size = 255 if (!$size); # Safety
4930
for ($length=$i=0; $length + $size <= $limit ; $length+=$size, $i++)
4931
{
4932
$res.= "q$i char($size) $self->{'null'},";
4933
$values.="'" . ('a' x $size) . "',";
4934
}
4935
if ($length < $limit)
4936
{
4937
$size=$limit-$length;
4938
$res.= "q$i char($size) $self->{'null'},";
4939
$values.="'" . ('a' x $size) . "',";
4940
}
4941
chop($res);
4942
chop($values);
4943
return ["create table " . $self->{'table_name'} . " ($res)",
4944
"insert into " . $self->{'table_name'} . " values ($values)"];
4945
}
4946
4947
sub max_limit
4948
{
4949
my ($self)=@_;
4950
return $self->{'max_limit'};
4951
}
4952
4953
sub cleanup
4954
{
4955
my ($self)=@_;
4956
main::safe_query($self->{'cleanup'});
4957
}
4958
4959
4960
sub check
4961
{
4962
return 1;
4963
}
4964
4965
sub check_query
4966
{
4967
return undef;
4968
}
4969
4970
#
4971
# Used to find max supported index length
4972
#
4973
4974
package query_index_length;
4975
4976
sub new
4977
{
4978
my ($type,$create,$drop,$max_limit)=@_;
4979
my $self={};
4980
$self->{'create'}=$create;
4981
$self->{'cleanup'}=$drop;
4982
$self->{'max_limit'}=$max_limit;
4983
bless $self;
4984
}
4985
4986
4987
sub query
4988
{
4989
my ($self,$limit)=@_;
4990
my ($res,$size,$length,$i,$parts,$values);
4991
$self->{'limit'}=$limit;
4992
4993
$res=$parts=$values="";
4994
$size=main::min($main::limits{'max_index_part_length'},
4995
$main::limits{'max_char_size'});
4996
$size=1 if ($size == 0); # Avoid infinite loop errors
4997
for ($length=$i=0; $length + $size <= $limit ; $length+=$size, $i++)
4998
{
4999
$res.= "q$i char($size) not null,";
5000
$parts.= "q$i,";
5001
$values.= "'" . ('a' x $size) . "',";
5002
}
5003
if ($length < $limit)
5004
{
5005
$size=$limit-$length;
5006
$res.= "q$i char($size) not null,";
5007
$parts.="q$i,";
5008
$values.= "'" . ('a' x $size) . "',";
5009
}
5010
chop($parts);
5011
chop($res);
5012
chop($values);
5013
if ($main::limits{'unique_in_create'} eq 'yes')
5014
{
5015
return [$self->{'create'} . "($res,unique ($parts))",
5016
"insert into crash_q values($values)"];
5017
}
5018
return [$self->{'create'} . "($res)",
5019
"create index crash_q_index on crash_q ($parts)",
5020
"insert into crash_q values($values)"];
5021
}
5022
5023
sub max_limit
5024
{
5025
my ($self)=@_;
5026
return $self->{'max_limit'};
5027
}
5028
5029
sub cleanup
5030
{
5031
my ($self)=@_;
5032
main::safe_query($self->{'cleanup'});
5033
}
5034
5035
5036
sub check
5037
{
5038
return 1;
5039
}
5040
5041
sub check_query
5042
{
5043
return undef;
5044
}
5045
5046
5047
5048
### TODO:
5049
# OID test instead of / in addition to _rowid
Loggerhead is a web-based interface for Breezy
Version: 2.0.1