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