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